约束与多表连接

not null

-- 查询
select * from employee where birthday is not null;
-- 添加not null约束
alter table employee modify employee_id int not null;

唯一约束unique

unique约束可以唯一标识数据库的记录；
一张表只能有一个主键约束，但是可以有多个unique约束。

-- 建表时创建unique约束
create table emp_info(
    id int not null,
    emp_id int not null comment '职工号',
    emp_name varchar(20) comment '职工姓名',
    address varchar(100) comment '家庭住址',
    family_num int comment '家庭人口',
    -- 建表时给emp_id创建unique约束
    -- unique (emp_id)
    -- 给unique约束命名,并且同时创建多个unique约束
    constraint u_id unique (id,emp_id)
);
-- 表已经存在，添加unique约束
alter table emp_info add unique (emp_id);
alter table emp_info add constraint u_id unique (id,emp_id);
-- 撤销约束
alter table emp_info drop index u_id;

primary key

primary key = unique + not null，建表时要注意这一点。此外，primary key可以与外键配合，not null unique不可以。

-- 建表时设置主键约束
create table dep_info(
    id int not null ,
    dep_id int not null comment '部门编号',
    dep_name varchar(20) comment '部门名称',
    leader varchar(20) comment '部门主管',
    -- id作为唯一主键
    -- primary key (id)
    -- 将id、dep_id组合成一个主键p_id
    constraint p_id primary key (id,dep_id)
);
-- 查表
desc dep_info;

-- 表已经存在，添加主键约束
alter table dep_info add primary key (dep_id);
alter table dep_info add constraint p_id primary key (id,dep_id);
-- 撤销主键约束
alter table dep_info drop primary key;

外键约束（FK）

报错：Failed to add the foreign key constraint. Missing index for constraint ‘bus_management_ibfk_1’ in the referenced table ‘XX’
设置外键时一定要保证被设置的字段有约束（unique、pk），如果是pk，则会导致pk冲突，需要将其合并成一个。

-- 建表时设置主键约束
create table dep_info(
    id int not null ,
    dep_id int not null comment '部门编号',
    dep_name varchar(20) comment '部门名称',
    manager varchar(20) comment '部门主管',
    manager_id int comment '主管编号',
    -- id作为唯一主键
    primary key (id),
    unique (manager_id)
    -- 将id、dep_id组合成一个主键p_id
    -- constraint p_id primary key (id,dep_id)
);
-- 建表时创建外键约束
create table bus_management(
    id int not null,
    dep_id int not null comment '部门编号',
    dep_name varchar(20) comment '部门名称',
    manager_id int comment '主管编号',
    manager varchar(20) comment '部门主管',
    count varchar(20) comment '部门人数',
    -- 设置id为主键
    primary key (id),
    -- 设置manager_id为外键
    foreign key (manager_id) references dep_info(manager_id),
    -- 并列设置多个外键
    constraint fk foreign key (manager_id,dep_id) references dep_info(manager_id,dep_id)
);
-- 表已存在时增加外键约束
alter table bus_management add foreign key (manager_id) references dep_info(manager_id);
-- 撤销外键
alter table bus_management drop foreign key fk;

check检查约束

-- 创建表时添加check约束
create table company_manage(
    id int not null,
    ceo_num int comment 'ceo工号',
    ceo_name varchar(50) comment 'tony',
    cto_num int comment 'cto工号',
    cto_name varchar(50) comment 'eleven',
    emp_count varchar(50) comment '员工总数',
    dep_count varchar(50) comment '部门总数',
    dep_id int comment '部门编号',
   -- check ( emp_count > 0 and dep_count > 0 )
   -- 给约束命名
   constraint check_check check ( emp_count > 0 )
);
-- 表已经存在时添加check约束
alter table company_manage add check ( emp_count > 0 );
alter table company_manage add constraint check_check check ( emp_count > 0 );
-- 撤销check约束
alter table company_manage drop check check_check;

default约束

-- 建表时创建default约束
create table `company_manage`(
    id int not null,
    ceo_num int comment 'ceo工号',
    ceo_name varchar(50) comment 'ceo姓名',
    cto_num int comment 'cto工号',
    cto_name varchar(50) comment 'cto姓名',
    emp_count varchar(50) comment '员工总数',
    dep_count varchar(50) comment '部门总数',
    dep_id int comment '部门编号',
    dep_manager varchar(50) comment '部门主管',
    -- 设置新增时的性别默认为女
    dep_manager_gender varchar(10) comment '主管性别' default '女',
    -- 设置新增时的时间默认为系统当前时间,mysql设置默认日期只能使用current_timestamp
    company_build_date datetime comment '公司注册日期' default current_timestamp,
    -- check ( emp_count > 0 and dep_count > 0 )
    -- 给约束命名
    constraint check_check check ( emp_count > 0 )
);
-- 表已经存在时，添加default约束
alter table company_manage alter dep_count set default 30;
-- 撤销default约束
alter table company_manage alter dep_count drop default ;

联结

！！！注意：where一般用作条件查询，但是在内连接中on和where都可以使用，外连接时只能使用on！

`table1`.'letter1' = `table2`.'letter2';

inner join内连接

-- inner join
select company_manage.dep_id from company_manage inner join dep_info on company_manage.dep_id = dep_info.dep_id;
select company_manage.dep_id from company_manage inner join dep_info where company_manage.dep_id = dep_info.dep_id;
select company_manage.dep_id from company_manage join dep_info where company_manage.dep_id = dep_info.dep_id;
select company_manage.dep_id as d_id from company_manage join dep_info where company_manage.dep_id = dep_info.dep_id;

outer join外连接

-- outer join
-- left outer join 左外连接，返回符合查询条件的左表中的所有数据，不仅仅是连接所匹配到的行
-- 如果左表的某行在右表中没有匹配行，那么在相关联的结果行中，右表的所有选择列表均为空值
select company_manage.dep_id from company_manage left join dep_info on dep_info.dep_id = company_manage.dep_id;
-- right outer join 右外连接,以右表作为参考表，如果右表的某行在左表中没有匹配行，左表就返回空值
select company_manage.dep_id from company_manage right join dep_info on dep_info.dep_id = company_manage.dep_id;
-- mysql不支持full join全外连接，但是可以使用union联合，将左外连接和右外连接连起来
select employee.employee_id as emp_id, employee.dep_id as dep_id, employee.dep_name as dep_name
from employee
         left join dep_info on employee.dep_id = dep_info.dep_id
union
select employee.employee_id as emp_id, employee.dep_id as dep_id, employee.dep_name as dep_name
from employee
         right join dep_info on employee.dep_id = dep_info.dep_id;

cross join交叉连接

-- cross join 交叉连接
-- 由于交叉连接生成的是两个表的笛卡尔积，所以只能使用where查询定义查询，不可以使用on
select employee.employee_name as name ,employee.dep_name as dep,dep_info.manager as leader from employee cross join dep_info
where employee.dep_id = dep_info.dep_id;