数据库表的操作

一、DDL创建数据库表 

1.查看该库中所有的表

        show tables;

# 表
-- 查看该库中所有的表
show tables;

2.创建数据库表的语法：

创建表
create table 表名(
     字段名  类型  属性,
     字段名  类型  属性,
     ... 
     字段名  类型  属性
 );

create table student(
	stu_name varchar(10),
	stu_age tinyint,
	stu_birthday datetime,
	stu_id char(18),
	stu_score decimal(4,1)
);

3.查看表结构 

方式一
desc 表名
desc student;

方式二
show create table student;

4.查看所有支持的存储引擎

show engines;

4.1 数据库存储引擎-InnoDB 

•MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。

•InnoDB是MysQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交 (Commit)和回滚(Rollback)。

• 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。

• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

• 数据文件结构:

        • 表名.frm存储表结构(MySQL8.0时，合并在表名.ibd中)。

        • 表名.ibd存储数据和索引

• InnoDB是为处理巨大数据量的最大性能设计。

        • 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比 如: .frm,.par , .trn ,.isl, .db.opt等都在MySQL8.0中不存在了。

• 对比MylISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。

• MyISAM只缓存索引，不缓存真实数据;InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性 能有决定性的影响。

4.2 数据库存储引擎-MyISAM 

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外 键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

• 5.5之前默认的存储引擎

• 优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用

• 针对数据统计有额外的常数存储。故而count(*)的查询效率很高

• 数据文件结构:

        • 表名.frm存储表结构。

        • 表名.MYD存储数据(MYData)。

        • 表名.MYI存储索引 (MYIndex)

• 应用场景:只读应用或者以读为主的业务。 

4.3 数据库存储引擎-MyISAM 和InnoDB区别 (重点)

 

 二、数据库表的字段类型

1.字符串类型 

类型	说明	取值范围	存储需求
char[(M)]	固定长字符串，检索快但浪费空 间， 0 <= M <= 255	M字符	char[(M)]
varchar[(M)]	可变字符串 0 <= M <= 65535	变长度	varchar[(M)]
tinytext	微型文本串	6777215长度+3个字节	tinytext
text	文本串（4个G左右大小）	4294967295长度+4个字节	text

2.char和varchar比较(重点) 

