实验3
表的基本操作

表（Table）是包含数据库中所有数据的数据库对象。数据在表中的组织方式与在电子表格中相似，都是按照行和列的格式组织的，每行代表一条记录，每列代表记录中的一个字段。

【实验目的】

①掌握表的创建方法。

②掌握表结构的修改方法。

③掌握复制表结构的方法。

④掌握删除表的方法。

【知识要点】

（1）MySQL数据类型

MySQL支持的数据类型包括数值类型、日期和时间类型、字符串类型、空间类型和JSON数据类型。

1）数值类型

数值类型包括整数类型（精确值）、定点类型（精确值）、浮点类型（近似值）和位值类型。

MySQL支持的整数类型所需的存储空间和范围见表3.1。

表3.1　MySQL支持的整数类型所需的存储空间和范围

定点类型（精确值）包括DECIMAL和NUMERIC，MySQL以二进制格式进行存储，在列声明中，可以指定精度和小数位数。例如：salaryDECIMAL（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-DDhh：mm：ss［.fraction］'格式检索和显示值。支持的范围从'1000-01-0100：00：00.000000'到'9999-12-3123：59：59.999999'。

TIMESTAMP：用于带时区同时包含日期和时间部分的值。有效范围是'1970-01-0100：00：01.000000'UTC至'2038-01-1903：14：07.999999'UTC。TIMESTAMP的值存储的是自'1970-01-0100：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至65535个成员，需要2个字节存储，最多允许有65535个成员。ENUM忽略了大小写，也支持通过下标（从1开始，下标越界时报错）插入数据，其中特殊值0表示空值。

SET类型是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时，各成员之间需要用逗号（“，”）间隔开，所以SET成员值本身不能包含逗号。ENUM只能取一个值，而SET可以取多个值。

4）空间数据类型

MySQL的空间类型是建立在OpenGISGeometryModel之上，其中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_namecolumn_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 [ NOTNULL|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 ] [ NOTNULL|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 [ = ] 'absolutepathtodirectory'

| 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 selectorunion statement )

说明：其中常用的子句或参数解释如下。

①TEMPORARY：所创建的是临时表，只能在当前会话中可见，会话关闭后，临时表会被自动删除。

②IFNOTEXISTS：判断数据库中是否已经存在同名的表，如果未加此项，当数据库中已经存在同名的表时，创建表语句会出错，加上此项后，可以阻止错误发生。

③DATA_TYPE：定义字段的数据类型。字符串类型（CHAR、VARCHAR）需要指定长度；整型（INT、BIGINT、TINYINT等）和日期类型（DATETIME、TIMESTAMP等）只需要指定类型，不需要指定长度；decimal类型需要指定精度和小数位数。

④PRIMARYKEY：指定表的主键。

⑤AUTO_INCREMENT：指定字段为自增字段，该字段的类型必须为INT或BIGINT才能设置为自增字段。

⑥NOTNULL|NULL：设置字段能否取空值。

⑦DEFAULT：设置字段的默认值。

⑧INDEX：创建索引。index_name指定索引名，该参数可以省略，如果省略则索引名就是字段名。

⑨FOREIGNKEY：指定外键。

⑩ENGINE：设置引擎类型，常用的有InnoDB和myISAM引擎。

⑪DEFAULTCHARACTERSET：设置表所用的字符集。

（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_namecolumn_definition

[ FIRST | AFTER col_name ]

| ADD [ COLUMN ] ( col_namecolumn_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_namenew_col_namecolumn_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_namecolumn_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 [ = ] ' absolutepathtodirectory '

| 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 ]

实验3 表的基本操作

【实验目的】

【知识要点】

（1）MySQL数据类型

1）数值类型

2）日期和时间类型

3）字符串类型

4）空间数据类型

5）JSON数据类型

（2）创建表的语法格式

（3）查看已创建的表的语法格式

（4）查看表结构的语法格式

（5）复制表结构的语法格式

（6）修改表结构的语法格式

（7）删除表的语法格式

实验3
表的基本操作