（二）数据表的基本操作

一、创建表

在创建完数据库之后，接下来我们就需要创建数据表。创建数据表是指在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性约束的过程。 数据表属于数据库，在创建数据表之前，应该使用语句"USE <数据库名>”指定操作是从哪个数据库中进行，如果没有选择数据库，会报错。

1.没有任何约束的表操作

mysql> use mytest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | test | +------------------+ 1 row in set (0.00 sec) mysql> create table test1 (id int,name varchar(20)); #创建表 Query OK, 0 rows affected (0.05 sec) mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | test | | test1 | +------------------+ 2 rows in set (0.00 sec) mysql> desc test1; #查看表结构 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into test1(id) values (1); #插入数据 Query OK, 1 row affected (0.00 sec) mysql> insert into test1(name) values ("zhang"); #插入数据 Query OK, 1 row affected (0.00 sec) mysql> select * from test1; #查询表 +------+-------+ | id | name | +------+-------+ | 1 | NULL | | NULL | zhang | +------+-------+ 2 rows in set (0.00 sec) mysql>

2.表约束的语法结构

mysql> help CREATE TABLE; Name: 'CREATE TABLE' Description: Syntax: 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 [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) } 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 (expr) } column_definition: { data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [COLLATE collation_name] [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}] [STORAGE {DISK | MEMORY}] [reference_definition] | data_type [COLLATE collation_name] [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] } data_type: (see https://dev.mysql.com/doc/refman/5.7/en/data-types.html) key_part: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: { KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' } 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: { 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 | 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} | 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: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONS num] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) } [SUBPARTITIONS num] ] [(partition_definition [, partition_definition] ...)] partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'string' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [(subpartition_definition [, subpartition_definition] ...)] subpartition_definition: SUBPARTITION logical_name [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'string' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] query_expression: SELECT ... (Some valid select or union statement) CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table. By default, tables are created in the default database, using the InnoDB storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist. MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables. For information about the physical representation of a table, see https://dev.mysql.com/doc/refman/5.7/en/create-table-files.html. URL: https://dev.mysql.com/doc/refman/5.7/en/create-table.html mysql>

简单来说，可写成下面的语法格式

CREATE TABLE <表名>（字段名1,数据类型 [列级别约束条件][默认值], 字段名2, 数据类型 [列级别约束条件][默认值],......):

3.主键约束（PRI）

主键约束要求主键列的数据唯一，并且不允许为空。

（1）方法一：

mysql> create table tb_emp2( -> id int(11) primary key, -> name varchar(20), -> deptID int(11), -> salary float); Query OK, 0 rows affected (0.02 sec) mysql> desc tb_emp2; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | deptID | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> insert into tb_emp2(name) values ("zhang"); ERROR 1364 (HY000): Field 'id' doesn't have a default value #错误1364 (HY000):字段“id”没有默认值。原因：id为主键，不能为空 mysql> mysql> insert into tb_emp2(id) values ("1"); Query OK, 1 row affected (0.00 sec) mysql> select * from tb_emp2; +----+------+--------+--------+ | id | name | deptID | salary | +----+------+--------+--------+ | 1 | NULL | NULL | NULL | +----+------+--------+--------+ 1 row in set (0.00 sec) #插入多个值 mysql> insert into tb_emp2(id) values (2),(3); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tb_emp2; +----+------+--------+--------+ | id | name | deptID | salary | +----+------+--------+--------+ | 1 | NULL | NULL | NULL | | 2 | NULL | NULL | NULL | | 3 | NULL | NULL | NULL | +----+------+--------+--------+ 3 rows in set (0.00 sec) mysql> insert into tb_emp2(id) values (2); ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' #错误1062 (23000):键“PRIMARY”的条目“2”重复 mysql>

（2）方法二：

mysql> create table tb_emp3( -> id int, -> name varchar(20), -> deptId int, -> salary float, -> primary key(id) -> ); Query OK, 0 rows affected (0.06 sec) mysql> desc tb_emp3; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | deptId | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> desc tb_emp2; #对比tb_emp3，效果相同 +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | deptID | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql>

