MySql 加强

三范式

第一范式：保证每列的原子性 必须遵循
第二范式：保证一张表只描述一件事情 不是必须遵循
第三范式----保证每列都和主键直接相关 不是必须遵循

2****、****SQL 分类

2.1****、数据查询语言（****DQL****）

其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字

SELECT 是 DQL（也是所有 SQL）用得最多的动词，其他 DQL 常用的保留字有 WHERE，ORDER

BY，GROUP BY 和 HAVING。这些 DQL 保留字常与其他类型的 SQL 语句一起使用。

2.2****、数据定义语言（****DDL****）

其语句包括动词 CREATE 和 DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP

TABLE）；为表加入索引等。DDL 包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询

的一部分。

2.3****、数据操作语言（****DML****）其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行，Insert /

Update / Delete。也称为动作查询语言。

2.4****、事务处理语言（****TCL****）

它的语句能确保被 DML 语句影响的表的所有行及时得以更新。TCL 语句包括 BEGIN

TRANSACTION，COMMIT 和 ROLLBACK。

2.5****、数据库控制语言（****DCL****）

它的语句通过 GRANT 或 REVOKE 获得许可，确定单个用户和用户组对数据库对象的访问。某些

RDBMS 可用 GRANT 或 REVOKE 控制对表单个列的访问。

2.6****、指针控制语言（****CCL****）

它的语句，像 DECLARE CURSOR，FETCH INTO 和 UPDATE WHERE CURRENT 用于对一个或多

个表单独行的操作。

二、单表查询

语法 :

SELECT [DISTINCT] * | 字段 [别名] [, 字段 [别名]]

FROM 表名称 [别名]

[WHERE 条件(S)]

[ORDER BY 字段 [ASC|DESC] [, 字段 [ASC|DESC], …]];

3****、多表查询分类

内连接查询

隐式内连接查询

SELECT [DISTINCT] * | 字段 [别名] [, 字段 [别名], …]

FROM 表名称 [别名], [表名称 [别名], …]

[WHERE 条件(S)/消除笛卡尔积连接]

[ORDER BY 排序字段 [ASC|DESC] [, 排序字段 [ASC|DESC], …]];

显示内连接查询

SELECT table1.column, table2.column

FROM table1 [INNER] JOIN table2 ON table1.column1 = table2.column2

WHERE 条件

显示内连接查询：查询的结果和隐式内连接一模一样。区别在于：

显示内连接可以看到 [INNER] JOIN；

消除笛卡尔积条件使用写在 ON 子句。

外连接查询左外连接查询

右外连接查询

查询出 JOIN 左边表的全部数据查询出来，JOIN 右边的表不匹配的数据使用 NULL 来填充数据。

全外连接查询

查询出 JOIN 右边表的全部数据查询出来，JOIN 左边的表不匹配的数据使用 NULL 来填充数据。

六、分组函数

1****、函数分类

2****、多行函数

COUNT()： AVG()：SUM()：MAX()：MIN()：

注意：

统计函数忽略空值，可以使用 IFNULL， 因为是 NULL 不会影响汇总值，但会影响汇总数量；

不能在 where 语句中使用分组函数。

七、分组查询

1****、分组语法

SELECT [DISTINCT] *|分组字段1 [别名] [, 分组字段2 [别名] ,…] | 统计函数

FROM 表名称 [别名], [表名称 [别名] , …]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [, 分组字段2 ,…]]

[ORDER BY 排序字段 ASC | DESC [, 排序字段 ASC | DESC]];

2****、使用分组注意

SELECT 子句出现的字段，要不在统计函数中，要不出现在 GROUP BY 子句中，否则不合理（整

体与个体）；

在GROUP BY 子句中出现的字段，可以不出现在 SELECT 列表中；

统计函数可以单独使用，SQL 中可以没有 GROUP BY 子句；

在 GROUP BY 子句中，可以按单列进行分组，也可以在多列上进行分组，多列分组就是按照多个

字段的组合进行分组，最终的结果也会按照分组字段进行排序显示。

3****、分组限定

不能在 WHERE 子句中对分组限定，限制组须使用 HAVING 子句；

不能在 WHERE 子句中使用统计函数，而在 HAVING 子句可使用统计函数。

八、单行函数

九、子查询

1****、定义和作用

子查询指的就是在一个查询之中嵌套了其他的若干查询。

2****、分类

3****、单行单列

4****、多行单列

5****、多行多列

单表查询 练习演示

练习：
1. 查询所有员工信息