类型	特点	空间上	时间上	适用场景
CHAR(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	节省存储空间	效率低	非CHAR的情况

3.数值类型 

类型	说明	取值范围	存储需求
tinyint【taɪni】	非常小的数据	有符值： -2 ^7 ~ 2^7-1，无符号值：0 ~ 28-1	1字节
smallint	较小的数据	有符值： -2 15 ~ 215 -1 ，无符号值： 0 ~ 216 -1	2字节
mediumi【miːdiəm】	中等大小的数据	有符值： -2 23 ~ 223 -1 ，无符号值： 0 ~ 224 -1	3字节
int	标准整数	有符值： -2^31 ~ 2^31-1，无符号值：0 ~ 2^32-1	4字节
bigint	较大的整数	有符值： -2^63 ~2^63-1，无符号值：0 ~2^64-1	8字节
float	单精度浮点数	±1.1754351e -38	4字节
double	双精度浮点数	±2.2250738585072014e -308	8字节
Decimal(钱) 【desɪml】	字符串形式的浮点数	decimal(m, d)	m个字节

4.日期和时间类型 

类型	说明	取值范围	存储需求
DATE	YYYY-MM-DD，日期格式	1000-01-01~ 9999-12-31	DATE
TIME	Hh:mm:ss ，时间格式	-838:59:59~838:59:59	TIME
DATETIME	YY-MM-DD hh:mm:ss	1000-01-01 00:00:00 至 9999-12-31 23:59:59	DATETIME
TIMESTAMP 【stamp】	YYYYMMDDhhmmss格式表 示的时间戳	197010101000000 ~2037年 的某个时刻	TIMESTAMP
YEAR	YYYY格式的年份值	1901~2155	YEAR

        设置当前 创建时间，或者是更新时间时给TIMESTAMP或者DATETIME字段 设置 DEFAULT CURRENT_TIMESTAMP 添加当前默认时间 

5.NULL类型

• 理解为“没有值”或“未知值”；
• 不要用NULL进行算术运算，结果仍为NULL；
• MySQL中，0或NULL都意味着为假，1为真。 

6.数据库表的字段注释

        使用COMMENT给数据库表的字段注释

CREATE TABLE `student`(
`stu_id`INT(4) COMMENT "ID",
`stu_name` VARCHAR(10) COMMENT“姓名",
`stu_age` INT(2)COMMENT“年龄",
`stu_sex`CHAR(2) COMMENT"性别"
);

三、表结构修改

1.修改表名

        语法：alter table 旧表名 rename as 新表名;

-- 1. 修改表名
-- alter table 旧表名 rename as 新表名;
alter table student rename as xuesheng;

2. 添加字段

        语法：alter table 表名 add 新字段名 类型  属性;

-- 2. 添加字段 ****
-- 字符串 'xxx' "xxx"
-- alter table 表名 add 新字段名 类型  属性;
alter table xuesheng 
	add stu_qq varchar(20) comment '这是学生的qq号'

3.删除字段（危险操作）

        语法：alter table 表名  drop 字段名;

-- 3.删除字段（危险操作）
-- alter table 表名  drop 字段名;
alter table xuesheng drop stu_age;

4.修改字段

方式一 modify 只能修改 数据类型 和 属性

方式二 change 数据类型 和 属性 还可以修改字段名

        语法：alter table 表名 modify 字段名 要修改的类型 要修改的属性; 

                   alter table 表名 change 旧字段名  新字段名 要修改类型  要修改属性;

-- 4.修改字段
-- 覆盖式，
-- 1.一定的默认值 
-- 2.如果已经存在具体数据--数据是可以做隐式转换的
-- 方式一 modify 只能修改 数据类型 和 属性
-- alter table 表名 modify 字段名 要修改的类型 要修改的属性;
alter table xuesheng modify stu_name varchar(100);
alter table xuesheng modify stu_qq int;

-- 方式二 change 数据类型 和 属性 还可以修改字段名
-- alter table 表名 change 旧字段名  新字段名 要修改类型  要修改属性;
alter table xuesheng 
	change stu_qq qq varchar(20) comment '这是qq号';

5.删除表 （危险操作）

        语法：drop table 表名

-- 删除表(危险操作)
-- drop table 表名
drop table xuesheng;

四、数据库设计的三大范式 

1.什么是范式 

        为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。 在关系型数据库中这种规则就叫做范式。

2.约束作用

        数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结 构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。

3.三范式

• 第一范式：确保每列保持原子性
• 第二范式：确保表中的每列都和主键相关
• 第三范式：确保每列都和主键列直接相关，而不是间接相关

3.1 第一范式（1NF）确保每列保持原子性 

        1.每一列属性都是不可再分的属性值，确保每一列的原子性

        2.两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

3.2 第二范式（2NF）属性完全依赖于主键 

        第二范式（2NF）是在第一范式（1NF）的基础上建立起来的。即满足第二范式必须先满足第一范式。 第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表 加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键

3.3 第三范式（3NF）属性不依赖于其它非主属性 属性直接依赖于主键

        数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。 像：a-->b-->c 属性之间含有这样的关系，是不符合第三范式的。 比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话） 这样一个表结构，就存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话) 这样的表结构，我们应该拆开来，如下。 （学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

总结：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。 如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结 构。所以不能一味的去追求范式建立数据库。

五、表约束 

 1.约束

        约束实际上就是表中数据的限制条件

2.约束作用

        表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效

3.约束种类

• 非空约束(not null)
• 唯一性约束(unique)
• 主键约束(primary key) PK
• 外键约束(foreign key) FK
• 检查约束(目前MySQL不支持、Oracle支持)

