cgb2107-day04

一，外键约束

–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="二区" #业务条件