欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库应用DDL、DML、DCL讲解

程序员文章站 2022-07-07 22:34:02
一、DDL(Data Definition Language)数据定义语言: 适用范围:对数据库中的某些对象 创建数据库 :CREATE DATABASE 1、创建表:...

一、DDL(Data Definition Language)数据定义语言:

适用范围:对数据库中的某些对象

创建数据库 :CREATE DATABASE

1、创建表:

CREATETABLE [IF NOT EXISTS] tbl_name (col1 type1,col2 type2,...)

col type1

PRIMARY KEY(col1,...)

INDEX(col1,...)

UNIQUE KEY(col1,...)

表选项:

ENGINE [=] engine_name

ROW_FORMAT [=]{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

获取帮助:mysql> help create table;

查看表创建表时使用的命令;

SHOWCREATE TABLE tb1_name;

查看某张表的状态;

SHOW TABLE STATUS LIKE 'tb1_name'\G

查看引擎

SHOW ENGINES;

[root@www ~]# mysql –uroot

#创建数据库

mysql> create database testdb;

Query OK, 1 rowaffected (0.02 sec)

#查看数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

| mysql |

| test |

| testdb |

+--------------------+

4 rows in set(0.00 sec)

使用那个数据库

mysql> use testdb;

Database changed

例1、创建表并定义定义主键

mysql> create table students (id intunsigned not null primary key,name varchar(20) not null,age tinyint unsigned);

Query OK, 0 rowsaffected (0.20 sec)

mysql> DESC students;

+-------+---------------------+------+-----+---------+-------+

| Field |Type | Null | Key |Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | PRI | NULL | |

| name | varchar(20) | NO | | NULL | |

| age | tinyint(3) unsigned | YES | |NULL | |

+-------+---------------------+------+-----+---------+-------+

3 rows in set(0.06 sec)

例2、定义联合主键

mysql> create table tb1(id int unsignednot null,name varchar(20) not null,age tinyint unsigned,primary key(id,name));

Query OK, 0 rowsaffected (0.01 sec)

mysql> desc tb1;

+-------+---------------------+------+-----+---------+-------+

| Field |Type | Null | Key |Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | PRI | NULL | |

| name | varchar(20) | NO | PRI | NULL | |

| age | tinyint(3) unsigned | YES | |NULL | |

+-------+---------------------+------+-----+---------+-------+

3 rows in set(0.00 sec)

例3、查看引擎

mysql> show engines;

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints|

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM |YES | Collection of identical MyISAMtables | NO | NO | NO |

| CSV | YES | CSV storage engine |NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL3.23 with great performance | NO | NO | NO |

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

+------------+---------+------------------------------------------------------------+--------------+------+------------+

5 rows in set(0.01 sec)

例4、查看表创建表时使用的命令

mysql> show create table students;

+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| students |CREATE TABLE `students` (

`id` int(10) unsigned NOT NULL,

`name` varchar(20) NOT NULL,

`age` tinyint(3) unsigned DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAMDEFAULT CHARSET=latin1 |

+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set(0.03 sec)

例5、查看某张表的状态;

mysql> show table status like'students';

+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

| Name | Engine | Version | Row_format | Rows |Avg_row_length | Data_length | Max_data_length | Index_length | Data_free |Auto_increment | Create_time |Update_time | Check_time |Collation | Checksum |Create_options | Comment |

+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

| students |MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2017-11-21 15:08:41 |2017-11-21 15:08:41 | NULL |latin1_swedish_ci | NULL | | |

+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

1 row in set(0.01 sec)

例6、\G竖行显示表状态;

mysql> show table status like'students'\G;

***************************1. row ***************************

Name: students 表名

Engine: MyISAM 存储引擎

Version: 10 版本

Row_format: Dynamic 行格式

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length:281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2017-11-21 15:08:41

Update_time: 2017-11-21 15:08:41

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set(0.00 sec)

2、删除修改表

删除表

DROP TABLE [IF EXISTS] 'tbl_name';

修改表

ALTER TABLE 'tb1_name'

字段:

添加:add

ADD coll1 data_type [FIRST|AFTER col_name]

删除字段:drop

修改字段:alter,change,modify

例1、修改表之添加字段[ADD]

mysql> ALTER TABLE students ADD gender ENUM('M','F');

Query OK, 0 rowsaffected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESCstudents;

+--------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra|

+--------+---------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | PRI | NULL | |

| name | varchar(20) | NO | | NULL | |

| age | tinyint(3) unsigned | YES | |NULL | |

| gender |enum('M','F') | YES | |NULL | |

+--------+---------------------+------+-----+---------+-------+

4 rows in set(0.03 sec)

例2、修改表字段名

mysql> ALTER TABLE students ADD gender ENUM('M','F');

Query OK, 0 rowsaffected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC students;

+--------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra|

+--------+---------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | PRI | NULL | |

| name | varchar(20) | NO | | NULL | |

| age | tinyint(3) unsigned | YES | |NULL | |

| gender |enum('M','F') | YES | |NULL | |

+--------+---------------------+------+-----+---------+-------+

4 rows in set(0.03 sec)

例3、删除字段:(drop)

mysql> ALTER TABLE students DROP age;

Query OK, 0 rowsaffected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC students;

+--------+------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+------------------+------+-----+---------+-------+

| sid | int(10) unsigned | NO | PRI | NULL | |

| name | varchar(20) | NO | UNI | NULL | |

| gender |enum('M','F') | YES | |NULL | |

+--------+------------------+------+-----+---------+-------+

3 rows in set(0.00 sec)

3、索引:

索引是特殊的数据结构,定义在查找时作为查找条件的字段;

索引:要有索引名称,键属于索引

创建索引 mysql> help CREATE INDEX;

CREATEINDEX index_name ON tbl_name(index_col_name,...)

删除索引:mysql> help drop index;

注意,索引一旦用不上应立即删除,否则每次查找删除修改数据时都会产生多余的io影响性能

DROPINDEX index_name ON tbl_name

例1、定义唯一键(unique)

mysql> alter table students add unique key(name);

Query OK, 0 rowsaffected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC students;

+--------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra|

+--------+---------------------+------+-----+---------+-------+

| sid | int(10) unsigned |NO | PRI | NULL | |

| name | varchar(20) | NO | UNI | NULL | |

| age | tinyint(3) unsigned | YES | |NULL | |

| gender |enum('M','F') | YES | |NULL | |

+--------+---------------------+------+-----+---------+-------+

4 rows in set(0.00 sec)

例2、为某字段创建索引(INDEX);注意,键属于特殊的索引;

mysql> alter table students addindex(age);

Query OK, 0 rowsaffected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC students;

+--------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra|

+--------+---------------------+------+-----+---------+-------+

| sid | int(10) unsigned | NO | PRI | NULL | |

| name | varchar(20) | NO | UNI | NULL | |

| age | tinyint(3) unsigned | YES | MUL | NULL | |

| gender |enum('M','F') | YES | |NULL | |

+--------+---------------------+------+-----+---------+-------+

4 rows in set(0.00 sec)

例3、查看表的索引

mysql> SHOW INDEXES FROM students;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index |Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| students | 0 | PRIMARY | 1 | sid | A | 0 | NULL | NULL | | BTREE | |

| students| 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | |

| students| 1 | age | 1 | age | A | NULL | NULL | NULL | YES | BTREE | |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

3 rows in set(0.00 sec)

例4、删除字段:(drop)

mysql> ALTER TABLE students DROP age;

Query OK, 0 rowsaffected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC students;

+--------+------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+------------------+------+-----+---------+-------+

| sid | int(10) unsigned | NO | PRI | NULL | |

| name | varchar(20) | NO | UNI | NULL | |

| gender |enum('M','F') | YES | |NULL | |

+--------+------------------+------+-----+---------+-------+

3 rows in set(0.00 sec)

mysql> show index from students;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index |Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| students| 0 | PRIMARY | 1 | sid | A | 0 | NULL | NULL | | BTREE | |

| students| 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set(0.00 sec)

例5、删除索引连同字段

mysql> DROP INDEX name ON students;

Query OK, 0 rowsaffected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM students;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index |Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| students| 0 | PRIMARY | 1 | sid | A | 0 | NULL | NULL | | BTREE | |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

1 row in set(0.01 sec)

二、DML(Data Manipulation Language)数据操纵语言

适用范围:对数据库中的数据进行一些简单操作:

INSERT,DELETE,SELECT,UPDATE增删查改

1、INSERT INTO

INSERT [INTO] tbl_name[(col_name,...)]{VALUES | VALUE} (val1,...),(...),...

例1、为students表插入数值

mysql> INSERT INTO students VALUES(1,'YangGuo','M'),(2,'GuoXiang','F');

Query OK, 2 rowsaffected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from students;

+-----+----------+--------+

| sid |name | gender |

+-----+----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

+-----+----------+--------+

2 rows in set(0.02 sec)

例2、为某些字段插入数值

mysql> INSERT INTO students (sid,name)VALUES (3,'zhangWuji'),(4,'ZhaoMin');

Query OK, 2 rowsaffected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from students;

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

| 3 | zhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

4 rows in set(0.00 sec)

2、SELECT:

SELECTcol1,col2,... FROM tb1_name [WHERE clause] [ORDER BY 'col_name] [LIMIT [m,]n];

字段表示法:

*:所有字段;

as:字段别名,col1 AS alias1;

WHERE clause:

条件符:

>,<,==,>=,<=,!=

between ... and ...

LIKE:

%:任意长度任意字符;

_:任意单个字符;

RLIKE:基于正则表达式做模式匹配,效率低。

IS NULL

IS NOT NULL

条件逻辑操作:

and,or,not

例1、基于条件符查找

mysql> select * from students wheresid<3;

+-----+----------+--------+

| sid |name | gender |

+-----+----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

+-----+----------+--------+

2 rows in set(0.03 sec)

mysql> select * from students wheregender IS NULL;

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 3 | zhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

2 rows in set(0.02 sec)

mysql> select * from students wheregender IS NOT NULL;

+-----+----------+--------+

| sid |name | gender |

+-----+----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

+-----+----------+--------+

2 rows in set(0.00 sec)

例2.字段定义查找

mysql> select * from students wheregender='M';

+-----+---------+--------+

| sid |name | gender |

+-----+---------+--------+

| 1 | YangGuo | M |

+-----+---------+--------+

1 row in set(0.00 sec)

mysql> SELECT * FROM students ;

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

| 3 | zhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

4 rows in set (0.00sec)

例3、查找后基于name排序

mysql> SELECT * FROM students ORDER BYname;

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 2 | GuoXiang | F |

| 1 | YangGuo | M |

| 3 | zhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

4 rows in set(0.03 sec)

例4、降序排序

mysql> SELECT * FROM students ORDER BYname DESC;

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 4 | ZhaoMin | NULL |

| 3 | zhangWuji | NULL |

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

+-----+-----------+--------+

4 rows in set(0.00 sec)

例5、限制字段查询

mysql> SELECT * FROM students ORDER BYname DESC LIMIT 2;

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 4 | ZhaoMin | NULL |

| 3 | zhangWuji | NULL |

+-----+-----------+--------+

2 rows in set(0.00 sec)

mysql> SELECT * FROM students ORDER BYname DESC LIMIT 1,2;

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 3 | zhangWuji | NULL |

| 1 | YangGuo | M |

+-----+-----------+--------+

2 rows in set(0.00 sec)

例6、基于条件逻辑操作查询

mysql> select * from students wheresid>=2 and sid<=4;

+-----+-----------+--------+

| sid | name | gender |

+-----+-----------+--------+

| 2 | GuoXiang | F |

| 3 | zhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

3 rows in set(0.00 sec)

mysql> select * from students where sidbetween 2 and 4;

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 2 | GuoXiang | F |

| 3 | zhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

3 rows in set(0.00 sec)

例7、字符匹配查询

mysql> SELECT * FROM students WHERE name LIKE 'Z%';

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 3 | zhangWuji | NULL |

| 4 | ZhaoMin | NULL |

+-----+-----------+--------+

2 rows in set(0.00 sec)

例8、基于正则表达式做模式匹配查询

mysql> select * from students where namerlike '.*u.';

+-----+-----------+--------+

| sid |name | gender |

+-----+-----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

| 3 | zhangWuji | NULL |

+-----+-----------+--------+

3 rows in set(0.01 sec)

mysql> select * from students where namerlike '.*[A-G]u.';

+-----+----------+--------+

| sid |name | gender |

+-----+----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

+-----+----------+--------+

2 rows in set(0.00 sec)

例9、查询后使用字段别名显示:as

mysql> SELECT sid as stuid,name asstuname from students;

+-------+-----------+

| stuid |stuname |

+-------+-----------+

| 1 | YangGuo |

| 2 | GuoXiang |

| 3 | zhangWuji |

| 4 | ZhaoMin |

+-------+-----------+

4 rows in set(0.00 sec)

3、DELETE:

DELETE FROM tb1_name [WHERE clause] [ORDERBY 'col_name' [DESC]] [LIMIT [m,]n];

例1、删除students表中sid为3的字段

mysql> DELETE FROM students WHERE sid=3;

Query OK, 1 rowaffected (0.01 sec)

mysql> select * from students;

+-----+----------+--------+

| sid |name | gender |

+-----+----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

| 4 | ZhaoMin | NULL |

+-----+----------+--------+

3 rows in set(0.01 sec)

4、UPDATE:

UPDATE tb1_name SETcol2=new_val1,col2=new_val2,... [WHERE clause] [ORDER BY 'col_name'[DESC]][LIMIT [m,]n];

例1、修改表中sid为4的性别为F

mysql> UPDATE students SET gender='F'WHERE sid=4;

Query OK, 1 rowaffected (0.00 sec)

Rows matched:1 Changed: 1 Warnings: 0

mysql> select * from students;

+-----+----------+--------+

| sid |name | gender |

+-----+----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

| 4 | ZhaoMin | F |

+-----+----------+--------+

3 rows in set (0.00sec)

三、DCL/用户账号及权限管理:

用户账号:'user@host'

user:用户名

host:此用户访问mysqld服务时允许通过那些主机远程创建连接;

IP、网络地址、主机名、通配符(%和_);

禁止检查主机名:

my.conf,[mysql]

skyp_name-resolve = yes

1、创建用户账号:mysql> help create user

CREATE USER‘username'@'host' [IDENTIFIED BY [PASSWORD] 'password']

示例:不给定权限创造用户field:

mysql> CREATE USER 'field'@'%'IDENTIFIED BY '8357690';

Query OK, 0 rowsaffected (0.08 sec)

mysql> use mysql;

Reading tableinformation for completion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql> select User,Host,Password FROMuser;

+----------+---------------+-------------------------------------------+

| User | Host | Password |

+----------+---------------+-------------------------------------------+

| root | localhost | |

| root | www.field.com | |

| root | 127.0.0.1 | |

| | localhost | |

| | www.field.com | |

| shopuser |localhost |*A1A66A9C24F2F46038A5E19159F93EC357B197A8 |

| shopuser |127.0.0.1 |*A1A66A9C24F2F46038A5E19159F93EC357B197A8 |

| field | % |*90C4C8F6A4ACEA4588AB0C9E2A5BECD99A4A80DD |

+----------+---------------+-------------------------------------------+

8 rows in set(0.00 sec)

另一台机上登录:

[root@test ~]# mysql -ufield-h192.168.88.131 -p

Enter password:

Welcome to theMySQL monitor. Commands end with ; or\g.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

| test |

+--------------------+

2 rows in set(0.01 sec)

mysql> show grants for 'field'@'%';

+------------------------------------------------------------------------------------------------------+

| Grants forfield@% |

+------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON*.* TO 'field'@'%' IDENTIFIED BY PASSWORD'*90C4C8F6A4ACEA4588AB0C9E2A5BECD99A4A80DD' |

+------------------------------------------------------------------------------------------------------+

1 row in set(0.00 sec)

mysql> show grants for'root'@'localhost';

+---------------------------------------------------------------------+

| Grants forroot@localhost |

+---------------------------------------------------------------------+

| GRANT ALLPRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

1 row in set(0.00 sec)

mysql> SHOW GRANTS FOR CURRENT_USER;

+---------------------------------------------------------------------+

| Grants forroot@localhost |

+---------------------------------------------------------------------+

| GRANT ALLPRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

1 row in set(0.00 sec)

2、删除用户:

DROPUSER 'usernam’@'host';

3、授权:mysql> help grant

权限:管理权限、数据库、表、字段、存储例程;

GRANT priv_type,... ON [object_type]db_name.tb_name TO 'usernam’@'host'[IDENTIFIED BY 'password'];

priv_type;ALL [PRIVILEGES]

db_name.tb_name:

db_name.*:指定库的所有表;

db_name.tb_name:指定库的指定表;

db_name.routine_name:指定库的存储例程;

查看指定用户获得的授权:

SHOWGRANTS FOR 'user'@'host';

SHOWGRANTS FOR CURRENT_USER;

回收授权:

REVOKE priv_type,... ON db_name.tb_name FROM 'usernam’@'host';

注意:MariaDB服务进程启动时会读取MySQL库表中的所有授权表至内存中:

(1)GRANT或REVOKE等执行权限操作会保存于表中,MariaDB的服务进程会自动重读授权表;

(2)对于不能够或不能及时重读授权表的命令,可手动让MariaDB的服务进程重读授权表;

mysql>FLUSH PRIVILEGES;

例1、创建testuser用户并赋予其在数据库testdb上具备SELECT,DELETE权限

[root@www ~]# mysql -u root

Welcome to theMySQL monitor. Commands end with ; or\g.

mysql> GRANT SELECT,DELETE ON testdb.* TO'testuser'@'%' IDENTIFIED BY 'testpass';

Query OK, 0 rowsaffected (0.03 sec)

用testuser远程连接数据库,查看是否授权成功;

[root@test ~]# mysql -utestuser -h192.168.88.131-p

Enter password:

Welcome to theMySQL monitor. Commands end with ; or\g.

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

|information_schema |

| test |

| testdb |

+--------------------+

3 rows in set(0.00 sec)

mysql> use testdb;

Reading table informationfor completion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql> show grants for CURRENT_USER;

+---------------------------------------------------------------------------------------------------------+

| Grants fortestuser@% |

+---------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON*.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |

| GRANT SELECT,DELETE ON `testdb`.* TO 'testuser'@'%' |

+---------------------------------------------------------------------------------------------------------+

2 rows in set(0.00 sec)

mysql> CREATE TABLE tb2(id int);

ERROR 1142(42000): CREATE command denied to user 'testuser'@'test.field.com' for table'tb2'

#不具备CREATE权限,无法创建表

mysql> SELECT * FROM students;

+-----+----------+--------+

| sid |name | gender |

+-----+----------+--------+

| 1 | YangGuo | M |

| 2 | GuoXiang | F |

| 4 | ZhaoMin | F |

+-----+----------+--------+

3 rows in set(0.02 sec)

mysql> DELETE FROM students WHERE sid=1;

Query OK, 1 rowaffected (0.02 sec)

#具备DELETE权限,可以删除某字段

mysql> SELECT * FROM students;

+-----+----------+--------+

| sid |name | gender |

+-----+----------+--------+

| 2 | GuoXiang | F |

| 4 | ZhaoMin | F |

+-----+----------+--------+

2 rows in set(0.00 sec)

mysql> UPDATE students SET gender='M'WHERE sid=2;

ERROR 1142(42000): UPDATE command denied to user 'testuser'@'test.field.com' for table'students'

#不具备UPDATE权限,无法修改字段。

例2、回收testuser用户在数据库testdb上具备的DELETE权限

[root@www ~]# mysql -u root

Welcome to theMySQL monitor. Commands end with ; or\g.

mysql> REVOKE DELETE ON testdb.* FROM 'testuser'@'%';

Query OK, 0 rowsaffected (0.02 sec)

远程确认是否回收成功

[root@test ~]# mysql -utestuser-h192.168.88.131 -p

Enter password:

Welcome to theMySQL monitor. Commands end with ; or\g.:

mysql> show grants for testuser;

+---------------------------------------------------------------------------------------------------------+

| Grants fortestuser@% |

+---------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON*.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'|

| GRANT SELECTON `testdb`.* TO 'testuser'@'%' |

+---------------------------------------------------------------------------------------------------------+

2 rows in set(0.00 sec)