前言：

本文内容：MyISAM和InnoDB的区别、修改和删除数据表字段、数据库级别的外键

推荐免费MySQL基础讲解视频：【狂神说Java】MySQL最新教程通俗易懂_哔哩哔哩_bilibili

MyISAM和InnoDB的区别

功能 MyISAM InnoDB(默认)
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大(前者的2倍)

常规使用操作

  • MyISAM 节约空间 速度较快；
  • InnoDB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有数据文件都在data目录下，一个文件夹对应一个数据库，本质还是文件的存储。

MySQL引擎在物理文件上的区别：

  • InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
  • MyISAM对应文件
    • *.frm 表结构定义文件
    • *.myd 数据文件(data)
    • *.myi 索引文件(index)

设置数据库的字符集编码

1
charset=utf8 --设置中文编码

不设置的话，回事mysql默认的字符集编码(不支持中文)

在my.ini中配置默认的编码

1
character-set-server=utf8

修改和删除数据表字段

修改

1
2
3
4
5
6
7
8
9
10
-- 修改表名 alter table 表名 rename 新表名
alter table student rename as students;
-- 增加表的字段 alter table 表名 add 字段名 列属性
alter table students add age int(11);
-- 修改表的字段(一般不用来重命名，只来修改约束)
alter table students modify age varchar(10);
-- 修改字段名称(一般用来重命名，不用来修改约束)
alter table students change age age1 int(11);
-- 删除表的字段
alter table students drop age1;

删除

1
2
-- 删除表(如果表存在在删除)
drop table if exists students;

注意点：

  • 字段名使用``包裹
  • 注释 -- 和 /**/
  • sql关键字大小写不敏感

数据库级别的外键

简介

外键表示一个表中的一个字段被另一个表中的一个字段引用。外键对相关表中的数据造成了限制，使MySQL能够保持参照完整性。

创建外键

方式一：在创建表时，增加约束(较麻烦);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 年级表
create table `grade`(
	`gradeid` int not null auto_increment comment '年级id',
	`gradename` varchar(20) not null comment '年级名称',
	primary key(`gradeid`)
)engine=innodb charset=utf8

-- 学生表
-- 定义外键  添加约束 引用references 表名(字段名)
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(20) 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 '邮箱',
	`gradeid` int not null comment '年级id',
  PRIMARY KEY (`id`),
	key `fk_gradeid`(`gradeid`),
	constraint `fk_gradeid` foreign key(`gradeid`) references `grade` (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 查看表
select * from grade gr,student st where gr.gradeid=st.gradeid;

注意：删除有外键关系的表时，必须要先删除引用的表，再删除被引用的表；

方式二：创建表成功后，添加外键约束;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 年级表
create table `grade`(
	`gradeid` int not null auto_increment comment '年级id',
	`gradename` varchar(20) not null comment '年级名称',
	primary key(`gradeid`)
)engine=innodb charset=utf8

-- 学生表
-- 定义外键  添加约束 引用references 表名(字段名)
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(20) 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 '邮箱',
	`gradeid` int not null comment '年级id',
  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`);
-- 查看表
select * from grade gr,student st where gr.gradeid=st.gradeid;

以上两种添加外键的方式都是物理外键，数据库级别的外键，并不建议使用！(避免数据库过多造成困扰)

最佳实现

  • 数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)；
  • 要想使用多张表的数据，想使用外键(可用程序区实现)；