MySQL-03-数据管理与DML
三、MySQL数据管理
3.1 外键
-
什么是外键
- 一个表中的某个字段中的数据,依赖于其他表中某个字段的数据
- 也就是说,我这个表(从表)中的某个字段的数据,要引用你那个表(主表)中的数据
-
外键的实现方式
-
物理层面上实现(不推荐),也就是在数据库中添加约束,即可在建表时添加,也可 在建表之后添加。
-
不推荐的原因:摘自https://zhuanlan.zhihu.com/p/62020571
- 性能问题
假设一张表名为user_tb。那么这张表里有两个外键字段,指向两张表。那么,每次往user_tb表里插入数据,就必须往两个外键对应的表里查询是否有对应数据。如果交由程序控制,这种查询过程就可以控制在我们手里,可以省略一些不必要的查询过程。但是如果由数据库控制,则是必须要去这两张表里判断。
- 并发问题
在使用外键的情况下,每次修改数据都需要去另外一个表检查数据,需要获取额外的锁。若是在高并发大流量事务场景,使用外键更容易造成死锁。
- 扩展性问题
这里主要是分为两点
-
做平台迁移方便,比如你从
Mysql
迁移到Oracle
,像触发器、外键这种东西,都可以利用框架本身的特性来实现,而不用依赖于数据库本身的特性,做迁移更加方便。 -
分库分表方便,在水平拆分和分库的情况下,外键是无法生效的。将数据间关系的维护,放入应用程序中,为将来的分库分表省去很多的麻烦。
-
技术问题
使用外键,其实将应用程序应该执行的判断逻辑转移到了数据库上。那么这意味着一点,数据库的性能开销变大了,那么这就对DBA的要求就更高了。很多中小型公司由于资金问题,并没有聘用专业的DBA,因此他们会选择不用外键,降低数据库的消耗。
相反的,如果该约束逻辑在应用程序中,发现应用服务器性能不够,可以加机器,做水平扩展。如果是在数据库服务器上,数据库服务器会成为性能瓶颈,做水平扩展比较困难。
-
-
程序层面上的实现(推荐),通过程序的逻辑来实现外键的约束
- 我同时查两个表,然后将结果组合起来使用
-
-
外键的示例
-- 在建表时添加外键约束 CREATE TABLE IF NOT EXISTS `grade` ( `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID', `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', PRIMARY KEY (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `student` ( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(5) NOT NULL DEFAULT '张三' COMMENT '姓名', `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '男性' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `address` VARCHAR(40) DEFAULT NULL COMMENT '地址', `email` VARCHAR(20) DEFAULT NULL COMMENT '电子邮箱', `gradeid` INT(10) DEFAULT NULL COMMENT '年级', #要作为外键的列 -- 设置索引名称与字段名 KEY `FK_gradeid` (`gradeid`), -- 给索引添加约束 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) PRIMARY KEY(`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -- 在建表之后添加约束 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (列名) REFERENCES 主表名(列名); ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`); -- 删除外键 ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>; ALTER TABLE `student` DROP FOREIGN KEY `FK_gradeid`;
3.2 DML语言
- DML(Data Manipulation Language)—— 数据操作语言
- 对数据库中的数据进行一些简单操作,如insert,delete,update等.
3.3 添加(INSERY)
-
语法
INSERT INTO table_name ( field1, field2,...,fieldN ) VALUES (value1, value2,...,valueN );
-
注意事项
- 一个INSERT语句可以插入多条数据,每条数据之间使用
()
进行分隔 - INSERT的字段名可以省略,如果省略,那么后面的values必须和表中的字段名一一对应,不建议省略
- 一个INSERT语句可以插入多条数据,每条数据之间使用
-
示例
-- 插入一条数据 INSERT INTO `grade`(`gradename`) VALUES ('大三'); -- 插入多条数据 INSERT INTO `student`(`name`,`age`,`sex`) VALUES('张三',18,'男'), ('李四',20,'女');
3.4 修改(UPDATE)
-
语法
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
-
注意事项
- 一定要加上条件判断,不然修改的表中所有的数据,这样的话准备后事就完事了嗷
-
示例
UPDATE grade SET gradename = '高中' WHERE gradeid = 1;
-
WHERE子句,可以理解为条件
-
类似于程序当中的if语句,会根据条件进行判断,然后返回一个布尔类型的值
-
常见运算符
操作符 描述 返回值 = 等于 (1=2)false <> 或 != 不等于 (1!=2) true > 或 >= 大于,大于等于 2>1 true < 或 <= 小于,小于等于 2<1 false BETWEEN 在…之间 between 1 and 5 表示在闭区间 [1,5] 之间 AND 且 等价于程序中的 && OR 或 等价于程序中的 ||
-
3.5 删除(DELETE)
-
语法
DROP TABLE table_name [WHERE case]
-
注意事项
- 如果不加上where条件进行限制,则会删除整个表中的数据,但不会改变表的结构
-
TRUNCATE 关键字
- 用于情况表,同样不会改变表的结构
- 区别于DELETE
- 同:都能删除表中的数据
- 异:TRUNCATE会重置自增量,delete不会
-
扩展:
- 对于InnoDB引擎,在MySQL 8.0以前,表的自增量计数器位于内存当中。这就导致了MySQL服务重启之后,自增量会重置。但在8.0之后修复了该问题