SELECT * FROM emp

查询每个员工的编号、姓名、职位

SELECT EMPNO,ENAME,JOB FROM emp

查询所有部门信息
SELECT DEPTNO FROM emp

2、消除重复
DISTINCT 关键字可以用于一列，也可以用于多列。比如：SELECT distinct job,deptno FROM emp; 只
有当 job 和 deptno 相同，才认为是重复的数据。
练习：
查询所有有员工的部门编号
SELECT DISTINCT DEPTNO FROM emp

查询有员工的部门和职位
SELECT DISTINCT DEPTNO,JOB FROM emp

3、算术运算符
对 NUMBER 型数据可以使用算数操作符创建表达式（+ - * /）；
对 DATE 型数据可以使用算数操作符创建表达式（+ -）。
练习：
查询所有员工的年薪
SELECT sal,sal*12 FROM emp
查询所有员工的年薪（使用别名）
SELECT sal,sal*12 yearsal FROM emp 

4.过滤查询
练习：
查询所有员工的年薪((月薪 + 奖金) * 12)
SELECT sal*12,comm*12,(sal+IFNULL(COMM,0))*12 FROM emp

查询有奖金的员工信息
#不为null 且不为0  才叫有奖金
SELECT * FROM emp where comm IS NOT NULL AND comm!=0
查询公司的老板
SELECT * FROM emp WHERE mgr is null

查询出基本工资高于 1500 的所有员工信息
SELECT * FROM emp where sal>1500

查询名字叫 SCOTT 的员工所从事的工作
SELECT ENAME,JOB FROM emp WHERE ENAME='SCOTT'

查询 1981 年入职的员工信息
SELECT * FROM emp where HIREDATE BETWEEN '1981-01-01' and '1981-12-31'
SELECT * FROM emp where HIREDATE LIKE '1981%';
查询年薪小于 3W 的员工
SELECT * FROM emp where (sal*12) < 30000

查询所有不是销售人员的员工信息
SELECT * FROM emp where JOB!='SALESMAN'

查询工资在 2000-3000 之间的员工信息
SELECT * from emp WHERE sal BETWEEN 2000 and 3000
查询 1981 年入职的员工
SELECT * FROM emp where HIREDATE BETWEEN '1981-01-01' and '1981-12-31'

查询工资为 800 或 1600 或 3000 的员工
SELECT * FROM emp where sal in (800,1600,3000)

查询出所有雇员姓名是以 A 开头的全部雇员信息。
SELECT * FROM emp where ename LIKE 'A%'

查询出雇员姓名第二个字母是 M 的全部雇员信息。
SELECT * from emp where ename like '_M%'

查询出雇员姓名任意位置上包含字母 A 的全部雇员信息。
SELECT * FROM emp WHERE ENAME LIKE ('%A%')

多表查询 练习演示

# 隐式 内连接
练习：
查询员工编号，员工名称，员工所属部门的编号和名称
SELECT e.DEPTNO,ENAME,d.DEPTNO,DNAME FROM emp e,dept d  where e.DEPTNO=d.DEPTNO  
SELECT EMPNO,ENAME,e.DEPTNO,d.DNAME 
from emp e 
JOIN dept d on e.DEPTNO=d.DEPTNO
查询员工的姓名，工资，所在部门的名称，以及工资的等级
SELECT ENAME,SAL,DEPTNO,GRADE FROM emp e , salgrade s where SAL>LOSAL and SAL<HISAL
SELECT ENAME,SAL,DEPTNO,GRADE FROM emp e , salgrade s where SAL BETWEEN LOSAL AND HISAL
#显式内连接
SELECT ENAME,SAL,DNAME,GRADE FROM emp e
JOIN dept d ON e.DEPTNO=d.DEPTNO 
JOIN salgrade on sal BETWEEN LOSAL AND HISAL

# DEPTNO 部门  DNAME 部门名称  LOC 位置
#EMPNO 编号  ENAME 名称  JOB 职位  MGR 上级  HIREDATE 入职日期  SAL 工资  COMM 奖金 DEPTNO 部门
#GRADE LOSAL HISAL


# 显式内连接查询
练习：
查询员工编号，员工名称，员工所属部门的编号和名称
SELECT e.EMPNO , e.ENAME, d.DEPTNO , d.DNAME 
FROM emp e
JOIN dept d ON e.DEPTNO=d.DEPTNO


