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>
