MySQL基础学习篇(6)——DCL+结语

MySql基础学习

注明：文章笔记均来自哔哩哔哩视频学习

十二、权限管理

12.1 创建用户

语法：CREATE USER 用户名 IDENTIFIED BY 密码

#创建一个用户
CREATE USER 'client1' IDENTIFIED BY '123456'; 

接下来验证用户是否添加成功，首先新建连接，在用户名和账号那里写上自己创建的用户和密码，测试一下是否连接成功。

然后点“确定”。但是打开该连接之后你会发现此连接中除了一个系统数据库，其他数据库都不存在。这是因为client1用户建立的连接没有得到任何授权，也就是没有对数据库的访问操作权限。

12.2 授权

语法：GRANT ALL ON 数据库.表 TO 用户名;

#将companydb下的所有表的权限都赋给client
GRANT ALL ON companydb.* TO client1;

该语句需要在root连接中执行，不能在client1连接中自己给自己授权。

12.3 撤销权限

语法：REVOKE ALL ON 数据库.表名 FROM 用户名

• 注意：撤销权限后，账户需要重新连接客户端才会生效。

#将client1的companydb的操作权限撤销
GRANT ALL ON companydb.* TO client1;

虽然刷新之后表不见了，但是依然可以对表进行操作，只有在重新连接数据库的时候权限才会消失。

12.4 删除用户

语法：DROP USER 用户名

#删除用户client1
DROP USER 'client1';

十三、视图

13.1 概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图表中，用户可以使用SELECT语句查询数据，也可以使用INSERT、UPDATE、DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

• 和临时表很像，但临时表不会被保存，而视图是保存下来的表。

13.2 视图特点

• 优点
  • 简单化，数据所见即所得。
  • 安全性，用户只能查询或修改他们所能见到的数据。
  • 逻辑独立性，可以屏蔽真实表结构变化带来的影响。
• 缺点
  • 性能相对较差，简单的查询也会变得稍显复杂。
  • 修改不方便，特别是复杂的聚合视图基本无法修改。

13.3 视图的创建

语法：CREATE VIEW 视图名 AS 查询数据源表结构语句;

13.3.1 创建视图

#创建t_emp_info的视图，其视图从t_employees表中查询员工编号、姓名、邮箱、工资
CREATE VIEW t_emp_info
AS
SELECT EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY FROM t_employees;

13.3.2 使用视图

#查询t_emp_info视图中编号为036的员工信息
SELECT * FROM t_emp_info WHERE EMPLOYEE_ID = 036;

使用视图可以简化查询的操作，将来视图应用最多的地方也是查询。

13.4 视图的修改

• 方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句
• 方式二：ALTER VIEW 视图名 AS 查询语句

方式一是在不明确视图是否存在时使用，如果存在则修改，否则创建；方式二是明确存在时进行修改。无论哪种方式都需要拼接一个完整查询语句。

#方式一，如果视图存在则修改，反之创建。
CREATE OR REPLACE VIEW t_emp_info
AS
SELECT EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,JOB_ID FROM t_employees;
#方式二，对已存在的视图进行修改
ALTER VIEW t_emp_info
AS
SELECT EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY FROM t_employees;

13.5 视图的删除

语法：DROP VIEW 视图名

#删除视图
DROP VIEW t_emp_info;

• 注意：删除视图不会影响原表。

13.6 视图的注意事项

• 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
• 如果视图包含以下结构中的一种，则视图不可更新：
  • 聚合函数的结果
  • DISTINCT去重后的结果
  • GROUP BY分组后的结果
  • HAVING筛选过滤后的结果
  • UNION、UNION ALL联合后的结果

十四、SQL语言分类

1. 数据查询语言DQL（Data Query Language）：SELECT、WHERE、ORDER BY、GROUP BY、HAVING；
2. 数据定义语言DDL（Data Definition Language）：CREATE、ALTER、DROP；
3. 数据操作语言DML（Data Manipulation Language）：INSERT、UNPATE、DELETE；
4. 事务处理语言TPL（Transaction Process Language）：COMMIT、ROLLBACK；
5. 数据控制语言DCL（Data Control Language）：GRANT、REVOKE。

十五、综合练习

某网上购物商城数据库表的结构如下：

