By | 2021-07-13
  • 连接到数据库服务器
root@localhost:~# mysql -u root -p
  • 数据库用户操作
# 查看数据库用户
MariaDB [(none)]> select host,user,password,plugin,authentication_string from mysql.user;

# 创建用户
MariaDB [(none)]> create user 'test'@'localhost' identified by '123456';

# 修改密码
MariaDB [(none)]> alter user 'test'@'localhost' identified by 'Test@1234';

# 删除用户
MariaDB [(none)]> drop user 'test'@'localhost';
  • 数据库权限操作
# 查看用户权限
# 方法一
MariaDB [(none)]> show grants for root@'localhost';
# 方法二
MariaDB [(none)]> select * from mysql.user where user='root'\G;

# 授予用户在本地服务器对指定数据库的全部权限
MariaDB [(none)]> grant all privileges on mydb.* to 'test'@'localhost';

# 撤销用户权限
MariaDB [(none)]> revoke all privileges on mydb.* to 'test'@'localhost';

# 刷新系统权限相关表
MariaDB [(none)]> flush privileges;
  • 数据库操作
# 查看所有数据库
MariaDB [(none)]> show databases;

# 创建新数据库并指定"utf8mb4"编码
MariaDB [(none)]> create database `mydb` character set utf8mb4 collate utf8mb4_general_ci;

# 查看数据库定义声明
MariaDB [(none)]> show create database mydb;

# 修改数据库编码为"gb2312"
MariaDB [(none)]> alter database mydb default character set gb2312 default collate gb2312_chinese_ci;

#选择数据库
MariaDB [(none)]> use mydb;

# 删除数据库
MariaDB [(none)]> drop database mydb;
  • 数据表操作
# 查看数据库所有表
MariaDB [mydb]> show tables;

# 查看表定义声明
MariaDB [(none)]> show create table username;

# 显示表结构
MariaDB [mydb]> desc username;