（3）多字段的联合主键

语法：

PRIMARY KEY[字段1，字段2，...]

应用：

mysql> create table tb_emp4; ERROR 1113 (42000): A table must have at least 1 column mysql> create table tb_emp4( -> name varchar(20), -> deptId int(11), -> salary float, -> primary key (name,deptId) -> ); Query OK, 0 rows affected (0.57 sec) mysql> desc tb_emp4; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name | varchar(20) | NO | PRI | NULL | | | deptId | int(11) | NO | PRI | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.02 sec) #插入值时要同时插入两项关于主键的值，否则会报错 mysql> insert into tb_emp4 (name) values ("zhang"); ERROR 1364 (HY000): Field 'deptId' doesn't have a default value mysql> insert into tb_emp4 (salary) values (1000); ERROR 1364 (HY000): Field 'name' doesn't have a default value mysql> insert into tb_emp4 (name,deptId) values ("zhang",123); Query OK, 1 row affected (0.50 sec) mysql> select * from tb_emp4; +-------+--------+--------+ | name | deptId | salary | +-------+--------+--------+ | zhang | 123 | NULL | +-------+--------+--------+ 1 row in set (0.00 sec) #当两个值都插入时，两个不能同时重复，但其中一个可以重复 mysql> insert into tb_emp4 (name,deptId) values ("zhang",456); Query OK, 1 row affected (0.49 sec) mysql> insert into tb_emp4 (name,deptId) values ("li",123); Query OK, 1 row affected (0.00 sec) mysql> select * from tb_emp4; +-------+--------+--------+ | name | deptId | salary | +-------+--------+--------+ | li | 123 | NULL | | zhang | 123 | NULL | | zhang | 456 | NULL | +-------+--------+--------+ 3 rows in set (0.00 sec) mysql> insert into tb_emp4 (name,deptId) values ("zhang",123); ERROR 1062 (23000): Duplicate entry 'zhang-123' for key 'PRIMARY' mysql>

4.外键约束（MUL）

外键用来在两个表数据之间建立连接，它可以是一列或者多列。

语法：

[CONSTRAINT <外键名> ] FOREIGN KEY [字段名1，字段名2...] REFERENCES <主表名> 主键列1 [主键列2...]

应用

mysql> create table tb_dept1( -> id int primary key, -> name varchar(22) not null, -> location varchar(50) -> ); Query OK, 0 rows affected (0.58 sec) mysql> desc tb_dept1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | NO | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> create table tb_emp5( -> id int primary key, -> name varchar(25), -> deptId int, -> salary float, -> constraint fk_emp_dept1 foreign key (deptId) references tb_dept1(id) -> ); Query OK, 0 rows affected (0.42 sec) mysql> insert into tb_dept1 values (1,"zhang","beijing"),(2,"li","hebei"),(3,"zhao","tianjin"); Query OK, 3 rows affected (0.49 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into tb_emp5 values (1,"liu",5,1000); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mytest`.`tb_emp5`, CONSTRAINT `fk_emp_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)) #报错原因：从表外键5在主表tb_dept1表中没有，必须插入有的才可以 mysql> select * from tb_dept1; +----+-------+----------+ | id | name | location | +----+-------+----------+ | 1 | zhang | beijing | | 2 | li | hebei | | 3 | zhao | tianjin | +----+-------+----------+ 3 rows in set (0.00 sec) mysql> insert into tb_emp5 values (1,"liu",3,1000); Query OK, 1 row affected (0.00 sec) mysql>

5.非空约束

（1）not null：值可以重复，但必须写

字段名 数据类型 not null

应用：

mysql> create table tb_emp6( -> id int primary key, -> name varchar(20) not null, -> deptId int, -> salary float -> ); Query OK, 0 rows affected (0.52 sec) mysql> desc tb_emp6; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | deptId | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> insert into tb_emp6 (id) values (1); ERROR 1364 (HY000): Field 'name' doesn't have a default value #报错原因：name列不允许为空，必须输入值，该值可以重复 mysql> insert into tb_emp6 (id,name) values (1,"zhang"); Query OK, 1 row affected (0.00 sec) mysql> insert into tb_emp6 (id,name) values (2,"zhang"); Query OK, 1 row affected (0.01 sec) mysql> select * from tb_emp6; +----+-------+--------+--------+ | id | name | deptId | salary | +----+-------+--------+--------+ | 1 | zhang | NULL | NULL | | 2 | zhang | NULL | NULL | +----+-------+--------+--------+ 2 rows in set (0.00 sec) mysql>

