MySQL

1、前言

1.1、什么是数据库？

• Database，简称DB
• 按照一定格式存储一些数据的组合

1.2、数据库管理系统？

• DatabaseManagements，简称DBMS
• 顾名思义，是用来管理数据库的
• 可以对数据库进行增删改查之类的操作
• 常见DBMS
  • mysql
  • oracle
  • sql server
  • DB2
  • sybase等

1.3、SQL

简介

• 结构化查询语言
• DBMS通过执行SQL，对DB进行一些操作
• 它是一套标准，可以运行于多种数据库

种类

1. DQL：数据查询语言（select）（操作数据）
2. DML：数据操作语言（insert，delete，update）（操作数据）
3. DCL：数据控制语言（grant，revoke）（操作权限）
4. DDL：数据定义语言（drop，create，alter）（操作表结构）
5. TCL：事务控制语言（commit，rollback）（操作事务）

1.4、MySQL的安装与卸载

1.5、MySQL的服务

2、MySQL基本使用

2.1、数据库

创建和销毁

创建

CREATE DATABASE  IF NOT EXISTS `数据库名` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

删除

DELETE DATABASE IF EXISTS `数据库名`；

2.2、数据类型

数值型

整数型

int

• 整数型，相当于Java中的int

bigint

• 整数型，相当于Java中的long

浮点型

float

• 单精度浮点型

double

• 双精度浮点型

字符型

varchar

• 可变长字符串
• 会根据实际传入字符串的长度动态分配空间
• 优点：节省空间
• 缺点：需要动态分配空间，速度慢

char

• 定长字符串
• 会根据建表时设置的长度分配空间
• 优点：不需要动态分配空间，速度快
• 缺点：使用不当会造成空间的浪费

clob

• Character Large OBject
• 字符大对象
• 最多存储4GB
• 适合存储文章，说明之类
• 超过255字符的数据可采用此类型

进制型

blob

• Binary Large OBject
• 二进制大对象
• 适合存储图片，音频，视频等流媒体数据

日期型

data

1. 短日期类型
2. 年月日

datatime

1. 长日期类型
2. 年月日时分秒
3. now() 返回当前时间，datetime类型

2.3、表

简介

• 数据库中最基本的单元；

• 用表来展示数据较为直观

• 每张表都由行和列

  • 行：row，记录
  • 列：column：字段

  • 性别	年龄
    男	18
    男	18
    男	18

• 每个字段都有字段名，字段类型，约束等属性

查看表结构

desc（describe缩写） 表名；

创建和销毁

创建

