MySQL-1-SQL讲解

B站学习视频来源

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。(记得加分号！！！)
2. SQL语句可以使用空格/缩进来增强语句的可读性(空格和缩进个数无所谓)。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释:
   • 单行注释: ——注释内容或#注释内容(MySQL特有)
   • 多行注释: /*注释内容*/

SQL分类

对应有：
DDL：定义数据库，定义表头(id，name)等等
DML：对表中数据增删改
DQL：对表中数据查询
DCL：创建用户，控制用户权限

DDL

DDL-数据库操作

查询所有数据库：

SHOW DATABASES;

查询当前数据库：

SELECT DATABASE(); 记得加()

创建数据库：

CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];

如果数据库名字已经存在，则会报错
避免报错(加 if not exists)：

加字符集(mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行)

删除数据库：

DROP DATABASE [ IF EXISTS ] 数据库名;
同理，在删除时使用if exists 可以避免报错。

使用数据库：

USE 数据库名;
切换所使用的数据库时使用，不需要退出，直接切换。

DDL-表操作

创建

创建表：

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	字段3 字段3类型 [COMMENT 字段3注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];
1
2
3
4
5
6
7

创建之前先进入使用的数据库,使用use xxx。

varchar表示的字符串类型，因为性别只有男或者女，所以字符串长度为1，即使用varchar(1) ，详细的数据类型会在下面介绍。

查询

查询当前数据库所有表：

SHOW TABLES; 需要先进入使用的数据库,使用use xxx。

查询表结构：

DESC 表名;

查询指定表的建表语句：

SHOW CREATE TABLE 表名;

修改

添加字段：

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
例：alter table user1 add idcard varchar(18) comment '身份证号';

修改字段的数据类型：

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
例如：alter table user1 modify name varchar(20);

修改字段名和字段类型：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
例：alter table user1 change id ip varchar(16);

删除字段：

ALTER TABLE 表名 DROP 字段名;

修改表名：

ALTER TABLE 表名 RENAME [TO/AS] 新表名;

删除表：

DROP TABLE [IF EXISTS] 表名;

清空表的数据（删除表并重新创建该表）

就是清空表的数据，只是先drop再创建，只保留表头：
TRUNCATE TABLE 表名;

DDL-数据类型及案例

数值类型

有符号范围指的是允许出现负数时的取值范围，定义时默认使用的有符号数据类型。
无符号范围指的是不允许出现负数时的取值范围，使用时在数据类型后标注UNSIGNED即可。
精度指的是整个数字长度，标度指的是小数部分长度，比如123.45的精度为5，标度为2。

例如：

1. 在定义年龄的时候，年龄的范围大概是0-200，而且都是整数。符合要求的类型有TINYINT、SMALLINT、MEDIUMINT、INT。为了节省空间，我们就可以使用TINYINT，如 ：age TINYINT UNSIGNED 。
2. 在定义分数的时候，范围大概为–100.0-100.0，是小数。常使用的类型为FLOAT和DOUBLE,在使用的时候还可以指定数字长度和小数位数：score DOUBLE(4,1),表示允许数字最长为4(100.0)，小数位的长度指定为1。

字符串类型

最上面两个在使用时都必须指定存放字符串的最大长度，char(10)，varchar(10)，超过指定长度时就会报错 。
char是定长字符串，如果指定长度为10，即使你的字符串长度不足10，它也会自动使用空格为字符串长度补充到10，char的性能更高。
varchar是变长字符串，传入的字符串数据长度是多少，会自动计算占用多少字符串的空间，因此varchar的性能较差一些。
例如：

1. 在定义姓名的时候因为每个人的姓名字符串长度不相同，所以用varchar更好；
2. 在定义性别的时候因为男、女这样的字符串都占一个长度，所以用char更好。

时间日期类型

要注意TIMESTAMP的范围很小，所以要根据场合慎用。
例如：
定义生日的时候，只记录年月日，所以使用DATE。

案例

create table emp(
id int,
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
)comment '员工表';
1
2
3
4
5
6
7
8
9

2. 为表emp添加一个新的字段“昵称”为nickname，类型为varchar(20)。
3. 将nickname改为username，类型为varchar(30)。
4. 删除字段username。

总结

DML

DML-添加数据

给指定字段添加字段：

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

给全部字段添加字段：

INSERT INTO 表名 VALUES (值1, 值2, ...);

批量添加数据：

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

例如：

insert into emp values(3,'3','韦一笑','男',38,'123456789087654321','1975-01-01'),
                       (4,'4','赵敏','女',39,'123456789087654322','1975-05-01');
1
2

注意事项

