MySQLday06(约束 约束查询 外键)

唯一性约束

  unique约束的字段具有唯一性 不可重复

创建用户 保证邮箱地址唯一

可以为空

drop table is exists t_user;

create table  t_user( 

id int(10),

name varchar(32) not null,

email varchar(128) unique

);

insert into t_user(id,name,email) values(1,'jack','[e-mail]');

insert into t_user(id,name) values(2,'bob'); 成功

unique 约束  约束的字段不能重复 但是可以为null  列级约束

drop table if exists t_user;

--表级约束

create table t_user(

id int(10),

name varchar(32) not null,

email varchar(128),

unique(email)

);

使用表级约束 给多个字段联合添加约束 以下程序表示 name和email两个字段联合唯一单独可以重复 但是一起不能重复

create table t_user(

id int(10),

name varchar(32) not null,

email varchar(128),

unique(email,name)

);

insert into t_user(id,name,email) values(1,'bbb','[e-mail]');成功

insert into t_user(id,name,email) values(2,'aaa','[e-mail]');成功

insert into t_user(id,name,email) values(3,'bbb','[e-mail]');成功

表级约束还可以给约束起名字 因为 以后要通过这个名字来删除这个约束

create table t_user(

id int(10),

name varchar(32) not null,

email varchar(128),

constraint t_user_email_unique unique(email,name)

);

查询约束的名字

show databases;

use information_schema;--使用该数据库

show tables;--看数据库的表格中

desc table_constraint ; --该表格专门用来存储约束信息 这个看表的结构中哪个存储了约束的名字

select constrain_name from table_constraints where table_name = 't_user';

not null 和unique 可以联合使用

被not null unique 约束的字段既不能为空 也不能重复

create table t_user(

id int (10),

name varchar(32) not null unique

);

insert into t_user(id,name) values(1,'jack');

insert into t_user(id,name) values(2,'jack');失败

