MySQL数据库应用DDL、DML、DCL讲解
一、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)
上一篇: oracle教程之Oracle的多表查询举例(笛卡尔集、等值与非等值连接、外连接、自连接、SQL1999连接、Oracle的set运算符)实例
下一篇: C题解:有一棵树,一开始每个点有一个初始值,每一个点的新数为它到树顶的路径中的所有数,去掉一个数后的最大公因数中的最大值
推荐阅读