MySQL for OPS 02：SQL 基础

写在前面的话

上一节主要谈谈 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：数据查询语言

数据类型

这算是数据定义过程中的一个重点，针对不同的数据我们给定不同的数据类型，作用在于保证数据的准确性和标准性。

数值类型：

类	类型	说明
tinyint	整数	很小，0 - 255
smallint	整数	较小，-2^15 - 2^15
mediumint	整数	中等，很少用
int	整数	常规，-2^31 - 2^31
bigint	整数	较大，-2^63 - 2^63
float	浮点数	小型单精度浮点数，四个字节
double	浮点数	常规单精度浮点数，八个字节
decimal	定点数	包含整数部分，小数部分或者同时包含二者精确数值
bit	BIT	位字段值

字符类型：

类	类型	说明
char	文本	固定长度字符串，最多 255 个字符
varchar	文本	可变长度字符串，最多 65535 个字符
tinytext	文本	可变长度字符串，最多 255 个字符
text	文本	可变长度字符串，最多 65535 个字符
mediumtext	文本	可变长度字符串，最多 1600万+ 个字符
longtext	文本	可变长字符串，最多 42亿+ 字符
enum	整数	一组固定合法值组成的枚举
set	整数	一组固定合法值组成的集

在生产中最容易出现的就是字符串字段长度不足的问题，所以在设计的时候注意选对类型。

char 之所以是定长，比如我们定义 char(10)，那就意味着传的最大长度是 10，如果不够补充空格，反正就是占用 10 个字符。

varchar 相比之下，则是在指定的范围内按需分配，如 varchar(10)，最大长度 10，不足就不足，不浪费。

至于 enum，则属于特别的使用，如本字段的值是指定范围，如 enum("北京", "上海", "广州", "深圳")，这样使用能够优化索引。但用的其实并不多。

时间类型：

类型	格式	示例
date	YYYY-MM-DD	2019-08-08
time	hh:mm:ss[.uuuuuu]	10:50:29.123456
datetime	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2019-08-08 10:50:29.123456
timestamp	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2019-08-08 10:50:29.123456
year	YYYY	2019

timestamp 会受到时区的影响，且范围有限制。不是很建议。

二进制类型（这类不建议存到 MySQL）：

类	类型	说明
binary	二进制	类似 char 固定长度，但存储的是二进制
varbinary	二进制	类型 varchar
tinyblob	blob	最大长度 255 的 blob 列
blob	blob	最大长度 65535 的 blob 列
mediumblob	blob	最大长度 1600万+ 的 blob 列
longblob	blob	最大长度 42亿+ 的 blob 列

表属性

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. 库名要有意义。

特别注意：

禁止生产种执行 drop database xxx;

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 代替。

特别注意：

禁止生产种执行 drop table xxx;

2. 查看表结构：

desc students;

结果如图：

也可以查看建表语句：

show create table studentsG

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 操作，速度快。

都不推荐！！！

特别注意：

update / delete 一定要记得 where，否则原地爆炸。

在实际生产中，我们都是使用伪删除的方式，也就是新加数据状态字段，如可用为 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;

结果：

更多的函数可以查看官方文档：

https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

3. 单表子句，FROM：

select * from students;

不建议在生产中使用，如果表数据太大会造成卡死。

查询指定列：

select sname,sage,mobile from students;

如图：

4. 单表子句，WHERE：

在使用之前，MySQL 官方提供了专门用于学习的一个数据库：world，可以前往官网下载：

https://dev.mysql.com/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 "%bei%";

如图：

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 表，其中主要的字段包括：

字段名称	作用
TABLE_SCHEMA	库名
TABLE_NAME	表名
ENGINE	引擎
TABLE_ROWS	表的行数
AVG_ROW_LENGTH	表中行的平均大小（字节）
DATA_LENGTH	数据占用空间大小（字节）
INDEX_LENGTH	索引占用空间大小（字节）

可以直接查询：

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 命令如下表

命令	作用
show databases;	查看数据库
show tables;	查看表
show tables from information_schema;	查看指定库的表
show create database world;	查看建库语句
show create table city;	查看建表语句
show grants for root@'%';	查看用户授权
show charset;	查看支持的编码
show collation;	查看数据库支持的排序规则
show processlist;	查看数据库连接情况
show index from city;	查看表索引情况
show status;	查看数据库情况
show status like '%lock%';	模糊查询数据库状态
show variables;	查看数据库配置信息
show variables like "%timeout%";	模糊查询配置信息
show engines;	查看存储引擎
show engine innodb statusG	查看 innodb 相关信息
show binary logs;	列举所有二进制日志
show master status;	查看数据库日志位置
show binlog evnets in xxx	查看二进制日志事件
show slave statusG	查看从库状态
show relaylog events;	查看从库 relaylog

小结 

增删查改的核心语句都在这里，内容非常多！