mysql创建视图和事务的语句_1.4MySQL基础入门【事务，视图，存储过程，函数，流程控制结构】...

事务，视图，存储过程，函数，流程控制结构

1.事务是什么？

事务是由一个或者多个sql语句组成的一个执行单元，里面的sql语句，要么全部执行，要么全部都不执行。

2.事务的四大特性

1)原子型：一个事务不可再分割，由一个或者多个sql语句组成的一个执行单元，里面的sql语句，要么全部执行，要么全部都不执行

2)一致性： 事务使得系统从一个一致的状态转换到另一个一致状态

3)隔离性：多个事务可以同时执行，互不影响

4)持久性：事务一旦提交，会永久改变数据库数据，不可以回滚

3.事务的创建

1)隐式事务：每一条insert,update,delete语句

2)显示事务：

-- 开启事务

set autocommit = 0

start TRANSACTION

-- 事务中的多条sql语句

UPDATE account set balance=500 where username='tom'

UPDATE account set balance=1500 where username='jony'

-- 结束事务

COMMIT

3.什么是视图？

视图相当于一张虚拟表，是一张临时的表，需要多次查询的结果集，可以写成一张视图

应用场景：多个地方用到同样的查询结果，该查询结果使用的sql语句比较复杂

4.视图的创建及使用

-- 查询姓张的学生名和专业名

SELECT stuname,majorname

from stuinfo s

join major m on s.marjorid = m.id

where s.stuname like '张%';

create view v1

as

select stuname,majorname

from stuinfo s

join major m on s.majorid = m.id

select * from v1 where stuname like '张%'

-- 修改视图

create or replace view v1 as

alter view v1 as

-- 删除视图

drop view v1

5.存储过程

create procedure 存储过程名称(in 输入参数名称 类型,out 输出参数名称 类型,inout 输入输出参数名称 类型)

begin

存储过程体

end

call 存储过程名称(参数列表)

1)空参存储过程

-- 空参列表

-- 创建无参存储过程

create PROCEDURE myp1()

BEGIN

INSERT INTO user VALUES('tom','1234'),('jack','3366'),('lucky','uugb'),('root','12345');

END;

-- 调用存储过程

call myp1();

2)带输入参数存储过程

-- 判断用户是否登录成功

CREATE PROCEDURE myp3(IN username VARCHAR(255),IN passwd VARCHAR(255))

BEGIN

DECLARE result int;

SELECT COUNT(*) into result

FROM user

WHERE user.username = username

AND user.`password` = passwd;

SELECT IF(result>0,'成功','失败') as '登录结果';

END;

call myp3('root','778899');

3)带输出参数存储过程

-- 根据用户输入的用户名，返回密码

CREATE PROCEDURE myp4(in username VARCHAR(20),out pwd VARCHAR(20))

BEGIN

SELECT `password` into pwd

from user

WHERE user.username = username;

END;

call myp4('root',@pwd);

SELECT @pwd as 密码;

-- 存储过程的参数 即是输入参数，也是输出结果

-- 将输入的两个数 扩大两倍后输出

create PROCEDURE myp5(INOUT x int,INOUT y int)

BEGIN

SET x=x*2;

SET y=y*2;

END

SET @num1=20;

SET @num2=50;

call myp5(@num1,@num2);

SELECT @num1,@num2;

6. 函数

create function 函数名称(参数列表) returns 参数类型

begin

函数体

return 返回值

end

1)空参函数

-- 查询有账号的数量

create FUNCTION myfun() RETURNS int

BEGIN

DECLARE num int DEFAULT 0;

SELECT count(*) into num

from user;

RETURN num;

END;

SELECT myfun();

2)有参数列表的函数

-- 根据用户输入的学生名字 返回学生的语文成绩

CREATE FUNCTION myfun2(stuname VARCHAR(20)) RETURNS DOUBLE

BEGIN

DECLARE grade DOUBLE(4,1);

SELECT sum(chinese+english+math)/3 into grade

from student

WHERE student.`name` = stuname;

RETURN grade;

END;

SELECT myfun2('王五') as 平均成绩;

7.存储过程和函数的区别

1)存储过程的参数列表可以是输入参数，输出参数，或者输入输出参数，而函数的参数列表只能是输入参数

2)存储过程可以有0个或者1个或者多个返回值，而函数有且仅有一个返回值

3)存储过程一般用来批量更新，插入数据，而函数一般用来操作数据并返回计算后的结果值

8.流程控制结构

1) if函数：if(条件表达式，语句1。语句2) 类似于三目运算符

SELECT if(98 > 97,'大于','小于');

2)case结构：

case 变量|表达式|字段

when 要判断的值 then 返回值1或者语句1

when 要判断的值 then 返回值2或者语句2

else 返回值n或者语句n

end case

case

when 要判断的条件1 then 返回值1或者语句1

when 要判断的条件2 then 返回值2或者语句2

else 返回值n或者语句n

end case

-- 创建一个存储过程根据用户输入的成绩返回等级

create PROCEDURE levelTest(IN grade int,OUT lev VARCHAR(20))

BEGIN

CASE

WHEN grade >= 60 && grade <=90 THEN

set lev= '良好';

WHEN grade > 0 && grade < 60 THEN

set lev = '不及格';

WHEN grade > 90 && grade <=100 THEN

set lev = '优秀';

END CASE;

END

CALL levelTest(98,@result);

SELECT @result;

3)if结构

create PROCEDURE levelTest2(IN grade int,OUT lev VARCHAR(20))

BEGIN

IF grade >= 60 && grade <=90 THEN set lev = '良好';

ELSE IF grade > 0 && grade < 60 THEN set lev = '不及格';

ElSE set lev = '优秀';

END IF;

END

CALL levelTest(98,@result);

SELECT @result;

4)while循环

-- 批量插入n条数据

create PROCEDURE insertN(in num INT)

BEGIN

DECLARE x int DEFAULT 0;

WHILE x <= num DO

INSERT into user(username,`password`) VALUES(CONCAT('root',x),'ggg');

set x = x + 1;

END WHILE;

END

CALL insertN(2);