MySQL中DQL查询数据——（四）

MySQL中DQL查询数据——（四）

本篇博客，是个人根据 西部开源-秦疆老师的教学视频整理出的笔记，想看最详细的教学笔记和提供的SQL语句素材，请点击如下链接:
https://www.cnblogs.com/hellokuangshen/p/10252711.html

6.DQL语言(重点)

6.1DQL( Data Query Language 数据查询语言 )

• 查询数据库数据 , 如SELECT语句
• 简单的单表查询或多表的复杂查询和嵌套查询
• 是数据库语言中最核心,最重要的语句
• 使用频率最高的语句

6.2指定查询字段

注意：查询时，对表的字段的大小写不敏感

-- 查询全部的信息
-- 格式：
SELECT * FROM 表名;
-- 查询全部的学生
SELECT * FROM student;

-- 查询指定字段
-- 格式：
SELECT `字敦1`[,`字段2`,`字段3,.....`] FROM student;
SELECT `StudentNo`,`StudentName` FROM student;

-- AS子句，可以给字段取别名，也可以给表取别名
SELECT `StudentNo` AS 学号,`StudentName` AS 姓名 FROM student AS 新表;

-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',`StudentName`) AS 姓名字  FROM `student`;

6.3去重

DISTINCT命令

• 作用：去掉SELECT查询出的结果中重复的数据，重复的数据只显示一条

-- 要求查看哪些同学参加了考试(学号)--去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT StudentNo FROM result; -- 查看哪些同学参加了考试
SELECT DISTINCT StudentNo FROM result;
-- 了解:DISTINCT 去除重复项 (默认是ALL)

6.4数据库的列（表达式）

SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT StudentNo,StudentResult+1 AS '提分后' FROM result;

数据库中的表达式：可以是文本值、列值、NULL、函数、计算表达式、系统变量

SELECT 表达式 FROM 表；

6.5where条件子句

• 作用：检索数据中符合条件的值
• 搜索条件可由一个或多个逻辑表达式组成 , 结果都是布尔值（为真或为假）.

逻辑运算符

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

-- 满足条件的查询(where)
SELECT Studentno,StudentResult FROM result;
-- 查询考试成绩在95-100之间的
SELECT Studentno,StudentResult FROM result 
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND &&（推荐使用AND）
SELECT studentno,studentresult FROM result 
WHERE StudentResult>=95 AND StudentResult<=100;
SELECT Studentno,StudentResult FROM result
WHERE StudentResult>=95 && StudentResult<=100;
-- 模糊查询
SELECT Studentno,StudentResult FROM result
WHERE StudentResult BETWEEN 95 AND 100;

-- 除了1000号同学之外查看其他同学的成绩
-- NOT和!=
SELECT studentno,studentresult FROM result
WHERE studentno!=1000;
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,a3…中的其中一个，结果为真

