学习和复习mysql这一篇就够了

第一章 mysql概述

1、常用指令

（1）MySQL的启动和关闭语句

可以用指令开启和关闭mysql服务

net stop MySQL  
net start MySQL
1
2

注意需要以管理员的身份才可以启动和停止

（2）MySQL的登录语句

在控制台中输入即可登录本地数据库

密码可以直接写在p的后面,也可以回车之后再写,这样的安全性更高

mysql -uroot  -p密码
1

（3）MySQL的退出语句

exit
1

（4）查看MySQL的版本号

select version();
1

（5）其它常见命令

查看所有数据库

show databases;
1

使用特定的数据库

use 数据库名;
1

创建数据库

create 数据库名;	
1

查看当前使用的数据库下所有表

show tables;
1

查看当前使用的数据库

select database();
1

注意： “;”,也就是分号表示语句结束！

\c用来终止一条命令的输入。

2、SQL语句的五种分类

1. DQL-Data Query Language：数据查询语言：select、from、where(凡是带有select关键字的都是查询语句)
2. DML-Data Manipulation Language：数据操作语言：insert（增）、update（删）、delete（改）。主要是操作表中数据的操作。
3. DDL-Data Definition Language：数据定义语言：create（增）、drop（删）、alter（改）、truncate。主要是对表结构进行操作。
4. DCL-Data Controller Language：数据控制语言：grant（授予）、revoke（撤销权限)。
5. TCL-Transactional Controller Language：事务控制语言：commit（事务提交）、rollback（事务回滚)。

第二章 DQL语言：数据查询语言

1、基础查询

(1) 数据库的导入
首先创建一个数据库

mysql>create database bjpowernode;
1

导入数据库

