目录
二、聚合函数(常用于GROUP BY从句的SELECT查询中)
1.视图
视图:是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据
视图有如下特点;
1. 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表(实表)产生的表(虚表)。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新(添加、删除和修改)直接影响基本表。
5. 当视图来自多个基本表时,不允许添加和删除数据。
6.当视图创建之后,更新(添加、删除和修改)原表,会导致视图无法使用
1.创建视图
create view 视图名称 as sql 查询语句
2.使用视图
select * from 视图名称;
3.更新视图
alter view 视图名称 AS SQL语句
4. 删除视图
drop view ren_view;
2.触发器-trigger
触发器:监视某种情况,并触发某种操作。
触发器创建语法四要素:1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
1.创建触发器语法
create trigger 触发器名字 after/before insert/update/delete
on 表名 for each row #这句话是固定的
begin
#需要执行的sql语句
end
注意1:after/before: 只能选一个 ,after 表示 后置触发, before 表示前置触发
注意2:insert/update/delete:只能选一个
2.删除触发器
drop TRIGGER 触发器名字
3.要插入值得触发器
对于insert而言,新插入的行用new来表示,行中的每一列的值用new.列名来表示。
栗子:
create trigger tg2 after insert on order_table
for each row
begin
update goods set num = num-new.much where id = new.gid;
end
3.存储过程
存储过程:类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合,该语句集包括SQL语句(对数据的增删改查)、条件语句和循环语句等。
1. 查看现有的存储过程
show procedure status;
2 .删除存储过程
drop procedure 存储过程名称;
3. 调用存储过程
call 存储过程名称(参数入/出类型 参数名 数据类型);
4.创建存储过程
- 体会封装
create procedure p1 ()
begin
要执行的sql语法;
end
- SQL 体会参数 in|out|inout
注意1: mysql中有三种出入参数类型:分别为:1. in 入参类型 2.out 出参类型 3. inout 出入参类型
注意2: into 关键字 可以 将前面字段的查询结果 执行 给 into 后面的变量.
create procedure 存储过程名称(in|out|inout 参数名称 参数类型,in|out|inout 参数名称 参数类型)
in|out栗子( inout栗子在下面 ):
create procedure p2(in i int,out n varchar(50))
begin
select name into n from account where id = i(要执行的sql语法);
end
-- 调用
set @name =null; 设置初始值,记得@
CALL p2(1,@name);
select @name;
- SQL 体会控制
格式:
create procedure p3(in x int,in c char(1))
begin
if c ='d' then
select * from account where money >x(sql语法);
else
select * from account where money <x(sql语法);
end if;
end
栗子二:
CREATE PROCEDURE p3(in flag char(5), in nums int)
BEGIN 开始循环
if flag = 'true' then 单等号
SELECT * from goods where num < nums;
ELSEIF flag ='false' THEN
SELECT * FROM goods where num > nums;
ELSE
SELECT * FROM goods;
END if; 结束循环
END
- 体会循环:计算1-100累加的和,并且返回计算结果.inout
create procedure p4(inout n int)
begin
DECLARE sum int default 0; -- 设置总和变量,并且指定初始值0
declare i int; -- 声明变量
set i = 0; -- 通过set为变量设置值
while i<=n DO -- 开始循环
set sum = sum +i;
set i = i+1;
end while; -- 结束循环
select sum; -- 提供结果
set n = sum; --将计算结果提供给 输出变量 n;
end;
-- 调用:
set @n = 100;
call p4(@n);
select @n;
存储过程优点:
1、存储过程增强了SQL语言灵活性。
存储过程可以使用控制语句编写,可以完成复杂的判断和较复杂的运算,有很强的灵活性;
2、减少网络流量,降低了网络负载。
存储过程在服务器端创建成功后,只需要调用该存储过程即可,而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
3、存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译。
一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点:
1、扩展功能不方便
2、不便于系统后期维护
4.函数
4.1 MySQL内建函数
官方函数文档:https://dev.mysql.com/doc/refman/5.7/en/functions.html
一、数学函数
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值 RAND() 返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
二、聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值 COUNT(col)返回指定列中非NULL值的个数 MIN(col)返回指定列的最小值 MAX(col)返回指定列的最大值 SUM(col)返回指定列的所有值之和 GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
三、字符串函数
CHAR_LENGTH(str) 返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。 CONCAT(str1,str2,...) 字符串拼接 如有任何一个参数为NULL ,则返回值为 NULL。 CONCAT_WS(separator,str1,str2,...) 字符串拼接(自定义连接符) CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点,或不含小数部分。 例如:SELECT FORMAT(12332.1,4); 结果为: '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos:要替换位置其实位置 len:替换的长度 newstr:新字符串 例如: SELECT INSERT('abcd',1,2,'tt'); 结果为: 'ttcd' SELECT INSERT('abcd',1,4,'tt'); 结果为: 'tt' 特别的: 如果pos超过原字符串长度,则返回原字符串 如果len超过原字符串长度,则由新字符串完全替换 INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 例如: SELECT INSTR('abc','c'); 结果为: 3 SELECT INSTR('abc','d'); 结果为: 0 LOWER(str)变小写 UPPER(str)变大写 REVERSE(str) 返回字符串 str ,顺序和字符顺序相反。 例如: SELECT REVERSE('1234567') 结果为:7654321 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -- 从第5位开始截取 -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -- 从第4位开始截取 -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); --从第5位开始截取,截取6个长度 -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -- 从倒数第3位开始截取 -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -- 从倒数第5位开始截取,截取3个长度 -> 'aki'
四、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期 CURTIME()或CURRENT_TIME() 返回当前的时间 DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7) DAYOFMONTH(date) 返回date是一个月的第几天(1~31) DAYOFYEAR(date) 返回date是一年的第几天(1~366) DAYNAME(date) 返回date的星期名,如:SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式,格式化UNIX时间戳ts HOUR(time) 返回time的小时值(0~23) MINUTE(time) 返回time的分钟值(0~59) MONTH(date) 返回date的月份值(1~12) MONTHNAME(date) 返回date的月份名,如:SELECT MONTHNAME(CURRENT_DATE); NOW() 返回当前的日期和时间 QUARTER(date) 返回date在一年中的季度(1~4),如SELECT QUARTER(CURRENT_DATE); WEEK(date) 返回日期date为一年中第几周(0~53) YEAR(date) 返回日期date的年份(1000~9999)
重点: DATE_FORMAT(date,format) 根据format字符串格式化date值 mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', -> '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52' mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00'
五、加密函数
MD5() 计算字符串str的MD5校验和 例如: SELECT MD5('1234') 结果为:81dc9bdb52d04dc20036dbd8313ed055 PASSWORD(str) 返回字符串str的加密版本,这个加密过程是不可逆转的 例如: SELECT PASSWORD('1234') 结果为:*A4B6157319038724E3560894F7F932C8886EBFCF
六、控制流函数
CASE WHEN[test1] THEN [result1]...ELSE [default] END 如果testN是真,则返回resultN,否则返回default CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等,则返回resultN,否则返回default IF(test,t,f) 如果test是真,返回t;否则返回f IFNULL(arg1,arg2) 如果arg1不是空,返回arg1,否则返回arg2 例如: SELECT IFNULL('bbb','abc'); 结果为: bbb SELECT IFNULL(null,'abc'); 结果为: abc NULLIF(arg1,arg2) 如果arg1=arg2返回NULL;否则返回arg1 例如: SELECT NULLIF('bbb','bbb');结果为: null SELECT NULLIF('aaa','bbb');结果为: aaa
4.2 自定义函数
CREATE FUNCTION fun1(i1 int,i2 int)
RETURNS INT //设置返回类型
BEGIN
DECLARE sum int default 0; 定义变量并设置初始值
set sum = i1+i2; 要执行的sql语句
RETURN(sum); //返回结果
end
4.3 调用自定义函数
-- 直接调用自定义函数
select fun1(1,5);
-- 在sql语句中使用自定义函数
select fun1(参数1,参数2),name from 表名
栗子:
当执行下面代码时:(数据就不列出来了)
select * from goods
在执行下面代码时:(f1是自定义的函数)
select goods.* ,f1(100,num) from goods
结论:自定义函数可以作为表头使用
4.4 删除自定义函数
DROP FUNCTION fun_name;
4.5 函数与存储过程的区别: