数据库基础2

多表查询

#显示雇员名，雇员工资及所在部门的名字
#雇员名，雇员工资来自 emp表
#部门的名字来自 dept 表
#当我们需要指定某个表的列：表.列名
SELECT ename,sal,dname
	FROM emp,dept
	WHERE emp.deptno = dept.deptno

#显示员工姓名，工资，工资级别

#姓名，工资来自emp 工资级别来自salgrade

SELECT ename,sal,grade
	FROM emp,salgrade
	WHERE sal BETWEEN losal AND hisal;
	

自连接

自连接是指在同一张表的连接查询

#显示公司员工名字和他上级的名字
#员工名字在emp表中，上级的名字也在emp中，两者通过emp中的mgr列关联

SELECT worker.ename AS '职员名', boss.ename AS '上级名'
	FROM emp worker, emp boss
	WHERE worker.mgr = boss.empno #职员上司的编号
	
#自连接的特点：1. 把同一张表当作两张表使用
#		2. 需要给表取别名(一个worker表，一个boss表)

子查询

子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询

单行子查询

指只返回一行数据的子查询语句 使用 =

#如何显示与smith在同一部门的所有员工

#1. 先查询到smith的部门号
#2. 把上面的select语句当作一个子查询来使用

SELECT deptno
	FROM emp
	WHERE ename = 'smith';
	
SELECT * 
	FROM emp
	WHERE depto = (
		SELECT deptno
		FROM emp
		WHERE ename = 'smith'
	)

多行子查询

返回多行数据的子查询，使用关键字 in 

#查询和部门10工作内容相同的员工的 名字、岗位、工资、部门号
#但是不包括部门10的员工

# 1. 查询到10号部门有哪些工作 （不能重复，因此用distinct）
# 2. 把上面查询的结果当作子查询使用

SELECT DISTINCT job
	FROM emp
	WHERE deptno = 10;
	
SELECT ename,job,sal,deptno
	FROM emp
	WHERE job IN (
		SELECT DISTINCT job
		FROM emp
		WHERE deptno = 10
	) AND deptno != 10  #部门不为10

子查询临时表

可以将子查询当作一张临时表使用

#查询ecshop中各个类别，价格最高的商品

#先得到各个类别中，价格最高的商品 (max, group by cat_id) 当作临时表

SELECT cat_id,MAX(shop_price)
	FROM ecshop
	GROUP BY cat_id
#获得编号+对应的最高价格	

SELECT goods_id,ecshop.cat_id,goods_name,shop_price #注意要声明哪个表中的cat_id
	FROM (
		SELECT cat_id,MAX(shop_price)
		FROM ecshop
		GROUP BY cat_id
	) temp , ecshop #temp是临时表的名字，从临时表和总表中查找
	WHERE temp.cat_id = ecshop.cat_id
	AND temp.max_pricr = ecshop.shop_price
#满足编号相同，价格是最大的，输出信息

all 和 any

#显示工资比部门30 的所有员工的工资高 的员工的姓名、工资和部门号

SELECT ename,sal,deptno
	FROM emp
	WHERE sal > ALL(
		SELECT sal
		FROM EMP
		WHERE deptno = 30
	) #嵌套查询 ，查出所有部门30员工的工资
	
#注：也可以把ALL 改成 MAX

#显示工资比部门30 的其中一个工资高 的员工的姓名、工资和部门号

SELECT ename,sal,deptno
	FROM emp
	WHERE sal > ANY(
		SELECT sal
		FROM EMP
		WHERE deptno = 30
	) #嵌套查询 ，查出所有部门30员工的工资
	
#注：也可以把ANY 改成 MIN

基本上ALL等价于MAX，ANY等价于MIN。

多列子查询

WHERE (字段1,字段2...) = (子查询语句)

#多列子查询返回多个列数据

#查询与smith部门和岗位完全相同的所有雇员（不包括smith本人）
SELECT deptno , job
	FROM emp
	WHERE ename = 'smith'  #得到smith的岗位和工作
	
#把上面的查询当作子查询来使用，并且使用多列子查询的语法来匹配
SELECT * 
	FROM emp
	WHERE (deptno,job) = (
		SELECT deptno , job
		FROM emp
		WHERE ename = 'smith' 
	) AND ename != 'smith'

一些练习

#查找每个部门工资高于本部门平均工资的人的资料

#1.先得到每个部门的部门号和对应的平均工资
SELECT deptno,AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno  #用部门号进行分组
	
#2. 把上面的结果当作子查询，和emp进行多表查询

SELECT ename,sal,temp.avg_sal,emp.deptno
	FROM emp,(
		SELECT deptno,AVG(sal) AS avg_sal
		FROM emp
		GROUP BY deptno
	)temp
	WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal

#查询每个部门的信息和人员数量

