一、SQL介绍

1.1、SQL概述

人和人交流需要语言，人和数据库交流也需要语言，而这个专门特定为程序员和数据库打交道的语言就是 SQL 语言。

SQL：结构化查询语言(Structured Query Language)。是关系型数据库标准语言。特点：简单，灵活，功能强大。

1.2、SQL包含的6个部分

1.2.1、数据查询语言（DQL）

其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字 SELECT 是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些 DQL 保留字常与其他类型的SQL语句一起使用。

1.2.2、数据操作语言（DML）

其语句包括动词 INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作语言。

1.2.3、数据定义语言（DDL）

其语句包括动词 CREATE 和 DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

1.2.4、事务处理语言（TPL）

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

1.2.5、数据控制语言（DCL）

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

1.2.6、指针控制语言（CCL）

它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

1.3、书写规则

数据库中，SQL 语句大小写不敏感. 如: select、SELECT.、SeleCt，为了提高可读性，一般关键字大写，其他小写。
SQL 语句可单行或多行书写,用分号来分辨是否结束。
合理利用空格和缩进使程序易读

二、表和ORM

2.1、表

二维表是同类实体的各种属性的集合，每个实体对应于表中的一行，在关系中称为元组，相当于通常的一条记录；表中的列表示属性，称为Field，相当于通常记录中的一个数据项，也叫列、字段。行: 表示一个实体,一条记录列: 字段,数据项。

2.2、表和对象的关系(ORM)

ORM： Oject Reraltional Mapping : 对象表的映射

在开发中，我们需要将表中的数据查询出来保存到内存中，或者把内存中的数据保存到数据库中，此时就需要将数据表的数据和Java中的对象进行映射关联起来。这种映射关联就称为 ORM 思想。

三、MySQL服务

3.1、MySQL服务

打开数据库连接之前:一定要保证 MySQL 服务已经开启了。

net start命令开启一个服务，如：net start MySQL。

net stop 命令关闭一个服务器，如：net stop MySQL

3.2、连接MySQL

方式一

进入 MySQL 自带的客户端, 在命令行中输入密码。

方式二

在运行（win + r 进入cmd ）中输入命令。

格式：mysql -u账户 -p密码 -h数据库服务器安装的主机 -P数据库端口

mysql -uroot -padmin -h127.0.0.1 -P3306
复制代码

若连接的数据库服务器在本机上，并且端口是 3306。则可以简写： mysql -uroot -padmin。

四、数据库基础

4.1、数据库基本操作

查看数据库服务器存在哪些数据库.：SHOW DATABASES。
使用指定的数据库.：USE database_name。
查看指定的数据库中有哪些数据表：SHOW TABLES。
创建指定名称的数据库.：CREATE DATABASE database_name。
删除数据库：DROP DATABASE database_name。

4.2、存储引擎

MySQL 中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

简单来说，存储引擎是表的存储方式。

MySQL常用存储引擎：

MyISAM：拥有较高的插入，查询速度，但不支持事务，不支持外键。
InnoDB：支持事务，支持外键，支持行级锁定，性能较低。最安全

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但对比MyISAM，处理效率差，且会占用更多的磁盘空间以保留数据和索引。一个系统，特别是金融系统，没有事务是很恐怖的事情，一般都要选择 InnDB。

五、MySQL列的常用类型

5.1、最常用的类型

MsSQL	Java
INT	int
BIGINT	long
DECIMAL	BigDecimal
DATE/DATETIME	java.util.Date
VARCHAR	String

5.2、整数类型

整数类型有宽度指示器，作用是指定位宽。

例如：某字段类型为 INT(3),保证少于3个值，从数据库检索出来时能够自动地用 0 填充，需设置填充，默认不填充。

宽度指示器不影响列存值得范围。一般不指定位宽。

5.3、小数类型

FLOAT[(s,p)] 或DOUBLE[(s,p)]: 小数类型，可存放实型和整型，精度 (p) 和范围 (s)。

DECIMAL : 高精度类型，金额货币优先选择。

5.4、字符类型

char(size) ：定长字符，0 - 255字节，size 指 N 个字符数，若插入字符数超过设定长度，会被截取并警告。
varchar(size)：变长字符，0 - 255字节，从 MySQL5 开始支持 65535 个字节，若插入字符数超过设定长度，在非严格模式下会被截取并警告。

一般存储大量的字符串，比如文章的纯文本，可以选用 TEXT 系列类型，这个系列都是变长的。