insert into t_user(id) values(3');失败

主键约束 primary key 简称pk

主键涉及到的术语

主键约束

主键字段

主键值

以上的主键约束 主键字段 主键值的关系

表中的某个字段添加主键约束滞后

该字段被称为主键字段 主键字段中

出现的每一个数据都被称为主键值

给某个字段添加主键约束之后 该字段不能重复 并且也不能为空

效果和not null unique 约束相同 但是本质不同 主键约束除了可以做到 not null unique

之外 主键字段还会默认添加"索引-index"

一张表应该有主键字段 若没有 表示这张表是无效的

主键值 是当前行数据的唯一标识符 主键是当前行数据的身份证号码 一定要有主键

即使表中的两行记录相关的数据是相同的 但是由于主键值不同 我们认为这是两行完全不同的旧路

给一个字段添加主键约束 被称为单一主键

单一主键列级约束

起名

create table t_user(

id int(10) primary key,

name varchar(32)

);

单一主键表级约束

create table t_user(

id int(10) ,

name varchar(32),

primary key (id)

);

单一主键表级约束 起名

create table t_user(

id int(10) ,

name varchar(32),

constraint t_user_id_pk primary key (id)

);

给多个字段联合添加一个主键约束 被称为复合主键

复合主键只能用表级约束

create table t_user(

id int(10),

name varchar(32).

primary key (id,name)

);

create table t_user(

id int(10),

name varchar(32).

constriant t_ user_id_name_pk primary key (id,name)

);

无论是单一主键还是复合主键 一张表主键约束只能有一个

主键根据性质分类

自然主键

主键值若是一个自然数 这个自然数和当前表的业务没有任何关系 这个主键叫做自然主键

建议使用自然主键

业务主键

主键值 若和当前表中业务紧密相关 那么这种主键值被称为业务主键 当业务数据发生改变的时候

主键值通常会收到影响 所以业务主键使用较少 大部分是使用业务主键

银行卡的帐号做主键为 业务主键

在MySQL 数据库管理系统中 提供了一个自增的数字 专门用来自动生成主键值

主键值不需要用户维护 也不需要用户提供 自动生成 这个自增的数字默认从1开始

以1递增 1，2，3，4，5，6，7..... 自增的数字

create table t_user(

id int (10)primary key auto_increment,

name varchar(32)

);

insert t_user(name) values('jack');

删掉 后会断号 但没什么影响 默认情况如此

外键约束 foreign key fk

外键涉及到的术语

外键约束

外键字段

外键值

外键约束 外键字段 外键值之间的关系

某个字段添加外键约束之后 该字段称为外键字段

外键字段中的每一个数据都是外键值

外键也分为 单一外键 【给一个字段添加外键约束】和复合外键【给多个外键字段添加外键约束】

一张表中可以有多个外键字段

设计数据库来存储学生和班级信息 两种解决方案

1、学生信息和班级信息之间的关系 一个班级对应多个学生 这是典型的一对多的关系

第一种设计方案 将学生信息和班级信息存储到一张表中

学生信息表 t_student

sno(pk) sname classno cname

1 jack100beijin.....1

2 lucy100beijin....1.

3 zhangsan100beijin....2

4 ford100beijin...3

设计的缺点 数据冗余  班级名很长 

第二种解决方案 将学生信息和班级信息分开两张表存储 学生表+班级表

学生表 t_student

sno(pk) sname classno(fk)

1 jack100

2 bob100

3 zhangssan200

4 lsii300

5 allen100

6 ford100

班级表 t_class

cno(PK) cname

100 beijin...1

200 beijin...2

300 beijin...3

为了保证t_student 表中的classno 字段中的数据必须来自于 t_class表中cno字段中的数据 

有必要给t_student表中的classno字段添加外键约束 classno字段被称为外键字段 该字段中的100 200 300 被称为外键值

classno 这里是一个单一外键字段

外键字段可以为null外键字段引用别的表的字段必须具有唯一性

外键字段去引用一张表的某个字段的时候 被引用的字段必须具有unique约束

有了外键引用之后 表分为父表和子表 父表是班级表 子表是学生表 

创建表先创建父表再创建子表 删除数据的时候先删除子表中的数据 再删除父表中的数据

插入数据的时候 先插入父表中的数据 再插入子表中的数据

drop table if exists t_student;

drop table if exists t_class;

create table t_class(

cno int(3) primary key,

cname varchar(128) not null unique

);

create table t_student(

sno int(3) primary key,

sname varchar(32) not null.

classno int(3),  --在MySQL 中外键只有列级约束

constraint t_student_classno_fk  foreign key(classno) references t_class(cno)

);

insert into t_class(cno,cname) values (100,'高三1班');

insert into t_class(cno,cname) values (100,'高三1班');

insert into t_class(cno,cname) values (100,'高三1班');

insert into t_student(sno,sname,classno) values(1,'jack',100);

insert into t_student(sno,sname,classno) values(2,'zhangsan',100);

insert into t_student(sno,sname,classno) values(3,'bob',200);

insert into t_student(sno,sname,classno) values(4,'lisi',300);

insert into t_student(sno,sname,classno) values(5,'zhaoliu',100);

insert into t_student(sno,sname,classno) values(6,'wangwu',500); 添加失败 500 不存在

典型的一对多  多的一方加外键

级联更新 级联删除

添加级联更新和级联删除的时候需要在外键约束处添加

在删除父表中数据的时候 级联删除子表中的数据  on delete cascade   级联删除

mysql 对有些约束的修改比较麻烦 所以可以先删除再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(calsses_id) references t_classes(classes_id) on update cascade;

删除外键约束 alter table t_student drop foreign key t_student_classno_fk;

添加外键约束 alter  table t_student add constraint t_student_classno_fk foreign key(classno) references t_class(cno) on delete cascade;

在更新父表中数据的时候 级联更新子表中的数据  on update cascade  级联更新

以上的级联更新 和级联删除谨慎使用 因为级联操作会将数据改变或者删除 数据无价

直接修改不用 直接删再添加

 取得每个部门最高薪水的人员名称

1、取得每个部门最高薪水 按照部门分组求最大值

select deptno,max(sal) from emp group by deptno;

2、将上面的查询结果当作临时表t t 表和emp e 表进行表连接 条件 t.deptno = e.deptno and t.maxsal=e.sal

select e.ename,t.*

fro emp e

join (select deptno,max(sal) as maxsal from emp group by deptno) t

on t.deptno= e.deptno and t.maxsal = e.sal;