课程7 约束关系

一.主键约束(唯一标识)

1.非空2.唯一3.引用
创建表时指定主键的方式

create table stu(
id char(6) primary key,
name varchar(20),
);

create table stu(
id char(6) ,
name varchar(20),
primary key (id)
);

修改表时指定主键
删除主键

alter table stu add primary key(id)

alter table stu drop primary key

二.主键自增长

主键列的特性是唯一不为空,一般会指定主键类型是int,设置其自动增长

创建表时指定主键自增长

create table stu(
id int primary key auto_increment,
name varchar(20),
);

alter table stu change id id int auto_increment;
//修改表时设置主键自增长

alter table stu change id id int;
//修改表 删除主键自增长

测试主键自增长
insert into stu values(null,zhangsan’);

三.非空和唯一约束

create table stu(
id char(6) primary key not null,//只是非空
name varchar(20) not unique,//只是唯一
age int,not null unique,//非空且唯一
);

四.模型

对象模型:可以双向关联,而且引用的是对象,而不是主键
关系模型:只能多方引用乙方,而且引用的只是主键而不是一整行记录

对象模型:在java中是domain 例如User Student.
is a
has a(关联) 关系模型
>1对1
>1对n
>n对n
use a
关系模型:在数据库中的表

五.外键约束

1.外键必须是另一主键的值(外键必须引用主键)
2.外键可以重复
3.外键可以为空
4.一张表可以有多个外键

CONSTRAINT fk_emp_dept FOREIGN KEY(dno)REFERENCES dept(deptno)
//把emp和dept里面的 dno和deptno建立外键约束
其中deptno为主键
dno为外键
也可以用
alter table emp add CONSTRAINT fk_emp_dept FOREIGN KEY(dno)REFERENCES dept(deptno);来实现

CREATE TABLE dept(	
deptno INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(50)

);

INSERT INTO dept VALUE(10,'研发部');
INSERT INTO dept VALUE(20,'人力部');
INSERT INTO dept VALUE(30,'财务部');
SELECT * FROM dept;

CREATE TABLE emp(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(50),
dno INT,
CONSTRAINT fk_emp_dept FOREIGN KEY(dno)REFERENCES dept(deptno)
);
INSERT INTO emp(empno,ename)VALUES(NULL,'张三');
INSERT INTO emp(empno,ename,dno)VALUES(NULL,'李三',10);
SELECT * FROM emp;

①一对一关系

CREATE TABLE husband(
hid INT PRIMARY KEY AUTO_INCREMENT,
hname VARCHAR(50)
);
INSERT INTO husband VALUE(NULL,'张三');
INSERT INTO husband VALUES(NULL,'李四');
SELECT * FROM husband;
CREATE TABLE wife(
wid INT PRIMARY KEY AUTO_INCREMENT,
wname VARCHAR(50),
CONSTRAINT fk_wife_husband FOREIGN KEY(wid) REFERENCES husband(hid)
);

INSERT INTO wife VALUES(1,'张三的老婆');
INSERT INTO wife VALUES(2,'李四的老婆');
SELECT * FROM wife;

②多对多查询

create table student(
sid int primary key auto_increment,
sname varchar(20)
);

create table teacher(
tid int primary key auto_increment,
tname varchar(20)
);

create table stu_tea(
sid int,
tid int,
constraint fk_student foreign key(sid) references student(sid),
constraint fk_teacher foreign key(tid) references teacher(tid)
);

insert into student value(null,'张三');
INSERT INTO student VALUE(NULL,'李四');
INSERT INTO student VALUE(NULL,'王五');

INSERT INTO teacher VALUE(NULL,'刘老师');
INSERT INTO teacher VALUE(NULL,'周老师');
INSERT INTO teacher VALUE(NULL,'吴老师');

select * from student;
select * from teacher;

insert into stu_tea values(1,1);
INSERT INTO stu_tea VALUES(2,1);
INSERT INTO stu_tea VALUES(3,1);

select * from stu_tea;

六.多表查询

Ⅰ.合并结果集

select * from 表1
union all//全部合并 不去重
select * from 表2

select * from 表1
union //去重
select * from 表2

Ⅱ.连接查询
分类
内连接
外连接
>左外连接
>右外连接
>全外连接(mysql不支持)
自然链接(简化方式)
1.内连接

	方言:select * from 表1 别名1,表2 别名2 where 别名1.xx=别名2.xx
	标准:select * from 表1 别名1 inner join 表2 别名2 on 别名1.xx=别名2.xx
	自然:select * from 表1 别名1 natural join 表2 别名2
//打印所有员工的姓名 工资 部门名称
select  emp.ename,emp.sal,dept.dname
from demp,det
where emp.deptno=dept.deptno;

2.外连接

/*外连接有一主一次
左外即左表为主
即emp为主,主表中的所有记录无论满不满足条件都打印出来
当不满足时,右表用null补位
*/
select  emp.ename,emp.sal,ifnull(dept.dname,'无部门') as dname
from emp e left outer join dept d
where e.deptno=d.deptno;

Ⅲ.子查询

select * from stu where age=(select max(age)from stu);
//查询年龄最大的学生的详细信息

//查询工资大于所有部门号大于30的员工的员工信息
select * from emp where sal> all (select sal from emp where deptno>30)

多行单列 in all any 都可以修饰
单行多列