IT十八掌 Mysql 多表查询

网中网

　　多表查询
　　
多表设计——外键约束
　　作用：保证数据的完整性
　　创建完语句后，可以直接使用修改语句定义
　　alter table 表名 add foreign key 当前表名 (字段名) references 目标表名 (目标表的主键);
　　分析：
　　有一个部门的表，还有一个员工表，
　　create database day16;
　　use day16;
　　create table dept(
　　did int primary key auto_increment,
　　dname varchar(30)
　　);
　　create table emp(
　　eid int primary key auto_increment,
　　ename varchar(20),
　　salaly double,
　　dno int
　　);
　　insert into dept values(null,'研发部');
　　insert into dept values(null,'销售部');
　　insert into dept values(null,'人事部');
　　insert into dept values(null,'扯淡部');
　　insert into dept values(null,'牛宝宝部');
　　insert into emp values(null,'班长',10000,1);
　　insert into emp values(null,'美美',10000,2);
　　insert into emp values(null,'小凤',10000,3);
　　insert into emp values(null,'如花',10000,2);
　　insert into emp values(null,'芙蓉',10000,1);
　　insert into emp values(null,'东东',800,null);
　　insert into emp values(null,'波波',1000,null);
　　update emp set salaly=2500 where eid = 5;
　　* 把研发部删除？
　　* 研发部下有人员？该操作不合理。
　　* 引入外键约束？
　　* 作用：保证数据的完整性。
　　* 添加外键
　　语法：alter table emp add foreign key 当前表名(dno) references 关联的表(did);
　　alter table emp add foreign key emp(dno) references dept(did);
　　删除delete from dept where did = 1；
多表设计中三种实体关系

1:N 的联系可不引入关系表
　　例如：一个班级可以有多个学生，但是一个学生只能属于一个班级。或者部门与员工之间的关系。这些都是属于一对多的例子。那一对多在数据库设计中是怎么描述的呢？画图举例部门与员工的关系。

M:N的联系引入关系表
　　例如：在生活中一个学生可以选择多门课程，每一门课程也可以被多个学生所选择。这些例子在数据库设计中可以使用多对多来完成。
　　建表原则：
　　需要创建第三张表，该表中至少有两个字段，作为外键分别指向多对多的双方的主键。
　　联系的属性及两个实体的主标识形成关系表。
　　关系表的主键为两个实体主标识的组合

1:1的联系不用引入关系表（很少用）
　　在生活中，1对1的应用比较少，因为完全可以作为一张表出现。但是有可能公司的业务原因，而需要设计1对1的表结构。例如：一个公司对应一个地址，一个地址也只能对应一个公司
　　建表原则：
　　第一种是主键对应
　　将两个表的主键进行关联
　　第二种是唯一外键对应
　　在任何一方加一个外键，但是需要设置成唯一（unique），指向另一方的主键
多表设计实例
　　假设模拟一个购物网站的表结构设计
　　那购物的网站一般都包含一些基本实体：例如用户、订单、商品、分类等等。
　　设计表结构首先需要分清楚各个实体之间的关系。
　　例如用户可以产生多个订单、一种分类下可以有多个商品、一个订单中可以有多个商品、一种商品可以在多个订单中。
　　可以画图描述各个实体之间的关系

多表查询——笛卡尔积（了解）

　　多表的查询
　　笛卡尔积的概念：（了解）
　　表A表B
　　aid anamebidbname
　　a1aa1b1bb1
　　a2aa2b2bb2
　　b3bb3
　　* 查询的语法
　　select * from 表A,表B;返回的结果就是笛卡尔积。
　　结果：
　　a1aa1b1bb1
　　a1aa1b2bb2
　　a1aa1b3bb3
　　a2aa2b1bb1
　　a2aa2b2bb2
　　a2aa2b3bb3
　　select * from dept,emp;
多表查询
　　内连接（用的比较多）
　　普通内连接
　　通过使用 select * from 表1 inner join 表2 on 条件
　　隐式内连接
　　通过使用select * from 表1,表2 where 条件
　　
　　普通内连接
　　* 前提条件：需要有外键的。
　　* 提交关键字inner join ... on
　　select * from dept inner join emp on dept.did = emp.dno;
　　隐式内连接（用的是最多的）
　　* 可以不使用inner join ... on关键字
　　select * from dept,emp where dept.did = emp.dno;

多表查询—外链接
　　左外连接（把左边表所有的数据都查出来）
　　语法：select * from 表1 left outer join 表2 … On
　　右外连接 （把右边表所有的数据都查出来）
　　语法：select * from 表1 right outer join 表2 … On
　　画图描述链接的区别

　　*外连接
　　* 左外链接（看左表，把左表所有的数据全部查询出来）
　　* 前提条件：需要有外键的。
　　* 语法：使用关键字left [outer] join ... on
　　select * from dept left outer join emp on dept.did = emp.dno;
　　* 右外链接（看右表，把右表所有的数据全部查询出来）
　　* 前提条件：需要有外键的。
　　* 语法：使用关键字right [outer] join ... on
　　select * from dept right join emp on dept.did = emp.dno;
多表查询—子查询
　　在sql语言中，select…from…语为一个查询块，将一个查询块嵌套在另一个查询块中作为条件称为嵌套查询,也称为子查询.
　　外层的查询块称为父查询，内层的查询块称为子查询。
　　语法：select * from table where 条件 > (select * from table where 条件)
　　* 查询的内容需要另一个查询的结果。
　　select * from emp where ename > (select * from emp where 条件);
　　any 任意
　　all全部
　　>any大于结果的最小值
　　>all大于结果的最大值
　　作业
　　查看所有人所属的部门名称和员工名称?
　　select dept.dname,emp.ename from dept,emp where dept.did = emp.dno;
　　select d.dname,e.ename from dept d,emp e where d.did = e.dno;
　　统计每个部门的人数(按照部门名称统计，分组group by  count)
　　select d.dname,count(*) from dept d,emp e where d.did = e.dno group by d.dname;
　　统计部门的平均工资（按部门名称统计 ，分组group by  avg）
　　select d.dname,avg(salaly) from dept d,emp e where d.did = e.dno group by d.dname;
　　统计部门的平均工资大于公司平均工资的部门（子查询）
　　* 公司的平均工资
　　select avg(salaly) from emp;
　　* 部门的平均工资
　　select d.dname,avg(e.salaly) as sa from dept d,emp e where d.did = e.dno group by d.dname having sa > (select avg(salaly) from emp);