MySQL

MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

操作数据库

MySQL不区分大小写

创建数据库

create database [if not] exists westos

删除数据库

drop database if exists westos

使用数据库

-- 如果表名或是字段名为特殊字符，就需要带``
use `school`

查看数据库

show database   -- 查看所有的数据库

数据库的列类型

数值类型

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等较大的数据 3个字节
int 标准的整数 4个字节
bigint 较大的整数 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数金融计算一般使用decimal

字符串

char 字符串（固定大小） 0-255
varchar 可变字符串 0-65535
tinytext 微型文本 2^8-1
text 文本串 2^16-1

时间日期

date YYYY-MM-DD 日期格式
time HH:mm:ss 时间格式
datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 1970.1.1到现在的毫秒数时间戳
year 年份表示

数据库的字段属性

Unsigned:

无符号整数
声明了该列不能为负数

zearfill:

0填充的
不足的位数使用0来填充

自增:

通常理解为自增，自动在上一条的记录上+1（默认）
通常用来设计唯一主键，必须为整数类型
可以自定义设计主键自增的起始值和步长

非空:

假设设置为 not null，插入数据为空时报错

默认:

设置默认值

操作表

创建数据库表

目标：创建学生表（列，字段）

学号、姓名、密码、性别、出生日期、家庭住址、email

CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ......
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]

常用命令：

show create database school -- 查看创建数据库的语句
show create table student -- 查看建表语句
desc student -- 显示表结构

MyISAM 与 InnoDB 的区别

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为MYISAM两倍

常规使用操作：

MYISAM 节约空间，速度较快
INNODB 安全性高，支持事务，多表多用户操作

在物理空间存在的位置：

所有的数据库文件都存在data目录下，一个文件夹对应一个数据库。

本质还是文件存储。

MySQL引擎在物理文件上的区别：

InnoDB 在数据库表中只有一个 *.frm 文件，以及上级目录下的 ibdata1 文件
MYISAM 对应文件
- *.frm - 表结构的定义文件
- *.MYD - 数据文件（data）
- *.MYI - 索引文件（index）

设置数据库表的字符集编码

CHARSET=utf8

修改与删除表

修改

-- 修改表名
ALTER TABLE `旧表名` RENAME AS `新表名`
-- 增加字段
ALTER TABLE `表名` ADD [字段名] [列属性]
-- 修改表的字段
ALTER TABLE `表名` MODIFY [字段名] [列属性] -- 修改约束
ALTER TABLE `表名` CHANGE [旧字段名] [新字段名] [列属性] -- 字段重命名
-- 删除字段
ALTER TABLE `表名` DROP [字段名]
-- 删除表
DROP TABLE [IF EXISTS] `表名`

MySQL数据管理

外键（约束）

方式一在建表时增加约束

CREATE TABLE `GRADE`(
    `gradeid` int(10) not null auto_increment comment '年级id',
    `gradename` varchar(50) not null comment '年级名称',
    PRIMARY key(`gradeid`)
)ENGINE=INNODB DEFAULT charset=utf8

CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二建表后添加外键关系

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

以上操作都是数据库级别的外键，不建议使用（避免数据库过多造成困扰）

DML语言

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

DML语言：数据操作语言

insert
update
delete

添加

-- 插入语句
insert into 表名(字段1,字段2,...,字段n) values(值1,值2,...,值n)

修改

-- 修改语句
update 表名 set colum_name1 = value,colum_name2 = value... where ...

删除

-- 删除数据
delete from 表名 where ...

-- TRUNCATE命令 作用：完全清空一个数据库表，表的结构和索引约束不会变
TRUNCATE 表名

delete 和 truncate 区别

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

了解：DELETE删除的问题，重启数据库

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

DQL语言（数据查询语言）

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1 [as alias1][,table2.field2 [as alias2][,...]]]}
FROM table_name [as table_alias]
    [ledf | right | inner join table_name2] -- 连接查询
    [WHERE ...] -- 指定结果满足的条件
    [GROUP BY ...] -- 指定结果按照字段分组
    [HAVING ...] -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}]

去重

-- 使用distinct关键字
select distinct [字段名] from 表名

where子句

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or \|\|	a \|\| b a\|\|b	逻辑或，其中一个为真，结果为真
not !	not a !a	逻辑非，真为假，假为真
between ... and	a between b and c	查询大于区间内的数据
is null	a is null	如果操作为null，结果为真
like	a like b	SQL匹配，如果a匹配b，结果为真
in	a in (a1,a2,a3...)	如果在集合中，结果为真

