cgb2107-day04

た入场券 2021-09-11 03:32 189阅读 0赞

### 文章目录 ###

*   *  一，外键约束
     *   *  \--1，测试
     *  二，索引
     *   *  \--1，概述
         *  \--2，测试
     *  三，多表联查
     *   *  \--1，概述
         *  \--2，测试

## 一，外键约束 ##

### –1，测试 ###

CREATE TABLE tb_user(
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(20)
    )
    #外键约束的效果：
    #1,子表的id必须取自主表的id 
    #2,想删除主表的数据必须先删掉子表相关的
    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_keys的值)
    SELECT * FROM dept WHERE dname='java'
    #3.创建唯一索引(索引列的值不能重复)
    CREATE UNIQUE INDEX uni_index ON dept(dname)
    #4. 创建复合索引 并 使用复合索引
      # CREATE INDEX 索引名 ON 表名 (字段1,字段2)
    CREATE INDEX fuhe ON emp(ename,job)
    SHOW INDEX FROM emp
    #5. 使用复合索引(最左特性,否则复合索引失效)
    EXPLAIN SELECT * FROM emp WHERE ename='jack' #按ename查，生效
    	#按ename和job查，生效
    EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='副总'
    	#按job和ename查，生效
    EXPLAIN SELECT * FROM emp WHERE job='副总' AND 	ename='jack'
    	#按job查，失效
    EXPLAIN SELECT * FROM emp WHERE job='副总'	
    
    SHOW INDEX FROM emp
    #删除索引
    # 修改表 表名 删除索引 索引名
    ALTER TABLE emp DROP INDEX fuhe

## 三，多表联查 ##

### –1，概述 ###

用来完成联合多张表的查询（3张以下），

### –2，测试 ###

#多表联查：产生了大量的冗余数据
    #1.笛卡尔积：把多张表用逗号隔开
    SELECT * FROM dept,emp 
    	#描述两张表的关系：表名.字段名
    	WHERE dept.deptno=emp.deptno 
    #2.连接查询,join..on
    SELECT * FROM dept JOIN emp
    	#描述两张表的关系：表名.字段名
    	ON dept.deptno=emp.deptno
    #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 deptno=1
    SELECT ename FROM emp WHERE deptno=(
    	SELECT deptno FROM dept WHERE dname='accounting')
    	
    #笛卡尔积：逗号隔开表名，where里写查询条件，可以多个
    SELECT emp.ename FROM dept,emp 
    	WHERE dept.deptno=emp.deptno #表关系
    	AND dept.dname='accounting' #业务条件
    #连接查询：JOIN连接表名，ON写表关系，where里写查询条件，可以多个 
    SELECT emp.ename FROM dept JOIN emp 
    	ON dept.deptno=emp.deptno #表关系
    	WHERE dept.dname='accounting' #业务条件
    #练习2：列出research部门下的所有员工的信息
    #子查询：
    #根据部门名称查部门编号
    SELECT deptno FROM dept WHERE dname='research'
    #根据部门编号查员工信息
    SELECT * FROM emp WHERE deptno=2
    SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='research')
    #笛卡尔积：
    SELECT emp.* FROM dept,emp 
    	WHERE dept.deptno=emp.deptno #表关系
    	AND dept.dname='research' #业务条件
    
    #三种连接查询：
    #内连接inner join:取两个表的交集
    #左外连接left join:左表的所有和右表满足条件的，不满足是null
    #右外连接right join:右表的所有和左表满足条件的，不满足是null
    SELECT * FROM dept LEFT JOIN emp 
    	ON dept.deptno=emp.deptno #表关系
    	WHERE dept.dname='research' #业务条件
    #练习3：查询工作地址在二区的所有员工信息
    #子查询:可以用in或者=连接子查询
    #根据部门地址查部门编号
    SELECT deptno FROM dept WHERE loc="二区" #会查到多个值
    #根据编号查员工信息
    SELECT * FROM emp WHERE deptno=2 OR deptno=3
    SELECT * FROM emp WHERE deptno IN(2,3) #in子查询
    SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE loc="二区")
    #笛卡尔积
    SELECT emp.* FROM dept,emp 
    WHERE dept.deptno=emp.deptno #表示了两张表的关系
    AND dept.loc="二区" #业务条件
    #连接查询--相对高效，小表驱动大表（左表写一个小表）
    SELECT emp.* FROM dept INNER JOIN emp #内连接取交集
    ON dept.deptno=emp.deptno #表示了两张表的关系
    WHERE dept.loc="二区" #业务条件