学习笔记(05):Mysql数据库基础入门视频教程-自连接-优快云博客

本文链接：https://blog.youkuaiyun.com/xuanxiaochuan/article/details/105724911

立即学习:https://edu.youkuaiyun.com/course/play/9885/210119?utm_source=blogtoedu

-- 子查询
   -- 一个select语句包含另一个完整的select语句或者两个以上的select语句。
   -- WHERE 后，把select查询出来的结果当做另一个select的条件值
   SELECT class FROM stu WHERE name = '兰陵王'；
   SELECT name, class FROM stu WHERE class = (SELECT class FROM stu WHERE name = '兰陵王');
   -- FROM 后，把查询出的记过当做一个新表
   SELECT st.name, st.age, sc.score, cs.name from stu st JOIN score sc ON st.id = sc.sid JOIN course cs ON sc.cid = cs.cid;
   SELECT *   FROM (SELECT st.name, st.age, sc.score, cs.name course from stu st JOIN score sc ON st.id = sc.sid JOIN course cs ON sc.cid = cs.cid) ss WHERE ss.score >= 90;
   -- 查询年龄低于兰陵王的学生
   SELECT name,age   FROM stu where age < (SELECT age FROM stu WHERE name = '兰陵王');
   -- 查询高于2班所有年龄的学生
   SELECT * FROM stu WHERE age > (SELECT MAX(age) FROM stu st WHERE st.class = 2) ORDER BY age DESC;
   -- 查询班级和年龄与兰陵王相同的学生
   SELECT * FROM stu WHERE class = (SELECT class FROM stu WHERE name = '兰陵王') AND age = (SELECT age FROM stu WHERE name = '兰陵王');
   SELECT * FROM stu where (age, class) in (SELECT age, class FROM stu WHERE name = '兰陵王');
   SELECT st.* FROM stu st, (SELECT age, class FROM stu WHERE name = '兰陵王') r WHERE st.age = r.age AND st.class = r.class;
   -- 查询有两个直接下属的学生
   SELECT mgr, GROUP_CONCAT(mgr), COUNT(mgr) FROM stu GROUP BY mgr HAVING COUNT(mgr) >= 2;
   SELECT * FROM stu WHERE id in (SELECT mgr FROM stu GROUP BY mgr HAVING COUNT(mgr) >= 2)
   -- 查询编号1008的学生id,姓名，上司id，姓名
   SELECT s1.id, s1.name, s2.id, s2.name   FROM stu s1, stu s2 WHERE s1.mgr = s2.id AND s1.id = 1008;


-- 表结构：
CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`gender` int(1) DEFAULT NULL,
`class` int(11) NOT NULL,
`position` varchar(10) DEFAULT NULL,
`mgr` int(11) DEFAULT NULL COMMENT '上司',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1010 DEFAULT CHARSET=utf8;

CREATE TABLE `score` (
`score` int(11) NOT NULL,
`sid` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
KEY `sid` (`sid`),
KEY `cid` (`cid`),
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `stu` (`id`),
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `course` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
`cid` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
KEY `cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `grade` (
`name` varchar(10) DEFAULT NULL,
`score_low` int(11) DEFAULT NULL,
`score_high` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 数据：
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1001, '张三', 18, '北京', 0, 1, '普通学生', 1005);
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1002, '李四', 20, '济南', 1, 2, '普通学生', 1005);
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1003, '王五', 19, '上海', 1, 2, '普通学生', 1005);
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1004, '赵六', 20, '厦门', 1, 3, '普通学生', 1005);
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1005, '周琪', 21, '齐齐哈尔', 0, 3, '卫生委员', 1006);
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1006, '兰陵王', 22, '峡谷', 1, 4, '班长', NULL);
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1007, '赵云', 17, '峡谷', 1, 4, '学习委员', 1006);
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1008, '扁鹊', 23, '峡谷', 1, 4, '普通学生', 1007);
INSERT INTO `stu`(`id`, `name`, `age`, `address`, `gender`, `class`, `position`, `mgr`) VALUES (1009, '妲己', 22, '峡谷', 0, 4, '普通学生', 1007);

INSERT INTO `grade`(`name`, `score_low`, `score_high`) VALUES ('优秀', 90, 100);
INSERT INTO `grade`(`name`, `score_low`, `score_high`) VALUES ('良好', 80, 89);
INSERT INTO `grade`(`name`, `score_low`, `score_high`) VALUES ('及格', 60, 79);
INSERT INTO `grade`(`name`, `score_low`, `score_high`) VALUES ('不及格', 0, 59);

INSERT INTO `score`(`score`, `sid`, `cid`) VALUES (60, 1001, 1);
INSERT INTO `score`(`score`, `sid`, `cid`) VALUES (60, 1001, 2);
INSERT INTO `score`(`score`, `sid`, `cid`) VALUES (90, 1002, 2);
INSERT INTO `score`(`score`, `sid`, `cid`) VALUES (70, 1003, 3);
INSERT INTO `score`(`score`, `sid`, `cid`) VALUES (60, 1004, 1);

INSERT INTO `course`(`cid`, `name`) VALUES (1, 'java');
INSERT INTO `course`(`cid`, `name`) VALUES (2, 'html');
INSERT INTO `course`(`cid`, `name`) VALUES (3, 'English');
INSERT INTO `course`(`cid`, `name`) VALUES (4, '数学');