第5章 -2 数据表管理

认识数据表

在数据库设计过程中，最重要的是表结构设计，好的表结构设计，对应着较高的效率和安全性。创建表的核心是定义表结构及设置表和列的属性，创建表以前，首先要确定表名和表的属性，表所包含的列名、列的数据类型、长度、是否为空、键、默认值等，这些属性构成表结构。

数据表名

完整的数据表名称应该由数据库名和表名两部分组成，即“数据库名.表名”。

• 在当前数据库中操作数据表时，表名之前的数据库名限定可以省略。
• 在处理属于两个不同数据库中的数据表时，需要用完整的数据表名。

属性列名

表可以拥有多个属性列，各个属性列分别用来存储不同性质的数据，属性列名同样必须符合MySQL的命名规则。

定义表就是指定义表的结构。在定义表之前首先需要注意：
（1）表名：在同一个数据库中，每一个表都应该有一个唯一的名称。
（2）列名：每个表由若干个列组成，在同一个表中每个列的名字应该是唯一的。
（3）列的数据类型：表中的每个列都要定义一个数据类型。
（4）列的数据约束：列中是否主键、外键、默认值、允许空、自增列等约束。

表中的数据

例：

学号	姓名	性别	出生日期	专业	总学分
191001	刘清泉	男	1998-06-21	计算机	52
191002	张慧玲	女	1999-11-07	计算机	50
191003	冯涛	男	1999-08-12	计算机	52
196001	董明霞	女	1999-05-02	通信	48

数据行：又称元组或记录，是现实世界中一个物理或逻辑实体的数据描述形式。

MySQL支持用多种方法在单个或多个列上创建约束，可以在创建表的同时创建约束，也可以在已有的表上创建约束。（约束详见第七章）
给约束定义的名称，称之为约束名。约束名可以由用户自己指定，也可由系统指定。

表间关联

在关系数据库中，一个数据库系统一般都包含多个表，表间也会存在联系。表间联系通过外键（外码）来实现，体现了关系之间的参照完整性。

一、数据类型

MySQL提供多种数据类型（对数据存储方式的一种约定），它能够规定数据的操作方式和存储所占空间的大小，主要根据实际需求（数据值的内容、大小、精度）来选择合适的数据类型。

数值型

MySQL数值型数据包括整数和小数。tinyint、smallint、mediumint、int、bigint，浮点小数类型float和double，定点（实数）小数类型decimal。

整数类型

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
tinyint	1B	0~255	-128~127
smallint	2B	0~65535	-32768~32767
mediumint	3B	0~16777215	-8388608~8388607
int或integer	4B	0~4294967295	-2147483648~2147483647
bigint	8B	0~18446744073709551615	-9223372036854775808~9223372036854775807

实数数据类型（定点数）

数据类型	字节数	负数的取值范围	非负数的取值范围
dec(m,d)、decimal(m,d)、numeric(m,d)	M+2	-1.7976931E+308~-2.2250738E-308	0和2.2250738E-308~1.7976931E+308

• 实数类型decimal，numeric用于存储整数或小数，保存的为确切精度的值。dec、numeric是decimal的别称。
• 用(m, d)来定义小数，其中m称为精度，表示总共的位数；d称为标度，表示小数的位数。 其中m和d的取值范围是：≤m≤65，0≤d≤m and 0≤d≤30。
  decimal(12,4)代表变量可以存放12位数字，小数点后可以存放4位小数。

浮点数类型

浮点类型有两种：单精度浮点类型float和双精度浮点类型double，为近似存储（不推荐使用）
用(m, d)来定义小数，其中m表示总共的位数；d表示小数的位数。
其中m和d的取值范围是：m（1~ 255），d(1~30)

数据类型	字节数	负数的取值范围	非负数的取值范围
float(m,d)	4B	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38~3.402823466E+38
double(m,d)	8B	-1.7976931E+308~-2.2250738E-308	0和2.2250738E-308~1.7976931E+308

注：在创建表时，数字类型的选择应遵循以下原则：

1. 在能够容纳所有数据的前提下，尽可能选择最小的可用类型，数据占用内存少，数据处理也更简单。例如年龄，正常的值在0到120之间，则可以使用tinyint。
2. 在需要表示的精度要求比较高的时候，比如货币、用于科学计算用的数据等，优先选择定点数类型decimal。
3. 不论定点类型还是浮点类型，如果用户指定的精度值超过系统定义的精度范围，则会进行四舍五入。
4. 因为浮点数存在误差，也尽量避免两个数值相近的浮点数进行大小比较。在设计数据库时，很少使用其作为列的数据类型。

