实验3 表的基本操作
表（Table）是包含数据库中所有数据的数据库对象。数据在表中的组织方式与在电子表格中相似，都是按照行和列的格式组织的，每行代表一条记录，每列代表记录中的一个字段。
【实验目的】
①掌握表的创建方法。
②掌握表结构的修改方法。
③掌握复制表结构的方法。
④掌握删除表的方法。
【知识要点】
（1）MySQL 数据类型
MySQL支持的数据类型包括数值类型、日期和时间类型、字符串类型、空间类型和JSON数据类型。
1）数值类型
数值类型包括整数类型（精确值）、定点类型（精确值）、浮点类型（近似值）和位值类型。
MySQL支持的整数类型所需的存储空间和范围见表3.1。
表3.1 MySQL 支持的整数类型所需的存储空间和范围
定点类型（精确值）包括DECIMAL和NUMERIC，MySQL以二进制格式进行存储，在列声明中，可以指定精度和小数位数。例如：salary DECIMAL（5， 2），其中5是精度，表示值存储的有效位数；2是小数位数，表示可以在小数点后存储的位数，因此可以存储在列中的值域为-999.99到999.99。
浮点类型（近似值）包括FLOAT和DOUBLE，单精度值FLOAT使用4个字节，双精度值DOUBLE使用8个字节。
位值类型有BIT，存储二进制位值，BIT（M）能存储M位的值，M可以是1到64。
2）日期和时间类型
日期和时间类型包括DATE、TIME、DATETIME、TIMESTAMP和YEAR。
DATE：用于具有日期部分但没有时间部分的值。MySQL以'YYYY-MM-DD'格式检索和显示值，支持的范围从' 1000-01-01'到 ' 9999-12-31'。
DATETIME：用于同时包含日期和时间部分的值。MySQL以'YYYY-MM-DD hh：mm：ss［.fraction］'格式检索和显示值。支持的范围从'1000-01-01 00：00：00.000000' 到 '9999-12-31 23：59：59.999999'。
TIMESTAMP：用于带时区同时包含日期和时间部分的值。有效范围是'1970-01-01 00：00：01.000000' UTC至'2038-01-19 03：14：07.999999' UTC。TIMESTAMP的值存储的是自'1970-01-01 00：00：00' UTC（格林尼治标准时间）到当前时间的秒数。MySQL将值从当前时区转换为UTC以进行存储，并从UTC转换回当前时区以进行检索。
TIME：MySQL以'hh：mm：ss'格式检索和显示值（或'hhh：mm：ss'格式表示大小时值）。 值的范围可以从'-838：59：59' 到 '838：59：59'。小时部分可能非常大，因为该类型不仅可以用于表示一天中的时间（必须小于24小时），还可以表示两个事件之间的经过时间或时间间隔（可能远大于24小时，甚至为负数）。
YEAR：该类型是用于表示年份值的1字节类型。MySQL 以 YYYY 格式显示值，范围为 '1901'到'2155'，还有'0000'。
3）字符串类型
字符串类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
CHAR和VARCHAR：这两个数据类型相似，但在存储和检索方式上有所不同，在最大长度和是否保留尾随空格方面也有所不同。CHAR和VARCHAR声明的长度表示要存储的最大字符数，CHAR是固定长度，会根据定义的长度分配空间，长度可以是0~255。比如CHAR（30）表示最多可以容纳30 个字符，存储不足30个字符的值时，将使用指定长度的空格对值进行右填充。CHAR适合存储很短的字符串，或者所有的值都接近同一个长度。VARCHAR用于存储可变长度的字符串，比定长类型更节省空间，长度可以是0~65535，VARCHAR需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则只使用1个字节表示；如果列长度大于255字节，则需要使用2个字节表示长度。
BINARY和VARBINARY：它们存储的是二进制字符串。二进制字符串和常规的字符串非常相似，但是二进制字符串存储的是字节码而不是字符。
BLOB和TEXT：都是为了存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储。二进制类型包括TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB；字符类型包括TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理，当BLOB和TEXT值太大时，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际值。BLOB和TEXT之间仅有的不同是，BLOB存储的是二进制数据，没有排序规则和字符集；而TEXT存储的是字符，有排序规则和字符集。
ENUM类型即枚举类型，它的取值范围需要在创建表时通过枚举方式（一个个地列出来）显式指定。对1至255个成员的枚举需要1个字节存储；对于255至65 535个成员，需要2个字节存储，最多允许有65 535个成员。ENUM忽略了大小写，也支持通过下标（从1开始，下标越界时报错）插入数据，其中特殊值0表示空值。
SET类型是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时，各成员之间需要用逗号（“，”）间隔开，所以SET成员值本身不能包含逗号。ENUM只能取一个值，而SET可以取多个值。
4）空间数据类型
MySQL的空间类型是建立在OpenGIS Geometry Model之上，其中Geometry 是顶级类，它具有所有类型都拥有的属性。
MySQL空间数据类型主要有两类。一类是代表单个值的GEOMETRY、POINT、LINESTRING、POLYGON。其中GEOMETRY可以是任意（POINT、LINESTRING 和POLYGON）类型；另一类是集合类型，如MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION。集合类型中的元素必须为同一参考系。
5）JSON数据类型
直接存储JSON格式的字符串，JSON数据列会自动验证JSON的数据格式，如果格式不正确会报错。JSON数据类型会采取最优化的存储格式，会把JSON格式的字符串转换成内部格式，以便能够快速地读取其中的元素。
（2）创建表的语法格式
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition, ...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition, ...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part, ...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part, ...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part, ...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part, ...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
reference_definition
| check_constraint_definition
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)}]
[VISIBLE | INVISIBLE]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
[SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
[STORAGE {DISK | MEMORY}]
[reference_definition]
[check_constraint_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[VISIBLE | INVISIBLE]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
[check_constraint_definition]
}
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
|ENGINE_ATTRIBUTE [=] 'string'
|SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
REFERENCES tbl_name (key_part, ...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] {NO | FIRST | LAST}
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_SAMPLE_PAGES [=] value
[COMMENT [=] 'string']
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[COMMENT [=] 'string']
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
SELECT ... (Some valid select or union statement)
说明：其中常用的子句或参数解释如下。
①TEMPORARY：所创建的是临时表，只能在当前会话中可见，会话关闭后，临时表会被自动删除。
②IF NOT EXISTS：判断数据库中是否已经存在同名的表，如果未加此项，当数据库中已经存在同名的表时，创建表语句会出错，加上此项后，可以阻止错误发生。(https://www.daowen.com)
③DATA_TYPE：定义字段的数据类型。字符串类型（CHAR、VARCHAR）需要指定长度；整型（INT、BIGINT、TINYINT等）和日期类型（DATETIME、TIMESTAMP等）只需要指定类型，不需要指定长度；decimal类型需要指定精度和小数位数。
④PRIMARY KEY：指定表的主键。
⑤AUTO_INCREMENT：指定字段为自增字段，该字段的类型必须为INT或BIGINT才能设置为自增字段。
⑥NOT NULL | NULL：设置字段能否取空值。
⑦DEFAULT：设置字段的默认值。
⑧INDEX：创建索引。index_name指定索引名，该参数可以省略，如果省略则索引名就是字段名。
⑨FOREIGN KEY：指定外键。
⑩ENGINE：设置引擎类型，常用的有InnoDB和myISAM引擎。
⑪DEFAULT CHARACTER SET：设置表所用的字符集。
（3）查看已创建的表的语法格式
SHOW [EXTENDED] [FULL] TABLES
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
（4）查看表结构的语法格式
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
[LIKE 'pattern' | WHERE expr]
或者
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
（5）复制表结构的语法格式
①只复制表结构，包括主键、索引，但不会复制表数据。
CREATE TABLE new_tbl LIKE orig_tbl;
②复制表结构及全部数据，但不会复制主键、索引等。
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;
③如果既要复制包括主键、索引的表结构，也要复制表数据，可以分两步完成，先复制表结构，再插入数据。
（6）修改表结构的语法格式
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
[partition_options]
alter_option: {
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ADD [COLUMN] (col_name column_definition, ...)
| ADD {INDEX | KEY} [index_name]
[index_type] (key_part, ...) [index_option] ...
| ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
(key_part, ...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part, ...)
[index_option] ...
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part, ...)
[index_option] ...
| ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
reference_definition
| ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
| DROP {CHECK | CONSTRAINT} symbol
| ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
| ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
| ALTER [COLUMN] col_name {
SET DEFAULT {literal | (expr)}
| SET {VISIBLE | INVISIBLE}
| DROP DEFAULT
}
| ALTER INDEX index_name {VISIBLE | INVISIBLE}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST | AFTER col_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| {DISABLE | ENABLE} KEYS
| {DISCARD | IMPORT} TABLESPACE
| DROP [COLUMN] col_name
| DROP {INDEX | KEY} index_name
| DROP PRIMARY KEY
| DROP FOREIGN KEY fk_symbol
| FORCE
| LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ORDER BY col_name [, col_name] ...
| RENAME COLUMN old_col_name TO new_col_name
| RENAME {INDEX | KEY} old_index_name TO new_index_name
| RENAME [TO | AS] new_tbl_name
| {WITHOUT | WITH} VALIDATION
}
partition_options:
partition_option [partition_option] ...
partition_option: {
ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| DISCARD PARTITION {partition_names | ALL} TABLESPACE
| IMPORT PARTITION {partition_names | ALL} TABLESPACE
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING
}
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
}
table_options:
table_option [[,] table_option] ...
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] {NO | FIRST | LAST}
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
| UNION [=] (tbl_name [, tbl_name] ...)
}
partition_options:
(see CREATE TABLE options)
（7）删除表的语法格式
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
