约束补充，多表查询，联合查询，子查询

1：约束

-- 主键约束，非空且唯一，auto—_increment表示每填入数据会自动增一
alter table tbusers modify id int primary key auto_increment;
-- 检查约束，check
alter table tbusers modify age int check ( age<120 );

-- 外键约束
create table dept(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)

create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    dept_id int comment '部门ID',
    -- 添加外键
    constraint fk foreign key(dept_id)references dept(id)

)
-- 外键即让两个表产生关联
-- 此时二表已产生关联，若想删除父表的id5字段则需先删除子表的关联id为5的字段
-- 若子表添加一个dept_id为10的字段报错，因为父表的关联id没有为10的

-- cascade指若删除父表5号部门，则子表金庸自动删除
alter table emp add constraint fk foreign key (dept_id)
    references dept(id) on DELETE cascade ;
-- set null指若删除父表5号部门，子表金庸的关联id变为null
alter table emp add constraint fk foreign key (dept_id)
    references dept(id) on DELETE set null ;

      

2：多表查询

        概念：多表查询就是指从多张表中查询数据。

        结构：select * from 表1，表2 where 表1字段 = 表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');

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

-- 一对一
create table dept(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

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

-- 多表查询，数据来源是(n>1)张表
-- 隐式内连接
select emp.name,dept.name from dept,emp where emp.dept_id=dept.id;
-- 显式内连接
select emp.name,dept.name from emp join dept on emp.dept_id=dept.id;
-- 查看张三丰的部门信息
select dept.* from emp,dept where emp.name='张三丰' and emp.dept_id=dept.id;

-- 外连接，左外链接右外连接，即以哪侧表为驱动表它的数据全部显示另外一侧没有数据补null
-- 左外连接
select * from emp left join dept on emp.dept_id = dept.id;
-- 右外连接
select * from emp right join dept on emp.dept_id = dept.id;

-- 自连接
select e.name,m.name from emp as e,emp as m where
e.managerid=m.id and e.name='韦一笑';
-- 查看张无忌的员工
select e.name from emp as e,emp as m where
e.managerid=m.id and m.name='张无忌';
-- 查看每个领导手下有多少员工
select count(e.name),m.name from emp as e ,emp as m where
e.managerid=m.id group by m.name;

3：联合查询

        关键字：union / union all

        区别：union会去掉重复的数据 相当于 or； union all 不会去掉重复的数据

        注意：查询的数据要保持一致！

-- 联合查询union/union all可将两种查询结果合体输出，查询的数据要保持一致
-- union会去除重复的数据
-- union all不会去除重复数据
select * from emp where salary < 5000
union all
select * from emp where age > 50;

4：子查询：

        概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

-- 和张无忌同部门的员工有哪些
select dept_id from emp where name='张无忌';
select * from emp where dept_id=1;
-- 如此可将两句合并为一句，此为子查询
select * from emp where dept_id=(select dept_id from emp where name='张无忌');
-- 查看销售部都有谁
select * from emp where dept_id=
(select id from dept where dept.name='销售部');

-- 列子查询：子查询是一列内容
-- 查询销售部和市场部的所有员工信息
select id from dept where name='市场部'or name='销售部';
select * from emp where dept_id in(2,4);
select * from emp where dept_id in(select id from dept where name='市场部'or name='销售部');
-- 查询比财务部所有人工资都高的员工信息，all为里面的最大值，any为最小值
select * from emp where salary>
(select max(salary) from emp where dept_id=(select id from dept where name='财务部'));
select * from emp where salary>all
(select salary from emp where dept_id=(select id from dept where name='财务部'));

-- 行子查询：子查询是一行内容
-- 查询与张无忌薪资及直属领导相同的员工
select salary,managerid from emp where name='张无忌';
-- 后面括号写的时候要对应起来就行
select * from emp where(salary,managerid)=
(select salary,managerid from emp where name='张无忌');

-- 查询与鹿杖客，宋远桥的职位和薪资相同的员工信息
-- 多行多列，表子查询
select * from emp where(job,salary)in
(select job,salary from emp where name='鹿杖客'or name='宋远桥');