DQL

（Data Query LANGUAGE :数据查询语言）

所有的查询操作都用它 select
简单的查询，复杂的查询都能做
==数据库中最核心的语言，最重要的语句==
使用频率最高的语句

Select常用语法：

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  #联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]   -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
  	-- 指定查询的记录从哪条至哪条

==注意==：[] 代表可选，{}代表必选

Select 完整的语法：

SELECT
	[ ALL | DISTINCT | DISTINCTROW ]
		[ HIGH_PRIORITY ]
		[ STRAIGHT_J0IN ]
		[ SQL_SMALL_RESULT ] [ SQL_BIG_RESULT] [ SQL_BUFFER_RESULT]
		[ SQL_CACHE | SQL_NO_CACHE] [ SQL_CALC_FOUND_ROWS]
	select_expr [, select_expr...]
	[ FRoM table_references
		[ PARTITION partition_list]
	[ WHERE where_condition ]
    [ GROUP BY { col_name | expr | position}
		[ ASC | DESC],...[ WITH ROLLUP]]
	[ HAVING where_condition]
	[ ORDER BY { col_name | expr | position}
     	[ ASC | DESC ],..]
	[ LIMIT { [offset,] row_count | row_count OFFSET offset}]
	[ PROCEDURE procedure_name(argument_1ist)]
    [INTO OUTFILE 'file_name'
		[CHARACTER SET charset_name]
		export_options 
     	| INTO DUMPFILE 'file_name'
		| INTO var_name [,var_name]]
	[FOR UPDATE | LOCK IN SHARE MODE]]

测试素材

https://gist.github.com/HuBoZhi/eee710db39b3f1745a3818fddc235f9b

或者https://gitcode.net/snippets/1690

指定查询字段

-- 查询全部学生  select 字段 from 表
select * from `student`;

-- 查询指定字段
select `StudentNo`,`StudentName` from `student`;

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
select `StudentNo` as 学号,`StudentName` as 学生姓名 from `student` AS s;

-- 函数 CONCAT(a,b)
select concat('姓名：',StudentName) as 新名字 from `student`;

语法：select 字段,... from 表

有的时候，列的名字不是那么的见名知意。所以我们起别名 AS: 字段名 as 别名表名 as 别名

去重 distinct

作用：去除 Select 查询出来的结果中重复的数据，只显示一条

-- 查询一下哪些同学参加了考试  成绩
select * from result; --  全部的成绩
select `StudentNo` from result; -- 查看有哪些同学参加了考试
select distinct `StudentNo` from result; -- 去除重复数据，去重

数据库的列（表达式）

select version();  -- 查看系统版本（函数）
select 100*3-1 as 计算结果;  -- 用来计算（表达式）
select @@auto_increment_increment  -- 查询自增的步长

-- 学员考试成绩 +1 分查看
select `StudentNo`,`StudentResult` as 原来得分,`StudentResult`+1 as 提分后 from result;

where 条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成！结果布尔值。

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or\|\|	a or b a\|\| b	逻辑或
Not !	not a !a	逻辑非

-- ===================== where ===========================
select `StudentNo`,`StudentName` from `student`;

-- 查询考试成绩在 95~100 分之间
select `StudentNo`,`StudentResult` 
from `result`
where `StudentResult`>=95 and `StudentResult`<=100;

-- and &&
select `StudentNo`,`StudentResult` 
from `result`
where `StudentResult`>=95 && `StudentResult`<=100;


-- 模糊查询（区间）
select `StudentNo`,`StudentResult`
from `result`
where StudentResult between 95 and 100;

-- 除了1000学号以外的同学的成绩
-- !=
select `StudentNo`,`StudentResult`
from `result`
where `StudentNo`!=1000;

-- <>
select `StudentNo`,`StudentResult`
from `result`
where `StudentNo`<>1000;

-- not 
select `StudentNo`,`StudentResult`
from `result`
where not `StudentNo`=1000;

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为NULL，结果为真
BETWEEN	a between b and c	若a在b和c之间，结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in (a1,a2,a3)	假设a在a1,a2,…,其中的某一个值中，结果为真

