十、视图

1、简单视图：

2、复杂视图：

3、视图更新：

十一、函数

1、函数创建：

十二、数据库优化

1、索引优化：

2、查询优化：

3、设计优化：

十、视图

在 MySQL 中，视图（View）是一种虚拟的表，它是基于一个或多个表的查询结果构建而成的。视图提供了一种方便和灵活的方式来处理复杂查询、控制数据访问和重用查询逻辑。

通过使用视图，可以提高查询的效率和可维护性，并增强数据库的安全性。

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

理解视图：

视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用
数据库中只存放了视图的定义，而并没有存放视图中的数据（数据存放在原来的表中）
使用视图查询数据时，数据库会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变

视图优点：

操作简单
减少数据冗余
数据安全

适应多变的需求
能够分解复杂的查询逻辑

视图分类： 简单视图和复杂视图

语法：

CREATE [OR REPLACE] VIEW view_name [(字段列表)]

AS

select语句 [WITH [CASCADED |LOCAL] CHECK OPTION]

select语句：表示一个完整的查询语句，将查询记录映射到视图中

[with [cascaded | local] check option] ：可选项，表示更新视图时要保证在该视图的权限范围之内，cascaded表示级联，local表示只考虑当前视图。

1、简单视图：

简单视图是基于单个表的查询结果构建的视图。它们通常包含简单的SELECT语句，可以直接从单个表中选择列或进行简单的列计算。简单视图的查询逻辑相对较简单，不涉及复杂的多表连接操作或子查询。

案例1：基于bemp构建视图，数据列只有id、name。

drop table emp;drop table dept;create table dept(id int primary key,name varchar(20));insert into dept values(1,'java'),(2,'bigdata'),(3,'web');create table emp(id int primary key,name varchar(20),salary double,dept_id int,foreign key(dept_id) references dept(id));insert into emp values(1,'lisi',3000,1),(2,'wangwu',3200,1);insert into emp(id,name,salary) values(5,'zhuqi',3500);insert into emp values(3,'zhansan',2800,1),(4,'zhaoliu',900,2);-- 新建或更新视图create or replace view v_emp(id,username)asselect id,namefrom emp;desc emp;select * from emp;-- 查看视图结构desc v_emp;select * from v_emp;

案例2：基于bemp员工表构建视图，要求有编号、名字、薪水和年薪

create view v_emp1asselect id,name,salary,salary*30from emp;

案例3：基于v_emp1构建视图，只需要名字和薪水

create view v_emp_newasselect name,salary*30 as yearSalfrom v_emp1;select * from v_emp_new;

2、复杂视图：

复杂视图是基于多个表的连接操作、子查询或其他复杂查询逻辑构建的视图。它们可以涉及多个表之间的关联、聚合函数、子查询、条件逻辑等复杂的查询操作。

案例1：构建视图v_emp_dept，能查询员工信息及所属部门

create view v_emp_deptasselect concat(e.name,'(',d.name,')') as username,e.salary,e.dept_id,d.name dept_namefrom emp e, dept dwhere e.dept_id=d.id;select * from v_emp_dept;

查看当前数据库下的视图语法

show tables;

重命名视图

rename table old_view_name to new_view_name;

修改视图

alter view view_name

as

select查询语句

注意事项：可以使用创建视图替换

删除视图

drop view if exists view_name;

3、视图更新：

对于简单视图，即只涉及单个表的视图，可以通过直接对底层表进行更新来间接地更新视图。例如，如果有一个视图 my_view ，它是从表 my_table 中选择的某些列，可以通过更新 my_table 来更新视图中的数据。

注意事项：对基表（数据表）进行更新改会影响视图！

案例：基于bemp构建视图，显示编号和名字，并对数据修改

create view v_bempasselect id,namefrom emp;insert into v_bemp values(6,'briup');update v_bemp set name='zhaosi' where id=4;select * from v_bemp;select * from emp;

如果是复杂视图，包含下述结构中的任何一种，那么就不能通过它去更新原来

表中数据：

聚合函数（SUM(), MIN(), MAX(), COUNT()等）
select查询列表有数学表达式
DISTINCT
UNION或UNION ALL
位于选择列表中的子查询
GROUP BY
HAVING
JOIN

常量视图

案例展示：

执行更新操作：

十一、函数

函数是事先经过编译并存储在数据库中的一段sql语句集合，调用函数可以简化应用开发工作，提高数据处理的效率。

