MySQL数据管理

外键

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

年级ID在年级表中是主键，在学生表中是外键。如果不使用外键，学生表的grade字段插入了一个值，但是这个值在年级表中并没有，这个时候，数据库允许插入，并不会对插入的数据做关系检查。然而在设置外键的情况下，插入学生表grade字段的值必须要求在年级表的ID字段能找到。 同时，如果要删除年级表的某个ID字段值，必须保证学生表中没有引用该字段值的列，否则就没法删除。这就是所谓的保持数据的一致性和完整性。

CREATE TABLE IF NOT EXISTS `grade`(
	`id` INT NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`name` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`id`)
) -- 建表

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
	`grade` INT NOT NULL COMMENT '年级',
	`name` VARCHAR(30) NOT NULL DEFAULT '佚名',
	`sex` TINYINT NOT NULL COMMENT '性别',
	`birthdy` DATETIME DEFAULT NULL COMMENT '生日',
	`address` VARCHAR(255) DEFAULT NULL COMMENT '住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`),
	/*
		创建外键
		student表的grade字段引用grade表的id字段
	*/
	KEY `FK_grade` (`grade`),
	CONSTRAINT `FK_grade` FOREIGN KEY (`grade`) REFERENCES `grade`(`id`)
) -- 建表

不能直接删除grade表，会报错：“Cannot drop table ‘grade’ referenced by a foreign key constraint ‘FK_grade’ on table ‘student’.”
不建议使用这样的物理外键！否则数据库内部表的关系混乱。

DML

• INSERT

  /*
  不同字段在同一括号中
  多行数据用多个括号隔开
  */
  INSERT INTO `grade`(`name`) VALUES ('大四'),('大一'),('大三')
  INSERT INTO `student`(`grade`,`sex`,`name`) VALUES (3,1,'张三'),(3,0,'李四')
  

• UPDATE

  /*
  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

  DELETE FROM `student` WHERE `id`=1
  

• TRUNCATE

  /*
  清空表
  不影响事务
  自增自动归零
  */
  TRUNCATE `student`
  

DQL

关键词顺序

SELECT [ALL|DISTINCT]
{* | TABLE.* | [TABLE.FIELD1[AS ALIAS1][,TABLE.FIELD2[AS ALIAS2]][,...]]}
FROM TABLE_NAME [AS TABLE_ALIAS]
	[LEFT | RIGHT | INNER JOIN ANOTHER_TABLE_NAME] -- 联表查询
	[WHERE ...] -- 指定查询结果条件
	[GROUP BY ...] -- 指定分组依据的字段
	[HAVING ...] -- 过滤分组记录的条件
	[ORDER BY ...] -- 指定查询记录的排序方式
	[LIMIT {ROW_INDEX,LENGTH}] -- 指定查询记录的开始位置和长度

查询指定字段

SELECT * FROM `student` -- 查询表的所有信息
SELECT `studentname`,`address` FROM `student` -- 查询指定字段
SELECT `studentname` AS `姓名`,`address` AS `地址` FROM `student` -- 起别名
SELECT CONCAT('姓名：',`studentname`) AS `姓名`,`address` AS `地址` FROM `student` --使用函数

去重

SELECT DISTINCT `studentno` FROM `result`

模糊查询

/*
% 任意个字符
_ 一个字符
*/
SELECT `studentname` FROM `student` WHERE `studentname` LIKE '张%'
/*
列表中是或的关系
*/
SELECT `studentname` FROM `student` WHERE `studentno` IN (1001,1000)

联表查询

• 在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

• LEFT JOIN 关键字会从左表 (A) 那里返回所有的行，即使在右表 (B) 中没有匹配的行。

• RIGHT JOIN 关键字会右表 (B) 那里返回所有的行，即使在左表 (A) 中没有匹配的行。

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	办公信息

• 查询父类对应的子类关系

  SELECT `a`.`categoryname` AS '父类',`b`.`categoryname` AS '子类' FROM `category` AS a,`category` AS b WHERE `a`.`categoryid`=`b`.`pid`
  

  父类	子类
  信息技术	办公信息
  软件开发	web开发
  软件开发	数据库
  美术设计	ps技术

排序

/*
ASC 升序
DESC 降序
*/
-- 按照成绩降序
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` ORDER BY `result`.`studentresult` DESC

分页

/*
LIMIT a,b
起始位置a，长度b
*/
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` ORDER BY `result`.`studentresult` DESC LIMIT 1,2

分类

/*
关键词GROUP BY
分组后再过滤用HAVING
*/
SELECT `subject`.`subjectname`,AVG(`result`.`studentresult`) AS `平均分`,MAX(`result`.`studentresult`) AS `最高分`,MIN(`result`.`studentresult`) AS `最低分` FROM `result` INNER JOIN `subject` ON `subject`.`subjectno`=`result`.`subjectno` GROUP BY `result`.`subjectno` HAVING `平均分`>80

子查询

/*
SELECT 嵌套
*/
SELECT `result`.`studentno`,`result`.`subjectno`,`result`.`studentresult` FROM `result` WHERE `result`.`subjectno` IN (SELECT `subjectname`='高等数学-1' FROM `subject`)

事务

定义

将一组SQL语句作为一个批次执行，其中一条语句失败则事务失败。

ACID原则

• 原子性：一个事务要么全部执行，要么不执行，也就是说一个事务不可能只执行了一半就停止了。
• 一致性：事务的运行并不改变数据库中数据的一致性；例如，完整性约束了 $a+b=10$，一个事务改变了 $a$，那么 $b$ 也应该随之改变。
• 独立性：事务的独立性也称作隔离性，是指两个以上的事务不会出现交错执行的状态；因为这样可能会导致数据不一致，更加具体的来讲，就是事务之间的操作是独立的。
• 持久性：事务的持久性是指事务执行成功以后，该事务对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚。

大白话讲解脏写、脏读、不可重复读和幻读 - 知乎 (zhihu.com)

示例

CREATE TABLE `account` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(32) NOT NULL,
	`cash` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY (`id`)
)
INSERT INTO account (`name`,`cash`) VALUES('A',2000.00),('B',10000.00)

SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务，一旦被提交就持久化了
-- rollback;
SET autocommit = 1; -- 恢复自动提交

索引

索引的优点

• 提高查询速度。
• 确保数据的唯一性。
• 可以加速表和表之间的连接 ，实现表与表之间的参照完整性。
• 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间。
• 全文检索字段进行搜索优化。

分类

• 主键索引 (Primary Key)
  • 只能有一个列作为主键
  • 用于确定特定数据记录在数据库中的位置
• 唯一索引 (Unique)
  • 唯一索引可能有多个
  • 用于避免同一个表中某数据列中的值重复
• 常规索引 (Index)
• 全文索引 (FullText)

索引的使用

/*
1. 在创建表时指定索引
2. 创建表完毕后增加索引
*/
SHOW INDEX FROM `student` -- 查看表的索引信息
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname` (`studentname`) -- 添加全文索引

索引使用准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

数据备份

• 拷贝物理文件（data文件夹）

• Navicat导出

• 命令行

  mysqldump -hlocalhost -uroot -proot school student >D:/student.sql