1.1 SQL语句:
1.1.1 多表设计:
外键约束:
约束:保证数据完整性.
单表约束:
* 主键约束:
* 唯一约束:
* 非空约束:
多表约束:
* 外键约束:foreign key. 外键约束保证多个表之间的关系的完整性!
创建一个部门表、员工表!
create database day16;
use day16;
create table dept(
did int primary key auto_increment,
dname varchar(20)
);
create table emp(
eid int primary key auto_increment,
ename varchar(20),
salary double,
dno int
);
insert into dept values (null,'研发部');
insert into dept values (null,'市场部');
insert into dept values (null,'教研部');
insert into emp values (null,'张三',3500,1);
insert into emp values (null,'李四',3500,1);
insert into emp values (null,'王五',3500,2);
insert into emp values (null,'赵六',3500,2);
insert into emp values (null,'孙七',3500,3);
insert into emp values (null,'王八',3500,3);
假设需要删除市场部!市场部的员工应该如何处理,已经没有所属部门!不应该让这种情况发生!应该将原2部门下的员工,先移除到其他的部门,然后才可以去删除部门!或者先将2号部门对应的员工删除掉,才可以删除部门!需要使用外键约束!!!
添加外键约束:
* alter table emp add foreign key(dno) references dept(did);
多表设计:实体关系.
在现实生活中,存在多个实体的,实体和实体之间是有关系存在.实体之间的关系有三种:
* 一对多:
* 一个部门对应多个员工,同时一个员工只能属于同一个部门!
* 一个用户对应多个订单,同时一个订单只能属于同一个用户!
***** 建表原则:在多的一方创建一个字段,作为外键用于指向一的一方的主键!!!
* 多对多:
* 一个学生选择多门课程,一个课程也可以被多个学生选择!
***** 建表原则: 需要创建第三张表,在第三张表中,创建两个字段,这两个字段分别作为外键用于指向多对多的双方的主键
* 一对一:
* 一个公司只能对应一个地址,一个地址只能对应一个公司.
* 实际开发中一对一的关系应用比较少.(可以创建成一张表).
* 两种方式描述:
* 唯一外键对应:
* 假设一对一的双方,任意的一方是多.需要在多的一方添加一个字段作为外键指向一的一方的主键.外键设置为唯一的.
* 主键对应:
* 在任意的一方的主键上添加外键约束执行另一方的主键
1.1.2 多表的SQL(查询)
交叉连接:基本不使用.
语法:
* 假设两个表A,B.
* select * from A,B;
* 两个表的交叉连接:得到的记录是两个表的笛卡尔积.
内连接:经常使用.
内连接:查询的是两个表的交集部分.
语法:select * from A inner join B on 条件;
如:
* select * from dept inner join emp ondept.did = emp.dno;
***** 在做内链接的查询的时候 inner 关键字可以省略!!!
* select * from dept join emp on dept.did = emp.dno;
* 隐式内连接:看不到join关键字,但是查询的结果和内连接一致!!!
* select * from dept,emp where dept.did = emp.dno;
外连接:
左外连接:左边表的全部以及两个表的交集!
语法:select * from A left outer join B on 条件;
* outer可以省略的.
如:
* select * from dept left outer join emp on dept.did = emp.dno;
右外连接:右边表的全部及两个表的交集!
语法:select * from A right outer join B on 条件;
* outer可以省略的.
如:
* select * from dept right outer join emp on dept.did = emp.dno;
子查询:
一条SQL语句需要依赖另外一条SQL语句.
* 查询按照部门编号统计平均工资:
* select dno,avg(salary) from emp group by dno;
* 按部门名称统计平均工资:
* select d.dname,avg(salary) from dept d,emp e where d.did = e.dno group by d.dname;
* 查询员工的信息.员工的工资大于2号部门的平均工资!
* 查询2号部门的平均工资!
* select avg(salary) from emp where dno=2;
* 查询员工的工资大于2部门:
* select * from emp where salary > 2号部门的平均工资;
* select * from emp where salary > (select avg(salary) from emp where dno=2); ---子查询.
* 在子查询中
* any:任意的.
* all:所有的.
* 查询员工的信息.员工的工资大于任意部门的平均工资!
* select * from emp where salary > any (每个部门的平均工资)
* 每个部门的平均工资:
* select avg(salary) from emp group by dno;
* select * from emp where salary > any (select avg(salary) from emp group by dno)
* 查询员工信息.员工的工资大于所有部门的平均工资!
* select * from emp where salary >= all (select avg(salary) from emp group by dno);
* 查询市场部下有哪些员工名称!
* select e.ename from dept d,emp e where d.did=e.dno and d.dname='市场部';
* 查询1号员工所属的部门名称!
* select d.dname from dept d,emp e where d.did=e.dno and e.eid = 1;
* 创建多对多关系:
create table student(
sid int primary key auto_increment,
sname varchar(20)
);
create table course(
cid int primary key auto_increment,
cname varchar(20)
);
create table stu_cour(
sno int,
cno int,
foreign key (sno) references student(sid),
foreign key (cno) references course(cid)
);
insert into student values (null,'张三');
insert into student values (null,'李四');
insert into course values (null,'Java');
insert into course values (null,'PHP');
insert into course values (null,'.NET');
insert into course values (null,'C语言');
insert into stu_cour values (1,1);
insert into stu_cour values (1,2);
insert into stu_cour values (1,3);
insert into stu_cour values (2,2);
insert into stu_cour values (2,4);
* 查询张三这个学生所选的课程名称?
* select c.cname from student s,stu_cour sc,course c where s.sid=sc.sno and c.cid = sc.cno and s.sname='张三';
* 查询选PHP这门课程有哪些学生名称?
* select s.sname from student s,stu_cour sc,course c where s.sid=sc.sno and c.cid=sc.cno and c.cname='PHP';
* 分析购物网站:
* 客户、商品、订单、分类