MySQL（三）DQL（续）（where子句，联表查询，分页和排序）

DQL（续）

where子句

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，ab都为真则为真，否则为假
or ||	a or b a||b	逻辑或，ab都为假则为假，否则为假
Not ！	not a !a	逻辑非，a为真则假，a为假则真

-- 查询成绩在95到100之间学生的成绩
SELECT `StudentId` , `StudentResult` FROM `result`
WHERE StudentResult>=95 AND StudentResult<=100;
-- and → &&
SELECT `StudentId` , `StudentResult` FROM `result`
WHERE StudentResult>=95 && StudentResult<=100;
-- between and
SELECT `StudentId` , `StudentResult` FROM `result`
WHERE StudentResult BETWEEN 95 AND 100;
-- 除了1000号学生以外的学生的成绩
SELECT `StudentId` , `StudentResult` FROM `result`
WHERE StudentId!=1000;
-- not
SELECT `StudentId` , `StudentResult` FROM `result`
WHERE NOT StudentId=1000;
-- !
SELECT `StudentId` , `StudentResult` FROM `result`
WHERE ! StudentId=1000;

比较运算符（模糊查询）

运算符	语法	说明
IS NULL	a is null	若值为null，则为真
IS NOT NULL	a is not null	若值不为null，则为真
BETEEN AND	a between b and c	若值在ab之间，则为真
LIKE	a like b	SQL匹配，若a匹配b，则为真
IN	a in (a1,a2,a3,a4…)	若a在数组中，则为真

--  =====================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 '赵__'

-- 查询名字中含有“加”的同学
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 `StudentId` , `StudentName` FROM `students`
WHERE Address='' OR Address IS NULL;
-- 查询有生日信息的同学
SELECT `StudentId` , `StudentName` FROM `students`
WHERE BornDate IS NOT NULL AND BornDate !='';

联表查询

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

-- 查询考试记录（学生表）学号姓名（成绩表）科目编号，分数
SELECT * FROM student;
SELECT * FROM result;
/*
1. 分析需求，分析查询的字段来自哪些表（连接查询）
2. 确定使用哪种连接查询 （7种）
3. 确定交叉点（两表间的联系）
在此例中：学生表中学号和成绩表中学号相等为判断条件
*/
-- inner join
SELECT s.studentNO,studentName,SubjectNO,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNO = r.studentNO;

-- rigth join
SELECT studentNO,studentName,SubjectNO,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNO = r.studentNO;

-- left join
SELECT studentNO,studentName,SubjectNO,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNO = r.studentNO;

• 三者的区别：以on为前提

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

-- 查询没有考试记录的同学
SELECT studentNO,studentName,SubjectNO,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNO = r.studentNO
WHERE StudentResult IS NULL

自连接及联表查询

• 思路：一张表当做两张表用
  

例图中所对应的课程中除了有自己的id信息外，还有一个父类id的字段（类似于科技树）
要查询父类子类对应表，即以下结果：

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

就需要用到自连接查询

-- 要查询父类子类对应表
SELECT p.categoryName AS 父类, s.categoryname AS 子类
FROM category AS s,category AS p
WHERE s.pid=p.categoryid;

-- 用联表查询也可
-- 由于有课程不存在父类也有课程不存在子类
-- 为了不出现null的查询结果 使用inner
SELECT p.categoryName AS 父类, s.categoryname AS 子类
FROM category AS s
INNER JOIN category AS p
ON s.pid=p.categoryid;

分页和排序

• 排序：ORDER BY 升序ASC 降序DESC

-- ========排序 order by==========
-- 排序：升序、降序
-- order by 字段 升/降序：以规定字段的规定方式对查询结果排序
SELECT * FROM student
ORDER BY studentNo ASC;-- 学号升序排列学生表
-- 降序
SELECT * FROM student
ORDER BY studentNo DESC;-- 学号升序排列学生表

• 分页
  只返回一部分数据，能够缓解数据库压力

-- =======分页 limit=========
-- limit 起始值，页面大小（显示几条数据）
-- limit 0,5 ————1~5条数据
-- limit 1,5 ————2~6条数据
-- limit 6,5 ————7~11条数据
SELECT * FROM students
LIMIT 0,22;
-- 若一页设置为n条数据
-- 第一页 limit 0,n
-- 第二页 limit n,2n
-- 第三页 limit 2n,3n
-- 第i页  limit (i-1)n,in