MySQL笔记

所有带 [ ] 表示可选

字段名用` `，属性用’ '

1. 操作数据库

1. 操作数据库（了解）

DDL 定义数据库

DML 操作数据库

DQL 查询数据库

DCL 控制数据库

create database [if not exist] xx；	[如果不存在]创建xx数据库

drop database [if not exist] xx；	[如果不存在]删除xx数据库

use `xx`；	切换到xx数据库

show databases；	查看所有数据库

show table；	查看数据库所有表

2. 数据类型

数值

• int 常用整数 4字节
• double 常用浮点数 8字节
• decimal 字符串形式浮点数，无精度损失，金融计算用

字符串

• varchar 可变字符串 0~65535
• text 保存大文本 2^16-1

时间日期

• date yyyy-mm-dd 日期格式
• time hh：mm：ss 时间格式
• datetime date+time 最常用时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数

3. 数据库的字段属性（重点）

unsigned：

• 无符号的整数
• 声名不能为负数

zero fill：

• 0填充
• 不足位数，用0填充

auto_increament：

• 自增（默认+1）
• 设计唯一的主键
• 必须整数
• 可自定义起始值和增量

not null：

• 必须赋值
• 可以为空值（null占空间，空值不占）
• 拓展：两个null比较结果还是null

default：

• 设置默认值
• 不设置值有默认值

comment：

• 对列进行备注

项目表必备字段：

• id：主键
• `version`：乐观锁
• is_delete：伪删除
• gmt_create：创建时间
• gmt_update：修改时间

4. 数据库表格式

create table [if not exists] `xx`(
	'字段名' 列类型 [属性][索引][注释],
    ...
    '字段名' 列类型 [属性][索引][注释]
    [主键设置]
)[表类型][字符集设置][注释]

常用语句

show create database xx -- 查看创建xx数据库的语句
show create table xx -- 查看xx数据表的定义语句
describe xx -- 显示表结构

5. 数据表的类型

innodb 默认使用

myisam 早些年使用

	myisam	innodb
事务支持	不支持	支持
数据锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，2倍

常规使用操作：

• myisam 节约空间，速度较快
• innodb 安全性高，支持事务处理，支持多表多用户操作

物理空间存在位置：data目录下，本质还是文件的存储

mysql引擎在物理文件上的区别

• innodb在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• myisam：
  • *.frm 表结构的定义文件
  • *.myd 数据文件（data）
  • *.myi 索引文件（index）

设置数据库表的字符集编码：charset=utf8

6. 修改删除表

-- 修改表名
alter table 表名1 rename as 表名2

-- 增加表的字段
alter table 表名 add 字段名 字段类型

-- 修改表的字段
alter table 表名 modify 字段名 字段类型 -- 只能修改约束
alter table 表名 change 字段名1 字段名2 字段类型 -- 重命名，还能修改类型

-- 删除表的字段
alter table 表名 drop 字段名


-- 删除表
drop table if exists 表名

2. MySQL数据管理

1. 外键（了解）

方式一：创建表的时候增加外键（麻烦）

1. 定义外键key

2. 给外键添加约束，然后引用

key `约束名`(`作为外键的列`)
constraint `约束名` foreign key (`作为外键的列`) references `引用表`(`引用表字段`)

删除有外键关系的表，必须先删除引用别人的表（从表），再删除被引用的表（主表）

方式二：创建表之后增加字段

alter table `表名`
constraint `约束名` foreign key (`作为外键的列`) references `引用表`(`引用表字段`)

以上操作都是物理外键，数据库级别外键，不建议使用

2. DML语言

数据库意义：数据存储，数据管理

dml语言：数据操作语言

• insert
• update
• delete

3. 添加

不写表字段，值会一一匹配

insert into `表名`([`字段名1`,`字段名2`...]) value ('值1','值2'...),('值3','值4'...)

4. 修改

不指定条件，会改变所有表！！！！

