MySQL数据管理
外键
如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。由此可见,外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表,具有此外键的表被称为主表的从表。外键又称作外关键字。
年级ID在年级表中是主键,在学生表中是外键。如果不使用外键,学生表的grade字段插入了一个值,但是这个值在年级表中并没有,这个时候,数据库允许插入,并不会对插入的数据做关系检查。然而在设置外键的情况下,插入学生表grade字段的值必须要求在年级表的ID字段能找到。 同时,如果要删除年级表的某个ID字段值,必须保证学生表中没有引用该字段值的列,否则就没法删除。这就是所谓的保持数据的一致性和完整性。
1 | CREATE TABLE IF NOT EXISTS `grade`( |
不能直接删除grade表,会报错:“Cannot drop table ‘grade’ referenced by a foreign key constraint ‘FK_grade’ on table ‘student’.”
不建议使用这样的物理外键!否则数据库内部表的关系混乱。
DML
-
INSERT
1
2
3
4
5
6/*
不同字段在同一括号中
多行数据用多个括号隔开
*/
INSERT INTO `grade`(`name`) VALUES ('大四'),('大一'),('大三')
INSERT INTO `student`(`grade`,`sex`,`name`) VALUES (3,1,'张三'),(3,0,'李四') -
UPDATE
1
2
3
4
5
6
7/*
WHERE字句用于设置条件
*/
UPDATE `student` SET `name`='阿三',`grade`=2 WHERE `id`=2
UPDATE `student` SET `name`='阿四' WHERE `sex`=0 OR `sex`=1
UPDATE `student` SET `name`='阿三',`email`='ddd@111.com' WHERE `id` BETWEEN 1 AND 5
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='阿四' -
DELETE
1
DELETE FROM `student` WHERE `id`=1
-
TRUNCATE
1
2
3
4
5
6/*
清空表
不影响事务
自增自动归零
*/
TRUNCATE `student`
DQL
关键词顺序
1 | SELECT [ALL|DISTINCT] |
查询指定字段
1 | SELECT * FROM `student` -- 查询表的所有信息 |
去重
1 | SELECT DISTINCT `studentno` FROM `result` |
模糊查询
1 | /* |
联表查询
-
在表中存在至少一个匹配时,
INNER JOIN
关键字返回行。 -
LEFT JOIN
关键字会从左表 (A) 那里返回所有的行,即使在右表 (B) 中没有匹配的行。 -
RIGHT JOIN
关键字会右表 (B) 那里返回所有的行,即使在左表 (A) 中没有匹配的行。
1 | SELECT `student`.`studentname`,`subject`.`subjectname`,`result`.`studentresult` FROM `student` RIGHT JOIN `result` ON `result`.`studentno`=`student`.`studentno` LEFT JOIN `subject` ON `result`.`subjectno`=`subject`.`subjectno` |
自连接
一门学科有多个方向,一个方向也可进行细分,形成树形结构。
-
原表
categoryid pid categoryname 2 1 信息技术 3 1 软件开发 4 3 数据库 5 1 美术设计 6 3 web开发 7 5 ps技术 8 2 办公信息 -
父类(pid=1)
categoryid categoryname 2 信息技术 3 软件开发 5 美术设计 -
子类
categoryid pid categoryname 4 3 数据库 6 3 web开发 7 5 ps技术 8 2 办公信息 -
查询父类对应的子类关系
1
SELECT `a`.`categoryname` AS '父类',`b`.`categoryname` AS '子类' FROM `category` AS a,`category` AS b WHERE `a`.`categoryid`=`b`.`pid`
父类 子类 信息技术 办公信息 软件开发 web开发 软件开发 数据库 美术设计 ps技术
排序
1 | /* |
分页
1 | /* |
分类
1 | /* |
子查询
1 | /* |
事务
定义
将一组SQL语句作为一个批次执行,其中一条语句失败则事务失败。
ACID原则
- 原子性:一个事务要么全部执行,要么不执行,也就是说一个事务不可能只执行了一半就停止了。
- 一致性:事务的运行并不改变数据库中数据的一致性;例如,完整性约束了 ,一个事务改变了 ,那么 也应该随之改变。
- 独立性:事务的独立性也称作隔离性,是指两个以上的事务不会出现交错执行的状态;因为这样可能会导致数据不一致,更加具体的来讲,就是事务之间的操作是独立的。
- 持久性:事务的持久性是指事务执行成功以后,该事务对数据库所作的更改便是持久的保存在数据库之中,不会无缘无故的回滚。
大白话讲解脏写、脏读、不可重复读和幻读 - 知乎 (zhihu.com)
示例
1 | CREATE TABLE `account` ( |
索引
索引的优点
- 提高查询速度。
- 确保数据的唯一性。
- 可以加速表和表之间的连接 ,实现表与表之间的参照完整性。
- 使用分组和排序子句进行数据检索时,可以显著减少分组和排序的时间。
- 全文检索字段进行搜索优化。
分类
- 主键索引 (Primary Key)
- 只能有一个列作为主键
- 用于确定特定数据记录在数据库中的位置
- 唯一索引 (Unique)
- 唯一索引可能有多个
- 用于避免同一个表中某数据列中的值重复
- 常规索引 (Index)
- 全文索引 (FullText)
索引的使用
1 | /* |
索引使用准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
数据备份
-
拷贝物理文件(data文件夹)
-
Navicat导出
-
命令行
1
mysqldump -hlocalhost -uroot -proot school student >D:/student.sql