-- ================= 模糊查询 ==================
-- 查询姓刘的同学
select `StudentNo`,`StudentName` 
from `student`
where `StudentName` like '刘%';

-- 查询姓刘的同学，并且后面只有一个字的
select `StudentNo`,`StudentName` 
from `student`
where `StudentName` like '刘_';

-- 查询姓刘的同学，后面有两个字的
select `StudentNo`,`StudentName`
from `student`
where `StudentName` like '刘__';


-- 查询名字中间有嘉字的
select `StudentNo`,`StudentName` 
from `student`
where `StudentName` like '%嘉%';

-- ======================= in =========================
-- 查询 1001,1002,1003 号学员
select `StudentNo`,`StudentName` 
from `student`
where `StudentNo` in (1001,1002,1003);

-- 查询在安徽的学生
select `StudentNo`,`StudentName` ,`Address`
from `student`
where `Address` in ('安徽','河南洛阳');

-- ================ null   not null ================
-- 查询性别为空的学生
select `StudentNo`,`StudentName` ,`sex`
from `student`
where `sex` is null;

-- 查询有出生日期的学生， 不为空
select `StudentNo`,`StudentName` ,`BornDate`
from `student`
where `BornDate` is not null;

-- 查询没有出生日期的学生
select `StudentNo`,`StudentName` ,`BornDate`
from `student`
where `BornDate` is null;

-- 查询学员所属的年级
select StudentNo,`StudentName`,`GradeName`
from `student` as s
inner join `grade` as g
on s.GradeId = g.GradeID;


-- 查询科目所属的年级
select SubjectName,`GradeName`
from `subject` as `sub`
inner join `grade` as g
on sub.GradeID = g.GradeID;


-- 查询参加了 数据库结构-1 考试的同学的信息：学号，学生姓名，分数
select s.StudentNo,StudentName,StudentResult
from `student` as s
inner join `result` as r
on s.StudentNo = r.StudentNo
inner join `subject` as `sub`
on r.SubjectNo = sub.SubjectNo
where SubjectName='数据库结构-1';

连接查询

在这里插入图片描述

/* 思路
1. 分析需求，分析查询的字段来自哪些表，（连接查询）
2. 确定使用那种连接：7种
确定交叉点，这两个表中那个字段是相同的
判断的条件：学生表中  StudentNo  =  成绩表中  StudentNo
*/

-- join on 连接查询
-- where 等值查询

select s.StudentNo,StudentName,SubjectNo,StudentResult
from student as s inner join result as r
on s.StudentNo = r.StudentNo;


-- Right Join
-- as 可以省略
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student s right join result r
on s.StudentNo = r.StudentNo;

-- Left Join
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student as s left join result as r
on s.StudentNo = r.StudentNo;

操作	描述
`Inner Join`	如果表中至少一个匹配，就返回该行
`Left Join`	会从做左表中返回所有的值，即使右表中没有匹配
`Right Join`	会从做右表中返回所有的值，即使左表中没有匹配

-- 查询缺考的同学
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student as s left join result as r
on s.StudentNo = r.StudentNo
where StudentResult is null;

-- 思考题（查询参加考试的同学的信息：学号，学生姓名，科目名，分数）
/* 思路
1. 分析需求，分析查询的字段来自哪些表，（连接查询）
2. 确定使用那种连接：7种
确定交叉点，这两个表中那个字段是相同的
判断的条件：学生表中  StudentNo  =  成绩表中  StudentNo
*/
select s.StudentNo,StudentName,SubjectName,StudentResult
from student as s 
right join result as r 
on s.StudentNo = r.StudentNo
left join `subject` as sub
on sub.SubjectNo = r.SubjectNo;

-- 我要查询哪些数据 select...
-- 从那几个表中查 from 表  XXX join 连接的表  on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表

-- from a left join b
-- from a right join b

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父表：

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子表：

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	PS技术

分页和排序

排序

-- 排序：升序：ASC   降序：DESC
-- ORDER BY  [通过哪个字段排] [怎么排：升序/降序]
-- 查询结果的根据 成绩降序 排序
select s.StudentNo,StudentName,SubjectName,StudentResult
from student as s
inner join result as r
on s.StudentNo = r.StudentNo
inner join `subject` as sub
on sub.SubjectNo = r.SubjectNo
where SubjectName = 'JAVA第一学年'
order by StudentResult DESC;

