6-MySQL-DQL语言-WHERE子句、模糊查询、联表查询、自连接

MySQL -> DQL查询数据 -> WHERE子句、模糊查询、联表查询、自连接

1. WHERE条件子句

• 作用：检索数据中符合条件的值
• 逻辑运算符：

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

SELECT `StudentNo`,`StudentResult` FROM result

-- 查询考试成绩在85~90之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=85 AND `StudentResult`<=90

-- 模糊查询（区间）
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult` BETWEEN 80 AND 90

-- 除了1001学生之外的学生成绩
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo` != 1001

SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo` = 1001

2. 模糊查询

• 本质就是比较运算符

  运算符	语法	描述
  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…）	假设a在a1、a2…中，则结果为真

-- 模糊查询Like
-- 查询名字以A开头的同学，%代表0到任意个字符，_代表一个字符
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'A%'

SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'A__'

-- 查询名字中有o的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%o%'

-- 模糊查询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` IS NOT NULL

3. 联表查询

• JOIN 对比
• LEFT JOIN 、 INNER JOIN 、 RIGHT JOIN

操作	描述
INNER JOIN	如果表中至少有一个匹配，就返回行
LEFT JOIN	会从左表中返回所有的值，即使右表中没有匹配
RIGHT JOIN	会从右表中返回所有的值，即使左表中没有匹配

• JOIN的7大理论
  

-- 联表查询
-- 查询参加了考试得同学（学号，姓名，科目编号，分数）

/*
1.分析需求，分析查询得字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
3.确定交叉点（这两个表种哪个字段是相同的）
判断条件：student表中的 StudentNo = result表中的 StudentNo
*/

-- JOIN（连接的表） ON（判断的条件）  连接查询
-- WHERE  等值查询

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 s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo

-- LEFT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo

-- 查询缺考的同学
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo
WHERE StudentResult IS NULL

-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名称，分数）
/*
1.分析需求，分析查询得字段来自哪些表，student，result，subject（连接查询）
2.确定使用哪种连接查询？7种
3.确定交叉点（这两个表种哪个字段是相同的）
*/
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 `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

-- 查询参加了 高等数学-3 考试的同学信息：学号，学生姓名，科目名称，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
RIGHT JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等数学-3'

4. 自连接

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

categoryid	pid	categoryName
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开发
5	7	PS技术
2	8	办公信息

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`)
VALUE('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','Web开发'),
('7','5','PS技术'),
('8','2','办公信息');

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