MySQL学习笔记（5）子查询

-- 测试子查询
-- 测试由IN引发的子查询

SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);

SELECT * FROM emp
WHERE depId NOT IN (SELECT id FROM dep);

-- 由EXISTS 引发的子查询

SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10);
SELECT * FROM emp WHERE NOT EXISTS (SELECT depName FROM dep WHERE id=1);


-- 学员stu
CREATE TABLE stu(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED NOT NULL
);
INSERT stu(username,score) VALUES('king',95),
('queen',75),
('zhangsan',69),
('lisi',78),
('wangwu',87),
('zhaoliu',88),
('tianqi',98),
('ceshi',99),
('tiancai',50);


-- 分数级别level
CREATE TABLE level(
id tinyint UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED COMMENT '分数'
);
INSERT level(score) VALUES(90),(80),(70);

-- 查询出成绩优秀的学员

SELECT score FROM level WHERE id=1;

SELECT id,username,score FROM stu
WHERE score>=(SELECT score FROM level WHERE id=1);

-- 查询出没有得到评级的学员

SELECT id,username,score FROM stu
WHERE score<(SELECT score FROM level WHERE id=3);



-- 带有ANY SOME ALL关键字的子查询
SELECT * FROM stu
WHERE score>= ANY(SELECT score FROM level);

SELECT * FROM stu
WHERE score>= SOME(SELECT score FROM level);

SELECT * FROM stu
WHERE score>= ALL(SELECT score FROM level);

SELECT * FROM stu
WHERE score< ALL(SELECT score FROM level);

SELECT * FROM stu
WHERE score=ANY(SELECT score FROM level);

SELECT * FROM stu
WHERE score!=ALL(SELECT score FROM level);

-- 创建一个user1表,id username
CREATE TABLE user1(
    id int UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
)SELECT id,username FROM emp;

-- 将stu表中id=3的用户名写入到user1表中
INSERT user1(username) SELECT username FROM stu where id=3;

-- 创建象user1一样的表user2
CREATE TABLE user2 LIKE user1;
INSERT user2(username) SELECT username FROM stu;

-- 将stu表中的tiancai用户名添加到user2表中
INSERT user2 SET username=(SELECT username FROM stu WHERE id=5);

-- 去掉字段的重复值
SELECT DISTINCT(username) FROM user2;

-- 将user1和user2数据合并到一起
--去重
SELECT username FROM user1
UNION
SELECT username FROM user2;
--不去重
SELECT username FROM user1
UNION ALL
SELECT username FROM user2;