MySQL数据库应用(DDL/DML/DCL)

时间:2021-03-22 18:44:56

一、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库表中的所有授权表至内存中:

      1GRANTREVOKE等执行权限操作会保存于表中,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)