mysql 普通sql流程控制,MySQL-各种功能

目录

视图

触发器

事务

储存过程

函数

流程控制

1、视图

视图是一个虚拟表(非真实存在)，其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。

1.1 创建视图

语法：

CREATE VIEW 视图名 AS SQL语句

实例：

create view t1_view as select * from tt1 inner join tt2 on tt1.tt2_id = tt2.tid;

1.2 修改视图

语法：

ALTER VIEW 视图名 AS SQL语句

实例：

alter view t1_view as select * from tt1 inner join tt2 on tt1.id = tt2.tid;

1.3 删除视图

语法：

DROP VIEW 视图名

实例：

drop view t1_view;

1.4 查看视图

语法：

SELECT * FROM 视图名;

实例：

select * from t1_view;

2、 触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为,没有查询

2.1 创建触发器

修改结束符语法：

delimiter 符号

实例：

delimiter //

创建触发器语法：

CREATE TRIGGER 触发器名 before/affer(前/后) insert/delete/update(增/删/改) ON 表名

FOR EACH ROW

BEGIN

sql语句

END

实例：

delimiter //# 修改结束符

create trigger tri_tt1 brfore update on tt1 for each row# 创建触发器

begin

update tt2 set tt2.hobby = '打豆豆' where tt2.hobby= '打游戏';# 自动触发的sql语句

end//

delimiter ;# 改回结束符

update tt1 set gender = 'male' where gender= 'female';# 更新语句自动触发触发器

2.2 删除触发器

语法：

DROP TRIGGER 触发器名

实例：

drop trigger tri_tt1;

3、事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整

性。

"""

事务是4个特征(ACID)：

原子性(Atomicity，或称不可分割性)

一致性(Consistency)

隔离性(Isolation，又称独立性)

持久性(Durability)。

原子性：一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。

事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

事务隔离分为不同级别，包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

"""

start transaction;# 开启事务

update user set balance=900 where name='wsb'; # sql语句1

update user set balance=1010 where name='egon'; # sql语句2

uppdate user set balance=1090 where name='ysb'; # sql语句3，任意语句失败都会回滚到原来状态

rollback;# 回滚，提交前都可以回滚

commit;# 提交，提交后无法回滚

4、储存过程

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

4.1 无参存储过程

"""定义存储过程"""

delimiter //# 修改结束符

create procedure p1()# create procedure 存储过程名()

BEGIN# begin 固定语句

...sql语句;# sql语句

select * from t1;# sql语句

select * from tt1;# sql语句

END //# end // 固定语句

delimiter ;# 还原结束符

"""调用：call 存储过程名加括号"""

call p1() # 调用

4.2 有参存储过程

"""

三类参数：

in 仅用于传入参数用

out 仅用于返回值用

inout 既可以传入又可以当作返回值

"""

"""定义存储过程"""

delimiter //

create procedure p2(

in n1 int,

out res int,

inout resn int

)

BEGIN

select * from tt1 where id > n1;

select * from tt1 where id > resn;

set res = 1;

set resn = 0;

END //

delimiter ;

"""调用传参"""

set @res = 0;

set @resn = 1;

call p2(2,@res,@resn);

select @res;

select @resn;

4.3 删除存储过程

语法：

DROP PROCEDURE 存储过程名

实例：

drop procedure p1;

5、函数

内置函数包含：数字函数、聚合函数、字符串函数、日期时间函数、加密函数、控制流函数等等许多函数

一、数学函数

"""

1. ROUND(x,y)

返回参数x的四舍五入的有y位小数的值

2. RAND()

返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

"""

二、聚合函数(常用于GROUP BY从句的SELECT查询中)

"""

1. AVG(col)返回指定列的平均值

2. COUNT(col)返回指定列中非NULL值的个数

3. MIN(col)返回指定列的最小值

4. MAX(col)返回指定列的最大值

5. SUM(col)返回指定列的所有值之和

6. GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

"""

三、字符串函数