（2）UNIQUE：值不可以重复，可不写（UNI）

[CONSTRATIN <约束名>] UNIQUE (<字段名>)

应用：

mysql> create table tb_dept3( -> id int primary key, -> name varchar(22), -> location varchar(50), -> constraint STH unique (name) -> ); Query OK, 0 rows affected (0.57 sec) mysql> desc tb_dept3; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | YES | UNI | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into tb_dept3(id,name) values (1,"zhang"); Query OK, 1 row affected (0.00 sec) mysql> insert into tb_dept3(id,name) values (2,"zhang"); ERROR 1062 (23000): Duplicate entry 'zhang' for key 'STH' #报错原因：插入的值不可重复 mysql> insert into tb_dept3(id) values (3); Query OK, 1 row affected (0.01 sec) #可以不插入name的值 mysql> select * from tb_dept3; +----+-------+----------+ | id | name | location | +----+-------+----------+ | 1 | zhang | NULL | | 3 | NULL | NULL | +----+-------+----------+ 2 rows in set (0.00 sec) mysql>

6.默认约束

默认约束指定某列的默认值。

语法：

字段名 数据类型 DEFAULT 默认值

应用

mysql> create table tb_emp7( -> id int primary key, -> name varchar(20) not null, -> deptId int default 111, -> salary float, -> info varchar(60)); Query OK, 0 rows affected (0.52 sec) mysql> insert into tb_emp7(id,name) values (1,"zhang"); Query OK, 1 row affected (0.00 sec) mysql> insert into tb_emp7(id,name,deptId) values (2,"li",123); Query OK, 1 row affected (0.01 sec) #不插入deptId的值，其拥有默认值，若指定插入的值，默认值则会被顶替 mysql> select * from tb_emp7; +----+-------+--------+--------+------+ | id | name | deptId | salary | info | +----+-------+--------+--------+------+ | 1 | zhang | 111 | NULL | NULL | | 2 | li | 123 | NULL | NULL | +----+-------+--------+--------+------+ 2 rows in set (0.00 sec) mysql>

7.设置表的属性值自动增加

语法：

字段名 数据类型 AUTO_INCREMENT

应用：

mysql> create table tb_emp8( -> id int primary key auto_increment, -> name varchar(20) not null, -> deptId int, -> salary float); Query OK, 0 rows affected (0.53 sec) mysql> insert into tb_emp8(name) values ("zhang"); Query OK, 1 row affected (0.00 sec) mysql> select * from tb_emp8; #即使没有插入id的值，也会自动添加 +----+-------+--------+--------+ | id | name | deptId | salary | +----+-------+--------+--------+ | 1 | zhang | NULL | NULL | +----+-------+--------+--------+ 1 row in set (0.00 sec) mysql> insert into tb_emp8(name) values ("li"),("zhao"); #id的值，会自动增加 Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tb_emp8; +----+-------+--------+--------+ | id | name | deptId | salary | +----+-------+--------+--------+ | 1 | zhang | NULL | NULL | | 2 | li | NULL | NULL | | 3 | zhao | NULL | NULL | +----+-------+--------+--------+ 3 rows in set (0.00 sec) mysql>

二、查看数据表结构

1.查看表基本结构语句DESCRIBE

语法：

DESCRIBE 表名； 或者DESC 表名；

应用：

mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | tb_dept1 | | tb_dept3 | | tb_emp2 | | tb_emp3 | | tb_emp4 | | tb_emp5 | | tb_emp6 | | tb_emp7 | | tb_emp8 | | test | | test1 | +------------------+ 11 rows in set (0.00 sec) mysql> desc test; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) mysql> describe test; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql>

2.查看表详细结构语句

