目录
视图
含义:
应用场景:
好处:
操作:
创建:
修改:
删除:
查看:
更新:
视图和表的对比
变量
系统变量
全局变量
会话变量
自定义变量
用户变量
局部变量
对比用户变量与局部变量
存储过程和函数
存储过程
创建:
调用:
删除:
查看:
参数情况:
函数:
创建:
调用:
删除:
参数情况:
流程控制结构
顺序结构:
分支结构:
if函数:
case:
if结构:
循环结构:
while:
loop:
repeat:
视图
含义:
虚拟表,和普通表一样使用,maysql5.1版本出现的新特性,是通过表多态生成的数据。MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果。
应用场景:
·多个地方用到同样的结果。
·该查询结果使用的sql语句较复杂。
好处:
1、重用sql语句。
2、简化复杂的sql操作,不必知道它的查询细节。
3、保护数据,提高安全性。
操作:
创建:
·语法
create view 视图名
as
查询语句;
·如
查询姓名中包含a字符的员工名、部门名和工种信息
#创建
create view myv1
as
select last_name,department_name,job_title
from employees e
join departments d on e.department_id = d.department_id
join jobs j on j.job_id=e.job_id
#使用
select * from myv1 where last_name like '%a%';
修改:
·方式一
语法:
create or replace view 视图名
as
查询语句;
·方式二
语法:
alter view 视图名
as
查询语句;
删除:
·语法
drop view 视图名,视图名,...;
查看:
DESC myv3;
或
show create view myv3;
更新:
·插入
insert into myv1 values('张飞','af@qq.com');
·修改
update myv1 set last_name = '张无忌' where last_name='张飞';
·删除
delete from myv1 where last_name='张无忌';
注意:视图更新后,原表也会跟着被更新
·具备一下特点的视图不允许更新
包含一下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
常量视图
select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
视图和表的对比
·视图
创建语法的关键字:create view
是否实际占用物理空间:只是保存了sql逻辑
使用:增删改查,只是一般不能增删改
·表
创建语法的关键字:create table
是否实际占用物理空间:保存了数据
使用:增删改查
变量
系统变量
·说明:变量由系统提供,不是用户定义,属于服务器层面
·语法:
1、查看所有的系统变量
show global | 【session】 variables;
2、查看满足条件的部分系统变量
如:show global | 【session】variables like '%char%'
3、查看指定的某个系统变量的值
select @@global | 【session】系统变量名;
4、为某个系统变量赋值
方式一:
set global | 【session】系统变量名=值;
方式二:
set @@global | 【session】.系统变量名=值;
·注意:
如果是全局级别,则需要加global,如果是会话级别,则需要加session,如果不写,则默认session
全局变量
·作用域:服务器每次启动将为所有的全局变量赋初始值,针对于所有的会话(连接)有效,但不能跨重启
·语法
1、查看所有的全局变量
show global variables;
2、查看部分的全局变量
show global variables like '%char%';
3、查看指定的全局变量的值
select @@global.autocommit;
4、为某个指定的全局变量赋值
set @@global.autocommit=0;
会话变量
·作用域:仅仅针对于当前会话(连接)有效
·语法:
1、查看所有的会话变量
show variables;
或
show session variables;
2、查看部分的会话变量
show variables like '%char%';
或
show session variables like
3、查看指定的某个会话变量
select @@tx_isolation;
或
select @@session.tx_isolation;
4、为某个会话变量赋值
set @@tx_isolation='read-uncommitted';
或
set session tx_isolation='read-committed';
自定义变量
·说明:变量是用户自定义的,不是由系统提供
·使用步骤:声明——赋值——使用(查看、比较、运算等)
用户变量
·作用域:针对于当前会话(连接)有效,同于会话变量的作用域,应用在任何地方,也就是begin end里面或begin end外面
·声明并初始化
赋值的操作符:=或:=
set @用户变量名=值; 或
set @用户变量名:=值; 或
select @用户变量名:=值;
·赋值(更新用户变量的值)
方式一:通过set或select
set @用户变量名=值; 或
set @用户变量名:=值; 或
select @用户变量名:=值;
方式二:通过select into
select 字段 into @变量名 from 表;
·使用(查看用户变量的值)
select @用户变量名;
局部变量
·作用域:仅仅在定义它的begin end中有效,应用在begin end中的第一句话
·语法:
1、声明
declare 变量名 类型;
declare 变量名 类型 default 值;
2、赋值
方式一:
通过set或select
set 局部变量名=值;或
set 局部变量名:=值;或
select @局部变量名:=值;
方式二:
通过select into
select 字段 into 局部变量名
from 表;
3、使用
select 局部变量名;
对比用户变量与局部变量
·用户变量
作用域:当前会话
定义和使用的位置:会话中的任何地方
语法:必须加@符号,不用限定类型
·局部变量
作用域:begin end中
定义和使用的位置:只能在begin end中,且为第一句话
语法:一般不用加@符号,需要限定类型
存储过程和函数
类似于java中的方法
存储过程
·含义:
一组预先编译好的SQL语句的集合,理解成批处理语句
·好处:
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
·注意:语句都是在命令提示符运行
创建:
·create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的SQL语句)
end
·注意:
1、参数列表包含三部分
参数模式、参数名、参数类型
如:in stuname varchar(20)
参数模式:
in:该参数可以作为输入,也就是该参数需要调用方传入值
out:该参数可以作为输出,也就是该参数可以作为返回值
inout:该参数既可以作为输入,又可以作为除数,也就是该参数既需要传入值,又可以返回值
2、如果存储过程体仅仅只有一句话,begin end可以省略,存储过程体中的每条SQL语句的结尾要求必须加分号,存储过程的结尾可以使用delimiter重新设置
语法:delimiter 结束标记
如:delimiter $
调用:
·语法
call 存储过程名(实参列表);
删除:
·语法
drop procedure 存储过程名;
查看:
·语法
show create procedure myp2;
参数情况:
空参:
·例子
插入到admin表中的五条记录
#创建
delimiter $
create procedure myp1()
begin
insert into admin(username,password)
values('john','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000'),
end $
#调用
call myp1()$
in模式:
·例子1
创建存储过程实现 根据女神名,查询对应的男神信息
create procedure myp2(in beautyname varchar(20))
begin
select bo.*
from boys bo
right join beauty b on bo.id=b.boyfriend_id
where b.name=beautynamel
end$
call myp2('柳岩')$
·例子2
创建存储过程实现,用户是否登录成功
create procedure myp4(in username varchar(20),in password varchar(20))
begin
delare result int default 0; #声明并初始化
select count(*) into result #赋值
from admin
where admin.username = username
and admin.password=password;
select if(result>0,'成功','失败'); #使用
end$
call myp3('张飞','8888')$
out模式:
·例子1
根据女神名,返回对应的男神名
create procedure myp5(in beautyName varchar(20),out boyName varchar(20))
begin
select bo.boyName into boyName
from boys bo
inner join beauty b on bo.id = b.boyfriend_id
where b.name=beautyName;
end$
call myp5('小昭',@bName)$
select @bName
·例子2
根据女神名,返回对应的男神名和男神魅力值
create procedure myp6(in beautyName varchar(20),out boyName varchar(20),out userCP int)
begin
select bo.boyName,bo.userCP into boyName,userCP
from boys bo
inner join beauty b on bo.id = b.boyfriend_id
where b.name=beautyName;
end$
call myp6('小昭',@bName,@usercp)$
select @bNmae,@usercp
inout模式:
·例子
传入a和b两个值,最终a和b都翻倍并返回
create procedure myp8(inout a int,inout b int)
begin
set a=a*2;
set b=b*2
end$
set @m=10$
set @n=20$
call myp8(@m,@n)$
select @m,@n$
函数:
·含义:
一组预先编译好的SQL语句的集合,理解成批处理语句
·好处:
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
·注意:语句都是在命令提示符运行
·与存储过程的区别
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果
创建:
·语法
create function 函数名(参数列表)returns 返回类型
begin
函数体
end
·注意
1、参数列表包含两部分:参数名 参数类型
2、函数体:肯定会有return语句,如果没有会报错,如果return语句没有放在函数体的最后也不报错,但不建议
3、函数体中仅有一句话,则可以省略begin end
4、使用delimiter语句设置结束标记
·select 函数名(参数列表);
删除:
·drop function myf3;
参数情况:
无参有返回:
·例子:
返回公司的员工个数
create function myf1() returns int
begin
declare c int default 0; #定义变量
select conut(*) into c #赋值
from employees;
return c;
end$
select myf1()$
有参有返回:
·例子1:
根据员工名,返回它的工资
create function myf2(empName varchar(20)) returns double
begin
set @sal=0;
select salary into @sal
from employees
where last_name=empName;
return @sal;
end$
select myf2('k_ing')$
·例子2:
根据部门名,返回该部门的平均工资
create function myf3(deptName varchar(20))returns double
begin
declare sal double;
select avg(salary) int sal
from employees e
join departments d on e.department_id = d.department_id
where d.department_name=deptName;
return sal
end$
select myf3('IT')$
流程控制结构
顺序结构:
含义:程序从上往下依次执行
分支结构:
含义:程序从上往下依次执行
if函数:
·功能:
实现简单的双分支
·语法:
if(表达式1,表达式2,表达式3)
·执行顺序
如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值
·应用
任何地方
case:
·情况1:
类似于java中的switch语句,一般用于实现等值判断
语法:
case 变量 | 表达式 | 字段
when 要判断的值 then 返回的值1或语句1;
when 要判断的值 then 返回的值2或语句2;
...
else 要返回的值n或语句n;
end case;
·情况2:
类似于java中的多重if语句,一般用于实现区间判断
语法:
case
when 要判断的条件1 then 返回的值1或语句1;
when 要判断的条件2 then 返回的值2或语句2;
...
else 要返回的值n或语句n;
end case
·特点
·1、可以作为表达式,嵌套在其他语句中使用,可以放在任何地方,begin end中或begin end的外面
可以作为独立的语句去使用,只能放在begin end中
·2、如果when中的值满足或条件成立,则执行对应的then后面的语句,并且结束case,如果都不满足,则执行else中的语句或值
·3、else可以省略,如果else省略了,并且所有when条件都不满足,则返回null
·例子
创建存储过程,根据传入的成绩,来显示等级,比如传入的成绩:90-100,显示A,80-90,显示B,60-80,显示C,否则,显示D
create procedure test_case(in score int)
begin
case
when score>=90 and score<=100 then select 'A';
when score>=80 then select 'B';
when score>=60 then select 'C';
else select 'D';
end case;
end$
call test_case(95)$
if结构:
·功能:实现多重分支
·语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if
·应用场景:在begin end中
·例子
根据传入的成绩,来显示等级,比如传入的成绩:90-100,返回A,80-90,返回B,60-80,返回C,否则,返回D
create function test_if(score int) returns char
begin
if score>=90 and score<=100 then return 'A'
elseif score>=80 then return 'B';
elseif score>=60 then return 'C';
else return 'D';
end if;
end $
循环结构:
·含义:程序在满足一定条件的基础上,重复执行一段代码
·循环控制
iterate类似于 continue,继续,结束本次循环,继续下一次
leave类似于break,跳出,结束当前所在的循环
while:
·语法
【标签:】while 循环条件 do
循环体
end while 【 标签】;
·例子1
批量插入,根据次数插入到admin表中多条记录
create procedure pro_while1(in insertCount int)
begin
declare i int default 1;
while i<=insertCount do
insert into admin(username,password) values(concat('Rose',i),'666');
set i=i+1;
end while
end $
call pro_while1(100)$
·例子2
批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
truncate table admin$
drop procedure test_while1$
create procedure test_while1(in insertCount int)
begin
declare i int default 1;
a:while i<=insertCount do
insert into admin(username,password) values(concat('xiaohua',i),'0000');
if i>=20 then leave a;
end if;
set i=i+1;
end while a;
end$
call test_while1(100)$
·例子3
批量插入,根据次数插入到admin表中多条记录,只插入偶数次
truncate table admin$
drop procedure test_while1$
create procedure test_while1(in insertCount int)
begin
declare i int default 0;
a:while i<=insertCount do
set i=i+1;
if mod(i,2)!=0 then iterate a;
end if;
insert into admin(username,password) values(concat('xiaohua',i),'0000');
end while a;
end$
call test_while1(100)$
loop:
·语法
【标签:】loop
循环体
end while【 标签】;
可以用来模拟简单的死循环
repeat:
·语法
【标签:】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;