[大数据]01MYSQL—DQL

01MQSQL—DQL

sql分类

DQL：数据库查询语言（select）

DML：数据操作语言（对表中的数据进行insert增、delete删、update改）

DDL：数据定义语言（create、drop、alter都是DDL）

TCL：事务控制语言（事务提交commit、回滚rollback）

DCL：数据控制语言（授权grant、撤销权限revoke）

sql常用命令

mysql登录（显示密码）：mysql -uroot -p123456

mysql登录（隐藏密码）：mysql -uroot -p

exit，退出mysql

describe/desc mysql，查看表结构

select version，查看数据库版本号

select database()，查看使用的是哪个数据库

show databases;，查看所有数据库

注意：sql语句不区分大小写

案例数据准备

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT(
    DEPTNO int(2) not null,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP(
    EMPNO int(4)  not null,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	);

CREATE TABLE SALGRADE(
    GRADE INT,
	LOSAL INT,
	HISAL INT );

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22', 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02', 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28', 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01', 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09', 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17', 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08', 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23', 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03', 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23', 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

一、单表查询

1.简单查询

注意：在数据库中字符串中使用 ‘ 单引号 ’ 括起来

2.条件查询

select
	字段1, 字段2, 字段3
from
	表名
where
	条件;

注意：在数据库中null不能使用等号判断，必须使用 var is null；var is not null；

3.排序order by

- 1.升序排序(默认)
select
	字段1, 字段2, 字段3
from
	表名
order by
	字段n (asc);
- 2.降序排序
select
	字段1, 字段2, 字段3
from
	表名
order by
	字段n desc;
- 3.多字段排序(只有字段i相等才启用字段i+1)
select
	字段1, 字段2, 字段3
from
	表名
order by
	字段i desc, 字段i+1 desc;

4.数据处理函数

数据处理函数又称单行处理函数（一个输入对应一个输出）

注意：null只要参与运算，最终的结果一定为null

5.分组处理函数

分组处理函数又称多行处理函数（多个输入对应一个输出）

注意：

1. 分组函数必须先进行分组（默认整张表为一组数据），然后才能使用
2. 分组函数不能直接使用在where子句中【注意】

6.分组查询

先对数据进行分组，然后对每一组的数据进行操作、计算

select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...

固定的执行顺序：

1. from：先从对应表中查询数据
2. where：条件查询
3. group by：根据限制进行分组
4. having：使用having子句继续筛选
5. select：查询结果
6. order by：进行分组处理
7. limit

注意：在一条select语句中如果有group by语句时，select后面只能有参加分组的字段、以及分组的函数，其他一律不能添加。

练习1-1：找出每个部门，不同工作岗位的最高薪资？

解题关键：将两个字段联合成一个字段，进行查看（两个字段联合分组）。

select
	deptno, job, max(sal)
from
	emp
group by
	deptno, job;

补充：使用having子句可以对分组之后的数据，进行进一步过滤（where和having优先使用where）

二、多表查询

1.连接查询：

从一张表中单独查询称为单表查询，

emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字，称为跨表查询。

多张表联合起来从中查询数据，称为连接查询。

（1）笛卡尔积现象：

select ename, dname from emp, dept;

result1：当两表进行连接查询时，无任何条件限制时，最后查询出的结果条数为两张表条数的乘积，被笛卡尔积现象（4 * 14 = 56）

select
	ename, dname
from
	emp, dept
where
	emp.deptno = dept.deptno;

result2：当两表进行连接查询时，有限制条件时对笛卡尔积的结果进行了筛选（14条）。

注意：在添加限制条件之后，虽然查询结果减少了（54->14），但查询匹配的次数仍然没有减少。可以对查询代码进行优化如下：

select
	emp.ename, dept.dname
from
	emp, dept
where
	emp.deptno = dept.deptno;

可以使用别名操作进一步简化sql语句（表的连接查询都需要使用别名简化）：

select
	e.ename, d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

查询结果都是一样的效果，但查询效率得到了提升。

总结：通过笛卡尔积现象得出，表的连接次数越多效率越低，应尽量减少表的连接次数。

（2）内连接查询：

内连接主要包括有等值连接、非等值连接和自连接。

<1>等值连接：

两表查询时约束条件为等值关系，故称为等值连接。

案例2-1：查询每个员工所在的部门名称，显示员工名和部门名称？

#sql92语法
select
	e.ename, d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;
	
#sql99语法
select
	e.ename, d.dname
from
	emp e
(inner) join
	dept d
on
	e.deptno = d.deptno;

注意：

1. sql92语法：结构不清晰，表的连接条件和后期进一步筛选的条件都放到了where的后面。
2. sql99语法：表连接的条件是独立的，如果需要对连接的结果进行进一步筛选，再后继续添加where即可。

<2>非等值连接：

两表查询时约束条件不是一个等量关系，则称为非等值连接。

案例2-2：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select
	e.ename, e.sal, s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

<3>自连接：

案例2-3：查询员工的上级领导，要求显示员工名和对应的领导名？

#内连接之自连接：技巧一张表看作两张表
select
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno; -- 员工的领导编号等于领导的员工编号

（3）外连接查询：

<1>右外连接/右连接：

将右边表的数据全部查询出来，捎带着关联查询左边的表。

#右外连接
select
 e.ename, d.dname
from
	emp e right join dept d
on
	e.deptno = d.deptno;

# right的含义:表示将join关键字右边的表看成主表，主要是为了将右边表的数据全部查询出来，捎带着关联查询左边的表

注意：内连接与外连接的区别：在外连接中两张表产生了主次关系，在内连接中两张表是平等的（没有主次关系）。

<2>左外连接/左连接：

将左边表的数据全部查询出来，捎带着关联查询右边的表。

#左外连接
select
	e.ename, d.dname
from
	dept d left join emp e
on
	e.deptno = d.deptno;

注意：外连接的查询结果条数一定大于内连接的查询结果条数。

案例2-4：查询每个员工的上机领导，要求显示所有员工的名字和领导名?

# 查询每个员工的上机领导，要求显示所有员工的名字和领导名
-- select
-- 	a.ename as '员工名', b.ename as '领导名'
-- from
-- 	emp a
-- join
-- 	emp b
-- on
-- 	a.mgr = b.empno;
select
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;

（4）多表连接：

多表连接语法如下：

select
	...
from
	a
join
	b
on
	a与b内连接的限制条件
join
	c
on
	a和c内连接的限值条件
right join
	d
on
	a和d右外连接的限值条件	

注意：一条sql中内连接和外连接可以混合出现。

案例2-5：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名称、薪资、薪资等级？

# 3.多表连接
select
	e.ename, e.sal, d.dname, s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

案例2-6：找出每个员工的部门名称以及工资等级，还有上级领导。要求显示员工名、领导名、部门名称、薪资、薪资等级？（增加表）

select
	e.ename, e.sal, d.dname, s.grade, l.ename
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp l
on 
	e.mgr = l.empno;

2.子查询：

select语句中嵌套着子查询，被嵌套的select语句称为子查询。

select
	...(select)...
from
	...(select)...
where
	...(select)...

（1）where中的子查询：

案例2-7：找出比最低工资高的员工姓名和工资？

select
	ename, sal
from
	emp
where
	sal > min(sal);

注意：分组函数不能直接使用在where子句中【注意】

select
	min(sal)              
from               
	emp;

select
	ename, sal
from
	emp
where
	sal > 800;

select
	ename, sal
from
	emp
where
	sal > (	select min(sal) from emp );

（2）from中的子查询：

解题技巧：from后面的子查询，可以将子查询的查询结果当做一张临时表。

案例2-8：找出每个岗位的平均工资的薪资等级？

• step1：找出每个岗位的平均工资（按照岗位求平均值）

select
	job, avg(sal)
from
	emp
group by
	job;

• step2：克服心理障碍，把以上查询的结果当做一张完整的数据表进行下一步处理。

下一步处理，将t表与salgrade表进行表的连接查询，查询条件为：avg(sal) between s.losal and s.hisal

select
	t.*, s.grade
from
	t
join
	salgrade s
on
	avg(sal) between s.losal and s.hisal;

• step3：将构建的虚拟t表带入得到最终的sql语句

select
	t.*, s.grade
from
	(select job, avg(sal) as avgsal from emp group by job) as t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

（3）select中的子查询：

注意：该内容了解即可

案例2-9：找出每个员工的部门名称，要求显示员工名、部门名？

select
	e.ename, (select d.dname from dept d where e.deptno = d.deptno)
from
	emp e;

select 
	e.ename, dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

注意：对于select后面的子查询，子查询只能一次返回1条结果，否则报错

3.distinct去除重复记录：

语法：distinct 字段

select distinct job from emp; 

select distinct job, deptno from emp; 

distince出现在所有字段前方，表示后面所有的字段联合起来，再去除重复记录。

4.union合并查询结果集

案例2-10：查询工作岗位是MANAGER和SALESMAN的员工？

#写法1:or
select
	e.ename, e.job
from
	emp e
where
	e.job = 'MANAGER' or e.job = 'SALESMAN';

#写法2:in
select
	e.ename, e.job
from
	emp e
where
	e.job in ('MANAGER', 'SALESMAN');

select e.ename, e.job from emp e where e.job = 'MANAGER'
union
select e.ename, e.job from emp e where e.job = 'SALESMAN';

注意：

1. union效率更高，对于表的连接每次匹配满足笛卡尔积，但是union不仅减少了匹配次数，并还能够完成两个结果集的拼接。
2. union在进行结果集合并的时候，要求两个结果集的列数必须相同。
3. union在进行结果集合并的时候，oracle要求两个结果集的列数据类型必须相同（mysql不要求）。

5.limit取子集

limit是将查询结果集的一部分取出来，通常使用在网页的分页查询中。

注意：分页的作用是为了提高用户体验。

limit语法：limit startIndex, length；（起始下标为0）

缺省语法：limit length

例如：按照薪资降序，取出排名在前5名的员工？

select
	ename, sal
from
	emp
order by
 sal desc
limit 0, 5;

注意：

1. 在mysql中，limit必须在order by之后执行。
2. limit应用分页：若每页显示pageSize条记录，则第pageNo页为：limit （pageNo - 1） * pageSize， pageSize;