语法：

SHOW CREATE TABLE <表名>; #有分号 SHOW CREATE TABLE <表名\G> #无分号

应用

mysql> show create table test; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql>

三、修改数据表

1.修改表名

语法：

ALTER TABLE <旧表名> RENAME <新表名>;

应用：

mysql> alter table test rename my_test; Query OK, 0 rows affected (0.03 sec) mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | my_test | | tb_dept1 | | tb_dept3 | | tb_emp2 | | tb_emp3 | | tb_emp4 | | tb_emp5 | | tb_emp6 | | tb_emp7 | | tb_emp8 | | test1 | +------------------+ 11 rows in set (0.00 sec) mysql> desc my_test; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql>

2.修改字段的数据类型

语法：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

应用：

mysql> desc tb_dept1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | NO | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from tb_dept1; +----+-------+----------+ | id | name | location | +----+-------+----------+ | 1 | zhang | beijing | | 2 | li | hebei | | 3 | zhao | tianjin | +----+-------+----------+ 3 rows in set (0.00 sec) mysql> insert into tb_dept1 (id,name) values (4,"abcdefghijklmnopqrstuvwxyz"); ERROR 1406 (22001): Data too long for column 'name' at row 1 #报错原因：name字段超出长度 mysql> alter table tb_dept1 modify name varchar(30); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_dept1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into tb_dept1 (id,name) values (4,"abcdefghijklmnopqrstuvwxyz"); Query OK, 1 row affected (0.00 sec) mysql> select * from tb_dept1; +----+----------------------------+----------+ | id | name | location | +----+----------------------------+----------+ | 1 | zhang | beijing | | 2 | li | hebei | | 3 | zhao | tianjin | | 4 | abcdefghijklmnopqrstuvwxyz | NULL | +----+----------------------------+----------+ 4 rows in set (0.00 sec) mysql>

3.修改字段名

语法：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

应用：

mysql> desc tb_dept1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table tb_dept1 change location my_local varchar(50); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_dept1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | my_local | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>

4.添加字段

语法：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在字段名];

应用：

mysql> alter table tb_dept1 add test1_col varchar(20) not null; #插入的新字段只能在表的最后 Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_dept1; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | my_local | varchar(50) | YES | | NULL | | | test1_col | varchar(20) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table tb_dept1 add test2_col int first; #插入的新字段放在第一列 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_dept1; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | test2_col | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | my_local | varchar(50) | YES | | NULL | | | test1_col | varchar(20) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table tb_dept1 add test3_col int after name; #插入的新字段放在指定某一列后面 Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_dept1; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | test2_col | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | test3_col | int(11) | YES | | NULL | | | my_local | varchar(50) | YES | | NULL | | | test1_col | varchar(20) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql>

5.删除字段

语法：

ALTER TABLE <表名> DROP <字段名>;

应用：

mysql> alter table tb_dept1 drop test1_col; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_dept1; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | test2_col | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | test3_col | int(11) | YES | | NULL | | | my_local | varchar(50) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql>

6.修改字段的排列位置

语法：

ALTER TABLE <表名> MODIFY <字段名> <数据类型> FIRST|AFTER <字段2>;

应用：

mysql> alter table tb_dept1 modify test2_col int(11) after my_local; #将字段放入某列的后面 Query OK, 0 rows affected (0.55 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_dept1; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | test3_col | int(11) | YES | | NULL | | | my_local | varchar(50) | YES | | NULL | | | test2_col | int(11) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table tb_dept1 modify my_local varchar(50) first; #将字段放入第一列 Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc tb_dept1; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | my_local | varchar(50) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | test3_col | int(11) | YES | | NULL | | | test2_col | int(11) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql>

7.更改表的存储引擎

语法：

ALTER TABLE <表名> ENGINE=<更改后的存储引擎>;

查看基本的存储引擎：

mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.01 sec) mysql>

应用：