#部门信息在dept表（部门名，编号，地址）
#各个部门的人员数量 -> 构建一个临时表

SELECT COUNT(*),deptno
	FROM emp
	GROUP BY deptno

SELECT dname,dept.deptno,loc,tmp.per_num AS '人数' 
    #也可以把 deptno和per_num 合并写成 tmp.*
	FROM dept, (
		SELECT COUNT(*),deptno
		FROM emp
		GROUP BY deptno
	) tmp
	WHERE dept.deptno = tmp.deptno

注：在多表查询中，当多个表的列不重复时，才可以直接写列名。

表复制

#把emp表复制到my_tab01中

INSERT INTO my_tab01
	(id,`name`,sal,job,deptno)
	SELECT empno,ename,sal,job,deptno 
	FROM emp

#自我复制
INSERT INTO my_tab01
	SELECT * FROM mytab_01

表去重

#创建一个临时表和要去重的表结构相同
CREATE TABLE my_tmp LIKE my_tab02 

#把tab02的记录通过distinct处理后 复制到tmp
INSERT INTO my_tmp
	SELECT DISTINCT * FROM my_tab02
	
#清除掉tab02的记录
DELETE FROM my_tab02

#把tmp表的记录复制到tab02
INSERT INTO my_tab02
	SELECT * FROM my_tmp
	
#drop掉临时表tmp
DROP TABLE my_tmp;

合并查询

SELECT ename,sal,job FROM emp WHERE sal >2500
UNION ALL  #查询结果合并，不会去重   DISTINCT会去重
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER'

外连接

        列出部门名称和这些部门的员工名称和工作，同时要求 显示出那些没有员工的部门，这个无法用多表查询得到（因为无法关联起来）

#使用左外连接实现
SELECT dname,ename,job
	FROM dept LEFT JOIN emp
	ON dept.deptno = emp.deptno
	
#使用右外连接实现
SELECT dname,ename,job
	FROM emp RIGHT JOIN dept
	ON dept.deptno = emp.deptno

mysql约束

primary key(主键)

CREATE TABLE t17
	(id INT PRIMARY KEY, #表示id列是主键,主键列的值不可以重复，并且不能是NULL
	...
	);

注：1. 一张表最多有一个主键，但可以是复合主键

CREATE TABLE t17
	(id INT, 
	`name` VARCHAR(32) 
	PRIMARY KEY(id,`name`) #复合主键，只有id和name都重复时才报错，只有一个重复不报错
	);

2. 主键指定方式有两种：A. 直接在字段名后指定  B. 在表定义的最后写

3. 使用 desc 表名，可以看到primary key 的情况
 

unique

加上unique后，值不能重复

1. 如果没有指定not null，那么unique字段可以有多个null

2. 如果指定了 not null，那么使用效果类似于 primary key

3. 一张表可以有多个unique字段

CREATE TABLE t17
	(id INT UNIQUE,
	`name` VARCHAR(32) 
	);

外键

#创建主表
CREATE TABLE my_class(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '');

#创建从表
CREATE TABLE my_stu(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT,
	#下面指定外键关系
	FOREIGN KEY(calss_id) REFERENCES my_class(id))

   比如主表里有class 3，但是从表还没有class 3的学生。这时候可以删除主表的class 3。如果有，就不能删除了。 

check

CREATE TABLE t23(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK(sex IN('man','woman')),
	sal DOUBLE CHECK (sal >10 AND sal<2000)
	);

自增长

        添加后，该字段从1开始自增长

CREATE TABLE t24(
	id INT PRIMARY KEY AUTO_INCREMENT);

#修改默认的自增长开始值
ALTER TABLE t24 AUTO_INCREMENT = 100

 比如加入了500这个数值，那么下一次就从501开始。

索引

原理就是二叉搜索树。

#在emp表的 empno列创建索引

CREATE INDEX empno_index ON emp(empno)

添加索引 

#查询表是否有索引
SHOW INDEXES FROM t25;

#添加唯一索引
CREATE UNIQUE INDEX id_index ON t25(id);

#添加普通索引
CREATE INDEX id_index ON t25(id);
ALTER TABLE t25 ADD INDEX id_index(id);
#如何选择：
#	如果某列的值是不会重复的，那么优先考虑使用unique索引，否则使用普通索引

#添加主键索引
CREATE TABLE t26(
	id INT,
	`name` VARCHAR(32));
	
ALTER TABLE t26 ADD PRIMARY KEY(id); #这个跟直接在CREATE写primary key效果相同

删除索引 

#删除索引
DROP INDEX id_index ON t25
#删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY

#修改索引：先删除，再添加新的索引

查询索引

SHOW INDEX FROM t25

什么时候使用索引 

第四条：也就是该字段用不到select。