mysql> source 路径名.sql`
1

注意:路径中不要有中文

导入的数据中有三张表：dept是部门表，emp是员工表，salgrade 是工资等级表

三张表的数据分别为
dept(部门表)

emp(员工表)

salgrade(工资表)

下面的例子将通过这三张表进行展开

(2) 查询数据库中表的数据

(3)查询表的结构

desc 表名;
1

(4）简单查询DQL

1.查询一个字段

select 字段名 from  表名;
1

其中要注意：select和from都是关键字。字段名和表名都是标识符。

2、查询多个字段：使用逗号隔开

查询部门编号和部门名

select deptno,dname from emp;
1

3、查询所有字段

方法一：把每个字段写上，逗号隔开

select a,b,c,d,e from tablename;
1

方法二：
*,这个符号代表的是查询所有的字段

select * from tablename;
1

方法二的缺点：效率低，可读性差；实际开发中不建议使用

（5）给查询的列起别名

select 字段名 as 字段别名 from 表名;
1

注意：只是将显示的查询结果显示为name，原表还是：dname
记住：select语句不会对数据进行修改。

as 关键字也可以省略不写

select deptno,dname deptname from dept;
1

如果起别名的时候,别名中间有空格的化,注意要加引号,

 select deptno,dname ‘dept name’ from dept; //加单引号
 select deptno,dname “dept name” from dept; //加双引号

1
2
3

注意：在所有的数据库当中，字符串统一使用单引号括起来。
单引号是标准，双引号在oracle数据库中用不了，mysql中可以使用

案例：计算员工年薪

查询员工的的月薪和年薪

mysql> select ename, sal as monthsal, (sal*12) as yearsal from emp;
+--------+----------+----------+
| ename  | monthsal | yearsal  |
+--------+----------+----------+
| SMITH  |   800.00 |  9600.00 |
| ALLEN  |  1600.00 | 19200.00 |
| WARD   |  1250.00 | 15000.00 |
| JONES  |  2975.00 | 35700.00 |
| MARTIN |  1250.00 | 15000.00 |
| BLAKE  |  2850.00 | 34200.00 |
| CLARK  |  2450.00 | 29400.00 |
| SCOTT  |  3000.00 | 36000.00 |
| KING   |  5000.00 | 60000.00 |
| TURNER |  1500.00 | 18000.00 |
| ADAMS  |  1100.00 | 13200.00 |
| JAMES  |   950.00 | 11400.00 |
| FORD   |  3000.00 | 36000.00 |
| MILLER |  1300.00 | 15600.00 |
+--------+----------+----------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

如果起中文别名的化,要加引号

mysql> select ename, sal '月薪', (sal*12) '年薪' from emp;
+--------+---------+----------+
| ename  | 月薪       | 年薪        |
+--------+---------+----------+
| SMITH  |  800.00 |  9600.00 |
| ALLEN  | 1600.00 | 19200.00 |
| WARD   | 1250.00 | 15000.00 |
| JONES  | 2975.00 | 35700.00 |
| MARTIN | 1250.00 | 15000.00 |
| BLAKE  | 2850.00 | 34200.00 |
| CLARK  | 2450.00 | 29400.00 |
| SCOTT  | 3000.00 | 36000.00 |
| KING   | 5000.00 | 60000.00 |
| TURNER | 1500.00 | 18000.00 |
| ADAMS  | 1100.00 | 13200.00 |
| JAMES  |  950.00 | 11400.00 |
| FORD   | 3000.00 | 36000.00 |
| MILLER | 1300.00 | 15600.00 |
+--------+---------+----------+
14 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

去除重复的字段

SELECT DISTINCT 字段名 FROM 表名;
1

2、条件查询

一、语法

SELECT 查询列表(可以有多个) FROM 表名 WHERE 筛选条件;
1

二、分类

1. 条件运算符：>、>=、<、<=、=、<=>、!=、<>(不等于同!=)
2. 逻辑运算符：and、or、not
3. 模糊运算符：
   • like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义
   • between and
   • not between and
   • in
   • is null
   • is not null

三、演示

条件运算符

下面举其中的一个例子,其它的条件运算符的查询差不多

查询薪资等于800的员工姓名和编号和薪资

mysql> select empno, ename, sal from emp where sal=800;
+-------+-------+--------+
| empno | ename | sal    |
+-------+-------+--------+
|  7369 | SMITH | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

在条件运算符中需要注意的是:
=只能判断普通类型的数值，而<=>不仅可以判断普通类型的数值还可以判断NULL
!=和<>都是判断不等于的意思，但是MySQL推荐使用<>

逻辑运算符

mysql> select empno, ename, sal from emp where sal>=2450 and sal<=3000;
mysql> select empno, ename, sal from emp where sal between 2450 and 3000;  //两句功能一样
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

注意：使用between and必须左小右大；并且between and是闭区间

案例：查询哪些员工的津贴/补助为null

mysql> select empno, ename, sal from emp where comm is null;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7566 | JONES  | 2975.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
10 rows in set (0.00 sec)

mysql> select empno, ename, sal from emp where comm is not null;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7654 | MARTIN | 1250.00 |
|  7844 | TURNER | 1500.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

案例：查询工作岗位是MANAGER并且（或者工资大于2500的员工信息

mysql> select empno, ename, job, sal from emp where job='MANAGER' and sal>2500;
+-------+-------+---------+---------+
| empno | ename | job     | sal     |
+-------+-------+---------+---------+
|  7566 | JONES | MANAGER | 2975.00 |
|  7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2 rows in set (0.00 sec)

mysql> select empno, ename, job, sal from emp where job='MANAGER' or sal>2500;
+-------+-------+-----------+---------+
| empno | ename | job       | sal     |
+-------+-------+-----------+---------+
|  7566 | JONES | MANAGER   | 2975.00 |
|  7698 | BLAKE | MANAGER   | 2850.00 |
|  7782 | CLARK | MANAGER   | 2450.00 |
|  7788 | SCOTT | ANALYST   | 3000.00 |
|  7839 | KING  | PRESIDENT | 5000.00 |
|  7902 | FORD  | ANALYST   | 3000.00 |
+-------+-------+-----------+---------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

注意：and优先级比or高。

in、not in

案例：查询工作岗位是MANAGER或SALESMAN的员工；查询薪资是800和5000的员工信息

mysql> select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec)

mysql> select * from emp where sal in (800,5000);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

案例：查询薪资不是800,5000,3000的员工信息

mysql> select * from emp where sal not in (800,5000,3000);
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+
10 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

like(模糊查询)

称为模糊查询，支持%或下划线匹配
​ %：匹配任意多个字符
​ 下划线：任意一个字符

1.​ 找出名字中含有O的

mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

2.找出名字以T结尾的

mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

3.找出名字以K开始的

mysql> select ename from emp where ename like 'K%';
+-------+
| ename |
+-------+
| KING  |
+-------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

4.找出第二个字每是A的

mysql> select ename from emp where ename like '_A%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

5.找出名字中有“ _”的，转义字符实现

mysql> select name from t_student where name like '%\_%'; // \转义字符。
		+----------+
		| name     |
		+----------+
		| jack_son |
		+----------+

1
2
3
4
5
6
7

注意：=、!=不能用来判断NULL、而<=>、is null 、 is not null可以用来判断NULL，但注意<=>也可以判断普通类型的数值

3、排序查询

(1)升序:asc

查询所有员工薪资，排序。升序

select ename,sal from emp order by sal;     //两句语句一样，默认就为升序
select ename,sal from emp order by sal asc;   
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

(2)降序：desc

select ename,sal from emp order by sal desc;
1

(3)多个字段的排序

案例：查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。

案例分析：分主次。sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。

mysql> select ename,sal from emp order by sal asc, ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

(4)按字段位置排序

了解一下，不建议这样排序。因为不健壮，很容易列顺序发生改变。基本上不会这样使用

select ename,sal from emp order by 2;   //按照第二列排序  索引从1开始
1

总结:

以上语句的执行顺序必须掌握：
	第一步：from
	第二步：where
	第三步：select
	第四步：order by（排序总是在最后执行！）
1
2
3
4
5

4、单行处理函数

(1) 介绍

即数据处理函数。当然还有多行处理函数
单行处理函数的特点：一个输入对应一个输出
多行处理函数的特点：多个输入对应一个输出
1
2
3

使用的通用语法

在这里特别强调一下,只要是使用[ ]扩起来的表市扩起的内容可写可不写

SELECT 函数名(实参列表) 【FROM 表】;
1

比如查看版本号,其它的函数的使用类似

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.61    |
+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

(2)分类

1. 字符函数
   concat：连接字符
   substr：截取子串
   replace：替换字符
   upper：变大写
   lower：变小写
   lpad：左填充
   rpad：右填充
   length：获取字节长度
   trim：去除前后空格
   instr：获取子串第一次出现的索引
2. 数学函数
   round：四舍五入
   ceil：向上取整
   floor：向下取整
   mod：取模运算（a-a/b*b）
   truncate：保留小数的位数，不进行四舍五入
   rand：获取随机数，返回0-1之间的小数
3. 日期函数
   now：返回当前日期+时间
   curdate：返回当前日期
   curtime：返回当前时间
   year：返回年
   month：返回月
   day：返回日
   hour：小时
   minute：分钟
   second：秒
   monthname：以英文形式返回月
   datediff：返回两个日期相差的天数
   date_format：将日期转换成字符
   str_to_date：将字符转换成日期

格式符：
%Y：四位的年份
%y：二位的年份
%m：二位的月份（01，02，...，12）
%c：一位的月份（1，2，...，12）
%d：日（01，02，...，31）
%H：小时（24小时制）
%h：小时（12小时制）
%i：分钟（00，01，02，...，59）
%s：秒（00，01，02，...，59）    

1
2
3
4
5
6
7
8
9
10
11

4. 控制函数
   if：判断函数
   case：分支函数

1、IF(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

2、case的格式一：
    CASE 变量或字段或表达式 
      WHEN 常量1 THEN 值1 
      WHEN 常量2 THEN 值2
      ...
      ELSE 值n 
    END ;
    
3、case的格式二：
    CASE
      WHEN 条件1 THEN 值1 
      WHEN 条件2 THEN 值2
      ...
      ELSE 值n 
    END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

5. 其它函数
   version：当前数据库的版本
   database：当前打开的数据库
   user：当前登录的用户
   password(‘字符’)：返回该字符的密码形式
   md5(‘字符’)：返回该字符的md5加密形式

(3)、举例

1. lower():转换成了小写

mysql> select lower(ename) from emp;
+--------------+
| lower(ename) |
+--------------+
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
14 rows in set (0.01 sec)

mysql> select lower(ename) as name from emp;(取别名之后)
+--------+
| name   |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+
14 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

2、substr()

注意：起始索引从1开始

mysql> select substr(ename,1,1) as ename from emp;
+-------+
| ename |
+-------+
| S     |
| A     |
| W     |
| J     |
| M     |
| B     |
| C     |
| S     |
| K     |
| T     |
| A     |
| J     |
| F     |
| M     |
+-------+
14 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

案例：挑选首字母为A的人名

mysql> select ename from emp where ename like 'A%';
mysql> select ename from emp where substr(ename,1,1)='A';   //两句功能一样
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

4. concat()：字符串拼接
   案例：将empno字段和ename字段拼接起来

mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH           |
| 7499ALLEN           |
| 7521WARD            |
| 7566JONES           |
| 7654MARTIN          |
| 7698BLAKE           |
| 7782CLARK           |
| 7788SCOTT           |
| 7839KING            |
| 7844TURNER          |
| 7876ADAMS           |
| 7900JAMES           |
| 7902FORD            |
| 7934MILLER          |
+---------------------+
14 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

5. trim()：去空格

mysql> select * from emp where ename='  KING';
Empty set (0.00 sec)

mysql> select * from emp where ename=trim('  KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

5、多行处理函数

多行处理函数也就是分组函数

多行处理函数的特点:输入多行,
注意：分组函数在使用时必须先分组，然后才能用。如果你没有对数据进行分组，整张表默认为一组。
(1)五个分组函数

• count()：计数
• sum()：求和
• avg()：平均值
• max()：最大值
• min()：最小值

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

mysql> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
|         14 |
+------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

使用的时候需要注意

第一点：分组函数自动忽略NULL，不需要提前对NULL进行处理。

例如：求sum时不会把NULL加进去。

第二点：分组函数中count(*)和count(具体字段)有什么区别？

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)
comm之所以为4,是因为在该字段中有10数据是null,没有进行统计
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
count(*)：统计表的总行数。如果某一行所有字段都为空,这样的行就不存在,也没有存在的意义。只要有一列不为null,这一行就是有意义的。

第三点:分组函数不能够直接使用在where子句中

例：找出比最低工资高的员工信息

下面的sql语句表面上看的没有问题,但是运行一下就会发现回报错。
想一下,这个是为什么呢?
学完分组查询(group by)之后就会明白了。

mysql> select ename,sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function
1
2

第四点：所有的分组函数可以组合起来一起用

mysql> select sum(sal),max(sal),min(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | max(sal) | min(sal) | avg(sal)    | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 |  5000.00 |   800.00 | 2073.214286 |       14 |
+----------+----------+----------+-------------+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

需要特别注意的是:

1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型

2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算：select sum(distinct 字段) from 表;

4、一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段
1
2
3
4
5
6
7
8
9

6、分组查询

这个内容非常重要

什么是分组查询？

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。
如：

• 计算每个部门的工资和
• 计算每个工作岗位的平均薪资
• 找出每个工作岗位的最高薪资
• 这个时候我们需要使用分组查询，怎么进行分组查询呢？

(1).语法

SELECT 
  查询列表 
FROM
  表 
【where 筛选条件】 
GROUP BY 分组的字段 
【having 分组后的筛选】
【order BY 排序的字段】 ;
1
2
3
4
5
6
7
8

以上关键字的执行顺序不能颠倒,

1. from
2. where
3. group
4. select
5. order by

注意关键字的书写顺序和最后的执行顺序没有必然联系,select写在最前面并不是说它最先执行。

通过这个执行顺序,现在就可以解决刚才的问题了。
select * from emp where sal > min(sal) //报错

因为分组函数在使用的时候必须先分组之后才能使用。where执行的时候还没有分组,所以where后面不能出现分组函数。

那再想一想?
select sum(sal) from emp;这个没有分组,为啥sum()函数可以用呢?
因为从上面的执行顺序可以知道select在group by之后执行的,而在上面的语句中虽然没有写group by,但是实际上在没有写的情况下是将整张表分成了一组。

强调:只有分完组了之后才可以执行分组函数

(2)特点

1、和分组函数一同查询的字段必须是group by后出现的字段

2、筛选分为两类：分组前筛选和分组后筛选
            	针对的表				 语句位置			   连接的关键字
	分组前筛选	 分组前的原始表			group by前			where
	分组后筛选	 分组后的结果集			group by后			having
	
3、分组可以按单个字段也可以按多个字段

4、分组可以搭配着排序使用

1
2
3
4
5
6
7
8
9
10
11

结论：select语句中，如果有group by语句，select后面只能跟参加分组的字段，以及分组函数，其它的一律不能跟。

例：找出每个工作岗位的工资和

mysql> select job, sum(sal) from emp group by job order by sal;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)

mysql> select job, sum(sal) from emp group by job order by sum(sal);
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
| ANALYST   |  6000.00 |
| MANAGER   |  8275.00 |
+-----------+----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

对于下面的执行语句要注意,没有对ename进行分组,但是select后面跟了ename,查询虽然没有报错,可以执行,但是在oracle中执行报错。oracle的语法比mysql的语法严格。(mysql的语法相对来说不是很严格)

重要结论:

在一条select语句中,如果有group by语句的话,select后面只能跟:参与分组的字段,以及分组函数,其它的一律不能跟

mysql> select ename,job,sum(sal) from emp group by job order by sal;
+-------+-----------+----------+
| ename | job       | sum(sal) |
+-------+-----------+----------+
| SMITH | CLERK     |  4150.00 |
| ALLEN | SALESMAN  |  5600.00 |
| JONES | MANAGER   |  8275.00 |
| SCOTT | ANALYST   |  6000.00 |
| KING  | PRESIDENT |  5000.00 |
+-------+-----------+----------+
5 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11

例：找出每个部门的最高薪资

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

实例：找出“每个部门，不同工作岗位”的最高薪资

mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

having的使用

having可以对分完组之后的数据进一步过滤；having必须和group by联合使用。
having不能单独使用，having不能代替where
优化策略：where和having，优先选择where，where实在完成不了了，再选择having。

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

先将大于3000找出来,在进行分组

mysql> select deptno,max(sal) from emp where sal >3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

先进行分组,再找出大于3000的(使用这种方式比上面的效率更加的高)

mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

无法使用where的情况：找出每个部门平均薪资，要求显示平均薪资高于2500的
下面的情况是无法使用where完成的,必须使用having才可以完成要求

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

大总结

select
...
where
...
group
...
having
...
order by
...
1
2
3
4
5
6
7
8
9
10

以上的关键字只能按照这个顺序来,不能颠倒
执行顺序?

1. from
2. where
3. group by
4. having
5. select
6. order by

从某张表中查询数据,

实例：找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排。

mysql>  select job,avg(sal) from emp where job <> 'MANAGER' group by job having avg(sal) >1500 order by avg(sal) desc;

mysql>  select job,avg(sal) from emp where job not in ('MANAGER') group by job having avg(sal) >1500 order by avg(sal) desc;

mysql> select job,avg(sal) from emp where job != 'MANAGER' group by job having avg(sal)>1500 order by avg(sal) desc;    //三句功能一样
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+
1
2
3
4
5
6
7
8
9
10
11

去重distinct的使用

注意：原表数据不会被修改，只是查询结果去重

mysql> select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+
14 rows in set (0.00 sec)

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

这样编写是错误的，语法错误：select ename, distinct job from emp;
注意：distinct只能出现在所有字段的最前方
例：distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重

mysql> select job, deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| SALESMAN  |     30 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
| CLERK     |     20 |
| CLERK     |     30 |
| ANALYST   |     20 |
| CLERK     |     10 |
+-----------+--------+
14 rows in set (0.00 sec)

mysql> select distinct job, deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+
9 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

例：去除重复之后统计一下工作岗位的数量

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.01 sec)

1
2
3
4
5
6
7
8

7、连接查询

(本章中最重要的内容,也是本章最复杂的内容)

(1) 、连接查询的简介

(1)简介

连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

(2)分类

(3)注意

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
1
2
3

注意:通过笛卡尔积现象得出,表的连接次数越多效率越低,尽量避免表的连接次数。实在没有办法避免的话,那就没有办法了。

在本节中用到的三张表

员工表
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

部门表
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

工资等级表
mysql> select * from salgrade
    -> ;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

(2). 内连接:inner

1. 内连接之等值连接

案例：查询每个员工所在部门名称，显示员工名和部门名
SQL92语法：

select 
	e.ename,d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;
1
2
3
4
5
6

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。
SQL99语法：inner可以省略（带着inner可读性更好）

mysql> select
    -> e.ename,d.dname
    -> from
    -> emp e
    -> inner join            //inner可以省略,
    -> dept d
    -> on
    -> e.deptno = d.deptno;  // 条件是等量关系，所以被称为等值连接。
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

sql99优点：表
连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

	select 
		...
	from
		a
	join
		b
	on
		a和b的连接条件
	where
		筛选条件

1
2
3
4
5
6
7
8
9
10
11

2. 非等值连接
   案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

mysql> select
    -> e.ename, e.sal, s.grade
    -> from
    -> emp e
    -> inner join    //inner可以省略
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

3. 内连接之自连接

实例：查询员工的上级领导，要求显示员工名和对应的领导名

mysql> select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;
+--------+--------+
| 员工名      | 领导名     |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
13 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

以上就是内连接中的：自连接，技巧：一张表看做两张表。
13条记录没有KING，因为KING没有领导

(3)、外连接

外连接分为左外连接和右外连接,下面的例子为右外连接

select e.ename,d.dname 
from 
emp e right join dept d
on
e.deptno = d.deptno
1
2
3
4
5

mysql> select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

由于这个是右连接,所有会将dept 表中的所有数据都查询出来,即使某个部门没有人,也会将其查询出来。
right代表什么:表示将join关键字右边的这张表看成是主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
在外连接中两张表产生了主次关系,而内连接两张表的关系是平等的。

左外连接
outer是可以省略的，带着可读性强。不管左连接还是右连接的outer都可以省略

select 
	e.ename,d.dname
from
	dept d 
left outer join   //outer可以省略
	emp e
on
	e.deptno = d.deptno;

1
2
3
4
5
6
7
8
9

注意:

• 带有right的是右外连接，又叫做右连接。
• 带有left的是左外连接，又叫做左连接。
• 任何一个右连接都有左连接的写法。(比如在上图中将dept d 和 emp e换一下)
• 任何一个左连接都有右连接的写法。

思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数的嘛？ 正确

实例：查询每个员工的上级领导，要求显示所有员工的名字和领导名

mysql> select a.ename, b.ename from emp a left join emp b on a.mgr=b.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)
从结果可以到即使员工没有上级领导也需要查询出来,可以看到这个没有上级领导的是公司的首级执行官
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

(4) 多张表连接

select 
	...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
right join
	d
on
	a和d的连接条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

一条SQL中内连接和外连接可以混合。都可以出现
案例找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

案例：找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级

mysql> select a.ename,b.ename,d.dname,a.sal,s.grade from emp a left join emp b on a.mgr = b.empno join dept d on a.deptno = d.deptno join salgrade s on a.sal between s.losal and s.hisal;
+--------+-------+------------+---------+-------+
| ename  | ename | dname      | sal     | grade |
+--------+-------+------------+---------+-------+
| SMITH  | FORD  | RESEARCH   |  800.00 |     1 |
| ALLEN  | BLAKE | SALES      | 1600.00 |     3 |
| WARD   | BLAKE | SALES      | 1250.00 |     2 |
| JONES  | KING  | RESEARCH   | 2975.00 |     4 |
| MARTIN | BLAKE | SALES      | 1250.00 |     2 |
| BLAKE  | KING  | SALES      | 2850.00 |     4 |
| CLARK  | KING  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | JONES | RESEARCH   | 3000.00 |     4 |
| KING   | NULL  | ACCOUNTING | 5000.00 |     5 |
| TURNER | BLAKE | SALES      | 1500.00 |     3 |
| ADAMS  | SCOTT | RESEARCH   | 1100.00 |     1 |
| JAMES  | BLAKE | SALES      |  950.00 |     1 |
| FORD   | JONES | RESEARCH   | 3000.00 |     4 |
| MILLER | CLARK | ACCOUNTING | 1300.00 |     2 |
+--------+-------+------------+---------+-------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

(5) 子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。
子查询都可以出现在哪里呢？

	select
		..(select).
	from
		..(select).
	where
		..(select).
1
2
3
4
5
6

注意:where子句中不能直接使用分组函数。

mysql> select ename,sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function
1
2

实现思路：
第一步：查询最低工资是多少

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1
2
3
4
5
6

​ 第二步：找出大于800的

mysql> select sal from emp where sal > 800;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

第三步合并：

mysql> select ename,sal from emp where sal >(select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

from子句中的子查询

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

案例：找出每个岗位的平均工资的薪资等级。
第一步：找出每个工作岗位的平均工资

mysql> select avg(sal) from emp group by job;
+-------------+
| avg(sal)    |
+-------------+
| 3000.000000 |
| 1037.500000 |
| 2758.333333 |
| 5000.000000 |
| 1400.000000 |
+-------------+

1
2
3
4
5
6
7
8
9
10
11

第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t。

mysql> select s.grade,t.* from(select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
+-------+-----------+-------------+
| grade | job       | avgsal      |
+-------+-----------+-------------+
|     1 | CLERK     | 1037.500000 |
|     2 | SALESMAN  | 1400.000000 |
|     4 | ANALYST   | 3000.000000 |
|     4 | MANAGER   | 2758.333333 |
|     5 | PRESIDENT | 5000.000000 |
+-------+-----------+-------------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

select后面出现的子查询（这个内容不需要掌握，了解即可！！！
找出每个员工的部门名称，要求显示员工名，部门名

mysql> select
    -> e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname
    -> from
    -> emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

(6) 合并查询：union

案例：查询工作岗位是MANAGER和SALESMAN的员工

mysql> select ename, job from emp where job='MANAGER' or job='SALESMAN';
mysql> select ename,job from emp where job in ('MANAGER', 'SALESMAN');

mysql> select ename, job from emp where job='MANAGER'
    -> union
    -> select ename, job from emp where job='SALESMAN';   //三条语句结果一样
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻…
但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。
比如:

注意事项：

错误：union在进行结果集合并的时候，要求两个结果集的列数相同。

select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';
ERROR 1222 (21000): The used SELECT statements have a different number of columns

1
2
3
4
5

对于数据类型不一致MYSQL可以，oracle语法严格 ，不可以，报错。 要求：结果集合并时列和列的数据类型也要一致。

(7). 分页函数：limit

作用：显示部分查询结果，通常使用在分页查询当中。
完整用法：limit startIndex, length
实例：按照薪资降序，取出排名在前5名的员工

mysql> select ename,sal from emp order by sal desc limit 5;
mysql> select ename,sal from emp order by sal desc limit 0,5;   //第一个参数为起始索引，默认从0开始  第二个参数为长度
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

注意：mysql当中limit在order by之后执行！
实例：取出工资排名在[3-5]名的员工
每页显示3条记录
第1页：limit 0,3
第2页：limit 3,3
第3页：limit 6,3
第4页：limit 9,3
第pageNo页：limit (pageNo - 1) * pageSize , pageSize
总结:
书写顺序如下

select 
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...
1
2
3
4
5
6
7
8
9
10
11
12
13
14

总结:本章中最难的是内外连接的并用

第三章 DML 数据操作语言

本节在刚才的数据库中增加几个表,进行测试

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	classno int primary key,
	classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references t_class(classno)
);
insert into t_class(classno, classname) values(100, '北京市大兴区亦庄镇第二中学高三1班');
insert into t_class(classno, classname) values(101, '北京市大兴区亦庄镇第二中学高三1班');
insert into t_student(name,cno) values('jack', 100);
insert into t_student(name,cno) values('lucy', 100);
insert into t_student(name,cno) values('lilei', 100);
insert into t_student(name,cno) values('hanmeimei', 100);
insert into t_student(name,cno) values('zhangsan', 101);
insert into t_student(name,cno) values('lisi', 101);
insert into t_student(name,cno) values('wangwu', 101);
insert into t_student(name,cno) values('zhaoliu', 101);

select * from t_student;
select * from t_class;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

DML：数据操作语言：insert（增）、update（删）、delete（改）。主要是操作表中数据的操作

1 DML增：insert

#方式一：
INSERT INTO 表名(字段名,...) VALUES(值,...);

#方式二：
INSERT INTO 表名 SET 字段名=值,字段名=值,...;
1
2
3
4
5

特点:

1. 要求值的类型和字段的类型要一致或兼容
2. 字段的个数和顺序不一定与原始表中的字段个数和顺序一致，但必须保证值和字段一一对应
3. 假如表中有可以为null的字段，注意可以通过以下两种方式插入null值：①字段和值都省略、②字段写上，值使用null
4. 字段和值的个数必须一致
5. 字段名可以省略，默认所有列
6. 方式一支持一次插入多行，语法如下：INSERT INTO 表名【(字段名,…)】 VALUES(值,…),(值,…),…;
7. 方式一支持子查询，语法如下：INSERT INTO 表名 查询语句;

举例
1、方式一：插入数据
插入多个

insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
1

插入单个

insert into t_student(name) values('wangwu');
1

需要注意的是
注意：前面字段名省略的话，等于都写上了！所以值也要都写上！

mysql> insert into t_student values(2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+------+------+------+--------------+
| no   | name | sex  | age  | email        |
+------+------+------+------+--------------+
|    1 | NULL | m    | NULL | NULL         |
|    2 | lisi | f    |   20 | lisi@123.com |
+------+------+------+------+--------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

插入日期：str_to_date()
str_to_date：将varchar类型转换成date类型
date_format：将date类型转换成varchar类型
注意：标识符全部小写，单词之间使用下划线。

drop table if exists t_user;                  //删除表
create table t_user(                          //重新创建表
    id int,
    name varchar(32),
    birth date
    );
1
2
3
4
5
6

mysql> insert into  t_user(id,name,birth) values(1,'zs','01-10-1990');  //插入数据
ERROR 1292 (22007): Incorrect date value: '01-10-1990' for column 'birth' at row 1
1
2

出问题了：原因是类型不匹配。数据库birth是date类型，这里给了一个字符串varchar。

可以使用str_to_date函数进行类型转换，将字符串转换成日期类型date

语法格式：str_to_date(‘字符串日期’, ‘日期格式’)
mysql的日期格式

	%Y	年
	%m  月
	%d  日
	%h	时
	%i	分
	%s	秒
1
2
3
4
5
6

mysql> insert into  t_user(id,name,birth) values(1,'zs',str_to_date('01-10-1990','%d-%m-%Y'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | zs   | 1990-10-01 |
+------+------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

str_to_date函数把字符串varchar转换成日期date类型，通常在插入insert时使用

注意：如果你提供的日期字符串是这个格式：%Y-%m-%d，可以直接插入，str_to_date函数就不需要了

mysql> insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | zs   | 1990-10-01 |
|    2 | lisi | 1990-10-01 |
+------+------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

查询的时候可以以某个特定的日期格式展示。date_format函数将日期类型转换成特定格式的字符串。
语法： date_format(日期类型数据, ‘日期格式’)

mysql>  select id,name,date_format(birth,'%m/%d/%y') as birth from t_user;
+------+------+----------+
| id   | name | birth    |
+------+------+----------+
|    1 | zs   | 10/01/90 |
|    2 | lisi | 10/01/90 |
+------+------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

mysql> select id,name,birth from t_user;
		+------+----------+------------+
		| id   | name     | birth      |
		+------+----------+------------+
		|    1 | zhangsan | 1990-10-01 |
		|    2 | lisi     | 1990-10-01 |
		+------+----------+------------+
1
2
3
4
5
6
7

以上语句进行了默认格式转换，自动将数据库中的date类型转换成varchar类型。默认的日期格式：’%Y-%m-%d’

date和datetime类型区别
date是短日期：只包括年月日信息。
datetime是长日期：包括年月日时分秒信息。
mysql短日期默认格式：%Y-%m-%d
mysql长日期默认格式：%Y-%m-%d %h:%i:%s

mysql> create table t_user(     //创建表
    -> id int,
    -> name varchar(32),
    -> birth date,
    -> create_time datetime    //长日期类型
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2021-09-14 15:49:50');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user values(1,'zhangsan','1990-10-01','2021-09-14 15:49:50');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
+------+----------+------------+---------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

获取系统当前时间
now() 函数获取的时间带有时分秒信息！是datetime类型。

mysql> insert into t_user values(3,'lisi','1990-10-01',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    3 | lisi     | 1990-10-01 | 2021-11-25 15:50:02 |
+------+----------+------------+---------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

一次插入多条数据
语法：insert into t_user(字段名1,字段名2) values(), (), (), ();

mysql> insert into t_user values
    -> (1, 'zs', '1980-10-11', now()),
    -> (2, 'ls', '1981-10-11', now()),
    -> (3, 'ww', '1982-10-11', now());
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_user;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | zs   | 1980-10-11 | 2021-11-29 20:55:54 |
|    2 | ls   | 1981-10-11 | 2021-11-29 20:55:54 |
|    3 | ww   | 1982-10-11 | 2021-11-29 20:55:54 |
+------+------+------------+---------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2 DML改：update

注意没有写明条件,将会导致所有的字段都更新。
语法

1、单表更新
UPDATE 表名 SET 列 = 值,... WHERE 查询条件;

2、多表更新
#sql92语法： 
UPDATE 
  表1 别名,
  表2 别名 
SET
  列 = 值,
  ...
WHERE 连接条件 AND 筛选条件 ;

#sql99语法： 
UPDATE 
  表1 别名 
INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件
  SET 列 = 值,
  ...
WHERE 筛选条件 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

多表更新

#修改张无忌的女朋友的手机号为13899888899，魅力值为1000
sql92语法：
UPDATE 
  boys bo,
  beauty b 
  SET b.`phone` = '13899888899',
      bo.`userCP` = 1000 
WHERE bo.`id` = b.`boyfriend_id` AND bo.`boyName` = '张无忌' ;

#修改张无忌的女朋友的手机号为13899888899，魅力值为1000
sql99语法：
UPDATE 
  boys bo 
INNER JOIN beauty b ON bo.`id` = b.`boyfriend_id`
  SET b.`phone` = '13899888899',
      bo.`userCP` = 1000 
WHERE bo.`boyName` = '张无忌' ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3 DML删：delete

语法格式： delete from 表名 where 条件;

详细的介绍和语法格式

1、单表删除 
DELETE FROM 表名 【WHERE 筛选条件 】;

2、多表删除（级联删除）
sql92语法： 
DELETE 
  表1的别名,
  表2的别名 
FROM
  表1 别名,
  表2 别名 
WHERE 连接条件 AND 筛选条件 ;

sql99语法： 
DELETE 
  表1的别名,
  表2的别名 
FROM
  表1 别名 
INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件 
WHERE 筛选条件 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

注意：没有条件，整张表的数据会全部删除！

mysql> delete from t_user where id = 3;   //删除id=3的数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
+------+----------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> insert into t_user(id) values(2);   //插
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    2 | NULL     | NULL       | NULL                |
+------+----------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> delete from t_user;             //删全表
Query OK, 3 rows affected (0.01 sec)

mysql> select * from t_user;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

总结:
快速创建表?
原理,将一个表的查询结果当作一张表新建,这个可以完成表的快速复制,表创建出来,同时表中的数据也才存在。
create table t_user as select empno,ename from emp where job = 'manager';

快速删除表中的数据?
delete语句删除数据的原理?
表中的数据被删除了,但是这个数据在硬盘上的真实存储空间不会被释放,这种删除的缺点是:删除效率比较低,
这种删除的优点是:支持回滚,后悔了可以再恢复数据。(这种操作属于DML语句)

truncate语句删除数据的原理?
这种删除效率比较高,表被删除了一次截断,物理删除,这种删除的缺点,不支持回滚,这种删除的有点:快速。
用法:truncate table user(这种操作属于DDL操作)

如果一张非常大的表,有上亿条记录?
删除的时候,使用delete也许需要执行1个小时才能完全删除。效率较低。
可以选择使用truncate删除表中的数据,只需要不到1秒钟的时间就删除介绍,效率较高,但是使用truncate之前,必须仔细询问客户是否真的要删除,并警告删除之后不可恢复。(这个是删除表钟的胡数据,但是表还在)

删除表结构?
drop table 表名 //这个不是删除表钟的数据,这是把表删除

第五章 DDL 数据定义语言

表一旦确定下来一般不会更改表的结构,所以本节不是很重要
DDL：数据定义语言：create（增）、drop（删）、alter（改）、truncate。主要是对表结构进行操作。

1 库的管理

注意:用[ ]扩起来的表示可写可不写

库的创建

CREATE DATABASE 【IF NOT EXISTS】 库名 【 CHARACTER SET 字符集名】;
1

库的修改

#它已经被废弃
RENAME DATABASE 旧库名 TO 新库名;

#修改库字符集
ALTER DATABASE 库名 CHARACTER SET 字符集名;
1
2
3
4
5

库的删除

DROP DATABASE 【IF EXISTS】 库名;
1

2 表的管理

表的创建

CREATE TABLE 【IF NOT EXISTS】 表名 (
  字段名 字段类型 【约束】,
  字段名 字段类型 【约束】,
  ...
  字段名 字段类型 【约束】
) ;
1
2
3
4
5
6

表的修改

1、添加列
ALTER TABLE 表名 ADD COLUMN 列名 类型 【FIRST|AFTER 字段名】;

2、修改列的类型或约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 【新约束】;

3、修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;

4、删除列
ALTER TABLE 表名 DROP COLUMN 列名;

5、修改表名
ALTER TABLE 表名 RENAME 【TO】 新表名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

表的删除

方式一：DROP TABLE 【IF EXISTS】 表名;

方式二：TRUNCATE TABLE 【IF EXISTS】 表名;
1
2
3

表的复制

1、复制表的结构
CREATE TABLE 表名 LIKE 旧表;

2、复制表的某些字段
CREATE TABLE 表名 
SELECT 字段1,字段2,... FROM 旧表 WHERE 0;

3、复制表的结构+数据
CREATE TABLE 表名 
SELECT 查询列表 FROM 旧表 【WHERE 筛选条件】;

4、复制表的某些字段+数据
CREATE TABLE 表名 
SELECT 字段1,字段2,... FROM 旧表 【WHERE 筛选条件】;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3 快速删除表中的数据：truncate

delete from dept_bak; //这种删除数据的方式比较慢。
delete原理： 表中数据被删除了，但是数据在硬盘上的真实存储空间不会被释放！
缺点：删除效率比较低； 优点：支持回滚（即可恢复）
回滚实例

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from dept_bak;
Query OK, 4 rows affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

truncate原理：表被一次截断，物理删除。
缺点：不支持回滚。 优点：效率高，快速。
语法：truncate table dept_bak;

mysql> truncate table dept_bak;          //删数据，不是删表
Query OK, 0 rows affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept_bak;          //回滚也没用
Empty set (0.00 sec)
1
2
3
4
5
6
7
8

truncate删除速度很快，但是数据不可恢复。

注意：truncate是删除表中的数据，表还在。删除表用drop

4 常见约束

约束是一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类

1. NOT NULL：非空，该字段的值必填
2. UNIQUE：唯一，该字段的值不可重复
3. DEFAULT：默认，该字段的值不用手动插入有默认值
4. CHECK：检查，MySQL不支持,oracle支持
5. PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
6. FOREIGN KEY：外键，该字段的值引用了另外的表的字段

特点
主键和唯一

#不同点：
1、一个表至多有一个主键，但可以有多个唯一
2、主键不允许为空，唯一可以为空

#相同点：
1、都具有唯一性
2、都支持组合键，但不推荐
1
2
3
4
5
6
7

外键

1、用于限制两个表的关系，从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3、主表的被引用列要求是一个key（一般就是主键）
4、插入数据，先插入主表；删除数据，先删除从表

可以通过以下两种方式来删除主表的记录
#方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
1
2
3
4
5
6
7
8
9
10
11

演示
1、创建表时添加约束

CREATE TABLE 表名(
	字段名 字段类型 NOT NULL,#非空
    字段名 字段类型 DEFAULT 值,#默认
	字段名 字段类型 PRIMARY KEY,#主键
	字段名 字段类型 UNIQUE,#唯一
	CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表(被引用列)
);
1
2
3
4
5
6
7

注意:

1. 列级约束支持：非空、默认、主键、唯一，不可以起约束名
2. 表级约束支持：主键、唯一、外键，可以起约束名，但是在MySQL中对主键无效
3. 列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

**需求,两个字段添加联合的约束**
例如
create table user{
	id int,
	name varchar(255),
	email varchar(255),
	unique(name,email)
}

这个时候name和email联合起来是唯一的
1
2
3
4
5
6
7
8
9
10

在mysql中如果一个字段同时被not null和unique同时修饰就自动变成了主键约束了
注意,not null没有表级的约束
任何一张表都应该有主键,没有主键的,这张表是无效的
表级约束,如果是两个字段联合起来当主键,被称作联合主键(复合主键)

不建议使用:varchar来做主键,主键一般都是数字,一般都是定长的!
主键除了单一主键和复合主键之外,还可以这样进行分类?
自然主键:主键值是一个自然数,和业务没有关系
业务组件:主键值和业务紧密相连,例如拿银行卡账号做主键值,这就是业务主键

在实际开发中使用业务主键比较多,还是使用自然主键多?
自然主键使用多一些,因为主键和业务挂钩,那么当业务发生变化的时候,可能会影响到主键值,所以业务主键不建议使用,尽量使用自然主键

create table user{
	id int,
	name varchar(255),
	email varchar(255),
	primary key(id,name)
}
id和name联合起来当作主键
1
2
3
4
5
6
7

在实际开发中不建议使用复合主键,建议使用单一的主键,因为主键的值存在的意义就是这行记录的身份证号,只要意义达到即可,单一主键可以做到。
一张表中只能有一个,下面的写法将会报错

create table user{
	id int primary key,
	name varchar(255) primary key,
	email varchar(255),
}
1
2
3
4
5

2、修改表时添加或删除约束

1、非空
添加非空（列级约束）
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NOT NULL;
删除非空
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;

2、默认
添加默认（列级约束）
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 DEFAULT 值;
删除默认
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;

3、主键
添加主键（列级约束）
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 PRIMARY KEY;
添加主键（表级约束）
ALTER TABLE 表名 add 【CONSTRAINT 约束名】 PRIMARY KEY(字段名);
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

4、唯一
添加唯一（列级约束）
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 UNIQUE;
添加唯一（表级约束）
ALTER TABLE 表名 add 【CONSTRAINT 约束名】 UNIQUE(字段名);
删除唯一
ALTER TABLE 表名 DROP INDEX 索引名;

5、外键
添加外键（表级约束）
ALTER TABLE 表名 add 【CONSTRAINT 约束名】 FOREIGN KEY(字段名) REFERENCES 主表(被引用列);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

5 自增长列

自增长列又称为标识列，它可以不用手动的插入值，系统提供默认的序列值

1. 不用手动插入值，可以自动提供序列值，默认从1开始，步长为1，如果要更改起始值，第一 次手动插入值，后续使用NULL，如果要更改步长，修改更改系统变量：SET 3. 3. 3. auto_increment_increment = 值;
2. 一个表至多有一个自增长列
3. 自增长列只能支持数值型
4. 自增长列必须为一个key

演示
1、创建表时添加自增长列

CREATE TABLE 表名 (
  字段名 字段类型 约束 AUTO_INCREMENT
) ;
1
2
3

2、修改表时添加或删除自增长列

添加自增长列
ALTER TABLE 表 MODIFY COLUMN 字段名 字段类型 约束 AUTO_INCREMENT;

删除自增长列
ALTER TABLE 表 MODIFY COLUMN 字段名 字段类型 约束;
1
2
3
4
5

6 外键约束

外键约束涉及到的相关术语：
外键约束：一种约束（foreign key）
外键字段：该字段上添加了外键约束
外键值：外键字段当中的每一个值。

业务背景：
请设计数据库表，来描述“班级和学生”的信息？

第一种方案：班级和学生存储在一张表中？？？
t_student
no(pk)          name        classno         classname
----------------------------------------------------------------------------------
1                   jack            100         北京市大兴区亦庄镇第二中学高三1班
2                   lucy            100         北京市大兴区亦庄镇第二中学高三1班
3                   lilei           100         北京市大兴区亦庄镇第二中学高三1班
4                   hanmeimei   100         北京市大兴区亦庄镇第二中学高三1班
5                   zhangsan        101         北京市大兴区亦庄镇第二中学高三2班
6                   lisi            101         北京市大兴区亦庄镇第二中学高三2班
7                   wangwu      101         北京市大兴区亦庄镇第二中学高三2班
8                   zhaoliu     101         北京市大兴区亦庄镇第二中学高三2班
分析以上方案的缺点：
    数据冗余，空间浪费！！！！
    这个设计是比较失败的！
 
第二种方案：班级一张表、学生一张表？？
 
t_class 班级表
classno(pk)         classname
------------------------------------------------------
100                 北京市大兴区亦庄镇第二中学高三1班
101                 北京市大兴区亦庄镇第二中学高三1班

t_student 学生表
no(pk)          name                cno(FK引用t_class这张表的classno)
----------------------------------------------------------------
1                   jack                100
2                   lucy                100
3                   lilei               100
4                   hanmeimei       100
5                   zhangsan            101
6                   lisi                101
7                   wangwu          101
8                   zhaoliu         101

当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。
所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。
那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

注意：
    t_class是父表
    t_student是子表

    删除表的顺序？
        先删子，再删父。

    创建表的顺序？
        先创建父，再创建子。

    删除数据的顺序？
        先删子，再删父。

    插入数据的顺序？
        先插入父，再插入子。

思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
    不一定是主键，但至少具有unique约束。
    
测试：外键可以为NULL吗？
    外键值可以为NULL。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	classno int primary key,
	classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) REFERENCES t_class(classno)
);
insert into t_class(classno,classname) values(100,'武汉市大兴区亦庄镇第二中学高三1班');
insert into t_class(classno,classname) values(101,'武汉市大兴区亦庄镇第二中学高三2班');

insert into t_student(name,cno) values('tom',100);
insert into t_student(name,cno) values('anliy',100);
insert into t_student(name,cno) values('jack',100);
insert into t_student(name,cno) values('jord',101);
insert into t_student(name,cno) values('dorf',101);
insert into t_student(name,cno) values('vnhy',101);

select * from t_student;
select * from t_class;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

注意一个问题,在用过命令行导入sql文件的时候一定要注意

第六章 DCL语言 数据库控制语言

本章的内容不是很常用,但是要学会复制粘贴
关于授权的权限列表：

1 创建用户

登录root用户之后,才可以进行如下的操作

CREATE USER 用户名@'IP地址' IDENTIFIED BY '密码';
注意：'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
1
2

2 删除用户

DROP USER 用户名@'IP地址';
注意：'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
1
2

注意如果创建用户的时候使用的是localhost的话,删除的时候也只能使用localhost,否者就会报错

3 用户授权(写的有点模糊)

GRANT 权限1,权限2,...... ON 数据库名.* TO 用户名@'IP地址' IDENTIFIED BY '密码';
注意：所有的数据库就用*.*，所有的权限就用all或者all privileges
1
2

4、撤销授权

REVOKE 权限1,权限2,...... ON 数据库名.* FROM 用户名@'IP地址' IDENTIFIED BY '密码';
注意：所有的数据库就用*.*，所有的权限就用all或者all privileges
1
2

5、刷新授权

FLUSH PRIVILEGES;
1

6、查看授权

SHOW GRANTS FOR 用户名@'IP地址';
注意：'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
1
2

7、修改密码(有点模糊)

#修改密码
SET PASSWORD = PASSWORD('123456');
#登录授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
#刷新授权
FLUSH PRIVILEGES;
1
2
3
4
5
6

8、忘记密码(有点模糊)

1、可以在配置文件里加上 skip-grant-tables ，注意写到[mysqld]参数组下，表示跳过授权
2、重启MySQL再登录就不需要密码，进去改密码，改完后，直接 FLUSH PRIVILEGES; 就可以使用新密码来登录了
（例：UPDATE mysql.user SET PASSWORD=PASSWORD("123456") WHERE USER="root" AND HOST="localhost";）
3、改完后记得去掉配置文件例的 skip-grant-tables，重新启动MySQL服务
4、再使用新的密码登录就可以了
1
2
3
4
5

第七章 存储引擎

1、什么是存储引擎，有什么用呢？

存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）
存储引擎这个名字高端大气上档次。
实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎，表存储数据的方式不同。

2、怎么给表添加/指定“存储引擎”呢？

show create table t_student;
 
    可以在建表的时候给表指定存储引擎。
    CREATE TABLE `t_student` (
      `no` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `cno` int(11) DEFAULT NULL,
      PRIMARY KEY (`no`),
      KEY `cno` (`cno`),
      CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
 
    在建表的时候可以在最后小括号的")"的右边使用：
        ENGINE来指定存储引擎。
        CHARSET来指定这张表的字符编码方式。
     
        结论：
            mysql默认的存储引擎是：InnoDB
            mysql默认的字符编码方式是：utf8
     
    建表时指定存储引擎，以及字符编码方式。
    create table t_product(
        id int primary key,
        name varchar(255)
    )engine=InnoDB default charset=gbk;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

3、怎么查看mysql支持哪些存储引擎呢？

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.36    |
+-----------+
 
命令： show engines \G
 
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

mysql支持九大存储引擎，当前5.5.36支持8个。版本不同支持情况不同。

4、关于mysql常用的存储引擎介绍一下

MyISAM存储引擎？
它管理的表具有以下特征：
使用三个文件表示每个表：
格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。
可被转换为压缩、只读表来节省空间

提示一下：
对于一张表来说，只要是主键，
或者加有unique约束的字段上会自动创建索引。

MyISAM存储引擎特点:
可被转换为压缩、只读表来节省空间
这是这种存储引擎的优势！！！！

MyISAM不支持事务机制，安全性低。

InnoDB存储引擎？
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征：
    – 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
    – InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）

    – 提供一组用来记录事务性活动的日志文件
    – 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
    – 提供全 ACID 兼容
    – 在 MySQL 服务器崩溃后提供自动恢复
    – 多版本（MVCC）和行级锁定
    – 支持外键及引用的完整性，包括级联删除和更新
 
InnoDB最大的特点就是支持事务：
    以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
    不能很好的节省存储空间。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

MEMORY存储引擎？
使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，
这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎管理的表具有下列特征：
    – 在数据库目录内，每个表均以.frm 格式的文件表示。
    – 表数据及索引被存储在内存中。（目的就是快，查询快！）
    – 表级锁机制。
    – 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。
1
2
3
4
5
6
7
8
9
10

第七章 事务

重要:核心,必须要精通事务

一个事务其实就是一个完整的业务逻辑，是最小的工作单元，不可再分。

什么是一个完整的业务逻辑？

假设转账，从A账户向B账户中转账10000.
将A账户的钱减去10000（update语句）
将B账户的钱加上10000（update语句）
这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。

这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。

只有DML语句才会有事务这一说：insert，delete，update三个语句，其它都没有关系。因为只有这三个语句是对数据库表种的数据尽心增,删,改的
数据的安全第一位。

思考一个问题?
假设世界上的所有业务,是要一条语句就能完成,还有必要存在事务机制吗?

正是因为做某件事的时候,需要多条DML语句共同联合起来才能完成,所以需要事务文档存在。如果任何一件复杂的事都能一条DML语句搞定,那么事务就没有存在的价值了。
到底什么是事务,说到底本质上,一个事务其实就是多条DML语句同时成功,或者同时失败!

一个事务其实就是多条DML语句同时成功，或者同时失败！

事务：就是批量的DML语句同时成功，或者同时失败

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
比如
事务开启了：
insert
insert
insert
delete
update
update
update
事务结束了！

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。 提交事务标志着，事务的结束。并且是一种全部成功的结束。

回滚事务
将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件, 回滚事务标志着事务的结束。并且是一种全部失败的结束。

1 事务的特点

1. 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行

2. 一致性：一个事务的执行不能破坏数据库数据的完整性和一致性

3. 隔离性：一个事务不受其它事务的干扰，多个事务是互相隔离的

4. 持久性：一个事务一旦提交了，则永久的持久化到本地,就相当于将没有保存到硬盘数据保存到硬盘上。

2 重点研究一个事务的隔离性

A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。这道墙越厚，表示隔离级别就越高。

事务和事务之间的隔离级别有哪些呢？

四种隔离级别
(1) 读未提交：read uncommitted

（最低的隔离级别）《没有提交就读到了》
事务A可以读取到事务B未提交的数据。
这种隔离级别存在的问题就是：
脏读现象！(Dirty Read)
这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

(2) 读已提交：read committed

提交之后才能读到
事务A只能读取到事务B提交之后的数据。
解决了什么问题？： 解决了脏读的现象。
存在什么问题？： 不可重复读取数据。
不可重复读取数据指：在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4称为不可重复读取。
这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
oracle数据库默认的隔离级别是：read committed

(3) 可重复读：repeatable read

《提交之后也读不到，永远读取的都是刚开启事务时的数据》
可重复读：事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变。
读解决了什么问题？：解决了不可重复读取数据。
存在的问题是什么？：可以会出现幻影读。
每一次读取到的数据都是幻象。不够真实！
早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！
读到的是假象。不够绝对的真实。
mysql中默认的事务隔离级别就是这个！

(4) 序列化/串行化：serializable

这是最高隔离级别，效率最低。解决了所有的问题。
这种隔离级别表示事务排队，不能并发！
synchronized，线程同步（事务同步）
每一次读取到的数据都是最真实的，并且效率是最低的。

验证各种隔离级别

查看隔离界别：

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |  //mysql默认的隔离级别
+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

下面主要一张简单的t_user表进行验证,刚开始的时候是一张空表

create table t_user(
name varchar(255)
);
1
2
3

验证：read uncommited
首先先设置连接到mysq的客户端,设置事务的隔离级别,设置之后,重新连接一下,发现隔离级别已经发生了改变。
同时开启两个黑窗口连接mysql,分别为事务A和事务B对应的黑窗口

mysql> set global transaction isolation level read uncommitted;

事务A		  							     				事务B
--------------------------------------------------------------------------------

1.use bjpowernode;
													2.use bjpowernode;
3.start transaction;
4.select * from t_user;
													5.start transaction;
												
													6.insert into t_user value('xaiownag');
7.select * from t_user;(查到了数据)
	
1
2
3
4
5
6
7
8
9
10
11
12
13
14

上面是执行顺序,最后如果在B事务种执行一下

在A事务种使用再查的时候发现查不到了

验证：read commited

mysql> set global transaction isolation level read committed;
事务A														事务B
--------------------------------------------------------------------------------

1.use bjpowernode;
													2.use bjpowernode;
3.start transaction;
													4.start transaction;
5.select * from t_user;(表现在为空)
													6.insert into t_user values('zhangsan');
7.select * from t_user;(查的不到数据)
													8.commit;
9.select * from t_user;(查到了数据)
1
2
3
4
5
6
7
8
9
10
11
12
13

上面是代码的执行顺序

验证：repeatable read

mysql> set global transaction isolation level repeatable read;
事务A													事务B
--------------------------------------------------------------------------------
1.use bjpowernode;
													2.use bjpowernode;
3.start transaction;
													4.start transaction;
5.select * from t_user;
													6.insert into t_user values('lisi');
													7.insert into t_user values('wangwu');
													8.commit;
9.select * from t_user;
1
2
3
4
5
6
7
8
9
10
11
12

可以看到即使B事务提交了,A事务读的还是空的,即使B事务将表删除了,A事务读到的还是空的表

验证：serializable

mysql> set global transaction isolation level serializable;
事务A													事务B
--------------------------------------------------------------------------------
1.use bjpowernode;
													2.use bjpowernode;
3.start transaction;
													4.start transaction;
5.select * from t_user;
6.insert into t_user values('abc');
													7.select * from t_user;(会一直卡在这里)
8.commit;																			

1
2
3
4
5
6
7
8
9
10
11
12

当执行7的时候会一直卡在那里不动,只有事务A种的8执行了以后,事务卡着的地方就会出现刚才事务插入的数据

3 事务的提交和回滚

提交事务：commit; 语句
回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）

事务对应的英语单词是：transaction

测试一下，在mysql当中默认的事务行为是怎样的
mysql默认情况下是支持自动提交事务的。（自动提交）

自动提交：每执行一条DML语句，则提交一次！

这种自动提交实际上是不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条。

怎么将mysql的自动提交机制关闭掉呢 ?
先执行这个命令：start transaction;(关系自动提交的机制)

回滚事务

mysql> select * from dept_bak;
Empty set (0.00 sec)

mysql> start transaction;   //关闭自动提交机制
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dept_bak values(10,'abc', 'tj');
Query OK, 1 row affected (0.00 sec)

mysql> insert into dept_bak values(10,'abc', 'tj');
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | abc   | tj   |
|     10 | abc   | tj   |
+--------+-------+------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept_bak;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

提交事务

mysql> select * from dept_bak;
Empty set (0.00 sec)

mysql> insert into dept_bak values(10,'abc','tj');
Query OK, 1 row affected (0.00 sec)

mysql> insert into dept_bak values(20,'abc','tj');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | abc   | tj   |
|     10 | abc   | tj   |
+--------+-------+------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | abc   | tj   |
|     10 | abc   | tj   |
+--------+-------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

4 事务的分类

1. 隐式事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete
2. 显式事务：具有明显的开启和结束，例如以下格式：

1、开启事务
set autocommit=0;#关闭自动提交
start transaction;#开启事务机制

2、编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete

【设置回滚点，可选项】
savepoint 回滚点名;

3、结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方： rollback to 回滚点名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

5 事务并发

一、事物的并发问题如何发生？
多个事务同时操作同一个数据库的相同数据时
二、事务的并发问题都有哪些？

1. 脏读：一个事务读到了另一个事务还未提交的update数据，导致多次查询的结果不一样
2. 不可重复读：一个事务读到了另一个事务已经提交的update数据，导致多次查询结果不一致
3. 幻读：一个事务读到了另一个事务已经提交的insert数据，导致多次查询的结果不一样
   事物的并发问题如何解决？
   通过设置隔离级别来解决并发问题
   隔离级别
   

6 丢失更新

第八章 高级部分

1 索引

(1) 什么是索引？

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
对于一本字典来说，查找某个汉字有两种方式：
第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。
效率比较低。
第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个
位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过
索引检索，效率较高。

t_user
	id(idIndex)	name(nameIndex)	email(emailIndex)		address  (emailAddressIndex)
	----------------------------------------------------------------------------------
	1				zhangsan...
	2				lisi
	3				wangwu
	4				zhaoliu
	5				hanmeimei
	6				jack

	select * from t_user where name = 'jack';
1
2
3
4
5
6
7
8
9
10
11

以上的这条SQL语句会去name字段上扫描，为什么？
因为查询条件是：name=‘jack’

如果name字段上没有添加索引（目录），或者说没有给name字段创建索引，
MySQL会进行全扫描，会将name字段上的每一个值都比对一遍。效率比较低。

MySQL在查询方面主要就是两种方式：
第一种方式：全表扫描
第二种方式：根据索引检索。
注意：
0 在实际中，汉语字典前面的目录是排序的，按照a b c d e f…排序，
为什么排序呢？因为只有排序了才会有区间查找这一说！（缩小扫描范围
其实就是扫描某个区间罢了！）

在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet
数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql
当中索引是一个B-Tree数据结构。
遵循左小又大原则存放。采用中序遍历方式遍历取数据。

(2) 索引的实现原理

假设有一张用户表：t_user

	id(PK)					name						每一行记录在硬盘上都有物理存储编号
	----------------------------------------------------------------------------------
	100						zhangsan					0x1111
	120						lisi						0x2222
	99							wangwu					0x8888
	88							zhaoliu					0x9999
	101						jack						0x6666
	55							lucy						0x5555
	130						tom
1
2
3
4
5
6
7
8
9
10
11

提醒事项:

提醒1：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，
因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动
创建索引对象。
提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有
一个硬盘的物理存储编号。
提醒3: 在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式
存在，
在MyISAM存储引擎中，索引存储在一个.MYI文件中。
在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。
在MEMORY存储引擎当中索引被存储在内存当中。
不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）

底层原理比较复杂,想了解更加详细的信息可以查阅更多的资料

(3) 在mysql当中，主键上，以及unique字段上都会自动添加索引的！！！！
什么条件下，我们会考虑给字段添加索引呢？

条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）(有一个场景就是如果用户反映查询速度太慢了,这个时候就需要优化,考虑添加索引,毕竟用户给了钱的)
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

(4) 索引怎么创建？怎么删除？语法是什么？

创建索引：
mysql> create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引，起名：emp_ename_index
删除索引：
mysql> drop index emp_ename_index on emp;
将emp表上的emp_ename_index索引对象删除。
1
2
3
4
5
6

(5)、在mysql当中，怎么查看一个SQL语句是否使用了索引进行检索？

mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
扫描14条记录：说明没有使用索引。type=ALL(全表进行扫描)

添加索引之后
mysql> create index emp_ename_index on emp(ename);

mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_ename_index | emp_ename_index | 33      | const |    1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
可以看到type是ref,并且只检索了一条就检索到了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

(6) 索引有失效的时候，什么时候索引失效呢？

重点了解一下第一种情况,尽量要知道每一种失效的理由,要对问题持有探索的心态

失效的第1种情况：
select * from emp where ename like '%T';
ename上即使添加了索引，也不会走索引，为什么？
	原因是因为模糊匹配当中以“%”开头了！
	尽量避免模糊查询的时候以“%”开始。
	这是一种优化的手段/策略。
mysql> explain select * from emp where ename like '%T';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12

失效的第2种情况：
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有
索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个
字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
1
2
3
4
5
6
7
8
9
10

失效的第3种情况：
使用复合索引的时候，没有使用左侧的列查找，索引失效
什么是复合索引？
	两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

create index emp_job_sal_index on emp(job,sal);

mysql> explain select * from emp where job = 'MANAGER';
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_job_sal_index | emp_job_sal_index | 30      | const |    3 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+

mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

失效的第4种情况：
	在where当中索引列参加了运算，索引失效。
	mysql> create index emp_sal_index on emp(sal);
	
	explain select * from emp where sal = 800;
	+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
	| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
	+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
	|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
	+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
	
	mysql> explain select * from emp where sal+1 = 800;
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

失效的第5种情况：
	在where当中索引列使用了函数
	explain select * from emp where lower(ename) = 'smith';
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1
2
3
4
5
6
7
8

(7)、索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
索引在数据库当中分了很多类？
单一索引：一个字段上添加索引。
复合索引：两个字段或者更多的字段上添加索引。
主键索引：主键上添加索引。
唯一性索引：具有unique约束的字段上添加索引。
…
注意：唯一性比较弱的字段上添加索引用处不大。

2 视图

(1)、什么是视图？

view:站在不同的角度去看待同一份数据。

(2)、怎么创建视图对和删除视图对象

为了不影响原有的表,可以快速复制一张表

表复制：
mysql> create table dept2 as select * from dept;
1
2

dept2表中的数据：
	mysql> select * from dept2;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	+--------+------------+----------+
1
2
3
4
5
6
7
8
9
10

创建视图对象：

create view dept2_view as select * from dept2;
1

删除视图对象：

drop view dept2_view;
1

注意：只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;

(3)、用视图做什么？

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致
原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）

//面向视图查询
select * from dept2_view; 
// 面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');
1
2
3
4

// 查询原表数据
	mysql> select * from dept2;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	|     60 | SALES      | BEIJING  |
	+--------+------------+----------+
// 面向视图删除
	mysql> delete from dept2_view;
// 查询原表数据
	mysql> select * from dept2;
	Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

// 创建视图对象
	create view 
		emp_dept_view
	as
		select 
			e.ename,e.sal,d.dname
		from
			emp e
		join
			dept d
		on
			e.deptno = d.deptno;
1
2
3
4
5
6
7
8
9
10
11
12

// 查询视图对象
	mysql> select * from emp_dept_view;
	+--------+---------+------------+
	| ename  | sal     | dname      |
	+--------+---------+------------+
	| CLARK  | 2450.00 | ACCOUNTING |
	| KING   | 5000.00 | ACCOUNTING |
	| MILLER | 1300.00 | ACCOUNTING |
	| SMITH  |  800.00 | RESEARCH   |
	| JONES  | 2975.00 | RESEARCH   |
	| SCOTT  | 3000.00 | RESEARCH   |
	| ADAMS  | 1100.00 | RESEARCH   |
	| FORD   | 3000.00 | RESEARCH   |
	| ALLEN  | 1600.00 | SALES      |
	| WARD   | 1250.00 | SALES      |
	| MARTIN | 1250.00 | SALES      |
	| BLAKE  | 2850.00 | SALES      |
	| TURNER | 1500.00 | SALES      |
	| JAMES  |  950.00 | SALES      |
	+--------+---------+------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

// 面向视图更新
	update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';
1
2

// 原表数据被更新
	mysql> select * from emp;
	+-------+--------+-----------+------+------------+---------+---------+--------+
	| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
	+-------+--------+-----------+------+------------+---------+---------+--------+
	|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
	|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
	|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
	|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
	|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
	|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
	|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
	|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
	|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
	|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
	|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
	|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
	|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
	|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
	+-------+--------+-----------+------+------------+---------+---------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

(4)、视图对象在实际开发中到底有什么用？《方便，简化开发，利于维护》

create view 
			emp_dept_view
		as
			select 
				e.ename,e.sal,d.dname
			from
				emp e
			join
				dept d
			on
				e.deptno = d.deptno;
		
1
2
3
4
5
6
7
8
9
10
11
12

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。
每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
可以把这条复杂的SQL语句以视图对象的形式新建。
在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。
并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要
修改视图对象所映射的SQL语句。

我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。
可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是
存储在硬盘上的，不会消失。

再提醒一下：
视图对应的语句只能是DQL语句。
但是视图对象创建完成之后，可以对视图进行增删改查等操作。

小插曲：
增删改查，又叫做：CRUD。
CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。
一般都说CRUD。

小插曲：
增删改查，又叫做：CRUD。
CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。
一般都说CRUD。
C:Create（增）
R:Retrive（查：检索）
U:Update（改）
D:Delete（删）

3 DBA的常用命令?

数据的导出

注意：在windows的dos命令窗口中：
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456

可以导出指定的表吗？
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456
1
2

数据导入？

注意：需要先登录到mysql数据库服务器上。
然后创建数据库：create database bjpowernode;
使用数据库：use bjpowernode
然后初始化数据库：source D:\bjpowernode.sql

第九章 数据库设计的三范式

(1) 什么是数据库设计范式？
数据库表的设计依据。教你怎么进行数据库表的设计

数据库设计范式共有3个
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，
不要产生部分依赖。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，
不要产生传递依赖。

声明：三范式是面试官经常问的，所以一定要熟记在心！

设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

(2)数据库三范式的详细介绍

第一范式
最核心，最重要的范式，所有表的设计都需要满足。
必须有主键，并且每一个字段都是原子性不可再分。

    学生编号     学生姓名   联系方式
	------------------------------------------
	1001		张三		zs@gmail.com,1359999999
	1002		李四		ls@gmail.com,13699999999
	1001		王五		ww@163.net,13488888888

	以上是学生表，满足第一范式吗？
		不满足，第一：没有主键。第二：联系方式可以分为邮箱地址和电话
	
	学生编号(pk)       学生姓名	邮箱地址         联系电话
	----------------------------------------------------
	1001				张三		zs@gmail.com	1359999999
	1002				李四		ls@gmail.com	13699999999
	1003				王五		ww@163.net		13488888888
1
2
3
4
5
6
7
8
9
10
11
12
13
14

第二范式

建立在第一范式的基础之上，

要求所有非主键字段必须完全依赖主键，不要产生部分依赖。

	学生编号        学生姓名  教师编号  教师姓名
	----------------------------------------------------
	1001			张三		001		王老师
	1002			李四		002		赵老师
	1003			王五		001		王老师
	1001			张三		002		赵老师
	
1
2
3
4
5
6
7

这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）
这是非常典型的：多对多关系！

分析以上的表是否满足第一范式？
不满足第一范式。
怎么满足第一范式呢？修改

学生编号+教师编号(pk)                 学生姓名      教师姓名
	----------------------------------------------------
	1001			001				张三			王老师
	1002			002				李四			赵老师
	1003			001				王五			王老师
	1001			002				张三			赵老师

1
2
3
4
5
6
7

学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）
经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？
不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
产生部分依赖有什么缺点？
数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。
为了让以上的表满足第二范式，你需要这样设计：
使用三张表来表示多对多的关系！！！！
学生表

		学生编号(pk)		        学生名字
		------------------------------------
		1001					张三
		1002					李四
		1003					王五
1
2
3
4
5

教师表
		教师编号(pk)		    教师姓名
		--------------------------------------
		001					王老师
		002					赵老师


1
2
3
4
5
6
7

学生教师关系表
id(pk)			        学生编号(fk)			       教师编号(fk)
------------------------------------------------------
1						1001						001
2						1002						002
3						1003						001
4						1001						002
1
2
3
4
5
6
7

背口诀： 多对多怎么设计？ 多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！

第三范式

第三范式建立在第二范式的基础之上
要求所有非主键字典必须直接依赖主键，不要产生传递依赖。

学生编号（PK）           学生姓名  班级编号      班级名称
---------------------------------------------------------
	1001				张三		01			一年一班
	1002				李四		02			一年二班
	1003				王五		03			一年三班
	1004				赵六		03			一年三班
1
2
3
4
5
6

以上表的设计是描述：班级和学生的关系。很显然是1对多关系！
一个教室中有多个学生。

分析以上表是否满足第一范式？
满足第一范式，有主键。

分析以上表是否满足第二范式？
满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。

分析以上表是否满足第三范式？
第三范式要求：不要产生传递依赖！

一年一班依赖01，01依赖1001，产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。

那么应该怎么设计一对多呢？

班级表：一
		班级编号(pk)				班级名称
		----------------------------------------
		01								一年一班
		02								一年二班
		03	
1
2
3
4
5
6

学生表：多

学生编号（PK） 学生姓名 班级编号(fk)
-------------------------------------------
1001				张三			01			
1002				李四			02			
1003				王五			03			
1004				赵六			03
1
2
3
4
5
6
7
8

背口诀： 一对多，两张表，多的表加外键！！！！！！！！！！！

(3) 总结表的设计？
一对多：一对多，两张表，多的表加外键！！！！！！！！！！！！
多对多：多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！
一对一：一对一放到一张表中不就行了吗？为啥还要拆分表？
在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。

一对一怎么设计？
			没有拆分表之前：一张表
				t_user
				id		login_name		login_pwd		real_name		email				address........
				---------------------------------------------------------------------------
				1			zhangsan		123				张三				zhangsan@xxx
				2			lisi			123				李四				lisi@xxx
				...
1
2
3
4
5
6
7
8

这种庞大的表建议拆分为两张：

t_login 登录信息表
				id(pk)		login_name		login_pwd	
				---------------------------------
				1				zhangsan		123			
				2				lisi			123			

				t_user 用户详细信息表
				id(pk)		real_name		email				address........	login_id(fk+unique)
				-----------------------------------------------------------------------------------------
				100			张三				zhangsan@xxx								1
				200			李四				lisi@xxx										2


				口诀：一对一，外键唯一！！！！！！！！！！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

(4) 总结

数据库设计三范式是理论上的。

实践和理论有的时候有偏差。 

最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，
并且对于开发人员来说，sql语句的编写难度也会降低。

面试的时候把这句话说上：他就不会认为你是初级程序员了！
1
2
3
4
5
6
7
8
9
10

参考资料

参考博客
https://blog.csdn.net/qq_39236499/article/details/121758197
https://blog.csdn.net/qq_38490457/article/details/107640904
参考视频
https://www.bilibili.com/video/BV1Vy4y1z7EX?share_source=copy_web&vd_source=1f198fa093790427b60b33b612ecabca
网盘资料
https://pan.baidu.com/s/1eFu2qQ_rYzos8-vghyhyfQ
提取码：java
重要资料
https://download.csdn.net/download/weixin_47994845/86398183