查询员工的姓名，工资，所在部门的名称，以及工资的等级
SELECT ENAME,SAL,d.DEPTNO,DNAME,GRADE,LOSAL,HISAL
FROM emp e
JOIN dept d on e.DEPTNO=d.DEPTNO
JOIN salgrade ON sal BETWEEN LOSAL AND HISAL

#左外连接查询 和 右外连接查询
查询出员工的编号，名字，薪水和所在部门的名称（使用内连接查询）
SELECT EMPNO,ENAME, SAL,e.DEPTNO 
FROM emp e
left JOIN dept d ON e.DEPTNO=d.DEPTNO 

SELECT EMPNO,ENAME, SAL,e.DEPTNO 
FROM emp e
left JOIN dept d ON e.DEPTNO=d.DEPTNO 

# 六 , 分组函数
/*注意：
统计函数忽略空值，可以使用 IFNULL， 因为是 NULL 不会影响汇总值，但会影响汇总数量；
不能在 where 语句中使用分组函数。*/
练习：
查询所有员工每个月的平均工资及总工资
SELECT avg(sal) , sum(sal) from emp

查询月薪在 2000 以上的员工总人数
SELECT count(*) FROM emp where sal>2000
SELECT count(*) from emp where sal>2000
查询员工最高工资和最低工资差距 
SELECT max(sal),min(sal),max(sal)-min(sal) FROM emp 
SELECT ename , max(sal) from emp
SELECT ename , min(sal) from emp
SELECT max(sal)-min(sal)from emp

# 2.分组语法
练习：
按照职位分组，求出每个职位的最高和最低工资
SELECT job,max(sal),min(sal) from emp GROUP BY job
SELECT job,max(sal),min(sal) from emp GROUP BY job
查询出每一个部门员工的平均奖金
SELECT DEPTNO,sum(IFNULL(comm,0))/count(*) from emp GROUP BY DEPTNO
SELECT DEPTNO,avg(IFNULL(comm,0)) from emp GROUP BY DEPTNO
3.使用分组注意
练习：
查询出每一个部门员工的平均工资

SELECT DEPTNO,avg(IFNULL(sal,0)) from emp GROUP BY DEPTNO
查询各个部门和岗位的平均工资
SELECT DEPTNO,job,avg(sal)FROM emp GROUP BY DEPTNO,job
SELECT DEPTNO,job,AVG(sal)  from emp GROUP BY DEPTNO,job

4、分组限定
不能在 WHERE 子句中对分组限定，限制组须使用 HAVING 子句；
不能在 WHERE 子句中使用统计函数，而在 HAVING 子句可使用统计函数。
练习：
查询部门平均工资高于 2000 的部门及其平均工资   先分组 再 确定
SELECT DEPTNO,AVG(sal) from emp GROUP BY DEPTNO HAVING avg(sal) 

SELECT dname,e.DEPTNO,AVG(sal) 
from emp e 
JOIN dept d on e.DEPTNO=d.DEPTNO GROUP BY e.DEPTNO HAVING avg(sal)>2000

SELECT year(HIREDATE)a,count(*) from emp GROUP BY year(HIREDATE) HAVING a in(1980,1981,1982)

查询在 80, 81, 82 年各进公司多少人
SELECT year(HIREDATE) a,count(*) FROM emp GROUP BY year(HIREDATE) HAVING a IN(1980,1981,1982) 

单行单列
查询出工资比 MARTIN 还要高的全部雇员信息
SELECT * from emp where sal>(SELECT sal FROM emp where ename='MARTIN')
SELECT * FROM emp where sal>(SELECT sal FROM emp where ename='MARTIN')
查询平均工资高于公司平均工资的部门信息
SELECT DEPTNO,avg(sal) from emp GROUP BY DEPTNO HAVING avg(sal)>(SELECT avg(sal) FROM emp)

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp)

多行单列
查询工资等于部门经理（职位是 MANAGER）的员工信息。
SELECT * from emp GROUP BY deptno  HAVING sal in(SELECT sal FROM emp WHERE job='MANAGER')


多行多列
查询出每个部门的编号、名称、部门人数、平均工资
select e.deptno,d.dname,e.a,e.c from (select deptno,avg(sal) a,COUNT(*) c from emp GROUP BY deptno) e join dept d on e.deptno=d.deptno

# DEPTNO 部门  DNAME 部门名称  LOC 位置
#EMPNO 编号  ENAME 名称  JOB 职位  MGR 上级  HIREDATE 入职日期  SAL 工资  COMM 奖金 DEPTNO 部门
#GRADE LOSAL HISA