11.多表设计
11.1.一对多
一对多和多对一是一回事，所以就不再提多对一这个词。一对多的概念是一个对象A会对应多个对象B，而从B的角度看，一个对象B只会对应一个对象A。比如说班级和学生就是一对多关系。一个班级对应多个学生，一个学生只会对于一个班级。
场景:部门对员工，国家对省份，省份对城市，用户对订单，商品分类对商品
部门表（dept） 员工表(emp)
did dname eid name address salary did
部门表和员工表之间要想产生关系就必须有主外键关系。那么我们来看一下外键怎么在多的一方表中定义吧
定义外键约束constraintforeignkey(did)referencesdepartment(did)
新建部门表department (id,name) 通过外键约束建立与员工表employee关系
表结构创建
create table dept(did int primary key,name varchar(100));
create table emp(eid int primary key auto_increment,name varchar(20),salary double(6,2),did int,constraint fk_id foreign key (did) references dept(did));
insert into dept values(1,'人事部');
insert into dept values(2,'教学部');
insert into dept values(3,'会计部');
insert into emp values(1,'张三',1000.01,1);
insert into emp values(2,'李四',1000.03,1);
insert into emp values(3,'王五',1000.02,2);
insert into emp values(4,'赵六',1000.03,3);
insert into emp values(5,'周七',1.01,3);
练习:
-- 查询出人事部所有的员工信息
-- 写法一:子查询
select * from emp where did=(select did from dept where dname="人事部");‘
-- 写法二:内连接
select * from dept d,emp e where d.did=e.did and d.dname="人事部";
-- 查询出张三所在的部门
select * from dept where did=(select did from emp where ename="张三");
select * from dept d,emp e where d.did=e.did and e.ename="张三";
-- 查询出每个部门的总人数
select d.dname,count(*) from dept d,emp e where d.did=e.did group by e.did;
-- 查询出人事部的总人数
select d.dname,count(*) from dept d,emp e where d.did=e.did group by e.did having d.dname="人事部";
-- 查询出每个部门的总工资
select d.dname,sum(salary) from dept d,emp e where d.did=e.did group by e.did;
-- 查询出每个部门的平均工资
select d.dname,avg(salary) from dept d,emp e where d.did=e.did group by e.did;
11.2.多对多
场景:学生对老师，员工对项目
学生表(student) 包含列 sid sname
中间表(st) 包含列 sid tid
老师表(teacher) 包含列 tid tname tsubject
表结构创建:
create table student(sid int primary key auto_increment,sname varchar(20));
create table teacher(tid int primary key auto_increment,tname varchar(20),tsubject varchar(20));
create table st(sid int,tid int,constraint fk_sid foreign key (sid) references student(sid),constraint fk_tid foreign key (tid) references teacher(tid));
insert into student values(1,"张三");
insert into student values(2,"李四");
insert into student values(3,"王五");
insert into student values(4,"赵六");
insert into teacher values(1,"王老师","数学");
insert into teacher values(2,"李老师","英语");
insert into teacher values(3,"赵老师","语文");
insert into teacher values(4,"刘老师","物理");
insert into st values(1,1);
insert into st values(1,2);
insert into st values(2,3);
insert into st values(3,1);
insert into st values(3,2);
insert into st values(3,4);
练习:
#查询出王老师教过的所有学生
select * from student s,st,teacher t where s.sid=st.sid and t.tid=st.tid and t.tname="王老师";
-- 查询出教过张三的所有老师
select * from student s,st,teacher t where s.sid=st.sid and t.tid=st.tid and s.sname="张三";
-- 查询出每个老师所教学生数量
select tname,count(*) from st,teacher t where st.tid=t.tid group by st.tid;
-- 查询出王老师所教学生数量
select tname,count(*) from st,teacher t where st.tid=t.tid group by st.tid having t.tname="王老师";
11.3. 一对一
场景:一个学生对应一个学生档案材料
create table user (uid int primary key auto_increment,uname varchar(20),idcard varchar(30));
create table user_detail(idcard varchar(20) primary key,address varchar(20),age int,salary float,gender varchar(20));
insert into user values(0,"张三","37119199010081010");
insert into user values(0,"李四","37119199010081011");
insert into user values(0,"小红","37119199010081012");
insert into user values(0,"小花","37119199010081013");
insert into user_detail values("37119199010081010","北京",28,12000.01,"男");
insert into user_detail values("37119199010081011","上海",40,13000.01,"男");
insert into user_detail values("37119199010081012","北京",40,13000.01,"女");
insert into user_detail values("37119199010081013","上海",40,20000.01,"女");
练习:
#查询每个用户的所有信息
select uid,uname,u.idcard,address,age,salary,gender from user u,user_detail ud where u.idcard=ud.idcard;
#查询张三的所有信息
select uid,uname,u.idcard,address,age,salary,gender from user u,user_detail ud where u.idcard=ud.idcard and uname="张三";
#查询北京的用户
select uid,uname,u.idcard,address,age,salary,gender from user u,user_detail ud where u.idcard=ud.idcard and ud.address="北京";
12. 连接查询
12.1. 笛卡尔积查询
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y。对于数据库的笛卡尔积查询，指的是两张表查询的时候，每一行数据进行连接查询。 关键词是cross join，也可以将两张表串连起来省略不写。
select * from customer cross join orders;
select * from customer,orders;
12.2. 内连接
内连接是A表的所有行交上B表的所有行得出的结果集,返回两张表满足条件的所有数据
select * from customer c inner join orders o on c.cid=o.cid;
select * from customer c,orders o where c.cid=o.cid;
12.3. 左外连接
左外连接是A表的所有行匹配上B表得出的结果集,返回满足条件的所有记录，同时返回左表中剩余的其他记录
select * from customer c left joinorders o onc.cid=o.cid;
12.4. 右外连接
右外连接是B表的所有行匹配上A表得出的结果集,返回满足条件的所有记录，同时返回右表中剩余的其他记录
select * from customer c right joinorders o onc.cid=o.cid;
12.5. 内外连接的区别
内连接返回两张表满足条件的所有数据
外连接除了返回两张表满足条件的所有数据,还会返回左表或者右表不满足条件的数据
12.6. 左外连接和右外连接的区别
左外连:返回满足条件的所有记录，同时返回左表中剩余的其他记录
右外连:返回满足条件的所有记录，同时返回右表中剩余的其他记录
练习案例:
#统计出每个省份包含的城市信息
select p.*,c.cname from province p left join city c on p.pid=c.pid;
#统计出每个城市包含的人员信息
select c.cid,c.cname,u.uname,u.salary from city c left join user u on c.cid=u.cid;
#统计出每个省份的人员信息
select p.*,c.cname,u.uname,u.salary from province p left join city c on p.pid=c.pid left join user u on c.cid=u.cid;
#统计出每个城市的总工资，平均工资,人数
select c.cname,sum(u.salary),avg(u.salary),count(u.uid) from city c left join user u on c.cid=u.cid group by c.cid;
#统计出每个省份的人员个数 这里不能用 count(*)
select p.pname,count(u.uid) from province p left join city c on p.pid=c.pid left join user u on c.cid=u.cid group by p.pname;
13. 存储过程
概述：带有业务逻辑的sql语句。里面有流程控制语句 if条件判断，while循环等等
特点：
1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！
2）移植性很差！不同数据库的存储过程是不能移植。
创建存储过程:create procedure存储过程名字 (参数)
create PROCEDURE delete_emp(IN empno INTEGER)
begin
delete from emp where empno = empno;
end
调用存储过程
call存储过程名字(参数)
删除存储过程
drop procedure存储过程名字
14. 什么是事务
作为单个逻辑工作单元执行的一系列操作,由多条语句组成的集合,要么全部成功,要么全部失败,数据库通过事务来保证数据的一致性
事物广泛的运用于订单系统，银行系统等多种场景中。
举例：银行转账操作
Begin； 【开启事务】
commit； 【都成功手动提交】
rollback； 【任何一部分失败了 回滚 返回最初状态】
正常提交演示
begin;
update account set money=money-1000 where name="zhangsan";
update account set money=money+1000 where name="lisi";
commit;
回滚场景
begin;
update account set money=money-1000 where name="zhangsan";
update account set money=money+1000 where name="lisi";
rollback;
15. 备份、恢复数据库
备份数据库表中的数据
cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql
例如: mysqldump -u root -p day12 > c:\day12.sql
恢复数据库
source 文件名.sql // 在mysql内部使用
mysql –u 用户名 p 数据库名 < 文件名.sql // 在cmd下使用
好啦！对于数据库的知识点总结就先到这里吧！后期会给大家安排一些面试经常碰到的一些数据库题目和答案和大家分享！
