MySQL 的数据管理及 DML 语句

外键

在school数据库中再创建一年级grade表

CREATE TABLE `grade`(
    `gradeid` INT(10) not NULL auto_increment COMMENT '年级id',
    `gradename` VARCHAR(50) not null COMMENT '年级名称',
    PRIMARY KEY (gradeid)
)ENGINE=INNODB DEFAULT charset=utf8

学生表的gradeid字段要去引用年级表的gradeid
1.定义外键key
2.给这个外键添加约束(执行引用) references 引用
创建表的时候没有外键关系

ALTER TABLE student ADD KEY `FK_gradeid` (`gradeid`);

ALTER TABLE student DROP KEY `FK_gradeid`;

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

alter table 表名 add constraint 约束名 foreign key(作为外键的列) references 主表(主表字段)

相当于

create table if not exists `student`(
    `id` int(4) not null auto_increment comment '学号',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd` varchar(20) not null default '123456' comment '密码',
    `sex` varchar(2) not null default '女' comment '性别',
    `birthday` datetime default null comment '出生日期',
    `address` varchar(100) default null comment '家庭住址',
    `email` varchar(50) default null comment '邮箱',
    primary key (`id`),
    key `FK_gradeid`(`gradeid`),
    constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`)
)engine=innodb default charset=utf8;

• 注意要使用外键约束，两个表中都需要有对应的字段，并且能互相关联上。
• 删除有外键关系的表，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

以上的操作都是物理外键，数据库级别的外键，我们不建议使用。(避免数据库过多造成困扰)

最佳实践

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

DML(数据库管理语言)

数据库意义：数据存储，数据管理。

• insert
• update
• delete

添加

insert

insert into 表名(字段名1,字段2,字段3...) values('值1','值2',...)

-- 如果不写字段，就会一一匹配，一般写插入语句，一定要数据和字段一一匹配
INSERT INTO student2(name,pwd,sex) VALUES('hudu','123123','男');
INSERT INTO student2(name,sex) VALUES('alex','男');

注意：
1.字段和字段之间用英文逗号隔开
2.字段是可以省略的，但是后面的值必须要一一对应，不能少
3.可以同时插入多条数据，values后面的值，需要使用逗号隔开即可

修改

updata

update 表名 set column_name=value where 判断条件

-- 修改学员的名字
update `student2` set `name`='Alex' where id=2;

-- 不指定条件的情况下会改动所有的表！
update `student` set `name`=`哈哈`;

-- 修改多个属性
update `student` set `name`=`哈哈`,`email`='213123124@icloud.com';

条件：
where 子句运算符id等于，大于，小于某个值
操作符会返回boolean值

-- 都表示当前时间
DATE(NOW())
current_time

UPDATE student2 SET birthday=DATE(NOW()) WHERE id = 4;

注意：
value可以是一个具体的值，也可以是一个变量。
多个设置属性之间，使用逗号隔开

删除

delete

语法：delete from 表名 where 条件

-- 避免这样写
delete from `student`

-- 删除指定数据
delete from `student` where id = 1;

truncate 命令

作用：完全清空一个数据库表，表的结构和索引约束不会变！

-- 清空表
truncate `student`

delete和truncate区别

• 相同点，都能删除数据，而且都不会删除表结构
• 不同：
  • truncate 重新设置 自增列 计数器会归零
  • truncate 不会影响事务

测试

CREATE TABLE `test`(
    `id` INT(4) auto_increment,
    `coll` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

然后使用delete

delete from `student`
-- 再次插入数据
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

发现delete是影响自增的

使用truncate

truncate table `test`;
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

发现自增又是从1开始

delete删除的问题，重启数据库，现象

• innodb自增列会重新从1开始(存在内存当中，断点即失，新版本好像修复了这个问题)
• MYISAM 继续从上一个自增量开始(存在文件中)

本作品采用《CC 协议》，转载必须注明作者和本文链接