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',...的形式)
- 最简单的情况：该表只有一个字段，本质上写不写(`gradename`)都会自动把值添加到这一列
INSERT INTO `grade` (`gradename`) VALUES ('undergraduate')
- 如果要同时添加多行同一字段的数据，则每个数据都要单独用括号和逗号分开
-- 如果要同时添加多行同一字段的数据，则每个数据都要单独用括号和逗号分开
INSERT INTO `grade` (`gradename`) VALUES ('sophomore'), ('junior')
（对应id4~5）
- 如果同时添加的是同一行不同字段的数据，则不需要把每个数据用括号分开，但需注意数据和字段名的一一对应关系
-- 如果同时添加的是同一行不同字段的数据，则不需要把每个数据用括号分开，但需注意数据和字段名的一一对应关系，如下
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')
- 注意
- 字段或值之间用英文逗号隔开
- ’ 字段1,字段2…’ 该部分可省略，但添加的值务必与表结构,数据列,顺序相对应,且数量一致
- 可同时插入多条数据 , values 后用英文逗号隔开
3.2.5.修改
-- 修改语句 --
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];
- 不指定条件则会把该字段中的所有数据都更改
-- 不指定条件则会把该字段中的所有数据都更改
UPDATE `student` SET `studentname` = 'momo'
- 选择某特殊字段的值作为filter，修改那一行的字段的值
-- 选择某特殊字段的值作为filter，修改那一行的字段的值
UPDATE `student` SET `studentname` = 'DL' where `studentno` = 1;
- 修改多个字段的属性，直接在SET后多跟几个赋值即可
-- 修改多个字段的属性，直接在SET后多跟几个赋值即可
UPDATE `student` SET `studentname` = 'QW', `address` = 'quagmire' where `studentno` = 2;
- 通过多个条件定位数据
-- 通过多个条件定位数据
UPDATE `student` SET `studentname` = 'dl' WHERE studentno = 0 AND phoneNum = 1234567;
- 插入当前时间
-- 插入当前时间
UPDATE `student` SET `borndate` = CURRENT_TIMESTAMP WHERE studentno = 0 OR phoneNum = 1234567;
- 注意
- column_name 为要更改的数据列
- value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果
- condition 为筛选条件 , 如不指定则修改该表的所有列数据
- Where条件语句
如：
UPDATE `student` SET `studentname` = 'as', `address` = 'quagmire' WHERE studentno BETWEEN 0 AND 2;
3.2.6.删除
- DELETE
DELETE FROM 表名 [WHERE condition];
condition为筛选条件，如不指定则删除该表的所有列数据
如：
-- 删除语句 --
-- 不建议这样写，是全部删除
DELETE FROM `student`
-- 有选择性地删除
DELETE FROM `student` WHERE studentno = 1;
- TRUNCATE
TRUNCATE [TABLE] table_name;
作用：用于完全清空表数据，但表结构、索引、约束等不变
如：
TRUNCATE `student`
- DELETE和TRUNCATE的区别
- 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
- 不同 :
- 使用TRUNCATE TABLE 会重新设置AUTO_INCREMENT计数器
- 使用TRUNCATE TABLE不会对事务有影响
- 测试
-- 测试
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 : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)