mysql> show create table tb_dept1; +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_dept1 | CREATE TABLE `tb_dept1` ( `my_local` varchar(50) DEFAULT NULL, `id` int(11) NOT NULL, `name` varchar(30) DEFAULT NULL, `test3_col` int(11) DEFAULT NULL, `test2_col` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table tb_dept1 engine=MyISAM; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails #报错原因：该表存有外键无法更改，因为InnoDB支持外键，但是MyISAM不支持外键 mysql> show create table tb_emp8; +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_emp8 | CREATE TABLE `tb_emp8` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `deptId` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table tb_emp8 engine=myisam; Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table tb_emp8; +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_emp8 | CREATE TABLE `tb_emp8` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `deptId` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) mysql>

8.删除表的外键约束

语法：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>；

应用：

mysql> desc tb_dept1; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | my_local | varchar(50) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | test3_col | int(11) | YES | | NULL | | | test2_col | int(11) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> create table tb_emp9( -> id int primary key, -> name varchar(25), -> deptId int, -> salary float, -> constraint fk_emp_dept foreign key (deptId) references tb_dept1(id)); Query OK, 0 rows affected (0.01 sec) mysql> show create table tb_emp9; #查看该表的外键名称 +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_emp9 | CREATE TABLE `tb_emp9` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `deptId` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_emp_dept` (`deptId`), CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table tb_emp9 drop foreign key fk_emp_dept; #删除外键 Query OK, 0 rows affected (0.51 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tb_emp9; #再次查看 +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_emp9 | CREATE TABLE `tb_emp9` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `deptId` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_emp_dept` (`deptId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>

四、删除数据表

1.删除没有被关联的表

语法：

DROP TABLE [IF EXISTS]表1,表2...

应用：

mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | my_test | | tb_dept1 | | tb_dept3 | | tb_emp2 | | tb_emp3 | | tb_emp4 | | tb_emp5 | | tb_emp6 | | tb_emp7 | | tb_emp8 | | tb_emp9 | | test1 | +------------------+ 12 rows in set (0.00 sec) mysql> drop table test1; #删除一个表 Query OK, 0 rows affected (0.54 sec) mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | my_test | | tb_dept1 | | tb_dept3 | | tb_emp2 | | tb_emp3 | | tb_emp4 | | tb_emp5 | | tb_emp6 | | tb_emp7 | | tb_emp8 | | tb_emp9 | +------------------+ 11 rows in set (0.00 sec) mysql> drop table my_test,tb_emp2; #删除多个表 Query OK, 0 rows affected (0.01 sec) mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | tb_dept1 | | tb_dept3 | | tb_emp3 | | tb_emp4 | | tb_emp5 | | tb_emp6 | | tb_emp7 | | tb_emp8 | | tb_emp9 | +------------------+ 9 rows in set (0.00 sec) mysql> drop table my_test,tb_emp2; #删除不存在的表会报错 ERROR 1051 (42S02): Unknown table 'mytest.my_test,mytest.tb_emp2' mysql> drop table if exists my_test,tb_emp2; #删除不存在的表不会报错，但会提示警告 Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql>

2.删除被其他关联的主表

创建主表tb_dept2

mysql> create table tb_dept2( -> id int(11) primary key, -> name varchar(22), -> location varchar(50)); Query OK, 0 rows affected (0.02 sec) mysql>

创建从表tb_emp

mysql> create table tb_emp( -> id int(11) primary key, -> name varchar(25), -> deptId int, -> salary float, -> constraint fk_emp_dept foreign key (deptId) references tb_dept2(id)); Query OK, 0 rows affected (0.01 sec) mysql>

直接删除父表tb_dept2报错：

mysql> drop table tb_dept2; #由于tb_emp对tb_dept2有依赖，直接删除tb_dept2会报错 ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql>

在有外键约束时，主表不能直接删除。

解除关联子表tb_dept的外键约束

mysql> alter table tb_emp drop foreign key fk_emp_dept; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>

删除父表：

mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | tb_dept1 | | tb_dept3 | | tb_emp | | tb_emp3 | | tb_emp4 | | tb_emp5 | | tb_emp6 | | tb_emp7 | | tb_emp8 | | tb_emp9 | +------------------+ 10 rows in set (0.00 sec) mysql>

这只是我的一些浅薄的见解，望多指教！