-- 模糊查询
-- like 结合%(代表任意个字符) _(一个字符)
select [字段名] from 表名 where [字段名] like ...

连表查询 join on

image

自链接：将一张表拆分为两张一样的表进行连接查询。

排序（Order by）

-- ASC 升序， DESC 降序
ORDER BY [字段名] [ASC | DESC]

分页（Limit）

-- Limit 当前值,页面大小
-- 常用公式 Limit (n-1)*pageSize,pageSize
LIMIT 0,5 -- 查询第1-5条数据

子查询

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

函数

普通函数

-- 数学运算
ABS(-8) -- 绝对值
CEILING(9.4) -- 向上取整
FLOOR(9.4) -- 向下取整
RAND() -- 0-1中间的随机数
SIGN(-10) -- 返回符号
-- 字符串
CHAR_LENGTH('abcde') -- 字符串长度
CONCAT('123','123') -- 拼接字符串
INSERT('HELLO',1,2,"12") -- 从某个位置替换某个长度
LOWER('ABC') -- 转小写
UPPER('abc') -- 转大写
INSTR('ABCDE','C') -- 某个字符在字符串中首次出现的位置
SUBSTR('ABCDE',2,3) -- 从字符串某个位置截取n个字符
REVERSE('ABCDE') -- 反转字符串
-- 日期和时间
CURRENT_DATE() -- 获取当前日期
CURDATE() -- 获取当前日期
NOW() -- 获取当前时间
LOCALTIME() -- 获取本地时间
SYSDATE() -- 获取系统时间
-- 系统
SYSTEM_USER()
USER()
VERSION

聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
...	...

注意：

SELECT COUNT(`字段名`) -- 会忽略null值
SELECT COUNT(*) -- 不忽略NULL,查询所有列
SELECT COUNT(1) -- 不忽略NULL,只查询第一页

数据库级别的MD5加密

-- 插入时加密
INSERT INTO [表名](password) value(MD5('123456'))
-- 校验：将用户传入的密码进行加密后比对

事务

什么是事务

要么都成功，要么都失败

参考链接 ↓

事务的ACID原则：原子性、一致性、隔离性、持久性

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）事务前后数据的完整性必须保持一致。
隔离性（Isolation）事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务的隔离级别

脏读：

指一个事务读取了另外一个事务未提交的数据。
不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）

索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

主键索引只能存在一个，唯一索引可以有多个

主键索引（PRIMARY KEY）

唯一的标识，主键不可重复，只能有一个列作为主键。
唯一索引（UNIQUE KEY）

避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引。
常规索引（KEY/INDEX）

默认的，使用index或key关键字来设定。
全文索引（FullText）

在特定的数据库引擎下存在（MyISAM），用于快速定位数据。

索引的使用###

-- 1.在创建表时给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM [table_name]
-- 增加一个全文索引
ALTER TABLE [table_name] ADD FULLTEXT INDEX [索引名]([字段名])
-- EXPLAIN 分析SQL执行状况
EXPLAIN SELECT * FROM [table_name]; -- 非全文索引
EXPLAIN SELECT * FROM [table_name] WHERE MATCH([索引名]) AGAINST([匹配值])
-- 创建索引 id_表名_字段名
CREATE INDEX id_表名_字段名 ON table_name([列名])

索引原则

索引不是越多越好
不要对经常改变的数据添加索引
小数据量的表不需要添加索引
索引一般添加在常用于查询的字段上

用户管理权限

用户管理权限###

-- 创建用户
CREATE USER [user_name] IDENTIFIED BY [password]

-- 修改密码（当前用户）
SET PASSWORD = PASSWORD([password])

-- 修改密码（指定用户）
SET PASSWORD FOR [user_name] = PASSWORD([password])

-- 重命名
RENAME USER [user_name] TO [new_user_name]

-- 用户授权 ALL PRIVILEGES 全部权限，除了Grant授权权限
-- 查询权限
GRANT ALL PRIVILEGES ON [库名].[表名] TO [user_name]

-- 查询权限
SHOW GRANTS FOR [user_name] -- 查看指定用户
SHOW GRANTS FOR root@localhost -- 查询管理员权限

-- 撤销权限
REVOKE ALL PRIVILEGES ON [库名].[表名] FROM [user_name]

-- 删除用户
DROP USER [user_name]

数据库设计

三大范式

第一范式（1NF）

要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范和性能问题

关联查询的表不得超过三张表

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