"""

1. CHAR_LENGTH(str)

返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。

2. CONCAT(str1,str2,...)

字符串拼接，如有任何一个参数为NULL ，则返回值为 NULL。

3. CONCAT_WS(separator,str1,str2,...)

字符串拼接(自定义连接符)CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。

4. CONV(N,from_base,to_base) 进制转换

5. FORMAT(X,D)

将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。

6. INSERT(str,pos,len,newstr)

在str的指定位置插入字符串，如果pos超过原字符串长度，则返回原字符串，如果len超过原字符串长度，则由新字符串完全替换

pos：要替换位置其实位置

len：替换的长度

newstr：新字符串

7. INSTR(str,substr)

返回字符串 str 中子字符串的第一个出现位置。

8. LEFT(str,len)

返回字符串str 从开始的len位置的子序列字符。

9. LOWER(str)变小写

10. UPPER(str)变大写

11. REVERSE(str)

返回字符串 str ，顺序和字符顺序相反。

12. SUBSTRING(str,pos) ,

13. SUBSTRING(str FROM pos)

14. SUBSTRING(str,pos,len) ,

15. SUBSTRING(str FROM pos FOR len)

不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。

使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

"""

四、日期和时间函数

"""

1. CURDATE()或CURRENT_DATE() 返回当前的日期

2. CURTIME()或CURRENT_TIME() 返回当前的时间

3. DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)

4. DAYOFMONTH(date) 返回date是一个月的第几天(1~31)

5. DAYOFYEAR(date) 返回date是一年的第几天(1~366)

6. DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);

7. FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts

8. HOUR(time) 返回time的小时值(0~23)

9. MINUTE(time) 返回time的分钟值(0~59)

10. MONTH(date) 返回date的月份值(1~12)

11. MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);

12. NOW() 返回当前的日期和时间

13. QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);

14. WEEK(date) 返回日期date为一年中第几周(0~53)

15. YEAR(date) 返回日期date的年份(1000~9999)

16. DATE_FORMAT(date,format) 根据format字符串格式化date值

"""

五、加密函数

"""

1. MD5()

计算字符串str的MD5校验和

2. PASSWORD(str)

返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

"""

六、控制流函数

"""

1. CASE WHEN[test1] THEN [result1]...ELSE [default] END

如果testN是真，则返回resultN，否则返回default

2. CASE [test] WHEN[val1] THEN [result]...ELSE [default]END

如果test和valN相等，则返回resultN，否则返回default

3. IF(test,t,f)

如果test是真，返回t；否则返回f

4. IFNULL(arg1,arg2)

如果arg1不是空，返回arg1，否则返回arg2

5. NULLIF(arg1,arg2)

如果arg1=arg2返回NULL；否则返回arg1

"""

6、流程控制

6.1 if条件语句

delimiter //

CREATE PROCEDURE proc_if ()

BEGIN

declare i int default 0;

if i = 1 THEN

SELECT 1;

ELSEIF i = 2 THEN

SELECT 2;

ELSE

SELECT 7;

END IF;

END //

delimiter ;

6.2 while循环

"""1.whille"""

delimiter //

CREATE PROCEDURE proc_while ()

BEGIN

DECLARE num INT ;

SET num = 0 ;

WHILE num < 10 DO

SELECT

num ;

SET num = num + 1 ;

END WHILE ;

END //

delimiter ;

"""2.repeat"""

delimiter //

CREATE PROCEDURE proc_repeat ()

BEGIN

DECLARE i INT ;

SET i = 0 ;

repeat

select i;

set i = i + 1;

until i >= 5

end repeat;

END //

delimiter ;

6.3 loop

BEGIN

declare i int default 0;

loop_label: loop

set i=i+1;

if i<8 then

iterate loop_label;

end if;

if i>=10 then

leave loop_label;

end if;

select i;

end loop loop_label;

END

标签：返回,语句,功能,各种,字符串,delimiter,str,MySQL,date

来源： https://blog.csdn.net/Wednesdayi/article/details/113858087