/* 时间：2021/12/18 功能：MySql 目录: 一: mysql 二: 库操作 三: 表 四: 字段/列 五: 记录/行 六: 查询 1 条件 2 关键字 3 排序 4 分页 5 聚合函数 6 分组 7 连接 七: 外键 八: 多表 九: 索引 十: 其他 十二: 思考 十三: 问题 */
一: mysql
-- mysql -- 登录 mysql -uroot -p -u: 表示MySQL服务端的用户名 -p: 表示MySQL服务端的密码 -- 退出 quit exit -- 清屏 system clear; -- 显示当前时间 select now();
二: 库操作
-- 库操作 -- 创建: 数据库 create database database_name charset=utf8; alter database database_name character set utf8; -- 修改编码 -- 删除: 数据库 drop database database_name; -- 修改: 使用数据库 use database_name; -- 查询: 所有数据库 show databases; -- 查询: 当前使用数据库 select database();
三: 表
-- 表 -- 创建表 create table name( 字段名称, 数据类型, 约束条件 ); create table students( id int unsigned primary key auto_increment not null, name varchar(20) not null, age tinyint unsigned default 0, height decimal(5,2), gender enum('男','女') ); CREATE TABLE st( id INT UNSIGNED PRIMARY KEY auto_increment NOT NULL, name VARCHAR(20) NOT NULL, age TINYINT UNSIGNED DEFAULT 0, height DECIMAL(5, 2), gender enum('男','女') ); -- 删除表 drop table table_name; drop table st; -- 清空表 -- delete : 可恢复 DELETE FROM table_name; DELETE FROM areas; -- truncate : 不可恢复 truncate FROM table_name; truncate FROM areas; -- 查询: 创表语句 show create table table_name; show create table st; -- 查询: 所有字段 desc table_name; desc st; -- 查看: 所有表 show tables;
四: 字段/列
-- 字段/列 -- 添加: add alter table table_name add column_name 数据类型 约束; alter table st add birthday datetime; -- 修改: 属性 -- 1 修改类型: modify alter table table_name modify column_name 数据类型 约束; alter table st modify birthday date not null; -- 2 修改名称和类型: change alter table table_name change column_name new_name 数据类型 约束; alter table st change birthday birth datetime not null; -- 修改: 数值 UPDATE <表名> SET <列名> = <表达式> UPDATE st set age=18, height=1.78 -- 删除: drop alter table table_name drop column_name; alter table st drop birth;
五: 记录/行
-- 记录/行 -- 添加 -- 1 全部字段 INSERT INTO table_name values(字段...); INSERT INTO st VALUES(0, '小明', 12, 1.7, '男') -- 2 部分字段 INSERT INTO table_name(column_name) VALUES(column_value); INSERT INTO st(id, name) VALUES(98, '小钱'); -- 3 默认字段 INSERT INTO table_name(column_name) VALUES(DEFAULT); INSERT INTO st(id, name) VALUES(DEFAULT, '小孙'); -- 4 多行插入 -- (1) 全部字段 INSERT INTO st VALUES(DEFAULT, '小周', DEFAULT, 1.69, DEFAULT), (DEFAULT, '小吴', DEFAULT, 1.69, DEFAULT); -- (1) 部分字段 INSERT INTO st(id, name) VALUES(DEFAULT, '诸葛'),(DEFAULT, '司马'); -- 5 复制表 --(1) 全部复制 insert into table1 select * from table2 --(1) 部分复制 INSERT INTO table2(id, name) SELECT id, name FROM table1; -- 修改 UPDATE <表名> SET <列名> = <表达式> WHERE <条件>; UPDATE st set age=18, height=1.78 WHERE id = 5; -- 删除 -- 1 逻辑删除 ALTER TABLE st ADD is_delete bit DEFAULT 0; UPDATE st set is_delete=1 WHERE name = '小明'; -- 2 物理删除 DELETE FROM <表名> WHERE <条件>; DELETE FROM st WHERE id = 5;
六: 查询
1 条件
-- 条件 -- 比较: = > >= < <= <> != -- 逻辑: and or not -- 模糊: like -- 范围: between...and...; in -- null: is not -- 比较: = > >= <= <> != --: = SELECT * FROM st WHERE NAME = "小明"; --: > >= < <= SELECT * FROM st WHERE height >= 1.7; --: <> != SELECT * FROM st WHERE NAME <> "小明"; -- 逻辑: and or not --: and SELECT * FROM st WHERE id > 3 AND is_delete = 0 --: or SELECT * FROM st WHERE id < 3 or id > 3; --: not SELECT * FROM st WHERE not(id < 3 or id > 3); -- 模糊: like --: % SELECT * FROM st WHERE `name` LIKE "小%"; --: _ SELECT * FROM st WHERE `name` LIKE "小_"; -- 范围: --: 1 between...and... SELECT * FROM st WHERE id BETWEEN 3 and 100; --: 2 in SELECT * FROM st WHERE id in (3, 5, 100); -- null: --: 1 is SELECT * FROM st WHERE height is NULL; --: 2 is not SELECT * FROM st WHERE height is not NULL;
2 关键字
-- 关键字: as distinct -- as: -- 字段 SELECT id, name as "姓名" FROM st; -- 表 SELECT s.id, s.name FROM st AS s; -- distinct SELECT DISTINCT name, age FROM st;
3 排序
-- 排序 -- order by SELECT * FROM st ORDER BY id; -- order by desc SELECT * FROM st ORDER BY id DESC;
4 分页
-- 分页 -- limit select * from 表名 limit start,count limit: 分页查询 - 关键字 start: 开始行索引, 默认0行 count: 查询条数 SELECT * FROM st LIMIT 3; SELECT * FROM st LIMIT 3, 5; -- n页线索 -- 每页m条数据，求第n页显示的数据 -- select * from st limit (n-1)*m, m;
5 聚合函数
-- 聚合函数 -- count(): 列总行数 -- max(): 列最大值 -- min(): 列最小值 -- sum(): 列数值和 -- avg(): 列数值和 -- count(): 列总行数 -- 总行数: 不包含null SELECT COUNT(height) FROM st; -- 总行数: 包含null SELECT COUNT(*) FROM st; -- max(): 列最大值 SELECT MAX(height) FROM st; -- min(): 列最小值 SELECT MIN(height) FROM st; -- sum(): 列数值和 SELECT AVG(height) FROM st; -- avg(): 列数值和 SELECT SUM(height) FROM st; -- null select avg(ifnull(height, 0)) from st WHERE gender='男';
6 分组
-- 分组查询 -- 单字段 SELECT gender FROM st GROUP BY gender; -- 多字段 SELECT name, gender FROM st GROUP BY name, gender; -- GROUP_CONCAT : 统计集合 - 分组字段 SELECT gender, GROUP_CONCAT(id) FROM st GROUP BY gender -- 聚合函数 SELECT gender, AVG(height) FROM st GROUP BY gender; SELECT gender, COUNT(*) FROM st GROUP BY gender; -- having SELECT gender, COUNT(*) FROM st GROUP BY gender HAVING COUNT(*) > 3; -- with rollup - 统计集合 - 所有分组 SELECT gender,GROUP_CONCAT(height) FROM st GROUP BY gender with rollup;
7 连接
-- 连接查询 -- 内连接 SELECT s.id, s.`name`, s.class_id, c.`name` FROM st as s INNER JOIN class as c on s.class_id = c.id -- 左连接 SELECT s.id, s.`name`, s.class_id, c.`name` FROM st as s LEFT JOIN class as c on s.class_id = c.id -- 右连接 SELECT s.id, s.`name`, s.class_id, c.`name` FROM st as s RIGHT JOIN class as c on s.class_id = c.id -- 子连接 -- 导入数据 create table areas( id varchar(30) not null primary key, title varchar(30), pid varchar(30) ); source areas.sql; -- 执行sql文件 -- 自查询 SELECT a1.id, a1.title, a2.title FROM areas AS a1 INNER JOIN areas AS a2 ON a1.pid = a2.id WHERE a2.title = "河北省"; -- 子查询 -- 查询: 省 市 SELECT * FROM areas WHERE pid = (SELECT id FROM areas WHERE title = "河北省"); -- 平均身高 select * FROM st WHERE height > (SELECT avg(height) FROM st) -- 查询学生表中不为空的班级名称 SELECT `name` FROM class WHERE class.id in (SELECT class_id FROM st WHERE class_id IS NOT NULL); -- 年龄和身高都是最大 SELECT * FROM st WHERE (age, height) = (SELECT MAX(age), MAX(height) FROM st);
七: 外键
-- 外键约束 数据类型(无符合) -- 添加约束 ALTER TABLE table_name1 add FOREIGN KEY(column_id) REFERENCES table_name2(id); ALTER TABLE st add FOREIGN KEY(class_id) REFERENCES class(id); -- 创造添加 CREATE TABLE school( id int not null PRIMARY KEY auto_increment, name VARCHAR(10) ); CREATE TABLE teacher( id int not null PRIMARY key auto_increment, name VARCHAR(10), s_id int not null, FOREIGN key(s_id) REFERENCES school(id) ); -- 删除约束 想·SHOW CREATE TABLE st; --获取名称 : 外键 ALTER TABLE st DROP FOREIGN KEY st_ibfk_1; --删除外键
八: 多表
-- 多表操作 -- 复制表 --(1) 全部复制 insert into table1 select * from table2 --(2) 部分复制 INSERT INTO table2(id, name) SELECT id, name FROM table1; --(3) 创建复制 CREATE TABLE good_brands( id int UNSIGNED PRIMARY KEY auto_increment, NAME VARCHAR(40) NOT NULL) SELECT brand_name as name FROM goods GROUP BY brand_name; -- 更新表值 UPDATE goods g INNER JOIN good_cates gc on g.cate_name = gc.`name` SET g.cate_name = gc.id -- 更改名称: 字段 ALTER TABLE goods change brand_name brand_id int not null CHANGE brand_name brand_id int NOT NULL;
九: 索引
-- 索引 -- 查看索引 show index from 表名; SHOW INDEX FROM test_index; -- 添加索引 alter table 表名 add index 索引名(列名, ..) alter table classes add index my_name (name); -- 删除索引 alter table 表名 drop index 索引名 alter table classes drop index my_name; -- 联合索引: 最左原则;
-- 开启运行时间监测： set profiling=1; -- 查找第1万条数据ha-99999 select * from test_index where title='ha-99999'; -- 查看执行的时间： show profiles; -- 给title字段创建索引： alter table test_index add index (title); -- 再次执行查询语句 select * from test_index where title='ha-99999'; -- 再次查看执行的时间 show profiles;
+----------+------------+--------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------------+ | 1 | 0.02104125 | select * from test_index where title = 'h_99999' | | 2 | 0.09241275 | alter table test_index add index(title) | | 3 | 0.00020575 | select * from test_index where title = 'h_99999' | +----------+------------+--------------------------------------------------+
十: 其他
-- 其他 -- 保留小数 - 两位 SELECT ROUND(avg(price),2) FROM goods; -- 行数 select count(*) from st;
-- 行列转换(纵横表): select * from scores; SELECT [姓名], SUM(CASE [课程] WHEN '语文' THEN [分数] ELSE 0 END) AS '语文', SUM(CASE [课程] WHEN '数学' THEN [分数] ELSE 0 END) AS '数学', SUM(CASE [课程] WHEN '物理' THEN [分数] ELSE 0 END) AS '物理' FROM scores GROUP BY [姓名]
十二: 思考
-- 组成: 行/列/表/库/系统 单表操作: 增删改查 多表操作: 增删改查 -- sql: 表的修改: 在列和行。 列行查询: 在表 列操作: alter; -- show/select show: 查询所有库/表; 创表语句; 索引 -- delete/drop: delete： 清空表; 清空行; drop: 删除库; 删除表; 删除列; 删除索引;
十三: 问题
-- 数据库系统三部曲(战德臣); 《SQL基础教程》; 《SQL进阶教程》; 《MySQL基础教程》; 《SQL必知必会》; 老男孩DBA