零基础学JavaWeb开发（三）之 DQL

五、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