SQL函数和高级语句

吾爱主题阅读：147 2023-10-27 17:03:00 评论：0

一、常用函数

1.聚合函数

平均价格

非NULL记录条目数

所有记录条目数

去重记录条目数

最大最小价格

价格总和

2.数学函数

函数名	用途
abs(x)	返回x的绝对值
rand()	返回0到1的随机数
mod(x,y)	返回x除以y以后的余数
power(x,y)	返回×的y次方
round(x)	返回离x最近的整数
round(x, y)	保留x的y位小数四舍五入后的值
sqrt(x)	返回x的平方根
truncate (x, y)	返回数字x截断为y位小数的值
ceil(x)	返回大于或等于x的最小整数
floor(x)	返回小于或等于x的最大整数
greatest(x1,x2,...)	返回集合中最大的值，也可以返回多个字段的最大的值
least(x1,x2,...)	返回集合中最小的值，也可以返回多个字段的最小的值

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;