MySQL（三）数据管理（外键及DML语言）

MySQL数据管理

一、外键

-- 创建一个年级表
CREATE TABLE `grade`(
	`gradeid` INT (10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
	`gradename` VARCHAR(10) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式一：在创建表的时候，增加约束（复杂）

-- 学生表的grade字段要去引用年级表的gradeid
-- 定义外键FK_grade
-- 给这个外键添加约束（执行引用）references

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(3) NOT NULL COMMENT '性别',
	`grade` INT (10) NOT NULL COMMENT '学生年级',
	PRIMARY KEY(`id`),
	KEY `FK_grade` (`grade`),  -- 定义外键
	CONSTRAINT `FK_grade` FOREIGN KEY (`grade`) REFERENCES `grade`(gradeid) -- 添加约束
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要先删除从表，在删除主表

方式二：创建表成功后，再增加外键约束

-- 创建表的时候没有外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_grade` FOREIGN KEY (`grade`) REFERENCES `grade` (gradeid); 

-- ALTER TABLE `表名` ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列`) REFERENCES `主表`(`字段`)

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

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行和列
• 如果想使用多张表的数据，想使用外键：程序实现

---

二、DML语言

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

DML语言：数据操作语言

• insert
• update
• delete

---

2.1 添加

insert

INSERT INTO `表名` (`字段1`,`字段2`,`字段3`) VALUES (`值1`,`值2`,`值3`),(`值1`,`值2`,`值3`),
-- 主键自增可以省略
INSERT INTO `grade`(`gradename`) VALUES ('大二')

-- 插入多行数据
INSERT INTO `grade` (`gradename`) VALUES ('大三'),('大四')

注意事项

• 字段和字段间用英文逗号隔开
• 主键字段可以省略，但值必须一一对应
• 可以添加多行数据，不同行数据间用英文逗号隔开

---

2.2 修改

update

-- 实验
UPDATE `student` SET `name`='wendaidai' WHERE id = 1
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='cow',`sex`='女',`grade`='大四' WHERE id = 3

条件：where 语句 运算符 id等于某个值，大于某个值，在某个区间内修改...

如果没有条件语句，默认修改该属性所有数据

操作符：返回布尔值

注意事项

• 条件语句不能省
• 可以修改多个属性，不同属性之间用逗号隔开
• 值可以是定值也可以是变量

---

2.3 删除

delete命令

语法：delete from 表名 where 条件

-- 删除数据 （避免这么写，会全部删除）
delete from `表名`

-- 删除指定数据
delete from `student` where id = 1

TRUNCATE命令

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

-- 清空student表
trunncate `student`

delete 和 truncate 区别

• 相同点：都能删除数据，都不会删除表结构

• 不同：

  • truncate：重新设置自增列，计数器归零
  • truncate不会影响事务

  delete删除问题：重启数据库的现象

  • INNODB：自增列会从1开始 （存在内存中）
  • MYISAM：继续从上一个自增量开始（存在文件中，不会丢失）