mariadb数据库（二）

一、数据库基本操作

1.查看数据库操作

查看数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.002 sec)

查看创建数据库状态

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

使用哪个库

MariaDB [(none)]> use mysql;

如果想退出这个库是不能退出的，但可以show databases;查询所有库，再使用use 库名 切换到那个库

退出mariadb数据库

exit
quit
\q
ctrl + c
ctrl + d

查看数据库版本

[root@c3 ~]# mysqladmin --version
mysqladmin  Ver 9.1 Distrib 10.3.14-MariaDB, for Linux on x86_64

2.修改数据库

创建数据库

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.001 sec)

删除数据库

MariaDB [(none)]> drop database test;

修改数据库默认字符集

MariaDB [(none)]> alter database 库名 default character set=utf8;

3.用户管理

查看当前用户

MariaDB [(none)]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

创建test用户并给test用户授予查，改，更新，删所有库的所有表的权限（在任意主机上登录）

MariaDB [mysql]> grant select,create,update,delete on *.* to test@'%' identified by '123';

创建test1用户并给test用户授予最高权限在所有库的所有表（只能在本地主机登录）

MariaDB [mysql]> grant all on *.* to 'test1'@'localhost' identified by '321';

查看test用户的权限

MariaDB [mysql]> show grants for 'test';
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE, CREATE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

查看mysql库的user表

MariaDB [mysql]> select user,host,password from mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root  | c3        | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root  | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root  | ::1       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| test  | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| test  | %         | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| test1 | localhost | *7