day03 子查询&分页&存储过程

标量子查询(scalar subquery)：返回1行1列一个值
- 查询出基本工资比ALLEN低的全部员工信息
```
SELECT * FROM emp WHERE sal<(SELECT sal FROM emp WHERE eanme='ALLEN');
```
行子查询(row subquery)：返回的结果集是 1 行 N 列
- 查询与SCOTT从事同一工作且工资相同的员工信息
```
SELECT e.* FROM emp e WHERE (e.job,e.sal)=(SELECT job,sal FROM emp WHERE ename='SCOTT')
AND e.ename<>'SCOTT';
```
列子查询(column subquery)：返回的结果集是 N 行 1列
- 在使用多行子查询需要使用多行比较操作符：
表子查询(table subquery)：返回的结果集是 N 行 N 列

模糊查询

在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容。

语法格式：

[NOT] LIKE '字符串'

NOT ：可选参数，字段中的内容与指定的字符串不匹配时满足条件。
字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。

LIKE 关键字支持百分号%和下划线“_”通配符。

%代表任何长度
_代表单个字符
\转义字符

注意事项和技巧

注意大小写。MySQL 默认是不区分大小写的。如果区分大小写，像“Tom”这样的数据就不能被“t%”所匹配到。
注意尾部空格，尾部空格会干扰通配符的匹配。例如，“T% ”就不能匹配到“Tom”。(可以使用trim去除前后空格)
注意 NULL。“%”通配符可以到匹配任意字符，但是不能匹配 NULL。也就是说 “%”匹配不到表中值为 NULL 的记录。

分页查询

作用：

限制查询结果返回的条数，减小对数据库服务器造成的压力

LIMIT关键字使用

指定初始位置

LIMIT 关键字可以指定查询结果从哪条记录开始显示，显示多少条记录。

LIMIT 初始位置，记录数

不指定初始位置

LIMIT 关键字不指定初始位置时，记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。

LIMIT 记录数

分页

#对12条数据进行分页=>每页数量3条
# limit (当前页-1)*每页数量,每页数量
select * from g_customer limit 0,3;#1-3
select * from g_customer limit 3,3;#1-3
select * from g_customer limit 6,3;#1-3
select * from g_customer limit 9,3;#1-3

视图

介绍：

视图是在数据库中定义的虚拟表。它是一个基于一个或多个实际表的查询结果集，可以像实际表一样被查询和操作。
视图可以看作是一个动态生成的数据表，其内容是从其他表中选择、过滤和计算得到的。
视图通过使用SQL查询语句来定义，这些查询语句可以包括与一个或多个表的连接、条件过滤、列计算、聚合函数等操作。

优点

数据的抽象和简化：将复杂的查询逻辑和多表连接操作封装为一个简化的视图查询，提供了更简洁、更易于理解的数据模型。
数据安全性：通过给用户授予对视图的访问权限，可以隐藏底层表的结构和敏感数据，只允许用户在特定条件下查看和操作数据。
提高查询性能：视图可以预先计算和存储查询结果，而不需要重新执行复杂的查询操作。
简化应用开发：通过将复杂的查询逻辑封装为视图，只需要简单地查询视图，而无需关心视图背后的复杂查询逻辑和表结构。

创建视图

create view empInfo as
select empno,ename,deptno from emp;
select * from  empInfo;

嵌套视图

-- 查询每个员工所属的部门名称
create view empDetail as
select e.EMPNO,e.ENAME,d.DNAME from emp e,dept d where e.DEPTNO=d.DEPTNO;
-- empDetail表:存储员工部门名称

create view empDetail2 as
select empno,dname from empDetail;
select * from empDetail2;

删除视图

drop view 视图名;

修改视图

-- ALTER VIEW 视图名 AS  sql语句;
alter view empDetail as
select e.EMPNO,d.DNAME from emp e,dept d where e.DEPTNO=d.DEPTNO;

更新视图

更新视图会影响原表的数据

要创建可更新视图,定义视图的select语句不能包含以下任何元素：

聚合函数，如：MIN，MAX，SUM，AVG，COUNT等。
DISTINCT子句
GROUP BY子句
HAVING子句
UNION或UNION ALL子句
左连接或外连接。

create view stuInfo3 as
select * from student

update  stuInfo3 set score=90.5 where id=8#修改视图数据

insert into stuInfo3 values(16,"h",'男',100,'',3)#往视图插入数据

