【WEEK5】 【DAY5】DML语言【中文版】

2024.3.29 Friday

3.DML语言

3.1.外键（了解）

3.1.1.概念

• 如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
• 在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

3.1.2.作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

3.1.3.添加（书写）外键的几种方法

3.1.3.1.创建表时直接在主动引用的表里写（被引用的表的被引用的部分）

本例中grade是被引用（主表）的表，student是主动引用（从表）的表

-- 第三大部分添加外键 法1 --
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
	`studentno` INT(4) NOT NULL COMMENT '学号',
	`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',

	#这行不是为了连接表‘grade’，但表示“要引用”
	`gradeid` INT(10) DEFAULT NULL COMMENT '年级',

	`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
	`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
	`borndate` DATETIME DEFAULT NULL COMMENT '生日',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY (`studentno`),
	
	KEY `FK_gradeid` (`gradeid`),		-- 其中FK_是默认的名字，（定义外键的）目的是引用grade表的graidid
	
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
	#给外键添加约束，并引用到另外一张表中的某个具体字段
) ENGINE=INNODB DEFAULT CHARSET=utf8

点击设计表student打开外键则能看到如下

3.1.3.2.先创建表后修改表以添加外键

注意：和上一种方法不同的都在从表，从表第一次书写时除了第104行和外键有关的也需要保留以外，其他都不用写（放在后来的添加中写（最后2行））

-- 第三大部分添加外键 法2 --
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
	`studentno` INT(4) NOT NULL COMMENT '学号',
	`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
	`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
	`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
	`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
	`borndate` DATETIME DEFAULT NULL COMMENT '生日',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY (`studentno`)		-- 作为括号内最后一行时不能加逗号
) ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);

3.1.3.3.以上操作都是物理外键，数据库级别的外键，不建议使用 -> 避免数据库过多

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据（外键）–> 改成用程序实现

3.1.4.删除

删除具有主外键关系的表时，要先删子表，后删主表，否则无法删除主表
以删除上例grade表为例：

3.2.DML语言（Data Manipulation Language）

3.2.1.数据库意义

数据存储、数据管理

3.2.2.管理数据库数据方法

• 通过Navicat、SQLyog等管理工具管理数据库数据
• 通过DML语句管理数据库数据

3.2.3.DML语言：数据操作语言

• 用于操作数据库对象中所包含的数据
• 包括 :
  • INSERT (添加数据语句)
  • UPDATE (更新数据语句)
  • DELETE (删除数据语句)

3.2.4.添加

-- 插入语句（添加）--
INSERT INTO 表名([字段名1, 字段名2, 字段名3, ...]) VALUES ('value1'), ('value2'), ('value3'), ('value4'), ...)或为('value1', 'value2', 'value3',...的形式)

1. 最简单的情况：该表只有一个字段，本质上写不写(`gradename`)都会自动把值添加到这一列

INSERT INTO `grade` (`gradename`) VALUES ('undergraduate')

2. 如果要同时添加多行同一字段的数据，则每个数据都要单独用括号和逗号分开

-- 如果要同时添加多行同一字段的数据，则每个数据都要单独用括号和逗号分开
INSERT INTO `grade` (`gradename`) VALUES ('sophomore'), ('junior')

（对应id4~5）

3. 如果同时添加的是同一行不同字段的数据，则不需要把每个数据用括号分开，但需注意数据和字段名的一一对应关系

-- 如果同时添加的是同一行不同字段的数据，则不需要把每个数据用括号分开，但需注意数据和字段名的一一对应关系，如下
INSERT INTO `student` (`studentname`, `phoneNum`) VALUES ('DL','1234567')
INSERT INTO `student` (`studentno`, `studentname`, `phoneNum`, `borndate`) VALUES ('1','DL','9876','1021')
INSERT INTO `student` (`studentno`, `studentname`, `phoneNum`, `borndate`) VALUES ('2','DL','9876','2222-10-21')

4. 注意

• 字段或值之间用英文逗号隔开
• ’ 字段1,字段2…’ 该部分可省略，但添加的值务必与表结构,数据列,顺序相对应,且数量一致
• 可同时插入多条数据 , values 后用英文逗号隔开

3.2.5.修改

-- 修改语句 --
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

1. 不指定条件则会把该字段中的所有数据都更改

-- 不指定条件则会把该字段中的所有数据都更改
UPDATE `student` SET `studentname` = 'momo'

2. 选择某特殊字段的值作为filter，修改那一行的字段的值

-- 选择某特殊字段的值作为filter，修改那一行的字段的值
UPDATE `student` SET `studentname` = 'DL' where `studentno` = 1;

3. 修改多个字段的属性，直接在SET后多跟几个赋值即可

-- 修改多个字段的属性，直接在SET后多跟几个赋值即可
UPDATE `student` SET `studentname` = 'QW', `address` = 'quagmire' where `studentno` = 2;

4. 通过多个条件定位数据

-- 通过多个条件定位数据
UPDATE `student` SET `studentname` = 'dl' WHERE studentno = 0 AND phoneNum = 1234567;

5. 插入当前时间

-- 插入当前时间
UPDATE `student` SET `borndate` = CURRENT_TIMESTAMP WHERE studentno = 0 OR phoneNum = 1234567;

6. 注意

• column_name 为要更改的数据列
• value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果
• condition 为筛选条件 , 如不指定则修改该表的所有列数据

7. Where条件语句
   
   如：

UPDATE `student` SET `studentname` = 'as', `address` = 'quagmire' WHERE studentno BETWEEN 0 AND 2;

3.2.6.删除

1. DELETE

DELETE FROM 表名 [WHERE condition];

condition为筛选条件，如不指定则删除该表的所有列数据
如：

-- 删除语句 --
-- 不建议这样写，是全部删除
DELETE FROM `student`
-- 有选择性地删除
DELETE FROM `student` WHERE studentno = 1;

2. TRUNCATE

TRUNCATE [TABLE] table_name;

作用：用于完全清空表数据，但表结构、索引、约束等不变
如：

TRUNCATE `student`

3. DELETE和TRUNCATE的区别

• 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
• 不同 :
  • 使用TRUNCATE TABLE 会重新设置AUTO_INCREMENT计数器
  • 使用TRUNCATE TABLE不会对事务有影响

4. 测试

-- 测试
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#插入测试数据
INSERT INTO `test`(`coll`) VALUES('row1'),('row2'),('row3');
#删除表数据
DELETE FROM test;
#再插入，看id不是从1而是从4开始
INSERT INTO `test`(`coll`) VALUES('row1'),('row2'),('row3');

TRUNCATE TABLE test;
#再插入，看id不是从7而是从1开始
INSERT INTO `test`(`coll`) VALUES('row1'),('row2'),('row3');

结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.
同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后，使用不同的数据引擎：
InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)