MySQL 创建、查看创建语句、删除数据库、表-蒲公英云

MySQL 创建、查看创建语句、删除数据库、表

show databases 查看当前的数据库

use database_name 切换到数据库

show tables 查看当前数据中的表

CRATE DATABASE database_name; 创建数据库

CRATE TABLE <表名>

(

字段名1 数据类型 [列级别约束条件] [默认值],

字段名2 数据类型 [列级别约束条件] [默认值]，

……

[表级别的约束条件]

);

show database database_name; 查看创建数据库语句

show table <表名>; 查看创建表语句

drop table <表名>; 删除该表【表中的数据一并删除】

drop database database_name; 删除该数据库【联通数据库中的其他表数据一并删除】

示例:

创建数据库
mysql> CREATE DATABASE test_d;
Query OK, 1 row affected (0.06 sec)
显示数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test_d             |
| world              |
+--------------------+
7 rows in set (0.00 sec)
切换数据库
mysql> use test_d
Database changed
创建表 table1 设置 id 为主键
mysql> CREATE TABLE table1
    -> (
    -> id int,
    -> name varchar(30),
    -> time date,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.20 sec)
查看当前数据中的表
mysql> show tables;
+------------------+
| Tables_in_test_d |
+------------------+
| table1           |
+------------------+
1 row in set (0.00 sec)
查看数据创建语句
mysql> show create database test_d;
+----------+-----------------------------------------------------------------------------------------------+
| Database | Create Database                                                                               |
+----------+-----------------------------------------------------------------------------------------------+
| test_d   | CREATE DATABASE `test_d` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
+----------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看表创建语句
mysql> show create table table1
    -> ;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                            |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除 table1 表
mysql> drop table table1;
Query OK, 0 rows affected (0.21 sec)
查看数据库中表数据
mysql> show tables;
Empty set (0.00 sec)
删除 test_d 数据库
mysql> drop database test_d;
Query OK, 0 rows affected (0.06 sec)
查看创建数据库语句
mysql> show create database test_d;
ERROR 1049 (42000): Unknown database 'test_d'
查看全部数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)
退出
mysql> exit
Bye