DQL语句介绍
DQL是数据查询语言,用来查询数据库中表的记录
DQL-基本查询语句
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVIMG
分组后条件列表
ORDER BY
排列字段列表
LIMIT
分页参数
讲解过程:基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询
DQL-基本查询
- 查询多个字段
SELECT 字段1,字段2,字段3,... FROM表名;
SELECT name,wordno
FROM emp;
SELECT * FROM 表名;(查询返回所有字段)
SELECT *
FROM emp
- 设置别名
设置别名SELECT 字段1[AS 别名],字段2[AS 别名]。。。FROM 表名;
SELECT entrydate as '入职日期'
FROM emp;
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
SELECT DISTINCT entrydate as '入职日期'
FROM emp;
DQL-条件查询
1.语法
SELECT * FROM 表名;(查询返回所有字段)
- 条件
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<>或!= | 不等于 |
BETWEEN...AND... | 在某个范围之类(含最小值、最大值) |
IN(...) | 在in之后的列表中的值、多选一 |
LIKE占位符 | 模糊匹配(_匹配单个字符,%匹配日任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
AND或 && | 并且(多个条件同时成立) |
OR或|| | 或者(多个条件任意一个成立) |
NOT或! | 非,不是 |
实例代码:
查询年龄小于20的员工姓名:
SELECT name
FROM emp
WHERE age<20;
查询年龄小于等于20 的员工姓名:
SELECT *
FROM emp
WHERE age<=20;
查询身份证号为空的员工:
SELECT *
FROM emp
WHERE idcard is null;
查询15到20岁之间的员工(不包含):
SELECT *
FROM emp
WHERE 15<age AND age<20;(开区间)
查询15到20岁之间的员工(包含)SELECT *
FROM emp
WHERE age BETWEEN 15 AND 20(含最小值、最大值)
查询15岁、20岁、23岁的员工SELECT * FROM emp WHERE age=18 || age = 20 || age = 23; 与IN()替换效果相同:SELECT * FROM emp WHERE age IN(18,20,23);
查询姓名为三个字的员工信息:
SELECT *
FROM emp
WHERE name like '___';
查询身份证号最后一位为6的员工信息:
SELECT *
FROM emp
WHERE idcard like '%6';
LIKE占位符:模糊匹配(_匹配单个字符,%匹配日任意个字符)
DQL-聚合函数
- 聚合函数:将一列数据作为一个整体,进行纵向计算。
- 常见聚合函数:
函数 | 功能 |
Count | 统计数量 |
Max | 最大值 |
Min | 最小值 |
Avg | 平均值 |
Sum | 求和 |
- 语法:
SELECT 聚合函数(字段列表) FROM 表名;
实例:
求员工总人数:
SELECT COUNT(*) as'总人数'
FROM emp;
求员工平均年龄:
SELECT avg(age) AS '员工平均年龄'
FROM emp;
求员工中最大年龄:
SELECT max(age) as '最大年龄'
FROM emp;
求员工中最小年龄:
SELECT min(age) AS '最小年纪'
FROM emp;
求员工中男生年龄总和:
SELECT sum(age) AS '男生年龄之和'
FROM emp
WHERE gender = '男';
DQL-分组查询
- 语法:
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
实例:
按照性别分组,显示各个性别人数;
SELECT gender,COUNT(*) as '人数'
FROM emp
GROUP BY gender;
按照性别分组,显示各个性别平均年龄;
SELECT gender ,avg(age) as '平均年龄'
FROM emp
GROUP BY gender;
筛选年龄30以下,按照年龄分组,展示人数大于等于2的:
SELECT age , count(*)
FROM emp
WHERE age <30
GROUP BY age
HAVING count(*)>=2;
- WHERE和HAVING区别
>执行时机不同:WHERE是分组之前进行过滤,不满足WHERE条件,不参与分组:而having是分组之后对对结果进行过滤。
>判断条件不同:WHERE不能对聚合函数进行判断,而HAVING可以。
注意:
- 执行顺序:WHERE>聚合函数>HAVING
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
DQL-排序字段列表
- 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
- 排序方式:
>ASC:升序(默认值)
>DESC:降序
实例:
按员工年龄大小正序排列
SELECT *
FROM emp
ORDER BY age ;
按入职时间长短排序
SELECT *
FROM emp
ORDER BY entrydate;
按年龄大小排序,相同年龄按入职时间倒叙排列
SELECT *
FROM emp
ORDER BY age ,entrydate DESC;
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
DQL-分页查询(方言:不同数据库语法不同)
- 语法
SELECT 字段列表 FROM 表名LIMIT 起始索引,查询记录数;
实例:
查询第一页,每页展示3条数据
SELECT *
FROM emp
LIMIT 0,3;
查询第二页,每页展示3条数据
SELECT *
FROM emp
LIMIT 3,3;
查询第三页,每页展示3条数据
SELECT *
FROM emp
LIMIT 3,3;
注意:
起始索引从0开始,起始索引= (查询页码 1)每页显示记录数
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
DQL-练习:
查询性别为男,年龄为20,21,23的员工;
SELECT *
FROM emp
WHERE gender = '男' AND age IN (20,21,23);
查询年纪20到40之间,姓名为三个字的男生;
SELECT *
FROM emp
WHERE gender= '男'AND name like '___' AND age BETWEEN 20 and 40;
查询年龄小于60,的男女数量;
SELECT gender,count(*)
FROM emp
WHERE age <60
GROUP BY gender;
查询所有小于等于35岁员工的姓名和年纪,并对结果按年龄升序排序,如果年纪相同按入职时间降序排列;
SELECT *
FROM emp
WHERE age <= 35
ORDER BY age ASC, entrydate DESC;
查询性别男,且年龄在20-40岁(含)以内的五个员工,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序。
SELECT *
FROM emp
WHERE age BETWEEN 20 and 40 AND gender = '男'
ORDER BY age ,entrydate
LIMIT 0,5;
DQL语句-执行顺序
MySQL-DCL语句
介绍:DCL数据控制语言,用来管理数据库用户、控制数据库的访问权限。
DCL-管理用户:
- 1.查询用户表
USE mysql ;
SELECT * FROM user;
- 创建用户:
CREATE USER ‘用户名’@’主机名’ IDENTIFED BY ‘密码’;
- 修改用户密码:
ALTER USER ‘用户名’@’主机名’ IDEBTIFIED WITH mysql_native_passwoerd BY ‘新密码’;
- 删除用户:
DROP USER ‘用户名’@’主机名’;
查询用户表:
USE mysql;
SELECT * FROM user;
创建用户:
CREATE user 'GUI1'@'%' identified by '123456';
修改用户密码:
ALTER USER 'GUI1'@'%' identified with mysql_native_password by '1234';
删除用户:
drop user 'GUI1'@'%';
DROP USER 'GUI'@'localhoust';
注意:
主机名可以使用%统配。
这类SQL开发人员操作的比较少,主要DBA数据库管理员使用;
DCL-权限控制
权限 | 说明 |
ALL,ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
其他权限描述及其含义,可以参考官方文档
- 查询权限:
SHOW GRANTS FOR ‘用户名’@’主机名’;
- 授予权限:
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@’主机名’;
GRANT ALL on sjk01.* TO 'GUI1'@'%';
- 撤销权限:
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@’主机名’;
revoke all on sjk01.* from 'GUI1'@'%';
查询权限:
SHOW GRANTS FOR GUI1;
授予权限
GRANT ALL on sjk01.* TO 'GUI1'@'%';
撤销权限:
revoke all on sjk01.* from 'GUI1'@'%';