SQL函数和高级语句(难能可贵的是重新开始的勇气)

一、常用函数

1.聚合函数

平均价格

非NULL记录条目数

所有记录条目数

去重记录条目数

最大最小价格

价格总和

2.数学函数

3.字符串函数

拼接字符串

如果sql模块中有PIPES_AS_CONCAT，也可以使用 || 拼接字符串

截取字符串

替换字符串

去除开头和结尾的字符串

二、sql高级语句

以下两张表用于sql高级语句的演示

商品表

商品分类表

1. SELECT（显示表格中指定字段或所有记录）

SELECT 字段名 FROM 表名;
* 代表所有字段

2.DISTINCT（不显示指定字段数据重复的记录）

SELECT DISTINCT 字段名 FROM 表名;

3.WHERE（指定条件查询）

SELECT * FROM 表名 WHERE 条件表达式;

4.AND、OR（条件且、或）

SELECT * FROM 表名 WHERE 条件1 [AND 条件2] [OR 条件3];

5.IN（显示字段指定的值的数据记录）

SELECT * FROM 表名 WHERE 字段名 [NOT] IN (‘值1’，‘值2’);

6.BETWEEN…AND…（显示字段指定的两个值范围内的数据记录）

SELECT * FROM 表名 WHERE 字段名 BETWEEN ‘值1’ AND ‘值2’;

7.通配符（常在like的模式中使用）

% 百分号表示零个、一个或多个字符
_ 下划线表示单个字符

8.LIKE（查找指定模式范围的记录）

SELECT * FROM 表名 WHERE 字段名 LIKE ‘通配符模式’;

9.ORDER BY（按关键字排序）

SELECT * FROM 表名 [WHERE 条件表达式] ORDER BY 字段 [ASC / DESC];
ASC 按升序排（不添加为默认） DESC 按降序排

10. GRUOP BY（对GROUP BY后面的字段的查询结果进行汇总分组）

通常是结合聚合函数一起使用的，凡是在GROUP BY后面出现的字段，必须在SELECT后面出现；凡是在 SELECT后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY后面。

SELECT 字段名 [聚合函数(字段名)] FROM 表名 GROUP BY 字段名;

11.HAVING（用来过滤由GROUP BY语句返回的记录集，通常与GROUP BY语句联合使用）

HAVING 语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

SELECT 字段名 [聚合函数(字段名)] FROM 表名 GROUP BY 字段名 HAVING 条件;

12.别名（字段别名，表格别名）

SELECT [表格别名.]字段名 [AS] 字段别名 FROM 表名 [AS] 表别名;

13.子查询（连接表格，在WHERE子句或HAVING子句中插入另一个sql语句）

SELECT 字段1 FROM 表1名 WHERE 字段2 [比较运算符] ( SELECT 字段1 FROM 表2名 WHERE 条件表达式);

比较运算符：可以是符号的运算符，例如=、>、<、>=、<= ；也可以是文字的运算符，例如 LIKE、IN、BETWEEN。

14.EXISTS（用来测试内查询有没有产生任何结果）

如果有的话，系统就会执行外查询中的sql语句；若是没有的话，那整个sql语句就不会产生任何结果。

15.表连接（内连、左连、右连）

• inner join (内连接)：只返回两个表中联结字段相等的行；

• left join (左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录；

• right join (右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录。

SELECT * FROM 表1名 A INNER JOIN 表2名 B ON A.字段=B.字段;
字段… LEFT JOIN
RIGHT JOIN

16.CREATE VIEW（视图可以被当作是虚拟表或存储查询）

视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写sql语句会很麻烦，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

创建和查看视图表

CREATE VIEW 视图表名 AS SELECT语句;

使用视图表

17.UNITON（联集，将两个sql语句的结果合并起来）

两个sql语句所产生的字段需要是同样的数据记录种类；

• UNION 生成结果的数据记录值将没有重复，且按照字段的顺序进行排序；

• UNION ALL 将生成结果的数据记录值都列出来，无论有无重复。

SELECT语句1 UNION [ALL] SELECT语句2;

18.空值(NULL)和无值(’ ')的区别

无值的长度为0，不占用空间的；而 NULL值的长度是 NULL，是占用空间的。

IS NULL 或者 IS NOT NULL 是用来判断字段是不是为 NULL，不能查出是不是无值。

无值的判断使用=’ ‘或者<>’ '来处理。<>代表不等于。

在通过 count() 指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值’ '会加入到记录中进行计算。

19.CASE（是sql用来做为if then else 之类逻辑判断的关键字）

SELECT 字段1,…,CASE (判断字段)
WHEN 条件1 THEN 结果1 //条件可以是一个数值或公式
WHEN 条件2 THEN 结果2
[ELSE] 结果n //ELSE子句不是必须的
END
FROM 表名;

20.正则（使用正则表达式查询内容）

SELECT 字段 FROM 表名 WHERE 字段 REGEXP ‘正则表达式’;

21.存储过程（存储过程是一组为了完成特定功能的SQL语句集合）

存储过程在使用过程中是将常用或者复杂的工作预先使用sql语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统sql速度更快、执行效率更高。

存储过程的优点
执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率；
SQL语句加上控制语句的集合，灵活性高；
在服务器端存储,客户端调用时，降低网络负载；
可多次重复被调用，可随时修改，不影响客户端调用；
可完成所有的数据库操作，也可控制数据库的信息访问权限。

（1）创建和使用

--修改结束符为 $$--
delimiter $$
 
--创建存储过程--
create procedure proc1()
    -> begin
    -> insert into `goods` values ('可乐',3.5,'广州');
    -> delete from `goods` where `gname`='可乐';
    -> end$$
 
--将结束符修改回 ;--
delimiter ;
 
--使用存储过程--
call proc1;

（2）查看存储过程信息

（3）查看存储过程创建信息

（4）删除存储过程

DROP PROCEDURE [IF EXISTS] 存储进程名;

（5）含参存储过程的创建和使用

传入参数 IN

传出参数 OUT

同时传入和传出参数 INOUT