update `表名` set `字段名1`='修改后的值',`字段名2`='修改后的值' where 条件

=, <>(!=), >, <, >=, <=,

between…and 在某个范围内

and 和

or 或

5. 删除

不指定条件，会改删除所有表！！！！

delete from `表名` where 条件

完全清空一个数据库表，表结构索引不改变

truncate `表名`

delete和truncate：

• 相同点：都能删除数据，但不删除表结构
• 不同：
  • truncate 重新设置自增列，计数器归零
  • truncate 不会影响事务

【了解即可】delete删除问题，重启数据库，现象：

• innodb 自增列会从1开始（存在内存中，断电即失）
• myisam 继续从上一个自增量开始（存在文件中，不会丢失）

3. DQL查询数据（重点）

1. DQL

• 所有查询都用select
• 简单，复杂查询都能做
• 数据库核心语言，最重要的语句
• 使用频率最高的数据

select完整语法：

select
from
	[join] [on]	 -- 联合查询
	[where]		 -- 条件
	[group by]	 -- 按字段分组
	[having]	 -- 对分组后的数据筛选
	[order by]	 -- 排序
	[limit]		 -- 分页

2. 指定查询字段

-- 查询表全部数据
select * from 表名

-- 查询指定字段
select 字段名,字段名 from 表名

-- 起别名（表和字段都可以）
select 字段名1 as 别名1,字段名2 as 别名 
from 表名 as 别名 

-- 函数 concat（a，b）
select concat('姓名',name) as 新名字 
from 表名

去重：distinct

取出查询结果中重复的数据，只显示一条

select distinct 字段名 from 表名

select version() -- 查版本
select 100*3 -- 计算
select @@auto_inrement_increment -- 查询自增步长（变量）
select `num`+1 from 表名 -- 查询数据处理

3. where条件字句

逻辑运算符：

• and &&：逻辑与
• or ||：逻辑或
• not ！：逻辑非

模糊查询：比较运算符

• is null：如果操作符为null，结果为true
• is not null：若果操作符为not null，结果为true
• between and：在两者之间，结果为true
• like：sql匹配，匹配到为true
  • %：代表0到任意个字符
  • _：代表一个字符
  • like ‘a%z_’：匹配第一个字母为a，倒数第二个字母为z的字符，只能在like中用！
• in （…）：操作符是数组中的一个，结果为true

4. 联表查询 JOIN

3种join

• inner join：只返回两表都匹配的行
• left join：从左表返回所有值，即使右表中没有匹配
• right join：从右表中返回所有的值，即使左表中没有

on和where

• 在多表查询时，ON和where都表示筛选条件；on先执行，where后执行
• 外连接时：
  • on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录
  • where条件是在临时表生成好后，再对临时表进行过滤的条件。

语法：

select b1.字段1, b2.字段2 ...
from 表1 as b1
inner join 表2 as b2
on 条件
left join 表3 as b3
on 条件
right join 表4 as b4
on 条件

自连接：

自己的表和自己的表连接

select a.字段1, b.字段2
from 表1 as a, 表1 as b
where 条件

5. 分页和排序

排序：order by

• 升序：asc
• 降序：desc

select *
from 表
order by 字段 asc -- 升序或降序

分页：limit a, b （a：起始位置，b：条数）

第n页：limit (n-1)*pageSize, pageSize

select *
from 表
limit 0,2 -- 第一条数据开始，总共查2条

6. 子查询

本质：在where语句中嵌套一个查询语句

原本的查询：

select *
from 表1 b1
inner join 表2 b2
on 条件
where 条件
order by 字段 desc

子查询

select *
from 表1
where 字段1 = (
	select 字段1
    from 表2
    where 条件
)
order by 字段 desc

7. 分组和过滤

select *
from 表1
left join 表2
on 条件
where 条件
group by 字段	-- 按字段分组
having 条件	-- 对分组后的数据筛选
	

4. MySQL函数

1. 常用函数

