时间:2021-07-01 10:21:17 帮助过:26人阅读
- --- 每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任
- ----主表
- 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)
- 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 | 财政部 |
- +--------+----------+------+---------+---------+-----------+
- -- 查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
- 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 | 人事部 |
- +--------+----------+------+---------+---------+-----------+
- --(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会去掉相同的纪录<br><br>
- -- 子查询是将一个查询语句嵌套在另一个查询语句中。
- -- 内层查询语句的查询结果,可以为外层查询语句提供查询条件。
- -- 子查询中可以包含: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);
MySQL(三)
标签:innodb 员工 constrain 创建 tool any 一个 基础 利用