存储过程

介绍：

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

存储过程可封装，并隐藏复杂的逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

创建存储过程

create procedure 存储过程名()
begin
-- sql
end;

删除存储过程

drop procedure 存储过程名;

无参存储过程

create PROCEDURE slectByEmpNo()
begin
#sql语句:当前存储过程的功能
select * from emp where empno=7369;
end;
#调用存储过程: call 存储过程名称();
call slectByEmpNo();

带参数的存储过程

create procedure selectByDeptNo(IN deptname varchar(20))
begin
select * from emp where DEPTNO in(select DEPTNO from dept where DNAME=deptname);
end;
call selectByDeptNo('SALES');

带输入输出参数

create procedure avgSal(in deptname varchar(20),out sal_ double)
begin 
select  avg(sal) sal into sal_ from emp where deptno in(select DEPTNO from dept where DNAME=deptname)  group by DEPTNO  ;
end;

call avgSal('abc',@sal_);
select @sal_;
select * from emp where DEPTNO in(select DEPTNO from dept where DNAME='abc') and sal>(select @sal_);

局部和全局变量

使用declare定义的是局部变量,每次调用存储过程都会重置值

declare a int ;#定义一个局部变量a 类型为int

使用set定义的是全局变量,每次调用存储过程都会改变变量值

set @b=1;

分支语句

IF 条件1 THEN
    -- sql;
ELSEIF 条件2 THEN
    -- sql;
ELSE
   -- sql;
END IF;

-- 根据员工编号查找工资,如果员工工资低于3000,则加5000,如果低于4000,则加2000,否则加1000,返回最终结果
create PROCEDURE addSal(
in empno_ int,
out mySal double
)
begin
DECLARE sal_ double;
select sal into sal_ from emp where empno=empno_;
if sal_<3000  then
  select sal+5000 into mySal from emp where empno=empno_;
elseif  sal_<4000 then
      select sal+2000 into mySal from emp where empno=empno_;
else 
      select sal+1000 into mySal from emp where empno=empno_;       
 end if;   
end;

call addSal(7369,@mySal);
select @mySal;

while循环

while i<=n do
if i%2=0 then
set gender='男';
else 
set gender='女';
end if;
insert into student(stu_id,name,age,gender) values(i,concat("tom",i),20,gender);
set i=i+1;
end while;

Q&A

题1 mysql子查询常用用法

标量子查询(scalar subquery)：返回1行1列一个值
- 查询出基本工资比ALLEN低的全部员工信息
```
SELECT * FROM emp WHERE sal<(SELECT sal FROM emp WHERE eanme='ALLEN');
```
行子查询(row subquery)：返回的结果集是 1 行 N 列
- 查询与SCOTT从事同一工作且工资相同的员工信息
```
SELECT e.* FROM emp e WHERE (e.job,e.sal)=(SELECT job,sal FROM emp WHERE ename='SCOTT')
AND e.ename<>'SCOTT';
```
列子查询(column subquery)：返回的结果集是 N 行 1列
- 在使用多行子查询需要使用多行比较操作符：
表子查询(table subquery)：返回的结果集是 N 行 N 列

题2 模糊查询常用通配符及含义

%代表任何长度
_代表单个字符
\转义字符

题3 什么是视图及创建格式

视图是在数据库中定义的虚拟表。它是一个基于一个或多个实际表的查询结果集，可以像实际表一样被查询和操作。

创建视图

create view empInfo as
select empno,ename,deptno from emp;
select * from  empInfo;

题4 更新视图需要注意哪些地方

更新视图会影响原表的数据
要创建可更新视图,定义视图的select语句不能包含以下任何元素：
- 聚合函数，如：MIN，MAX，SUM，AVG，COUNT等。
- DISTINCT子句
- GROUP BY子句
- HAVING子句
- UNION或UNION ALL子句
- 左连接或外连接。

题5 存储过程定义格式及调用

创建存储过程

create procedure 存储过程名()
begin
-- sql
end;

调用存储过程: call 存储过程名称();

原文地址:https://blog.csdn.net/Liang_z_/article/details/139710429 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：https://www.suanlizi.com/kf/1803092819586453504.html 如若内容造成侵权/违法违规/事实不符，请联系《酸梨子》网邮箱：1419361763@qq.com进行投诉反馈，一经查实，立即删除！

阅读全部