MySQL数据库应用（DDL/DML/DCL）

不念不忘少年蓝@ 2022-06-05 09:52 161阅读 0赞

**一、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)

MySQL数据库应用（DDL/DML/DCL）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关数据库原理及MySQL应用 | 视图

相关数据库原理及MySQL应用 | 事件

相关数据库原理及MySQL应用 | 关系规范化

相关数据库原理及MySQL应用 | 数据表操作

相关数据库原理及MySQL应用 | 约束

相关数据库原理及MySQL应用 | 数据库安全加固

相关数据库原理及 MySQL 应用 | 并发控制

相关 MySQL数据库应用（DDL/DML/DCL）

相关《MySQL 数据库技术与应用》教学大纲

相关 MySQL数据库应用实践