# 创建用户表
CREATE TABLE USER(
	userId INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20) NOT NULL,
	PASSWORD VARCHAR(18) NOT NULL,
	address VARCHAR(100),
	phone VARCHAR(11)
);
SELECT * FROM USER;
INSERT INTO USER(username, PASSWORD, address,phone) VALUES('张三','123','北京昌平','13541489201');
INSERT INTO USER(username, PASSWORD, address,phone) VALUES('李四','678','北京海定','13541489202');
INSERT INTO USER(username, PASSWORD, address,phone) VALUES('王五','123','北京朝阳','13541489203');
INSERT INTO USER(username, PASSWORD, address,phone) VALUES('赵柳','123','北京大兴','13541489204');
# 创建分类表
CREATE TABLE category(
	cid VARCHAR(32) PRIMARY KEY,
	cname VARCHAR(100) NOT NULL  # 分类名称
);

INSERT INTO category VALUES('c001','电器');
INSERT INTO category VALUES('c002','服饰');
INSERT INTO category VALUES('c003','化妆品');
INSERT INTO category VALUES('c004','书籍');

# 商品表
CREATE TABLE `products`(
	`pid` VARCHAR(32) PRIMARY KEY,
	`name` VARCHAR(40),
	`price` DOUBLE(7,2),
	category_id VARCHAR(32),
	CONSTRAINT fk_products_category_id FOREIGN KEY (category_id) REFERENCES category(cid)
);

INSERT INTO products(pid, NAME, price, category_id) VALUES('p001','联想',5000,'c001');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p002','海尔',3000,'c001');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p003','雷神',5000,'c001');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p004','JACK',800,'c001');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p005','真维斯',200,'c002');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p006','花花公子',440,'c002');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p007','劲霸',2000,'c002');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p008','香奈儿',800,'c003');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p009','相宜本草 ',200,'c003');
INSERT INTO products(pid, NAME, price, category_id) VALUES('p0010','梅明子',200,NULL);
# 订单表
CREATE TABLE `orders` (
	`oid` VARCHAR(32) PRIMARY KEY,
	`totalprice` DOUBLE(12,2), # 总计
	`userId` INT,
	CONSTRAINT fk_orders_userId FOREIGN KEY(userId) REFERENCES USER(userId) # 外键
);

INSERT INTO orders VALUES('o6100',18000.50,1);
INSERT INTO orders VALUES('o6101',7200.35,1);
INSERT INTO orders VALUES('o6102',600.00,2);
INSERT INTO orders VALUES('o6103',1300.26,4);
SELECT * FROM orders;
# 订单详情表
CREATE TABLE orderitem(
	oid VARCHAR(32), # 订单id
	pid VARCHAR(32), # 商品id
	num INT, # 购买商品数量
	PRIMARY KEY(oid, pid), # 主键
	CONSTRAINT fk_orderitem_oid FOREIGN KEY(oid) REFERENCES orders(oid),
	CONSTRAINT fk_orderitem_pid FOREIGN KEY(pid) REFERENCES products(pid)
);
# 订单详情表添加信息
INSERT INTO orderitem VALUES('o6100','p001',1),('o6100','p002',1),('o6101','p003',1);

15.1 综合练习1-【多表查询】

1. 查询所有用户的订单

   # 查询所有用户的订单
   # 用户	订单表
   SELECT * FROM USER INNER JOIN orders ON orders.`userId` = user.`userId`;
   
   

2. 查询用户id为1的所有订单详情

   # 查询用户id为1的所有订单详情
   # 用户	 订单 	订单详情表
   SELECT * FROM USER
   INNER JOIN orders
   ON orders.`userId` = user.`userId`
   INNER JOIN orderitem
   ON orders.`oid` = orderitem.`oid`
   WHERE user.`userId` = 1;
   

15.2 综合练习2-【子查询】

1. 查看用户为张三的订单

   # 查看用户为张三的订单
   # 子查询单行单列
   SELECT userId FROM USER WHERE username='张三';
   SELECT * FROM orders WHERE userId=(SELECT userId FROM USER WHERE username='张三');
   
   

2. 查询出订单的价格大于800的所有用户的信息

   # 查询出订单的价格大于800的所有用户的信息
   # 子查询	多行单列	枚举查询
   SELECT DISTINCT userId FROM orders WHERE totalprice > 800;
   SELECT * FROM USER WHERE userId IN (SELECT DISTINCT userId FROM orders WHERE totalprice > 800);
   
   

15.3 综合练习3-【分页查询】

1. 查询所有订单信息，每页显示5条数据

   # 查询所有订单信息，每页显示5条数据
   # 查询第一页
   SELECT * FROM orders LIMIT 0, 5;
   # 查询第二页
   SELECT * FROM orders LIMIT 5, 5;
   

至此，MySql的基础学习已经告一段落，继续加油吧，在计算机这篇星辰大海中继续遨游。！