*表和表关系
一对一
一对多
多对多
*一对多
部门和员工
先创键被依赖表(dept)
create table dept(
dept_id integer primary key auto_increment
comment '部门id',
dept_name varchar(10)comment '部门名',
dept_des varchar(300)comment '部门介绍'
)comment '部门表';
员工表(关联表)
create table emp (
emp_id integer primary key auto_increment
comment '员工id',
emp_name varchar(20)comment '员工姓名',
emp_salary integer comment '员工薪资',
emp_aga integer comment '员工年龄',
d_id integer comment '部门id',
#foreign key (自己表的列名) references 被关联表(被关联的列)
foreign key (d_id)references dept(dept_id)
on UPDATE cascade on DELETE cascade
#加上on UPDATE cascade 改一起改
#on DELETE cascade 删一起删
);
给有外键的表添加数据的时候,
要添加的数据必须在被关联表(dept)中出现
insert into dept values (null,'研发部','xxxxx');
insert into dept values (null,'市场部','aaaaa');
insert into dept values (null,'人事部','bbbbb');
insert into dept values (null,'销售部','ccccc');
insert into dept values (null,'行政部','dddd');
insert into emp values (null,'崔凯明',6200,25,1);
insert into emp values (null,'孙铭泽',4800,22,2);
insert into emp values (null,'张继龙',5200,23,3);
insert into emp values (null,'丛平',6000,18,4);
insert into emp values (null,'罗宇航',5500,22,5);
insert into emp values (null,'刘思涵',4000,20,1);
insert into emp values (null,'于佳伟',7000,25,2);
*关于外键
1.外键只和update和delete有关系
2.在insert的时候,只有被关联表(dept)有相应的值,
才能在关联表(emp)中添加
3.如果在创建表的时候给外键设置on UPDATE cascade,
那么关联表(emp)的外键只可以随被关联表(dept)的改变而自动修正
4.如果 添加了on DELETE cascade
那么删除被关联表(dept)的数据之后,
关联表(emp)的相关数据一并删除
5, 删除表的时候,先 drop关联表 (emp),在drop被关联表(dept)
练习题
修改dept_id为5 的改成 dept_id为9
update dept set dept_id=9
where dept_id=5;
删除所有dept_id为 1的
delete from dept where dept_id=1;
*多表查询
同时查询两个表
select emp.emp_name,emp.emp_id,
dept.dept_name,dept.dept_id
from emp ,dept
where dept.dept_id=emp.d_id;
*笛卡尔积
多表查询的就是把不符合条件的笛卡尔积全部去掉
join............on.....
Join :两表以笛卡尔积的形式合成一个表
on :过滤笛卡尔积的条件
select emp.emp_name,emp.emp_id,
dept.dept_name,dept.dept_id
from emp join dept on emp.d_id = dept.dept_id;
select *
from emp join dept
on emp.d_id = dept.dept_id;
*常用
inner join 就是默认的join
left join 左连接(左外连接)
right join 右连接(右外连接)
left join左侧的表的所有行一定会出现在结果中
*仅做参考
full join 全连接
cross join :行成一个笛卡尔积
natural join :自然连接
自连接 :自己连自己//取代子查询的手
*ON > where >having
*where和ON
count()和count (列名)
如果是count(*):会把null值也算进来
count (列名):会把null行进行忽略
*Join on 练习
1,查询所有员工的部门名称dept和薪资
select dept.dept_name,emp.emp_salary,emp.emp_name from emp
join dept on emp.d_id = dept.dept_id;
2.查询所有员工名字及部门描述
select emp.emp_name,dept.dept_des from dept
join emp on dept.dept_id = emp.d_id;
3.查询市场部的所有员工名字和薪资
select emp.emp_name,emp.emp_salary from emp
join dept on emp.d_id = dept.dept_id
where dept.dept_name='市场部';
4.查询市场部的大于22岁员工名字及其薪资
select emp.emp_name,emp.emp_salary,dept.dept_name
from dept join emp on dept.dept_id = emp.d_id
where dept.dept_name='市场部'and emp.emp_aga>22;
5查询每个部门的名字和员工的平均工资
select dept.dept_name ,AVG(emp.emp_salary)from emp
join dept on emp.d_id = dept.dept_id
group by dept.dept_name;
6查询每个部门名字和该部门的员工人数
select dept.dept_name,count(*) from dept
join emp on dept.dept_id = emp.d_id
group by dept.dept_name
having count(*);
7.查询每个部门中,年龄大于22岁的员工的人数和部门名字
select dept.dept_name,count(emp_name)from dept
left join emp on dept.dept_id = emp.d_id
and emp_aga>22
group by dept.dept_name;
8.查询平均薪资大于5000的部门和该部门的平均年龄
select dept.dept_name,AVG(emp.emp_aga),AVG(emp.emp_salary)
from emp join dept on emp.d_id = dept.dept_id
group by dept.dept_name
having AVG(emp.emp_salary)>5000;
*多对多
学生和课程
学生表 (student)
d name cid
create table if not exists stu(
sid integer auto_increment primary key
,sname varchar(10)
)comment '学生表';
课程表(course)
id name
create table if not exists course(
cid integer auto_increment primary key ,
cname varchar(10)
)comment '课程表';
多对多的中间表
id s_id c_id
create table if not exists suc(
id integer auto_increment primary key ,
s_id integer,
c_id integer ,
foreign key (s_id)references stu(sid)
on update cascade on delete cascade ,
foreign key (c_id)references course(cid)
on update cascade on DELETE cascade
)comment '学生课程表';
*如果两个表示多对多关系
,那么必然会有一个中间表来关联两者
