SQLyog基本操作（五）-where条件子句、连接查询、自连接查询
4.2.2 去重 distinct
作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条
-- 查询一下有哪些同学参加了考试
-- 查询学生的全部考试信息，显示result表的所有数据
SELECT * FROM `result`
-- 通过学号查询哪些同学参加了考试，`StudentNo`表示学号
SELECT `StudentNo` FROM `result`
-- 去除重复的学号信息，重复的数据（此处指学号信息）只显示一条
SELECT DISTINCT `StudentNo` FROM `result`
4.2.3 数据库的列（表达式）
数据库中的表达式：包括文本值、列、null、函数、计算表达式、系统变量等。
格式：SELECT 表达式 FROM 表
查看MySQL技术文档：
5.7版本：MySQL :: MySQL 5.7 Reference Manual :: 12.1 Built-In Function and Operator Reference
https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
8.0版本：MySQL :: MySQL 8.0 Reference Manual :: 12.1 Built-In Function and Operator Reference
https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html
-- 通过函数查看系统版本
SELECT VERSION(); -- 5.7.19
-- 用数学表达式来计算结果，将结果保存为“计算结果”
SELECT 100*3-1 AS '计算结果' -- 299
-- 查询自增的步长（变量）
SELECT @@auto_increment_increment -- 1
-- 对数据进行某种整体运算，例：将学员考试成绩+1后输出
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM `result`
4.3 where条件字句
作用：检索数据中符合条件的值，搜索的条件由一个或多个表达式组成，结果是布尔值。
常用的逻辑运算符：建议使用英文字母方式，见名知意！！！
| 运算符 | 语法 | 描述 |
|---|---|---|
| and 或 && | a and b 或 a&&b | 逻辑与，两个都为真，结果为真 |
| or 或 || | a or b 或 a||b | 逻辑或，其中一个为真，结果为真 |
| not 或 ！ | not a 或 !a | 逻辑非，非真则假，非假则真 |
-- 练习
-- 使用and查询成绩在95~100之间的学员
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=95 AND `StudentResult`<=100
-- 使用&&查询成绩在95~100之间的学员
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=95 AND `StudentResult`<=100
-- 使用or查看成绩为60或95的学员
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`=60 OR `StudentResult`=95
-- 使用||查看成绩为60或95的学员
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`=60 || `StudentResult`=95
-- 使用not查看学号不是10000的学员
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE NOT `StudentNo`=10000
-- 使用!查看学号不是10000的学员
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentNo`!=10000
-- 使用between... and...查看成绩在90~100之间的学员
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult` BETWEEN 90 AND 100
-
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a IS NULL | 如果操作符为NULL，结果为真 |
| IS NOT NULL | a IS NOT NULL | 如果操作符不为NULL，结果为真 |
| BETWEEN... AND ... | a BETWEEN b AND c | 如果a在b和c之间，结果为真 |
| LIKE | a LIKE b | SQL匹配，如果a匹配b，结果为真 |
| IN | a IN(a1,a2,a3,...) | 如果a为a1，a2...其中的某一个，结果为真 |
-
LIKE练习：模糊查询
-- LIKE练习：%代表0~任意多个字符 _代表1个字符
-- 查询姓李的同学：第一个字符为刘，后面的字符任意
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练习：精确查询
-- IN：条件为具体的一个或多个值，并非模糊匹配，而是具体匹配
-- 查询10000、10001、10002号学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentNo` IN(10000,10001,10002)
-- 查询在地址不详的学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('地址不详');
-- 查询在地址不详和北京市东城区的学员
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
4.4 联表查询 join
我们在做查询的时候，会遇到可能一张表满足不了我们要求的情况，可能要求获取的数据来自于两张、三张甚至更多张表，对此，我们就应该使用联表查询。
-
3种JOIN对比：
-
实际JOIN理论细分可分为7种：
-- 练习：查询参加了考试的同学，输出学生信息：学号、姓名、科目编号、分数
-- 查询所有学生信息
SELECT * FROM `student`
-- 查询所有成绩信息
SELECT * FROM `result`
/*
根据上面两条语句查询出来的效果，我们还需要一个一个对照着学生的学号去找，这种方式过于麻烦，因此我们需要用一种方式将这些数据拼接起来，方便我们查看。
思路：
1.分析需求：分析查询的字段来自那些表？student、result
2.连接方式：确定使用哪种连接连接查询？7种
确定交叉点：这两个表中哪个数据是相同的
*/
-- join on 连接查询（判断的条件）
-- where 等值查询
-- 分析两张表结构，我们可以判断的条件为：student表中的`StudentNo`=result表中的`StudentNo`
-- 1.使用INNER JOIN：内连接，也称为自然连接
-- 注意: 内连接是从结果中删除其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。
-- 重点：内连接，只查匹配行。
-- 语法：INNER可省略，单独写JOIN时，默认INNER JOIN
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS `s`
INNER JOIN `result` AS `r`
WHERE s.`StudentNo`=r.`StudentNo`
输出结果：保留两个表中都有匹配的行
-- 外连接：与内连接相比，即使没有匹配行，也会返回一个表的全集。
-- 外连接分为三种：左外连接，右外连接，全外连接，对应SQL：LEFT/RIGHT/FULL OUTER JOIN。
-- 通常我们省略outer 这个关键字，写成：LEFT/RIGHT/FULL JOIN。
-- 重点：至少有一方保留全集，没有匹配行用NULL代替。
-- 1.LEFT OUTER JOIN，简称LEFT JOIN，左外连接（左连接）
-- 结果集保留左表的所有行，但只包含第二个表与第一表匹配的行，第二个表相应的空行被放入NULL值。
-- 通过结果，可以看到左连接包含了第一张表的所有信息，在第二张表中如果没有匹配项，则用NULL代替
-- 2.RIGHT OUTER JOIN，简称RIGHT JOIN，右外连接(右连接)
-- 右外连接保留了第二个表的所有行，但只包含第一个表与第二个表匹配的行，第一个表相应空行被入NULL值。
-- 通过结果，可以看到右连接包含了第二张表的所有信息，在第一张表中如果没有匹配项，则用NULL代替
-- 3.FULL OUTER JOIN，简称FULL JOIN，，全外连接（全连接）
-- 全外连接，简称：全连接，会把两个表所有的行都显示在结果表中
-- 包含了两张表的所有记录，没有记录丢失，没有匹配的行用NULL代替。
--
-- 2.使用RIGHT JOIN 侧重于result
SELECT * FROM `student`
SELECT * FROM `result`
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS `s`
RIGHT JOIN `result` AS `r`
ON s.`StudentNo`=r.`StudentNo` -- 此处使用ON
参考链接：
-
SQL的连表查询 详细s562872451的博客-CSDN博客连表查询 https://blog.csdn.net/s562872451/article/details/80474726
-
SQL多表连接查询（详细实例） - 博雅源 - 博客园 https://www.cnblogs.com/wgphp/p/8183812.html
输出结果：输出右表的所有信息，左表中没有匹配的行信息用NULL填充
-- 3.使用LEFT JOIN 侧重于student
SELECT * FROM `student`
SELECT * FROM `result`
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS `s`
LEFT JOIN `result` AS `r`
ON s.`StudentNo`=r.`StudentNo` -- 此处使用ON
输出结果：输出左表的所有信息，右表中没有匹配的行信息用NULL填充
JOIN ON 连接查询 --- ON在连接查询中均适用
WHERE 等值查询 --- 在INNER JOIN可以使用
-
INNER JOIN、LEFT JOIN、RIGHT JOIN结果对比：
| 操作 | 描述 |
|---|---|
| INNER JOIN | 如果表中至少有一个匹配，就返回；如果两张表都有，就确定是哪张表即可 |
| LEFT JOIN | 会从左表中返回所有的值，即使右表中没有匹配 |
| RIGHT JOIN | 会从右表中返回所有的值，即使左表中没有匹配 |
-- 查询缺考的同学：侧重于找出成绩为NULL的同学，故使用LEFT JOIN
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种
确定交叉点：这两个表中哪个数据是相同的
判断的条件：`student`表中的`StudentNo`=`result`表中的`StudentNo`
`result`表中的`SubjectNo`=`subject`表中的`SubjectNo`
*/
-- 先连接查询student表、result表，用StudentNo进行连接
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
RIGHT JOIN `result` r -- 侧重于参加考试同学的成绩分数，故使用RIGHT JOIN
ON s.`StudentNo`=r.`StudentNo`
-- 再连接查询result表、subject表，用SubjectNo进行连接
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
-- 或
-- 先连接查询student表、result表，用StudentNo进行连接
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
RIGHT JOIN `result` r -- 侧重于参加考试同学的成绩分数，故使用RIGHT JOIN
ON s.`StudentNo`=r.`StudentNo`
-- 再连接查询result表、subject表，用SubjectNo进行连接
LEFT JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
输出结果：
总结：
-
我要查询哪些数据？ select...
-
从哪几个表查？ from 表 join方法 连接的表 on 交叉条件
-
假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢增加。
4.5 自连接查询
定义：自己的表和自己的表连接，核心：一张表拆为两张一样的表即可。
在school数据库中添加catalog表
-- 创建category表：categoryid为子类id，pid为父类id
DROP TABLE IF EXISTS `category`;
CREATE TABLE IF NOT EXISTS `category`(
`categoryid` INT(3) NOT NULL COMMENT '子类id',
`pid` INT(3) NOT NULL COMMENT '父类id，如果没有父类id，则为1',
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
-- 插入category数据
INSERT INTO `category` (`categoryid`,`pid`,`categoryname`)
VALUES (2,1,'信息技术'),
(3,1,'软件开发'),
(5,1,'美术设计'),
(4,3,'数据库'),
(8,2,'办公信息'),
(6,3,'web开发'),
(7,5,'ps技术');•
输出结果：
我们分析这张表，可以看出，在这张表内实现了分级，例如：信息技术子类id=2，父类id=1，而数据库子类id=4，父类id=1。我们可以根据上述规则将这张表拆分为两个表：
-
父类：父类id都是1的
| categoryid | categoryname | pid |
|---|---|---|
| 2 | 信息技术 | 1 |
| 3 | 软件开发 | 1 |
| 5 | 美术设计 | 1 |
-
子类：父类id不是1的，即上面剩下的数据
| categoryid | categoryname | pid |
|---|---|---|
| 4 | 数据库 | 3 |
| 6 | web开发 | 3 |
| 7 | ps技术 | 5 |
| 8 | 办公信息 | 2 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 对于都在一个表中的字段，我们就使用自连接查询
-- 查询父子信息：把一张表拆为两个一模一样的表
-- 拆名字
SELECT a.`categoryname` AS 父栏目 ,b.`categoryname` AS 子栏目
-- 拆表
FROM `category` AS a,`category` AS b
-- 通过id进行连接
WHERE a.`categoryid`=b.`pid` -- 此处不能调换位置，要一一对应
输出结果：
-- 通过自连接查询实现
SELECT a.`categoryname` 科目类别,b.`categoryname` 课程
FROM `category` a
INNER JOIN `category` b -- 此处只能用INNER JOIN
ON a.`categoryid`=b.`pid` -- 此处不能调换位置，要一一对应
输出结果：
-- 补充练习：
-- 1.查询学员所属的年级，输出学员的信息：学号、学生姓名、年级名称
SELECT `StudentNo`,`StudentName`,`gradeName`
FROM `student` s
INNER JOIN `grade` g
ON s.`GradeID`=g.`gradeID`
-- 与上面结果相同，只是顺序有交换
SELECT `StudentNo`,`StudentName`,`gradeName`
FROM `student` s
LEFT JOIN `grade` g
ON s.`GradeID`=g.`gradeID`
输出结果：
-- 2.查询科目所属年级，输出科目名称、年级名称
SELECT `SubjectName`,`gradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.`GradeID`=g.`gradeID`
-- 与上面结果相同，只是顺序有交换
SELECT `SubjectName`,`gradeName`
FROM `subject` s
LEFT JOIN `grade` g
ON s.`GradeID`=g.`gradeID`
输出结果：
-- 3.查询参加数据库结构-1考试的同学信息：学号、学生姓名、科目名称、分数
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`='数据库结构-1' -- 单独选出数据库结构-1考试一科
输出结果：