• 插入数据时，指定的字段顺序需要与值的顺序一一对应，如果是添加全部字段，需要输入所有对应的值，否则报错。
• 字符串和日期类型数据应该包含在引号中。
• 插入的数据大小应该在字段的规定范围内。

DML-修改数据

UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
例如：update emp set name='小昭',gender='女' where id=1;

注意事项

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

例如：update emp set entrydate='2008-01-01';

DML-删除数据：

DELETE FROM 表名 [ WHERE 条件 ];
例如：delete from emp where gender='女';

注意事项

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE语句不能删除某一个字段的值(可以使用UPDATE将字段值设置为NULL UPDATE 表名 set 字段=NULL WHERE ...)。

总结

DQL

开始之前，先重建数据表并上传一些数据，代码如下：

use study;

drop table if exists emp;

create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate date comment '入职时间'
)comment '员工表';

insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
values (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01'),
        (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
        (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),
        (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),
        (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),
        (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01'),
        (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),
        (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),
        (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),
        (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
        (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),
        (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),
        (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),
        (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),
        (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),
        (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01') ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

DQL-基础查询

查询多个字段

SELECT 字段1, 字段2, 字段3, ... FROM 表名;
例如：select id,name,idcard from emp;

SELECT * FROM 表名;
例如：select * from emp; 在实际开发中尽量不用 * , 一是不直观，二是数据太多时效率太低。

设置别名

SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
例如：select workaddress as '工作地址' from emp; / select workaddress '工作地址' from emp;

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;
例如：select distinct workaddress '工作地址' from emp;

转义

SELECT * FROM emp WHERE name LIKE '柳岩___';
SELECT * FROM 表名 WHERE name LIKE '柳岩/___' ESCAPE '/'
/ 之后的_不作为通配符

DQL-条件查询

语法：

SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件/运算符：

案例

• 查询年龄等于 88 的员工
  select * from emp where age = 88;
• 查询年龄小于等于 20 的员工信息
  select * from emp where age <= 20;
• 查询没有身份证号的员工信息
  select * from emp where idcard is null; 不能使用 idcard=null 来判断，因为null表示没有这个数据，所以不能比较。
• 查询有身份证号的员工信息
  select * from emp where idcard is not null;
• 查询年龄不等于 88 的员工信息
  select * from emp where age != 88;
select * from emp where age <> 88;
select * from emp where not age=88;
• 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
  select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20; 必须先写最小值再写最大值
• 查询性别为 女 且年龄小于 25岁的员工信息
  select * from emp where gender = '女' and age < 25;
• 查询年龄等于18 或 20 或 40 的员工信息
  select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,20,40);
• 查询姓名为两个字的员工信息 _ %
  select * from emp where name like '__';
• 查询身份证号最后一位是X的员工信息
  select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

DQL-聚合函数

常见聚合函数

语法

SELECT 聚合函数(字段列表) FROM 表名 ;

案例

• 统计该企业员工数量
  select count(*) from emp; -- 统计的是总记录数 ,相当于查行数
select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数
• 对于count聚合函数，统计符合条件的总记录数，还可以通过 count(数字/字符串)的形式进行统计
  查询，比如：select count(1) from emp;
• 统计该企业员工的平均年龄
  select avg(age) from emp;
• 统计该企业员工的最大年龄
  select max(age) from emp;
• 统计该企业员工的最小年龄
  select min(age) from emp;
• 统计西安地区员工的年龄之和
  select sum(age) from emp where workaddress = '西安';

注意

在聚合查询时是不计算NULL值的，比如在计算年龄平均值的时候，除数就是除了年龄是NULL值以外的行数。

DQL-分组查询

语法

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];

where 和 having 的区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以，但是having后判断的字段必须出现在group by后面的字段中。

注意

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 新版本中，当使用了分组语句的时候，在select查询列表中不能包含其它列名，除非该列同时也出现在了group子句中，但是聚合函数可以使用其他列名。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB

案例

• 根据性别分组 , 统计男性员工 和 女性员工的数量
  select gender,count(*) from emp group by gender; group by相当于把表聚合成了两组，然后count(*)就是分别对两组进行聚合查询数量并返回。
• 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
  select gender,avg(age) from emp group by gender;
• 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
  select workaddress as '工作地',count(*) from emp where age<45 group by workaddress having count(*)>=3;
  或者
  select workaddress as '工作地',count(*) as num_count from emp where age<45 group by workaddress having num_count>=3;
  扩展一下：select workaddress as '工作地',count(*) from emp where age<45 group by '工作地' having count(*)>=3;会报错，这行代码的意思我们想用workaddress的别名 ‘工作地’ 来group by，mysql在解析时会在分组时更换别名(DQL语法的解析顺序)，所以在这之前是不能使用别名的(只能输出显示)。
• 统计各个工作地址上班的男性及女性员工的数量
  select workaddress,gender,count(*) from emp group by workaddress,gender;

DQL-排序查询

语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

排序方式

• ASC : 升序(默认值)
• DESC: 降序

注意事项

• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 。

案例

• 根据年龄对公司的员工进行升序排序
  select * from emp order by age;
• 根据入职时间, 对员工进行降序排序
  select * from emp order by entrydate desc;
• 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
  select * from emp order by gender, entrydate desc;

DQL-分页查询

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• LIMIT在所有语法的最后执行，它只截取数据，不会对数据进行任何其他处理。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

案例

• 查询第1页员工数据, 每页展示10条记录
  select * from emp limit 0, 10; or select * from emp limit 10;
• 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
  select * from emp limit 10, 10;

DQL语法的书写顺序

SELECT
	字段列表
FROM
	表名字段
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后的条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14

DQL语法的执行顺序

1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE 或 WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.LIMIT
1
2
3
4
5
6
7
8
9
10
11

案例

• 我们给emp表起一个别名 e，然后在select 及 where中使用该别名。
  select e.name , e.age from emp e where e.age > 15 order by age asc;,可以看到from是先执行的。

注意，这里如果在from的时候就定义了别名，那么后面的操作都是基于这个‘虚拟表‘，所以只能用这个别名，而不能用原来的表名。
但是如果from之后在局部为某个字段定义了别名，那么因为已经导入了这张表，所以使用原来的字段名和别名都可以。

DQL语法的解析顺序

1.FROM 子句 组装来自不同数据源的数据
2.WHERE 子句 基于指定的条件对记录进行筛选
3.GROUP BY 子句 将数据划分为多个分组(划分时会解析as来形成别名，从而形成“虚拟表”，后面解析时便可以使用别名或者原名)
4.使用聚合函数对所有分组进行计算
5.使用HAVING子句筛选分组
6.计算所有的表达式
7.使用ORDER BY对结果集进行排序
1
2
3
4
5
6
7

案例

对于3可以给一个验证，
select workaddress as '工作地',count('工作地') as acount from emp;
select workaddress as '工作地',count('工作地') as acount from emp group by workaddress;
差别就在于是否有group by，上面那个因为别名没有被识别就会报错，下面的则可以正常运行。

DQL-案例练习

1. 查询年龄为20,21,22,23岁的员工信息。
   select * from emp where gender = '女' and age in (20,21,22,23);
2. 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。
   select * from emp where gender = '男' and (age between 20 and 40) and (name like '___');
3. 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
   select gender, count(*) from emp where age<60 group by gender;
4. 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
   select name, age from emp where age<=35 order by age asc, entrydate desc;
5. 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
   select * from emp where gender = '男' and age between 20 and 40 order by age asc , entrydate asc limit 5;

DCL

DCL管理用户

查询用户

select * from mysql.user; or
use mysql; select * from user;

用户信息都被储存在mysql数据库的user表里，所以查询该表就可以了。
其中 Host 代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以
远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一
个用户。

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码';

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户

DROP USER '用户名'@'主机名' ;

注意

• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
• 主机名可以使用 % 通配。
• 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库
  管理员）使用。

案例

• 创建用户itcast, 只能够在当前主机localhost访问, 密码123456;
  create user 'itcast'@'localhost' identified by '123456';
  
  在命令行重新使用此用户查看数据库可以发现只有两个数据库显示，就是因为还没有为他分配权限。

• 创建用户god, 可以在任意主机访问该数据库, 密码123456;
  create user 'god'@'%' identified by '123456';

• 修改用户god的访问密码为1234;
  alter user 'god'@'%' identified with mysql_native_password by '1234';（mysql_native_password是加密方式，在创建用户的时候也可以指定，客户端要使用mysql_native_password这种加密方式，caching_sha2_password是mysql8.0新引入的，注意一下即可）

• 删除 itcast@localhost 用户
  drop user 'itcast'@'localhost';

DCL权限控制

常用权限：

上述只是简单罗列了常见的几种权限描述，其他权限描述及含义，可以直接参考官方文档

查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意

• 多个权限之间，使用逗号分隔
• 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。

案例

• 查询 ‘god’@‘%’ 用户的权限
  show grants for 'god'@'%';
• 授予 ‘god’@‘%’ 用户mysql数据库所有表的所有操作权限
  grant all on mysql.* to 'god'@'%';
• 撤销 ‘god’@‘%’ 用户的itcast数据库的所有权限
  rrevoke all on study.* from 'god'@'%';（超级用户）