三、DQL 查询数据（最重点）

DQL 查询数据（最重点）

DQL

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

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

Select完整语法：

SELECT [ALL | DISTINCT]
{*  |  table.*  |  [table.field1[as alist1][,table.field2[as alist2]][,...]]}
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 * 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 别名

去重

作用：去除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` +1 AS '提分后' FROM result

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量，......

select 表达式 from 表

WHERE条件子句

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

逻辑运算符

尽量使用英文

-- 查询成绩在95-100分之间的
SELECT STUDENTNO,STUDENTRESULT FROM RESULT
-- AND
SELECT STUDENTNO,STUDENTRESULT FROM RESULT WHERE STUDENTRESULT >=95 AND STUDENTRESULT <=100
-- &&
SELECT STUDENTNO,STUDENTRESULT FROM RESULT WHERE STUDENTRESULT >=95 && STUDENTRESULT <=100
-- 模糊查询（区间）BETWEEN
SELECT STUDENTNO,STUDENTRESULT FROM RESULT WHERE STUDENTRESULT BETWEEN 95 AND 100 OR STUDENTRESULT BETWEEN 80 AND 90

-- 除了1000号学生以为的同学的成绩
-- !=
SELECT STUDENTNO,STUDENTRESULT FROM RESULT WHERE STUDENTNO != 1000 and STUDENTNO != 1001
-- NOT
SELECT STUDENTNO,STUDENTRESULT FROM RESULT WHERE NOT STUDENTNO = 1000 AND NOT STUDENTNO = 1001

模糊查询：比较运算符

-- 查询姓刘的同学
-- like结合 % （代表0到任意个字符）_（一个字符）
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 FROM student WHERE ADDRESS IN ('北京')


--====null not null=====
-- 查询地址为空的学生
SELECT studentNO,studentNAME FROM student WHERE ADDRESS = '' OR ADDRESS IS NULL;
-- 查询有生日的同学 不为空
SELECT studentNO,studentNAME FROM student WHERE BORNDATE != '' OR BORNDATE IS NOT NULL;
  

联表查询

JOIN 对比

-- ======联表查询======
SELECT * FROM student
SELECT * FROM RESULT
/* 思路
1. 分析需求，分析查询的字段来自哪些表，（连接查询）
2. 确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中国 studentno = 成绩表 studentno*/
SELECT S.studentNO,studentNAME,SUBJECTNO,studentRESULT 
FROM student AS S
INNER JOIN RESULT AS R
WHERE S.studentNO = R.studentno

-- Right Join
SELECT S.studentNO,studentNAME,SUBJECTNO,studentRESULT 
FROM student AS S
RIGHT JOIN RESULT AS 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

-- 思考题（查询参加了考试的同学信息：学号、姓名、科目名、分数）
/* 思路
1. 分析需求，分析查询的字段来自哪些表，student\result\subject （连接查询）
2. 确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中 studentno = 成绩表 studentno
*/
SELECT s.studentNO,studentNAME,subjectname,studentresult
FROM student AS S
RIGHT JOIN result AS R
ON S.studentno = R.studentno
INNER JOIN `subject` AS sub
ON R.subjectno = sub.subjectno

-- 要查询哪些数据 select ....
-- 从哪几个表中查  from 表  xxx Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢增加

-- From a left join b 主体为 a 表
-- From a RIGHT JOIN b 主体为 b 表

自连接(了解)

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

父类：

子类：

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

-- 查询父子信息
SELECT C1.categoryName AS '父类' , C2.categoryName AS '子类'
FROM category AS C1 , category AS C2
WHERE C1.categoryid = C2.pid

分页和排序

排序

-- 分页 limit  排序 order by 
-- 排序：升序 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 r.subjectNO = sub.subjectNO
WHERE subjectNAME = '高等数学-1'
ORDER BY studentresult DESC

分页

-- 分页，每页显示五条数据
-- 语法：limit 起始值，页面大小
-- limit 0,5  0-5
-- limit 6,5  6-11
-- limit 1,5  1-6

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 DESC
LIMIT 0,5

-- 第一页 limit 0,5  （1-1）*5
-- 第二页 limit 0,5  （2-1）*5
-- 第三页 limit 0,5  （3-1）*5
-- 第N页  limit 0,5  （n-1）* pageSize
-- [pageSize 页面大小]
-- [(n-1)* pageSize 起始值]
-- [n：当前页]
-- [数据总数/页面大小 = 总页数]

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

子查询

where（这个值是计算出来的）

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

-- 连表查询
-- 查询课程为 高等数学-2 且 分数不下于 80 的同学的学号和姓名  
SELECT S.studentno,studentname 
FROM student AS S
INNER JOIN result AS R
ON R.studentno = S.studentno
INNER JOIN subject AS sub
ON R.subjectno = sub.subjectno
WHERE subjectname = '高等数学-2' AND studentresult >= 80

-- 子查询
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT S.studentno,studentname 
FROM student AS S
INNER JOIN result AS R
ON R.studentno = S.studentno
WHERE studentresult >= 80

-- 在这个基础上增加一个科目 高等数学-2
-- 查询高等数学-2 的编号
SELECT DISTINCT S.studentno,studentname 
FROM student AS S
INNER JOIN result AS R
ON R.studentno = S.studentno
WHERE studentresult >= 80 AND subjectno = (
    SELECT subjectno FROM subject
    WHERE subjectname = '高等数学-2'
)
-- 再改造
SELECT studentno,studentname FROM student WHERE studentno IN (
  SELECT studentno FROM result WHERE studentresult >= 80 AND
  subjectno = (
    SELECT subjectno FROM subject 
    WHERE subjectname = '高等数学-2'
  )
)

分组和过滤

-- 查询不同课程的平均分、最高分、最低分、平均分大于80
-- 核心：根据不同的课程分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult),MIN(studentresult) FROM result r
INNER JOIN subject sub ON r.subjectno = sub.subjectno
GROUP BY r.subjectno -- 通过什么字段分组
HAVING 平均分 > 80

select小结