MySQL 子查询 查询结果写入表

[NOT] IN

SELECT * FROM employee WHERE depId IN (SELECT id FROM department);

比较运算符  =，>, < ...

SELECT * FROM employee WHERE depId = (SELECT id FROM department WHERE name='管理部');

[NOT] EXISTS 判断真假

SELECT * FROM employee WHERE EXISTS(SELECT id FROM department WHERE name='管理部');

ANY|SOME  ALL 搭配比较运算符使用

>=ANY 大于等于最小值

>=ALL 等于最大值

=ANY 相当于IN

!=ALL 相当于 NOT IN

------------------------

将查询结果写入已有表

INSERT tablename(id, num)   SELECT id, score FROM student;

将查询结果写入新表

CREATE TABLE test(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

score TINYINT UNSIGNED

)SELECT id, score FROM student;