-- 数学运算
select abs(-8)  	-- 绝对值	8
select ceiling(9.4) -- 向上取整	10
select floor(9.4) 	-- 向下取整	9
select rand() 		-- 返回一个0-1的随机数
select sing(10) 	-- 判断一个数的符号 -1/0/1	

-- 字符串函数
select char_length('asdsd') 	-- 字符串长度	5
select concat('i','love','u') 	-- 拼接字符串	'iloveu'
select insert('1234',2,2,'abc')	-- 替换	'1abc4'	
select lower('Abc')				-- 转小写字母	'abc'
select upper('Abc')				-- 转大写字母	'ABC'
select instr('abcd','c')		-- 返回第一次出现字符串的索引	3
select substr('abc',1,2)		-- 返回指定字符串	'ab'
select replace('1234','12','a')	-- 替换出现的指定字符串	'a34'
select reverse('123')			-- 反转	'321'

-- 时间和日期函数（重点）
select now()		-- 获取当前日期
select localtime()	-- 获取本地时间
select sysdate()	-- 系统时间
select year(now())	-- 获取年
...

-- 系统
select user()		-- 获取当前用户
select version()	-- 获取版本

2. 聚合函数（重点）

函数名称	描述
count（）	统计行数
sun（）	求和
avg（）	平均值
max（）	最大值
min（）	最小值

select count(字段) from 表	-- 会忽略所有null
select count(*) from 表 -- 不会忽略null，本质：计算行数
select count(1) from 表 -- 不会忽略null，本质：计算行数

3. 数据库级别的md5加密（扩展）

算法复杂度高

不可逆性

不防碰撞

update `user` set password=md5(password) -- 密码全部加密

-- 用户传递的密码进行md5加密，然后比对加密后的值
select * from `user` where `name`='name' and password=md5(`123`)

5. 事务

事务：将一组sql放在一个批次中去执行

ACID：

• 原子性
  • 要么都成功，要么都失败
• 一致性
  • 事务前后的数据完整性一致
• 隔离性
  • 隔离多个用户对同一数据的操作
• 持久性
  • 断电事务没有提交，恢复到原状
  • 断电事务提交，持久化数据

隔离所导致的问题：

• **脏读：**一个事务读取了另一个事务没有提交的诗句
• **不可重复读：**一个事务内读取某个数据，多次读取结果不同（不一定错误，至少是某些场合不对）
• 幻读：：一个事务内读取到了别的事务插入的数据，导致读取前后不一致

mysql是默认开启事务自动提交的

set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启

自定义事务：

set autocommit = 0 -- 关闭事务自动提交

start transaction -- 开启事务

insert 数据
update 数据
...

commit -- 提交：持久化（成功）
rollback -- 回滚：回到事务前的样子（失败）

set autocommit = 1 -- 结束后恢复默认

-- 了解
savepoint -- 设置一个事务的保存点
rollback to savepoint -- 回滚到保存点
release savepoint -- 撤销保存点

事务的隔离级别：

隔离级别	脏读	不可重复读	幻读
read-uncommitted（读取未提交）	√	√	√
reda-conmitted（读取提交）	×	√	√
repeatable-read（可重复读）	×	×	√
serializable（可串行化）	×	×	×

MySQL innodb 默认隔离级别是repeatable-read（可重复读）

6. 索引

索引是帮助MySQL高效获取数据的数据结构。

索引是数据结构

1. 索引的分类

• 主键索引（primary key）
  • 唯一的表示，主键不可重复
  • 不允许空值
• 唯一索引（unique key）
  • 该字段列数据不重复，多个列都可以标识为唯一索引
  • 允许空值
• 常规索引（key / index）
  • 默认
• 全文索引（fullkey）
  • 特定数据库引擎才有，myisam
  • 快速定位数据

基础语法

-- 1.创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
-- 3.create index 索引名 on 表(列名)

-- 显示所有的索引信息
show index from 表