字符串类型

字符串类型	取值范围	说明
char(n)	0~255个字符	固定长度字符串
varchar(n)	0~65535个字符	可变长度字符串
tinytext	0~255个字符	可变长度短文本
text	0~65535个字符	可变长度长文本

char和varchar类型：

• char(m)存储固定长度字符串，m表示字符个数，取值范围0~255，输入的字符串长度不足m时，右侧用空格填满，当查询到char的值时，尾部的空格被删除。
• varchar(m)存储可变长度的字符串，m表示最大的字符串长度，取值范围０~65535，varchar的最大实际长度由最长字段的大小和使用的字符集确定。
  例如：varchar(50)定义一个最长为50的字符串，如果输入的字符串只有20个字符，那么实际存储的字符串的长度为21，20个字符+一个字符串结束符。
• varchar在值存储和查询时尾部的空格仍保留

日期时间类型

日期时间类型	字节数	范围	格式	用途
date	4	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-01-19 03:14:07	YYYYMMDD HHMMSS	时间戳（日期和时间）

• 形式上，日期类型的表示方法与字符串的表示方法相同，使用单引号括起来；
• 本质上，日期类型的数据是一个数值类型，可以参与简单的加、减运算；
• date类型的字段可用函数current_date()或now()插入计算机系统的当前日
• 每种类型都有取值范围，当插入不合法的值时，系统会将“0”插入该字段

二进制类型

• binary
  binary和varbinary用于保存二进制字符串，它保存的是字节而不是字符，所以它们没有字符集，排序和比较按字节进行比较。
• blob
  blob是一个二进制大对象，可以容纳可变数量的数据，可以存储数据量很大的二进制数据，如图片、音频、视频等二进制数据。

其它数据类型

（1） 枚举类型—特殊的字符串类型
字段名 enum(‘值1’,’值2’,…,’值n’)

• “ 字段名”指将要定义的字段名称；“ 值n“指的是枚举列表中的第n个值，枚举列表中最多可以有65535个值，列表中的每个值的尾部空格自动被删除。
• enum类型的字段在取值时，只能在指定的枚举列表中取，而且只能取一个值。
• 如果enum类型的字段声明为允许null，null则为该字段的有效值，也是默认值；否则默认值为枚举列表中的第1个值。

（2） 集合类型—特殊的字符串类型
字段名 set(‘值1’,‘值2’,…,‘值n’)

• set类型也属于字符串类型，在创建表的时候，通过列举方式(一个个的值列出来)为字段显式指定集合列表。
• 与enum类型不同的是：enum类型的字段只能在指定的枚举列表中选择一个值；而set类型的字段可以从定义的列表中选择多个值。
• 如果插入set字段中的值有重复，则MySQL自动删除多余的重复值。

二、数据表建立

创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（实体完整性、参照完整性和用户定义完整性）约束的过程。

CREATE TABLE 数据表名 (
    列名1 数据类型 [约束条件] [默认值],
    列名2 数据类型 [约束条件] [默认值],
    ...
    [表级别约束条件]
);

每列定义是可以使用以下参数：

列名 数据类型 [ not null |null][default 默认值][auto increment] [unique[key]] [[primary] key][comment 注释名称][reference 外键][index 索名][charset 字符集][collate 校验规则]
其中：
not null ，非空约束，作用：规定某个字段的内容不能空。
default ，默认值约束，作用：定义字段中的默认值属性。
auto_increment， 自增约束，每个表最多只能有一个，作用：自动增加数值，属性列数据类型必须为整型数据类型，初始值为1，每增加一条记录自动加1。
unique， 唯一约束，作用：规定某个字段中的值，不可以重复，每个值都是唯一的。
primary key ，主键约束，作用：非空约束 + 唯一约束。
foreign key， 外键约束，作用：主表中的列与从表中的列建立联系。
check， 检查约束，作用：检查数值的范围。

• 根据约束的作用范围，约束可分为：
  • 列级约束: 只能作用在一个列上，定义在列的定义后面；
  • 表级约束: 可以作用在多个列上，不与列一起定义，定义在所有列的最后面。
• 有些约束既可以创建为列级约束，也可以创建为表级约束。

