从0到1学数据库：表和约束

点击上方“罗晓胜”，马上关注，您的支持对我帮助很大

上期文章

 

 

• 从0到1学数据库：where条件查询和Order By数据排序

• 从0到1学数据库：Function函数

 

/   前言   /

 

前面我们都是学习怎么操作数据，下面我们学习怎么去操作表，对表结构进行增删改查。

 

/   正文   /

 

本章要点

• 创建表
• 修改表
• 删除表
• 重命名
• 截断表
• 数据完整性约束

数据库对象命名原则

• 数据库对象命名原则 – 必须由字母开始，长度在 1–30个字符之间。– 名字中只能包含 A–Z, a–z, 0–9, _ (下划线)，$ 和 #。– 同一个数据库服务器用户所拥有的对象名字不能重复。– 名字不能为数据库的保留字。– 名字是大小写不敏感

注：详情可参考本项目-mysql开发规范

建表语句语法

• 建表语法 CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);

用子查询语法创建表

• 使用子查询创建表的语法

CREATE TABLE table[(column, column...)] AS subquery;

• 新表的字段列表必须与子查询中的字段列表匹配 • 字段列表可以省略

CREATE TABLE student2 AS SELECT id, name, age+10 newAge FROM student WHERE id = 1;

• Select列表中的表达式列需要给定别名，如果没有别名会产生错误

表的分类

MySQL 数据表主要支持六种类型 ，分别是：BDB、HEAP、ISAM、MERGE、MYISAM、InnoBDB。

• 事务安全型(transaction-safe)：

  • BDB：Mysql最早的具有事务能力的表的类型，目前已经停止开发

  • InnoDB：在MySQL 5.1之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。

• 非事务安全型(non-transaction-safe)：

  • HEAP：是MySQL表中访问最快的表，将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问（注意，这种类型下数据是非持久化设计的，它一般适应于临时表，如果MySQL或者服务器崩溃，表中数据全部丢失。）

  • ISAM：是MyISAM类型出现之前MySQL表使用的默认类型，现在已经被MyISAM代替。

  • MERGE：由一组MyISAM表组成，之所合并主要出于性能上考虑，因为它能够提高搜索速度，提高修复效率，节省磁盘空 间

  • MYISAM：基于ISAM类型，但它增加了许多有用的扩展，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一

