MySQL回顾

用orm写多了数据库操作会弱化你的sql能力

回顾一下mysql的知识

外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

准备工作

创建年纪表

CREATE TABLE `grade` (
				`id` INT(10) NOT NULL AUTO_INCREMENT,
				`name` VARCHAR(20) NOT NULL,
				PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET="utf8mb4"

在创建数据库的时候关联

CREATE TABLE `student` (
		`id` INT(10) NOT NULL AUTO_INCREMENT,
		`name` VARCHAR(20) NOT NULL,
		`grade_id` INT(10) NOT NULL,
		PRIMARY KEY(`id`),
		KEY `FK_grade_id` (`grade_id`),
		CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`id`)
) ENGINE=INNODB CHARSET="utf8mb4"

删除外键关系表的时候，先要删除从表的，在删除被引用的表

表创建后在关联

CREATE TABLE `student` (
		`id` INT(10) NOT NULL AUTO_INCREMENT,
		`name` VARCHAR(20) NOT NULL,
		`grade_id` INT(10) NOT NULL,
		PRIMARY KEY(`id`)
) ENGINE=INNODB CHARSET="utf8mb4"

ALTER TABLE `student` ADD CONSTRAINT `FK_grade_id` FOREIGN KEY(`grade_id`) REFERENCES	`grade`(`id`)

最好把数据表当作单纯的表来操作，只有行和列

外键是个好东西，不使用物理外键，我们也可以约定逻辑外键（不在数据库声明FK，在程序实现上表达关联）

阿里的JAVA规范里面强制要求不得使用外键和级联，一切外键的概念在应用曾解决

TRUNCATE和DELETE的区别

相同点

都是删除数据表的操作，且都不会改变表的结构

不同点

truncate：
- 重新设置自增列，主键锁释放，计数器归0
- 不会影响事务
delete：
- 在innodb下，当delete删除数据表的数据，当数据库重启后，自增列的计数会自动从当前最后的一行的自增列开始，如果当前数据表为空，则从1开始。如果没有重启，则会从删除前的状态开始。（存在内存）
- 在myisam下，自增列始终从上次的位置继续。（存在磁盘）

查询

去重

去除重复的数据，只取出一条

SELECT DISTINCT `id` from `myTable`

模糊查询

between： a between b and c
like：a like b
- _：代表任意字符（一个位置）
- %：代表任意字符
in：在某一范围内

连表查询

操作	结果
INNER JOIN	如果表中至少有一个匹配，就返回行
LEFT JOIN	会从左表中返回，即使右表中没有匹配
RIGHT JOIN	会从右表中返回，即使左表中没有匹配

SELECT a.name, b.age, c.sex FROM tableA as a  RIGHT JOIN tableB as b ON a.id = b.id INNER JOIN tableC as c ON b.c_id = c.id

自连接

自己和自己连接，一张表拆为2张一样的表即可

id	p_id	name
1	2	test1
2		test2

像在后台管理的菜单表中，一般菜单都是有层级关系的，可以让菜单自己关联自己

SELECT a.name as `父菜单`, b.name AS `子菜单` FROM tableA as a, tableB as b WHERE a.id = b.p_id

嵌套查询

select 语句里面可以嵌套select

SELECT * FROM tableA AS a WHERE a.pid = (
	SELECT `id` FROM tableB AS b WHERE b.name = "abc" LIMIT 1
)

函数

常用函数

abs：绝对值

ceiling：向上取整

floor：向下取整

rand：0-1的随机数

sign：判断一个数的符号（0返回0，负数返回-1，正数返回1）

char_length：返回字符串长度

concat：合并字符串长度

upper：字符串大写

lower：字符串小写

replace：字符串替换

......

聚合函数

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

count：

SELECT COUNT(`id`) FROM tableA
SELECT COUNT(*) FROM tableA
SELECT COUNT(1) FROM tableA

当count指定字段的时候，会忽略该字段为null的值
count(*)和count(1)不会忽略null的值

实例

SELECT name, AVG(result) AS 平均分, MAX(result) AS 最高分, MIN(result) AS 最低分 FROM tableResult GROUP BY name

索引

索引是帮助mysql高效获取数据的数据结构，提取句子主干，就可以得到索引的本质，索引是数据结构

分类

主键索引（primary key）
- 唯一标识，主键不可重复，只能有一个列作为索引
唯一索引（unique）
- 避免重复的列出现，唯一索引可以重复，多个列可以标识为唯一索引
常规索引（KEY/INDEX）
- 默认的索引，用INDEXKEY关键字来设置
全文索引 (FULLTEXT)
- 在特定的数据库引擎下才有
- 快速定位数据位置

索引的使用

1、在创建表的时候给字段增加索引

2、创建完毕后增加索引

显示所有的索引信息
- show index from tbl
增加一个索引
- ALTER TABLE tbl ADD FULLTEXT INDEX indexname(colName)
  
  增加全文索引

在使用索引查数据的时候，小数据查询速度的感差不大，测试100万条数据的时候，不使用索引查询的话要花1s，使用索引花费0.1s。

使用索引在高并发的情况下能够减少数据的阻塞操作，能够让数据库接受更多的查询请求。

3、索引原则

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

数据备份

数据导出

1、借助sql的管理工具

2、使用命令行mysqldump

mysqldump -hhost -uuser -ppassword database table1 table2 ... >save-path

数据导入

source sqlpath

数据库的三大范式

第一范式

原子性：保证每一列不可再分

第二范式

前提：满足第一范式

每张表只描述一件事情

第三范式

前提：满足第一范式和第二范式

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

规范和性能

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

性能大于规范，性能为保证前提，可以不遵循规范（适当考虑）