第3章 数据表的管理和表中数据操纵
3.1 MySQL数据类型
- 数字类型:数字类型包括整数类型和数值类型
- 整数类型:TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT
- 数值类型:精确数值型DECIMAL和近似数值型FLOAT、DOUBLE、REAL
- 字符串类型:字符串类型用于存储字符串数据,包括CHAR、VARCHAR和TEXT
- TEXT类型用于表示非二进制字符串,进一步分为TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
- CHAR和VARCHAR类型都用来表示字符串数据
- VARCHAR和TEXT类型是变长类型,其存储需求取决于字符的实际长度
- 时间日期类型:表示时间和日期的类型包括TIME、DATE、YEAR、DATETIME和TIMESTAMP
- 二进制类型:二进制类型包括BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
- BIT是位字段类型,如果输入的数据值长度小于设定长度,则在数据值的左边用 0 填充
- BINARY是定长的二进制数据类型,VARBINARY是非定长的二进制数据类型
- BLOB可用于存储可变大小的数据。TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 4种类型的
区别在于可容纳存储范围不同
- 其他类型:MySQL支持两种复合数据类型ENUM和SET
- ENUM类型:ENUM类型只允许在给定的集合中取一个值,因此,用户可以在处理相互排斥的数据时使用此数据类型
- SET:SET类型可以从给定集合中取得多个值
3.2 MySQL数据表管理
3.2.1 创建数据表
-
使用MySQL Workbench创建数据表
- 在导航区Navigator的Schema选项卡下,右击要创建新表的数据库下的“Tables”节点,从弹出的右键菜单中,选择Create Table选项
- 打开定义数据表结构标签后,可在此设置表名、字符集、存储引擎等。双击 “Column Name” 下的单元格,可进行字段名、字段类型、约束的设置
- “Table Name”,即要创建的表名,最多可有64个字符,如s、sc、c等,不区分大小写,不允许重名,不能使用SQL中的关键字
- “Charset/Collation”,字符集/校对规则,一般采用默认设置就够了
- “Engine”,存储引擎,一般也是采用默认设置
- “Comments”,注释表名
- “Column Name”,表中某个字段名,同一表中不允许有重名的字段
- “Data Type”,数据类型,定义字段可存放数据的类型
- “PK”,定义字段是否主码;“NN”,定义字段是否非空;“UQ”,定义字段是否唯一;“B”, 二进制(比text更大);“UN”, 无符号数(非负数);“ZF”, 填充零;“AI”, 当插入行时,字段值会自增,只有整型数据类型能够设置;“G”,基于其他字段的公式生成值的字段
- Defalut/Expression,表示该字段的默认值
-
使用CREATE TABLE语句创建数据表
- CREATE[TEMPORARY]TABLE[IF NOT EXIST]<表名>
[([<字段定义>],…|[<索引定义>])]
[table_option] [select_statement]- TEMPORARY:若使用该关键字,则创建的是临时表
- IF NOT EXIST:用于判断数据库中是否已经存在同名的表
- <字段定义>:<字段名> <数据类型> [DEFAULT] [AUTO_INCREMENT] [COMMENT ‘String’] [{<列约束>}]
- <索引定义>:为表中相关字段指定索引
- table_option:表选项
- select_statement:定义表的查询语句
- CREATE[TEMPORARY]TABLE[IF NOT EXIST]<表名>
3.2.2 定义表的约束
-
数据的完整性是指保护数据库中数据的正确性、有效性和相容性,防止错误的数据进入数据库造成无效操作
-
约束主要包括NULL/NOT NULL约束、UNIQUE约束、PRIMARY KEY约束、FOREIGN KEY约束和CHECK约束
-
NULL/NOT NULL约束
<字段名> <数据类型> [NULL|NOT NULL] -
UNIQUE约束
UNIQUE约束指所有记录中字段的值不能重复出现,用于保证数据表在某一字段或多个字段的组合上取值必须唯一-
列约束
<字段名> <数据类型> UNIQUE -
表约束
UNIQUE(<字段名>[{,<字段名>}]) -
一个表中可以允许有多个UNIQUE约束,UNIQUE约束可以定义在多个字段上
-
使用UNIQUE约束的字段允许为NULL值
-
UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引,默认为非聚集索引
-
-
PRIMARY KEY约束
- PRIMARY KEY约束用于定义基本表的主码,起唯一标识作用,保证数据表中记录的唯一性
- 一张表只能有一个PRIMARY KEY约束,可以作用于一个字段,也可以作用于多个字段的组合
- 列约束
<字段名> <数据类型> PRIMARY KEY - 表约束
[CONSTRAINT <约束名>] PRIMARY KEY (<字段名>[{,<字段名>}]) - PRIMARY KEY约束与UNIQUE约束类似,通过建立唯一索引来保证基本表在主码字段取值的唯一性,但它们之间存在以下区别:
- 在一个基本表中只能定义一个PRIMARY KEY约束,但可定义多个UNIQUE约束
- 对于指定为PRIMARY KEY的一个字段或多个字段的组合,其中任何一个字段都不能出现NULL值,而对于UNIQUE所约束的唯一码,则允许为NULL,但是只能有一个空值
- 不能为同一个字段或一组字段,既定义UNIQUE约束,又定义PRIMARY KEY约束
-
FOREIGN KEY约束
- FOREIGN KEY约束用于在两个数据表A和B之间建立连接
- 通过FOREIGN KEY约束可以保证两表间的参照完整性
- [CONSTRAINT <约束名>] FOREIGN KEY (<从表A中字段名>[{,<从表A中字段名>}])
REFERENCES <主表B表名> (<主表B中字段名>[{,<主表B中字段名>})
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]- RESTRICT:拒绝对主表B的删除或更新操作。若有一个相关的外码值在主表B中,则不允许删除或更新B表中主要码值
- CASCADE:在主表B中删除或更新时,会自动删除或更新从表A中对应的记录
- SET NULL:在主表B中删除或更新时,将子表中对应的外码值设置为NULL
- NO ACTION:NO ACTION和RESTRICT相同,InnoDB拒绝对主表B的删除或更新操作
- CHECK约束
- CHECK约束用来检查数据表中字段值所允许的范围
- CHECK (<条件>)
- “条件”用于指定需要检查的限定条件
- MySQL可以使用简单的表达式来实现CHECK约束,也可以使用复杂的表达式作为限定条件
- 若将CHECK约束子句置于所有字段的定义以及主码约束和外码定义之后,则这种约束也称为CHECK的表约束
-
3.2.3 修改数据表
-
使用MySQL Workbench修改数据表
- 1、在MySQL Workbench中的“SCHEMAS”中,选中相应的数据库,从中找到要修改的数据表,右击该表,从弹出的快捷菜单中选择“Alter Table”菜单项
- 2、可在显示的定义数据表结构标签中修改表名、字符集、存储引擎等,点击Apply按钮即可提交修改后的数据表定义
-
使用SQL语句修改表
-
MySQL使用SQL语句中的ALTER TABLE语句来修改表名、修改字段数据类型、修改字段名、添加和删除字段、更改表的存储引擎等
-
ADD
-
用于增加新字段和完整性约束
-
ALTER TABLE <表名> ADD [<新字段名>、<数据类型>] [<完整性约束定义>] [FIRST|AFTER已有字段名]
-
添加多个字段时不能指定位置关系,只能添加在数据表的末尾
-
添加多个字段时必须用小括号括起来
-
在增加NOT NULL约束时,语法结构不同于其他完整性约束
-
ALTER TABLE <数据表名>
CHANGE [COLUMN] <字段名>
<字段名> <数据类型> NOT NULL;
-
-
-
RENAME
-
RENAME方式用于修改表名
-
ALTER TABLE <旧表名>
RENAME [TO] <新表名>;
-
修改表名并不修改数据表结构,因此,修改表名后的数据表结构与修改表名之前一样
-
-
CHANGE
-
CHANGE方式用于修改字段名
-
ALTER TABLE <表名>
CHANGE <旧字段名> <新字段名> <新数据类型>;
-
即使不需要修改字段的数据类型,也不能省略<新数据类型>,只需把数据类型设置为与原字段一致即可
-
-
MODIFY
-
MODIFY方式可用于修改字段数据类型和字段排序
-
ALTER TABLE <表名>
MODIFY <字段名1> <数据类型> [FIRST|AFTER 字段名2];
-
在修改字段数据类型时,“数据类型”指修改后字段的新数据类型
-
在修改字段排序时,若使用FIRST,则将“字段名1”修改为表的第一个字段;若使用AFTER,则将“字段名1”插入“字段名2”后面
-
在修改字段排序时,“数据类型”不可省略
-
-
ENGINE
-
ENGINE用于修改表的存储引擎
-
ALTER TABLE <表名>
ENGINE= <修改后存储引擎名>;
-
若被修改表有外码,则存储引擎不能由InnoDB修改为MyISAM,因为MyISAM不支持外码
-
-
DROP
-
DROP方式可用于删除字段和完整性约束
-
ALTER TABLE <旧表名>
DROP <字段名>
-
ALTER TABLE <表名>
DROP CONSTRAINT <约束名>
-
-
-
3.2.4 删除数据表
- 使用MySQL Workbench删除数据表
- 在MySQL Workbench中,右击要删除的表,从弹出的快捷菜单中选择“Drop Table”命令,弹出“Drop Table”对话框,单击“Review SQL”,可以查看删除表对应的SQL语句,单击“Drop Now”,即可删除表。
- 使用SQL语句DROP TABLE删除一个或多个表
- DROP TABLE [IF EXISTS] <表名>;
3.2.5 查看数据表
-
查看已创建的数据表
- SHOW TABLES;
-
查看数据表结构
- 使用MySQL Workbench查看数据表结构
- 在MySQL Workbench中的“SCHEMAS”中,选中相应的数据库,从中找到要查看的数据表,右击该表,从弹出的快捷菜单中选择“Table Inspector”菜单项,显示“数据库.表名”标签
- 使用DESCRIBE(DESC)和SHOW CREATE TABLE语句查看数据表结构
-
查看数据表中数据
- 使用MySQL Workbench查看数据表中数据
- 在MySQL Workbench中的“SCHEMAS”中,右击要查看数据的表,从弹出的快捷菜单中选择“Select Rows-Limit 1000”(选择前1000行)命令,系统显示数据表中的前1000条数据
3.3 数据表中数据的操纵
3.3.1 向数据表中添加数据
- 使用MySQL Workbench添加数据
- 打开待添加数据的数据表,单击鼠标右键,从弹出的快捷菜单中选择“Select Rows-Limit 1000”(选择前1000行)命令,在查询结果中单击空白行,分别向各字段中输入新数据
- 输入完毕后,单击“Apply”按钮可打开SQL脚本审核对话框,检查无误后单击“Apply”按钮,即可完成数据的添加
- 使用SQL语句向数据表中添加数据
- MySQL使用INSERT/REPLACE语句添加数据,可以添加一行记录的所有数据值,也可以添加一行记录的部分数据值,还可以添加多行数据
- 添加一行新记录
- INSERT|REPLACE INTO <表名>[(<字段名1>[,<字段名2>…])] VALUES(<值>);
- 必须用逗号将各个数据分开,字符型数据要用单引号括起来
- 如果INTO子句中没有指定字段名,则新添加的记录必须在每个字段上均有值,且排序一致
- 使用REPLACE语句添加记录时,如果要添加的新记录的主码或UNIQUE约束的字段值已存在于表中,则需删除已有记录后再添加新纪录
- INSERT|REPLACE INTO <表名>[(<字段名1>[,<字段名2>…])] VALUES(<值>);
- 添加一行记录的部分数据值
- INTO子句中没有出现的字段,新添加的记录在这些字段上将被赋NULL值。但在表定义时有NOT NULL约束的字段不能取NULL值,添加记录时必须给其赋值。
- 添加多行记录
- INSERT|REPLACE INTO <表名> [(<字段名1>[,<字段名2>…])] VALUES(<值列表1>[,<值列表2>…])
- 添加一行新记录
3.3.2 修改数据表中数据
-
使用MySQL Workbench修改数据表中数据
- 打开待修改数据的数据表,单击鼠标右键,从弹出的快捷菜单中选择“Select Rows-Limit 1000”(选择前1000行)命令,在打开标签的查询结果中查看表中数据,直接双击要修改的字段,或选中要修改的行,单击“Edit current row”按钮,在修改处直接输入新数据即可
- 修改完毕后,单击“Apply”按钮可打开SQL脚本审核对话框,检查无误后单击“Apply”按钮,即可完成数据的修改
-
使用SQL语句修改数据表中数据
-
UPDATE <表名>
SET <字段名>=<表达式>[,<字段名>=<表达式>]…
[WHERE <条件>]
- 修改一行记录
- 修改多行记录
-
3.3.3 删除数据表中数据
-
使用MySQL Workbench删除数据表中数据
- 打开待删除数据的数据表,单击鼠标右键,从弹出的快捷菜单中选择“Select Rows-Limit 1000”(选择前1000行)命令,在查询结果中查看表中数据,选中要删除的行,单击“Delete selected rows”按钮即可
- 删除完毕后,单击“Apply”按钮可打开SQL脚本审核对话框,检查无误后单击“Apply”按钮,即可完成数据的删除
-
使用SQL语句删除数据表中数据
-
DELETE
FROM <表名>
[WHERE <条件>]
- 删除一行记录
- 删除多行记录
-