子查询和 EXISTS
SELECT * FROM student;
– 查询最高分
SELECT MAX(score) FROM student;
– 再查询这个分数位最分的学生
SELECT name, class FROM student WHERE score = 95;
– 合并 sql 成为子查询
SELECT name, class FROM student WHERE score = (SELECT MAX(score) FROM student);
– 查询成绩高于全校平均成绩的学生记录
SELECT * FROM student WHERE score > (SELECT AVG(score) FROM student);
– 查询部门表数据
SELECT * FROM department;
– 查询员工表的数据
SELECT * FROM employee;
– EXISTS 查询有员工的部门
SELECT name FROM department WHERE EXISTS (SELECT * FROM employee WHERE department.id = employee.department_id);
– NOT EXISTS 查询所有没有员工的部门
SELECT name FROM department WHERE NOT EXISTS (SELECT * FROM employee WHERE department.id = employee.department_id);
– 新建产品表
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10,2),
category VARCHAR(50),
stock INT
);
– 插入数据
INSERT INTO product (id, NAME, price, category, stock)
VALUES
(1, ‘iPhone12’, 6999.00, ‘手机’, 100),
(2, ‘iPad Pro’, 7999.00, ‘平板电脑’, 50),
(3, ‘MacBook Pro’, 12999.00, ‘笔记本电脑’, 30),
(4, ‘AirPods Pro’, 1999.00, ‘耳机’, 200),
(5, ‘Apple Watch’, 3299.00, ‘智能手表’, 80);
– 查询价格最高的产品信息
– 通过一个子查询查最高的价格，然后外层查询价格为最高价格的产品
SELECT name, price FROM product WHERE price = (SELECT MAX(price) FROM product);
– 把每个产品分类的分类名、平均价格查出来放入另一个 avg_price_by_category 表
CREATE TABLE avg_price_by_category (
id INT AUTO_INCREMENT,
category VARCHAR(50) NOT NULL,
avg_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id)
);
– 把产品表里的分类和平均价格查询出来插入这个表
INSERT INTO avg_price_by_category (category,avg_price)
SELECT category, AVG(price) FROM product GROUP BY category;
SELECT category, AVG(price) FROM product GROUP BY category
SELECT * FROM avg_price_by_category;
– 查询名字等于技术部的 department 的 id，然后更新 department_id 为这个 id 的所有 employee 的名字为 CONCAT(“技术-“, name)
UPDATE employee SET name = CONCAT(‘技术-‘,name) WHERE department_id = (SELECT id FROM department WHERE name = ‘技术部’);
SELECT * FROM employee;
– 删除所有技术部的员工
DELETE FROM employee WHERE department_id = (SELECT id FROM department WHERE name = ‘技术部’);
总结：
sql 和 sql 可以组合来完成更复杂的功能，这种语法叫做子查询。
它还有个特有的关键字 EXISTS（和 NOT EXISTS），当子查询有返回结果的时候成立，没有返回结果的时候不成立。
子查询不止 select 可用，在 update、insert、delete 里也可以用。
灵活运用子查询，能写出功能更强大的 sql.