MySQL 案例实战--MySQL 数据库的基础操作

前言

本环境是基于 Centos 7.8 系统构建MySQL-5.7.14
具体构建，请参考 MySQL-5.7.14 环境构建

---

一、查看数据库

• 查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

• 通配符匹配，模糊查询数据库

mysql> show databases like 'info%';
+--------------------+
| Database (info%)   |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

二、创建数据库

• 创建数据库 db1

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

• 创建数据库 db1,指定字符集

mysql> create database if not exists db2
    -> default character set utf8;
Query OK, 1 row affected (0.00 sec)

• 查看建库语句

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

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

三、切换数据库

mysql> use db1;
Database changed

四、更改数据库

ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;

五、删除数据库

mysql> drop database if exists db1;
Query OK, 0 rows affected (0.00 sec

六、查看数据库信息

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

#查看数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.14    |
+-----------+
1 row in set (0.00 sec)

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

#查看所有用户
mysql> select User,Host from mysql.user;
+-----------+--------------+
| User      | Host         |
+-----------+--------------+
| admin     | 192.168.%.%  |
| admin     | 192.168.43.% |
| mysql.sys | localhost    |
| root      | localhost    |
+-----------+--------------+
4 rows in set (0.00 sec)

#执行Linux命令
mysql> system date
Wed Oct 21 20:35:19 CST 2020

mysql> system free -m
              total        used        free      shared  buff/cache   available
Mem:           5786         431        4804          11         550        5116
Swap:          6015           0        6015
mysql>