文章目录
- MySQL个人总结
- 1. 数据库概述
- 2. SQL语言
- 3.MySQL的数据类型
- 4. 常用的数据库命令
- 5. DDL(数据定义语言)-操作数据库/表
- 6. DML(数据操作语言) - 对表中的数据进行增删改
- 7. DQL(数据查询语言)-进行数据的查询
- 8. 表的约束
- 9.存储引擎
- 10.事务
- 11. 索引
- 12. 视图
- 13.数据库设计的三大范式
- 13.数据库设计的三大范式
MySQL个人总结
1. 数据库概述
1.1 什么是数据库
英文：DataBase，简称 DB
概念：数据仓库，软件，安装在操作系统之上
作用：存储数据，管理数据
1.2 什么是数据库管理系统
概念:管理数据库的大型软件
英文：DataBase Management System，简称 DBMS
1.3 数据库的分类
关系型数据库：SQL（Structured Query Language）
- MySQL、Oracle、Sql Server、DB2、SQLlite
- 通过表和表之间，行和列之间的关系进行数据的存储
- 关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的 二维表 组成的数据库
- 通过外键关联来建立表与表之间的关系
非关系型数据库：NoSQL（Not Only SQL）
- Redis、MongoDB
- 指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
2. SQL语言
2.1 SQL简介
英文：Structured Query Language，简称 SQL，结构化查询语言
操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准
SQL:结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。
SQL属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。
SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成。）
DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。
DBMS -(执行)-> SQL -(操作)-> DB
优点:
- 简单易学，具有很强的操作性
- 绝大多数重要的数据库管理系统均支持SQL
- 高度非过程化；用SQL操作数据库时大部分的工作由DBMS自动完成
2.2 SQL通用语法
通用语法
- SQL 语句可以单行或多行书写，以分号结尾。
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
注释
- 单行注释: – 注释内容 或 #注释内容(MySQL 特有)
- 多行注释: /* 注释 */
2.3 SQL的分类
DDL(数据定义语言)：create drop alter，用来定义数据库对象：数据库，表，列等
DML(数据操作语言)：insert delete update，对表当中的数据进行增删改。
DQL(数据查询语言): 查询语句，凡是select语句都是DQL。
DCL(数据控制语言): grant授权、revoke撤销权限等。
TCL(事务控制语言)：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
3.MySQL的数据类型
3.1 整数类型
根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的，
| 数据类型 | 字节数 | 描述 |
|---|---|---|
| TINYINT | 1 | 十分小的数据 |
| SMALLINT | 2 | 较小的数据 |
| MEDIUMINT | 3 | 中等大小的数据 |
| INT/INTEGER | 4 | 标准的整数 |
| BIGINT | 8 | 较大的数据 |
3.2 浮点数类型和定点数类型
在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。
| 数据类型 | 字节数 | 描述 |
|---|---|---|
| FLOAT | 4 | 单精度浮点数值 |
| DOUBLE | 8 | 双精度浮点数值 |
| DECIMAL（M,D） | M+2 | 定点数 |
DECIMAL类型的取值范围与DOUBLE类型相同。但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长 度，D表示的是小数点后的长度。
比如，将数据类型为DECIMAL(6,2)的数据6.5243 插人数据库后显示的结果为6.52
3.3 字符串类型
| 数据类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255bytes | 定长字符串 |
| VARCHAR | 0-65535bytes | 变长字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| TEXT | 0-65535 bytes | 长文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
tips:
VARCHAR:
- 可变长度的字符串,比较智能，节省空间。会根据实际的数据长度动态分配空间。
- 优点：节省空间
- 缺点：需要动态分配空间，速度慢。
CHAR
- 定长字符串,不管实际的数据长度是多少。分配固定长度的空间去存储数据。使用不恰当的时候，可能会导致空间的浪费。
- 优点：不需要动态分配空间，速度快。
- 缺点：使用不当可能会导致空间的浪费。
3.4 日期类型
| 数据类型 | 描述 | 格式 |
|---|---|---|
| DATE | 日期格式 | YYYY-MM-DD |
| time | 时间格式 | HH：mm：ss |
| datetime | 最常用的时间格式 | YYYY-MM-DD HH：mm：ss |
| TIMESTAMP | 时间戳，1970.1.1到现在的毫秒数 | |
| YEAR | 年份表示 |
3.5 NULL
- 没有值，未知
- 不要使用NULL值进行计算
4. 常用的数据库命令
注意:在SQL语句中 所有的语句都要以 分号 “;” 结尾
show databases; -- 查询所有的数据库
use 数据库名称; -- 使用哪个数据库
show tables; -- 查询数据库下有那些表
describe/desc 表名; --显示表的信息
create database 数据库名称; -- 创建数据库
exit; -- 退出数据库
-- --单行注释
# #单行注释
/**/
5. DDL(数据定义语言)-操作数据库/表
5.1 操作数据库
5.1.1 创建数据库
语法格式:
CREATE DATABASE [IF NOT EXISTS] 数据库名;
IF NOT EXISTS用中括号括起来的 表示可写可不写。意思就是:创建数据库如果这个数据库不存在
5.1.2 删除数据库
语法格式:
DROP DATABASE [IF EXISTS] 数据库;
5.1.3 使用数据库
语法格式:
USE 数据库;
5.1.4 查看数据库
语法格式:
SHOW DATABASES
5.1.5 面试题
数据库可以修改名字吗?如果要修改名字要怎么操作
- 数据库不可以修改名称 => 新建一个新的数据库 将老数据的内容拷贝到新数据库中,在删除老数据库
5.2 操作表
5.2.1 创建表
语法格式:
CREATE TABLE [IF NOT EXISTS] 表名(
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释],
......
'字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]
注意: 最后的末尾一行不用加","
实例:
CREATE TABLE employee(
eid INT UNSIGNED,-- 补充: UNSIGNED(无符号 没有负数)
ename VARCHAR(20), -- VARCHAR(长度)
address VARCHAR(255),
gender CHAR(1), -- CHAR(长度)
height DOUBLE(3,2), -- DOUBLE(M,N) M最长保存的位数 N表示小数位后面的位数
id_card CHAR(18), -- 数据库中列名的命名规则【蛇形命名】
phone CHAR(11),
email VARCHAR(50),
birthday DATE -- DATA报表示月日 Time 时分秒 DATETIME 年月日时分表秒
);
数据库的字段属性:
- UnSigned
- 无符号的
- 声明了该列不能为负数
- ZEROFILL
- 0填充的
- 不足位数的用0来填充 , 如int(3),5则为005
- Auto_InCrement
- 自增，从1开始，以1递增
- NULL 和 NOT NULL
- 默认为NULL , 即没有插入该列的数值
- 如果设置为NOT NULL , 则该列必须有值
- DEFAULT
- 默认的
- 用于设置默认值
- 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
扩展:每一个表，都必须存在以下五个字段：
| 名称 | 描述 |
|---|---|
| id | 主键 |
| version | 乐观锁 |
| is_delete | 伪删除 |
| create_time | 创建时间 |
| create_ | 修改时间 |
这里我们演示还用不到
5.2.2 删除表
语法格式
DROP TABLE 表名;
删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
5.2.3 修改表
5.2.3.1 修改表名
语法格式
ALTER TABLE 表名 RENAME 新的表名：
ALTER TABLE employee RENAME TO emp;
ALTER TABLE emp RENAME TO employee;
5.2.3.2 添加一列
语法格式
ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE employee ADD salary INT;
5.2.3.3 修改数据类型
语法格式
ALTER TABLE 表名 MODIFY 表名 新的数据类型;
ALTER TABLE employee MODIFY salary BIGINT;
5.2.3.4 修改列名和数据类型
语法格式
ALTER TABLE 表名 CHANGE 列名 新列名 新的数据类型;
ALTER TABLE employee CHANGE phone phone_number char(11);
5.2.3.5 删除列
语法格式
ALTER TABLE 表名 DROP 列名;
ALTER TABLE employee DROP salary;
这些知道一下就可以了,可以用图形化界面进行操作
6. DML(数据操作语言) - 对表中的数据进行增删改
6.1 添加数据
创建student表
CREATE TABLE student
(
sid INT,
sname VARCHAR(10),
birthday DATE,
score DOUBLE(5, 2)
);
6.1.1 给指定列添加数据
语法格式
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
INSERT INTO student(sid, sname, birthday) VALUES (1, '张三', '1999-10-08');
6.1.2 给全部列添加数据
语法格式
INSERT INTO 表名 VALUES(值1,值2.....);
INSERT INTO student VALUES (2, '李四', '2000-02-18', 99.99);
6.1.2 批量添加数据
语法格式:
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),
(值1,值2,…),
(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),
(值1,值2,…),
(值1,值2,…)…;
INSERT INTO student VALUES (3, '王五', '2001-07-18', 98.5),
(4, '赵四', '2001-03-18', 98.2);
6.2 修改数据
语法格式:
UPDATE 表名 SET 列名1=值1,列名2=值2,...[WHERE 条件];
UPDATE student SET sname='王二麻' WHERE sid = 1;
注意：修改语句中如果不加条件，则将所有数据都修改！
6.3 删除数据
语法格式
DELETE FROM 表名 [WHERE 条件]
DELETE FROM student WHERE sname = '王小花';
注意：删除语句中如果不加条件，则将所有数据都删除！
7. DQL(数据查询语言)-进行数据的查询
7.1 基础查询
准备一个学生表
CREATE TABLE student
(
sid INT,
sname VARCHAR(20),
gender CHAR(1),
address VARCHAR(20),
score DOUBLE(4, 1),
classroom VARCHAR(20),
birthday DATE
);
INSERT INTO student
VALUES (1, '张二狗', '男', '北京市昌平区', 100, 'Java','1999-11-11');
INSERT INTO student
VALUES (2, '李蛋', '女', '北京市朝阳区', 98, 'Python','1999-11-11');
INSERT INTO student
VALUES (3, '刘铁柱', '男', '北京市朝阳区', 99.5, 'Java','1999-11-11');
INSERT INTO student
VALUES (4, '赵民', '女', '北京市朝阳区', 87, 'Python','1999-11-11');
INSERT INTO student
VALUES (5, '王小花', '女', '北京市海淀区', 86.5, 'Java','1999-11-11');
INSERT INTO student
VALUES (6, '李云', '男', '北京市海淀区', 75.5, 'Java','1999-11-11');
INSERT INTO student
VALUES (7, '蔡徐坤', '女', '北京市门头沟区', 55, 'Python','1999-11-11');
INSERT INTO student
VALUES (8, '李易峰', '女', '北京市通州区', 45.5, 'Python','1999-11-11');
INSERT INTO student
VALUES (9, '吴亦凡', '女', '北京市通州区', 59.5, 'Java','1999-11-11');
INSERT INTO student
VALUES (10, '吴京', '男', '北京市通州区', 61, 'Java','1999-11-11');
INSERT INTO student
VALUES (11, '魏俊杰', '女', '北京市丰台区', 75, 'Java','1999-11-11');
INSERT INTO student
VALUES (12, '王志伟', '男', '北京市丰台区', 74, 'Python','1999-11-11');
INSERT INTO student
VALUES (13, '王致和', '女', '北京市丰台区', 99, 'Java','1999-11-11');
7.1.1 简单查询
语法格式
SELECT 字段名1,字段名2... FROM 表名; -- 查询指定的字段
SELECT * FROM 表名; -- 查询全部的字段
代码示例:
-- 查询全部的字段
SELECT * FROM student;
-- 查询指定的字段
SELECT sname,gender,address FROM student;
tips:
字段可以使用数学表达式
如：查询所有学生信息并且将所有的分数-5分
SELECT sid,sname,gender,address,score-5,classroom,birthday FROM student;
7.1.2 去重查询
语法格式
SELECT DISTINCT 字段名 FROM 表名;
示例代码
-- 去重查询
SELECT DISTINCT address FROM student;
7.1.3 起别名
在我们查询出来的第一行列的名称为在创建表的时候指定的列名,我们可以使用AS 对列起别名
语法格式
AS: AS 也可以省略
示例代码
SELECT sname AS 学生姓名 FROM student;
SELECT sname 学生姓名 FROM student;
7.2 条件查询
7.2.1 条件查询的语法
语法格式
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件
| 符号 | 功能 |
|---|---|
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
<>或!= | 不等于 |
= | 等于 |
BETWEEN...ADN... | 在某个范围之间都包括 |
IN(...) | 多选一 |
LIKE 占位符 | 模糊查询 |
IS NULL | 为空 |
IS NOT NULL | 不为空 |
AND 或 && | 并且 |
| `OR 或 | |
NOT 或 ! | 非,不是 |
7.2.1 条件查询->
示例:查询学生成绩大于60的学生所有信息
SELECT * FROM student WHERE score>60;
7.2.2 条件查询- <
示例:查询学生成绩小于60的学生所有信息
SELECT * FROM student WHERE score<60;
7.2.3 条件查询- >=
示例:查询学生的编号大于等于10的学生信息
SELECT * FROM student WHERE sid>=10;
7.2.4 条件查询- <=
示例:查询学生的编号小于等于5的学生信息
SELECT * FROM student WHERE sid<=5;
7.2.5 条件查询- =
示例:查询地址为北京市昌平区的学生信息
SELECT * FROM student WHERE address = '北京市昌平区'; -- MySQL中 字符串用 ''
7.2.6 条件查询- <>/!=
示例:查询班级名称不为Java的学生信息
SELECT * FROM student WHERE classroom <> 'Java';
SELECT * FROM student WHERE classroom != 'Java';
7.2.7 条件查询- =
示例:查询学生编号为7的学生信息
SELECT * FROM student WHERE sid = 7;
7.2.8 条件查询-BETWEEN...ADN...
示例:查询学生成绩在80-90之间的学生
SELECT * FROM student WHERE score BETWEEN 80 AND 90;
示例：查询出生日期是1999-02-01到1999-12-01的学生信息
SELECT * FROM student WHERE birthday BETWEEN '1999-02-01' AND '1999-12-01';
7.2.9 条件查询- IN()
示例:查询学生是 北京市昌平区、海淀区、丰台区的学生信息
SELECT * FROM student WHERE address IN ('北京市昌平区','北京市海淀区','北京市丰台区');
7.2.10 条件查询- 模糊查询
_表示单个任意字符 %表示任意多个字符
示例1：查询名字中带有京的学生信息
SELECT * FROM student WHERE sname LIKE '%京%'
示例2：查询姓张的学生信息
SELECT * FROM student WHERE sname LIKE '张%';
示例3：查询姓李的学生信息 并且是两个字的
SELECT * FROM student WHERE sname LIKE '李_';
示例4：查询名字中以狗结尾的学生信息
SELECT * FROM student WHERE sname LIKE '%狗';
示例5：查询名字当中第二个字为志并且是三个字的学生信息
SELECT * FROM student WHERE sname LIKE '_志_';
7.2.11 条件查询-IS NULL
示例：查询sid为NULL的学生信息
SELECT * FROM student WHERE sid IS NULL; -- 这里没有sid为NULL的学生所以没有查询出来任何数据
7.2.12 条件查询- IS NOT NULL
示例：性别不为NULL的学生信息
SELECT * FROM student WHERE gender IS NOT NULL; -- 这里的学生都有性别 所以会全部查询出来
7.2.13 条件查询 -AND/&&
示例：查询北京市朝阳区并且性别为女的学生信息
SELECT * FROM student WHERE address = '北京市朝阳区' AND gender = '女';
SELECT * FROM student WHERE address = '北京市朝阳区' && gender = '女';
tips: AND可以替换BETWEEN…ADN
就比如上面BETWEEN…ADN 的案例当中 用 AND来写
SELECT * FROM student WHERE birthday>'1999-02-01' AND birthday<'1999-12-01';
7.2.14 条件查询- OR/||
示例：查询名字中带有志或者是狗的学生信息
SELECT * FROM student WHERE sname LIKE '%志%' OR sname LIKE '%狗%';
SELECT * FROM student WHERE sname LIKE '%志%' || sname LIKE '%狗%';
tips: OR可以替换IN()
就比如上面IN() 的案例当中 用 OR来写
SELECT * FROM student WHERE address ='北京市昌平区' OR address ='北京市海淀区' OR address='北京市丰台区';
7.2.15 添加查询-NOT取反
示例：查询所有不住在北京市昌平区和北京市海淀区的学生信息
SELECT * FROM student WHERE address NOT IN('北京市昌平区','北京市海淀区');
7.3 排序查询
语法格式
SELECT 字段名 [WHERE 条件] [ORDER BY desc / asc] -- desc降序 asc升序 默认为升序
7.3.1 单个字段排序
示例1：查询所有学生并且根据成绩升序
SELECT * FROM student ORDER BY score;
示例2：查询所有男同学并且根据成绩的降序排列
SELECT * FROM student WHERE gender='男' ORDER BY score DESC ;
示例3：查询班级是Java班级并且为男的学生信息根据成绩升序排列
SELECT * FROM student WHERE classroom ='Java' AND gender='男' ORDER BY score;
7.3.2 多个字段排序
可以两个字段排序吗？或者说按照多个字段排序？
示例：查询性别的为男的学生信息并且按照成绩的升序如果成绩相同按照名字的降序排列
SELECT *
FROM student
WHERE gender = '男'
ORDER BY score ASC, sname DESC; -- score 在前起主导,只有score相同的时候 才会考虑 sname排序
7.3.3 执行顺序
select
... 3
from
... 1
where
... 2
order by
... 4
- 第一步：from
- 第二步：where
- 第三步：select
- 第四步：order by(排序总在最后执行！！！)
7.4 数据处理函数
7.4.1 单行处理函数
概念
数据处理函数又被称为单行处理函数
单行处理函数的特点：一个输入对应一个输出。
和单行处理函数相对的是：多行处理函数.(多行处理函数特点：多个输入,对于一个输出)
准备前置
首先创建一个简易的薪资表
create table Pay(
id int,
name varchar(10),
salary decimal(10,3),
bonus decimal(10,3),
holiday int );
insert into Pay values (1001,'Bob',2345.435,800,5);
insert into Pay values (1002,'Tom',3454.534,500.435,2);
insert into Pay values (1003,'Mimi',5534.565,900,10);
insert into Pay values (1004,'Boss',10000,888.666,20);
insert into Pay values (1005,'Ggg',2454,300,null);
SELECT * FROM pay;
单行处理函处理有这些：
| 函数名 | 描述 |
|---|---|
| LOWER | 转换小写 |
| UPPER | 转换大写 |
| SUBSTR | 字符串长度 |
| LENGTH | 取长度 |
| TRIM | 去空格 |
| STR_TO_DATE | 将字符串转换为日期 |
| DATE_FROMAT | 格式化日期 |
| FORMAT | 设置千分位 |
| ROUND | 四舍五入 |
| RAND() | 生成随机数 |
| IFNULL() | 可以将NULL转换成一个具体值 |
| CONCAT | 函数进行字符串的拼接 |
下面我们说一下常用的单行处理函数
7.4.1.1 LOWER和UPPER函数
ower函数的用法就是将一串字符种的大写转换成小写，如果字符本身就是小写则不管。
语法为：select lower(字段) from 表名;
我要将薪资表的name中大写都改为小写：
SELECT LOWER(name) AS LOWERNAME FROM pay;
upper函数的用法就是将一串字符种的小写转换成大写，如果字符本身就是大写则不管。
语法为：select upper(字段) from 表名;
我要将薪资表的name中小写都改为大写：
SELECT UPPER(name) AS UPPERNAME FROM pay;
7.4.1.2 SUBSTR函数
substr是截取字符串从某一下标到另一下标的字段，起始下标为1终止下标根据你的需求来定。
格式为：select substr(字段,前下标,后下标) from 表名;
比如我要找到薪资表name的第一个字符，也就是员工的姓：
SELECT SUBSTR(name,1,1) FROM pay;
找姓名为B的人
SELECT name FROM pay WHERE substr(name,1,1) ='B'; -- 用SUBSTR的写法
SELECT * FROM pay WHERE name LIKE 'B%'; -- 模糊查询的写法
7.4.1.3 LENGTH函数
Length跟java的length一样都是求长度
语法为select length(字段) from 表名;
求薪资表中所有name的长度：
SELECT LENGTH(name) nameLength FROM pay;
7.4.1.4 CONCAT函数
concat函数的用法就是合并两个字符串
语法：select concat(字段1,字段2) from 表名;
将薪资表中的id和salary连接起来：
SELECT CONCAT(id,salary) FROM pay;
7.4.1.5 TRIM函数
trim是去掉一个字符串前后的括号，比如我不小心在一个字段前后打上了几个空格trim函数会自动去掉这些空格：
SELECT * FROM pay WHERE name = TRIM(' Boss ');
7.4.1.6 ROUND函数
round函数是用来做四舍五入操作的，当然你可以规定保留几位小数。
语法为：select 字段 round(字段,保留位数) from 表名;
比如我要使工资四舍五入取整,：
SELECT name,ROUND(salary) FROM pay;
-- SELECT name,ROUND(salary,1) FROM pay; 保留一位小数
-- SELECT name,ROUND(salary,2) FROM pay; 保留两位小数
7.4.1.7 IFNULL
ifnull可以将null转换成一个具体值
ifnull是空处理函数,专门处理空的
在所有数据库当中,只要有null参与数学运算,最终结果就是null
我们把Boss的bonus奖金改为NULL
UPDATE pay SET bonus = NULL WHERE name='Boss';
当计算奖金和薪水的总和的时候
SELECT name, (salary+bonus) FROM pay;
通过测试结果发现Boss的总和为null 这就是 在所有数据库当中,只要有null参与数学运算,最终结果就是null
所有使用 IFNULL函数进行处理
SELECT name ,salary+ifnull(bonus,0) FROM pay; -- ifnull(bonus,0) 当 bonus 为null的时候 当作0
注意：
NULL只要参与运算,最终结果一定是NULL,需要使用ifnull函数
ifnull函数用法:ifnull(数据,被当作那个值)
如果数据为NULL的时候,把这个数据当作那个值
7.4.2 分组函数
分组函数/聚合函数/多行处理函数
| 函数名 | 描述 |
|---|---|
| COUNT | 计数 |
| SUM | 求和 |
| AVG | 平均值 |
| MAX | 最大值 |
| MIN | 最小值 |
注意：
分组函数在使用时必须先分组再使用，如果是对整张表使用则默认这张表为一组。下面我们会讲分组查询
7.4.2.1 COUNT函数
count函数是用来计数的，统计某一个字段出现的次数，
语法：select count(字段) from 表名;
求薪资表中员工的个数：
mysql> SELECT COUNT(id) FROM pay;
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
7.4.2.2 SUM函数
sum函数是用来求和的，计算某一字段数据的总和，
语法:select sum(字段) from 表名;
求薪资表中所有员工工资总和：
mysql> SELECT SUM(salary) FROM pay;
+-------------+
| SUM(salary) |
+-------------+
| 23788.534 |
+-------------+
1 row in set (0.00 sec)
7.4.2.3 AVG函数
avg函数是求某一字段内数据的平均值，
语法:select avg(字段) from 表名;
求薪资表中员工平均工资：
mysql> SELECT AVG(salary) FROM pay;
+--------------+
| AVG(salary) |
+--------------+
| 4757.7068000 |
+--------------+
7.4.2.4 MAX函数
max函数是求某一字段内数据的最大值。
语法:select max(字段) from 表名;
求薪资表中工资最高为多少：
mysql> select max(salary) salmax from Pay;
+-----------+
| salmax |
+-----------+
| 10000.000 |
+-----------+
1 row in set (0.00 sec)
7.4.2.5 MIN函数
min函数是求某一字段内数据的最小值。
语法:select min(字段) from 表名;
求薪资表中工资最低为多少：
mysql> select min(salary) salmin from Pay;
+----------+
| salmin |
+----------+
| 2345.435 |
+----------+
1 row in set (0.00 sec)
7.4.3 分组函数注意点
第一点：分组函数自动忽略NULL,你不需要提前对NULL进行处理
我们可以看到Pay表中假期(holiday)字段有一空，那么我对它进行求和会不会造成结果为空呢？
mysql> select sum(holiday) test from Pay;
+------+
| test |
+------+
| 37 |
+------+
1 row in set (0.00 sec)
我们发现并没有结果为NULL，证实了上面那句话，分组函数运算时忽略NULL。
第二点：分组函数中count(*)和count(具体字段)有什么区别？
mysql> SELECT COUNT(*) FROM pay;
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(holiday) FROM pay;
+----------------+
| COUNT(holiday) |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
count(具体字段)：表示统计该字段下所有不为NULL的元素的总是
count(*)：统计表当中的总行数.(只要有一行数据 count则++) 因为每一行记录不可能都为NULL,一行数据中有一列不为NULL,则这行数据就是有效的.
第三点：分组函数不能够直接使用在where字句后面.
找出比最低工资高的员工信息.
select * from pay where salary>min(sal);
表面上没问题，运行一下？
ERROR 1111 (HY000): Invalid use of group function;
第四点：所有的分组函数可以组合起来一起用。
7.5 分组查询
7.5.1 概念
在实际的应用中,可能有这样的需求,需要先进行分组,然后对每一组数据进行操作
这个时候我们需要使用分组查询,怎么运行分组查询呢？
计算每个部门的工资和?
计算每个工作岗位的平均薪资？
计算每个工作岗位的最高薪资
7.5.2 基础语法
SELECT 分组函数,分组后的字段
FROM 表
[WHERE 筛选条件]
GROUP BY 分组的字段
[ORDER BY 排序列表]
以上关键字的顺序不能颠倒
- FROM
- WHERE
- GROUP BY
- SELECT
- ORDER BY
为什么分组函数不能直接在where后面？
在上面写到分组函数的注意点中
select* from pay where salary>min(sal); -- 报错
因为分组函数在使用的时候,必须先分组在使用WHERE执行的时候,还没有分组,所以WHERE后面不能出现分组函数
SELECT SUM(salary) FROM pay; -- 那为什么这条SQL语句可以呢？ 因为SELECT在GROUP BY之后执行。
7.5.3 案例
7.5.3.1 准备工作
创建员工EMP表
CREATE TABLE EMP(
EMPNO int(4) not null ,-- 员工编号
ENAME VARCHAR(10),-- 员工姓名
JOB VARCHAR(9),-- 工作
MGR INT(4),-- 领导编号
HIREDATE DATE DEFAULT NULL, -- 入职日期
SAL DOUBLE(7,2), -- 工资
COMM DOUBLE(7,2),-- 补贴
primary key (EMPNO),-- 主键 到后面的约束会讲到
DEPTNO INT(2) -- 部门编号
);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902, '1980-12-17'
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20'
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, 'WARD', 'SALESMAN', 7698, '1981-02-22'
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839, '1981-04-02'
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28'
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01'
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839, '1981-06-09'
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL, '1981-11-17'
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08'
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788, '1987-05-23'
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, 'JAMES', 'CLERK', 7698, '1981-12-03'
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566, '1981-12-03'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782, '1982-01-23'
, 1300, NULL, 10);
7.5.3.2 找出每个工作岗位的工资和
实现思路：按照工作岗位分组,然后对工资就和。
SELECT JOB, SUM(SAL) SALSUM
FROM emp
GROUP BY JOB;
以上这个语句的执行顺序？
先从emp表中查询数据.
根据job字段进行分组
然后对每一组的数据进行sum(sal)
那多SELECT后面添加一个不参与分组的字段可以吗?
SELECT ENAME,JOB, SUM(SAL) SALSUM
FROM emp
GROUP BY JOB;
以上语句在mysql中可以执行,但是毫无意义.以上语句在oracle中执行报错.
oracle的语法比mysql的语法严格(mysql的语法相对于来说松散一些！)
重要结论在一条SELECT语句当中,如果有GROUP BY语句的话,SELECY后面只能跟：参加分组的字段,以及分组函数.其他一律不能跟
7.5.3.3 找出每个部门的最高薪资
实现思路:按照部门编号分组,求每一组的最大值
SELECT DEPTNO, MAX(SAL) MAXSAL
FROM emp
GROUP BY DEPTNO
ORDER BY DEPTNO;
7.5.3.4 找出每个部门,不同工作岗位的最高薪资？
对两个字段同时进行分组–这就是联合分组
+--------+-----------+---------+ | deptno | job | sal | +--------+-----------+---------+ | 10 | MANAGER | 2450.00 | | 10 | PRESIDENT | 5000.00 | | 10 | CLERK | 1300.00 | | 20 | CLERK | 800.00 | | 20 | MANAGER | 2975.00 | | 20 | ANALYST | 3000.00 | | 20 | CLERK | 1100.00 | | 20 | ANALYST | 3000.00 | | 30 | SALESMAN | 1600.00 | | 30 | SALESMAN | 1250.00 | | 30 | SALESMAN | 1250.00 | | 30 | MANAGER | 2850.00 | | 30 | SALESMAN | 1500.00 | | 30 | CLERK | 950.00 | +--------+-----------+---------+ 也就是 10 MANAGER | 2450.00 | 10 PRESIDENT | 5000.00 | 10 CLERK | 1300.00 | 20 CLERK | 800.00 | 20 MANAGER | 2975.00 | 20 ANALYST | 3000.00 | 20 CLERK | 1100.00 | 20 ANALYST | 3000.00 | 30 SALESMAN | 1600.00 | 30 SALESMAN | 1250.00 | 30 SALESMAN | 1250.00 | 30 MANAGER | 2850.00 | 30 SALESMAN | 1500.00 | 30 CLERK | 950.00 |
SELECT DEPTNO, JOB, MAX(SAL)
FROM emp
GROUP BY DEPTNO, JOB -- 两个字段连成一个字段看
ORDER BY DEPTNO;
7.5.4 HAVING关键字
7.5.4.1 案例
使用HAVING可以对分完组之后的数据进一步过滤
案例:找出每个部门的最高薪资,要求显示最高薪资大于3000
SELECT DEPTNO,MAX(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MAX(SAL)>3000;
思考一个问题:以上的sql语句执行效率是不是低？
比较低,实际上可以这样考虑:先将大于3000的都找出来,然后在分组
SELECT DEPTNO,MAX(SAL)
FROM EMP
WHERE SAL>3000
GROUP BY EMPNO;
**优化测量：WHERE和HAVING,优先选择WHRER,WHERE实在完成不了了,在选择HAVING **
HAVING关键字不可以单独出现！！
7.5.4.2 where没办法的?
找出每个部门的平均薪资,要求显示平均薪资>2500的。
第一步：找出平均薪资
SELECT DEPTNO,AVG(SAL)
FROM EMP
GROUP BY EMPNO;
第二步：要求显示平均薪资高于2500的
SELECT DEPTNO,AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>2500;
7.6 大总结(单表的查询)
SELECT 字段名
FROM 表名
[WHERE 过滤条件]
[GROUP BY 分组字段]
[HAVING 分组后的过滤字段]
[ORDER BY 排序字段]
以上关键字只能按照这个顺序来,不能颠倒
执行顺序：
- FROM:从某张表中查询,
- WHERE: 先经过WHERE条件筛选出有价值的数据.
- GROUP BY: 对这些有价值的数据进行分组
- HAVING: 分组之后可以使用HAVING继续筛选
- SELECT :SELECT查询出来
- ORDER BY:最后排序输出！
7.7 连接查询
在上面EMP表的基础上在新建一张SALGRADE工资等级表和DEPT部门表
CREATE TABLE DEPT(
DEPTNO int(2) not null ,
DNAME VARCHAR(14) ,
LOC VARCHAR(13),
primary key (DEPTNO)
);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON');
CREATE TABLE SALGRADE(
GRADE INT,
LOSAL INT,
HISAL INT
);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
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 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)
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.00 sec)
7.7.1 连接查询的概念
从一张表中单独查询,成为单表查询
emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字这种跨表查询,多张表联合起来查询数据,被称为连接查询。
7.7.2 连接查询的分类
根据语法的分类:
- SQL92:1992年的时候出现的语法
- SQL99:1999年的时候出现的语法
重点学习SQL99(这个过程中简单演示一下SQL92的例子)
根据表的连接的方式分类：
- 内连接：
- 等值连接
- 非等值连接
- 自连接
- 外连接：
- 左外连接(左连接)
- 右外连接(右连接)
- 全连接(不演示)
7.7.3 笛卡儿积现象
当去查询每个员工的所在部门的时候,没有加上条件限制
查询员工:SELECT * FROM EMP;===>会有14条数据
查询部门:SELECT * FROM DEPT===>会有4条数据
两张表连接没有任何条件限制:
mysql> SELECT ENAME,DNAME FROM EMP,DEPT;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | OPERATIONS |
| SMITH | SALES |
| SMITH | RESEARCH |
| SMITH | ACCOUNTING |
| ALLEN | OPERATIONS |
| ALLEN | SALES |
| ALLEN | RESEARCH |
| ALLEN | ACCOUNTING |
| WARD | OPERATIONS |
| WARD | SALES |
| WARD | RESEARCH |
| WARD | ACCOUNTING |
| JONES | OPERATIONS |
| JONES | SALES |
| JONES | RESEARCH |
| JONES | ACCOUNTING |
| MARTIN | OPERATIONS |
..........
56 rows in set (0.00 sec) -- 可以看出来是 14*4=56 数据
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表条数的乘积
这种现象被成为:笛卡儿积现象.(笛卡儿积发现的,这是一个数学现象)
7.7.4 怎么避免笛卡儿积
连接时加条件,满足这个条件的记录被筛选出来！
mysql> SELECT ename,dname FROM emp,dept WHERE emp.deptno=dept.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
表起别名，很重要,效率问题:相比于上一个效率高
mysql> SELECT e.ENAME, d.DNAME FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
思考：最终查询的结果条数时14条,但匹配的过程中,匹配的次数减少了吗？
- 还是56次,只不过进行了四选一.次数没有减少。
注意:通过笛卡儿积现象得出,表的连接次数越多效率越低,尽量避免表的连接次数
7.7.5 内连接之等值连接.
7.7.5.1 语法
SELECT 字段列表 FROM 表1，表2.. WHERE 条件; -- 隐式内连接/SQL92语法
SELECT 字段列表 FROM 表1 [inner] JOIN 表2 ON 表的连接条件;
**SQL92的缺点：**结构不清晰,表的连接条件,和后期进一步筛选的条件,都放到了WERE后面。
**SQL99优点：**表连接的条件是独立的,连接之后还需要进一步筛选,往后继续加WHERE
7.7.5.2 案例：查询每个员工所在部门名称
-- 查询员工所在部门名称 显示员工名和部门名
-- 采用SQL99写法
-- SQL92写法 SELECT e.ENAME,d.DNAME FROM emp e ,dept d WHERE e.deptno=d.deptno;
-- INNER可以不带 带了可读性比较好
mysql> SELECT e.ENAME,d.DNAME FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
7.7.6 内连接之非等值连接
语法都是一样的 只不过条件不是等量关系
7.7.6.1 案例：找出每个员工的薪资等级
mysql> SELECT e.ENAME, s.GRADE
-> FROM emp e
-> INNER JOIN salgrade s ON e.SAL BETWEEN s.LOSAL AND s.HISAL -- 条件不是一个等量关系
-> ORDER BY s.GRADE;
+--------+-------+
| ENAME | GRADE |
+--------+-------+
| SMITH | 1 |
| ADAMS | 1 |
| JAMES | 1 |
| WARD | 2 |
| MARTIN | 2 |
| MILLER | 2 |
| ALLEN | 3 |
| TURNER | 3 |
| JONES | 4 |
| BLAKE | 4 |
| CLARK | 4 |
| SCOTT | 4 |
| FORD | 4 |
| KING | 5 |
+--------+-------+
14 rows in set (0.00 sec)
7.7.7 内连接之自连接
语法都是一样的,自连接就是自己连接自己
7.7.7.1 案例:查询员工的上级领导,
先查询一下员工表
mysql> SELECT EMPNO,ENAME,MGR FROM emp;
+-------+--------+------+
| EMPNO | ENAME | MGR |
+-------+--------+------+
| 7369 | SMITH | 7902 | -- 就按 SMITH来说 员工编号为 7369 领导编号为 7902 那么他的领导为 FORD
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL | -- KING没有上级等到 代表他就是老板
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
14 rows in set (0.00 sec)
内连接的技巧,一张表看成两张表
这里就是员工表a表的mgr和领导表b表的empno进行配对
代码示例
mysql> SELECT a.ENAME AS '员工名', b.ENAME AS '领导名'
-> FROM emp a
-> INNER 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.02 sec) -- 13条记录，没有KING<内连接>
以上就是内连接中的:自连接,技巧为一张表看作两张表
7.7.8 外连接
在说外连接之前,先说一下内连接的一些特点:
内连接(A和B连接,AB两张表没有主次关系.平等的)
完成能够匹配上这个条件的数据查询出来
7.7.8.1 语法
-- 左外连接
-- LEFT代表什么：表示将JOIN关键字左边的这张表看成主表,主要是为了将这样表的数据全部查询出来,捎带着关联查询右边的表在外连接当中,两张表连接,产生了主次关系
-- 左外连接：相当于查询A表所有数据和交集部分数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
-- 右外连接
-- RIGHT代表什么：表示将JOIN关键字左边的这张表看成主表,主要是为了将这样表的数据全部查询出来,捎带着关联查询左边的表在外连接当中,两张表连接,产生了主次关系
-- 右外连接：相当于查询B表所有数据和交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
7.7.8.2 案例：查询每个员工的上级领导
在内连接之自连接的案例当中, 查询出来的数据条数是13条,因为KING没有老板所以没有显示出来
-- 如果把emp b 当成了主表的话 由于b表是一个领导表,那么表明 每一个员工都会下属,
-- 那么 这个时候最底层的员工也就是没有下属的员工 就会出现 员工表有空的情况
mysql> SELECT a.ENAME AS '员工名', b.ENAME AS '领导名'
-> FROM emp a -- 这里把员工表当成了主表 所有要把员工所有都搜索出来
-> LEFT OUTER JOIN emp b
-> ON a.MGR = b.EMPNO;
+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL | -- 把a表当做是主表 所以查询出来所有信息 KING是老板 所有 领导为NULL
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
14 rows in set (0.01 sec)
7.7.8.3 三张表，四张表怎么连接
SELECT 字段列表
FROM 表1
INNER JOIN 表2
ON 表1和表2的连接条件
INNER JOIN 表3
ON 表1和表3的连接条件
RIGHT OUTER 表4
ON 表1和表4的连接条件;
一条SQL中内连接和外连接可以混合。都可以出现！
7.7.8.4 案例：找出每个员工的部门名称以及工资等级
mysql> SELECT e.ENAME,d.DNAME,s.GRADE
-> FROM emp e
-> INNER JOIN dept d
-> on e.DEPTNO = d.DEPTNO
-> INNER JOIN salgrade s
-> ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
+--------+------------+-------+
| ENAME | DNAME | GRADE |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
14 rows in set (0.00 sec)
7.7.8.5 找出每个员工的部门名称以及工资等级，还有上级领导
mysql> SELECT e.ENAME 员工姓名, d.DNAME 部门名称 ,s.GRADE 工资等级,e2.ENAME 领导名称
-> FROM emp e
-> INNER JOIN dept d
-> on e.DEPTNO = d.DEPTNO
-> INNER JOIN salgrade s
-> ON e.SAL BETWEEN s.LOSAL AND s.HISAL
-> LEFT OUTER JOIN emp e2 ON e.MGR=e2.EMPNO;
+----------+------------+----------+----------+
| 员工姓名 | 部门名称 | 工资等级 | 领导名称 |
+----------+------------+----------+----------+
| SMITH | RESEARCH | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCH | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
+----------+------------+----------+----------+
14 rows in set (0.00 sec)
7.8 子查询
7.8.1 什么是子查询
SELECT语句中嵌套SELECT语句，被嵌套的SELECT语句称为子查询。
7.8.2 子查询可以出现在哪里呢？
SELECT (SELECT)
FROM (SELECT)
WHERE (SELECT)
7.8.3 WHERE子句中的子查询
7.8.3.1 案例：找出比最低工资高的员工姓名和工资？
技巧：把WHERE子句中的子查询当做一个数值即可
-- WHERE子句中不能直接使用分组函数。
mysql> SELECT * FROM emp WHERE sal > MIN(SAL);
ERROR 1111 (HY000): Invalid use of group function
实现思路：
-
第一步：查询最低工资是多少
mysql> SELECT MIN(SAL) FROM emp; +----------+ | MIN(SAL) | +----------+ | 800.00 | +----------+ 1 row in set (0.00 sec) -
找出>800的
mysql> SELECT ENAME,SAL FROM emp WHERE SAL>800; +--------+---------+ | 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 | +--------+---------+ 13 rows in set (0.00 sec) -
第三步合并
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 | +--------+---------+ 13 rows in set (0.00 sec)
7.8.4 FROM子句中的子查询
注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）
7.8.4.1 案例：找出每个岗位的平均工资的薪资等级。
实现思路:
-
第一步：找出每个岗位的平均工资（按照岗位分组求平均值）
mysql> SELECT JOB,AVG(SAL) FROM emp GROUP BY JOB; +-----------+-------------+ | JOB | AVG(SAL) | +-----------+-------------+ | CLERK | 1037.500000 | | SALESMAN | 1400.000000 | | MANAGER | 2758.333333 | | ANALYST | 3000.000000 | | PRESIDENT | 5000.000000 | +-----------+-------------+ -- t表 5 rows in set (0.00 sec) -- 克服心理障碍，把以上的查询结果就当做一张真实存在的表t。 -
第二步: 查询工资等级表
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) -
第三步：进行合并
mysql> SELECT t.*, s.GRADE -> FROM (SELECT JOB, AVG(SAL) avgsal FROM emp GROUP BY JOB) t -> JOIN salgrade s -> ON t.avgsal BETWEEN S.LOSAL AND S.HISAL; +-----------+-------------+-------+ | JOB | avgsal | GRADE | +-----------+-------------+-------+ | CLERK | 1037.500000 | 1 | | SALESMAN | 1400.000000 | 2 | | MANAGER | 2758.333333 | 4 | | ANALYST | 3000.000000 | 4 | | PRESIDENT | 5000.000000 | 5 | +-----------+-------------+-------+ 5 rows in set (0.00 sec)注意:avg(sal) 要起别名,否则t.avg(sal) 中avg是一个关键字,查询报错.
7.8.5 SELECT后面出现的子查询(了解即可)
7.8.5.1 案例：找出每个员工的部门名称，要求显示员工名，部门名？
mysql> SELECT e.ENAME, e.DEPTNO, (SELECT d.DNAME FROM dept d WHERE d.DEPTNO = e.DEPTNO) AS DEPTNAME
-> FROM emp e;
+--------+--------+------------+
| ENAME | DEPTNO | DEPTNAME |
+--------+--------+------------+
| 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 |
+--------+--------+------------+
14 rows in set (0.00 sec)
注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果，多于1条，就报错了.换句话说
7.9 UNION合并查询结果集
案例:查询工作岗位是MANAGER和SALESMAN的员工？
SELECT * FROM emp WHERE JOB = 'MANAGER' OR JOB= 'SALESMAN';
SELECT * FROM emp WHERE JOB IN ('MANAGER','SALESMAN');
UNION 的写法
mysql> SELECT * FROM emp WHERE JOB = 'MANAGER'
-> UNION
-> SELECT * FROM emp WHERE JOB ='SALESMAN';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
效率问题
UNION的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。。。
但是UNION可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。
a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是：1000
a 连接 b一个结果：10 * 10 --> 100次
a 连接 c一个结果：10 * 10 --> 100次
使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）
注意点
-
UNION在进行结果集合并的时候，要求两个结果集的列数相同。
-- 错误演示 select ename,job from emp where job = 'MANAGER' union select ename from emp where job = 'SALESMAN'; -
结果集合并时列和列的数据类型也要一致。
-- 错误演示 select ename,job from emp where job = 'MANAGER' union select ename,sal from emp where job = 'SALESMAN';
7.10 LIMIT
7.10.1 LIMIT的作用
将查询结果集的一部分取出来。通常使用在分页查询当中。
分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差。
可以一页一页翻页看。
7.10.2 LIMIT怎么用呢？
完整用法:
LIMIT startIndex , length;
-- startIndex是起始下标，length是长度。 起始下标从0开始。
省略用法
LIMIT 5; -- 这是取前5.
案例：按照薪资降序，取出排名在前5名的员工？
mysql> SELECT * FROM emp ORDER BY SAL DESC LIMIT 0,5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
+-------+-------+-----------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)
注意：mysql当中LIMIT在ORDER BY之后执行！！！！！！
7.10.3 案例
取出工资排名在[3-5]名的员工？起始下标为0
-- 2表示起始位置从下标2开始，就是第三条记录开始
-- 3表示长度。
mysql> SELECT ename,sal FROM emp ORDER BY SAL DESC LIMIT 2,3;-- 下标从0开始
+-------+---------+
| ename | sal |
+-------+---------+
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)
取出工资排名在[5-9]名的员工？
mysql> SELECT ename,sal FROM emp ORDER BY SAL DESC LIMIT 4,5;
+--------+---------+
| ename | sal |
+--------+---------+
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
5 rows in set (0.00 sec)
7.10.4 分页
分页公式：
每页显示pageSize条记录
第pageNo页：LIMIT (pageNo - 1) * pageSize , pageSize
起始索引 = (当前页码-1)*每页显示的条数
LIMIT 起始索引, 每页显示的条数
每页显示3条记录
第1页：limit 0,3 [0 1 2]LIMIT 1-1*3,3
第2页：limit 3,3 [3 4 5]LIMIT 2-1*3,3
第3页：limit 6,3 [6 7 8]LIMIT 3-1*3,3
第4页：limit 9,3 [9 10 11]public static void main(String[] args){ // 用户提交过来一个页码，以及每页显示的记录条数 int pageNo = 5; //第5页 int pageSize = 10; //每页显示10条 int startIndex = (pageNo - 1) * pageSize; String sql = "select ...limit " + startIndex + ", " + pageSize; }
7.11 关于DQL语句的大总结
SELECT 字段列表
FROM 表1
[INNER JOIN 表2 ON 表1和表2的连接条件]
[LEFT OUTER JOIN 表3 表1和表3的连接条件]
WHERE 过滤条件
GROUP BY 分组字段
HAVING 分组后的过滤条件
ORDER BY 排序字段
LIMIT 显示条数
执行顺序：
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- LIMIT
8. 表的约束
8.1 什么是约束
约束对应的英语单词：constraint
在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的
完整性、有效性！！！
约束的作用就是为了保证：表中的数据有效！！
8.2 约束有那些呢？
- 非空约束：NOT NULL 非空约束not null约束的字段不能为NULL。
- 唯一约束：UNIQUE
- 主键约束：PRIMARY KEY(简称PK)
- 外键约束：FOREIGN KEY(简称FK)
- 默认约束：DEFAULT
- CHECK：检查约束
重点学习:非空/唯一/主键/外键/默认约束
8.3 非空约束
语法
-- 创建表时添加非空约束
CREATE TABLE 表名(
列名 数据类型 NOT NULL,
…
);
创建表
CREATE TABLE a(
a INT,
b varchar(10) NOT NULL -- 创建表的时候如果想要某列不为NULL 添加 NOT NULL 非空约束
);
插入数据
INSERT INTO a VALUES (1,'张二狗');
-- (1) 当没有给非空约束的列赋值【不生效】
-- ERROR 1364 (HY000): Field 'b' doesn't have a default value
INSERT INTO a(a) VALUES (2);
-- (2) 当给非空约束的列赋值 赋值为NULL【生效】
INSERT INTO a VALUES (3,NULL);
-- 删除非空约束【基于修改列的数据类型的时候 不声明NOT NULL】
ALTER TABLE a MODIFY a VARCHAR(10);
总结: 实际开发中大部分表中的列都要非空【保证数据的完整性】
8.4 唯一约束
8.4.1 基础语法
-- 创建表时添加唯一约束
CREATE TABLE 表名(
列名 数据类型 UNIQUE [AUTO_INCREMENT],
-- AUTO_INCREMENT: 当不指定值时自动增长
…
);
创建表
DROP TABLE IF EXISTS t_vip;
CREATE TABLE t_vip(
id int,
name varchar(255) unique, -- 创建表的时候 如果想要保证某一列的数据唯一 添加唯一约束 unique
email varchar(255)
);
插入数据
INSERT INTO t_vip(id,name,email) VALUES(1,'zhangsan','zhangsan@123.com');
INSERT INTO t_vip(id,name,email) VALUES(2,'lisi','lisi@123.com');
INSERT INTO t_vip(id,name,email) VALUES(3,'wangwu','wangwu@123.com');
-- ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'
INSERT INTO t_vip(id,name,email) VALUES(4,'wangwu','wangwu@sina.com');
当添加了唯一约束以后【列:唯一索引】基于这一列进行查询的时候，有唯一索引的查询速度会更快
name字段虽然被unique约束了，但是可以为NULL。
8.4.2 联合唯一约束
怎么将name和email两个字段联合起来具有唯一性呢？
错误写法
DROP TABLE IF EXISTS t_vip; t_vip;
-- 这张表这样创建是不符合我以上“新需求”的。
-- 这样创建表示：name具有唯一性，email具有唯一性。各自唯一。
CREATE TABLE t_vip(
id int,
name varchar(255) unique, -- 约束直接添加到列后面的，叫做列级约束。
email varchar(255) unique
);
-- 但如果采用以上方式创建表的话，肯定创建失败，因为'zhangsan'和'zhangsan'重复了。
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
正确写法
DROP TABLE IF EXISTS t_vip;
CREATE TABLE t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) -- 约束没有添加在列的后面，这种约束被称为表级约束。
);
-- 下面这样就会插入成功'zhangsan','zhangsan@123.com' 和 'zhangsan','zhangsan@sina.com' 没有重复
-- name和email两个字段联合起来唯一！！！
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
-- 下面这个就会报错
-- ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'
insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
什么时候使用表级约束呢？
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
8.4.3 联合 UNIQUE 和NOT NULL
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null unique
);
-- 查询结构
mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | NO | PRI | NULL | |
+-------+--------------+------+-----+---------+-------+
在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'zhangsan'); -- 错误了：name不能重复
insert into t_vip(id) values(2); -- 错误了：name不能为NULL。
8.5 主键约束
8.5.1 什么是主键约束
主键值是每一行记录的唯一标识。
主键值是每一行记录的身份证号！！！
主键约束的相关术语？
- 主键约束：就是一种约束。
- 主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
- 主键值：主键字段中的每一个值都叫做：主键值。
记住：任何一张表都应该有主键，没有主键，表无效！！
主键的特征：NOT NULL + UNQUIE（主键值不能是NULL，同时也不能重复！）
8.5.2 给一张表添加主键约束
基础语法
-- 创建表时添加主键约束
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
…
);
案例
drop table if exists t_vip;
-- 1个字段做主键，叫做：单一主键
create table t_vip(
id int primary key, -- 列级约束
name varchar(255)
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
-- 错误：不能重复
insert into t_vip(id,name) values(2,'wangwu');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
-- 错误：不能为NULL
insert into t_vip(name) values('zhaoliu');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
8.5.3 联合主键
drop table if exists t_vip;
-- id和name联合起来做主键：复合主键！！！！
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
//错误：不能重复
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'
在实际开发中不建议使用：复合主键。建议使用单一主键！
因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。
复合主键比较复杂，不建议使用！！！
8.5.4 可以有两个主键吗？
drop table if exists t_vip;
create table t_vip(
id int primary key,
name varchar(255) primary key
);
ERROR 1068 (42000): Multiple primary key defined
-- 结论：一张表，主键约束只能添加1个。（主键只能有1个。）
主键值建议使用：
int
bigint
char
等类型。不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！
8.5.5 AUTO_INCREMENT
在mysql当中，有一种机制，可以帮助我们自动维护一个主键值？
drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment, -- auto_increment表示自增，从1开始，以1递增！
name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
| 3 | zhangsan |
| 4 | zhangsan |
| 5 | zhangsan |
| 6 | zhangsan |
| 7 | zhangsan |
| 8 | zhangsan |
+----+----------+
8.6 默认约束
保存数据时，未指定值则采用默认值
基础语法
-- 创建表时添加默认约束
CREATE TABLE 表名(
列名 数据类型 DEFAULT 默认值,
…
);
案例
-- 默认约束:当作用于某一列的时候,保存数据的时候没有给这一列声明值 则使用默认值
-- 创建e表 e/f(默认值)
CREATE TABLE e(
e INT PRIMARY KEY AUTO_INCREMENT,
f VARCHAR(10) DEFAULT '佚名'
);
-- 注意：赋NULL值也算是值【不会出发默认约束】
INSERT INTO e VALUES (null,null);
-- 没有给这一列赋值才会触发默认约束
INSERT INTO e VALUES (null);
8.7 外键约束
8.7.1 外键约束的概念
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性
外键约束涉及到的相关术语：
外键约束：一种约束（foreign key）
外键字段：该字段上添加了外键约束
外键值：外键字段当中的每一个值。
8.7.2 添加外键约束
-- 创建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
8.7.3 外键理解
业务背景：请设计数据库表，来描述"班级和学生"的信息？
8.7.3.1 方案一：班级和学生存储在一张表中
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班
----------------------------------------------------------------------------------------
分析以上方案的缺点：
- 数据冗余，空间浪费！！！！班级名称 编号重复
- 这个设计是比较失败的！
8.7.3.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字段中的每一个值都是外键值。
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);
8.7.4 外键约束的注意点
在上述案例当中 t_class是父表 t_student是子表 也就是 有外键字段是子表,被引入字段作为外键的就是父表
删除表的顺序:先删子，再删父。
创建表的顺序:先创建父，再创建子。
删除数据的顺序:先删子，再删父。
插入数据的顺序:先插入父，再插入子。
子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
不一定是主键，但至少具有unique约束。
9.存储引擎
9.1 概述
存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）
存储引擎这个名字高端大气上档次。实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。
9.2 给表添加/指定"存储引擎"
使用命令show create table t_student;
mysql> show create table t_student;
CREATE TABLE `t_student` (
`no` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
在建表的时候可以在最后使用：ENGINE来指定存储引擎。CHARSET来指定这张表的字符编码方式。
mysql默认的存储引擎是：InnoDB
mysql默认的字符编码方式是：utf8
建表时指定存储引擎，以及字符编码方式。
create table t_product(
id int primary key,
name varchar(255)
)engine=InnoDB default charset=gbk;
9.3查看mysql支持哪些存储引擎
命令: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
mysql支持九大存储引擎，当前5.5.36支持8个。版本不同支持情况不同。
9.4 关于mysql常用的存储引擎介绍一下
9.4.1 MyISAM存储引擎
它管理的表具有以下特征：
- 使用三个文件表示每个表：
- 格式文件 — 存储表结构的定义（mytable.frm）
- 数据文件 — 存储表行的内容（mytable.MYD）
- 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。
- 可被转换为压缩、只读表来节省空间
提示一下：
对于一张表来说，只要是主键，
或者加有unique约束的字段上会自动创建索引。
MyISAM存储引擎特点：
- 可被转换为压缩、只读表来节省空间 这是这种存储引擎的优势！！！！
- MyISAM不支持事务机制，安全性低。
9.4.2 InnoDB存储引擎
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。
它管理的表具有下列主要特征：
- 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
- InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
- 提供一组用来记录事务性活动的日志文件
- 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
- 提供全 ACID 兼容
- 在 MySQL 服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
InnoDB最大的特点就是支持事务：以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间。
9.4.3 MEMORY存储引擎
使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。
MEMORY 存储引擎管理的表具有下列特征：
- 在数据库目录内，每个表均以.frm 格式的文件表示。
- 表数据及索引被存储在内存中。（目的就是快，查询快！）
- 表级锁机制。
- 不能包含 TEXT 或 BLOB 字段。
MEMORY 存储引擎以前被称为HEAP 引擎。
MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。
10.事务
10.1 事务简介
数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
事务是一个不可分割的工作逻辑单元
一个事务其实就是一个完整的业务逻辑。
什么是一个完整的业务逻辑？
假设转账，从A账户向B账户中转账10000.
将A账户的钱减去10000（update语句）
将B账户的钱加上10000（update语句）
这就是一个完整的业务逻辑。以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。
这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。
10.2 事务和那些语句有关系呢?
只有DML(INSERT/UPDATE/DELETE)语句才会有事务这一说，其它语句和事务无关！！！
只有以上的三个语句和事务有关系，其它都没有关系。
因为 只有以上的三个语句是数据库表中数据进行增、删、改的。
只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑安全问题。
数据安全第一位！！！
10.3 事务的本质
假设所有的业务，只要一条DML语句就能完成，还有必要存在事务机制吗？正是因为做某件事的时候，需要多条DML语句共同联合起来才能完成，所以需要事务的存在。如果任何一件复杂的事儿都能一条DML语句搞定，那么事务则没有存在的价值了。
说到底，说到本质上，一个事务其实就是多条DML语句同时成功，或者同时失败！
事务：就是批量的DML语句同时成功，或者同时失败！
10.4 事务怎么做到多条DML语句同时成功/失败呢?
InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
事务开启了：
insert
insert
insert
delete
update
update
update
事务结束了！
在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。
提交事务
-
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
-
提交事务标志着，事务的结束。并且是一种全部成功的结束。
回滚事务
-
将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
-
回滚事务标志着，事务的结束。并且是一种全部失败的结束。
10.5 怎么提交事务，怎么回滚事务
提交事务:COMMIT;
回滚事务:ROLLBACK;(回滚永远都是只能回滚到上一次的提交点！)
开启事务:START TRANSACTION
那么MySQL默认的事务行为是怎样的呢?
mysql默认情况下是支持自动提交事务的。（自动提交）
什么是自动提交？每执行一条DML语句，则提交一次！
这种自动提交实际上是不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条。
10.6 演示事务
创建表
CREATE TABLE dept_bak(
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(20),
LOC VARCHAR(20)
)
10.6.1 回滚事务
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)
10.6.2 提交事务
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | bj |
+--------+-------+------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(20,'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> insert into dept_bak values(20,'abc','tj')
Query OK, 1 row affected (0.00 sec)
mysql> commit; -- 这里提交了 也就是定型了,在回滚就滚不回去了
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | bj |
| 20 | abc | tj |
| 20 | abc | tj |
| 20 | abc | tj |
+--------+-------+------+
4 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 | bj |
| 20 | abc | tj |
| 20 | abc | tj |
| 20 | abc | tj |
+--------+-------+------+
4 rows in set (0.00 sec)
10.7 事务包括4个特性
- 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
- 一致性（Consistency） 所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
- 隔离性（Isolation） :多个事务之间，操作的可见性
- 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
10.7 事务的隔离性分类
A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。
这道墙越厚，表示隔离级别就越高。
事务和事务之间的隔离级别有哪些呢？4个级别
10.7.1 读未提交
读未提交：read uncommitted（最低的隔离级别）[没有提交就读到了]
什么是读未提交？
事务A可以读取到事务B未提交的数据。
这种隔离级别存在的问题就是：
脏读现象！(Dirty Read)
我们称读到了脏数据。
这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！
10.7.2 读已提交
读已提交：read committed [提交之后才能读到]
什么是读已提交？
事务A只能读取到事务B提交之后的数据。
这种隔离级别解决了什么问题？
解决了脏读的现象。
这种隔离级别存在什么问题？
不可重复读取数据。
在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4 称为不可重复读取。
这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
oracle数据库默认的隔离级别是：read committed
10.7.3 可重复读
可重复读：repeatable read [提交之后也读不到，永远读取的都是刚开启事务时的数据]
什么是可重复读取？
事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
可重复读存在的问题是什么？
可以会出现幻影读。每一次读取到的数据都是幻象。不够真实！
早晨9点开始开启了事务，只要事务不结束到晚上9点，读到的数据还是那样！读到的是假象。不够绝对的真实。
mysql中默认的事务隔离级别就是这个！！！！！！！！！！！
10.7.4 序列化/串行化
序列化/串行化：serializable（最高的隔离级别）
这是最高隔离级别，效率最低。解决了所有的问题。
这种隔离级别表示事务排队，不能并发！synchronized，线程同步（事务同步）每一次读取到的数据都是最真实的，并且效率是最低的。
10.8 验证各种隔离级别
创建表
CREATE TABLE t_user(
name VARCHAR(20)
)
查看隔离级别：SELECT @@tx_isolation
10.8.1 验证read uncommited 读未提交
前提
mysql> set global transaction isolation level read uncommitted;-- 设置全局的隔离级别,改完之后退一下
测试
-- 执行顺序
事务A 事务B
---------------------------------------------------------------------
use mysql_blog;
use mysql_blog;
START TRANSACTION;
SELECT * FROM t_user;
START TRANSACTION;
INSERT INTO t_user VALUES ('zhangsan');
SELECT * FROM t_user;
-- 这里可以看到没有提交(commit)就查到了
10.8.2 验证 read commited 读已提交
前提
mysql> set global transaction isolation level read committed;
演示
-- 执行顺序
事务A 事务B
---------------------------------------------------------------------
use mysql_blog;
use mysql_blog;
START TRANSACTION;
SELECT * FROM t_user;
START TRANSACTION;
INSERT INTO t_user VALUES ('zhangsan');
SELECT * FROM t_user;
COMMIT;
SELECT * FROM t_user;
10.8.3 验证 repeatable read 可重复读
前提
set global transaction isolation level repeatable read;
演示
-- 执行顺序
事务A 事务B
---------------------------------------------------------------------
use mysql_blog;
use mysql_blog;
START TRANSACTION;
SELECT * FROM t_user;
START TRANSACTION;
INSERT INTO t_user VALUES ('zhangsan');
SELECT * FROM t_user;
COMMIT;
SELECT * FROM t_user;
-- 即便事务B提交了也查询不到
-- 只有重新打开的时候才可以查询的到
10.8.4 验证 serializable 序列化/串行化
事务A 事务B
--------------------------------------------------------------------------------
use mysql_blog;
use mysql_blog;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('abc');
select * from t_user;-- 如果没有事务Acommit的时候光标会停留
commit
这个时候才查询出来
11. 索引
11.1 什么是索引
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
对于一本字典来说，查找某个汉字有两种方式：
第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。
效率比较低。
第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个
位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过
索引检索，效率较高。
对于一条查询语句而言
select * from t_user where name = 'jack';
以上的这条SQL语句会去name字段上扫描，为什么？因为查询条件是：name=‘jack’
如果name字段上没有添加索引（目录），或者说没有给name字段创建索引，MySQL会进行全扫描，会将name字段上的每一个值都比对一遍。效率比较低
MySQL在查询方面主要就是两种方式：
- 第一种方式：全表扫描
- 第二种方式：根据索引检索。
注意：
在实际中，汉语字典前面的目录是排序的，按照a b c d e f…排序，
为什么排序呢？因为只有排序了才会有区间查找这一说！（缩小扫描范围其实就是扫描某个区间罢了！）
在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet数据结构相同。
TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。
遵循左小右大原则存放。采用中序遍历(左根右)方式遍历取数据。
11.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 0x7777
- 在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
- 在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
- 在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）
在mysql当中，主键上，以及unique字段上都会自动添加索引的！！！！
11.3 什么条件下，我们会考虑给字段添加索引呢？
- 条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
- 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
- 条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）
建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。
11.4 索引的创建和删除
11.4.1 创建索引：
CREATE INDEX 索引名称 on 表名（column）
11.4.2 删除索引：
DROP INDEX 索引名称 on 表名;
11.5 怎么查看一个SQL语句是否用了索引
-- 扫描14条记录：说明没有使用索引。type=ALL
mysql> explain select * from emp where ename = 'KING';
11.6 索引失效
11.6.1 失效情况1
select * from emp where ename like '%T';
ename上即使添加了索引，也不会走索引，为什么？原因是因为模糊匹配当中以“%”开头了！
尽量避免模糊查询的时候以“%”开始。
这是一种优化的手段/策略。
11.6.2 失效情况2
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
11.6.3 失效情况3
使用复合索引的时候，没有使用左侧的列查找，索引失效
什么是复合索引？两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引
create index emp_job_sal_index on emp(job,sal);
-- 要用到 job 去查找
mysql> explain select * from emp where job = 'MANAGER'; -- 索引有效
mysql> explain select * from emp where sal = 800; -- 索引失效
11.6.4 失效情况4
在where当中索引列参加了运算，索引失效。
explain select * from emp where sal+1 = 800;-- 失效
11.6.5 失效情况5
在where当中索引列使用了函数
12. 视图
12.1 视图的概述
view:站在不同的角度去看待同一份数据。
12.2 创建/删除视图
创建视图
CREATE VIEW 视图名称 as SELECT 视图字段 from 表名;
删除视图
DROP VIEW 视图名称;
注意：只有DQL语句才能以view的形式创建。
12.3 用视图做什么？
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）
假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。
每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？可以把这条复杂的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。
我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。
增删改查，又叫做：CRUD。
CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。
一般都说CRUD。C:Create（增）
R:Retrive（查：检索）
U:Update（改）
D:Delete（删）
12.4 练习
创建视图对象
create view dept_view as select * from dept;
面向视图插入
insert into dept_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');
查询原表数据
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 dept_view;
查看原有数据
mysql> select * from dept2;
Empty set (0.00 sec)
13.数据库设计的三大范式
13.1三大范式的概述
数据库表的设计依据。教你怎么进行数据库表的设计。
13.2 三大范式有那些
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。
13.3 第一范式
最核心，最重要的范式，所有表的设计都需要满足。必须有主键，并且每一个字段都是原子性不可再分。
学生表
学生编号 学生姓名 联系方式
------------------------------------------
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
13.4 第二范式
建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖。
学生编号 学生姓名 教师编号 教师姓名
----------------------------------------------------
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师
这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）
这是非常典型的：多对多关系！
分析以上的表是否满足第一范式？不满足第一范式。
修改
学生编号+教师编号(pk) 学生姓名 教师姓名
----------------------------------------------------------------------------------
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）
经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
产生部分依赖有什么缺点？
数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。
为了让以上的表满足第二范式，需要这样设计：使用三张表来表示多对多的关系！！！！
学生表
学生编号(pk) 学生名字
------------------------------------
1001 张三
1002 李四
1003 王五
教师表
教师编号(pk) 教师姓名
--------------------------------------
001 王老师
002 赵老师
学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
------------------------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002
多对多怎么设计？多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！
13.5 第三范式
第三范式建立在第二范式的基础之上,要求所有非主键字典必须直接依赖主键，不要产生传递依赖。
学生编号（PK） 学生姓名 班级编号 班级名称
------------------------------------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
以上表的设计是描述：班级和学生的关系。很显然是1对多关系！一个教室中有多个学生
分析以上表是否满足第一范式？。
满足第一范式，有主键
分析以上表是否满足第二范式。
满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键
分析以上表是否满足第三范式？
第三范式要求：不要产生传递依赖！一年一班依赖01，01依赖1001，产生了传递依赖。不符合第三范式的要求。产生了数据的冗余
那么应该怎么设计一对多呢？
班级表：一
班级编号(pk) 班级名称
----------------------------------------
01 一年一班
02 一年二班
03 一年三班
学生表：多
学生编号（PK） 学生姓名 班级编号(fk)
---------------------------------------------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
一对多，两张表，多的表加外键！！！！！！！！！！！！
13.6 总结表的设计
一对多：一对多，两张表，多的表加外键！！！！！！！！！！！！
多对多：多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！
说CRUD。
C:Create（增）
R:Retrive（查：检索）
U:Update（改）
D:Delete（删）
12.4 练习
创建视图对象
create view dept_view as select * from dept;
面向视图插入
insert into dept_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');
查询原表数据
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 dept_view;
查看原有数据
mysql> select * from dept2;
Empty set (0.00 sec)
13.数据库设计的三大范式
13.1三大范式的概述
数据库表的设计依据。教你怎么进行数据库表的设计。
13.2 三大范式有那些
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。
13.3 第一范式
最核心，最重要的范式，所有表的设计都需要满足。必须有主键，并且每一个字段都是原子性不可再分。
学生表
学生编号 学生姓名 联系方式
------------------------------------------
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
13.4 第二范式
建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖。
学生编号 学生姓名 教师编号 教师姓名
----------------------------------------------------
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师
这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）
这是非常典型的：多对多关系！
分析以上的表是否满足第一范式？不满足第一范式。
修改
学生编号+教师编号(pk) 学生姓名 教师姓名
----------------------------------------------------------------------------------
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）
经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
产生部分依赖有什么缺点？
数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。
为了让以上的表满足第二范式，需要这样设计：使用三张表来表示多对多的关系！！！！
学生表
学生编号(pk) 学生名字
------------------------------------
1001 张三
1002 李四
1003 王五
教师表
教师编号(pk) 教师姓名
--------------------------------------
001 王老师
002 赵老师
学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
------------------------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002
多对多怎么设计？多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！
13.5 第三范式
第三范式建立在第二范式的基础之上,要求所有非主键字典必须直接依赖主键，不要产生传递依赖。
学生编号（PK） 学生姓名 班级编号 班级名称
------------------------------------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
以上表的设计是描述：班级和学生的关系。很显然是1对多关系！一个教室中有多个学生
分析以上表是否满足第一范式？。
满足第一范式，有主键
分析以上表是否满足第二范式。
满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键
分析以上表是否满足第三范式？
第三范式要求：不要产生传递依赖！一年一班依赖01，01依赖1001，产生了传递依赖。不符合第三范式的要求。产生了数据的冗余
那么应该怎么设计一对多呢？
班级表：一
班级编号(pk) 班级名称
----------------------------------------
01 一年一班
02 一年二班
03 一年三班
学生表：多
学生编号（PK） 学生姓名 班级编号(fk)
---------------------------------------------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
一对多，两张表，多的表加外键！！！！！！！！！！！！
13.6 总结表的设计
一对多：一对多，两张表，多的表加外键！！！！！！！！！！！！
多对多：多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
