【约束、多表关系和多表连接】

2023-10-18 19:52:40

文章目录

约束

约束就是对字段进行限制，从而保证数据的准确、有效和完整。

多个约束间直接空格分隔开

【约束、多表关系和多表连接】

# 创建student表并为每个字段设置约束
create table student(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    age tinyint unsigned check ( age > 0 and age <=120),
    gender char(1) default '男'
);

# 插入时，如果不插入id字段，则自动从1开始递增。如果不插入性别字段，则默认为‘男’
insert into student(name, age) values ('penrose',18);

#此处插入的数据“年龄”列不符合约束条件，则此次操作不会占用序列
insert into student(name, age, gender) values ('cxf',120,'女');

#此处插入的数据与上一条插入的数据重复，则插入数据失败，且会占用一个序列
insert into student(name, age) values ('penrose',18);

注：

  1. 只能为一个字段设置自增长，多搭配主键使用。
  2. 为字段设置自增长约束时，不必为其插入数据。如果插入序列后，则下一条数据就会依次往后增加。
  3. 如果数据插入失败后，再次插入新数据，则序列就会出现断层。这里的插入失败意思是所有插入的数据都符合字段的约束条件，但是表中已有此记录，则此次的序列就会被占用。但是如果插入的数据出现data truncation（数据截断），则不会占用序列。

外键约束

外键能将两张表之间的数据建立连接，从而保证数据的一致性和完整性。
外键存在时，至少存在两张表。
被关联的表是父表（外键所关联的表），需要关联的表是子表
一旦被关联，主表中的数据默认不可删除

外键的删除/更新行为
【约束、多表关系和多表连接】

注：如果想要删除主表中的数据，就需要设置删除/更新行为

#新建表时添加外键约束
create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age  int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID',
    constraint fk_emp_dept_id foreign key (dept_id) references dept(id)
)comment '员工表';

#为已有表添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 父表(父表字段名);

#删除外键约束
alter table drop foreign key 外键名称

#为emp表添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

#删除外键
alter table emp drop foreign key fk_emp_dept_id;

#添加外键约束，默认情况下如果不设置外键的更新/删除行为，主表中的数据不能被删除。
#为主表设置级联删除和更新
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on UPDATE cascade on DELETE  cascade ;

【约束、多表关系和多表连接】
设置外键行为后，可以通过可视化工具查看到相应的行为。
【约束、多表关系和多表连接】

多表关系

每张表各自对应一些业务逻辑，而业务逻辑之间有相互的联系，所以表与表之间也存在相应的联系。
表之间的关系分为三种：

  1. 一对多（部门与员工、教室与学生），一的一方作为主表，多的一方作为子表，在多的一方设置外键（在员工表/学生表中设置外键）
  2. 多对多（课程和学生），通常需要建立第三张表，中间表中至少要包含两个外键
  3. 一对一，多用于单表的拆分。这里的拆分意思是在创建表的时候一张完整的表分两次创建。为了维护一对一间的关系，就需要在任意一方添加外键。同时为了限定他们之间的一对一的关系，就需要为外键设置unique约束。

一对多

#创建员工表（一）
create table dept(
    id int primary key auto_increment comment 'ID',
    name varchar(50) not null comment '部门名称'
)comment '部门表';

#创建emp表（多）
create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age  int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID',
    constraint fk_emp_dept_id foreign key (dept_id) references dept(id)
)comment '员工表';

多对多

#创建学生表（多）
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');

#创建课程表（多）
create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');

#创建学生_课程表（中间表）
create table student_course(
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';

【约束、多表关系和多表连接】

一对一

#原本 tb_user和 tb_user_edu中的字段都是在一起的，但为了提升查询效率就进行了拆分
create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1: 男 , 2: 女',
    phone char(11) comment '手机号'
) comment '用户基本信息表';

#为了让两表之间建立联系，就需要在某一表中设置外键进行关联，同时两表之间是一对一的关键，则需要将外键设置unique约束
create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';


insert into tb_user(id, name, age, gender, phone) values
        (null,'黄渤',45,'1','18800001111'),
        (null,'冰冰',35,'2','18800002222'),
        (null,'码云',55,'1','18800008888'),
        (null,'李彦宏',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
        (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
        (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
        (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
        (null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

【约束、多表关系和多表连接】
【约束、多表关系和多表连接】
【约束、多表关系和多表连接】

多表查询

概述

笛卡尔积

简单理解就是两个集合的乘积

注：因为多表查询时会出现笛卡尔积现象，所以最重要的操作就是消除多余的笛卡尔积

【约束、多表关系和多表连接】

select * from emp,dept;

【约束、多表关系和多表连接】

多表查询

连接查询（join on）

内连接

内连接，查询两个集合中相交的部分

#隐式内连接（不包含关键词join on）
select 字段列表 from 表1,表2 where 条件;

#显示内连接（包含关键词join on）
select 字段列表 from 表1 [inner] join 表2 on 连接条件;

#查询员工的姓名以所属部门
#隐式内连接实现
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

#显示内连接
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

外连接

左（右）外连接，查询左（右）表中的数据以及两表中的交集部分，但多采用左连接，因为左连接可以将右连接替换。


#查询emp表中的所有数据和对应的部门信息
select e.*,d.name from emp e left join dept d on e.dept_id = d.id;

#查询dept中的所有数据和对应的员工姓名
select d.*,e.name from dept d left join emp e on d.id = e.dept_id;
上一篇：使用python-iptables库设置linux防火墙权限，只允许特定ip访问指定端口，二次验证的web服务


下一篇：20220227 省选模拟赛