1、创建/编辑数据库

# 创建字符集为utf8的数据库testuser
mysql> create database testuser character set \'utf8\';
Query OK, 1 row affected (0.01 sec)

# 创建数据库不指定字符集
mysql> create database testuser1;
Query OK, 1 row affected (0.01 sec)

# 判断创建的数据库存不存在，若不存在创建数据库
mysql> create database if not exists testuser;
Query OK, 1 row affected, 1 warning (0.00 sec)

# 修改数据库字符集
mysql> alter database deluser character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database deluser;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| deluser  | CREATE DATABASE `deluser` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

2、删除数据库

mysql> drop database deluser;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| bidding_db           |
| inverstment_projects |
| invest               |
| invest_authority     |
| jira                 |
| mysql                |
| performance_schema   |
| shengli              |
| sys                  |
| testuser             |
+----------------------+
11 rows in set (0.00 sec)

3、查看数据库

# 切换数据库
mysql> use testuser;
Database changed

# 查询所有的数据库
mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| bidding_db           |
| inverstment_projects |
| invest               |
| invest_authority     |
| jira                 |
| mysql                |
| performance_schema   |
| shengli              |
| sys                  |
| testuser             |
+----------------------+
11 rows in set (0.00 sec)

# 查看某个已创建的数据库
mysql> show create database testuser;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| testuser | CREATE DATABASE `testuser` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

# 查看当前所在的数据库
mysql> select database();
+------------+
| database() |
+------------+
| testuser   |
+------------+
1 row in set (0.00 sec)

# 查看用户
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

4、创建/编辑表

# 创建一张用户信息表
mysql> create table user(
    -> name varchar(10),
    -> gender varchar(10),
    -> hobby varchar(50));
Query OK, 0 rows affected (0.02 sec)

# 修改表名称
mysql> rename table user to sys_user;
Query OK, 0 rows affected (0.01 sec)

# 默认最后一列追加字段，关键词add
mysql> alter table user add phone int(11);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 在首列追加字段，关键词first
mysql> alter table user add id int(11) first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 在某列后面追加字段，关键词after
mysql> alter table user add age int(3) after name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除字段，关键词drop
mysql> alter table user drop hobby ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 修改字段名称，顺带修改字符类型
mysql> alter table user change gender addre varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 只修改字段属性
mysql> alter table user modify addre varchar(255);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

5、删除表

mysql> drop table user1;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+--------------------+
| Tables_in_testuser |
+--------------------+
| user               |
+--------------------+
1 row in set (0.00 sec)

6、查看表

# 查看支持的引擎类型
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+--
| Engine             | Support | Comment                                           | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+--
| InnoDB             | DEFAULT | Supports transactions,...						   | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful ...          | NO           | NO   | NO         |
...
+--------------------+---------+----------------------------------------------------------------+--------------+------+--
9 rows in set (0.00 sec)

# 查看当前库中所有的表
mysql> show tables;
+--------------------+
| Tables_in_testuser |
+--------------------+
| user               |
+--------------------+
1 row in set (0.00 sec)

# 查看某个库中所有的表
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
...
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

# 查看表状态
mysql> show table status like \'user\'\G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-08-05 14:22:31
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

# 查看表结构
mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| hobby  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 查看创建的表
mysql> show create table user;
+-------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(10) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `hobby` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 复制查看的表同事复制数据
mysql> show tables;
+--------------------+
| Tables_in_testuser |
+--------------------+
| user               |
| user1              |
+--------------------+
2 rows in set (0.00 sec)