MySQL学习笔记——基础篇（约束、多表查询）

1.6 约束

• 用于限制存储在表中的数据

  非空约束：NOT NULL
  唯一约束：UNIQUE
  主键约束：PRIMARY KEY，非空且唯一
  默认约束：DEFAULT
  检查约束：CHECK
  外键约束：FOREIGH KEY，用于两张表之间建立连接

• 案例

  create table test(
  	id int PRIMARY KEY auto_increment comment '主键',
  	name varchar(10) not null unique comment '姓名',
  	age int check (age > 0 and age<=100) comment '年龄',
  	status char(1) default '1' comment '状态',
  	gender int comment '性别'
  );
  

• 外键约束
  

  #添加外键
  alter table tb1 add constraint fkeyname(外键名) foreign key(tb1key外键字段名) references tb2(主表列名); 
  #删除外键
  alter table tb1 drop foreign key 外键名;
  

• 外键删除/更新行为

  #no action:在父表中删除更新时，首先检查改记是否有对应外键，如果有则不允许操作。
  #restrict:与no action 一致
  #cascade:在父表中删除更新时，首先检查是否有对应外键，如果有则在子表中进行相同的操作
  #set null:当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中外键值位null
  #set fault:父表有变更，子表将外键设置一个默认值（innodb不支持）
  
  #样例：
  alter table tb1  add constraint emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete set null;
  

1.7 多表查询

1.7.1 多表关系

• 一对多，多对一

  案例：员工与部门的关系

  一个部门对应多个员工

  在多的一方建立外键，指向一的一方的主键

• 多对多

  案例：学生与课程的关系

  建立第三张中间表

CREATE TABLE student ( id INT PRIMARY KEY, NAME VARCHAR ( 10 ) NOT NULL, num VARCHAR ( 8 ) UNIQUE COMMENT '学号' ) COMMENT '学生表';
CREATE TABLE course ( id INT PRIMARY KEY, NAME VARCHAR ( 10 ) NOT NULL ) COMMENT '课程表';
CREATE TABLE student_course (
	id INT PRIMARY KEY,
	std_id INT NOT NULL,
	cou_id INT NOT NULL,
	CONSTRAINT fk_courseid FOREIGN KEY ( cou_id ) REFERENCES course ( id ),
CONSTRAINT fk_studentid FOREIGN KEY ( std_id ) REFERENCES student ( id ) 
);

• 一对一

  用于单表拆分，将一张表的基础字段放在一张表中、
  

1.7.2 多表查询

• #笛卡尔积：两个集合A、B的所有组合情况
  select * from emp,dept where emp.dept_id=dept.id;
  

• 内连接

#查询A、B表交集部分
#隐式,查询一个员工的姓名，及关联的部门的名称
select  emp.name,dept.name from emp,dept where emp.dept_id=dept.id;
#起别名
select  emp.name,dept.name from emp e,dept d where e.dept_id=d.id;
#显式,样例同上
select emp.name,dept.name from emp e join dept d on e.dept_id=d.id;

• 外连接

  #左外连接
  select e.*,d.name from emp e left outer join dept d on e.dept_id=d.id;
  #右外连接
  select d.*,e.name from emp e right outer join dept d on e.dept_id=d.id;
  

• 自连接

  两次表必须使用别名

  #内连接，查询员工及其所属领导的名字,领导也是员工
  select a.name,b.name from emp a,emp b where a.managerid=b.id;
  #外连接，查询员工及其所属领导的名字,如果没有领导，也要显示
  select a.name,b.name from emp a left join emp b on a.managerid==b.id;
  

• 联合查询

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

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

  #将薪资低于5000的员工和年龄大于35的员工查询出来
  select * from emp where salary<5000
  union all
  select * from emp where age >35;
  #合并去重
  select * from emp where salary<5000
  union 
  select * from emp where age >35;
  

• 子查询

  又称为嵌套查询

  • 标量子查询，返回结果时单个值

    #查询销售部的所有员工信息，销售部在另一张表
    select * from emp where dept_id=(select id from dept where name='销售部');
    #查询员工xyh入职之后的新员工的入职日期
    select * from emp where entrydate > (select entrydate form emp where name='xyh');
    

  • 列子查询，返回结果是一列

    #in，not in
    #all 返回回来的结果都需要满足
    #some，any 返回回来的结果存在一个满足
    #查询销售部和市场部的所有员工信息
    select * from emp where dpet_id in (select id from dept where name='销售部' or name ='市场部');
    #查询比财务部所有人工资都高的员工信息
    select * from emp where salary > all (select salary from emp where dept_id=(select id from dept where name='财务部'));
    #查询比财务部其中一人工资高的员工信息
    select * from emp where salary > any (select salary from emp where dept_id=(select id from dept where name='财务部'));
    

  • 行子查询，返回结果是一行

    #=，<>，in，not in
    select * from emp where (salary,managerid) = (select salary,managerid from emp where name='zwj');
    

  • 表子查询，返回结果是多行多列

    #in
    #与xyh或zy职位和工资相同的员工
    select * from emp where (job,salary) in (select job,salary from emp where name='xyh' or name='zy');
    

• 多表查询案例

  #查询员工的姓名、年龄、职位、部门（隐式内连）
  select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id=d.id;
  
  #查询年龄小于35岁的员工姓名、职位、年龄、部门信息（显示内连）
  select e.name,e.job,e.age,d.name from emp e join dept d on e.dept_id=d.id where e.age<35;
  
  #查询拥有员工的部门id和部门名称 (内连接)
  select distinct d.id,d.name from emp e,dept d where e.dept_id=d.id;
  
  #查询所有年龄大于35的员工的部门名称 (外连接)
  select * from emp e left join dept d on e.dept_id=d.id where e.age >35;
  
  #查询研发部所有员工的信息及工资等级
  select e.*,s.garde from emp e ,dept d,salary_grade s where e.dept_id=d.id and (e.salary between s.lowsal and s.highsal) and d.name='研发部';