MySQL 多表查询
多表查询：
A）内连接：join，inner join
B）外连接：left join，left outer join，right join，right outer join，union
C）交叉连接：cross join
JOIN 按照功能大致分为如下三类：
- INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。
- LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
- RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。
内连接查询:
select * from emp inner join dep on emp.dep_id=dep.id;
左外连接查询：
select * from emp left join dep on emp.dep_id=dep.id;
mysql> select * from emp; +----+------+--------+--------+ | id | name | salary | dep_id | +----+------+--------+--------+ | 1 | 张三 | 8000 | 2 | | 2 | 李四 | 12000 | 1 | | 3 | 王五 | 5000 | 2 | | 4 | 赵六 | 8000 | 3 | | 5 | 猪七 | 9000 | 1 | | 6 | 周八 | 7000 | 4 | | 7 | 蔡九 | 7000 | 2 | +----+------+--------+--------+ 7 rows in set mysql> select * from dep; +----+--------+ | id | name | +----+--------+ | 1 | 教学部 | | 2 | 销售部 | | 3 | 人事部 | +----+--------+ 3 rows in set
内连接
查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
mysql> select * from emp join dep on emp.dep_id=dep.id; +----+------+--------+--------+----+--------+ | id | name | salary | dep_id | id | name | +----+------+--------+--------+----+--------+ | 1 | 张三 | 8000 | 2 | 2 | 销售部 | | 2 | 李四 | 12000 | 1 | 1 | 教学部 | | 3 | 王五 | 5000 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000 | 2 | 2 | 销售部 | +----+------+--------+--------+----+--------+ 6 rows in set
这时，我们就可以利用两张表中所有的字段进行查询了
mysql> select * from emp; -- 这么一个表 +----+------+--------+--------+ | id | name | salary | dep_id | +----+------+--------+--------+ | 1 | 张三 | 8000 | 2 | | 2 | 李四 | 12000 | 1 | | 3 | 王五 | 5000 | 2 | | 4 | 赵六 | 8000 | 3 | | 5 | 猪七 | 9000 | 1 | | 6 | 周八 | 7000 | 4 | | 7 | 蔡九 | 7000 | 2 | +----+------+--------+--------+ 7 rows in set mysql> select * from dep; -- 这么一个表 +----+--------+ | id | name | +----+--------+ | 1 | 教学部 | | 2 | 销售部 | | 3 | 人事部 | +----+--------+ 3 rows in set mysql> select * from emp join dep on emp.dep_id=dep.id; -- 内连接，也叫等值连接，inner join产生同时符合A和B的一组数据 +----+------+--------+--------+----+--------+ | id | name | salary | dep_id | id | name | +----+------+--------+--------+----+--------+ | 1 | 张三 | 8000 | 2 | 2 | 销售部 | | 2 | 李四 | 12000 | 1 | 1 | 教学部 | | 3 | 王五 | 5000 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000 | 2 | 2 | 销售部 | +----+------+--------+--------+----+--------+ 6 rows in set -- 查询李四所在的部门名称 mysql> select emp.name,dep.name from emp join dep on emp.dep_id=dep.id group by dep.id having emp.name='李四'; +------+--------+ | name | name | +------+--------+ | 李四 | 教学部 | +------+--------+ 1 row in set -- 查询李四所在的部门名称 mysql> select emp.name,dep.name from emp join dep on emp.dep_id=dep.id where emp.name='李四'; +------+--------+ | name | name | +------+--------+ | 李四 | 教学部 | +------+--------+ 1 row in set -- 查询销售部所有工作人员以及所属部门 mysql> select dep.name ,group_concat(emp.name) from emp join dep on dep.id=emp.dep_id where dep.id=2; +--------+------------------------+ | name | group_concat(emp.name) | +--------+------------------------+ | 销售部 | 张三,王五,蔡九 | +--------+------------------------+
左右连接
--（1）左外连接：在内连接的基础上增加左边有右边没有的结果
--（1）左外连接：在内连接的基础上增加左边有右边没有的结果 mysql> select * from emp left join dep on emp.dep_id=dep.id; +----+------+--------+--------+------+--------+ | id | name | salary | dep_id | id | name | +----+------+--------+--------+------+--------+ | 2 | 李四 | 12000 | 1 | 1 | 教学部 | | 5 | 猪七 | 9000 | 1 | 1 | 教学部 | | 1 | 张三 | 8000 | 2 | 2 | 销售部 | | 3 | 王五 | 5000 | 2 | 2 | 销售部 | | 7 | 蔡九 | 7000 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000 | 3 | 3 | 人事部 | | 6 | 周八 | 7000 | 4 | NULL | NULL | +----+------+--------+--------+------+--------+
--（1）外右连接：在内连接的基础上增加右边有左边没有的结果
--（1）外右连接：在内连接的基础上增加右边有左边没有的结果 mysql> select * from emp right join dep on emp.dep_id=dep.id; +----+------+--------+--------+----+--------+ | id | name | salary | dep_id | id | name | +----+------+--------+--------+----+--------+ | 1 | 张三 | 8000 | 2 | 2 | 销售部 | | 2 | 李四 | 12000 | 1 | 1 | 教学部 | | 3 | 王五 | 5000 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000 | 2 | 2 | 销售部 | +----+------+--------+--------+----+--------+ 6 rows in set
完整性约束
完整性约束是对字段进行限制，从而符合该字段达到我们期望的效果比如字段含有默认值，不能是NULL等 。
直观点说：如果插入的数据不满足限制要求，数据库管理系统就拒绝执行操作
唯一约束
唯一约束可以有多个但索引列的值必须唯一，索引列的值允许有空值。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE。
CREATE TABLE t5( id INT AUTO_INCREMENT, name VARCHAR(20) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY UK_t5_name (name) ); -- 建表后添加约束: alter table t5 add constraint UK_t5_name unique (name); -- 如果不需要唯一约束，则可以这样删除 ALTER TABLE t5 DROP INDEX UK_t5_name;
添加约束和删除约束
-- 创建唯一约束: create unique index UK_t5_name on t5 (name); -- 建表后添加约束: alter table t5 add constraint UK_t5_name unique (name); -- 如果不需要唯一约束，则可以这样删除 ALTER TABLE t5 DROP INDEX UK_t5_name;
自增约束
MySQL 每张表只能有1个自动增长字段，这个自动增长字段通常作为主键，也可以用作非主键使用，
但是请注意将自动增长字段当做非主键使用时必须必须为其添加唯一索引，否则系统将会报错。
mysql> CREATE TABLE t4( -> id INT NOT NULL, -> name VARCHAR(20), -> age INT AUTO_INCREMENT -> ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
可以改为
mysql> CREATE TABLE t4( -> id INT NOT NULL, -> name VARCHAR(20), -> age INT UNIQUE AUTO_INCREMENT -> ); Query OK, 0 rows affected (0.13 sec)
主键约束
主键是用于唯一标识一条记录的约束，如同身份证。
主键有两个约束：非空且唯一！
创建主键
-- 方式1 CREATE TABLE t1( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); -- 方式2 CREATE TABLE t2( id INT NOT NULL, name VARCHAR(20) );
注意：
1、一张表中最多只能有一个主键
2、表中如果没有设置主键，默认设置NOT NULL的字段为主键；此外，表中如果有多个NOT NULL的字段，则按顺序将第一个设置NOT NULL的字段设为主键。
结论：主键一定是非空且唯一，但非空且唯一的字段不一定是主键。
3、主键类型不一定必须是整型
添加主键和删除主键
-- 添加主键 alter table tab_name add primary key(字段名称,...) -- 删除主键 alter table users drop primary key; 注意，如果主键是AUTO_INCREMENT，需要先取消AUTO_INCREMENT，因为AUTO_INCREMENT只能加在KEY上。 -- CREATE TABLE test(num INT PRIMARY KEY AUTO_INCREMENT);<br> -- 思考，如何删除主键？ ALTER TABLE test modify id int; -- auto_increment没了，但这样写主键依然存在，所以还要加上下面这句 ALTER TABLE test drop primary key;-- 仅仅用这句也无法直接删除主键
复合主键
所谓的复合主键 就是指你表的主键含有一个以上的字段。
如果一列不能唯一区分一个表里的记录时，可以考虑多个列组合起来达到区分表记录的唯一性，形式
①创建时
create table sc ( studentid int, courseid int, score int, primary key (studentno,courseid) );
②修改时
alter table tb_name add primary key (字段1,字段2,字段3);
外键约束
外键语法
外键的定义语法： [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
该语法 可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
准备表和数
-- 子表 CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), dep_id INT -- CONSTRAINT emp_fk_emp FOREIGN KEY (dep_id) REFERENCES dep(id) -- 注意外键字段的数据类型必须与关联字段一致 ); -- 主表 CREATE TABLE dep( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); INSERT emp (name,dep_id) VALUES ("alex",1), ("egon",2), ("alvin",2), ("莎莎",1), ("wusir",2), ("女神",2), ("冰冰",3), ("姗姗",3); INSERT dep (name) VALUES ("市场部"), ("教学部"), ("销售部");
添加外键
现在，删除市场部：
mysql> DELETE FROM dep WHERE name="市场部"; Query OK, 1 row affected (0.01 sec)
居然删除成功了，不可思议，现在问题来了： alex和莎莎两个人怎么办？
所以，为了避免类似操作，我们需要给两张表建立约束，这种约束称为外键约束。外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作
INSERT dep (id,name) VALUES (1,"市场部"); -- 思考为什么加这一句? ALTER TABLE emp ADD CONSTRAINT dep_fk_emp FOREIGN KEY (dep_id) REFERENCES dep(id); mysql> DELETE FROM dep WHERE name="市场部"; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fail
INNODB支持的ON语句
外键约束对子表的含义: 如果在主表中(比如dep)找不到候选键,则不允许在子表(比如emp)上进行insert/update
外键约束对父表的含义: 在主表上进行update/delete以更新或删除在子表中有一条或多条应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的 -- on update/on delete子句
-- ------------------------innodb支持的四种方式--------------------------------- cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除 FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE; set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null ; 要注意子表的外键列不能为not null FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL; Restrict方式 :拒绝对父表进行删除更新操作(了解) No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 ; 进行update/delete操作（了解）
表与表之间的关系