CREATE TABLE IF NOT EXISTS `表名`(
	字段1 数据类型 [PRIMARY KEY],
	...
	字段n 数据类型 [DEFAULT 默认值]
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

删除

DELETE TABLE IF EXISTS `表名`;

复制（了解）

CREATE TABLE 表名1 AS SELECT 字段1, ..., 字段n FROM 表名2；
# 原理
将查询到的 数据与结构 一起构建新表

2.4、常用命令

命令	解释
select version()	查看mysql版本
select database()	查看当前使用数据库
show databases	查看所有数据库
show tables	查看所有表
show engines \g	查看所有支持引擎

2.5、字符集

注意：如果不是通过my.ini配置文件设置的内容，只在当前状态下有效，当重启数据库服务后失效。所以如果想要不出现乱码只有修改my.ini文件，数据库编码可以在创建数据库时候指定UTF8，如下：

|character_set_client      | utf8                                  |
| character_set_connection | utf8                                  |
| character_set_database   | utf8                                  |
| character_set_filesystem | binary                                |
| character_set_results    | utf8                                  |
| character_set_server     | latin1                                |
| character_set_system     | utf8          

注：
该配置character_set_server无法设置成UTF8
交互时候仍然会出现乱码。

通过命令设置编码：

SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_database = utf8;
SET character_set_results = utf8;
SET character_set_server = utf8;
SET collation_connection = utf8_bin;
SET collation_database = utf8_bin;
SET collation_server = utf8_bin;

my.ini中配置默认编码

default-character-set=utf8

连接数据库设置编码

jdbc:mysql://192.168.18.1:3306/test?characterEncoding=utf8

常用编码：

Java	MySQL
UTF-8	utf8
GBK	gbk
GB2312	gb2312
ISO-8859-1	latin1

3、增删改查

• 增删改查，作用于数据

3.1、SELECT

语法

SELECT 字段1, ..., 字段n FROM 表名, ..., 表名
	[WHERE 条件1 [AND 条件2] ]

3.2、INSERT

语法：

# 插入单条数据
INSERT INTO 表名(字段1, ..., 字段n) VALUES(值1, ..., 值n);
# 插入多条数据
INSERT INTO 表名(字段1, ..., 字段n) VALUES(值1, ..., 值n), ..., (值1, ..., 值n);
# 与select连用 (少用)
INSERT INTO 表名1(字段1, ..., 字段n) SELECT 字段1, ..., 字段n FROM 表名2

3.3、DELETE

语法：

DELETE FROM 表名 
	[WHERE 条件1[ AND 条件2]] 

优点：

可以回滚

缺点：

按照字段逐个删除，且空间不释放，效率较低

TRUNCATE
属于DDL操作
语法：

TRUNCATE TABLE 表名;

优点：

截断表，物理删除，效率高

缺点：

无法恢复

3.4、UPDATE

语法：

UPDATE 表名 
	SET 字段1 = 值, ..., 字段n = 值 
	[WHERE 条件1 [ AND 条件2]]

3.5、ALTER（待补充）

• 操作表结构，DDL
• 修改表结构成本较高
• 这个责任应该由设计人员来承担

4、MySQL进阶语法

4.1、ORDER BY

• 排序语法
• 升序asc（默认），降序desc
• 若排序遇到字段值相同，可使用多个字段

案例：单个字段

select * from 表名 order by 字段 desc；

案例：多个字段

# 当字段值相同，再根据第二个字段排序
select * from 表名1 order by 字段1 desc，字段2 asc;

可以根据字段位置排序（了解）

# 根据第二列排序
select 字段1, 字段2 from 表名 order by 2；
# 不推荐，因为不健壮
# 因为列的顺序和数量很容易发生变化

4.2、数据处理函数

• 又被称为单行处理函数
• 一个输入对应一个输出，记录之间没有关系
• 比如sum求和是个多行处理函数，输入可以任意个，输出是一个

函数名	作用	案例	输出
lower(字符串)	转小写	select lower(’'XXX) from 表名	xxx
upper(字符串)	转大写	select upper(‘xxx’) from 表名	XXX
substr(起始位置, 截取长度)	截取子串	select substr(‘abcd’, 1, 1) from 表名	a
concat(串1, 串2)	字符串拼接	select concat(‘x’, ‘x’) from 表名	xx
length(参数)	取得长度	select length(‘abc’) from 表名	3
l/r/trim(串)	去空格，trim去两边，l/r 去 左/右	select trim(’ x ') from 表名	x
str_to_date(字符串, 自定义格式)	转字符串为日期	select str_to_date(‘1999-04-07 1:00:00’, ‘%Y-%m-%d %h:%i:%s’)	1999-04-07 01:00:00
data_format(日期, 自定义格式)	转日期为具有一定格式的字符串	select date_format(date, ‘%Y/%m/%d’)	年/月/日
round(数)	四舍五入，参数1为字段，2为保留的小数位	select round(4.5, 0) from 表名	5
rand()	生成[0, 1)范围的随机数	select rand()	0.9057697559760601
ifnull(值, 值)	可将null转为具体值	select ifnull(null, 0) from 表名	0
format(值，自定义格式)	格式化	select format(123456, ￥999, 999) from 表名	123,456

4.3、分组函数

• 多行处理函数
• 输入多行，返回单行
• 一般先分组再使用
• 若不分组，则计算所有
• 自动忽略null
• 不能直接使用在where中

函数名	作用	测试	输出
max	求得最大值	select max(字段) from 表名	最大值
min	求得最小值	select min(字段) from 表名	最小值
avg	求得平均值	select avg(字段) from 表名	平均值
count	求得总数量	select count(字段) from 表名	总数量
sum	求得总值	select sum(字段) from 表名	总值

count(*)和count(字段)的区别？

• 前者返回所有记录的数量
• 后者返回该字段不为null记录的数量

4.4、GROUP BY

分组

• 分组函数必须分组才能使用
• 若不分组默认一组
  思考：为什么这条会报错？

select * from 表名 where 字段1 > min(字段1); 

语句执行顺序：
from > where > group by > select > order by

因为where执行在分组之前，因此分组函数失败！

4.5、DISTINCT

去除重复记录
错误示范：

select ename,  distinct job from emp
#error：ename与去重之后的job记录数量不匹配

4.6、LIMIT

可以控制要显示数据的起始下标和数量，多用于分页

1. 一个参数

# 取前五条数据
select * from 表 limit 5;

2. 两个参数

# 取下标1开始的五条数据
select * from 表 limit 1, 5;

5、连接查询

• 一张表中取数据称为单表查询
• 多张表中取数据称为连接查询

连接分为：

• 内连接
  1. 等值连接
  2. 非等值连接
  3. 自连接
• 外连接
  1. 左（外）连接
  2. 右（外）连接

5.1、内连接：等值连接

条件是等量，因此被称为等值连接
SQL92语法：

select * from 表1, 表2 where 表1.字段1 = 表2.字段1

缺点：连接条件和进一步筛选条件放到了一起，结构不清晰。
解决方案：SQL99语法

select * from 表1
	inner（可省略） join 表2
		on 表1.字段1 = 表2.字段1
	where 条件...

5.2、内连接：非等值连接

条件是一个范围，不是等量关系，称为非等值连接

select * from 表1
	inner（可省略） join 表2
		on 表1.字段1 between 表2.字段2 and 表2.字段3
	where 条件...

5.3、内连接：自连接

自连接，自己连接自己
假如一张员工表emp有两个字段，no工号，name姓名和mgr对应领导工号，mgr对应的是，比如

no	name	mgr
1	员工1	2
2	员工2	3
3	员工3	null

求出员工姓名和对应领导姓名

select a.name '员工姓名', b.name '领导姓名' from emp a
		join emp b
		on a.mgr = b.no

5.4、外连接：左（右）外连接

以左（右）边的表为主表，只要有数据就显示，不管右（左）边是否为null

select * from 表1
	left（right） outer（可省略）join 表2
		on 表1.字段1 = 表2.字段1 

5.5、子查询

select中嵌套select，被嵌套的称为子查询，比如：

#子查询可能出现的位置
select
	(select...)
from 
	(select...)
where
	(select...)

5.5、UNION

• union要比表连接高一些
• 表连接每连接一次新表则匹配的次数满足笛卡尔积，成倍的翻
• 但是union能减少匹配的次数，还能完成两个结果集的拼接
• union的两张表字段必须匹配，包括数量和类型
  语法：

select * from 表1
union
select * from 表2

6、约束

建表时可以给一些字段添加约束，增加数据的完整性，有效性
约束分为：
主键约束：primary key
外键约束：foreign key
非空约束：not null
唯一约束：unique
检查约束：check（mysql不支持，oracle可以）

6.1、PRIMARY KEY

• 主键约束
• 一张表只能有一个
• 被修饰字段不能重复
• 唯一 + 非空 = 主键
• 相当于人的身份证

单字段主键：建表时语法：

字段名 数据类型 PRIMARY KEY 

多字段（复合）主键（不建议）：建表时语法：

PRIMARY KEY(字段1, ..., 字段n)```

6.2、NOT NULL

• 非空约束
• 被修饰字段不能为NULL
• 建表时语法：

字段名 数据类型 NOT NULL 

• 主键类型通常为int、bigint、char
• 不建议使用varchar
• 主键一般为定长
  注
  主键除了单一主键和复合主键，还分为自然主键和业务主键
  **自然主键：**主键值是一个自然数，跟业务没关系；
  **业务主键：**主键值和业务紧密相连，例如银行卡号；

6.3、UNIQUE

• 唯一约束
• 正如其名，被修饰字段不能重复
• 但可为NULL

单字段唯一：建表时语法

字段名 数据类型 UNIQUE

多字段唯一：建表时语法：

UNIQUE(字段1, ..., 字段n)

6.4、FOREIGN KEY

• 外键约束
• 表与表之间建立联系

语法：

FOREIGN KEY(字段1) REFERENCES 表名(字段)

7、存储引擎

• MySQL特有的术语
• 是一个表存储/组织数据的方式

查看MySQL支持的引擎

SHOW ENGINES \G

7.1、MyISAM

将一张表分成三份进行存储

1. 格式文件 - 存储表结构的定义（mytable.frm）
2. 数据文件 - 存储表行的内容（mytable.MYD）
3. 索引文件 - 存储表上的索引（mytable.MYI）

**优点：**可被转化为压缩，只读表来节省空间
**缺点：**不支持事务，不安全

7.2、InnoDB

• MySQL默认存储引擎
• 支持事务，以保证数据安全，所以效率不高
• 不支持压缩，只读表
• 支持数据库崩溃后的自动回复
• 表在数据库目录中以.frm格式表示
• 数据和索引被存放在表空间 tablespace
• 提供一组用来记录事务性活动的日志文件
• 多版本MVCC和行级锁定
• 支持外键及引用的完整性，包括级联删除和更新

**优点：**支持事务，安全
**缺点：**慢，不能被压缩，没有只读表

7.3、MEMORY

• 数据和索引被存在内存中
• 表在数据库目录中以.frm格式表示
• 表级锁机制
• 没有TEXT和BLOB字段
• 之前被称为堆引擎

**优点：**快
**缺点：**不安全，数据断电即失

8、事务

8.1、什么是事务？

• 一个事务就是一个完整的业务逻辑。

比如A向B转账10000
A扣10000
B加10000

• 以上是最小的单元，同成共败，不可再分。
• 只有操作数据的DML具有事务
• 当开启了事务，所有DML操作会被记录到事务性活动日志文件中
• 提交：COMMIT，清空日志，将数据持久化，标志着事务成功的结束；
• 回滚：ROLLBACK，撤销操作，清空日志，标志着事务失败的结束；
• MySQL默认当执行一条DML语句会自动提交

8.2、特性（ACID）

原子性

• Atomicity
• 事务是最小的单元，全部成功或全部失败，没有第二种可能

一致性

• Consistency
• 在事务前后，数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性

• Isolation
• 数据库允许多个事务同时对其数据进行读写，隔离性可以防止由于交叉执行而导致的数据不一致。

持久性

• Durability
• 事务结束后，对数据的修改是永久的，即便系统故障也不会丢失。

8.3、隔离级别

读未提交（最低）

• READ UNCOMMITTED
• 未提交的事务可以被其他事务读到
• 存在脏读现象

脏读： 读到的数据不是最终数据，可能会被修改或回滚，没有意义。

读已提交

• READ COMMITTED
• 只能读取其他事务已提交的数据
• 解决了脏读
• 存在不可重复读问题

不可重复读： 事务开启过程中，读取到的数据随时可能被其他事务修改，事务期间读取数据不一致称为不可重复读

可重复读

• REPEATABLE READ
• 事务开启之后，读取到的数据一致，不会因为被其他事务而改变
• 解决了不可重复读
• 存在幻读现象

幻读： 读到的数据是幻象，不够真实。

比如昨天开启事务，a为100，到了第二天结束事务，在此过程中a被改变了n次，但此事务中a还是100。

串行化（最高）

• SERIALIZABLE
• 最高级别，解决了上述所有问题
• 最低效率
• 给被操作的数据加锁，阻止并发

9、索引

9.1、简介

1. 索引就是事先排序，分块存储，结构是二叉树，查找时可以应用二分找到其所在块，相当于一本书的目录，缩小扫描范围

2. 加于字段

3. 主键和唯一约束自动添加索引

4. 不管是否有索引，首先会在SGA的数据缓冲区中查找所需要的数据，没有时服务器进程才去读磁盘。

   无索引，直接去读表数据存放的磁盘块，读到数据缓冲区中再查找需要的数据。

   有索引，先读入索引表，通过索引表直接找到所需数据的物理地址，并把数据读入数据缓冲区中。

什么时候适合用索引呢？

• 数据量庞大
• 经常被作为筛选条件的字段
• 很少被DML修改

优点： 提高了查询速度
缺点：

• 索引底层是用B-Tree来存储的，是一颗平衡二叉树，如果我们对这棵树增删改的话，那肯定会破坏它的原有结构。
• 要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度
• 任何一张表的任何一张记录在硬盘上都有一个物理存储编号

9.2、索引分类

1. 单一索引：一个字段添加索引
2. 复合索引：多个字段联合索引
3. 主键索引：主键上添加索引
4. 唯一索引：UNIQUE约束字段添加索引

注意： 唯一性较弱的字段添加索引意义不大

9.3、索引的使用

创建语法

CREATE INDEX 索引名 ON 表名(字段名)

删除语法

DROP INDEX 索引名 ON 表名

查看语句是否使用了索引？

EXPLAIN + SQL语句
# type为ref表示使用了索引

9.4 索引失效

1. 当使用模糊查询以%开头，索引失效
2. 当使用or时，两边字段都有索引才会生效
3. 当使用复合索引当作条件，若没有使用左侧字段则失效
4. where条件中索引列参加了运算，失效
5. where条件中索引列使用了函数

10、视图

10.1、什么是视图？

将查询结果保存起来，以不同的角度看待数据

10.2、视图的使用

创建语法：

CREATE VIEW 视图名 AS SELECT 字段... FROM 表名

只有DQL语句才能以视图形式创建

删除语法：

DROP VIEW 视图名

更新视图会导致原表发生变动

11、DBA常用命令

11.1、用户创建

CREATE USER 用户名 IDENTIFIED BY 用户密码

11.2、授权

CREATE USER 用户名 IDENTIFIED BY 用户密码

11.3、导入导出

导出

# 导出数据库
mysqldump 数据库名>绝对路径 -r用户名 -p密码

mysqldump 数据库名 表名>绝对路径 -r用户名 -p密码

导入

1. 新建一个数据库；
2. 进入数据库；
3. 执行source 路径```sql

CREATE DATABASE  IF NOT EXISTS `数据库名` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE 数据库名;
# 进入命令提示符页面
source 路径

12、三大范式（空）

1. 第一范式(确保每列保持原子性)
2. 第二范式(确保表中的每列都和主键相关)
3. 第三范式(确保每列都和主键列直接相关,而不是间接相关)

参考：https://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html 博客园博主：Ruthless

13、存储过程

13.1、简介

SQL 语句需要先编译后执行。
而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
创建的存储过程保存在数据库的数据字典中。

13.2、 优点

（1）增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
（2）标准组件式编程·：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。
（3）较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被执行多次，那么存储过程要比批处理的执行速度快很多。这是因为存储过程是预编译的。
（4）减少网络流量：针对同一个数据库对象（如查询、修改），如果这一操作所设计的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
（5）作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应数据访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

13.3、 基本语法

CREATE PROCEDURE 过程名（[ [IN | OUT | INOUT] 参数名 数据类型[,[IN | OUT | INOUT] 参数名 数据类型...]]） 
	[特性...] 过程体

存储过程根据需要可能会有输入、输出、输入输出参数，类型分别为：IN，OUT，INOUT： 如果有多个参数用“，”分隔开。

MySQL存储过程的参数类型在存储过程定义时指定

特性：

1. CONTAINS SQL 表示子程序包含SQL语句，但不包含读写数据的语句（默认）
2. NO SQL 表示子程序不包含SQL语句。
3. REDS SQL DATA 表示子程序包含读数据的语句。
4. MODIFIES SQL DATA 表示子程序包含写数据的语句。

过程体用BEGIN、END用来标识
例：

DELIMITER //
	CREATE PROCEDURE myproc(OUT s INT)
		BEGIN
			SELECT COUNT(*) INTO S FROM students;
		END
	//
DELIMITER ;

DELIMITER 分隔符
MySQL默认以“；”为分隔符，所以需事先用”DELITITER //“声明当前段分隔符，让编译器把两个”//“之间的内容当作存储过程的代码，不会执行这些代码；换句话说将语句的结束符号从分号；临时改为//（可自定义）

默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀；比如 select host, user from mysql.user; 其中mysql.user 为数据库名加表名

13.4、 存储过程实例

• 编写addresult存储过程，计算n+…+m的结果

	DELIMITER //
		CREATE PROCEDURE addresult1(IN n INT, IN m INT, OUT sum INT)
			BEGIN
				SET sum = 0;
				WHILE(n <= m) DO
					SET sum = sum + n;
					SET n = n + 1;
				END WHILE;
			END
		//
	DELIMITER ;
	//调用addresult1
	SET @sum = 0;
	CALL addresult1(n, m, @sum);
	SELECT @sum;

全局变量需要以@开头
用set @变量名 = value 赋值
用select @变量名显示值

• 创建存储过程sp_student_info, 用于显示输入学号的学生基本信息（包括学号、姓名、性别和系）

	DELIMITER //
		CREATE PROCEDURE sp_student_info(IN s_sno char(9))
			BEGIN
				SELECT SNO, SNAME, SSEX, SDEPT FROM STUDENT WHERE SNO = S_SNO;
			END
		//
	DELIMITER ;
	//调用sp_student_info
	CALL sp_student_info('201215122');

13.5、 查看存储过程

	show procedure status[\g | \G];

\g : 作用通；
\G：作用把显示结果旋转90°

例：

	show procedure status like "%sp%" \G;
	// 获取数据库所有名称包含sp的存储过程信息

查看存储过程或函数的创建代码

show create procedure <sp_name>;

13.6、 删除存储过程

	DROP PROCEDURE [IF EXISTS] SP_NAME

IF EXISTS 子句时MYSQL的一个扩展
如：

	DROP PROCEDURE IF EXISTS SP_AGE_PLUS;