五、DQL
1、什么是DQL
1.DQL(Data Query Language)即数据库查询语言,用来查询所需要的信息,在查询的过程中,需要判断所查询的数据与表之间的关,我们可以使用select语句来查询数据。
语法:select * from 表的名称 where 查询的条件
DML与DDL区别?
- DDL 对数据库 表结构 增加、修改 删除操作
- DML 表结构中的数据 增加(insert)、修改(update) 删除(delete)
2.查询语句语法格式
SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] …
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]
[limit <数字或者列表>]
分页、分组、排序
select *(列名称) from 表名称 where 条件
1.查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件;
2.SELECT 命令可以读取一条或者多条记录;
3.使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
4.使用 WHERE 语句来包含任何条件。
5.使用 LIMIT 属性来设定返回的记录数。
6.OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
3.数据的初始化
-- 创建数据库 ddl
create DATABASE if not EXISTS mayikt;
-- 使用mayikt数据库
use mayikt;
drop table mayikt_student;
-- 创建mayikt_student学生表
CREATE TABLE `mayikt_student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) not null COMMENT '姓名',
`age` tinyint COMMENT '年龄',
`address` varchar(255) COMMENT '地址',
`class_id` int COMMENT '班级id',
PRIMARY KEY (`id`)
);
-- 新增测试数据 dml
INSERT INTO mayikt_student VALUES(NULL,'张三丰',28,'湖北武汉','01');
INSERT INTO mayikt_student VALUES(NULL,'小哈',21,'上海','01');
INSERT INTO mayikt_student VALUES(NULL,'张三',17,'北京','02');
INSERT INTO mayikt_student VALUES(NULL,'李四',22,'山东','02');
INSERT INTO mayikt_student VALUES(NULL,'王麻子',11,'四川','02');
2、基本查询
-- 1.查询所有的学生
select * from mayikt_student;
-- 2.查询学生的姓名和年龄
select name,age from mayikt_student;
-- 3.别名称查询 使用关键字as
select * from mayikt_student as student;
-- 4.列别名称
select name as 姓名,age 年龄 from mayikt_student as student;
-- 5.去重复值
select DISTINCT class_id from mayikt_student;
-- 6.查询结果是表达式(运算值);将所有的学生年龄+5岁
select name,age+5 as age from mayikt_student;
-- 1.查询所有的学生
-- 2.查询学生的姓名和年龄
-- 3.别名称查询 使用关键字as
-- 4.列别名称
-- 5.去重复值
-- 6.查询结果是表达式(运算值);将所有的学生年龄+5岁
3、运算符
数据库中的表结构确立后,表中的数据代表的意义就已经确定。而通过MySQL运算符进行运算,就可以获取到表结构以外的另一种数据。例如,学生表中存在一个birth(出生日期)字段,这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份,那么得到的就是这个学生的实际年龄数据。这就是MySQL的运算符,所以熟悉并掌握运算符的应用,我们需要熟悉一下MySQL支持的4种运算符都具备哪些功能。
name | birth | |
张三丰 | 1997.10.7 | |
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
算术运算符
算术运算符是MySQL中最常用的一类运算符。MySQL支持的算术运算符包括:加、减、乘、除、求余。
运算符 | 作用 |
+ | 加法 |
- | 减法 |
* | 乘法 |
/ 或 DIV | 除法 |
% 或 MOD | 取余 |
select 6+2;
select 6-2;
select 6*2;
select 6/2;
select 6%2;
-- 将每位学生的年龄+10
SELECT `name`,age +10 as age from mayikt_student;
-- 将每位学生的年龄乘以5
SELECT `name`,age *5 as age from mayikt_student;
比较运算符
比较运算符是查询数据时最常用的一类运算符。SELECT语句中的条件语句经常要使用比较运算符。通过这些比较运算符,可以判断表中的哪些记录是符合条件
符号 | 描述 | 备注 |
= | 等于 | |
<>, != | 不等于 | |
> | 大于 | |
< | 小于 | |
<= | 小于等于 | |
>= | 大于等于 | |
BETWEEN | 在两值之间 | >=min&&<=max |
NOT BETWEEN | 不在两值之间 | |
IN | 在集合中 | |
NOT IN | 不在集合中 | |
<=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时,其所得值为1;而当一个操作码为NULL时,其所得值为0 |
LIKE | 模糊匹配 | |
REGEXP 或 RLIKE | 正则式匹配 | |
IS NULL | 为空 | |
IS NOT NULL | 不为空 |
逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真,结果返回1。如果表达式是假,结果返回0。逻辑运算符又称为布尔运算符。MySQL中支持4种逻辑运算符,分别是与、或、非和异或。
种逻辑运算符,分别是与、或、非和异或。
运算符号 | 作用 |
NOT 或 ! | 逻辑非 |
AND | 逻辑与 |
OR | 逻辑或 |
XOR | 逻辑异或 |
位运算符
参与运算符的操作数,按二进制位进行运算。包括位与(&)、位或(|)、位非(~)、位异或(^)、左移(<<)、右移(>>)6种。
运算符号 | 作用 |
& | 按位与 |
| | 按位或 |
^ | 按位异或 |
! | 取反 |
<< | 左移 |
>> | 右移 |
比较和逻辑运算符使用
--查询学生的名称是为张三丰
SELECT * from mayikt_student where name ='张三丰'
--查询学生的名称不是为张三丰
SELECT * from mayikt_student where name !='张三丰'
SELECT * from mayikt_student where name <>'张三丰'
SELECT * from mayikt_student where not (name ='张三丰')
--查询学生年龄是为17岁
SELECT * from mayikt_student where age=17
--查询学生年龄是大于17岁
SELECT * from mayikt_student where age>17
--查询学生年龄是小于17岁
SELECT * from mayikt_student where age<17
--查询学生年龄是18岁-40岁之间
SELECT * from mayikt_student where age>17 and age<41
SELECT * from mayikt_student where age>17 && age<41
SELECT * from mayikt_student where age BETWEEN 18 and 40
-- 查询年龄是在17或者 28岁的学生
SELECT * from mayikt_student where age=17 or age=28;
SELECT * from mayikt_student where age=17 || age=28;
SELECT * from mayikt_student where age in (17,28);
--查询名称含有“丰”
SELECT * from mayikt_student where name like '%丰%'
--查询名称开头“小”
SELECT * from mayikt_student where name like '小%'
--查询名称第二字“汉”
SELECT * from mayikt_student where name like '_汉%'
--查询地址是为null学生
SELECT * from mayikt_student where address is null;
--查询地址不是为null的学生
SELECT * from mayikt_student where address is not null;
--查询学生的名称是为张三丰
--查询学生的名称不是为张三丰
--查询学生年龄是为17岁
--查询学生年龄是大于17岁
--查询学生年龄是小于17岁
--查询学生年龄是18岁-40岁之间
-- 查询年龄是在17或者 28岁的学生
--查询名称含有“军”
--查询名称开头“小”
--查询名称第二字“汉”
--查询地址是为null学生
--查询地址不是为null的学生
4、排序
如果我们需要对读取的数据进行排序,可以利用 order by 根据字段来进行升序或者降序排列 再返回结果。
- 升序: 从小到大
- 降序:从大到小
order by 根据字段 数字、字母、汉字
语法格式:
以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT field1, field2,…fieldN table_name1, table_name2…
ORDER BY field1, [field2…] [ASC [DESC]]
1.asc代表 升序排列 desc代表降序排列 默认是为升序;
升序就是从小到大、降序就是从大到小
2.order by 可以支持单个字段,多个字段 ;
3.order by 放在查询语句最后面,limit 分页除外;
1.根据学生年龄从小到大;
SELECT * from mayikt_student order by age;
2.根据学生年龄从大到小;
SELECT * from mayikt_student order by age desc;
3.判断学生的年龄大于18岁,在从小到大排序
SELECT * from mayikt_student where age>18 order by age ;
4.根据学生的年龄从大到小排序,以班级id 从小到大排序 当年龄相同 则根据 班级id从小到大排序
SELECT * from mayikt_student order by age desc ,class_id desc ;
5.根据班级id去重,根据班级id从大到小排序
SELECT DISTINCT class_id from mayikt_student ORDER BY class_id desc;
1.根据学生年龄从小到大;
2.根据学生年龄从大到小;
3.判断学生的年龄大于18岁,在从小到大排序
4.根据学生的年龄从大到小排序,以班级id 从小到大排序 当年龄相同 则根据 班级id从小到大排序
5.根据班级id去重,根据班级id从大到小排序
一般的情况下我们使用数字排序,从小到大 或者从大到小;
如果是字母排序 则 根据字母的顺序 从A到Z排序 或者Z到A顺序
如果是汉字的拼音排序,用的比较多是在人名的排序中,按照姓氏的拼音字母,从A到Z排序
5、分页
概述:limit
在mysql中当数据量很大时,显示屏长度有限,我们可以对数据进行分页显示,例如数据总共
100条,每页10条数据,可以分成10页。
格式:
方式1:显示前n条
select 字段1,字段2 ... from mayikt_student limit n
方式2:分页显示
select 字段1,字段2 ... from mayikt_student limit m,n
m: 整数,表示从第几条索引开始 计算方式(当前页-1)*每页显示条数
n:整数,表示查询多少条数据
案例:
SELECT * from mayikt_student limit 5;
SELECT * from mayikt_student limit 5,5;
需求
1.查询用户表中前5条数据
SELECT * from mayikt_student limit 5;
2.从第6条开始显示 显示5条
SELECT * from mayikt_student limit 5,5;
6、聚合查询
我们在之前的查询是根据条件一行一行的判断,而使用聚合查询,它是对列的值进行计算,让后返回一个单一的值。
聚合函数 | 作用 |
count(age) | 统计指定列不为null的记录行数 |
sum() | 计算指定列的数据和 |
max() | 计算指定列的数据最大值 |
min() | 计算指定列的数据最小值 |
avg() | 计算指定列的数据平均值 |
1.查询学生表的总人数
SELECT count(*) from mayikt_student
2.查询学生年龄大于18的 总人数
SELECT count(*) from mayikt_student where age>18
3.查询classid=1 所有学生年龄总和
SELECT SUM(age) from mayikt_student where class_id='1'
4.查询学生最大年龄
SELECT max(age) from mayikt_student
5.查询学生最小年龄
SELECT max(age),min(age) from mayikt_student
6.求学生年龄的平均值
SELECT avg(age) from mayikt_student
1.查询学生表的总人数
2.查询学生年龄大于18的 总人数
3.查询classid=1 所有学生年龄总和
4.查询学生最大年龄
5.查询学生最小年龄
6.求学生年龄的平均值
聚合查询 null的处理
1.count函数对null值的处理
如果count函数的参数(*),则统计所有记录的行数,如果参数是为某字段,不统计含null值记录行数
2.sum、avg、max、min 函数对null 也是做忽略
1.查询学生年龄为null总人数
SELECT count(*) from mayikt_student where age is null;
7、分组查询
分组查询是指使用 group by 字句对查询信息进行分组。
格式:
select 字段1,字段2 ... from 表名称 group by 分组字段 having 分组条件
分组的条件使用 having 不是where
案例:
SELECT class_id ,count(*) from mayikt_student group by class_id;
1.统计class_id 分类对应学生的个数
SELECT class_id ,count(*) from mayikt_student group by class_id;
注意分组 返回列 只能为 分组的字段或者聚合函数;
分组之后的条件筛选
1.分组之后对统计结果进行分组条件筛选 必须使用having,不能够使用where
2.HAVING语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集
语法格式:
SELECT 字段1,字段2 ...
from mayikt_student GROUP BY 分组字段 having 分组条件;
案例需求:
统计每个班级学生人数大于1人以上的班级
SELECT class_id as 班级, count(*) as 人数 from mayikt_student GROUP BY class_id HAVING
count(*) >1
先from 、 GROUP BY 、count 、HAVING
注意事项 :
1、where 后不能跟聚合函数,因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。
8、简单一练
练习1
CREATE TABLE `meite_student` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
`sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`chinese` int DEFAULT NULL,
`english` int DEFAULT NULL,
`maths` int DEFAULT NULL
) ;
测试数据
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('1', '张三丰', '男', '98', '99', '100');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('2', '刘文', '女', '99', '87', '88');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('3', '王麻子', '女', '22', '55', '33');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('4', '黄红军', '男', '55', '33', '22');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('5', '张玲', '女', '95', '95', '88');
1.查询表中所有学生信息
2.查询表中所有学生信息 只返回学生名称和数学成绩 字段
3.过滤表中重复数据
4.统计每个学生的总分
5.所有学生总分 加5分
6.查询语文成绩大于80分以上同学
7.查询总分大于180分以上同学
8.查询英语成绩80-90 之间的同学
9.查询英语成绩不在80-90 之间的同学
10.查询英语成绩22,99,33学生 信息
11.查询所有姓张的学生信息 like
12.查询语文、数学、外语 大于等于72分的学生信息
13.查询英语大于等于72分或者总分大于等于180分以上学生信息 or
14.根据英语成绩字段排序 升序和降序
15.根据总分成绩 从高到低排列
16.统计学生表中 性别男和女 分别有多少学生
17.根据英语成绩字段排序 (从高到低排序 取前3名)
SELECT name, chinese+english+maths as 总分
FROM meite_student ORDER BY chinese+english+maths desc
limit 3;
1.查询表中所有学生信息
select * from meite_student;
2.查询表中学生名称和数学成绩
select `name`,maths from meite_student;
3.过滤表中重复数据
select DISTINCT * from meite_student;
4.统计每个学生的总分
select name 姓名, chinese+english+maths 总分 from meite_student
5.所有学生总分 加5分
select name 姓名, (chinese+english+maths)+5 总分 from meite_student
6.查询语文成绩大于80分以上同学
select * from meite_student where chinese>80
7.查询总分大于180分以上同学
select name 姓名, (chinese+english+maths) as 总分 from meite_student
where chinese+english+maths>180
8.查询英语成绩80-90 之间的同学
select * from meite_student
where english >80 and english<90
select * from meite_student
where english BETWEEN 80 and 90
9.查询英语成绩不在80-90 之间的同学
select * from meite_student
where english not BETWEEN 80 and 90
10.查询英语成绩22,99,33学生 信息
select * from meite_student
where english in(22,99,33)
11.查询所有姓张的学生信息
SELECT * from meite_student where name like '%张%'
12.查询语文、数学、外语 大于等于72分的学生信息
SELECT * from meite_student where chinese>=72
and english>=72 and maths>=72
13.查询语文和数学大于等于72分或者总分大于180分以上学生信息
SELECT * from meite_student where
(english>=72 and maths>=72) or (chinese+english+maths)>180
14.根据英语成绩字段排序 升序和降序
SELECT * from meite_student ORDER BY english desc
15.根据总分成绩 从高到低排列
SELECT name as 姓名,(chinese+english+maths) as 总分
from meite_student order by (chinese+english+maths) asc
16.统计学生表中 姓名男和女 分表有多少学生
SELECT sex ,count(*) from meite_student GROUP BY sex
练习2
需求:
-- 1. 根据员工的薪资升序或者降序排列
-- 2.根据员工的薪资升序排列,且不查询到财务部门的员工信息
-- 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息,根据薪资升序排列
-- 4.查询每位员工综合年薪 根据年薪降序排列
-- 5.求每个不同部门的平均薪水
-- 6.求每个不同部门的平均薪水且平均薪资大于13000的部门
-- 7.求每个部门中最高薪水
-- 8.求每个部门有多少员工
-- 9.查询员工最高薪资和最低薪资员工信息
-- 10.查询员工最高薪资和最低薪资的差距
初始化sql:
CREATE TABLE `employee` (
`number` int DEFAULT NULL COMMENT '员工编号',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
`hiredate` date DEFAULT NULL COMMENT '入职时间',
`salary` double DEFAULT NULL COMMENT '薪资',
`bonus` double DEFAULT NULL COMMENT '奖金',
`department` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门编号'
)
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1001', '张三丰', '2020-06-01', '18000', '3000', '研发部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1002', '刘军', '2019-06-01', '16000', '1000', '研发部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1003', '张三', '2018-02-10', '6000', '500', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1004', '王麻子', '2022-02-10', '27000', '500', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1005', '刘软', '2013-02-10', '6000', '500', 'UI部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1006', '王麻子', '2022-02-10', '6000', '500', 'UI部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1007', '李四', '2022-02-25', '5000', '100', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1008', '余国军', '2022-02-25', '10000', '50', '研发部门');
-- 1. 根据员工的薪资升序或者降序排列
select * from employee order by salary desc ;
select * from employee order by salary asc ;
-- 2.根据员工的薪资升序排列,且不查询到财务部门的员工信息
select * from employee where department!='财务部门' order by salary asc
-- 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息,根据薪资升序排列
select * from employee where name not like '_麻%' and salary>=15000
order by salary asc ;
-- 4.查询每位员工综合年薪 根据年薪降序排列
select name as 姓名 ,(salary*12)+bonus as 年薪 from employee
order by (salary*12)+bonus desc
-- 5.求每个不同部门的平均薪水
select department,avg(salary) from employee GROUP BY department
-- 6.求每个不同部门的平均薪水且平均薪资大于13000的部门
select department,avg(salary) from employee GROUP BY department
HAVING avg(salary)>13000
-- 7.求每个部门中最高薪水
select max(salary) from employee GROUP BY department
-- 8.求每个部门有多少员工
select department, count(*) from employee GROUP BY department
-- 9.查询员工最高薪资和最低薪资员工信息
select * from employee order by salary desc limit 1
select * from employee order by salary asc limit 1
-- 10.查询员工最高薪资和最低薪资的差距
select max(salary)-min(salary) from employee