4 DQL查询数据

4 DQL查询数据

4.1 DQL

（Data Query Language）

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

4.2 指定查询字段

-- 查询全部学生  SELECT 字段 FROM 表
SELECT * FROM student

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给结果起一个名字 AS   可以给字段取别名，也可以给表取别名
SELECT `studentno`AS 学号,`studentname`AS 学生姓名 FROM student

-- 函数  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`+1 AS '提分后' FROM result

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

select 表达式from 表

4.3 where条件子句

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

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

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or ||	a or b a||b	逻辑或，其中一个为真，则结果为真
Not ！	not a ! a	逻辑非，真为假，假为真！

尽量使用英文字母

-- 查询考试成绩在95-100之间的
SELECT `studentno`,`studentresult` FROM result WHERE studentresult>=95 AND studentresult<=100   -- 用 逻辑符号 和 AND（&&） 实现
SELECT `studentno`,`studentresult` FROM result WHERE studentresult BETWEEN 95 AND 100   -- 使用between and实现

-- 除了1000号学生以外的同学的成绩
SELECT `studentno`,`studentresult` FROM result WHERE studentno != 1000
SELECT `studentno`,`studentresult` FROM result WHERE NOT studentno = 1000

模糊查询：比较运算符

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

-- ====================== 模糊查询 =========================
-- 查询姓张的同学
-- 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==========
-- 查询地址为空的学生 null
SELECT `studentno`,`studentname`FROM student WHERE address IS  NULL

-- 查询出生日期为空的学生 null
SELECT `studentno`,`studentname`FROM student WHERE borndate IS NULL

-- 查询出生日期不为空的学生 not null
SELECT `studentno`,`studentname`FROM student WHERE borndate IS NOT NULL

4.4 联表查询

JION 对比

-- ==========联表查询==========

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

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

-- join (连接的表) on （判断的条件）   连接查询
-- where    等值查询

-- inner join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
INNER JOIN result r
WHERE s.studentno = r.studentno

-- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno

-- right join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

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

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

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

自连接

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

父类

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

子类

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

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

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

-- ================= 自连接 ==================
-- 查询父子信息:把一个表看为两个一模一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

/* 思路
1.分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用的哪种连接查询（7种）
3.确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 StudentNO = 成绩表中的StudentNO
*/
-- 查询学院所属的年级 （学号，学生的姓名，年纪名称）
/*
1. student,grade
2. left join
3. gradeid
*/
SELECT `studentno`,`studentname`,`gradename`
FROM `student` s
INNER JOIN `grade` g
ON s.`gradeid` = g.`gradeid`

-- 查询科目所述的年级（科目名称，年级名称）
SELECT `subjectname`,`gradename`
FROM `subject` s
INNER JOIN `grade` g
ON s.`gradeid` = g.`gradeid`

-- 查询了参加数据库结构-1 考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'

4.5 分页和排序

排序

-- 排序：升序ASC  降序DESC
-- order by 通过哪个字段排序，怎么排
-- 查询的结果根据 成绩降序/升序 排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY studentresult DESC

分页

-- 分页
-- 原因：缓解数据库压力，给人更好的体验  瀑布流
-- 语法： limit 起始值，页面的大小
-- 网页应用： 当前，总的页数，页面的大小
-- limit 0,5    1~5
-- limit 1,5    2-6
-- limit 2,5    3-7
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY studentresult DESC
LIMIT 0,5

-- 查询 Java第一学年 课程成绩排名前十的学生，并且分数要大于 80 的学生信息（学号，姓名，课程名称，分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = 'Java第一学年' AND `studentresult`>=80
ORDER BY studentresult DESC
LIMIT 0,10

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

4.6 子查询

-- ==================== 子查询 ====================
-- 1.查询 数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE sub.subjectname = '数据库结构-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
WHERE `subjectno`(
SELECT `subjectno` FROM `subject` WHERE `subjectname` = '数据库结构-1'
)

-- 查询课程为 高等数学-2 且分数不小于 80 的学生的学号和姓名
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学' AND `studentresult` >=80


-- 查询课程为 高等数学-2 且分数不小于80的同学的学号和姓名

SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.`studentno` = s.`studentno`
WHERE `studentresult`>=80 AND subjectno = (
SELECT subjectno FROM `subject` WHERE `subjectname` = '数据库结构-1'
)

-- 在改造（由里及外） 
SELECT `studentno`,`studentname` FROM student WHERE studentno IN(
	SELECT studentno FROM result WHERE studentresult >=80 AND subjectno = (
		SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
	)
)

4.7 分组和过滤

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