六、视图
1、视图
视图:MySQL是从5.1之后开始提供视图功能。视图是一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了SQL逻辑,不保存查询结果。
使用场景:
- 多个地方用到同样的查询结果
- 该查询结果使用的SQL语句较复杂
例子:
create view v1
as
select stuname,majorname
from stuinfo s
inner join major m on s.majorId=m.id;
视图创建
create view 视图名
as
查询语句
视图修改
1)
create or replace view 视图名
as
查询语句
2)
alter view 视图名
as
查询语句
视图删除
drop view 视图名,视图名。。。。。
视图查看
show create view 视图名
视图更新:和数据表一样(增删改查)
视图的可更新性和视图中查询的定义有关系,以下类型的
视图是不能更新的。
• 包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表
视图和表:
创建 | 是否占用物理空间 | 使用 | |
视图 | create view | 否,只是保存了SQL逻辑 | 增删改查,有一定限制 |
表 | create table | 是,保存了数据 | 增删改查 |
七、变量
1、变量
1)系统变量:变量是由系统提供的,不是用户定义的,属于服务器层面
如果是全局,则加上global,如果是会话则加上session,如果不写,默认是session。
- 全局变量
作用域:服务器每次启动将为所有全局变量赋初始值,针对所有会话(连接)有效,但是不能跨重启。
查看变量
查看指定的某个系统变量的值show global variables 【like '%char%'】;
为某个系统变量赋值select @@global.系统变量名
set global 系统变量名 = 值; set @@global.系统变量名 = 值;
- 会话变量
作用域:仅仅针对当前会话(连接)有效。
查看变量
查看指定的某个系统变量的值show session variables 【like '%char%'】;
为某个系统变量赋值select @@session.系统变量名
set session 系统变量名 = 值; set @@session.系统变量名 = 值;
2)自定义变量:用户自定义的,不是系统定义的
- 用户变量
作用域:针对当前会话(连接)有效。可以在begin end中,也可以在begin end外。
使用:①声明并初始化
②赋值(更新用户变量的值)set @用户变量名=值; 或 set @用户变量名 := 值; 或 select @用户变量名 := 值
方式一:
方式二:set @用户变量名=值; 或 set @用户变量名 := 值; 或 select @用户变量名 := 值
③查看select 字段 into 变量名 from 表;
例子:select @用户变量名;
set @num = 1; select count(*) into @num from t_user; select @num;
- 局部变量
作用域:仅仅定义在begin end中有效
使用①声明
②赋值declare 局部变量名 类型; declare 局部变量名 类型 default 值;
方式一:
方式二:set 局部变量名=值; 或 set 局部变量名 := 值; 或 select @局部变量名 := 值
③查看select 字段 into 局部变量名 from 表;
select 局部变量名;
- 比较
作用域 定义和使用的位置 语法 用户变量 当前会话 会话中的任何地方 必须加@符号,不用限定类型 局部变量 begin end中 只能在begin end中,且为第一句话 一般不用加@符号,需要限定类型
八、存储过程
1、存储过程
一组预先编译好的SQL语句的集合,可以理解为批处理语句。
2、语法
1)创建
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意:
①参数列表包含三部分
参数模式 参数名 参数类型
如:
IN name varchar(20)
参数模式
- IN:该参数可以作为输入,也就是使用的时候需要传入值
- OUT:该参数可以作为输出,也就是该参数可以作为返回值
- INOUT:该参数即可以作为输入,也可以作为输出。既需要传入值,又可以返回值。
②如果存储过程体只有一句话,begin end可以省略。存储过程体中的每条SQL语句的结尾要求必须加分号。
结尾可以使用 delimiter关键字重新设置。
2)调用
call 存储过程名(实参列表);
实例1
delimiter $
CREATE PROCEDURE myPro(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) INTO result #赋值
FROM admin
WHERE admin.username=username AND admin.password = password;
SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myPro('张飞','10086')$
实例2
CREATE PROCEDURE myPro1(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 myPro1('小赵',@bName)$
SELECT @bName$
实例3
CREATE PROCEDURE myPro2(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END$
#调用前定义两个用户变量
SET @m=10$
SET @n=20$
CALL myPro2(@m,@n)$
SELECT @m,@n$
3)删除存储过程
drop procedure 存储过程名;
4)查看存储过程
show create procedure 存储过程名;
九、函数
1、概念和存储过程差不多
区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入,批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果。
2、语法
1)创建
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
- 参数列表包含两部分:参数名 参数类型
- 函数体肯定会有return语句,如果没有会报错。如果return语句没有放在函数体的最后也不会报错,但是不建议。
- 当函数体中只有一句的时候,可以省略begin end、
- 使用delimiter语句作为设置结束标记。
2)调用
select 函数名(参数列表);
实例1
delimiter $
CREATE FUNCTION myFun() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c#赋值
FROM t_user;
RETURN c;
END $
#调用
SELECT myFun()$
实例2
CREATE FUNCTION myFun1(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal = 0;#定义用于变量
SELECT salary INTO @sal#赋值
FROM employees
WHERE last_name=empName;
RETURN @sal;
END $
SELECT myFun1('k_ing')$
3)查看
show create function +函数名;
4)删除
drop function +函数名;
十、流程控制
流程控制结构:
顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路劲中选择一条去执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码。
数据库连接池
数据库连接的建立及关闭是极其耗费系统资源的操作。
因此使用数据库连接池的解决方案:当系统启动的时候,系统主动建立足够的数据库连接,并将这些连接组成一个连接池。每次程序请求数据库连接的时候,无需从新打开连接,而是直接从连接池中取出已有的连接使用,使用完后不再关闭数据库连接,而是直接将连接还给连接池。
数据库连接池常用的参数如下:
1、数据库的初始连接数
2、连接池的最大连接数
3、连接池的最小连接数
4、连接池每次增加的容量
Database通常被称为数据源,包含连接池和连接池管理两个部分,但习惯也把Database称为连接池
一、DPCP数据源
二、C3P0数据源