本章主要讲解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!