----------------------模糊查询--------------------
-- like--
-- 查询姓刘的同学的姓名
-- 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--
-- 查询学号为1000,1001,1002对应的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在安徽、武汉加油、天津市和平区
SELECT studentno,studentname FROM student 
WHERE Address IN ('安徽','武汉加油','天津市和平区');
-- NULL 和 NOT NULL--
-- 查询出生日期没有填写的同学
-- 避免直接写=NULL , 这是错误的写法 , 用 is null 
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学(注意：空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

6.6联表查询

• inner join(等值连接)：只返回两个表中联结字段相等的行。
• left join(左联接)：返回包括左表中的所有记录和右表中联结字段相等的记录。
• right join(右联接)：返回包括右表中的所有记录和左表中联结字段相等的记录

-- 联表查询--
-- 查询参加了考试的学生（学号，姓名，科目编号，分数）
/* 思路：
1、分析需求，分析查询的字段来自哪张表，student表,result表（连接查询）
2、确定使用哪些连接查询？7种
3、确定交叉点（这两个表中哪个数据是相同的，起连接作用）
*/
SELECT * FROM student;
SELECT * FROM result;
-- INNER JOIN--
SELECT student.studentno,studentname,`subjectno`,studentresult 
FROM student 
INNER JOIN result
ON student.StudentNo = result.StudentNo;

-- RIGHT JOIN--
-- 查询出参加考试的学生，未参加考试的学生不会查出
SELECT student.studentno,studentname,`subjectno`,studentresult 
FROM student 
RIGHT JOIN result
ON student.StudentNo = result.StudentNo;

-- LEFT JOIN--
-- 查出需要考试的全部学生，不管有没有参加考试
SELECT student.studentno,studentname,`subjectno`,studentresult 
FROM student 
LEFT JOIN result
ON student.StudentNo = result.StudentNo;

总结：

操作	描述
inner join	如果表中至少有一个匹配，就放回行（只返回两个表中联结字段相等的行）
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- join on 和 where 效果一样
-- 连接查询
SELECT student.studentno,studentname,`subjectno`,studentresult 
FROM student 
INNER JOIN result
ON student.StudentNo = result.StudentNo;

-- 等值查询
SELECT student.studentno,studentname,`subjectno`,studentresult 
FROM student 
INNER JOIN result
where student.StudentNo = result.StudentNo;

注意：

• join on ：连接查询
  • 语法格式：join 表名 on(条件判断)
• where ： 等值查询

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
/* 思路：
1、分析需求，分析查询的字段来自哪张表，student表,result表,subject表（连接查询）
2、确定使用哪些连接查询？7种
3、确定交叉点（这两个表中哪个数据是相同的，起连接作用）
*/
SELECT s.studentno,studentname,SubjectName,studentresult 
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo=r.StudentNo
INNER JOIN `subject` AS sb
ON sb.SubjectNo=r.SubjectNo

6.7自连接

-- 创建一张category表
CREATE TABLE `category` (
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`) 
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

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

父类表：

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

子类表：

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

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

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

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

6.8分页和排序

i.排序

• 升序：ASC
• 降序：DESC、
• ORDER BY 字段名 [ASC|DESC]：指根据哪个字段来进行排序（降序或升序）

-- 查询了参加数据库-1考试的学生的信息（学号，姓名，科目名称，分数）并进行倒叙排
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` u
ON r.subjectno=u.Subjectno
WHERE `subjectname` = '数据库结构-1'
ORDER BY studentresult DESC;

ii.分页

-- 分页，每页只显示5条数据
-- 语法：LIMIT 起始值,页面能显示的数据量
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` u
ON r.subjectno=u.Subjectno
WHERE `subjectname` = '数据库结构-1'
ORDER BY studentresult DESC
LIMIT 5,5;

分页规律：
第一页：limit 0,5————（1-1）*5
第二页：limit 5,5————（2-1）*5
第三页：limit 10,5————（3-1）*5
第n页： limit x,pageSize————（n-1）*pageSize
pageSize:页面大小
n:当前页
x=（n-1）*pageSize:起始值
数据总数/页面大小=总页面

6.9子查询和嵌套查询

-- 查询 数据库结构-1 的所有考试结果(学号,科目名称,成绩),并且成绩降序排列
-- 方式一：联表查询
SELECT studentno,r.subjectno,studentResult
FROM result r 
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方式二：子查询（执行顺序：由里及外）
SELECT studentno,subjectno,studentResult
FROM result
WHERE subjectno=(
    SELECT subjectno FROM `subject` 
    WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

-- 查询课程为 高等数学-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 studentresult >=80 AND `subjectname`='高等数学-2'

-- 方法二:子查询
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
    SELECT subjectno FROM `subject`
    WHERE subjectname = '高等数学-2'
)

-- 方法三:嵌套查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
    SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
        SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
    )
)

6.10分组和过滤

• group by 表的字段名 ：指根据表的哪个字段进行分组
• having :分组后需要进行过滤使用
• where写在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;

总结：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}];
    -- 指定查询的记录从哪条至哪条

注意 : [ ] 括号代表可选的 , { }括号代表必须得