阅读量:1
1、多表关系
概述：项目开发中，在进行数据库表结构操作设计时，会根据业务需求及业务模板之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
一对多（多对一）
多对多
一对一
· 一对多（多对一）
案例：部门与员工的关系（一个部门对应多个员工，一个员工对应一个部门）
实现：在多的一方建立外键，指向一的一方的主键
· 多对多
案例：学生与课程的关系（一个学生可以选修多门课程，一门课程可以共多个学生选择）
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
create table student( id int primary key auto_increment comment '主键ID', name varchar(10) comment '姓名', no varchar(10) comment '学号' )comment '学生表'; insert into student values(null,'黛丽丝','2000100101'),(null,'谢逊','2000100102'),(null,'殷天正','2000100103'),(null,'韦一笑','2000100104'); create table course( id int primary key auto_increment comment '主键ID', name varchar(10) comment '课程名称' )comment '课程表'; insert into course values(null,'java'),(null,'php'),(null,'mysql'),(null,'hadoop'); create table student_course( id int primary key auto_increment comment '主键ID', studentid int not null comment '学生ID', courseid int not null comment '课程ID', constraint fk_courseid foreign key (courseid) references course (id), constraint fk_studentid foreign key (studentid) references student (id) )comment '课程中间表'; insert into student_course values(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);
· 一对一
案例：用户与用户详情的关系（一对一关系多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率）
实现：在任一方加入外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）
create table tb_user( id int primary key auto_increment comment '主键ID', name varchar(10) comment '姓名', age int comment '年龄', gender varchar(1) comment '性别', phone char(11) comment '电话' )comment '用户基本信息表'; create table tb_user_edu( id int primary key auto_increment comment '主键ID', degree varchar(20) comment '学历', major varchar(50) comment '专业', primaryschool varchar(50) comment '小学', middleschool varchar(50) comment '中学', university varchar(50) comment '大学', userid int unique comment '用户ID', constraint fk_userid foreign key (userid) references tb_user(id) )comment '用户教育信息表'; insert into tb_user values (null,'黄渤',45,'1','18903944771'), (null,'冰冰',32,'2','18903955771'), (null,'马云',55,'1','17719224870'), (null,'李彦宏',50,'1','15538655111'); insert into tb_user_edu values (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1), (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2), (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3), (null,'本科','应用数学','阳泉区第一小学','阳泉区第一中学','清华大学',4);2、多表查询概述
概述：指从多张表中查询数据
案例：我们用员工与所属部门来作为案例
select * from emp,dept;
但问题是我们明明只有五条数据却显示了25行信息，这种现象称为笛卡尔积。
笛卡尔积：在数学中两个集合，A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
select * from emp,dept where emp.dept_id=dept.id;多表查询分类
· 连接查询
内连接：相当于查询A、B交集部分的数据
外连接：
左外连接：查询左表所有数据，以及两张表交际部分数据
右外连接：查询右表所有数据，以及两张表交际部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名
· 子查询
3、内连接查询
内连接查询语法：
隐式内连接：
SELECT 字段列表 FROM 表1，表2 WHERE 条件 ...;
显示内连接：
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
内连接查询的是两张表的交集部分
#内连接演示 #1、查询每一个员工的姓名，及关联部门的名称（隐式内连接实现） #连接条件：emp.dept_id=dept.id select emp.name,dept.name from emp,dept where emp.dept_id=dept.id; #2、查询每一个员工的姓名，及关联部门的名称（显示内连接实现）--关键字：INNER JOIN ... ON ... select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;4、外连接查询
外连接查询语法：
左外连接：
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
查询的是左表的所有数据包含两表交集部分的数据
右外连接：
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;
查询的是右表的所有数据包含两表交集部分的数据
#左外连接和右外连接 #1、查询emp表的所有数据，和对应部门的信息（左外连接） select * from emp left outer join dept on emp.dept_id = dept.id; #2、查询dept表的所有数据，和对应员工信息（右外连接） select * from dept right outer join emp on dept.id = emp.dept_id;5、自连接查询
自连接查询语法：
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
自连接查询，可以是内连接查询，也可以是外连接查询。
#自连接 #1、查询员工及其所属领导的名字 select a.name,b.name from emp a,emp b where a.managerid=b.id; #2、查询所有员工 emp 及其领导的名字 emp，如果员工没有领导也要查询出来 select a.name,b.name from emp a left join emp b on a.managerid=b.id;6、联合查询
联合查询-union，union all
对于联合查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;
#联合查询 #将薪资低于10000的员工，和年龄大于50岁的员工全部查询出来 select * from emp where salary<10000 union all select * from emp where age>50; #对查询结果去重 select * from emp where salary<10000 union select * from emp where age>50;总结：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致；
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。
7、子查询
概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT 的任何一个。
根据子查询的结果不同，分为：
标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）
根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
#标量子查询 #1、查询研发部所有员工信息 #第一步：查询研发部部门ID select id from dept where name='研发部'; #第二步：根据研发部部门ID查询员工信息 select * from emp where dept_id=1; select * from emp where dept_id=(select id from dept where name='研发部'); #2、查询在”杨逍“入职之后的员工信息 #第一步：查询”杨逍“的入职日期 select entrydate from emp where name='杨逍'; #第二步：查询指定日期之后入职员工的信息 select * from emp where entrydate > '2000-11-03'; select * from emp where entrydate >(select entrydate from emp where name='杨逍');列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用操作符：IN、NOT IN、ANY、SOME、ALL
| 操作符 | 描述 |
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
#列子查询 #1、查询”总经办“和”研发部“所有员工的信息 #第一步：查询总经办和研发部的部门ID select id from dept where name='总经办'||name='研发部'; #第二步：根据部门ID，查询员工信息 select * from emp where dept_id in (5,1); select * from emp where dept_id in(select id from dept where name='总经办'||name='研发部'); #2、查询比研发部所有员工工资都高的员工信息 #第一步：查询研发部部门工资 select id from dept where name='研发部'; select salary from emp where dept_id=1; select salary from emp where dept_id=(select id from dept where name='研发部'); #第二步：比研发部所有员工工资都高的员工信息 select * from emp where salary > all(select salary from emp where dept_id=(select id from dept where name='研发部')); #3、查询比研发部其中任意一人工资高的员工信息 #第一步：查询研发部所有员工的工资 select salary from emp where dept_id=(select id from dept where name='研发部'); #第二步：比研发部其中任意一人工资高的信息 select * from emp where salary > any(select salary from emp where dept_id=(select id from dept where name='研发部'));行子查询
子查询的结果是一行（可以是多列），这种查询就称行子查询。
常用操作符：= 、<>、IN、NOT IN
#行子查询 #1、查询与”张无忌“的薪资及所属领导相同的员工信息 #第一步：查询张无忌的薪资及直属领导 select salary,emp.managerid from emp where name='张无忌'; #第二步：查询与”张无忌“的薪资及所属领导相同的员工信息 select * from emp where salary=(select salary from emp where name='张无忌')&& emp.managerid=(select managerid from emp where name='张无忌'); #上面这种方法是标量子查询 select * from emp where (salary,managerid)=(select salary,emp.managerid from emp where name='张无忌');表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用操作符：IN
#表子查询 #1、查询与杨逍与韦一笑的职位和薪资相同的员工信息 #第一步：查询与杨逍与韦一笑的职位和薪资 select job,salary from emp where name='杨逍'||name='韦一笑'; #第二步：查询与杨逍与韦一笑的职位和薪资相同的员工信息 select * from emp where (job,salary) in(select job,salary from emp where name='杨逍'||name='韦一笑'); #2、查询入职日期是”2002-01-01“之后的员工信息，及其部门信息 #第一步：入职信息是”2002-01-01“之后的员工信息 select * from emp where entrydate > '2002-01-01'; #第二步：查询这部分员工，对应的部门信息 select e.*,d.* from (select * from emp where entrydate > '2002-01-01') e left join dept d on e.dept_id=d.id;多表查询案例
#多表查询案例 #1、查询员工的姓名、年龄、职位、部门信息。(隐式内连接） select emp.name,emp.age,emp.job,dept.name from emp,dept where emp.dept_id=dept.id; #2、查询年龄小于30岁的员工姓名、年龄、职位、部门信息（显示内连接） select emp.name,emp.age,emp.job,dept.name from emp inner join dept on emp.dept_id=dept.id where emp.age<30; #3、查询拥有员工的部门ID、部门名称 select distinct dept.id,dept.name from emp,dept where emp.dept_id=dept.id; #4、查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来 select emp.*,dept.name from emp left outer join dept on emp.dept_id = dept.id where emp.age>40; #5、查询所有员工的工资等级 #准备工作 create table salarygrade( grade int, losal int, hisal int )comment'工资等级表'; insert into salarygrade values(1,0,3000), (2,3001,5000), (3,5001,8000), (4,8001,10000), (5,10001,15000), (6,15001,20000); select emp.name,salarygrade.grade from emp,salarygrade where emp.salary>=salarygrade.losal and emp.salary<=salarygrade.hisal; select emp.name,salarygrade.grade from emp,salarygrade where emp.salary between salarygrade.losal and salarygrade.hisal; #6、查询研发部所有员工信息及工资等级 select e.*,s.grade from emp e,salarygrade s,dept d where e.dept_id=d.id and e.salary between s.losal and s.hisal and d.name='研发部'; #7、查询研发部员工的平均工资 select avg(emp.salary) from emp,dept where emp.dept_id=dept.id and dept.name='研发部'; #8、查询工资比韦一笑高的员工信息 select * from emp where salary>(select salary from emp where name='韦一笑'); #9、查询比平均薪资高的员工信息 select * from emp where salary >(select avg(salary) from emp where id); #10、查询低于研发部门平均工资的员工信息 select * from emp where salary<(select avg(salary) from emp,dept where dept.name='研发部' and emp.dept_id=dept.id); #11、查询所有部门信息，并统计部门员工人数 select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id)'人数' from dept d;