SQL语法基础之ALTER语句

　　　　　　　　　　　　　　　SQL语法基础之ALTER语句

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

版权声明：原创作品，谢绝转载！否则将追究法律责任。

 

 

一.查看ALTER的帮助信息

mysql> ? ALTER
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER DATABASE
   ALTER EVENT
   ALTER FUNCTION
   ALTER INSTANCE
   ALTER PROCEDURE
   ALTER RESOURCE GROUP
   ALTER SERVER
   ALTER TABLE
   ALTER TABLESPACE
   ALTER USER
   ALTER VIEW
   GRANT
   SPATIAL

mysql> 
mysql> 

 

 

二.ALTER DATABASE 语句

　　ALTER DATABASE 语句用来修改数据库的属性。

1>.查看ALTER DATABASE的帮助信息

mysql> ? ALTER DATABASE
Name: 'ALTER DATABASE'
Description:
Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

ALTER DATABASE enables you to change the overall characteristics of a
database. These characteristics are stored in the data dictionary. To
use ALTER DATABASE, you need the ALTER privilege on the database. ALTER
SCHEMA is a synonym for ALTER DATABASE.

The database name can be omitted from the first syntax, in which case
the statement applies to the default database.

National Language Characteristics

The CHARACTER SET clause changes the default database character set.
The COLLATE clause changes the default database collation.
http://dev.mysql.com/doc/refman/8.0/en/charset.html, discusses
character set and collation names.

You can see what character sets and collations are available using,
respectively, the SHOW CHARACTER SET and SHOW COLLATION statements. See
[HELP SHOW CHARACTER SET], and [HELP SHOW COLLATION], for more
information.

If you change the default character set or collation for a database,
stored routines that use the database defaults must be dropped and
recreated so that they use the new defaults. (In a stored routine,
variables with character data types use the database defaults if the
character set or collation are not specified explicitly. See [HELP
CREATE PROCEDURE].)

URL: http://dev.mysql.com/doc/refman/8.0/en/alter-database.html


mysql> 

2>.上述帮助信息的关键点说明

• Db_name可以不指定，如果不指定说明是修改当前数据库的属性

• Character set代表修改数据库的默认字符集

• Collate代表修改数据库的默认排序规则

• 如果修改了数据库的默认字符集或排序规则，那数据库中的所有 存储过程和函数都需要重新创建一遍

3>.案例展示

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| A1                 |
| A2                 |
| A3                 |
| course             |
| day03              |
| devops             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yinzhengjie        |
+--------------------+
11 rows in set (0.00 sec)

mysql> 
mysql> SHOW CREATE DATABASE A1;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| A1       | CREATE DATABASE `A1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> ALTER  DATABASE A1 DEFAULT  CHARACTER SET gbk;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SHOW CREATE DATABASE A1;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| A1       | CREATE DATABASE `A1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

 

三.ALTER VIEW 语句

1>. 查看ALTER VIEW的帮助信息

mysql> ? ALTER VIEW
Name: 'ALTER VIEW'
Description:
Syntax:
ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

This statement changes the definition of a view, which must exist. The
syntax is similar to that for CREATE VIEW see [HELP CREATE VIEW]). This
statement requires the CREATE VIEW and DROP privileges for the view,
and some privilege for each column referred to in the SELECT statement.
ALTER VIEW is permitted only to the definer or users with the
SET_USER_ID or SUPER privilege.

URL: http://dev.mysql.com/doc/refman/8.0/en/alter-view.html


mysql> 