3.1 非空约束 

        用not null约束的字段不能为null值，必须给定具体的数据

        非空约束没有表级约束，只有行级约束

create table tb2(
	username varchar(10) not null, -- 非空约束
	userage int
);

show create table tb2;

CREATE TABLE `tb2` (
  `username` varchar(10) NOT NULL,
  `userage` int(11) DEFAULT NULL
);

create table tb3(
	username varchar(10) not null default '无名', -- 非空约束
	userage int
);

3.2 唯一约束

        unique约束的字段，具有唯一性，不可重复，但可以为null 

-- 唯一约束
-- 是可以为null 并且可以有多个null，因为null 是一个类型
create table tb4(
	username varchar(10) unique, -- 唯一约束  行级约束
	userage int unique   -- 每个字段都是各自的唯一约束
);

-- 
create table tb5(
	username varchar(10),
	userage int,
	unique(username,userage)  -- 表级约束, 联合唯一约束
											-- 字段是有关联的，必须每个字段的值都相同时才会触发唯一约束
);
create table tb6(
	username varchar(10),
	userage int,
	CONSTRAINT name_age_unique unique(username,userage)
);

 3.2.1 行级约束

-- 唯一约束
-- 是可以为null 并且可以有多个null，因为null 是一个类型
create table tb4(
	username varchar(10) unique, -- 唯一约束  行级约束
	userage int unique   -- 每个字段都是各自的唯一约束
);

3.2.2 表级约束

create table tb5(
	username varchar(10),
	userage int,
	unique(username,userage)  -- 表级约束, 联合唯一约束
							  -- 字段是有关联的，必须每个字段的值都相同时才会触发唯一约束
);

3.3.3 给约束起名字

        表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束) 

        constraint是约束关键字，t_user_email_unique 自己取的名字

create table tb6(
	username varchar(10),
	userage int,
	CONSTRAINT name_age_unique unique(username,userage)
);

 

 

3.3 主键约束(primary key)PK 

        主键(primary key)是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录

        表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值

-- 主键约束
-- 每张表必须有且只有一个主键
-- 主键的值是唯一
-- 主键是不能为null
create table tb7(
	username varchar(10) primary key,
	userage int primary key -- 错误，主键在一张表中只能有一个
);

create table tb8(
	username varchar(10),
	userage int,
	primary key(username,userage) -- 联合主键，联合约束
														-- 多个字段的值，只有完全相同时，才会触发约束
);

-- 一般主键的建立方式
-- int bitint 自增
-- 主键的值是不会回补
create table tb9(
	tid int primary key auto_increment,  -- 自增
	username varchar(10),
	userage int
);

3.4 外键约束(foreign key)FK 

        外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。 若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键

A为基本表或父标，主表，B为信息表，子表，副表 

外键约束(foreign key)FK 只能是表级定义，不能行级定义

foreign key(表的字段名) references 父表表名(父表的字段名)

        某个字段添加外键约束之后，该字段称为外键字段，外键字段中每个数据都是外键值 

按外键约束的字段数量分类

• 单一外键：给一个字段添加外键约束
• 复合外键：给多个字段联合添加一个外键约束 

注意：

一张表可以有多个外键字段（与主键不同）

外键值可以为null

外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束

有了外键引用之后，表分为父表和子表

        班级表：父表

        学生表：子表

创建先创建父表 删除先删除子表数据 插入先插入父表数据 

4.约束的添加 

添加非空约束
alter table表名modify test_student char(10) not null;
添加唯一约束
alter table表名add unique(表字段名,字段,字段,字段);
添加主键约束
alter table表名add primary key(表的字段名,字段,字段);
添加外键约束
alter table表名 add constraint N1 foreign key(表字段名)references 父表(父表字段名);

5.约束的删除

删除not null约束
alter table表名 modify列名类型;

删除unique约束
alter table表名 drop index唯一约束名;

删除primary key约束
alter table表名 drop primary key;

删除foreign key约束
alter table表名 drop foreign key 外键名;