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

程序员文章站 2022-03-31 10:27:55

一、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;

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

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

| id | int(10) unsigned | NO | PRI | 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;

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

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

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

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

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

3 rows in set(0.00 sec)

例3、查看引擎

mysql> show engines;

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

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

| 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';

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

| 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;

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

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

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

| age | tinyint(3) unsigned | 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;

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

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

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

| age | tinyint(3) unsigned | 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;

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

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

| sid | int(10) unsigned | NO | PRI | 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;

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

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

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

| age | tinyint(3) unsigned | 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;

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

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

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

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

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

4 rows in set(0.00 sec)

例3、查看表的索引

mysql> SHOW INDEXES FROM students;

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

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

| students | 0 | PRIMARY | 1 | sid | 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;

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

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

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

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

3 rows in set(0.00 sec)

mysql> show index from students;

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

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

| students| 0 | PRIMARY | 1 | sid | 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;

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

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

| 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)

上一篇： Android中转义字符

下一篇： Windows下如何更改MySQL数据库的存储位置？

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

数据库语句分类：DDL、DML、DCL

SQL基础、DDL语句、DML语句、DCL语句实例讲解

数据库语言分类DDL、DCL、DML详解

SQL语言分类：DCL,DDL,DML,DQL,的区别讲解

Oracle数据库基本操作（一） —— Oracle数据库体系结构介绍、DDL、DCL、DML

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

MySQL数据库（四）：DML（增、改、删）、DDL（表的创建、修改、删除、复制等）、约束、标识列(自增长列)

DDL、DML和DCL的理解_MySQL

DDL、DML和DCL的理解_MySQL

SQL基础、DDL语句、DML语句、DCL语句实例讲解