-- 增加一个全文索引		（索引名）列明=名
alter table 表.列名 add fulltext index `索引名`('列名')

-- explain：分析SQL执行情况
explain select * from 表 -- 非全文索引
explain select * from 表 where match(列名) against('数据') -- 全文索引 

2. 测试索引

索引在小数据量的时候，用处不大，但在大数据的时候，用处十分明显

3. 索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的列（字段）上

索引的数据结构：

harsh类型的索引

Btree：innodb的默认数据结构

7. 权限管理和备份

1. 用户管理

用户表：mysql.user

本质：对这张表的增删改查

-- 创建用户
create user 用户名 identified by 密码
-- 修改密码（当前用户）
set password = password(新密码)
-- 修改密码（指定用户）
set password for 指定用户 = password(新密码)
-- 用户重命名
rename user 用户名 to 新用户名
-- 删除用户
drop user 用户名

-- 用户授权
-- all privileges，不能给别人授予权限
grant all privileges on 库.表 to 用户 
-- 查询指定用户权限
show grant for 用户
show grant for root@localhost
-- 撤销权限
remove all privileges on 库.表 from 用户 

2. MySQL备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移

MySQL数据库备份方式

• 直接拷贝物理文件

• 可视化工具中手动导出

• 使用命令行（不是编辑器）导出 mysqldump

  # 导出
  mysqldump -h地址 -u用户 -p密码 数据库 表1 表2 表n > 位置(D:/a.sql)
  # 导入
  source D:/a.sql # 提前登录
  mysqldump -h地址 -u用户 -p密码 数据库 < 备份文件位置(D:/a.sql) #不建议！！！防止输错导致覆盖
  

8. 规范数据库设计

1. 为什么需要设计

糟糕的数据库设计：

• 数据库冗余，浪费空间
• 数据库的插入和删除都会麻烦、异常【处理：屏蔽使用物理外键】
• 程序性能差

良好的数据库设计：

• 节省内存空间
• 保证数据的完整性
• 方便我们开发系统

关于数据库的设计：

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图 E-R 图

设计数据库的步骤：（个人博客）

• 收集信息，分析需求
  • 用户表 user（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表 category（文章分类，谁创建）
  • 文章表 blog（文章的信息）
  • 友链表 links（友链信息）
• 标识实体（把需求落到每个字段）
• 标识实体之间的关系
  • 写博客：user—>blog
  • 创建分类：user–>category
  • 关注：user–>user
  • 友链：links
  • 评论：user–>user–>blog

2. 三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效的信息

专有名词：

• 元组：二维表中的行
• 属性：二维表中的列
• 超键：能唯一标识元祖的属性集
• 候选键：不含多余属性的超建
• 主键：用户选作元组标识的候选键
• 外键：对于当前模式而言，是另一模式下的主键。
• 主属性：构成候选键的属性

三大范式：

• 1NF
  • 原子性：保证每一列不可再分
• 2NF
  • 满足1NF
  • 非主属性完全依赖于候选键
• 3NF
  • 满足2NF
  • 消除非主属性对候选键的传递依赖
• BCNF
  • 满足3NF
  • 消除每一属性对候选键的传递依赖

**部分依赖：**C可以通过AB得到，并且C也可以仅通过A得到，仅通过B得到， 那么就说C部分依赖AB。

**完全依赖：**C可以通过AB得到，并且C不可以仅通过A得到，也不可以仅通过B得到， 那么就说C完全依赖AB。

**传递依赖：**B可以通过A得到，C可以通过B得到，那么就称C传递依赖A。

规范和性能问题

关联查询的表不得超过三张表（阿里规范）

• 考虑商业化的需求和目标（成本，用户体验），数据库性能更加重要
• 在提高性能的时候，需要适当的考虑下规范性
• 故意给某些表增加一些冗余手段。（多表查询变为单表查询）
• 故意增加一些计算列（从大数据量降低为小数据量的查询）