数据库 MySQL 中 DQL 数据库查询语言(特别重要)

DQL(Data Query Language) 为数据查询语言，所有的查询操作都用select，简单的查询，复杂的查询都能做，这也是数据库最核心最重要使用频率最高的语句

首先我们来创建四张表

第一张表： grade

GradeID 为年级编号，设置为自增主键，GradeName 为年级名称

第二张表：result

对应的属性为：学号，课程编号，考试日期，考试成绩

第三张表：student

属性包含学生主要信息

第四张表 subject, 学科表

对应的属性为

1、简单查询

-- 查询全部学生
SELECT* FROM `student`

-- 查询全部成绩
SELECT* FROM `result`

-- 查询学号，学生姓名
SELECT `StudentNo`,`StudentName` FROM `student`;

-- 起别名查询
SELECT `StudentNo` AS '学号',`StudentName` '学生姓名' FROM `student`

-- 查询哪些同学考试了？(全部查询)
SELECT `StudentNo` FROM `result`

-- 查询哪些同学考试了？(去重查询)
SELECT DISTINCT `StudentNo`  FROM `result`

-- 函数  Concat（a,b）,结合 a 列和 b 列
SELECT CONCAT('姓名：',`studentname`) AS '学生信息'FROM student;

2、where 条件字句

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

-- 查询成绩在 95 到 100 之间的学生
SELECT `StudentNo` AS '学号',`StudentResult` AS '学生成绩' FROM `result`
WHERE `StudentResult` >=95 AND `StudentResult`<=100;

-- 模糊查询（区间范围）本质是比较运算符
SELECT `StudentNo` AS '学号',`StudentResult` AS '学生成绩' FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100;

-- 查询除了序号为 1000 号的学生
SELECT `StudentNo` AS '学号',`StudentResult` AS '学生成绩' 
FROM result WHERE `StudentNo` != 1000

-- 查询除了序号为1000号的学生
SELECT `StudentNo` AS '学号',`StudentResult` AS '学生成绩' 
FROM result WHERE NOT `StudentNo`=1000 

--  =========== 模糊查询 ==============
-- 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 '_嘉_';

-- =========== in ============
-- 查询 1001,1002,1003 的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1001,1002,1003);

-- 查询在安徽的学生，in 可以是具体的一个或多值
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('安徽','洛阳','河南')


-- ==========null 和 not null================
-- 插叙地址为null ''的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IS NULL OR `Address`='';

-- 查询有出生日期的学生
SELECT `StudentNo` AS '学生学号',`StudentName` AS '学生姓名' FROM `student`
WHERE `BornDate` IS NOT NULL;

-- 查询没有出生日期的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL

3、联表查询

链表查询是啥？

分为左查询和右查询

-- =========== 连表查询==============
-- 查询参加了考试同学的学号，姓名，科目编号，分数

/*思路:
1. 分析查询的字段来自哪些表， 
2. 确定需要哪种连接查询？确定交叉点，两个表中哪些字段是相同的
3. 由于 result 表中没有姓名，所以需要连接到 student 表中
*/ 
-- inner join
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s 
INNER JOIN `result` AS r
WHERE s.`StudentNo` = r.`StudentNo`;  
-- join on 连接查询,where 等值查询


-- 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`;  


-- 查询缺考的学生
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. 确定需要哪种连接查询？确定交叉点，两个表中哪些字段是相同的
3. 由于 result 表中没有姓名，所以需要连接到 student 表中
*/  

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 字段1,字段2,... 
FROM 要查询的表 AS 表别名1
INNER/LEFT/RIGHT JOIN 要连接的表 AS 表别名2
ON 表别名1.要连接的字段 = 表别名2.要连接的字段
INNER/LEFT/RIGHT JOIN 要连接的表 AS 表别名3
....

-- 查询学员所属年级(学号，学生姓名，年级名称)
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.`GradeID`=g.`GradeID`

-- 查询科目所属的年级(科目名称，年级名称)
SELECT `SubjectName`,`GradeName`
FROM `subject` AS s
INNER JOIN `grade` AS g
ON s.`GradeID`=g.`GradeID`

-- 查询参加了'数据结构'考试同学的：学号，学生姓名，科目名，分数
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
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`='数据库结构-1'

