​ MySQL基础2.0

一：约束：

1.约束概述

（1.）简介：

1.约束概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据 2.保证数据库中数据的正确性、有效性、和完整性

（2.）约束分类：

-- 1.约束概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
-- 2.保证数据库中数据的正确性、有效性、和完整性
-- 3.  约束分类：                                                        关键字（！！！！！！！）
--    1.非空约束    用来限制字段的数据不能为null                              not null
--    2.唯一约束    保证该字段的所有数据都是唯一的，不重复的（用户名，密码）        unique
--    3.主键约束    主键是一行数据的唯一标识，要求非空且唯一                     primary key
--    4.默认约束    保存数据时，如果未指定该字段的值，则采用默认值                default
--    5.检查约束    保证字段值满足某一个条件                                  check
--    6.外键约束    用来让两张表的数据之间建立连接，保证数据的一致性和完整性（必须是两张表）  foreign key
-- 4.！！！约束：a：是作用于表中的字段上的，可以在建表和修改表的时候为表建立约束；：直接加在字段后面即可！！！
    --         b：一个字段可以添加多个约束，使用“  ”隔开；
--             c：直接加在字段后面即可

2.演示约束使用：

(1.)

--  1.案例演示  如何在创建表的时候指定约束！！！：eg:not null,unique,check,默认,primary
--    a:根据需求完成表的创建：
-- 字段名      字段含义       字段类型          约束条件                 自己分析约束的关键字：
-- id         ID的唯一标识    Int            主键，并且自动增长          primary key auto_increment(！空格间隔！)
-- name       姓名          varchar(10)     不为空，并且唯一            not null unique
-- age        年龄           int            大于0，并且小于120          check
-- status     状态           char(1)        如果没有指定值，默认1        default
-- gender     性别           char(1)         无

