T N T

美食博主

0%

$1 数据库相关操作

Posted on Edited on In

本节记录数据库的增删改查

查看数据库

  1. SHOW DATABASES 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    (root@localhost) [book]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | book               |
    | db_test            |
    | douban             |
    | information_schema |
    | mysql              |
    | mysql_test         |
    | performance_schema |
    | sys                |
    +--------------------+
    8 rows in set (0.00 sec)

  2. SELECT DATABASE() 

    1
    2
    3
    4
    5
    6
    7
    (root@localhost) [book]> select database();
    +------------+
    | database() |
    +------------+
    | book       |
    +------------+
    1 row in set (0.00 sec)

  3. STATUS 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    (root@localhost) [book]> status;
    --------------
    mysql  Ver 8.0.19 for osx10.15 on x86_64 (Homebrew)

    Connection id:          8
    Current database:       book
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          less
    Using outfile:          ''
    Using delimiter:        ;
    Server version:         8.0.19 Homebrew
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    utf8mb4
    Db     characterset:    gb2312
    Client characterset:    utf8mb4
    Conn.  characterset:    utf8mb4
    UNIX socket:            /tmp/mysql.sock
    Binary data as:         Hexadecimal
    Uptime:                 9 hours 17 min 49 sec

    Threads: 2  Questions: 43  Slow queries: 0  Opens: 141  Flush tables: 3  Open tables: 63  Queries per second avg: 0.001
    --------------

  4. SHOW CREATE DATABASE database 

    1
    2
    3
    4
    5
    6
    7
    (root@localhost) [book]> show create database book;
    +----------+----------------------------------------------------------------------------------------------------+
    | Database | Create Database                                                                                    |
    +----------+----------------------------------------------------------------------------------------------------+
    | book     | CREATE DATABASE `book` /*!40100 DEFAULT CHARACTER SET gb2312 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
    +----------+----------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

创建数据库

  1. CREATE DATABASE database 
    1
    2
    3
    4
    5
    6
    7
    8
    (root@localhost) [(none)]> create database book;
    Query OK, 1 row affected (0.02 sec)

    推荐:
    (root@localhost) [(none)]> create database if not exists book
        -> default character set gb2312             编码格式
        -> default collate gb2312_chinese_ci;       校对规则
    Query OK, 1 row affected (0.01 sec)

使用数据库

  1. USE database 
    1
    2
    3
    (root@localhost) [(none)]> use book;
    Database changed
    (root@localhost) [book]>

修改数据库

  1. ALTER DATABASE database 
    1
    2
    3
    4
    (root@localhost) [book]> alter database book
        -> default character set gb2312
        -> default collate gb2312_chinese_ci;
    Query OK, 1 row affected (0.01 sec)

删除数据库

  1. DROP DATABASE database 
    1
    2
    (root@localhost) [book]> drop database book;
    Query OK, 0 rows affected (0.01 sec)