1、函数创建：

mysql8 增加了一个安全选项，需要执行一下代码才能创建函数

set global log_bin_trust_function_creators=TRUE;

基本格式：

delimiter 自定义符号

create function 函数名(形参列表) returns 返回类型 -- 注意是retruns

begin

        函数体 -- 若干sql语句，但是不要直接写查询

        return val;

        end 自定义符号

delimiter ;

-- 格式说明：

delimiter 自定义符号是为了在函数内写语句方便，制定除了;之外的符号作为函数书写结束标志，一般用$$或者//

形参列表：形参名类型类型为mysql支持类型

返回类型: 函数返回的数据类型，mysql支持类型即可

函数体：若干sql语句组成

return: 返回指定类型返回值

案例1：创建无参数的函数

-- 开启函数创建set global log_bin_trust_function_creators=TRUE;-- 创建无参数的函数delimiter $$create function func01() returns intbeginreturn (select salary from s_emp order by salary desc limit 1);end $$delimiter ;-- delimiter后面必须跟空白字符, 再跟;-- 函数调用select func01();select * from s_emp where salary=func01();

案例2：创建有参数的函数

-- 创建包含参数的函数delimiter $$create function func02(eid int)returns varchar(25)beginreturn (select last_name from s_emp where id=eid);end $$delimiter ;-- 函数调用select func02(1);

设置变量

1）定义用户变量

固定格式：

-- 定义格式

set @[变量名] = 值;

-- 使用格式

@[变量名]

具体案例：

set @eid=10;

select func02(@eid);

2）定义局部变量

可以在函数内部定义局部变量。局部变量只在函数内部可见，其作用域仅限于函数内部。

局部变量可以使用set赋值。

DECLARE variable_name datatype [DEFAULT initial_value];

-- variable_name 是变量的名称

-- datatype 是变量的数据类型

-- initial_value 是可选的初始值

具体案例：定义函数获取商品总价，传递数量和单价。

DELIMITER //CREATE FUNCTION get_total_price(num INT, price DECIMAL(10, 2))RETURNS DECIMAL(10, 2)BEGINDECLARE total DECIMAL(10, 2);SET total = num * price;RETURN total;END //DELIMITER ;select get_total_price(5,2.5);-- 删除函数drop function get_total_price;

十二、数据库优化

数据库优化是针对关系型数据库系统的性能和效率进行改进的过程。通过优化数据库，可以提高查询速度、减少资源占用，提升系统的响应性和可扩展性。

以下是一些常见的数据库优化技术和策略：

1、索引优化：

（1）在合适的字段上创建索引

如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

所以要建在合适的地方，合适的对象上。经常操作、比较、判断的字段应该建索引。索引根据实际需要来设置，不是越多越好，索引本身也是占用内存空间的。

（2）复合索引代替单索引

比如有一条语句是这样的： select * from users where area=’beijing’ and age=22; 如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。

因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

（3）使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

2、查询优化：

（1）尽量不使用NULL

通常，索引字段是不存在 NULL 的，所以指定 IS NULL 和 IS NOT NULL 的话会使得索引无法使用，进而导致查询性能低下。

（2）减少子查询

执行子查询时，会创建临时表，查询完毕后再删除它，所以子查询的速度会收到影响。

（3）减少模糊查询

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like "%aaa%" 不会使用索引而 like "aaa%" 可以使用索引。

（4）使用 EXISTS 代替 IN

在大多时候，[NOT] IN 和 [NOT] EXISTS 返回的结果是相同的。但是两者用于子查询时，EXISTS 的速度会更快一些。

-- exists使用案例-- 参考博文https://blog.csdn.net/zhangzehai2234/article/details/124652056-- exists：外表先进行循环查询，将查询结果放入exists的子查询中进行条件验证，确定外层查询数据是否保留-- 查询存在员工的部门的信息select id,namefrom s_dept as sdwhere exists (select id,last_name,dept_idfrom s_empwhere dept_id = sd.id);-- 查询已分配好部门员工的信息select id,last_name,dept_idfrom s_emp as sewhere exists(select id,namefrom s_deptwhere id = se.dept_id);

（5）避免排序

我们在查询的时候，虽然我们没有想要进行排序，但是在数据库内部频繁地进行着暗中的排序。因此对于我们来说，了解都有哪些运算会进行排序很有必要，会进行排序的代表性的运算有下面这些