【MySQL基础篇】SQL指令：DQL及DCL

1、DQL

· DQL - 介绍

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据表中的记录。（在MySQL中应用是最为广泛的）

查询关键字：SELECT

· DQL - 语法

SELECT

            字段列表

FROM

            表名列表

WHERE

            条件列表

GROUP UP

            分组字段列表

HAVING

            分组后条件列表

ORDER BY

            排序字段列表

LIMIT

            分页参数 

· DQL - 基础查询

1、查询多个字段

SELECT 字段1，字段2，字段3，... FROM 表名;

SELECT * FROM 表名;

2、设置别名（增强字段可读性）

SELECT 字段1 [AS 别名1]，字段2[AS 别名2] .... 表名;

3、去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

insert into staff_table values(1,001,'张无忌','男','123456789000000000','大理','2000-12-31'),
                              (2,002,'赵敏','女','123456789000000001','北京','2000-01-01'),
                              (3,003,'韦小宝','男','123456789000000002','上海','2001-03-04'),
                              (4,004,'郭峰','男','123456789000000003','天津','2004-02-19'),
                              (5,005,'黄蓉','女','123456789000000004','广东','2006-11-12'),
                              (6,006,'杨过','男','123456789000000005','佛山','2004-02-14'),
                              (7,007,'狗蛋','男','','上海','2011-01-01');
#1、查询指定字段name，worknumber，dress，返回
select name,worknumber,dress from staff_table;
#2、查询返回所有字段
select * from staff_table;
#尽量不要去写*第一不直观，第二会影响效率，遵循开发规范
#也可以这样写
select id,worknumber,name,gender,idcard,dress,staff_date from staff_table;
#3、查询所有员工的工作地址，起别名
select dress as '工作地址' from staff_table;
#4、查询员工的上班地址（不要重复）
select distinct dress '工作地址' from staff_table;

· DQL - 条件查询

1、语法

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

2、条件
比较运算符功能>大于<小于>=大于等于<=小于等于=等于<> 或 !=不等于BETWEEN ...AND ...在某个范围值之内（含最小、最大值）IN(...)在in之后的列表中的值，多选一LIKE (占位符)模糊匹配（_匹配单个字符，%匹配任意个字符）IS NULL是NULL逻辑运算符功能AND 或 &&并且（多个条件同时成立）OR 或 ||或者（多个条件任意一个成立）NOT 或 ！非，不是

#1、查询编号等于4的员工
select * from staff_table where id=4;
#2、查询编号大于3的员工
select * from staff_table where id>3;
#3、查询编号大于等于3的员工
select * from staff_table where id>=3;
#4、查询没有身份证号的员工信息
select * from staff_table where idcard is null;
#5、查询有身份证号的员工信息
select * from staff_table where idcard is not null;
#6、查询编号不等于4的员工信息
select * from staff_table where id!=4;
#7、查询编号在2（包含）到6（包含）之间的信息
select * from staff_table where id between 2 and 6;
select * from staff_table where id>=2&&id<=6;
#8、查询性别为女且编号小于等于5的员工信息
select * from staff_table where gender='女'&&id<=5;
#9、查询编号为2或8或10的员工信息
select * from staff_table where id=2||id=8||id=10;
select * from staff_table where id(2,8,10);
#10、查询姓名为两个字的员工信息
select * from staff_table where name like '__';
#11、查询身份证最后一位为X的信息
select * from staff_table where idcard like '%X';

· DQL - 聚合函数

1、聚合函数是将一列数据作为一个整体，进行纵向计算。

2、常见聚合函数
函数功能count统计数量max最大值min最小值avg平均值sum
求和

3、语法

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

#1、统计该企业员工数量
select count(*) from staff_table;
#2、统计该企业员工编号的平均值
select avg(id) from staff_table;
#3、统计该企业员工最晚入职时间
select max(staff_date) from staff_table;
#4、统计该企业员工最早入职时间
select min(staff_date) from staff_table;
#5、统计上海地区员工编号之和
select sum(id) from staff_table where dress='上海';

注意：null值不参与运算

