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
8.0版本：MySQL :: MySQL 8.0 Reference Manual :: 12.1 Built-In Function and Operator Reference
-- 通过函数查看系统版本
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
-- 使⽤ 查看成绩在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，结果为真
< 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 侧重于resultdistinct查询
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博客连表查询
SQL多表连接查询（详细实例） - 博雅源 - 博客园
输出结果：输出右表的所有信息，左表中没有匹配的⾏信息⽤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`
输出结果：
总结：
1. 我要查询哪些数据？
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,'软件开发'),
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论