|
写在前面的话
上一节主要谈谈 MySQL 是怎么安装的以及最简单的初始化我们应该做哪些配置。其中也用到了一些简单的用户操作 SQL，所以这一节主要学习常用的 SQL 使用。
SQL 介绍
在了解 SQL 之前，对于 SQL 需要有以下简单的认知： 1. 主流的来个标准，SQL92 和 SQL99，在 MySQL 5.7 以后采用的是 SQL92。 2. 在 5.7 中新增了 sql_mode，作用在于限制哪些 SQL 能够使用，一个很明显的例子是 group by 的使用。
在上一节简单说过常用的 SQL 分类（主要前 3 个）： DDL：数据定义语言 DCL：数据控制语言 DML：数据操作语言 DQL：数据查询语言
数据类型
这算是数据定义过程中的一个重点，针对不同的数据我们给定不同的数据类型，作用在于保证数据的准确性和标准性。 数值类型：
字符类型：
在生产中最容易出现的就是字符串字段长度不足的问题，所以在设计的时候注意选对类型。 char 之所以是定长，比如我们定义 char(10)，那就意味着传的最大长度是 10，如果不够补充空格，反正就是占用 10 个字符。 varchar 相比之下，则是在指定的范围内按需分配，如 varchar(10)，最大长度 10，不足就不足，不浪费。 至于 enum，则属于特别的使用，如本字段的值是指定范围，如 enum("北京", "上海", "广州", "深圳")，这样使用能够优化索引。但用的其实并不多。
时间类型：
timestamp 会受到时区的影响，且范围有限制。不是很建议。
二进制类型（这类不建议存到 MySQL）：
表属性
1. 列属性（主要关键字）： primary key：主键，非空唯一约束，一个表只能有一个，但是能由多个列组成。 not null：非空约束，属于设计规范，尽可能不要列空，可以使用默认值 0 替代空。 unique key：唯一键，值不能重复 。 unsigned：无符号，主要用于数字列，非负数。 key：索引，可以给某列建立索引来优化查询。 default：默认值，列没有值时默认填充。 auto_increment：自增，主要针对数字，顺序填充数据，默认 1 开始，可以设置起始值和偏移量。 comment：注释。
2. 表属性： 存储引擎：在 5.7 默认是 innodb，在老版本中可能是 MyISAM。 字符集：常见的 utf8，utf8mb4 等。 校对（排序）规则：如 utf8_general_ci，utf8_bin 这种。主要用于大小写是否敏感。 可以通过以下 SQL 查看系统支持： # 查看编码 show charset; # 查看排序规则 show collation;
数据库操作（DDL）
1. 创建数据库并指定查看字符集： # 默认创建 create database a; show create database a; # 指定字符集创建 create database b charset utf8mb4; show create database b; # 指定字符集和排序规则创建 create database c charset utf8mb4 collate utf8mb4_bin; show create database c; 结果如下：
可以看到，MySQL 默认不指定字符集创建数据库的时候，创建的数据库的字符集为拉丁（latin1）。 排序规则 ci 结尾的都是大小写不敏感的。bin 大小写敏感。 当然，创建数据库还可以使用： create schema d; 这样也是能够创建数据库的。 建库规范： a. 库名不应该包含大小写。 b. 库名不该以数字开头。 c. 建库一定要加字符集。 d. 库名要有意义。
特别注意：
2. 修改数据库字符集： alter database a charset utf8mb4; 查看：
但是值得注意的是，修改后的字符集必须比之前的字符集范围更大。原因是数据的兼容性。 同时，不到万不得已一般不要修改。
数据表操作（DDL）
语法格式： create table students( 列1 属性（数据类型, 约束, 其它）, 列2 属性, ... )
1. 创建一个名为学校的测试库，创建一张名为学生的用户表： # 创建库
create database school charset utf8mb4 collate utf8mb4_bin;
# 指定库
use school;
# 创建表
create table students (
id int not null primary key auto_increment comment "学号",
sname varchar(255) not null comment "姓名",
sage tinyint unsigned not null default 0 comment "年龄",
sgender enum("m", "f", "n") not null default "n" comment "性别",
id_card char(18) not null unique comment "身份证",
add_time timestamp not null default now() comment "入学时间"
) engine=innodb charset=utf8mb4 comment "学生表";
建表规范： a. 表名小写，不能数字开头且具有意义。 b. 选择合适的数据类型，字符集，存储引擎。 c. 每个列都需要有注释说明且非空，如果为空选择 0 代替。
特别注意：
2. 查看表结构： desc students; 结果如图：
也可以查看建表语句： show create table students\G
3. 添加列： a. 直接添加手机号列： alter table students add mobile varchar(20) not null unique comment "手机号"; 如图：
默认添加列加到最后面。
b. 在 id_card 后面添加微信列： alter table students add wechat varchar(20) not null unique comment "微信" after id_card; 如图：
c. 在最前面加个列： alter table students add school_id tinyint not null comment "学校编号" first; 如图：
4. 修改列： a. 添加 qq 列，然后删除它： # 添加 alter table students add qq varchar(20) not null unique comment "QQ"; desc students; # 删除 alter table students drop qq; desc students; 添加：
删除：
b. 修改列属性： alter table students modify mobile varchar(15); 如图：
可以发现，虽然只是修改了 varchar，但是 null 也修改了。所以修改的时候建议多以属性都加一遍。
c. 修改列名和数据类型： alter table students change sgender sg char(1) not null default 'n'; 我们这里将 sgender 改为 sg，并修改类型：
在过去的版本中，我们应该避免在业务高峰期修改表结构，因为这会导致数据库锁表。 但可以使用 pt-osc 工具（Percona 的），可以在线修改，不再锁表，原理在于创建一个新表。 当然，在 MySQL 8.0 以后的版本以及自身集成了该工具。
5. 复制表结构建立一张新表： create table t1 like students; 对于 DCL，主要就两个，一个是 grant，一个是 revoke。
数据增删改（DML）
1. 插入数据： a. 最标准的 insert 语法： insert into students(school_id,id,sname,sage,sg,id_card,wechat,add_time,mobile) values (11,1,'张三',18,'m','511123199311111214','13290909801',now(),'13290909801');
b. 省事写法： 由于我们每个字段都按照顺序写，所以没必须要把字段列出来。 insert into students values (11,2,'李四',19,'f','511123199311111124','13290222201',now(),'13290222201');
c. 部分插入： 因为有些字段是由默认值的，所以我们可以就使用默认值： insert into students(school_id,sname,sage,id_card,wechat,mobile) values (11,'王五',18,'511123199311112224','13290909221','13290909221');
d. 同时插入多个： insert into students(school_id,sname,sage,id_card,wechat,mobile) values (11,'老赵',12,'511123133311112224','13233909221','13233909221'), (11,'老钱',16,'511333133311112224','13333909221','13333909221'), (12,'老孙',25,'511113133311112224','13111909221','13111909221');
e. 查看插入结果： select * from students; 结果如图：
2. 修改数据： a. 把张三的年龄改为 20： update students set sage=20 where sname='张三';
b. 把所有名字老开头的性别改为 m： update students set sg='m' where sname like '老%'; 结果如图：
c. 同时修改多个值： update students set sage=25,sg='f' where sname='张三'; 结果如图：
3. 删除数据： delete from students where sname="老孙"; 不推荐使用！！！
清空表的方法： delete from students; delete 逐行全部删除，属于 DDL 操作，速度慢！！！ 同时，我们可以从上面的 id 可以看到，由于发生了 delete 导致 id 不连续，确实的那一部分仍然占据着磁盘，这将导致可能数据量不大，但是磁盘占用很大的情况。这就是磁盘碎片。 truncate table students; truncate 全部清空数据页，干干净净，属于 DML 操作，速度快。 都不推荐！！！ 特别注意：
在实际生产中，我们都是使用伪删除的方式，也就是新加数据状态字段，如可用为 1，不可用为 0，我们删除就将状态由 1 改为 0。 alter table students add status tinyint not null default 1 comment "数据状态"; 删除就将改行数据 status 改为 0。
查询 DQL（Data Query Language）
1. 单独使用，查看系统参数：select @@xxx select @@port; select @@basedir; select @@datadir; select @@socket; select @@server_id; 结果如下：
有些复杂的我们可以使用 show 来模糊查询： show variables like 'log%'; 如图：
2. 单独使用，调用函数：select 函数(); # 显示当前信息
select now();
select database();
select user();
# 打印输出
select concat("hello world");
# 定制化输出
select concat(user,"@",host) from mysql.user;
# 一行输出
select group_concat(user,"@",host) from mysql.user;
结果：
更多的函数可以查看官方文档：
3. 单表子句，FROM： select * from students; 不建议在生产中使用，如果表数据太大会造成卡死。 查询指定列： select sname,sage,mobile from students; 如图：
4. 单表子句，WHERE： 在使用之前，MySQL 官方提供了专门用于学习的一个数据库：world，可以前往官网下载： https://dev./doc/index-other.html 如图：
将示例的数据库导入 MySQL 中，当然也可以下载 world_x，那个是新数据，只是测试没必要：
包含三张表，城市，国家，国家语言。可以使用 desc 了解每张表的数据结构。 a. 等值查询：查询中国（CHN）的城市 select * from city where CountryCode="CHN"; 结果：
也可以单独查询深圳： select * from city where Name="shenzhen"; 如图：
b. 比较查询：>，<，>=，<=，<> 查询世界人口小于 100 的城市： select * from city where population<100; 如图：
c. 逻辑查询：and，or 查询中国人口大于 500 万的： select * from city where countrycode="CHN" and population>=5000000; 如图：
查询中国或美国的城市： select * from city where countrycode="CHN" or countrycode="USA";
d. 模糊查询：% 查询 bei 开头的： select * from city where name like "bei%"; 查询名字中包含 bei 的（不走索引，性能极差，不推荐）： select * from city where name like "�i%"; 如图：
e. 列表匹配：in（类似 or） select * from city where countrycode in ("CHN","USA");
如图：
f. 查询范围：between and（类似 >= and <=） 查询人口在 830000 和 840000 之间的城市： select * from city where population between 830000 and 840000; 换成 and 写为： select * from city where population>=830000 and population<=840000; 结果：
5. GROUP BY 聚合函数 常用的聚合函数有以下一些： 最大值：max() 最小值：min() 平均值：avg() 求和：sum() 统计：count() 列转行：group_concat()
a. 统计每个国家的人口数量： select countrycode,sum(population) from city group by countrycode; 结果：
b. 统计中国各省总人口： select District,sum(population) from city where countrycode="CHN" group by District; 结果：
3. 统计世界上每个国家城市数量： select CountryCode,count(name) from city group by CountryCode; 结果：
6. 单表子句：HAVING 例如统计中国各省总人口数，只显示小于 100 万的。 select district,sum(population) from city where countrycode="CHN" group by district having sum(population)<1000000; 如图：
之所以使用 having，是因为 where 有顺序要求，分别是 where -- group by -- having。 在 group by 之后只能使用 having 不能再用 where 了。另外 having 后条件不走索引。
7. 排序和限制：ORDER BY LIMIT a. 查询中国的城市信息，并按照人口升序排序。 select * from city where countrycode="CHN" order by population; 如图：
b. 统计各省总人口数并按照降序排列。 select district,sum(population) from city where countrycode="CHN" group by district order by sum(population) desc; 默认升序，降序需要 desc：
c. 统计全国各省人口大于 500 万的按照降序排列并取前三。 select district,sum(population) from city where countrycode="CHN" group by district order by sum(population) desc limit 3; 如图：
当然，limit 也可做限制，如： select district,sum(population) from city where countrycode="CHN" group by district order by sum(population) desc limit 2,3; limit n,m，这意味着跳过前面的 n 行，然后显示 m 行，于是结果为：
当然也可以另外的写法：limit m offset n，一个意思。
8. 去重复：DISTINCT 查询所有国家： select distinct(countrycode) from city; 如果只是查询 countrycode 会有很多重复数据，可以通过 distinct 去掉重复：
9. 联合查询：union all 之前查询中国和美国使用了 and 和 in 的方法，但这并不是性能最优的方法。最好的是使用 union all： select * from city where countrycode="CHN" union all select * from city where countrycode="USA"; 值得注意的是 union 会去掉重复数据，而 union all 不会去重复。
10. 多表连接查询：join 首选准备 4 张表，关系是这样的：
建表语句： -- 删掉旧数据新建数据库
drop database school;
create database school charset utf8mb4 collate utf8mb4_bin;
use school;
-- 学生表
create table student(
sno int not null primary key auto_increment comment "学号",
sname varchar(20) not null comment "姓名",
sage tinyint unsigned not null comment "年龄",
sgender enum("m","f","n") not null default "n" comment "性别"
) engine=innodb charset=utf8mb4;
-- 课程表
create table course(
cno int not null primary key auto_increment comment "课程编号",
cname varchar(20) not null comment "课程名称",
tno int not null comment "教师编号"
) engine=innodb charset=utf8mb4;
-- 学生成绩表
create table sc(
sno int not null comment "学号",
cno int not null comment "课程编号",
score tinyint not null default 0 comment "成绩"
) engine=innodb charset=utf8mb4;
-- 教师表
create table teacher(
tno int not null primary key auto_increment comment "教师编号",
tname varchar(20) not null comment "教师名字"
) engine=innodb charset=utf8mb4;
基础数据： -- 学生信息 INSERT INTO student VALUES (1,'张三',18,'m'), (2,'李四',18,'m'), (3,'王五',18,'m'), (4,'老赵',19,'f'), (5,'老钱',20,'m'), (6,'老孙',20,'f'), (7,'老李',25,'m'); -- 教师信息 INSERT INTO teacher VALUES (101,'李老师'), (102,'张老师'), (103,'王老师'); -- 课程信息 INSERT INTO course VALUES (1001,'linux',101), (1002,'python',102), (1003,'mysql',103); -- 学生成绩 INSERT INTO sc VALUES (1,1001,80), (1,1002,59), (2,1002,90), (2,1003,100), (3,1001,99), (3,1003,40), (4,1001,79), (4,1002,61), (4,1003,99), (5,1003,40), (6,1001,89), (6,1003,77), (7,1001,67), (7,1003,82);
最后效果：
多表查询测试： a. 统计张三学习了几门课： select st.sname,count(sc.cno) as course_nums from student as st join sc on st.sno=sc.sno where st.sname="张三"; 这里用到的知识有： 1. 通过 as 可以对字段就行取别名，便于后面书写使用。 2. 在一开始不知道这么写的时候可以选择将 select 和 from 之间的内容替换为 *，然后再根据需求修改。 结果：
b. 查询张三学习的课程名称： 同样可以先查出所有信息： select * from student as st join sc on st.sno=sc.sno join course as co on co.cno=sc.cno where st.sname="张三"; 结果：
然后我们只需要姓名列和课程名称列： select st.sname,co.cname from student as st join sc on st.sno=sc.sno join course as co on co.cno=sc.cno where st.sname="张三"; 结果：
c. 查询李老师教的学生名字： select te.tname,co.cname,st.sname from teacher as te join course as co on co.tno=te.tno join sc on sc.cno=co.cno join student as st on st.sno=sc.sno where te.tname="李老师"; 结果：
d. 计算李老师所教课程的平均分： select avg(sc.score) from teacher as te join course as co on te.tno=co.tno join sc on co.cno=sc.cno where te.tname="李老师"; 结果：
e. 计算每个老师的平均分，并降序排列： select te.tname,avg(sc.score) from teacher as te join course as co on te.tno=co.tno join sc on co.cno=sc.cno group by te.tname order by avg(sc.score) desc; 结果：
f. 查询李老师80分以下的学生： select * from teacher as te join course as co on te.tno=co.tno join sc on sc.cno=co.cno join student as st on st.sno=sc.sno where te.tname="李老师" and sc.score<80; 结果：
g. 查询所有老师成绩不及格的： select * from teacher as te join course as co on co.tno=te.tno join sc on sc.cno=co.cno join student as st on sc.sno=st.sno where sc.score<60; 结果：
h. 查询平均成绩大于 60 的学生： select st.sname,avg(score) from sc join student as st on st.sno=sc.sno group by sc.sno having avg(score)>60; 结果：
这里值得注意的是，由于 group by 后面不能使用 where，所以筛选条件变成 having。
i. 显示各门成绩的最高分最低分： select co.cname,max(sc.score) as "最高分",min(sc.score) as "最低分" from sc join course as co on co.cno=sc.cno group by sc.cno; 结果：
11. information_schema 视图库： 在说明这个之前，可以简单了解以下视图。可以这样理解，视图就是对复杂 SQL 的封装，举个例子： select co.cname,max(sc.score) as "最高分",min(sc.score) as "最低分" from sc join course as co on co.cno=sc.cno group by sc.cno; 这是上面一条复杂的查询，如果每次用这个就写一次，这么长肯定很麻烦，这就可以将它保存为视图： create view mytest_view as select co.cname,max(sc.score) as "最高分",min(sc.score) as "最低分" from sc join course as co on co.cno=sc.cno group by sc.cno; 可以在前面增加创建视图：create view 视图名字 as 此时就可以直接使用： select * from mytest_view; 结果：
在 MySQL 5.7 中，有三个库用于存储视图：information_schema，performance_schema，sys 这里主要谈谈 information_schema 中的 tables 表，其中主要的字段包括：
可以直接查询： select TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH,INDEX_LENGTH from tables;
示例： a. 显示每个库都有哪些表： select TABLE_SCHEMA,group_concat(TABLE_NAME) from information_schema.tables group by TABLE_SCHEMA; 结果类似： | world | countrylanguage,country,city|
b. 统计所有库下表的个数： select TABLE_SCHEMA,count(TABLE_NAME) from information_schema.tables group by TABLE_SCHEMA; 结果：
c. 统计 world 下面每张表所占的磁盘： select TABLE_NAME,concat((DATA_LENGTH INDEX_LENGTH)/1024, " KB") as LENGTH from information_schema.tables where TABLE_SCHEMA="world"; 结果：
当然会发现小数位数太多，没啥意义，可以设置小数：保留两位 select TABLE_NAME,concat(round((DATA_LENGTH INDEX_LENGTH)/1024, 2)," KB") as LENGTH from information_schema.tables where TABLE_SCHEMA="world"; 结果：
d. 统计所有库占用大小： select TABLE_SCHEMA,concat(round(sum(DATA_LENGTH INDEX_LENGTH)/1024,2)," KB") as DB_LENGTH from information_schema.tables group by TABLE_SCHEMA; 结果：
e. 统计整个库占用的空间： select concat(sum(DATA_LENGTH INDEX_LENGTH)/1024," KB") as TOTAL_LENGTH from information_schema.tables; 结果：
f. 假设现在有 1000 张表，需要单独生产备份语句，就需要用到 SQL 拼接： mysqldump -uroot -p123 world city >/tmp/world_city.sql 这是备份单个表的语句，如果需要备份 1000 个，可以使用 SQL 来生成： select concat("mysqldump -uroot -p123 ",TABLE_SCHEMA," ",TABLE_NAME," >/tmp/",TABLE_SCHEMA,"_",TABLE_NAME,".sql") from information_schema.tables where TABLE_SCHEMA="world";
结果如下：
但这只是单纯的 SQL 结果，可以将其输出到文件：前提是在 /etc/my.cnf 中指定安全目录 secure-file-priv=/tmp select concat("mysqldump -uroot -p123 ",TABLE_SCHEMA," ",TABLE_NAME," >/tmp/",TABLE_SCHEMA,"_",TABLE_NAME,".sql") from information_schema.tables where TABLE_SCHEMA="world" into outfile '/tmp/1.sh'
12. show 命令如下表
小结
增删查改的核心语句都在这里，内容非常多！ 来源：https://www./content-2-389751.html |
|
|