2>.ALTER VIEW 语句用来修改视图的定义，本身的语法结构和CREATE VIEW相同，语句起到的作用和CREATE OR REPLACE VIEW语句相同。

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_yinzhengjie |
+-----------------------+
| course                |
| student               |
| teacher               |
| view_teacher          |
+-----------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> SHOW CREATE TABLE view_teacher\G
*************************** 1. row ***************************
                View: view_teacher
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_teacher` AS select `teacher`.`id` AS `id`,`teacher`.`name` AS `name`,`teacher`.`course_id` AS `course_id` from `teacher`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> 
mysql> ALTER VIEW view_teacher AS SELECT name FROM teacher WHERE id = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> SELECT * FROM view_teacher;
+-----------+
| name      |
+-----------+
| 谢霆锋    |
+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> SELECT * FROM teacher WHERE id = 1;
+----+-----------+-----------+
| id | name      | course_id |
+----+-----------+-----------+
|  1 | 谢霆锋    |        11 |
+----+-----------+-----------+
1 row in set (0.00 sec)

mysql> 

 

四.ALTER TABLE 语句

　　ALTER TABLE语句是用来修改表的

1>.查看ALTER TABLE语句的帮助信息

mysql> ? ALTER TABLE;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    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 [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 FULLTEXT [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (col_name,...)
        reference_definition
  | ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | 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
  | 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
  | UPGRADE 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:
    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
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    (see CREATE TABLE options)

ALTER TABLE changes the structure of a table. For example, you can add
or delete columns, create or destroy indexes, change the type of
existing columns, or rename columns or the table itself. You can also
change characteristics such as the storage engine used for the table or
the table comment.

o To use ALTER TABLE, you need ALTER, CREATE, and INSERT privileges for
  the table. Renaming a table requires ALTER and DROP on the old table,
  ALTER, CREATE, and INSERT on the new table.

o Following the table name, specify the alterations to be made. If none
  are given, ALTER TABLE does nothing.

o The syntax for many of the permissible alterations is similar to
  clauses of the CREATE TABLE statement. column_definition clauses use
  the same syntax for ADD and CHANGE as for CREATE TABLE. For more
  information, see [HELP CREATE TABLE].

o The word COLUMN is optional and can be omitted, except for RENAME
  COLUMN (to distinguish a column-renaming operation from the RENAME
  table-renaming operation).

o Multiple ADD, ALTER, DROP, and CHANGE clauses are permitted in a
  single ALTER TABLE statement, separated by commas. This is a MySQL
  extension to standard SQL, which permits only one of each clause per
  ALTER TABLE statement. For example, to drop multiple columns in a
  single statement, do this:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

o If a storage engine does not support an attempted ALTER TABLE
  operation, a warning may result. Such warnings can be displayed with
  SHOW WARNINGS. See [HELP SHOW WARNINGS]. For information on
  troubleshooting ALTER TABLE, see
  http://dev.mysql.com/doc/refman/8.0/en/alter-table-problems.html.

o For information about generated columns, see
  http://dev.mysql.com/doc/refman/8.0/en/alter-table-generated-columns.
  html.

o For usage examples, see
  http://dev.mysql.com/doc/refman/8.0/en/alter-table-examples.html.

o With the mysql_info() C API function, you can find out how many rows
  were copied by ALTER TABLE. See
  http://dev.mysql.com/doc/refman/8.0/en/mysql-info.html.

URL: http://dev.mysql.com/doc/refman/8.0/en/alter-table.html


mysql> 
mysql> 

2>.ALTER 常用方式介绍

• Alter table ... add [column_name]

• Alter table ... add constraint [name] unique [index/key] [name]

• Alter table ... add constraint [name] foreign key (column_name) references table_name(column_name)

• Alter table ... drop column [column_name]

• Alter table ... drop [index/key] [index_name]

3>.案例展示

mysql> SELECT * FROM students;
+--------+--------------+--------+
| stu_id | stu_name     | gender |
+--------+--------------+--------+
|     10 | 漩涡鸣人     |    100 |
|     11 | 佐助         |    100 |
|     21 | 孙悟空       |    200 |
|     22 | 猪八戒       |    250 |
|     23 | 唐三藏       |    300 |
|     24 | 沙和尚       |    350 |
|     31 | 李白         |    400 |
|     32 | 蛮王         |    500 |
|     33 | 诡术妖姬     |    600 |
|     34 | 小鱼人       |    700 |
+--------+--------------+--------+
10 rows in set (0.00 sec)

mysql> 
mysql> ALTER TABLE students ADD remarks VARCHAR(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT * FROM students;
+--------+--------------+--------+---------+
| stu_id | stu_name     | gender | remarks |
+--------+--------------+--------+---------+
|     10 | 漩涡鸣人     |    100 | NULL    |
|     11 | 佐助         |    100 | NULL    |
|     21 | 孙悟空       |    200 | NULL    |
|     22 | 猪八戒       |    250 | NULL    |
|     23 | 唐三藏       |    300 | NULL    |
|     24 | 沙和尚       |    350 | NULL    |
|     31 | 李白         |    400 | NULL    |
|     32 | 蛮王         |    500 | NULL    |
|     33 | 诡术妖姬     |    600 | NULL    |
|     34 | 小鱼人       |    700 | NULL    |
+--------+--------------+--------+---------+
10 rows in set (0.00 sec)

mysql> 
mysql> 

mysql> SELECT * FROM students;
+--------+--------------+--------+---------+
| stu_id | stu_name     | gender | remarks |
+--------+--------------+--------+---------+
|     10 | 漩涡鸣人     |    100 | NULL    |
|     11 | 佐助         |    100 | NULL    |
|     21 | 孙悟空       |    200 | NULL    |
|     22 | 猪八戒       |    250 | NULL    |
|     23 | 唐三藏       |    300 | NULL    |
|     24 | 沙和尚       |    350 | NULL    |
|     31 | 李白         |    400 | NULL    |
|     32 | 蛮王         |    500 | NULL    |
|     33 | 诡术妖姬     |    600 | NULL    |
|     34 | 小鱼人       |    700 | NULL    |
+--------+--------------+--------+---------+
10 rows in set (0.00 sec)

mysql> 
mysql> ALTER TABLE students DROP remarks;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT * FROM students;
+--------+--------------+--------+
| stu_id | stu_name     | gender |
+--------+--------------+--------+
|     10 | 漩涡鸣人     |    100 |
|     11 | 佐助         |    100 |
|     21 | 孙悟空       |    200 |
|     22 | 猪八戒       |    250 |
|     23 | 唐三藏       |    300 |
|     24 | 沙和尚       |    350 |
|     31 | 李白         |    400 |
|     32 | 蛮王         |    500 |
|     33 | 诡术妖姬     |    600 |
|     34 | 小鱼人       |    700 |
+--------+--------------+--------+
10 rows in set (0.00 sec)

mysql> 

 

五.RENAME TABLE语句

　　RENAME TABLE语句用来重命名一个或多个表名。

1>.查看RENAME TABLE语句的帮助信息

mysql> ? RENAME TABLE
Name: 'RENAME TABLE'
Description:
Syntax:
RENAME TABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

RENAME TABLE renames one or more tables. You must have ALTER and DROP
privileges for the original table, and CREATE and INSERT privileges for
the new table.

For example, to rename a table named old_table to to new_table, use
this statement:

RENAME TABLE old_table TO new_table;

That statement is equivalent to the following ALTER TABLE statement:

ALTER TABLE old_table RENAME new_table;

RENAME TABLE, unlike ALTER TABLE, can rename multiple tables within a
single statement:

RENAME TABLE old_table1 TO new_table1,
             old_table2 TO new_table2,
             old_table3 TO new_table3;

Renaming operations are performed left to right. Thus, to swap two
table names, do this (assuming that a table with the intermediary name
tmp_table does not already exist):

RENAME TABLE old_table TO tmp_table,
             new_table TO old_table,
             tmp_table TO new_table;

As of MySQL 8.0.13, you can rename tables locked with a LOCK TABLES
statement, provided that they are locked with a WRITE lock or are the
product of renaming WRITE-locked tables from earlier steps in a
multiple-table rename operation. For example, this is permitted:

LOCK TABLE old_table1 WRITE;
RENAME TABLE old_table1 TO new_table1,
             new_table1 TO new_table2;

This is not permitted:

LOCK TABLE old_table1 READ;
RENAME TABLE old_table1 TO new_table1,
             new_table1 TO new_table2;

Prior to MySQL 8.0.13, to execute RENAME TABLE, there must be no tables
locked with LOCK TABLES.

With the transaction table locking conditions satisfied, the rename
operation is done atomically; no other session can access any of the
tables while the rename is in progress.

If any errors occur during a RENAME TABLE, the statement fails and no
changes are made.

You can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

Using this method to move all tables from one database to a different
one in effect renames the database (an operation for which MySQL has no
single statement), except that the original database continues to
exist, albeit with no tables.

Like RENAME TABLE, ALTER TABLE ... RENAME can also be used to move a
table to a different database. Regardless of the statement used, if the
rename operation would move the table to a database located on a
different file system, the success of the outcome is platform specific
and depends on the underlying operating system calls used to move table
files.

If a table has triggers, attempts to rename the table into a different
database fail with a Trigger in wrong schema (ER_TRG_IN_WRONG_SCHEMA)
error.

To rename TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE
instead.

RENAME TABLE works for views, except that views cannot be renamed into
a different database.

Any privileges granted specifically for a renamed table or view are not
migrated to the new name. They must be changed manually.

RENAME TABLE changes internally generated foreign key constraint names
and user-defined foreign key constraint names that contain the string
"tbl_name_ibfk_" to reflect the new table name. InnoDB interprets
foreign key constraint names that contain the string "tbl_name_ibfk_"
as internally generated names.

Foreign key constraint names that point to the renamed table are
automatically updated unless there is a conflict, in which case the
statement fails with an error. A conflict occurs if the renamed
constraint name already exists. In such cases, you must drop and
re-create the foreign keys for them to function properly.

URL: http://dev.mysql.com/doc/refman/8.0/en/rename-table.html


mysql> 

2>.RENAME TABLE语句用来重命名一个或多个表名。

mysql> SHOW TABLES;
+------------------------+
| Tables_in_devops       |
+------------------------+
| course                 |
| student_course_foreign |
| student_primary        |
| students               |
+------------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> RENAME TABLE students TO new_students;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> SHOW TABLES;
+------------------------+
| Tables_in_devops       |
+------------------------+
| course                 |
| new_students           |
| student_course_foreign |
| student_primary        |
+------------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> 

3>.当想让两个表名相互调换是，可以执行以下语句

mysql> SHOW TABLES;
+------------------------+
| Tables_in_devops       |
+------------------------+
| course                 |
| new_students           |
| student_course_foreign |
| student_primary        |
+------------------------+
4 rows in set (0.01 sec)

mysql> 
mysql> SELECT * FROM course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Chinese     |
|  2 | English     |
|  3 | Mathematics |
|  4 | Physics     |
|  5 | Chemistry   |
|  6 | Biology     |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM new_students;
+--------+--------------+--------+
| stu_id | stu_name     | gender |
+--------+--------------+--------+
|     10 | 漩涡鸣人     |    100 |
|     11 | 佐助         |    100 |
|     21 | 孙悟空       |    200 |
|     22 | 猪八戒       |    250 |
|     23 | 唐三藏       |    300 |
|     24 | 沙和尚       |    350 |
|     31 | 李白         |    400 |
|     32 | 蛮王         |    500 |
|     33 | 诡术妖姬     |    600 |
|     34 | 小鱼人       |    700 |
+--------+--------------+--------+
10 rows in set (0.01 sec)

mysql> 
mysql> 
mysql> RENAME TABLE course TO temp_table, new_students TO course,temp_table TO new_students;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> SHOW TABLES;
+------------------------+
| Tables_in_devops       |
+------------------------+
| course                 |
| new_students           |
| student_course_foreign |
| student_primary        |
+------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM course;
+--------+--------------+--------+
| stu_id | stu_name     | gender |
+--------+--------------+--------+
|     10 | 漩涡鸣人     |    100 |
|     11 | 佐助         |    100 |
|     21 | 孙悟空       |    200 |
|     22 | 猪八戒       |    250 |
|     23 | 唐三藏       |    300 |
|     24 | 沙和尚       |    350 |
|     31 | 李白         |    400 |
|     32 | 蛮王         |    500 |
|     33 | 诡术妖姬     |    600 |
|     34 | 小鱼人       |    700 |
+--------+--------------+--------+
10 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM new_students;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Chinese     |
|  2 | English     |
|  3 | Mathematics |
|  4 | Physics     |
|  5 | Chemistry   |
|  6 | Biology     |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 

 

4>.RENAME TABLE能将表中的数据，索引，主键定义都自动转型到新表下，但视图和对原表分配的权限不能自动转型到新表，需要手动执行

 

六.TRUNCATE TABLE语句

1>.查看TRUNCATE TABLE语句的帮助信息 

mysql> ? TRUNCATE TABLE
Name: 'TRUNCATE TABLE'
Description:
Syntax:
TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE empties a table completely. It requires the DROP
privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement
that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE
statements.

To achieve high performance, TRUNCATE TABLE bypasses the DML method of
deleting data. Thus, it does not cause ON DELETE triggers to fire, it
cannot be performed for InnoDB tables with parent-child foreign key
relationships, and it cannot be rolled back like a DML operation.
However, TRUNCATE TABLE operations on tables that use an atomic
DDL-supported storage engine are either fully committed or rolled back
if the server halts during their operation. For more information, see
http://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html.

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL
statement rather than a DML statement. It differs from DELETE in the
following ways:

o Truncate operations drop and re-create the table, which is much
  faster than deleting rows one by one, particularly for large tables.

o Truncate operations cause an implicit commit, and so cannot be rolled
  back. See
  http://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html.

o Truncation operations cannot be performed if the session holds an
  active table lock.

o TRUNCATE TABLE fails for an InnoDB table or NDB
  (http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html) table if
  there are any FOREIGN KEY constraints from other tables that
  reference the table. Foreign key constraints between columns of the
  same table are permitted.

o Truncation operations do not return a meaningful value for the number
  of deleted rows. The usual result is "0 rows affected," which should
  be interpreted as "no information."

o As long as the table definition is valid, the table can be re-created
  as an empty table with TRUNCATE TABLE, even if the data or index
  files have become corrupted.

o Any AUTO_INCREMENT value is reset to its start value. This is true
  even for MyISAM and InnoDB, which normally do not reuse sequence
  values.

o When used with partitioned tables, TRUNCATE TABLE preserves the
  partitioning; that is, the data and index files are dropped and
  re-created, while the partition definitions are unaffected.

o The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

o Truncating a corrupted InnoDB table is supported.

URL: http://dev.mysql.com/doc/refman/8.0/en/truncate-table.html


mysql> 

2>.TRUNCATE TABLE语句用来删除/截断表里的所有数据

3>.和DELETE删除所有表数据在逻辑上含义相同，但性能更快

4>.类似中了DROP TABLE和CREATE TABLE两个语句

mysql> SELECT * FROM student;
+-----+-------------+
| sid | sname       |
+-----+-------------+
|   1 | yinzhengjie |
+-----+-------------+
1 row in set (0.00 sec)

mysql> 
mysql> TRUNCATE TABLE student;　　　　　　　　#清空改表数据
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> SELECT * FROM student; 　　　　　　　　#查询结果为空
Empty set (0.00 sec)

mysql> 
mysql>