//创建数据库team,定义数据表player
mysql> create database team;
Query OK, 1 row affected (0.00 sec)
mysql> use team;
Database changed
mysql> create table player(
-> playid INT PRIMARY KEY,
-> playname VARCHAR(30) NOT NULL,
-> teamnum INT NOT NULL UNIQUE,
-> info VARCHAR(50));
Query OK, 0 rows affected (0.01 sec)
mysql> desc player;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| playid | int(11) | NO | PRI | NULL | |
| playname | varchar(30) | NO | | NULL | |
| teamnum | int(11) | NO | UNI | NULL | |
| info | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
// 1、创建一个新账户,用户名为accountl,该用户通过本地主机连接数据库,密码为oldpwd1。授权该用户对Team数据库中 player表的SELECT 和 INSERT权限,并且授权该用户对player表的info字段的UPDATE权限。
mysql> create user account1@localhost identified by 'oldpwd1';
Query OK, 0 rows affected (0.01 sec)
// 2、创建SQL 语句,更改account1用户的密码为newpwd2。
mysql> grant select,insert,update(info) on team.player to account1@localhost;
Query OK, 0 rows affected (0.01 sec)
// 3、创建SQL语句,使用FLUSH PRIVILEGES重新加载权限表。
mysql> alter user account1@localhost identified by 'oldpwd2';
Query OK, 0 rows affected (0.00 sec)
// 4、创建SQL语句,查看授权给account1用户的权限。
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for account1@localhost;
+----------------------------------------------------------------------------------+
| Grants for account1@localhost |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'account1'@'localhost' |
| GRANT SELECT, INSERT, UPDATE (info) ON `team`.`player` TO 'account1'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
// 5、创建SQL语句,收回account1用户的权限。
mysql> revoke all on team.player from account1@localhost;
Query OK, 0 rows affected (0.00 sec)
// 6、创建SOL语句,将account1用户的账号信息从系统中删除。
mysql> drop user account1@localhost;
Query OK, 0 rows affected (0.00 sec)