数据类型与列定义

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes (-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度、浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度、浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2		依赖于M和D的值	依赖于M和D的值

日期和时间类型：表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小	范围（有符号）	范围（无符号）	用途
DATE	3 1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3 '-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1 1901/2155	YYYY	年份值
DATETIME	8 1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型：字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

修改表的定义

1. 修改表名 ALTER table tablename rename to newtablename;

2. 添加列语法：ALTER TABLE table ADD (columnname datatype[DEFAULT expr] [, columnname datatype]...);

3. 修改列语法：ALTER TABLE table MODIFY(columnname datatype[DEFAULT expr] [, columnname datatype]...);

4. 删除列语法：ALTER TABLE table DROP (columnname [,columnname]);

示例：

-- 修改表名
ALTER table student rename to newstudent;
-- 添加列
ALTER TABLE student ADD column c_weight int(5) not null comment '体重' AFTER `age`; //在哪个字段后面添加
-- 修改列
ALTER TABLE student MODIFY c_weight varchar(20) COMMENT '体重';
-- 删除列
ALTER TABLE student DROP c_weight;

添加新列

ALTER TABLE table ADD (columnname datatype[DEFAULT expr] [, columnname datatype]...);

• 增加列原则：– 可以添加或修改列 – 可指定新添加列的位置，使用AFTER 字段。

修改已存在的列

• 列的修改可以修改数据类型，长度，及默认值。• 修改数据类型：已有的行数据必须为空。• 修改长度原则：数值型修改长度：当长度向小改时，已有行的数该列必须为空；当长度向大改时，可以随意修改。字符型修改长度：当长度向小改时，只要修改后的值能容纳下 当前已有数据的最大值即可，当长度向大改时，可以随意修改。• 修改列的默认值：

-默认值的修改不会影响已经存在的行，只影响新增加的行

• 把student表年龄(age) 列，修改为长度为2 ALTER TABLE student MODIFY age CHAR(2);

• 添加默认值 ALTER TABLE student MODIFY age DEFAULT ‘1’;

删除列

• 可以用DROP子句从表中删除列，包括列的定义和数据。• 删除列原则：

– 列可以有也可以没有数据。– 表中至少保留一列。– 列被删除后，不能再恢复。– 被外键引用的列，不能被删除。

• 删除列语法一 ALTER TABLE emp student COLUMN age;

• 删除列语法二 ALTER TABLE table student (columnname[,columnname]);

• 删除student表的两个字段“name”和“age”。ALTER TABLE student DROP (name,age);

删除、重命名与截断表

删除表

• 删除表语法：

DROP TABLE table；

– 只有表的创建者 – 或具有DROP ANY TABLE权限的用户才能删除表

• 删除表原则：– 表中所有的数据和结构都被删除。– 任何视图和同义词被保留但无效。– 所有与其相关的约束和索引被删除。– 任何未完成的事务被提交。

示例 DROP TABLE student;

重命名表

• 重命名语句语法：alter table RENAME old_name TO new_name; – 必须是对象的所有者

• 把emp表重新命名为empl alter table test_a rename to sys_app;

截断表

• 截断表语法：TRUNCATE TABLE table;

– 执行TRUNCATE语句的前提，必须是表的所有者 – 或者有DELETE ANY TABLE系统权限来截断表。

示例：TRUNCATE TABLE emp;

备注：清空表和截断表的区别 清空表：delete from users；清空表只是清空表中的逻辑数据，但是物理数据不清除，如主键值、索引等不被清除，还是原来的值。

截断表：truncate table users；截断表可以用于删除表中 的所有数据。截断表命令还会回收所有索引的分配页

约束的概念

• 约束 (constraints) 是防止无效数据输入到表中。约束做下面的事：– 多个表之间的具体关系，比如两个表之间的主外键关系。– 表在插入、更新行或者删除行的时候强制表中的数据遵循 约束规则。– 对于成功的操作，约束条件是必须被满足的。– 如果表之间有依赖关系，使用约束可以防止表或表中相关 数据的删除。

• 约束命名 – 约束命名原则：所有的约束定义存储在数据字典中。– 如果给约束一个有意义的名字，约束易于维护，约束命名 必须遵守标准的对象命名规则。– 如果没有给约束命名，Oracle服务器将用默认格式 SYS_Cn产生一个名字，这里 n 是一个唯一的整数，来保 证名称的唯一性。– 建议至少应该给表的主、外键按照命名原则来命名，如可 以采用这样的原则来命名，表名_字段名_约束类型。

约束的类型

约束	说明
NOT NULL	指定列不能包含空值
DEFAULT	默认值，用于保证该字段有默认值。
UNIQUE	指定列的值或者列的组合的值对于表中所有的行必须 是唯一的
PRIMARY KEY	表的每行的唯一性标识
FOREIGN KEY	在列和引用表的一个列建立并且强制的列之间关系
CHECK	指定一个必须为真的条件

生成与维护约束

• 约束的语法如下：CREATE TABLE [schema.] table (column datatype [ DEFAULT expr][column_constraint], ...[table_constraint][,...]);

– 约束可以在两个级别上定义，表级约束与列级约束。– 列级约束能够定义完整性约束的任何类型。– 表级约束除了NOT NULL之外，能够定义完整性约束的任 何类型。

示例

create table if not exists t_stuinfo(
     id int primary key,    #主键
     stuName varchar(20) not null,    #非空
     gender char(1) check(gender='男' or gender='女'),    #检查约束，MySql没有效果但不报错
     seat int unique,    #唯一约束
     age int default 18,    #默认（值）约束
     majorId int references major(id) #外键约束，MySql没有效果，但不报错
     );

现有表中增加/删除约束

• 增加约束语法如下：ALTER TABLE table ADD [CONSTRAINT constraint] type (column);

• 删除约束语法 Alter TABLE tablename Drop Primary key | Unique (column)| Constraint constraint [Cascade]

示例：

-- 添加非空约束
alter table 表名称 modify column 列名 列类型 not null;
-- 添加默认约束
alter table 表名称 modify column 列名 列类型 default 默认值;
-- 添加主键
alter table 表名称 modify column 列名 列类型 primary key;
alter table 表名称 add primary key(id);

-- 删除默认约束
alter table 表名称 modify column 列名 列类型;
-- 删除主键
Alter table student modify column id int;;
Alter table student drop primary key;
-- 删除唯一
alter table 表名称 drop index(索引名) 设置唯一时的名称;

约束的启用和禁用

• 禁用约束 – 如果有大批量数据导入时，我们可以采用禁用约束的方法， 主要的好处，首先效率高，另外有主外键约束的表之间导 入时，不用考虑导入的先后顺序。

• 禁用外键约束语法：SET FOREIGN_KEY_CHECKS=0;

• 启用约束语法：SET FOREIGN_KEY_CHECKS=1;

 

/   总结   /

 

本文主要讲了如何修改表结构等信息，在表创建有问题或者需要拓展时，可以参考文中的操作方法。

 

往期推荐：

如何入门做软件开发

为什么我不推荐入行程序员

做全栈开发很难吗

关注我的公众号，学习技术或投稿

长按上图，识别图中二维码即可关注