SQL高级语句
select
显示表格中的一个或者多个字段中所有的信息
#语法：
select 字段名 from 表名;
distinct
查询不重复记录
#语法:
select distinct 字段 from 表名﹔
where
where 有条件的查询
#语法：
select '字段' from 表名 where 条件
and;or
and 且 ； or 或
#语法：
select 字段名 from 表名 where 条件1 (and|or) 条件2 (and|or)条件3;
in
显示已知值的资料
#语法：
select 字段名 from 表名 where 字段 in ('值1'，'值2'....);
between
显示两个值范围内的资料
#语法：
select 字段名 from 表名 where 字段 between '值1' and '值2';
包括 and两边的值
like 通配符
通配符通常是和 like 一起使用
#语法：
select 字段名 from 表名 where 字段 like 模式
| 通配符 | 含义 |
|---|---|
| % | 表示零个，一个或者多个字符 |
| _ | 下划线表示单个字符 |
| A_Z | 所有以A开头 Z 结尾的字符串 'ABZ' 'ACZ' 'ACCCCZ'不在范围内 下划线只表示一个字符 AZ 包含a空格z |
| ABC% | 所有以ABC开头的字符串 ABCD ABCABC |
| %CBA | 所有以CBA结尾的字符串 WCBA CBACBA |
| %AN% | 所有包含AN的字符串 los angeles |
| _AN% | 所有 第二个字母为 A 第三个字母 为N 的字符串 |
order by
order by 按关键字排序
#语法：
select 字段名 from 表名 where 条件 order by 字段 [asc,desc];
asc ：正向排序
desc ：反向排序
默认是正向排序
函数
数学函数
| 函数 | 含义 |
|---|---|
| abs(x) | 返回x 的 绝对值 |
| rand() | 返回0到1的随机数 |
| mod(x,y) | 返回x除以y以后的余数 |
| power(x,y) | 返回x的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..........) | 返回返回集合中最小的值 |
聚合函数
| 函数 | 含义 |
|---|---|
| avg() | 返回指定列的平均值 |
| count() | 返回指定列中非 NULL 值的个数 |
| min() | 返回指定列的最小值 |
| max() | 返回指定列的最大值 |
| sum(x) | 返回指定列的所有值之和 |
字符串函数
| 函数 | 描述 |
|---|---|
| trim() | 返回去除指定格式的值 |
| concat(x,y) | 将提供的参数 x 和 y 拼接成一个字符串 |
| substr(x,y) | 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring（）函数作用相同 |
| substr(x,y,z) | 获取从字符串 x 中的第 y 个位置开始长度为z 的字符串 |
| length(x) | 返回字符串 x 的长度 |
| replace(x,y,z) | 将字符串 z 替代字符串 x 中的字符串 y |
| upper(x) | 将字符串 x 的所有字母变成大写字母 |
| lower(x) | 将字符串 x 的所有字母变成小写字母 |
| left(x,y) | 返回字符串 x 的前 y 个字符 |
| right(x,y) | 返回字符串 x 的后 y 个字符 |
| repeat(x,y) | 将字符串 x 重复 y 次 |
| space(x) | 返回 x 个空格 |
| strcmp(x,y) | 比较 x 和 y，返回的值可以为-1,0,1 |
| reverse(x) | 将字符串 x 反转 |
group by
- 对group by 后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
- group by 有一个原则，就是select 后面的所有列中，没有使用聚合函数的列必须出现在 group by 的后面。
#语法：
select 字段1，sum(字段2) from 表名 group by 字段1；
having
- having：用来过滤由group by语句返回的记录集，通常与group by语句联合使用
- having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。
- 要根据新表中的字段，来指定条件
#语法:
SELECT 字段1，SUM("字段")FROM 表格名 GROUP BY 字段1 having（函数条件);
别名
栏位別名 表格別名
v#语法：
SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";
连接查询
inner join(等值相连)
只返回两个表中联结字段相等的行
SELECT * FROM students A INNER JOIN scores B on A.stuid = B.stuid;
left join(左联接)
返回包括左表中的所有记录和右表中联结字段相等的记录
select * from scores A left join students B on A.stuid = B.stuid;
right join(右联接)
select * from scores A right join students B on A.stuid = B.stuid;
子查询
连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL语句
语法：
SELECT "栏位1" FROM "表格1" WHERE "栏位2" [比较运算符]
#外查询
(SELECT "栏位1" FROM "表格1" WHERE "条件");
EXISTS
- 用来测试内查询有没有产生任何结果类似布尔值是否为真
- 如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。
#语法：
SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
CREATE VIEW（视图）
可以被当作是虚拟表或存储查询
- 视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。
- 临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
- 视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。
#语法：
CREATE VIEW "视图表名" AS "SELECT 语句";
联集
联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类
UNION
生成结果的资料值将没有重复，且按照字段的顺序进行排序
#语法：
[SELECT 语句 1] UNION [SELECT 语句 2];
UNION ALL
将生成结果的资料值都列出来，无论有无重复
#语法：
[SELECT 语句 1] UNION ALL [SELECT 语句 2];
交集值
取两个SQL语句结果的交集
交集可重复
SELECT A.Store_Name FROM location A INNER JOIN store_info B ON A.Store_Name = B.Store_Name;
SELECT A.Store_Name FROM location A INNER JOIN store_info B USING(Store_Name);
交集无重复
SELECT DISTINCT A.Store_Name FROM location A INNER JOIN store_info B USING(Store_Name);
SELECT DISTINCT Store_Name FROM location WHERE (Store_Name) IN (SELECT Store_Name FROM store_info);
SELECT DISTINCT A.Store_Name FROM location A LEFT JOIN store_info B USING(Store_Name) WHERE B.Store_Name IS NOT NULL;
SELECT A.Store_Name FROM (SELECT B.Store_Name FROM location B INNER JOIN store_info C ON B.Store_Name = C.Store_Name) A
GROUP BY A.Store_Name;
SELECT A.Store_Name FROM
(SELECT DISTINCT Store_Name FROM location UNION ALL SELECT DISTINCT Store_Name FROM store_info) A
GROUP BY A.Store_Name HAVING COUNT(*) > 1;
无交集值
显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复
SELECT DISTINCT Store_Name FROM location WHERE (Store_Name) NOT IN (SELECT Store_Name FROM store_info);
SELECT DISTINCT A.Store_Name FROM location A LEFT JOIN store_info B USING(Store_Name) WHERE B.Store_Name IS NULL;
SELECT A.Store_Name FROM
(SELECT DISTINCT Store_Name FROM location UNION ALL SELECT DISTINCT Store_Name FROM store_info) A
GROUP BY A.Store_Name HAVING COUNT(*) = 1;
CASE
CASE是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字
#语法：
SELECT CASE ("栏位名")
WHEN "条件1" THEN "结果1"
WHEN "条件2" THEN "结果2"
...
[ELSE "结果N"]
END
FROM "表名";
# "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。
表格内容排序
算排名
表格自我连结 (Self Join)，然后将结果依序列出，算出每一行之前 (包含那一行本身) 有多少行数
算中位数
select scores middle from ( select a1.name,a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name <= a2.name) group by a1.name ,a1.scores order by a1.scores desc) a3 where a3.rank = (select (count(*)+1) div 2 from class);
算累积总计
select a1.*,sum(a2.scores) sum_scores from class a1,class a2 where a1.scores < a2.scores or(a1.scores = a2.scores and a1.name = a2.name) group by a1.name order by a1.scores desc;
空值(NULL) 和 无值(’ ') 的区别
- 无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
- IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。
- 无值的判断使用=’‘或者<>’'来处理。<> 代表不等于。
- 在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。
正则表达式
| 匹配模式 | 描述 | 实例 |
|---|---|---|
| ^ | 匹配文本的开始字符 | '^bd'匹配以bd开头的字符串 |
| $ | 匹配文本的结束字符 | 'qn$'匹配以gn结尾的字符串 |
| . | 匹配任何单个字符 | 's.t'匹配任何s和t之间有—个字符的字符串 |
| * | 匹配零个或多个在它前而的字符 | 'fo*t'匹配t前而有任意个。 |
| + | 匹配前面的字符1次或多次 | 'hom+'匹配以ho开头，后而至少一个m的字符串. |
| 字符串 | 匹配包含指定的字符串 | 'clo'匹配含有clo的字符串 |
| p1lp2 | 匹配p1或p2 'bg | fg'匹配bg或者fg |
| […] | 匹配字符集合中的任意一个字符 | '[abc]'匹配a或者b或者c |
| [^…] | 匹配不在括号中的任何字符 | "[^ab]"匹配不包含a或者b的字符串 |
| {n} | 匹配前面的字符串n次 | 'g{2}'匹配含有2个g的字符串 |
| {n,m} | 匹配前而的字符串至少n次，至多m次 | "f{1,3}'匹配f最少1次，最多3次 |
#语法:
SELECT "栏位”FROM "表名" WHERE "栏位”REGEXP {模式};
日期时间函数
| 字符串函数 | 描述 |
|---|---|
| curdate() | 返回当前时间的年月旦 |
| curtime() | 返回当前时间的时分秒 |
| now() | 返回当前时间的日期和时间 |
| month(x) | 返回日期×中的月份值 |
| week(x) | 返回日期×是年度第几个星期 |
| hour(x) | 返回×中的小时值 |
| minute(x) | 返回×中的分钟值 |
| second(x) | 返回×中的秒钟值 |
| dayofweek(x) | 返回×是星期几，1星期日，2星期一 |
| fdayofmonth(x) | 计算日期×是本月的第几天 |
| dayofyear(x) | 计算日期×是本年的第几天 |
#示例1：显示当前日期
select curdate();
#示例2：显示当前的 时分秒
select curtime();
#示例3：返回月份
select month('2021-12-8');
存储过程
存储过程是组为了完成特定功能的SQL语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
存储过程的优点
封装性
通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码
可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
可减少网络流量
由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
提高性能
当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能
提高数据库的安全性和数据的完整性
存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。
使数据独立
数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。
创建、调用和查看存储的过程
创建存储过程
#语法：
CREATE PROCEDURE <存储过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>
- 过程名 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。 需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
- 过程参数 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。 MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。
- 过程体 存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束 在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。
为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下： delimiter
语法说明如下：
$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符
成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个??
mysql > DELIMITER ??
若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可
mysql > DELIMITER ;
注意：DELIMITER 和分号“;”之间一定要有一个空格
调用存储过程
#语法
call 过程名
查看存储过程
#查看某个存储过程的具体信息（如果在指定库中，库名可以省略）
SHOW CREATE PROCEDURE [数据库.] 存储过程名;
删除存储过程
#语法
DROP PROCEDURE IF EXISTS 过程名;
存储过程的参数
- IN输入参数: 表示调用者向过程传入值(传入值可以是字面量或变量)
- OUT输出参数: 表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
- INOUT输入输出参数: 既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)
存储过程的控制语句
条件控制语句 if-then-else … end if
DELIMITER $$
CREATE PROCEDURE proc2(IN pro int)
-> begin
-> declare var int;
-> set var=pro*2;
-> if var>=10 then
-> update t set id=id+1;
-> else
-> update t set id=id-1;
-> end if;
-> end $$
DELIMITER ;
CALL Proc2(6);
循环语句 while … end while
CREATE PROCEDURE "e"()
begin
declare i int;
set i = 1;
create table t2 (id int, name varchar(20));
while i <= 10000
do
insert into t2 values (i, concat('student',i));
set i = i+1;
end while;
end
