本文共 5269 字，大约阅读时间需要 17 分钟。

一、mariadb用户管理

1. 创建登录用户

MariaDB [(none)]>Create user username@ ‘%’ identified by ‘123456’	%: 指任意的远端ip	localhost：本地	ip(x.x.x.x)：远程主机地址	网段(x.x.x.0)：X.x.x. 远程网段

2. 用户修改密码

MariaDB [(none)]>Set password=password(‘111111’);

3. Root 用户为其他用户找回密码

MariaDB [(none)]>Set password for xxx@’%’ =password(‘123123’);

4. Root 找回自己的密码并修改

1. 关闭数据库，修改主配置文件（/etc/my.cnf） 添加： skip-grant-tables

   Vim /etc/my.cnf 			Skip-grant-tables

2. 启动数据库，空密码登录帮修改密码

   Update mysql.user set password=password(‘新密码’) where user=’root’;

3. 删除skip-grant-tables，重启数据可验证新密码

5. 用户授权

MariaDB [(none)]> create user admin@'%' identified by '123456';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> select user from mysql.user;+-------+| user  |+-------+| admin || root  || root  ||       || root  ||       || root  |+-------+7 rows in set (0.00 sec)

#给已存在的用户授权

MariaDB [(none)]> grant all on mariadb.student to admin@'%';Query OK, 0 rows affected (0.00 sec)

#创建新用户并授权

MariaDB [(none)]> grant all on mariadb.student to test@'%' identified by "123456";Query OK, 0 rows affected (0.00 sec)

#查看用户授权

MariaDB [(none)]> show grants for admin@'%' ;+------------------------------------------------------------------------------------------------------+| Grants for admin@%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT ALL PRIVILEGES ON `mariadb`.`student` TO 'admin'@'%'                                           |+------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

#取消用户鉴权

MariaDB [(none)]> revoke drop,delete on mariadb.student from admin@'%';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show grants for admin@'%' ;                          +---------------------------------------------------------------------------------------------------------------------------------------+| Grants for admin@%                                                                                                                    |+---------------------------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'                                  || GRANT SELECT, INSERT, UPDATE, CREATE, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `mariadb`.`student` TO 'admin'@'%' |+---------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

二、mariadb database操作管理

1.创建、查询及删除数据库

#创建

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

#查看

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || mariadb            || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)

#删除

MariaDB [(none)]> drop  databases mariadb;

2.创建/删除/修改数据表及查看表结构

#创建表

MariaDB [(none)]> use mariadb;Database changedMariaDB [mariadb]> create table student(id int,name char(30),age int);Query OK, 0 rows affected (0.02 sec)

#查看表结构

MariaDB [mariadb]> describe student;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(11)  | YES  |     | NULL    |       || name  | char(30) | YES  |     | NULL    |       || age   | int(11)  | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+3 rows in set (0.01 sec)

#删除表

MariaDB [mariadb]>  drop table student;

#修改表名称

MariaDB [mariadb]> alter table student rename teacher;

#修改表字段类型

MariaDB [mariadb]> alter table student modify name char(50);

#修改表的字段类型详情

MariaDB [mariadb]> alter table student change name username char(50) ;

#添加字段

MariaDB [mariadb]> alter table student add time datetime   #添加到末尾MariaDB [mariadb]> alter table student add birthday year first  #添加到第一列MariaDB [mariadb]> alter table student add sex nchar(1)  after id;  #添加到指令字段后

#删除字段

MariaDB [mariadb]> alter table student drop birthday;

3.插入/删除表数据

#插入

MariaDB [mariadb]> insert into student (id,name,age) values (20200401,'lisa',23);Query OK, 1 row affected (0.00 sec)MariaDB [mariadb]> insert into student values (20200402,'linda',24);             Query OK, 1 row affected (0.01 sec)MariaDB [mariadb]> insert into student values (20200403,'luna',24),(20200404,'hha',24);Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0MariaDB [mariadb]> select * from student;                                              +----------+-------+------+| id       | name  | age  |+----------+-------+------+| 20200401 | lisa  |   23 || 20200402 | linda |   24 || 20200403 | luna  |   24 || 20200404 | hha   |   24 |+----------+-------+------+4 rows in set (0.00 sec)

#删除

MariaDB [mariadb]>delete from student where id=20200402;MariaDB [mariadb]>delete from student where age between 25 and 26;

#修改

MariaDB [mariadb]> update  student set age=25 where id=20200404;

转载地址：http://tqfef.baihongyu.com/