分页

-- 100万条数据
-- 为什么要分页
-- 缓解数据库压力，给人更好的体验，瀑布流和分页不同
-- 
-- 分页 每页只显示五条数据
-- 语法： limit 起始值，页面大小
-- 网页应用：当前，总的页数，页面大小
-- LIMIT 0,5  1~5
-- LIMIT 1,5  2~6
-- LIMIT 6,5  
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from student as s
inner join result as r
on s.StudentNo = r.StudentNo
inner join `subject` as sub
on r.SubjectNo = sub.SubjectNo
where SubjectName='数据库结构-1'
order by StudentResult ASC
limit 1,5;

-- 第一页 limit 0,5   (1-1)*5
-- 第一页 limit 6,5   (2-1)*5
-- 第一页 limit 10,5   (3-1)*5
-- 第N页 limit 0,5    (n-1)*pageSize,pageSize
-- 【pageSize】：页面大小
-- 【(n-1)*pageSize】：起始值
-- 【n】：当前页
-- 【数据总数/页面大小】：总页数

语法：limit 查询的起始下标, pageSize

子查询

where (这个值是计算出来的)

本质：在 where 语句中嵌套一个子查询语句

-- 思考：
-- 查询JAVA第一学年 课程成绩排名前十的学生，并且分数要大于八十的学生信息（学号，姓名，课程名称，分数）
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from `student` as s
inner join `result` as r
on s.StudentNo = r.StudentNo
inner join `subject` as sub
on r.SubjectNo = sub.SubjectNo
where SubjectName='JAVA第一学年' and StudentResult>=80
order by StudentResult ASC;


-- 查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
select StudentNo,sub.SubjectNo,StudentResult
from`result` as r
inner join `subject` as sub
on sub.SubjectNo = r.SubjectNo
where SubjectName='数据库结构-1'
order by StudentResult DESC;


-- 方式二：使用子查询（由里及外）
select StudentNo,SubjectNo,StudentResult
from `result`
where SubjectNo in (
		select `SubjectNo`
		from `subject`
		where SubjectName='数据库结构-1'
	)
	order by StudentResult DESC;



-- 查询课程为 高等数学-2 且分数不小于80的同学的学号和姓名
select s.StudentNo,StudentName
from `student` as s
inner join `result` as r
on s.StudentNo = r.StudentNo
inner join `subject` as sub
on r.SubjectNo = sub.SubjectNo
where `StudentResult`>=80 and `SubjectName`='高等数学-2';

-- 分数不小于80的同学的学号和姓名
select `StudentNo`,`StudentName`
from `student`
where `StudentNo` in (
	select `StudentNo`
	from `result`
	where `StudentResult`>=80
);


-- 在这个基础上增加一个科目，高等数学-2
-- 查询 高等数学-2 的编号 
select `SubjectNo`
from `subject`
where `SubjectName`='高等数学-2';

-- 综上改造
select `StudentNo`,`StudentName` from `student`
where `StudentNo` in (
	select `StudentNo` from `result` where `StudentResult`>=80 and `SubjectNo`in (
		select `SubjectNo` from `subject` where `SubjectName`='高等数学-2'
	)
);




-- 练习： 查询C语言-1前五名同学的成绩的信息（学号，姓名，成绩）
-- 使用子查询
select s.`StudentNo`,`StudentName`,`StudentResult`
from `student` as s
inner join `result` as r
where s.`StudentNo` in (
	select `StudentNo` from `result` where `SubjectNo` in (
			select `SubjectNo` from `subject` where `SubjectName`='C语言-1'
	)
	order by `StudentResult` ASC
)
limit 5;

分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
select SubjectName,AVG(`StudentResult`) as 平均分,MAX(`StudentResult`) as 最大值,MIN(`StudentResult`) as 最小值
from `result` as r
inner join `subject` sub
on r.SubjectNo = sub.SubjectNo
GROUP BY r.`SubjectNo`  -- 通过什么字段来分组
having 平均分>=80;