· DQL - 分组查询

1、语法

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

2、where与having的区别：

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤；

判断条件不同：where不能对聚合函数进行判断，而having可以。

#分组查询
#1、根据性别分组，统计男员工和女员工的数量
select gender,count(*) from staff_table group by gender;
#2、根据性别分组，统计男员工与女员工的平均编号
select gender,avg(id) from staff_table group by gender;
#3、查询编号小于5的员工，并根据工作地址分组，获取员工数量小于等于3的工作地址
select dress,count(*) from staff_table where id<5 group by dress having count(*)<=3;

注意：

执行顺序：where>聚合函数>having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

· DQL - 排序查询

1、语法

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

2、排序方式

ASC：升序（默认值)

DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

#1、根据根据性别对公司员工进行升序排序
select * from staff_table order by gender;
#2、根据入职时间对员工进行降序排序
select * from staff_table order by staff_date desc;
#3、根据根据性别对员工进行升序排序，性别相同，再按照入职时间进行降序排序
select * from staff_table order by gender,staff_date desc;

· DQL - 分页查询

1、语法

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

注意：

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数；

分页查询是数据库的方言，不同的数据库有不同的实现方式MySQL是LIMIT；

如果查询的是第一页的数据，起始索引可以省略，直接简写为limit 10。

#1、查询第一页的员工数据，每页展示3条记录
select * from staff_table limit 3;
#2、查询第二页员工数据，每页展示3条记录
select * from staff_table limit 3,3;
#3、查询第三页员工信息
select * from staff_table limit 6,3;

例题分析：

#1、查询编号为2，3，4的女员工信息
select * from staff_table where id in(2,3,4)&&gender='女';
#2、查询性别为男，并且编号在2~6（含）以内姓名为三个字的员工
select * from staff_table where gender='男'&&id between 2 and 6&&name like '___';
#3、统计员工表中，编号小于4的，男性员工与女性员工的人数
select gender,count(*) from staff_table where id<4 group by gender;
#4、查询所有编号小于5的员工的姓名和编号，并对查询结果按性别升序排序，如果性别相同按入职时间降序排序
select name,id from staff_table where id<5 order by gender,staff_date desc;
#5、查询性别为男，且编号在2~5（含）以内的前两个员工信息，查询结果按性别升序排序，如果性别相同按入职时间降序排序
select * from staff_table where gender='男'&&id between 2 and 5  order by gender,staff_date desc limit 2;

· DQL - 执行顺序

首先执行FROM来决定要查询的是哪张表的数据紧接着通过where来指定查询条件第三步通过group by以及having来指定分组以及分组以后的条件第四步决定我们查询要返回哪些字段，执行SELECT，再往下就是order by以及limit。

2、DCL

1、介绍

DCL英文全称是Date Control Language(数据控制语句)，用来管理数据库用户、控制数据库的访问权限。

· DCL - 用户管理

1、查询用户

USE mysql;

SELECT * FROM user;

2、创建用户

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

3、修改用户密码

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

4、删除用户

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

#1、创建用户名为test，只能在主机localhost访问，密码123456
create user 'test'@'localhost' identified by '123456';
#2、创建用户xiaodu，可以在任意主机访问密码为123456
create user 'xiaodu'@'%' identified by '123456';
#3、修改用户xiaodu的访问密码为 1234
alter user 'xiaodu'@'%' identified with mysql_native_password by '1234';
#4、删除test@localhost用户
drop user 'xiaodu'@'%';

注意：

主机名可以用%通配；

这类SQL开发人员操作的比较少，主要是DBA（数据库管理员）使用。

· DCL - 权限控制

权限说明ALL,ALL PRIVILEGES所有权限SELECT查询数据INSERT插入数据UPDATE修改数据DELETE删除数据ALTER修改表DROP删除数据库/表/视图CREATE创建数据库/表
1、查询权限

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

2、授予权限

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

3、撤销权限

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

#查询权限
show grants for 'test'@'localhost';
#授予权限
grant all on test.* to 'test'@'localhost';
#撤销权限
revoke all on test.* from 'test'@'localhost';