子查询

1》通过IN引发的子查询

SELECT * FROM employee WHERE depid in(SELECT id FROM department);

SELECT * FROM employee WHERE depid NOT IN(SELECT id FROM department);

2》由于比较运算符引发的子查询

SELECT * FROM employee WHERE depid=(SELECT id FROM department WHERE depname='摄影部');

--

SELECT depname FROM department WHERE id IN(SELECT depid FROM employee WHERE age>25);

--摄影部年龄最小/大值

SELECT MIN(age) FROM employee WHERE depid=1;12

SELECT MAX(age) FROM employee WHERE depid=1;14

--年龄大于摄影部年龄最大的员工

SELECT * FROM employee WHERE age>(SELECT MAX(age) FROM employee WHERE depid=1);

3》EXIST(内层有结果才执行外层）

SELECT id FROM department WHERE depname='国防部';空

SELECT * FROM employee WHERE EXISTS(SELECT id FROM department WHERE depname='国防部');

SELECT * FROM employee WHERE EXISTS(SELECT id FROM department WHERE depname='督导部');

4》由ANY|SOME|ALL修饰的比较运算符

>=ANY|SOME:大于子查询中的最小值

>=ALL:大于子查询中的最大值

SELECT * FROM employee WHERE depid>ANY(SELECT id FROM department);depid>2

SELECT * FROM employee WHERE depid>SOME(SELECT id FROM department);depid>2

SELECT * FROM employee WHERE id>ALL(SELECT id FROM department);id>5

<=ANY|SOME:小于子查询中的最大值

<=ALL:小于子查询中的最小值

=ANY|SOME:相当于IN,不能写不等于

SELECT * FROM employee WHERE depid=ANY(SELECT id FROM department);全部成员

<>ALL:相当于NOT IN

SELECT * FROM employee WHERE depid<>ALL(SELECT id FROM department);空

等于：只能ANY|SOME;不等于：ALL只能。

开发中很少用子查询，效率不高，必须现执行子查询，再执行外层查询。

连接查询

连接查询：内连接+外连接

外连接：左连接+右连接

1》两表查询

--员工表中员工姓名、编号、性别、年龄、所属部门名称

SELECT e.id,e.username,e.sex,e.age,d.depname FROM employee AS e,department AS d;得到两边笛卡尔积

由于哪个桥梁连接起来的

员工表中的depid等于部门表的id

SELECT e.id,e.username,e.sex,e.age,d.depname FROM employee AS e,department AS d WHERE e.depid=d.id;

1>内连接

[INNER|CROSS] JOIN

通过ON连接条件

查询出两个或多个表都满足的结果

--员工表所属部门名称

SELECT e.id,e.username,e.sex,e.age,d.depname FROM department AS d INNER JOIN employee AS e ON e.depid=d.id;

WHERE通常用于条件塞选

--员工年龄大于25

SELECT e.id,e.username,e.sex,e.age,d.depname FROM department AS d INNER JOIN employee AS e ON e.depid=d.id WHERE e.age>25;

2》多表查询：

CREATE TABLE IF NOT EXISTS provinces(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

pname VARCHAR(10) NOT NULL UNIQUE

);

INSERT provinces(pname) VALUES('四川'),('云南'),('陕西'),('广东');

ALTER TABLE employee ADD addr1 TINYINT UNSIGNED DEFAULT 1;

UPDATE employee SET addr1=2 WHERE id IN(2,4,6,8);

ALTER TABLE employee CHANGE addr1 pid TINYINT UNSIGNED DEFAULT 1;

UPDATE employee SET pid=2 WHERE id IN(2,4,6,8);

--查询员工所属省份

SELECT e.id,e.username,p.pname FROM employee AS e JOIN provinces AS p ON p.id=e.pid;

--部门名称、省份名称、用户的信息

SELECT e.id,e.username,p.pname,d.depname FROM employee AS e JOIN provinces AS p ON p.id=e.pid JOIN department AS d ON e.depid=d.id;

不断在后面加JOIN跟上ON条件。

SELECT e.id,e.username,p.pname,d.depname FROM employee AS e JOIN provinces AS p ON p.id=e.pid JOIN department AS d ON e.depid=d.id LIMIT 0,3;

INSERT employee(username,age,addr,salary,sex,depid,pid) VALUES('test5',25,'北京',6542,'女',6,9);

插入成功了，但这条数据部合法。省份只有4个，部门只有4个，没有保证记录完整性与一致性，那么就应当通过外键来实现。

外键

依赖已存在表的主键来设置外键。

作用：保证记录完整性与一致性

创建外键注意事项：

父表与子表必须使用相同的存储引擎，禁止使用临时表；

存储引擎只能为InnoDB

子表外键必须关联父表主键

外键列与参照列应具有相似的数据类型。

1》创建外键

创建表时

CREATE TABLE IF NOT EXISTS user(

id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL UNIQUE,

pid TINYINT UNSIGNED ,

FOREIGN KEY(pid) REFERENCES provinces(id)

);