名称	位置	支持的约束类型	是否可以另起约束名
列级约束	列的后面	所有约束都支持，但外键没有效果	不可以
表级约束	所有列的最后面	默认约束和非空约束不支持，其他都支持	可以（但主键没有效果）

例如：

--1
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
--2
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(50),
    phone VARCHAR(20),
    INDEX(username),
    INDEX(email),
    INDEX(phone),
    COMMENT '用户表'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

三、数据表的修改

alter table 列名
{
   [add 新列名 数据类型 [列级完整性约束] [first|after 已存在字段名]]
   --增加新的属性列、约束
   --增加属性列分三种情况，第一列、在指定列之后、最后一列（默认）
   |[modify 列名1 新数据类型 [列级完整性约束条件][first|after 列名2]
   --修改属性列的数据类型或完整性约束
   |[change 旧列名 新列名 新数据类型]
   --修改属性列的名称或数据类型
   --change也可以只修改数据类型，实现modify的功能
   --方法是：新字段名和旧字段名同名。
   --即使不修改字段的数据类型，也不能省略新数据类型，可以将新类型设置成和原来一样。
   |[drop 列名|完整性约束名]
   --删除属性列或完整性约束
   |[rename [to]新表名]
   --对表重新命名
   |[engine=更新后的存储引擎名]
   --修改表的存储引擎
};

例如：

--1. 添加新列：
第一列alter table books add id int first;
在指定列（例如author列）之后alter table books add type varchar(100) after author;
默认在最后一列alter table books add press varchar(50);
--2.添加外键约束
   ALTER TABLE sc add foreign key(sno) references student(sno)on delete cascade on update cascade;
--3. 修改列的数据类型：
   ALTER TABLE table_name MODIFY column_name VARCHAR(50);
--4. 修改列的数据类型并指定位置：
   ALTER TABLE table_name MODIFY column_name VARCHAR(50) AFTER existing_column;
--5. 修改列名和数据类型：
   ALTER TABLE table_name CHANGE old_column_name new_column_name INT;
--6. 删除列：
   ALTER TABLE table_name DROP column_name;
   alter table sc drop foreign key sc_ibfk_1;--删除外键
--7. 重命名表：
   ALTER TABLE table_name RENAME TO new_table_name;
--8. 修改存储引擎：
   ALTER TABLE table_name ENGINE = InnoDB;

外键约束格式：

[constraint [constraint_name]] 
foreign key [index_name](column[,…n]) references ref_talbe(ref_col[,…n])
	[on delete {restrict | cascade | set null | no action | set default}]
	[on update {restrict | cascade | set null | no action | set default}]

• ON DELETE和ON UPDATE选项是通过使用级联引用完整性约束，定义当用户试图删除或更新现有外键指向的键时，数据库引擎执行的操作；
• RESTRICT表示拒绝对父表进行删除或更新操作。
• CASCADE表示如在父表中删除或更新了一行，则将在引用表中删除或更新相应的行。即级联删除或级联更新。
• SET NULL表示如果删除或更新了父表中的相应行，则会将构成外键的所有值设置为 NULL。若要执行此约束，外键列必须可为空值。
• NO ACTION与RESTRICT的作用相同，它是标准的SQL关键字。
• SET DEFAULT表示如果删除或更新了父表中的相应行，则会将构成外键的所有值设置为它们的默认值。

四、数据的插入、修改与删除

数据插入

• 在mySQL中通过insert语句插入新的数据，使用insert语句可以同时为表的所有属性插入数据，也可以为表的指定属性插入数据。
• insert语句可以同时插入多行元组。
• 如果设置了外键约束，表间已经建立了关联，在录入数据时应该先录入主表的记录，然后再录入从表的记录。

数据插入，可以使用以下语句：

insert 
into 表名 [(列名列表)] 
--列名：用来指定要向其中插入数据的列的名称。
values (取值列表1) [,(取值列表2), … , (取值列表n)]
--①不指定列名：必须为每个列都插入数据，且值的顺序必须与表定义的列的顺序一致。
--②指定列名：只需要为指定列插入数据，列名顺序自定义,数据与列名一一对应。

注：

• 取值列表中的数据值应该与列名列表中的属性列一一对应，并且数据类型也相同，不止一条数据时，用英文逗号隔开。
• 必须为表中所有定义为not null的列提供取值。
• 如果表中存在自增列，则系统可以自动维护自增列的值，不需要向自增列中插入值。
• 主键所在列的值不允许重复，也不允许出现空值

数据修改

数据修改，可以使用以下语句：

update 表名 
set 列名1=值1, 列名2=值2, … , 列名n=值n 
[where 条件表达式];

例如：

update sc
set grade=87
where sno=‘04001’ and cno=‘002’;
--将学号为04001，课程号为002的选课记录中的成绩修改为87。

update books 
set unitprice=unitprice*0.9; 
--修改books表中的unitprice值为调低为9折以后的价格。

数据删除

• 从数据表中删除数据一般使用DELETE语句，它允许用WHERE子句指定删除条件。
• 使用SQL语句删除数据可以分为两种情况：删除所有数据和删除指定数据。
• 删除主外键关系的主键表中的数据时，可能影响外键表的对应数据：级联删除、不允许删除……
• 使用TRUNCATE语句时，只需提供要清空数据的表名即可。执行该语句后，表中的所有数据将被删除，但表结构和定义将保持不变。

数据删除，可以使用以下语句：

--1
   DELETE 
   FROM 表名 
   [WHERE condition];
--如果不加where关键字，那么将删除整个表
--加上where关键字将只删除符合条件的数据行

--2
   TRUNCATE [TABLE] 表名;
--Truncate语句用于删除表中所有行，不带where条件
--TRUNCATE语句无法回滚操作
--一旦执行了TRUNCATE语句，数据将无法恢复

• truncate 是整体删除所有记录，速度更快，delete是逐条删除记录，速度较慢，truncate 和 delete 都不删除表结构;

• truncate table 语句清空表记录后会重新设置自增型字段的计数起始值为1;使用 delete 语句删除记录后自增字段的值并没有设置起始值，而是依次递增。

• truncate 语句的操作对象只能是数据表，不能是视图;而 delete 可以对表和视图进行记录的删除操作

• 当表被 truncate后，这个表和索引所占用的空间会恢复到初始大小，释放存储空间; delete 操作不会减少表或索引所占用的空间，不释放存储空间。

例如：

--在students表中插入一条id为101，姓名为Tom，年龄为20，性别为男的新记录
   INSERT 
   INTO students (id, name, age, gender) 
   VALUES (101, 'Tom', 20, 'Male');
--将students表中所有年龄小于18岁的学生的性别改为“男”
   UPDATE students 
   SET gender = 'Male' 
   WHERE age < 18;
--将students表中年龄大于22岁的所有学生的记录删除
   DELETE 
   FROM students 
   WHERE age > 22;

   delete from sc;
--删除sc表中的所有数据
--等价于
   truncate sc;

五、数据表复制与删除

数据表复制

数据表由表结构和内容两部分构成，在进行复制时，可以分别复制，也可以同时复制。

• 只复制表结构
• 同时复制表结构和内容
• 只复制表内容

   -- 只复制表结构
   CREATE TABLE 表名 [if not exists]LIKE 旧表名;
   或
   CREATE TABLE 表名 [if not exists] as 
   select 列名的列表 
   from 旧表名 
   where 1=2
   --WHERE 1=2 这个条件语句永远不会为真，因为 1 总是等于 1 而不是 2
   --因此，这个 WHERE 子句实际上会过滤掉所有的行
   --结果返回一个空的结果集
   
   --复制表结构和内容
   CREATE TABLE 表名 [if not exists] as 
   select 列名的列表 
   from 旧表名 
   [where 条件];
   
   -- 复制数据
   --同结构
   INSERT INTO 表名（字段列表） 
   SELECT * 
   FROM 旧表名;
   --不同结构
   INSERT INTO 表名（字段列表） 
   SELECT 字段列表 
   FROM 旧表名
   [where 条件];

例如：
将学生表s的表结构复制到student表中，student表中只复制学号、姓名和学院属性列。

create table student  as
select sno, sname, scollege from s where 1=2;
--如果不加where 1=2，则将数据和表结构一起复制，上面只复制了表结构
insert into student(sno,sname,scollege)
select sno,sname,scollege from s;

数据表删除

• 删除数据表是指将表的定义（表结构）和表中的数据全部删除，并释放被删除表所占用的存储空间，且不能恢复。
• 在删除表之前最好对表进行备份，以免造成无法挽回的后果。

   DROP TABLE [if not exists] 表名1[,表名2,···];

DROP TABLE语句将完全删除表及其数据，并且无法恢复。

truncate 、delete、drop的区别：

	删除内容	释放空间	不删除定义(结构)
truncate table	√	√	√
delete	√	×	√
drop	√	√	×