MySQL数据增删改操作详解(DML)

本章主要讲解MySQL的增删改操作

外键

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

外键创建的方式

• 删除具有主外键关系的表时，要先删子表，后删主表

创建子表同时创建外键

-- 年级表
create TABLE `grade` (
	`gradeid` INT(10) not NULL auto_increment COMMENT '年级id',
	`gradename` varchar(20) not null COMMENT '年级名称',
  PRIMARY KEY(`gradeid`)
)	ENGINE=INNODB DEFAULT charset=utf8

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)，外键gradeid
create table `student` (
	`studentno` int(4) not NULL auto_increment COMMENT '学号',
	`studentname` VARCHAR(20) not NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) not NULL DEFAULT '男' COMMENT '性别',
	`stugradeid` INT(10) DEFAULT NULL COMMENT '年级id',
	`phone` 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`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY(`stugradeid`) REFERENCES `grade`(`gradeid`)
)	ENGINE=INNODB DEFAULT charset=utf8

创建子表完毕，修改子表添加外键

-- 年级表
create TABLE `grade` (
	`gradeid` INT(10) not NULL auto_increment COMMENT '年级id',
	`gradename` varchar(20) not null COMMENT '年级名称',
  PRIMARY KEY(`gradeid`)
)	ENGINE=INNODB DEFAULT charset=utf8

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)，外键gradeid
create table `student` (
	`studentno` int(4) not NULL auto_increment COMMENT '学号',
	`studentname` VARCHAR(20) not NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) not NULL DEFAULT '男' COMMENT '性别',
	`stugradeid` INT(10) DEFAULT NULL COMMENT '年级id',
	`phone` 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(`stugradeid`) REFERENCES `grade`(`gradeid`)

添加数据

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')

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

INSERT INTO `grade`(`gradename`) VALUES ('大一');

-- INSERT INTO `grade` VALUES ('大二'); X 添加的值务必与表结构，数据列顺序相对应，且数量一致
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES (2,'大二');

-- 一次插入多条数据
INSERT INTO `grade`(`gradename`) VALUES ('大三'),('大四'); 

修改数据

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

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

-- 先添三条学生信息数据
INSERT INTO `student`(`studentname`,`stugradeid`,`sex`,`phone`); 
VALUES ('张三','1','男','15336774423'),('李四','2','男','15336774333'),('王五','4','女','15336774428');
-- 不指定条件下会改变所有的表
-- UPDATE `student` SET `studentname`='赵六';

-- 修改年级信息(单条)
UPDATE `grade` SET `gradename`='高中' WHERE gradeid=1;
-- 修改学生信息(多条)
UPDATE `student` SET `phone`='13736774423',`sex`='女' WHERE `studentname`='张三';

where 运算符

• BETWEEN 5 AND 10 = [2 , 5]
• LIKE 结合使用的通配符：% (代表0到任意个字符) _ (一个字符)

删除数据

DELETE命令

DELETE FROM 表名 [WHERE condition];

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

-- 删除gradeid=3的该行数据
DELETE FROM `grade` WHERE gradeid=3;

-- 因为刚刚在student表中引用了grade表中gradeid=4的这行数据，所以该行数据在grade表中不能被删除(外键被引用不能删除)
-- DELETE FROM `grade` WHERE gradeid=4; X 
-- DELETE FROM `grade` WHERE gradename='大四'; X

TRUNCATE命令

• 用于完全清空表数据 , 但表结构 , 索引 , 约束等不变

TRUNCATE [TABLE] table_name;

DELETE 与TRUNCATE的区别

• 两者都能删除数据，不删除表结构，但TRUNCATE速度更快
• TRUNCATE 在清空表数据的后，重新设置AUTO_INCREMENT计数器，且TRUNCATE 不会对事务有影响
• DELETE 在清空表数据之后，会继续删除之前的自增量

-- 创建一个测试表 
CREATE TABLE `test` (
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `coll` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入几个测试数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
-- 输出结果：
1	row1
2	row2
3	row3

DELETE FROM test;
INSERT INTO test(coll) VALUES('row4'),('row5'),('row6');
-- 输出结果：
4	row4
5	row5
6	row6

-- 删除test测试表，重新创建再插入数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
TRUNCATE TABLE test;
INSERT INTO test(coll) VALUES('row4'),('row5'),('row6');
-- 输出结果：
1	row4
2	row5
3	row6

Hi, welcome to JasperのBlog！