MySQL回顾

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`

模糊查询

  • betweena between b and c
  • likea 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)
    • 默认的索引,用INDEX\KEY关键字来设置
  • 全文索引 (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

数据库的三大范式

第一范式

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

第二范式

前提:满足第一范式

每张表只描述一件事情

第三范式

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

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

规范和性能

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

性能大于规范,性能为保证前提,可以不遵循规范(适当考虑)

上一篇:2021-1-14 python列表学习


下一篇:MySQL如何做如等级/成色等特殊顺序的排序