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>