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查询成绩在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

• 模糊查询：比较运算符

• 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结果对比：

 -- 查询缺考的同学：侧重于找出成绩为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`

输出结果：

总结：

1. 我要查询哪些数据？ select...

2. 从哪几个表查？ from 表 join方法 连接的表 on 交叉条件

3. 假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢增加。

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的

• 子类：父类id不是1的，即上面剩下的数据

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

 -- 对于都在一个表中的字段，我们就使用自连接查询
 -- 查询父子信息：把一张表拆为两个一模一样的表
 -- 拆名字
 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考试一科

输出结果：

SQLyog基本操作（五）