连接查询的区别

• inner join 如果表中至少有一个匹配，就返回行
• left join 返回左表中所有值，即使右表没有匹配
• right join 返回右表中所有值，即使左表没有匹配

4、自连接查询

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

这是一张表

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

拆分为两张表

1、父类表

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

2、子类表

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

最后的结果

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

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

order by 和 group by 区别

• order by 行排序，默认为升序，order by 后面必须列出排序的字段名，可以是多个字段名
• group by 分组的意思，必须有聚合函数来配合使用，使用时至少需要一个分组来标志字段，需要注意 where 后面不能接聚合函数

5、分页和排序

排序 : order by

-- ======== 分页 limiit 和 排序 order by ==========
-- 排序：升序 asc，降序 desc
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
-- LIMIT 5 OFFSET 0
-- 上面这两种写法都一样，表示从第1行数据开始，共显示五条数据
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 5 OFFSET 0


-- 查询java 第一学年课程成绩排名前10的学生，并且要求分数要大于80（学号，姓名，课程名称，成绩）
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第一学年' AND `StudentResult` >80
ORDER BY `StudentResult` DESC
LIMIT 10 OFFSET 0

6、子查询

在 where 语句中嵌套一个子查询语句
where （select * from）

-- 连表查询 C语言-1前5名同学的信息：学号，姓名，分数
SELECT s.`StudentNo`,`StudentName`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo`=r.`StudentNo`
WHERE `SubjectNo`=(
  SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='C语言-1'
)
ORDER BY `StudentResult` DESC
LIMIT 0,5

-- 子查询 C语言-1前5名同学的信息：学号，姓名，分数
SELECT s.`StudentNo`,`StudentName` FROM `student` AS s,`result` WHERE s.`StudentNo` IN(
  SELECT s.`StudentNo` FROM `result` WHERE `SubjectNo`=(
    SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`= 'C语言-1'
  )  
)
ORDER BY `StudentResult` DESC
LIMIT 0,5

7、MySQL 函数

SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9,4) -- 向下取整
SELECT RAND() -- 返回0~1之间的随机数

-- 字符串函数
SELECT CHAR_LENGTH('aadaskjd')
SELECT CONCAT('我','爱','你们')

-- 替换函数
-- 从 1 开始替换2长度的字符
SELECT INSERT('我爱编程',1,2,'我超级爱')

-- '坚持'替换为'努力'
SELECT REPLACE('坚持就能成功','坚持','努力')

-- 获取当前时间
SELECT CURRENT_TIME()

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()


-- =============== 聚合函数 ==============

-- ======= 聚合函数 =============
SELECT COUNT(`StudentNo`) FROM `student` -- 不会查询 null 值

-- 下面的会查询到 null 值
SELECT COUNT(*) FROM `student` 
SELECT COUNT(1) FROM `student` 

SELECT  SUM(`StudentResult`) AS 总和 FROM `result`
SELECT  AVG(`StudentResult`) AS 平均分 FROM `result`
SELECT  MAX(`StudentResult`) AS 最高分 FROM `result`
SELECT  MIN(`StudentResult`) AS 最低分 FROM `result`

-- 查询不同课程的平均分，最高分，最低分
-- 核心：根据不同的课程分组
-- 如果有 where 后面不可以加 group by 
-- 如果想等值查询,可以在 group by 后面加 having
SELECT `SubjectName`,AVG(`StudentResult`)AS 平均分 ,MAX(`StudentResult`)AS 最高分,MIN(`StudentResult`)AS 最低分
FROM `result` AS r
INNER JOIN `subject` AS s
ON r.`SubjectNo` = s.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通过什么字段来分组
HAVING 平均分>80 -- 和 where 字段功能相同

8、select 小结

按照下面的顺序查询，select 有很严格的顺序限制