学习MySQL的第十一天-EW帮帮网

逆风执炬，向光而行

一、补充

我们首先对之前的文章进行一下补充

1.1 check约束

我们通过具体的案例来对这check约束进行一下讲解

CREATE TABLE gardens(
  id INT CHECK(id<10),
  name VARCHAR(255) UNIQUE,
  garden INT DEFAULT 100
);

DESC gardens;

INSERT INTO gardens
VALUES(1,'t',100)

此时，如果你要添加的数据中，id 大于等于10，将会显示插入失败；如果garden没有具体数据，则garden默认为是100

INSERT INTO gardens(id,name)
VALUES(2,'p')

INSERT INTO gardens(id,name)
VALUES(40,'t')

SELECT * FROM gardens

Check constraint 'gardens_chk_1' is violated

1.2 在 ALTER TABLE时添加约束

ALTER TABLE gardens
MODIFY `name` VARCHAR(255) DEFAULT 'Me'

DESC gardens

1.3 在 CREATE TABLE时删除约束

ALTER TABLE gardens
MODIFY `name` VARCHAR(255);
 
DESC gardens

二、视图

2.1 视图的理解

        1.视图是一种虚拟表,本身是不具有数据的,占用很少的内存空间,它是SQL中的一个重要概念，可以将视图理解为存储起来的SELECT 语句
        2.视图中的select语句涉及到的表，称为基表，针对视图做DML操作，会影响到对应的基表中的数据，反之亦然
        3.视图本身的删除，不会导致基表中数据的删除
        4.视图的应用场景：针对小型项目，不推荐使用视图
        5.视图的优点：简化查询、控制数据访问权限

2.2 创建视图

在 CREATE VIEW语句中嵌入子查询
方式为：CREATE VIEW 视图名称
AS 查询语句

准备工作：

    CREATE DATABASE test1;
	USE test1;
	CREATE TABLE emps
	AS
	SELECT *
	FROM emp.departments;
	
	CREATE TABLE depts
	AS
	SELECT *
	FROM emp.department_position;

	SELECT * FROM emps;
	SELECT * FROM depts;

2.3 创建视图

	CREATE VIEW empss 
	AS
	SELECT emps.id,emps.`name`,depts.position
	FROM emps,depts

    # 因为这里只是为了展示一下单表的情况，所以就没有加入多表的连接条件

2.4 针对于多表

	CREATE VIEW empsss
	AS
	SELECT emps.id,emps.`name`,depts.position
	FROM emps,depts
	WHERE emps.id = depts.id

2.5 添加视图字段的别名

	CREATE VIEW empsss(emp1_id,depts_position)
	AS
	SELECT emps.id,depts.position
	FROM emps,depts
	WHERE emps.id = depts.id;

    SELECT * FROM empsss;

2.6 视图的优化

	CREATE VIEW emp_dept
	AS
	SELECT CONCAT(emp1.`name`,'(',depts.position,')') '姓名(部门)',emp1.salary
	FROM emp1 JOIN depts ON emp1.id = depts.id;

    SELECT * FROM emp_dept;

2.7 基于视图创建视图

 CREATE VIEW f_emp_dept
 AS
 SELECT emp_dept.salary
 FROM emp_dept;

 SELECT * FROM emp_dept;

2.8 查看视图

 # 查看数据库的表对象、视图对象
 SHOW TABLES;
 # 查看视图结构
 DESC emp_dept;
 # 查看视图属性信息
 SHOW TABLE STATUS LIKE 'emp_dept'; 
 # 查看视图的详细定义信息
 SHOW CREATE VIEW emp_dept;

2.9 更新视图中的数据

更新表中的数据会导致视图中的数据改变;同理，更新视图中的数据也会导致表中的数据改变

 UPDATE emp_dept
 SET salary = 1000
 WHERE salary IS NULL;
 
 SELECT * FROM emp_dept;

当视图中的字段在及原表中不存在，则不更新视图中的数据

 USE test
 CREATE VIEW avg
 AS
 SELECT student.Sname,avg(sc.grade)
 FROM student,sc,course
 WHERE student.Sno = sc.Sno AND sc.`课程号` = course.`课程号`
 GROUP BY student.Sname

在这里进行错误展示

 UPDATE avg
 SET avg(sc.grade) = 100
 WHERE Sname = '周九'
 SELECT * FROM avg;

三、修改、删除视图

3.1 修改视图

 方式一:
 CREATE OR REPLACE VIEW emp_depts
 AS 
 SELECT emp_dept.`姓名(部门)`,emp_dept.salary
 FROM emp_dept;
 
 方式二:
 ALTER VIEW emp_depts
 AS
 SELECT emp_dept.salary
 FROM emp_dept

 DESC emp_dept
 SELECT * FROM emp_dept

3.2 删除视图

 SHOW TABLES
 DROP VIEW emp_dept
 # 或
 DROP VIEW IF EXISTS emp_dept,empss

3.3 视图的优点

        1.操作简单
        将经常使用的查询操作定义为视图,可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系,也不需要关心数据表之间的业务逻辑和查询条件,而只需要简单地操作视图即可,极大简化了开发人员对数据库的操作。
        2.减少数据冗余
        视图跟实际数据表不一样,它存储的是查询语句。所以,在使用的时候,我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据,不占用数据存储的资源,减少了数据冗余。
        3. 数据安全
        MySQL将用户对数据的访问限制在某些数据的结果集上,而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。
        4.适应灵活多变的需求
        当业务系统的需求发生变化后,如果需要改动数据表的结构,则工作量相对较大,可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。
        5.能够分解复杂的查询逻辑
        数据库中如果存在复杂的查询逻辑,则可以将问题进行分解,创建多个视图获取数据,再将创建的多个视图结合起来,完成复杂的查询逻辑。

3.4 视图的不足

        如果我们在实际数据表的基础上创建了视图,那么,如果实际数据表的结构变更了,我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图(就是在视图的基础上创建视图),维护会变得比较复杂,可读性不好,容易变成系统的潜在隐患。因为创建视图的SQL查询可能会对字段重命名,也可能包含复杂的逻辑,这些都会增加维护的成本。
        实际项目中,如果视图过多,会导致数据库维护成本的问题。
        所以,在创建视图的时候,要结合实际项目需求,综合考虑视图的优点和不足,这样才能正确使用视图,使系统整体达到最优。

四、结语

风起青萍，浪成微澜

须知少日拏云志，曾许人间第一流。我们还年轻，我们还有无限可能！

学习MySQL的第十一天