SQL语法基础之DROP语句
SQL语法基础之DROP语句
作者：尹正杰版权声明：原创作品，谢绝转载！否则将追究法律责任。
⼀.查看DROP帮助信息
mysql> ? DROP
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 TABLE
DEALLOCATE PREPARE
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP FUNCTION UDF
DROP INDEX
DROP PROCEDURE
DROP RESOURCE GROUP
DROP ROLE
DROP SERVER
DROP SPATIAL REFERENCE SYSTEM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP USER
DROP VIEW
mysql>
mysql>
⼆.DROP DATABASE
DROP DATABASE语句⽤来删除数据库操作，即删除了数据库也删除数据库⾥的所有表。
1>.查看DROP DATABASE的帮助信息
mysql> ? DROP DATABASE
Name: 'DROP DATABASE'
Description:
Syntax:
DROP {DATABASE|SCHEMA} [IF EXISTS]db_name
DROP DATABASE drops all tables in the database and deletes the
database. Be very careful with this statement! To use DROP DATABASE,
you need the DROP privilege on the database. DROP SCHEMA is a synonym
for DROP DATABASE.
*Important*:
When a database is dropped, privileges granted specifically for the
database are not automatically dropped. They must be dropped manually.
See [HELP GRANT].
IF EXISTS is used to prevent an error from occurring if the database
does not exist.
URL: sql/doc/refman/8.0/en/drop-database.html
mysql>
mysql> ? DROP DATABASE
2>.删除数据库的操作将删除改数据库所在⽂件夹乳腺癌格式的⽂件，以及db.opt⽂件
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>DROP DATABASE A1; #删除⼀个已经存在的库，由于我知道该库肯定存在，因此这⾥我就没有使⽤判断语句！
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>DROP DATABASE IF EXISTS A2; #删除⼀个库如果你不确定该库是否存在时，你可以使⽤判断语句，既然改库不存在你也不怕报错啦～
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SHOW DATABASES;
+--------------------+
|Database|
+--------------------+
| A3 |
| course |
| day03 |
| devops |
| information_schema |
| mysql |
| performance_schema |
| sys |
| yinzhengjie |
+--------------------+
9 rows in set (0.00 sec)
mysql>
三.DROP INDEX语句
DROP INDEX 语句是⽤来删除索引操作的
1>.查看DROP INDEX语句的帮助信息
mysql> ? DROP INDEX
Name: 'DROP INDEX'
Description:
Syntax:
DROP INDEX index_name ON tbl_name
[algorithm_option | lock_option] ...
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
DROP INDEX drops the index named index_name from the table tbl_name.
This statement is mapped to an ALTER TABLE statement to drop the index.
See [HELP ALTER TABLE].
To drop a primary key, the index name is always PRIMARY, which must be
specified as a quoted identifier because PRIMARY is a reserved word:
DROP INDEX `PRIMARY` ON t;
URL: sql/doc/refman/8.0/en/drop-index.html
mysql>
mysql> ? DROP INDEX
2>.案例展⽰
mysql> SHOW CREATE TABLE teacher;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|Table|Create Table
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| teacher |CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`course_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_teacher_id_unique` (`id`),
UNIQUE KEY `index_teacher_id_comment` (`id`) COMMENT '这就是创建⼀个唯⼀索引⽽已!',
KEY `teacher_course` (`course_id`),
KEY `index_teacher_id` (`id`),
KEY `index_teacher_id_and_name` (`id`,`name`),
CONSTRAINT `teacher_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)
mysql>
mysql>DROP INDEX index_teacher_id_and_name ON teacher;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> SHOW CREATE TABLE teacher;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|Table|Create Table
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| teacher |CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`course_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_teacher_id_unique` (`id`),
UNIQUE KEY `index_teacher_id_comment` (`id`) COMMENT '这就是创建⼀个唯⼀索引⽽已!',
KEY `teacher_course` (`course_id`),
KEY `index_teacher_id` (`id`),
CONSTRAINT `teacher_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)
truncatedelete和drop的区别) ENGINE=InnoDB AUTO_INCREMENT=16DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)
mysql>
四.DROP TABLE语句
DROP TABLE 语句⽤来删除⼀个或多个表操作，当然也可以删除临时表。
1>.查看帮助信息
mysql> ? DROP TABLE
Name: 'DROP TABLE'
Description:
Syntax:
DROP[TEMPORARY]TABLE[IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
DROP TABLE removes one or more tables. You must have the DROP privilege
for each table.
Be careful with this statement! It removes the table definition and all
table data. For a partitioned table, it permanently removes the table
definition, all its partitions, and all data stored in those
partitions. It also removes partition definitions associated with the
dropped table.
DROP TABLE causes an implicit commit, except when used with the
TEMPORARY keyword. See
sql/doc/refman/8.0/en/implicit-commit.html.
*Important*:
When a table is dropped, privileges granted specifically for the table
are not automatically dropped. They must be dropped manually. See [HELP
GRANT].
If any tables named in the argument list do not exist, the statement
fails with an error indicating by name which nonexisting tables it was
unable to drop, and no changes are made.
Use IF EXISTS to prevent an error from occurring for tables that do not
exist. Instead of an error, a NOTE is generated for each nonexistent
table; these notes can be displayed with SHOW WARNINGS. See [HELP SHOW
WARNINGS].
IF EXISTS can also be useful for dropping tables in unusual
circumstances under which there is an entry in the data dictionary but
no table managed by the storage engine. (For example, if an abnormal
server exit occurs after removal of the table from the storage engine
but before removal of the data dictionary entry.)
The TEMPORARY keyword has the following effects:
o The statement drops only TEMPORARY tables.
o The statement does not cause an implicit commit.
o No access rights are checked. A TEMPORARY table is visible only with
the session that created it, so no check is necessary.
Using TEMPORARY is a good way to ensure that you do not accidentally
drop a non-TEMPORARY table.
The RESTRICT and CASCADE keywords do nothing. They are permitted to
make porting easier from other database systems.
DROP TABLE is not supported with all innodb_force_recovery settings.
See
sql/doc/refman/8.0/en/forcing-innodb-recovery.html.
URL: sql/doc/refman/8.0/en/drop-table.html
mysql>
mysql> ? DROP TABLE
2>.RESTRICT/CASCADE两个关键词在5.7版本中没⽤
mysql> SHOW TABLES;
+--------------+
| Tables_in_A3 |
+--------------+
| B3 |
| v_B3 |
+--------------+
2 rows in set (0.00 sec)
mysql>
mysql>DROP TABLE B3; #删除已经存在的表
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>DROP TABLE IF EXISTS B3; #删除⼀张表，我这⾥加了判断条件，意思是当这张表存在就删除，若不存在就不执⾏删除操作，这样可以避免报错～Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> SHOW TABLES;
+--------------+
| Tables_in_A3 |
+--------------+
| v_B3 |
+--------------+
1 row in set (0.00 sec)
mysql>
五.DROP VIEW语句
DROP VIEW语句⽤来删除⼀个或多个视图。
1>.查看DROP VIEW的帮助信息
mysql> ? DROP VIEW
Name: 'DROP VIEW'
Description:
Syntax:
DROP VIEW[IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
DROP VIEW removes one or more views. You must have the DROP privilege
for each view.
If any views named in the argument list do not exist, the statement
fails with an error indicating by name which nonexisting views it was
unable to drop, and no changes are made.
*Note*:
In MySQL 5.7and earlier, DROP VIEW returns an error if any views named
in the argument list do not exist, but also drops all views in the list
that do exist. Due to the change in behavior in MySQL 8.0, a partially
completed DROP VIEW operation on a MySQL 5.7 master fails when
replicated on a MySQL 8.0 slave. To avoid this failure scenario, use IF
EXISTS syntax in DROP VIEW statements to prevent an error from
occurring for views that do not exist. For more information, see
sql/doc/refman/8.0/en/atomic-ddl.html.
The IF EXISTS clause prevents an error from occurring for views that
don't exist. When this clause is given, a NOTE is generated for each
nonexistent view. See [HELP SHOW WARNINGS].
RESTRICT and CASCADE, if given, are parsed and ignored.
URL: sql/doc/refman/8.0/en/drop-view.html
mysql>
mysql>
mysql> ? DROP VIEW
2>.同样，RESTRICT/CASCADE两个关键词在MySQL5.7依旧也是⽆效mysql> SHOW TABLES;
+--------------+
| Tables_in_A3 |
+--------------+
| v_B3 |
+--------------+
1 row in set (0.00 sec)
mysql>
mysql>DROP VIEW v_B3;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql>
mysql>DROP VIEW IF EXISTS v_B3;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql>
mysql>
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论