cgb2107-数据库-day04

一.外键约束

-1.测试

create table tb_user
(
    id   int primary key auto_increment,
    name varchar(20)
);
#外键约束的效果:
#1. 子表的id必须取自主表的id
#2. 想删除主表的数据必须先删掉子表相关的
drop table if exists tb_user_addr;
create table tb_user_addr
(
   user_id int primary key ,
   addr varchar(20),
   # 外键: 通过特殊字段(外键),描述了两张表间的关系
   #foreign key(当前表的主键 ) references 对方表(对方表的主键
   foreign key (user_id) references tb_user(id)
);

索引

-1.概述

好处是最大的作用就是提高查询效率,坏处是索引本身也是一张表不适合大量的添加实现过程: 1. 设置索引 2.使用索引
分类: 单值索引 , 唯一索引 ,复合索引

-2 测试

# 0.查看索引

show index from dept;
#1.创建单值索引(给常用来作为查询条件的字段加)
    # 语法: create index 索引名 on 表名(字段名)
create index dname_index on dept (dname);
#2.使用索引(背后的手段   )
explain  #观察sql的性能/执行计划(找possible_key的值)
select *
from dept;

show index from dept;
#3.创建唯一索引(索引列的值不能重复)
show index from emp;
create index fuhe on emp (ename, job);
show index from emp;

create unique index uni_index on dept(dname);

explain
select * from emp where ename = 'jack' and job = '副总';

explain
select * from emp where job = '副总' and ename= 'jack';

explain
select * from emp where job = '副总';

show index from emp;

三.多表联查

-1. 概述

用来完成联合多张表的查询(三张以下

-2 测试

#多表联查: 产生了大量的冗余数据
# 1. 笛卡尔积: 把多张表用逗号隔开
select * from dept , emp
    where dept.deptno = emp.depno;
# 表述两张表的关系: 表名.字段
#2. 连接查询, join.on
    select * from dept join emp
        on dept.deptno = emp.depno;

# 3.子查询: 把上次的查询结果,用来作为下次查询的条件

#练习语法: teachers/courses
select * from teachers, courses where  teachers.tno = courses.tno;
select * from teachers join courses on teachers.tno = courses.tno;
# 练习1: 查询部门名称叫accounting的员工姓名
# 子查询
# 1. 查询门表, 根据部门名称accounting 查deptno

select deptno from dept where dname = 'accounting';
#2. 查询员工表,根据deptno查姓名
select ename from emp where depno = 1;
select ename from emp where depno = (select deptno from dept where dname = 'accounting')

#笛卡尔积: 逗号隔开表名, where里写查询条件,可以多个
select emp.ename
from dept,
     emp
where dept.deptno = emp.depno
  and dept.dname = 'accouting';

select emp.ename
from dept
         join emp
              on dept.deptno = emp.depno
where dept.dname = 'accounting';
# 练习2 : 列出research部门下的所有员工的信息
# 子查询
# 根据部门名称查部门员工信息
select deptno from dept where dname = 'research';
select *
from emp
where depno = any (select deptno from dept where dname = 'research');

#笛卡尔积:
select emp.*
from dept,
     emp
where dept.deptno = emp.depno
  and dname = 'accounting';


#三种连接查询:
# 内连接inner join: 取两个表额交集
#左外连接left join: 左表的所有和右表满足条件的,不满足是null
#右外连接right join: 右表的所有和左表满足条件的,不满足是null

select * from dept left join emp
    on dept.deptno = emp.depno # 表关系
    where dept.deptno = emp.depno; # 业务条件

#练习3: 查询工作地址在二区的所有员工信息
#子查询: 可以用in或者= 连接所有员工信息
#根据部门地址查部门编号
select deptno
from dept
where loc = '二区';

select * from emp where depno = 2 or depno = 3;
select *
from emp
where depno in (2, 3);
select *
from emp
where depno in (select deptno
                from dept
                where loc = '二区'
);

# 笛卡尔积
select emp.*
from dept,
     emp
where dept.deptno = emp.depno
  and dept.loc = '二区';
# 连接查询 -- 相对高效,小表驱动大表(左表写一个小表)
select emp.*
from dept
         inner join emp
                    on dept.deptno = emp.depno
where dept.loc = '二区';