--- 每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任
----主表
CREATE TABLE ClassCharger(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR (20),
age INT ,
is_marriged boolean -- show create table ClassCharger: tinyint(1)
);
INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0),
("丹丹",14,0),
("歪歪",22,0),
("姗姗",20,0),
("小雨",21,0);
----子表
CREATE TABLE Student(
id INT PRIMARY KEY auto_increment,
name VARCHAR (20),
charger_id TINYINT, --切记:作为外键一定要和关联主键的数据类型保持一致
-- [ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
) ENGINE=INNODB;
INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
("alvin2",4),
("alvin3",1),
("alvin4",3),
("alvin5",1),
("alvin6",3),
("alvin7",2);
DELETE FROM ClassCharger WHERE name="冰冰";
INSERT student (name,charger_id) VALUES ("yuan",1);
-- 删除居然成功,可是 alvin3显示还是有班主任id=1的冰冰的;
-----------增加外键和删除外键---------
ALTER TABLE student ADD CONSTRAINT abc
FOREIGN KEY(charger_id)
REFERENCES classcharger(id);
ALTER TABLE student DROP FOREIGN KEY abc;
多表查询
准备表
-- 准备两张表
-- company.employee
-- company.department
create table employee(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int
);
insert into employee(emp_name,age,dept_id) values
('A',19,200),
('B',26,201),
('C',30,201),
('D',24,202),
('E',20,200),
('F',38,204);
create table department(
dept_id int,
dept_name varchar(100)
);
insert into department values
(200,'人事部'),
(201,'技术部'),
(202,'销售部'),
(203,'财政部');
mysql> select * from employee;
+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | A | 19 | 200 |
| 2 | B | 26 | 201 |
| 3 | C | 30 | 201 |
| 4 | D | 24 | 202 |
| 5 | E | 20 | 200 |
| 6 | F | 38 | 204 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)
mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 200 | 人事部 |
| 201 | 技术部 |
| 202 | 销售部 |
| 203 | 财政部 |
+---------+-----------+
4 rows in set (0.01 sec)
多表查询之连接查询
1.笛卡尔积查询
mysql> SELECT * FROM employee,department;
-- select employee.emp_id,employee.emp_name,employee.age,
-- department.dept_name from employee,department;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 1 | A | 19 | 200 | 201 | 技术部 |
| 1 | A | 19 | 200 | 202 | 销售部 |
| 1 | A | 19 | 200 | 203 | 财政部 |
| 2 | B | 26 | 201 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 2 | B | 26 | 201 | 202 | 销售部 |
| 2 | B | 26 | 201 | 203 | 财政部 |
| 3 | C | 30 | 201 | 200 | 人事部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 202 | 销售部 |
| 3 | C | 30 | 201 | 203 | 财政部 |
| 4 | D | 24 | 202 | 200 | 人事部 |
| 4 | D | 24 | 202 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 4 | D | 24 | 202 | 203 | 财政部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| 5 | E | 20 | 200 | 201 | 技术部 |
| 5 | E | 20 | 200 | 202 | 销售部 |
| 5 | E | 20 | 200 | 203 | 财政部 |
| 6 | F | 38 | 204 | 200 | 人事部 |
| 6 | F | 38 | 204 | 201 | 技术部 |
| 6 | F | 38 | 204 | 202 | 销售部 |
| 6 | F | 38 | 204 | 203 | 财政部 |
+--------+----------+------+---------+---------+-----------+
2.内连接
-- 查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
select * from employee,department where employee.dept_id = department.dept_id;
--select * from employee inner join department on employee.dept_id = department.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
+--------+----------+------+---------+---------+-----------+
3.外连接
--（1）左外连接：在内连接的基础上增加左边有右边没有的结果
select * from employee left join department on employee.dept_id = department.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 6 | F | 38 | 204 | NULL | NULL |
+--------+----------+------+---------+---------+-----------+
--（2）右外连接：在内连接的基础上增加右边有左边没有的结果
select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| NULL | NULL | NULL | NULL | 203 | 财政部 |
+--------+----------+------+---------+---------+-----------+
--（3）全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
-- mysql不支持全外连接 full JOIN
-- mysql可以使用此种方式间接实现全外连接
select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
UNION
select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| NULL | NULL | NULL | NULL | 203 | 财政部 |
| 6 | F | 38 | 204 | NULL | NULL |
+--------+----------+------+---------+---------+-----------+
-- 注意 union与union all的区别：union会去掉相同的纪录
多表查询之子查询
-- 子查询是将一个查询语句嵌套在另一个查询语句中。
-- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
-- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
-- 还可以包含比较运算符：= 、 !=、> 、<等
-- 1. 带IN关键字的子查询
---查询employee表，但dept_id必须在department表中出现过
select * from employee
where dept_id IN
(select dept_id from department);
+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | A | 19 | 200 |
| 2 | B | 26 | 201 |
| 3 | C | 30 | 201 |
| 4 | D | 24 | 202 |
| 5 | E | 20 | 200 |
+--------+----------+------+---------+
5 rows in set (0.01 sec)
-- 2. 带比较运算符的子查询
-- =、!=、>、>=、<、<=、<>
-- 查询员工年龄大于等于25岁的部门
select dept_id,dept_name from department
where dept_id IN
(select DISTINCT dept_id from employee where age>=25);