MySQL学习第三弹——约束与多表查询详解

约束

• 概述
• 约束展示
• 外键约束

约束是作用于表中字段上的队则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确，有效和完整性。

-- ---------------------------------------------------------------约束展示-------------------------------------
create table user(
    id     int key auto_increment             comment '主键',
    name   varchar(10) not null unique        comment '姓名',
    age    int check ( age > 0 && age <= 120) comment '年龄',
    status char(1) default '1'                comment '状态',
    gender char(1)                            comment '性别'
) comment '用户表';

-- 插入数据
insert into user(name, age, status, gender) values ('Tom1', 19, '1', '男'), ('Tom2', 25, '0', '男');
insert into user(name, age, status, gender) values ('Tom3', 19, '1', '男');

insert into user(name, age, status, gender) values (null, 19, '1', '男');
insert into user(name, age, status, gender) values ('Tom4', 18, '1', '男');

insert into user(name, age, status, gender) values ('Tom5', -1, '1', '男');
insert into user(name, age, gender) values ('Tom5', 18, '男');

-- 约束
-- 1、概述
-- 2、约束演示
-- 3、外键约束
-- 约束是作用于表中字段上的队则，用于限制存储在表中的数据。
-- 目的：保证数据库中数据的正确，有效和完整性
-- 分类
-- 外键约束
-- 外键用来让两张表的数据之间建立联系，从而保证数据的一致性和完整性。
-- 外键的删除和更新行为

-- ---------------------------------------------------------约束（外键）--------------------------------------
-- 准备数据
create table dept(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name) values (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办');

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'
) comment '员工表';

insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
            (1, '金庸', 66, '总裁', 20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2,1),(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2,1);

-- 添加外键
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 ;

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on UPDATE set null on delete set null ;

多表查询

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多
• 多对多
• 一对一
  
  
  
  

-- 多表查询
-- 多表关系
-- 项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，
-- 基本上分为三种：
-- 一对多
-- 多对多
-- 一对一
-- 1、一对多
-- 2、多对多
-- 3、一对一
-- ----------------------------------------------------多表关系演示-------------------------
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 ccourse(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into ccourse 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 ccourse(id),
    constraint fk_studentid foreign key (studentid) references student(id)
) comment '学生课程中间表';
insert into student_course values (null, 1, 1),(null, 1, 3), (null, 2, 2),(null, 2, 3),(null, 3, 4);

-- ----------------------------------------一对一------------------------------------------------------------------------
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 '用户基本信息表';

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);

-- 多表查询
-- 指从多张表中查询数据

-- 数据准备

create table dept(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name) values (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');

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'
) comment '员工表';

insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
            (1, '金庸', 66, '总裁', 20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2,1),(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2,1),
            (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1,3),(8, '周芷若', 19, '会计', 48000, '2006-06-02', 7,3),
            (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7,3),(10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1,2),
            (11, '鹿杖客', 56, '职员', 3750, '2006-10-13', 10,2),(12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10,2),
            (13, '方东白', 19, '职员', 5500, '2009-02-12', 10,2),(14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1,4),
            (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14,4),(16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14,4),
            (17, '陈友谅', 42, null, 2000, '2011-10-12', 1,null);

-- 查询
select * from emp;
select * from emp, dept;
-- 多表查询，查到的是102条数据（17*6=102）笛卡尔积
-- 笛卡尔积指的是在数学中，两个集合，集合A和集合B的所有组合情况。（在多表查询时，需要消除笛卡尔积）已截图！
-- 使用以下语句消除笛卡尔积
select * from emp, dept where emp.dept_id = dept.id;