注意: 在 MySQL 中，字符类型必须指定长度，值要使用单引号引起来。相当于Java中字符(String,StringBuilder/StringBuffer);

5.5、日期类型

常用日期和时间类型： DATE、DATETIME。

注意: 在 MySQL 中，日期时间值使用单引号引起来。相当于 Java中 Date，Calender。

5.6、二进制类型

二进制类型主要用于存放图形、声音和影像，二进制对象，0-4GB。

开发中，我们一般存储二进制文件保存路径,所以以上的类型非特殊需求不会使用。

BIT，一般存储 0 或 1，存储是 Java 中的 boolean/Boolean 类型的值（需要使用）。

六、表的操作（DDL）

表的操作主要是使用 DDL 来创建表和删除表等操作

6.1、创建表

6.1.1、语法

CREATE TABLE 表名 (
  列名1 列的类型 [约束],
  列名2 列的类型 [约束],
  ....
  列名N 列的类型 约束
);
-- 注意:最后一行没有逗号
复制代码

6.1.2、例子

创建一张学生表(t_student) 有id、name、email、age。

CREATE TABLE t_student (
  id BIGINT,
  name VARCHAR(15),
  email VARCHAR(25),
  age INT
);
复制代码

6.1.3、注意

创建表时，不能使用 MySQL 的关键字、保留字。

解决办法：

# 1. 尽量避免使用关键字,可以使用其他的单词或单词组合来代替。
# 2. 一般情况下,创建表的时候习惯使用 t_ 做表名的开头。
# 3. 使用反引号(``) 将表名括起来就 ok  (`order`)。
复制代码

6.2、删除表

6.2.1、语法

DROP TABLE 表名;
复制代码

6.2.2、例子

-- 删除订单表
DROP TABLE `order`;
复制代码

6.2.3、注意

