约束 / 多表查询

分类：

 

 

例题：

 

 

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

-- 插入数据
# 有了auto_increment, 主键自动增长, 不需要再手动录入
insert into user (name, age, status, gender) values ('Tom1', 19, '1', '男'), ('Tom2', 25, '0', '男');

 

添加外键：

create table 表名(
        字段名  数据类型
        ···
        [constraint] [外键名称] foreign key (外键字段名) reference 主表(主表列名)
);


alter table add constraint 外键名称 froeign key (外键字段名) reference 主表（主表列名);

删除外键：

alter table 表名 drop foreign key 外键名称;

删除和更新行为：

 

 

 

多表查询分类：

　　连接查询：

　　　　内连接：相当于查询A、B交集部分数据

    

-- 隐式内连接
select 字段列表 from 表1, 表2 where 条件...;
-- 显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

-- 查询每一个员工的姓名，以及关联部门名称
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
-- 表名起别名  注意: DQL中from先执行
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

-- 显式内连接执行上述语句
select emp.name, dept.name from emp join dept on emp.dept_id = dept.id;

　　　　外连接：

　　　　　　左外连接：查询左表所有数据，以及两张表交集部分数据

select 字段列表 from 表1 left [outer] join 表2 on 条件...;

-- 查询emp表的所有数据，和对应的部门信息(左外连接)
select emp.*, dept.name from emp left join dept on emp.dept_id = dept.id;

      右外连接：查询右表所有数据，以及两张表交集部分数据

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

-- 查询dept表的所有数据，和对应的员工信息(左外连接)
select dept.*, emp.* from emp right join dept on emp.dept_id = emp.id;

    自连接：当前表与自身的连接查询，自连接必须使用表别名

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

-- 查询员工 及其 领导的名字
select a.name, b.name from emp a, emp b where a.managerid = b.id;
select a.name, b.name from emp a join emp b on a.managerid = b.id;

-- 查询员工 及其 领导的名字 （员工没有领导，也要查询出来）
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

-- 自连接可以是内连接 左外连接 右外连接

联合查询 -union, union all

就是把多次查询的结果合并起来，形成一个新的查询结果

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;

注意：加了all会直接合并，不加all会去重

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

 

子查询

标量子查询

子查询返回的结果事单个值（数字、字符串、日期等）

常用操作符： = 、<>、>、>=、<、<=

列子查询

子查询返回的结果是一列（可以是多行）

常用操作符：in、not in、any、some、all

注意：any、all与 操作运算符、in、not in 一起用

行子查询

常用的操作符：=、<>、in、not in

-- 查询与‘张无忌'的薪资及其直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列

常用的操作符：in

-- 查询与‘宋远桥'和'谢逊'的职位和工资相同的员工信息
select * from emp where (salary, managerid) in (select salary, managerid from emp where name = '宋远桥' or name = '谢逊');