一、数据库基本操作
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