1.创建数据表
1.1.1语法
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_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]
}
data_type:
(see Chapter 11, Data Types)
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'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| START TRANSACTION
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| tablespace_option
| 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]
tablespace_option:
TABLESPACE tablespace_name [STORAGE DISK]
| [TABLESPACE tablespace_name] STORAGE MEMORY
query_expression:
SELECT ... (Some valid select or union statement)
1.1.2案例
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table tb_emp1
-> ( id int(11),
-> name varchar(25),
-> deptid int(11),
-> salary float );
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_emp1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from tb_emp1;
Empty set (0.00 sec)
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptid | int | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table tb_emp1\G;
*************************** 1. row ***************************
Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
`id` int DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`deptid` int DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
2.创建临时表
2.1.1案例
mysql> create temporary table new_tb_emp1 select * from tb_emp1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_emp1 |
+----------------+
1 row in set (0.00 sec)
mysql> create temporary table new_tb_emp1 select * from tb_emp1 limit 0;
ERROR 1050 (42S01): Table 'new_tb_emp1' already exists
mysql> select * from new_tb_emp1;
Empty set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_emp1 |
+----------------+
1 row in set (0.00 sec)
mysql> show create table new_tb_emp1;
+-------------+------------------------------------------------------------------
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------+
| Table | Create Table
|
+-------------+------------------------------------------------------------------
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------+
| new_tb_emp1 | CREATE TEMPORARY TABLE `new_tb_emp1` (
`id` int DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`deptid` int DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.复制表结构
3.1.1案例
mysql> insert into tb_emp1(id,name,deptid,salary) value(1,'zhangsan',10,1000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_emp1(id,name,deptid,salary) value(2,'lisi',20,2000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb_emp1;
+------+----------+--------+--------+
| id | name | deptid | salary |
+------+----------+--------+--------+
| 1 | zhangsan | 10 | 1000 |
| 2 | lisi | 20 | 2000 |
+------+----------+--------+--------+
2 rows in set (0.00 sec)
mysql> create table like_tb_emp1 like tb_emp1;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| tb_emp1 |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from like_tb_emp1;
Empty set (0.00 sec)
mysql> desc like_tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptid | int | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
4.复制表数据
4.1.1案例
mysql> create table select_tb_emp1 select * from tb_emp1 where id=2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| select_tb_emp1 |
| tb_emp1 |
+----------------+
3 rows in set (0.01 sec)
mysql> select * from select_tb_emp1;
+------+------+--------+--------+
| id | name | deptid | salary |
+------+------+--------+--------+
| 2 | lisi | 20 | 2000 |
+------+------+--------+--------+
1 row in set (0.00 sec)
5.修改数据表
5.1.1语法
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)
5.1.2案例
mysql> CREATE TABLE t1 (a INTEGER, b CHAR(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| select_tb_emp1 |
| t1 |
| tb_emp1 |
+----------------+
4 rows in set (0.00 sec)
# 修改表名
mysql> ALTER TABLE t1 RENAME t2;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| select_tb_emp1 |
| t2 |
| tb_emp1 |
+----------------+
4 rows in set (0.00 sec)
# 修改数据类型和字段名称
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | tinyint | NO | | NULL | |
| c | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 增加字段
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| a | tinyint | NO | | NULL | |
| c | char(20) | YES | | NULL | |
| d | timestamp | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 添加索引
mysql> ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| a | tinyint | NO | PRI | NULL | |
| c | char(20) | YES | | NULL | |
| d | timestamp | YES | MUL | NULL | |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 删除字段
mysql> ALTER TABLE t2 DROP COLUMN c;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| a | tinyint | NO | PRI | NULL | |
| d | timestamp | YES | MUL | NULL | |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 增加自增新字段
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY
(c);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| a | tinyint | NO | UNI | NULL | |
| d | timestamp | YES | MUL | NULL | |
| c | int unsigned | NO | PRI | NULL | auto_increment |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
# 指定字段最左增加字段
mysql> ALTER TABLE t2 ADD first_col INT(11) FIRST;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc t2;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| first_col | int | YES | | NULL | |
| a | tinyint | NO | UNI | NULL | |
| d | timestamp | YES | MUL | NULL | |
| c | int unsigned | NO | PRI | NULL | auto_increment |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
# 指定相对位置增加字段
mysql> ALTER TABLE t2 ADD second_col INT(11) AFTER d;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc t2;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| first_col | int | YES | | NULL | |
| a | tinyint | NO | UNI | NULL | |
| d | timestamp | YES | MUL | NULL | |
| second_col | int | YES | | NULL | |
| c | int unsigned | NO | PRI | NULL | auto_increment |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
# 修改字段位置
mysql> desc t2;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| first_col | int | YES | | NULL | |
| a | tinyint | NO | UNI | NULL | |
| d | timestamp | YES | MUL | NULL | |
| second_col | int | YES | | NULL | |
| c | int unsigned | NO | PRI | NULL | auto_increment |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE t2 MODIFY c INT UNSIGNED FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| c | int unsigned | NO | PRI | NULL | |
| first_col | int | YES | | NULL | |
| a | tinyint | NO | UNI | NULL | |
| d | timestamp | YES | MUL | NULL | |
| second_col | int | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 调整字段顺序
mysql> desc t2;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| c | int unsigned | NO | PRI | NULL | |
| first_col | int | YES | | NULL | |
| a | tinyint | NO | UNI | NULL | |
| d | timestamp | YES | MUL | NULL | |
| second_col | int | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE t2 MODIFY a TINYINT AFTER c;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| c | int unsigned | NO | PRI | NULL | |
| a | tinyint | YES | UNI | NULL | |
| first_col | int | YES | | NULL | |
| d | timestamp | YES | MUL | NULL | |
| second_col | int | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 修改表引擎
mysql> show create table t2\G;
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`c` int unsigned NOT NULL,
`a` tinyint DEFAULT NULL,
`first_col` int DEFAULT NULL,
`d` timestamp NULL DEFAULT NULL,
`second_col` int DEFAULT NULL,
PRIMARY KEY (`c`),
UNIQUE KEY `a` (`a`),
KEY `d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> ALTER TABLE t2 ENGINE = MyISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t2\G;
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`c` int unsigned NOT NULL,
`a` tinyint DEFAULT NULL,
`first_col` int DEFAULT NULL,
`d` timestamp NULL DEFAULT NULL,
`second_col` int DEFAULT NULL,
PRIMARY KEY (`c`),
UNIQUE KEY `a` (`a`),
KEY `d` (`d`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
6.删除数据表
6.1.1语法
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
6.1.2案例
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| select_tb_emp1 |
| t2 |
| tb_emp1 |
+----------------+
4 rows in set (0.00 sec)
mysql> drop table t2;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| like_tb_emp1 |
| select_tb_emp1 |
| tb_emp1 |
+----------------+
3 rows in set (0.00 sec)