MySQL单表查询、用户授权

最新推荐文章于 2024-04-23 09:30:54 发布

奋斗的蜗牛_-

最新推荐文章于 2024-04-23 09:30:54 发布

阅读量175

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_69157431/article/details/132481748

版权

MySQL单表查询、用户授权

(1)
#创建数据表orders,在0_num字段上添加主键约束和自增约束，在c_id字段上添加
外键约束，关联customers表中的主键c_num
mysql> create table orders( 
    ->   0_num int(11) primary key auto_increment, 
    ->   0_date date, 
    ->   c_id int(11), 
    ->   foreign key(c_id) references customers(c_num)
    ->   );
(2)
#查看创建表时的外键名为orders_ibfk_1
mysql> show create table orders;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                              |
+--------+-------------------------------------------------------------------------------
-----------------------------------------+
| orders | CREATE TABLE `orders` (
  `0_num` int(11) NOT NULL AUTO_INCREMENT,
  `0_date` date DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`0_num`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `customers` (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------
------------------------------------------+
#删除orders表的外键约束
mysql> alter table orders drop foreign key orders_ibfk_1;
#删除表customers
mysql> drop table customers;

mysql> CREATE DATABASE Team;
mysql> use Team;
mysql> CREATE TABLE player
    -> （
    ->   playid INT PRIMARY KEY,
    ->   playname VARCHAR(30) NOT NULL,
    ->   teamnum INT NOT NULL UNIQUE,
    ->   info VARCHAR(50)
    -> ）;
(1)
#创建用户account1通过本地连接数据库密码为oldpwd1
mysql> create user account1@localhost identified by 'oldpwd1';
#赋予account1用户SELECT、INSERT和对表player的info列的UPDATE权限
mysql> grant select on player.* to account1@localhost;
mysql> grant insert on player.* to account1@localhost;
mysql> grant update(info) on player to account1@localhost;
(2)
#更改该用户的密码
mysql> alter user account1@localhost identified by 'newpwd2';
(3)
#重新加载权限表
mysql> flush privileges;
(4)
#查看授权给该用户的权限
mysql> show grants for account1@localhost;
+------------------------------------------------------------------+
| Grants for account1@localhost                                    |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'account1'@'localhost'                     |
| GRANT SELECT, INSERT ON `player`.* TO 'account1'@'localhost'     |
| GRANT UPDATE (info) ON `Team`.`player` TO 'account1'@'localhost' |
+------------------------------------------------------------------+
3 rows in set (0.00 sec)
(5)
#收回授权给该用户的权限
mysql> revoke insert,select,update(info) on Team.player from account1@localhost;
(6)
#删除该用户
mysql> drop user account1@localhost;