如果表名是数据库的关键字或保留字需要加上反引号 (`)

6.3、表的复制和批量插入

6.3.1、表的复制

表的复制本质上是将查询结果当做表创建出来。

create table 表名 as select语句；
复制代码

6.3.2、表的批量插入

表的批量插入本质上是将查询结果插入到另一张表中。

insert into dept1 select * from dept;
复制代码

6.5、表的约束

约束是为了保证表中的数据的合法性、有效性和完整性，我们一般对表会有约束。

非空约束：NOT NULL，不允许某列的内容为空。
设置列的默认值：DEFAULT。
唯一约束：UNIQUE，在该表中，该列的内容必须唯一。
主键约束：PRIMARY KEY，非空且唯一。
主键自增长：AUTO_INCREMENT，从 1 开始，步长为 1。
外键约束：FOREIGN KEY，A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键)。

6.5.1、主键约束

主键值是这行记录在这张表中的唯一标识，就如同身份证号。一张表的主键约束只能有一个。

主键约束(primary key)不能重复且不能为NULL。

6.5.1.1、主键的分类

业务主键:使用有业务含义的列作为主键 (不推荐使用);
自然主键:使用没有业务含义的列作为主键 (推荐使用);

6.5.1.2、如何设计主键

对于主键，我们有以下两种的主键设计原则：

单字段主键，单列作为主键，建议使用。
复合主键，使用多列充当主键，不建议。

6.5.1.3、结论

使用单字段的自然主键。

6.5.1.4、例子

创建学生表，id为主键自增，name唯一，email不为空，age默认18。

-- 移除存在的表
DROP TABLE IF EXISTS `t_student`;
  CREATE TABLE t_student(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(25) UNIQUE,
  email VARCHAR(25) NOT NULL,
  age INT DEFAULT 18
);
复制代码

6.5.2、外键约束（foreign key）

**外键是另一张表的主键。**例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。

外键可以为NULL，且不一定是另一张的主键，但是必须具有唯一性，一般情况下会引用另一张表的主键。

create table t_student(
  sno it,
    sname varchar(255),
    classno ,int
    foreign key (classno) references t_class(no) -- 对t_student的classno字段添加外键约束，引用的是t_calss的no字段
);
复制代码

6.5.3、唯一性约束（unique）

唯一约束修饰的字段具有唯一性，不可以重复，但是可以为NULL，也可以同时为NULL。

create table t_user(
  id int,
    username varchar(255) unique,-- 列级约束
    pwd varchar(255)
)
复制代码

我们也可以同时给两个列或者多个列添加唯一约束。

-- 这样表示两个字段连起来不能重复,两个字段添加一个约束。表级约束
create table t_user(
  id,int,
    username varchar(255),
    pwd varchar(255),
    unique(username,pwd)
)
复制代码

-- 这样表示两个字段都不能重复,两个字段加两个约束。
create table t_user(
  id,int,
    username varchar(255) unique,
    pwd varchar(255) unique
)
复制代码

6.6、表与表之间的关系

6.6.1、一对一

例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

6.6.2、一对多（多对一）

一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

6.6.3、多对多

例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

七、DML增删改操作

DML是数据操作语句，用户对表的数据进行操作，所有的DML操作都有一个受影响的行，表示SQL执行，操作了多少行数据。

7.1、插入操作

7.1.1、语法

INSERT INTO 表名 (列1,列2,列3...) VALUES(值1,值2,值3...);
复制代码

7.1.2、例子

-- 1.插入完整数据记录
INSERT INTO t_student(name,email,age) VALUES('xiaoming','xiao@',18);
-- 2.插入数据记录一部分
INSERT INTO t_student(name,age) VALUES('xiaodong',19);
-- 3.插入多条数据记录（MySQL特有）
INSERT INTO t_student(name,email,age) VALUES('xiaohong','hong@',17),
('xiaohong2','hong2@',17),('xiaohong3','hong@3',17)
-- 4.插入查询结果
INSERT INTO t_student(name,email,age) SELECT name,email,age FROM t_student
复制代码

7.1.3、注意

一次插入操作只插入一行，插入多条数据为 MySQL 特有语法（不推荐使用，Mybatis有循环来批量加入）

7.2、修改操作

7.2.1、语法

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, column3 = value3...
WHERE [条件]
复制代码

7.2.2、练习

-- 将张三改为西门吹水
UPDATE t_student SET name='西门吹水' WHERE name='张三';
-- 将 id 为3 的 name 改为叶孤城,email 改为ye@,age 改为100
UPDATE t_student SET name='叶孤城' WHERE id=3;
复制代码

7.2.3、注意

如果省略了条件，那么整张表的数据都会被修改，所以一般都会带上条件
修改语句没有from关键字。

7.3、删除操作

7.3.1、语法

DELETE FROM 表名 WHERE [条件]
复制代码

7.3.2、练习

-- 删除 id 为 2 的学生信息
DELETE FROM t_student WHERE id=2;
-- 删除叶孤城的所有信息
DELETE FROM t_student WHERE name='叶孤城'
复制代码

7.3.3、注意

FROM 不能写成 FORM
如果省略了 WHERE 子句，则全表的数据都会被删除

八、DQL 查询操作

8.1、语法说明

SELECT 列1,列2,列3... FROM 表名 [WHERE];
-- SELECT 选择要查询的列
-- FROM 提供数据源 (表、视图或其他的数据源)
-- 可以写*表示查询所有列，但是在实际开发中基本上不会使用，性能低，实际开发中是将所有字段列出来
复制代码

8.2、普通查询

8.2.1、设置别名

8.2.1.1、语法

SELECT 列名 AS 别名 FROM 表名 [WHERE];
SELECT 列名 别名 FROM 表名 [WHERE]
复制代码

8.2.1.2、作用

改变列的标题头。
作为计算结果的含义。
作为列的别名。
如果别名中使用特殊字符，或是强制大小写或有空格时都需要加单引号。

8.2.1.3、例子

-- 查询所有货品的id，名称，各进50个，并且每个运费1元的成本（使用别名）
SELECT id,productName,(costPrice + 1) * 50 1 AS allPrice FROM product
SELECT id,productName,(costPrice + 1) * 50 allPrice FROM product
复制代码

8.2.2、按照格式输出

为方便用户浏览查询结果数据，有时需要设置查询结果的显示格式，可以使用 CONCAT 函数来连接字符串。

8.2.2.1、语法

CONCAT(字符串1,字符串2,...)
复制代码

8.2.2.2、实战

-- 查询商品的名字和零售价。格式： xxx 商品的零售价为：ooo
SELECT CONCAT(productName,'商品的零售价为：',salePrice) FROM product
复制代码

8.2.3、消除重复的数据

distinct前面不能接其他的字段，他只能出现在所有字段的最前方。他表示的意思是后面所有的字段联合起来一起去重。

SELECT DISTINCT 列名, ... FROM 表名;
复制代码

8.2.4、算数运算符

对 number 型数据可以使用算数操作符创建表达式他有如下优先级：

乘法和除法的优先级高于加法和减法。
同级运算的顺序是从左到右。
表达式中使用"括号"可强行改变优先级的运算顺序

-- 查询所有货品的id，名称和批发价(批发价=卖价*折扣)
SELECT id,productName,salePrice * cutoff FROM product
-- 查询所有货品的id，名称，和各进50个的成本价(成本=costPirce)
SELECT id,productName,costPrice * 50 FROM product
-- 查询所有货品的id，名称，各进50个，并且每个运费1元的成本
SELECT id,productName,(costPrice + 1) * 50 FROM product
复制代码

8.2.5、比较运算符

比较运算符有如下几个：

=
>
<
<=
!= （<> 等价 !=）

-- 查询商品名为 罗技G9X 的货品信息
SELECT * FROM product WHERE productName='罗技G9X';
-- 查询零售价小于等于 200 的所有货品信息
SELECT * FROM product WHERE salePrice <= 200
-- 查询批发价大于 350 的货品信息
SELECT *,salePrice * cutoff allPrice FROM product WHERE salePrice * cutoff > 350
复制代码

8.2.6、逻辑运算符

运算符	含义
AND	如果组合的条件都是TRUE，返回TRUE
OR	如果组合的条件之一是TRUE，返回TRUE
NOT	如果下面的条件是FALSE，返回 TRUE，如果是 TRUE ,返回 FALSE

8.2.7、范围匹配

范围匹配：BETWEEN AND 运算符,一般使用在数字类型的范围上。但对于字符数据和日期类型同样可

用。需要两个数据。

8.2.7.1、语法

WHERE 列名 BETWEEN minValue AND maxValue; -- 闭区间
复制代码

8.2.7.2、例子

-- 查询零售价在300-400 之间的货品信息
SELECT * FROM product WHERE salePrice BETWEEN 300 AND 400
-- 查询零售价不在300-400之间的货品信息
SELECT * FROM product WHERE NOT salePrice BETWEEN 300 AND 400
复制代码

8.2.8、集合查询

集合查询：使用 IN 运算符，判断列的值是否在指定的集合中。

8.2.8.1、语法

WHERE 列名 IN (值1，值2....);
复制代码

8.2.8.2、例子

-- 查询分类编号为2,4的所有货品的id，货品名称，
SELECT id,productName FROM product WHERE dir_id IN (2,4)
-- 查询分类编号不为2,4的所有货品的id，货品名称，
SELECT id,dir_id,productName FROM product WHERE dir_id NOT IN (2,4)
复制代码

8.2.9、判空

IS NULL: 判断列的值是否为空值，非空字符串，空字符串使用==判断。

8.2.9.1、语法

WHERE 列名 IS NULL;
复制代码

8.2.9.2、例子

-- 查询商品名为NULL的所有商品信息。
SELECT * FROM product WHERE productName IS NULL;
SELECT * FROM product WHERE supplier =''
复制代码

8.2.9.3、注意

使用=来判断只能判断空字符串,不能判断null 的，而使用IS NULL只能判断null值，不能判断空字符串。

8.2.10、过滤查询

使用 WHERE 子句限定返回的记录

8.2.10.1、语法

SELECT <selectList>
FROM 表名
WHERE 条件;
复制代码

8.2.10.2、注意

WHERE子句在 FROM 子句后。
查询语句的字句的执行顺序 FROM 子句: 从哪张表中去查询数据 => WHERE 子句 : 筛选需要哪些行的数据 => SELECT 子句: 筛选要显示的列。

8.2.11、模糊查询

模糊查询数据使用 LIKE 运算符执行通配查询，他有两个通配符：

%：表示可能有零个或者任意多个字符。
_：表示任意的一个字符。

8.2.11.1、语法

WHERE 列名 Like '%M_'
复制代码

8.2.11.2、例子

-- 查询货品名称匹配'%罗技M9_' 的所有货品信息
SELECT * FROM product WHERE productName LIKE '%罗技M9_'
复制代码

8.3、结果排序

使用 ORDER BY 子句将查询结果进行排序，他有两种排序的模式：

ASC : 升序（默认）。
DESC：降序。

ORDER BY 子句出现在，SELECT 语句的最后。

8.3.1、例子

--单列排序： 选择id，货品名称，分类编号,零售价并且按零售价降序排序
SELECT id,productName,dir_id,salePrice FROM product ORDER BY salePrice DESC
--多列排序： 选择id，货品名称，分类编号,零售价先按分类编号降序排序,再按零售价升序排序
SELECT * FROM product ORDER BY dir_id DESC,salePrice ASC
复制代码

8.3.2、注意

谁在前面谁先排序。
如果列的别名使用 ' ' 则按此别名进行的排序无效。

-- 反例
SELECT id,salePrice 'sp' FROM product ORDER BY 'sp'
复制代码

8.4、分页查询

limit是mysql特有的，他用于取结果集中的部分数据，Oracle中有一个相同的机制，叫rownum。

limit是SQL语句最后执行的环节。

8.4.1、语法

SELECT <selectList>
FROM 表名
[WHERE] LIMIT ?,?
-- 第一个? : 开始行的索引数 beginIndex，默认为0
-- 第二个? : 每页显示的最大记录数 pageSize
-- 每页显示 3条数据
-- 第一页： SELECT * FROM product LIMIT 0,3
-- 第三页： SELECT * FROM product LIMIT 6,3
-- 第八页： SELECT * FROM product LIMIT 21,3
-- 当前页 : currentPage
-- 每页显示的最大记录数: pageSize
复制代码

8.4.2、通用的标准分页sql

beginIndex = (currentPage - 1) * pageSize

8.4.3、案例

案例

找出工资排名在4到6名的员工

select name,sal,from emp order by desc limit 3,6;
复制代码

8.5、分组函数

COUNT(*) ：统计表中有多少条记录
SUM(列) ：汇总列的总和
MAX(列) : 获取某一列的最大值
MIN(列) : 获取某一列的最小值
AVG(列) : 获取列的平均值

-- 查询货品表中有多少数据
SELECT COUNT(*) FROM product
-- 计算所有货品的总的进货价
SELECT SUM(costPrice) FROM product
复制代码

注意：

分组忽略null，无需额外过滤是否为null这个条件。**
SQL语句中有一个语法规则，分组函数不可以直接使用在where字句当中。
count(*)和count(具体的字段的区别)

count(*)一定是总记录数，和字段无关。
count(具体的某个字段)是这个字段不为空的记录数。

8.6、分组查询

8.6.1、group by

group by：按照某个字段或者是某些字段进行分组。

聚合函数分组会和group by一起联合使用，并且任何一个分组函数都是在group by语句执行结束之后才会执行。当一条sql语句没有group by的话，整张表的数据会自成一组。 SQL语句中有一个语法规则，分组函数不可以直接使用在where字句当中。原因是因为：group by是在where执行之后才会执行。如下面这条错误的sql语句：

select * from emp where sal > avg(sal);
复制代码

当执行到avg(sal)的时候，还没有执行group by，所以没办法执行分组函数。还没有分组就不可以执行分组函数。

需求：求每一个工作岗位的最高薪资

select max(sal),job from emp group by job;
复制代码

结论：当一条sql语句中有group by的时候，select 后面只允许出现分组函数或者是参加分组的字段。

需求：找出每个部门不同工作岗位的最高薪资）

select
  deptno ,job ,max(sql)
from 
  emp
group by
  deptno,job
复制代码

8.6.2、having

having：having是对分组之后的数据进行再次过滤。

需求：找出每个部门的平均薪资，要求显示薪资大于2000的数据

select val(sal),deptno from emp group by deptno having val(sal) > 2000;
复制代码

8.6.3、group by和having的总结

能用where过滤的就先用where过滤，无法用where过滤的在用having，但是having一定要搭配group by使用，先分组在过滤。

8.6、DQL字句的执行顺序

select 
  ...
from 
  ...
where
  ...
group by
  ...
having
  ...
order by
  ...
limit
  ...
复制代码

from: 从哪张表中去查数据。
where: 筛选需要的行数据。
group by :分组
having：对分组的数据进行再次过滤
SELECT : 筛选需要显示的列的数据。
ORDER BY : 排序操作。

九、多表查询

9.1、连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般是多张表进行联合查询取出最终的结果，一般一个业务都会对应多张表。

连接查询的分类有两种：

SQL92（语法较老，过时）。
SQL99（语法比较新）。

9.2、笛卡尔积现象

当两张表进行连接查询的时候，没有任何条件进行限制，最终查询的结果条数是两张表记录条数的乘积，这个现象称为笛卡尔积现象。

我们在开发的时候一般会给表起别名，他有两个好处：

执行效率高。
可读性好

select 
  e.ename,d.dname
from
  emp e,dept d
复制代码

这样出现的条数就是两张表条数的乘积。

既然出现了笛卡尔积现象，我们就要避免笛卡尔积现象，避免笛卡尔积现象的措施就是增加条件进行过滤，但是避免了笛卡尔积你现象，会减少记录匹配的次数吗？答案是不会，次数还是两张表条数的乘积，只不过显示的是有效的记录数。

9.3、内连接

假设A和B两张表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录都会被查询出来，AB两张表是平等的，没有主副之分，这就是内连接。

9.3.1、等值连接

内连接最大的特点是：条件是等量关系。

select
  ...
from
  ...
inner join -- inner是可以省略的，带着inner可读性更好
  ...
on 
  连接条件
where
  ...
复制代码

select 
  e.name,d.name 
from 
  emp e 
inner join -- inner是可以省略的，带着inner可读性更好
  dept d 
on 
  e.deptno =  d.deptno
复制代码

SQL99语法结构更清晰一些，表的连接条件和后来的where过滤条件分离了。

9.3.2、非等值连接

连接条件中的关系是非等量关系。

select e.name,e.sal,e.grade
from emp e
join salgrade s
on e.sal between s.local and s.hisal
复制代码

9.3.3、自连接

最大的特点是一张表看成两张表，自己连接自己。（不常用）

9.4、外连接

假设A表和B表进行连接，使用外连接的话，AB两张表有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表。

当副表中的数据没有和主表中的数据匹配上的时候副表自动模拟出NULL与之匹配。主表的数据会无条件的全部查询出来。

9.4.1、外连接的分类

外连接分为两类：

左外连接（左连接 LEFT）：表示左边的这张表是主表。
右外连接（右连接 RIGHT）：表示右边的这张表是主表。

左连接有连接的写法，右连接也有对应的左连接的写法。用左连接LEFT的时候，说明上面（左边）的表是主表。

9.4.1.1、左连接

SELECT * FROM emp e LEFT OUTER JOINdept d ON e.deptno=d.deptno;
复制代码

注意：OUTER可以省略

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

9.4.1.2、右连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

需求：

dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno
复制代码

9.4.2、注意

内连接说明两张表是平等的，没有主副之分。

外连接说明有一张表是主表，另一张表是副表。

在开发中外连接居多，因为内连接查询的数据会丢失。

9.5、三张表连接

select ...
from
  ...
join
  ...
on
  ...
join
  ...
on
  ...(条件)
复制代码

9.6、union

union关键字可以用于将查询结果集相加。他是连接两个查询结果的，可以用于两张不相干的表中的数据拼接在一起显示。

注意：union必须用于两张列数相同的表进行查询，否则无法显示。

案例

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

select ename,job from  emp where job = 'MANAGER'
union
select ename,job from  emp where job = 'SALESMAN'
复制代码

十、子查询

10.1、子查询概述

select语句中嵌套select语句，被嵌套的select语句就是子查询，他可以出现的位置有select、from、where后。

10.2、where子句中使用

案例

找出高于平均薪资的员工信息

select * 
from emp 
where sal > (select avg(sal) from emp);
复制代码

10.3、from字句后使用

案例

找出每个部门平均薪水的薪资等级。

先找出每个部门的平均薪水（按照部门编号分组，求sal的平均值）

select deptno,avg(sal) avgsal from emp group by deptno
复制代码

将以上的查询结果作为临时表t，让t表和salgrade（薪水等级表） s连接，条件是：t.avgsal between s.losal and s.hisal

select 
  t.*,s.grade
from
  (select deptno,avg(sal) avgsal from emp group by deptno) t
join
  salgrade s
on
  t.avgsal between s.losal and s.hisal
复制代码

10.4、在select后使用

需求

找出每个员工所在的部门名称，要求显示员工名和部门名。

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

十一、事务

11.1、什么是事务

一个事务是一个i完整的业务逻辑单元，不可再分。事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。

和事务相关的语句只有DML语句，因为他们这三个语句都是和数据库表中的数据相关的。事务的存在是为了保证数据的完整性、安全性。

11.2、开启事务的原理

假设我们完成一个操作，需要先执行一条insert，然后再执行一条update，最后执行一条delete，在mysql中执行流程可以这么理解：

11.3、事务的特征

事务具有四个特征ACID

原子性（Atomicity）

事务是最小的工作单元，不可再分。整个事务中的所有操作，必须作为一个单元全部完成（取消）。

一致性（Consistency）

事务必须保证多条DML语句同时成功或者同时失败。

隔离性(Isolation)

一个事务不会影响其他事务的运行。

持久性(Durability)

最终该事务对数据库所作的更改将持久地保存在硬盘文件之中，事务才算成功。

MySQL事务默认情况下是自动提交的，可以通过命令来改成手工提交。

start transaction;
复制代码

11.4、隔离性详解

11.4.1、并发访问可能导致的问题

####  11.4.1.1、脏读取
复制代码

一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。

11.4.1.2、不可重复读

在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。

11.4.1.3、幻读

幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。幻读强调的是前后读的行数不一样。

11.4.2、隔离级别

InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务。隔离级别从低往高依次是：

读未提交（READ UMCOMMITTED）
读已提交（READ COMMITTED）
可重复读（REPEATABLE READ） MySQL默认
串行化（SERIALIZABLE）

11.4.2.1、读未提交

对方的事务还没有提交，我们当前事务可以读取到对方未提交的数据。这种隔离级别是最低的，读为未提交存在脏读现象，表示堵到了脏数据。

11.4.2.2、读已提交

对方事务提交之后的数据我们才可以读到，这种隔离级别解决了脏读现象，但是却出现了不可重复读现象。

这个级别是oracle的默认隔离级别。

11.4.2.3、可重复读

我们无法看到已提交的事务了，这种隔离级别虽然解决了不可重复读的问题，但是却带来了幻读的问题。比方说一个线程删除了数据库中的所有数据，但是我们依然读取的是原来的数据，读到的是数据库的备份。

MySQL的默认级别。

11.4.2.4、串行化

将一个事务与其他事务完全地隔离。两个事务不可以并发，线程之间需要排队，也叫作序列化。虽然很安全，但是性能很低且客户的体验不好。

十二、索引

12.1、什么是索引

索引相当于一本书的目录，通过目录可以快速找到对应的资源。索引被用来快速找出在一个列上用一特定值的行，索引可以有效地缩小扫描的范围。添加索引是给某个字段或者是某些字段添加的。

在数据库方面，查询一张表的时候有两种检索方式：

全表扫描
根据索引检索（效率高）

索引虽然可以提高检索的效率，但是不能随意添加索引，因为索引也是数据库中的对象，也需要数据库不断地维护，维护需要成本的。比如表中的的数据如果经常被修改的话就不适合添加索引，因为数据一旦被修改，索引需要重新排序。

12.2、什么时候需要创建索引

数据量庞大。
该字段很少的DML操作（因为字段进行修改操作，索引也需要维护）。
该字段经常出现在where子句中（经常根据哪个字段查询）

注意：主键和具有unique约束的字段会自动添加索引，根据主键查询的效率高，尽量根据主键索引，我们可以查询sql语句的执行计划。他的底层是B+Tree。

explain select * from emp where SAL = 1500;
复制代码

type字段的值时ALL表示是全表扫描（没有添加索引）。rows表示搜索了14条数据。

12.3、添加索引

-- 给emp表的sal字段添加一个索引，名称为emp_sal_index
create index emp_sal_index on emp(sal);
-- 语法格式
create index 索引名称 on 表名(字段名)
复制代码

12.4、查看索引

-- 查看索引的语法
show index from emp;
-- 语法格式
show index from 表名;
复制代码

12.5、删除索引

-- 删除索引的语法
drop index 索引名称 on 表名;
复制代码

12.6、索引的原理

索引底层采用的数据结构是B+Tree，通过B+Tress缩小扫描范围，底层索引进行排序、分区，索引会携带在表中的物理地址，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位到表中的数据，效率是最高的（不走表，走硬盘）。

select ename from emp where ename = 'SMITH';
复制代码

通过索引sql语句会转换

select ename from emp where 物理地址 = '索引检索到的物理地址'
复制代码

12.7、索引的分类

单一索引：给打那个字段添加索引。
复合索引：给多个字段联合起来添加索引。
主键索引：主键上会自动添加索引。
唯一索引：有unique约束的字段上会自动添加索引。

12.8、索引的失效

在模糊查询的时候，如果第一个通配符使用的是%，这个索引会失效，因为他不知道一开始匹配的字符是什么。

十三、视图

13.1、什么是视图

视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。所以他也称为虚拟表。

视图是站在不同的角度看到数据，同一张表的数据，通过不同的角度去看待数据。

我们可以对视图进行增删改查，会影响到原表的数据，通过视图来影响原表数据的，并不是直接操作原表。只有DQL语句才可以以视图对象的方式创建出来。

13.2、创建视图

-- 语法格式
create view 视图名 as select语句
-- 示范
create view myview as select empo,ename from emp;
复制代码

13.3、修改视图

-- 语法格式
update 视图名 set 列名 = '值' where 条件;
复制代码

13.4、删除视图

-- 语法格式
delete from 视图名 where 条件;
-- 示范
delete from myview where empo = '12134';
复制代码

13.5、视图的作用

视图可以隐藏表的实现细节，保密级别比较高的系统，数据库只对外提供相关的视图，面向视图对象进行CRUD。

十四、数据库设计三范式

设计范式是设计表的依据，按照这三个范式设计的表不会出现数据冗余。但是在实际开发中，根据客户的需求，可能会拿数据冗余来换取执行速度，拿空间换时间。

14.1、第一范式

任何一张表都应该有主键，且每一个字段原子性不可再分。

14.2、第二范式

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

典型的例子就是解决多对多的问题上，遇到多对多的时候，背口诀：多对多？三张表，关系表两外键

14.3、第三范式

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

典型的例子就是一对多，遇到一对多问题的时候背口诀：一对多？两张表，多的表加外键。

14.4、一对一关系的设计方案

14.4.1、主键共享

t_user_login 用户登录表

id(pk)	username	password
1	zs	123
2	ls	456

t_user_detail 用户详细信息表

id(pk+fk)	realname	tel
1	张三	111
2	李四	456

14.4.2、外键唯一

t_user_login 用户登录表

id(pk)	username	password
1	zs	123
2	ls	456

t_user_detail 用户详细信息表

id(pk)	realname	tel	userid(fk+unique)
1	张三	111	2
2	李四	456	2

视图

-- 语法格式
delete from 视图名 where 条件;
-- 示范
delete from myview where empo = '12134';
复制代码

13.5、视图的作用

视图可以隐藏表的实现细节，保密级别比较高的系统，数据库只对外提供相关的视图，面向视图对象进行CRUD。

十四、数据库设计三范式

14.1、第一范式

任何一张表都应该有主键，且每一个字段原子性不可再分。

14.2、第二范式

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

典型的例子就是解决多对多的问题上，遇到多对多的时候，背口诀：多对多？三张表，关系表两外键

14.3、第三范式

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

典型的例子就是一对多，遇到一对多问题的时候背口诀：一对多？两张表，多的表加外键。

14.4、一对一关系的设计方案

14.4.1、主键共享

t_user_login 用户登录表

id(pk)	username	password
1	zs	123
2	ls	456

t_user_detail 用户详细信息表

id(pk+fk)	realname	tel
1	张三	111
2	李四	456

14.4.2、外键唯一

t_user_login 用户登录表

id(pk)	username	password
1	zs	123
2	ls	456

t_user_detail 用户详细信息表

id(pk)	realname	tel	userid(fk+unique)
1	张三	111	2
2	李四	456	2

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

全网最全MySQL知识点万字整理

一、SQL介绍

1.1、SQL概述

1.2、SQL包含的6个部分

1.2.1、数据查询语言（DQL）

1.2.2、数据操作语言（DML）

1.2.3、数据定义语言（DDL）

1.2.4、事务处理语言（TPL）

1.2.5、数据控制语言（DCL）

1.2.6、指针控制语言（CCL）

1.3、书写规则

二、表和ORM

2.1、表

2.2、表和对象的关系(ORM)

三、MySQL服务

3.1、MySQL服务

3.2、连接MySQL

四、数据库基础

4.1、数据库基本操作

4.2、存储引擎

五、MySQL列的常用类型

5.1、最常用的类型

5.2、整数类型

5.3、小数 类型

5.4、字符类型

5.5、日期类型

5.6、二进制类型

六、表的操作（DDL）

6.1、创建表

6.1.1、语法

6.1.2、例子

6.1.3、注意

6.2、删除表

6.2.1、语法

6.2.2、例子

6.2.3、注意

6.3、表的复制和批量插入

6.3.1、表的复制

6.3.2、表的批量插入

6.5、表的约束

6.5.1、主键约束

6.5.1.1、主键的分类

6.5.1.2、如何设计主键

6.5.1.3、结论

6.5.1.4、例子

6.5.2、外键约束（foreign key）

6.5.3、唯一性约束（unique）

6.6、表与表之间的关系

6.6.1、一对一

6.6.2、一对多（多对一）

6.6.3、多对多

七、DML增删改操作

7.1、插入操作

7.1.1、语法

7.1.2、例子

7.1.3、注意

7.2、修改操作

7.2.1、语法

7.2.2、练习

7.2.3、注意

7.3、删除操作

7.3.1、语法

7.3.2、练习

7.3.3、注意

八、DQL 查询操作

8.1、语法说明

5.3、小数类型