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

刺骨的言语ヽ痛彻心扉 2022-03-19 06:46 452阅读 0赞

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