-- b:创建表（也可以使用图像化界面创建表：即右键tables，new table，在框中创建勾选约束即可！！！！！！！）
--  打开sjh数据库
use sjh;
-- 创建表
create table user(
    id int primary key auto_increment comment 'ID主键',
    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 '用户表' ;
-- 插入数据
-- 注意！！！：id是自增，可以不用插入，所以我们就不要指定id值了，它会自己增加！！！；当然也可以自己填写指定；！！
insert into user(name,age,status,gender) values('kunkun',18,'好','男'),('kunkun1',19,'几','男');
-- 之后我们发现user表中已经有了两条数据，且id自动为1和2；且id字段列有一个：“黄色🔑”-->:表示主键；
insert into user(name, age, status, gender) values ('kunkun4',17,'j','男');
insert into user (id, name, age, status, gender) values (4,'kunkun3',17,'好','男');

insert into user (id, name, age, status, gender) values (4,'kunkun3',17,'好','男');
-- 1.会报错因为我们设置的name是not null unique--》：唯一约束！
-- 不能重复：因为已经有了kunkun3了，蓑衣会报错；
-- 且不能为null
insert into user ( name, age, status, gender) values ('kunkun5',123,'好','男');
-- 2.因为有“ 检查约束”，age只能在0~120之间；--保障数据正确性
insert into user ( name, age,  gender) values ('kunkun5',90,'男');
-- 3如果我们没有传入statue，会默认约束：即自动传入’1‘；

(2.)外键约束

 1.外键：外键用来让两张表的数据之间建立联系，从而保证数据的一致性和完整性；
 2.具有外键的表成为子表，外键所关联的表称为父表。或者（主表，从表）；

演示：

--     a.创建部门表：(主表/父表！！！)
use sjh;
create table dep(
    id int primary key auto_increment comment 'ID', -- id:主键约束且自增！！！
    name varchar(10) not null comment '部门名称'
) comment '部门表' ;
insert into dep(name) values ('研发部'),('市场部'),('财务部'),('销售部'),('总经办');
--   b.创建员工表（从表/子表！！！）
create table emp(
    id  int auto_increment primary key comment 'ID', -- id:主键约束自增！！
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导',
    dapt_id int comment '部门ID' -- 关联部门表的ID！！！
) comment '员工表' ;
insert into emp(id, name, age, job, salary, entrydate, managerid, dapt_id) values (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
                    (2,'张无忌',20,'项目经理',10000,'2005-11-01',1,1 ), (3,'张',25,'k',10000,'2005-11-05',2,1 ), (4,'无',27,'k',10000,'2005-11-11',2,1 ),
                     (5,'l',32,'k',10000,'2005-11-14',3,1 ), (6,'kun',30,'程序员',10000,'2008-11-01',2,1 );

-- 添加外键！！！：外键设在子表/从表中！！！！！！！
    -- 具有外键的表为子表，外键所关联的表为父表；！！！！


-- c.创建完两个表之后，两个表之间并没有建立起来联系关联。比如我们删掉部门表，但员工表emp中所属部门仍然存在（因为两个表没有相互关联，不影响）
--  但我们要保证数据的一致性和完整性（即dep表的变动会影响到emp表）-------->:所以我们要建立外键关联！！！！！！！！

-- 添加外键的语法
-- （1.）创建表的时候直接添加
-- create table 表名（
-- 字段名 数据类型....
-- .....
-- [comment] [外键名称] foreign(外键字段名) references 主表（主表列名）；
-- ）
--
-- （2.）表创建好以后额外增加：
-- alter table 表名  add constraint 外键名称 foreign key(外键字段名) reference  主表（主表列名）；
--    指定表名         添加约束       自由定义    外键 关联的字段            关联哪一个表的主键！！！
-- eg：
alter table emp add constraint fk1  foreign key (dapt_id) references dep(id);
-- 这时添加外键以后就不能删除比如：部门id，因为外键和主键相关联，相互影响！！！

（3.）外键的删除更新：

1.使用背景：

使用时机：在添加外键时来指定他们的删除和更新规则！！！！！！！

-- 1.通过外键使emp表的dapt_id与dep表建立了外键关联后，当我们删除父表数据时，它会判断当前父表的这条数据在子表当中是否存在关联关系，如果存在则不允许被删除
--  这样就保证了数据的完整性

2.外键的删除/更新行为

-- no action     当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允删除/更新。 (与 RESTRICT 一致) 默认行为
-- restrict      当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为
-- cascade          当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
-- set null       当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。
-- set default    父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)
-- 注意：前两个：no action，restrict是默认行为！即当我们在父表中删除/更新对应记录时, ...会自动执行；

3.语法：

- alter table 表名  add constraint 外键名称 foreign key(外键字段名) reference  主表（主表列名）on update cascade on delete cascade；

-- （1）.演示cascade
-- 解释1：更新：on update cascade：当我们去修改/更新父表数据的时候，首先检查该记录是否有对应的关联外键，如果有也会更新外键所在的子表中的记录！！！
alter table emp add constraint fk1  foreign key (dapt_id) references dep(id) on update cascade on delete cascade;

-- 解释2：删除：on delete cascade：当我们去删除父表的数据时，首先检查该记录是否有对应的关联外键，如果有也会删除外键所在的子表中的记录！！
-- 即当我们删除/更新dep表中（父表）中的数据时，....子表也相应的数据也会发生变化！！！！！！！！！！！！


-- （2.）演示set null
alter table emp add constraint fk1  foreign key (dapt_id) references dep(id) on update set null on delete set null ;
-- ！！！创建外键的同时指定删除更新规则
-- eg：删除dep表中id为1的数据：---》：id为1的数据在dep表中被set null，且在emp表中id为1的数据条也被set null了！！！！！！！！！！！


    -- 总结:主键是一张表当中的唯一标识 ：非空且唯一；如果要实现主键的自动增长（auto_increment）;
    -- 一旦涉及到外键：必须是两张表，用来保证数据的完整性和正确性
    -- ---------------------------------------------------------------------------------------------
--  也可以通过图型化界面修改：右击表emp--modify table!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

二：多表查询（部分）

一：多表关系：

1.分类

由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
--   一对多(多对一)
--    多对多
--    一对一

2.介绍：

1.一对多：

（1.） 一对多
#      案例: 部门 与 员工的关系
#      关系: 一个部门对应多个员工，一个员工对应一个部门
#      实现: 在多的一方建立外键，指向一的一方的主键；！！！！
   --       eg：员工为多的一方，部门为一的一方；

2.多对多：

#      案例: 学生 与 课程的关系
#      关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
#      实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
    --

演示：

 -- 学生表：
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');


-- 因为学生表和课程表是多对多的关系：所以要建立中间表，然后中间表和要和其它两张表主键相关联
-- 第三张中间表！！！！！！！！！！！1
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 '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),
(null,2,3),(null,3,4);

3.一对一：

eg：用户与用户详情的关系
--       关系：一对一关系，多用于单表拆分，将一张表的基础字段放在另一张表中，其它详情字段放在另一张表中，以提升操作效率；
--       实现：！！！在任意一方加入外键，关系另外一方的主键，并设置外键为唯一的（unique）

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

二：多表查询概述：

-- 1.分类：
--   一：连接查询：（1.）内连接 ：相当于查询A、B表交集部分的数据

--              （2.）外连接 ：
--                   a：左外连接：查询左表的所有数据，以及两张表交集部分的数据
--                   b：右外连接：查询右表的所有数据，已经两张表交集部分的数据
--              （3.）自连接：当前表与自身的连接查询，子连接必须使用别名！！
--   二：子查询

三：多表查询介绍：

A：连接查询

1.内连接：

内连接：相当于查询A、B表交集部分的数据
--   1.内连接查询语法：
      -- a：隐式内连接：select 字段列表 from 表1,表2.. where 条件;
      -- b：显示内连接：select 字段列表 from 表1 [inner] join 表2 on连接条件;
--
-- 2.内连接演示：
    -- a：查询每一个员工的姓名，及相关联的部门的名称(隐式内连接实现)
       -- 分析：两个表结构：emp，dep 及 通过连接条件消除笛卡尔积
    select emp.name , dep.name from dep,emp where emp.id=dep.id;
       -- 也可以起别名来简化sql语句：增强可读性，就不能再使用原名！！！
    select e.name ,d.name from emp e ,dep d where e.id=d.id;


    -- b：查询每一个员工的姓名，及相关联的部门的名称(显示内连接实现)
    select e.name,d.name from emp e inner join dep d on d.id=e.id;

2.外连接：

外连接：
-- 1.   a：左外连接：查询左表的所有数据，以及两张表交集部分的数据(用的多！！！！！！！！！！！)
    -- 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;
    -- 查询表1所有的数据，以及两张表交集部分的数据；

--     b：右外连接：查询右表的所有数据，已经两张表交集部分的数据
    -- 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;
    -- 查询表2所有的数据，以及两张表交集部分的数据
-- 2.案例演示：
-- a:查询emp表的所有数据，和对应的部门信息---（左外连接）
    -- 分析：左外连接：表结构：emp，dep，||连接条件：||查询内容：emp所有+dep.name;
    select e.*,d.name from emp e left outer join dep d on e.dapt_id = d.id;

-- b:查询dep表的所有数据，和对应的员工信息---（右外连接）
    -- 分析：右外连接：表结构：dep，emp，||连接条件..||查询内容：dep所有+emp.name;
    select d.*,e.name from emp e right outer join dep d on e.dapt_id = d.id;
    select d.*,e.name from  dep d left join  emp e on d.id = e.dapt_id;-- 也可以使用左表：只不过要调换两个表位置即可；
-- c：右外连接和左外可以相互转换；

3.子连接：

子连接：当前表与自身的连接查询，子连接必须使用别名；！！！！！！！！！！！
--    1.子连接查询语法：select 字段列表 from 表A 别名表A join 表A 别名表B on 条件；
--    2.                                       这里可以说内连接也可以时外连接（左或右）都可！！！
--    3.子连接即自己连接自己：
--    4.演示：a：查询员工员工 及其所属领导的名字
        -- 分析：员工及所属领导在一个表中-->所以只涉及一个emp表，-----自连接
        --      但是多表查询：只涉及一个表无法完成所所以，-->是自己emp表关联自己！！！ 将emp看作两张表！！！！
-- ！！！ a：表结构：emp； b： ！！！！！！！！将emp表看作两张表：表A员工表和 表B领导表： c：！AB表的连接条件：员工表的mangerid关联领导表的主键id
--       d：查询信息：员工 员工领导的名字
        select a.name,b.name from emp a,emp b  where a.id=b.managerid;
--                    注意：这里我们使用的是：内连接隐式查询（其它也可以）必须起别名！！！！
--
--      演示b：查询所有员工emp及其领导的名字，如果员工没有领导也要查询出来：（比如金庸）
        -- 分析：1.分析本身是哪一种：查询所有员工emp及其领导的名字--->在一张表中：即自连接！！！
        --      2.分析中间使用哪一种连接：如果员工没有领导也要查询出来：（比如金庸）--》：没有也要查询出来：使用！！外连接（左或右）
--              才会包含左表或右表的数据-- :即完全包含员工：可以使用左外，！！！！
        --      3.查询内容：员工名字 领导名字||表结构：一张表但看作两张 ||连接条件：
           select a.name,b.name from emp a left outer join emp b on  a.managerid=b.id;
           select a.name as '员工',b.name as'领导' from emp a left outer join emp b on  a.managerid=b.id;