文章目录
- 一、MySQL高级SQL语句
- EXISTS
- inner join、left join、right join
- CREATE VIEW 视图
- UNION 联集
- 交集值
- 无交集值
- CASE
- 二、数值计算
- 算排名
- 算中位数
- 算累积总计
- 算总合百分比
- 算累计总合百分比
- 空值无值
- 正则表达式
- 存储过程
- 循环语句while
一、MySQL高级SQL语句
EXISTS
用来测试内查询有没有产生任何结果，类似布尔值是否为真
如果有的话，系统就会执行外查询中的SQL语句，若是没有，那整个SQL语句就不会产生任何结果。
语法：SELECT 字段1 FROM 表1 WHERE EXISTS (SELECT * FROM 表2 WHERE 条件);select money from FARE where exists (select * from FARE where money = '1000');
inner join、left join、right join
inner join（等值相连）：只返回两个表中联接字段相等的行
语法：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;left join（左联接）：返回包括左表中所有记录和右表中联接字段相等的记录
语法：SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;right join（右联接）：返回包括右表中的所有记录和左表中联接字段相等的记录
语法：SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;例：
update FARE set site='changzhou' where money='1400';
select * from REGION AS A inner join FARE AS B on A.site = B.site;
select * from REGION AS A left join FARE AS B on A.site = B.site;
select * from REGION AS A right join FARE AS B on A.site = B.site;
CREATE VIEW 视图
视图：可被当作虚拟表或存储查询。
视图跟表格不同的是，表格中有实际存储资料，而视图是建立在表格之上的一个架构，它本身并不实际存储资料。
临时表在用户退出或者同数据库的连接断开后就自动消失了，而视图不会。
语法：CREATE VIEW 视图表名 AS SELECT语句;例:
select A.region,SUM(B.money) from REGION AS A inner join FARE AS B on A.site = B.site GROUP BY region;
create view C AS select A.region,SUM(B.money) from REGION AS A inner join FARE AS B on A.site = B.site GROUP BY region;
show tables;
select * from C;
删除视图表
drop view C;
UNION 联集
将两个SQL语句的结果合并起来，两个SQL语句产生的字段需要是同样的资料种类
UNION:生成结果的资料值将没有重复，且按照字段的顺序进行排序。
语法：SELECT语句1 UNION SELECT语句2;UNION ALL：将生成结果的资料值都列出来，无论有无重复
语法：SELECT语句1 UNION ALL SELECT语句2;例：
select region from REGION union select money from FARE;
select site from REGION union select site from FARE;
select site from REGION union all select site from FARE;
交集值
取两个SQL语句结果的交集
insert into FARE values ('changchun',1000,'2021-03-12');
select * from REGION;
select * from FARE;
select A.site from FARE AS A inner join REGION AS B on A.site = B.site;
select A.site from FARE AS A inner join REGION AS B using(site);
两表没用重复的行，并且确实有交集时
select site from (select site from FARE union all select site from REGION) A group by A.site HAVING count(*) > 1;
取两个SQL语句结果的交集，且没有重复
select site from (select A.site from FARE A inner join REGION B on A.site = B.site) C group by C.site;
select site from FARE where site in (select site from REGION);
无交集值
显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，还不能重复
select distinct site from FARE where (site) not in (select site from REGION);
例：
select A.*,B.* from FARE A left join REGION B using(site);
select site from FARE A left join REGION B using(site) where B.site is null;
select distinct site from FARE A left join REGION B using(site) where B.site is null;
CASE
SQL用来作为IF-THEN-ELSE之类逻辑的关键字
SELECT CASE （字段名）
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
……
ELSE 结果N
END
FROM 表名
#条件可以是一个数值或是公式。ELSE子句不是必须的。select * from FARE;
select case site
when 'henan' then money + 1000
when 'nanjing' then money - 100
else money * 2
end
A,site
from FARE;
二、数值计算
CREATE TABLE passenger (Name char(10),score int(5));
INSERT INTO passenger VALUES ('zhangsan',10);
INSERT INTO passenger VALUES ('lisi',15);
INSERT INTO passenger VALUES ('wangwu',20);
INSERT INTO passenger VALUES ('zhaoliu',40);
INSERT INTO passenger VALUES ('xiaoming',50);
select * from passenger;算排名
表格自我连接（self join），然后将结果依序列出，算出每一行之前（包括那一行本身）有多少行数
select A1.name,A1.score,count(A2.score) rank from passenger A1,passenger A2 where ere A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name orrder by A1.score desc;当A1的score字段值小于A2的score字段值、或者两表score字段值相等并且name字段值相等时，从A1和A2表中 查询A1的name字段值、A1的score字段值、和A2的score字段的非空值 rank是别名 ，并为A1的name字段分组，A1的score字段降序排序
A1.score为75时，A2.score可为75、80、90、95、96 则 count(A2.score)为5
A1.score为80时，A2.score可为80、90、95、96 则 count(A2.score)为4
A1.score为90时，A2.score可为90、95、96 则 count(A2.score)为3
A1.score为95时，A2.score可为95、96 则 count(A2.score)为2
A1.score为96时，A2.score可为96 则 count(A2.score)为1
算中位数
select * from (select A1.name,A1.score,count(A2.score) rank from passenger A1,passenger A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from passenger);
select score mid from (select A1.name,A1.score,count(A2.score) rank from passenger A1,passenger A2 where A1.score < A2.score OR (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from passenger);
算累积总计
select A1.*,sum(A2.score) sum_socore from passenger A1,passenger A2 where A1.score < A2.score or(A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;
算总合百分比
select A1.*,A1.score/(select sum(score) from passenger) z_sum from passenger A1,passenger A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name;
算累计总合百分比
select A1.name,A1.score,sum(A2.score),sum(A2.score)/(select sum(score) from passenger) Z from passenger A1,passenger A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;
select A1.name,A1.score,sum(A2.score),TRUNCATE(sum(A2.score)/(select sum(score) from passenger),2) ||'%' Z from passenger A1,passenger A2 where A1.score < A2.score or (A1.score=A2.score and A1.name=A2.name) group by A1.name order by A1.score desc;
空值无值
空值（null）和无值（’’）的区别
1、无值的长度为0，不占用空间；而空值null 的长度是null，是占用空间的
2、IS NULL或者IS NOT NULL，是用来判断字段是不是NULL或者不是NULL，是不能查出是不是无值的
3、无值的判断使用=’‘或者<>’'来处理。<>代表不等于
4、在通过count()指定字段统计又多少行数时，如果遇到NULL值会自动忽略掉，遇到空值会自动加入记录中进行计算
create table SITE(site varchar(20));
insert into SITE values('taijin');
insert into SITE values('');
insert into SITE values();
select * from SITE;
select length(site) from SITE;
select * from SITE where site is NULL;
select * from SITE where site is not NULL;
select * from SITE where site ='';
select * from SITE where site <> '';
正则表达式
与Shell部分一样
匹配模式 描述 实例
^ 匹配文本的开始字符 ‘^bd’ 匹配以 bd 开头的字符串
$ 匹配文本的结束字符 ‘qn$’ 匹配以 qn 结尾的字符串
. 匹配任何单个字符 ‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
* 匹配零个或多个在它前面的字符 ‘fo*t’ 匹配 t 前面有任意个 o
+ 匹配前面的字符 1 次或多次 ‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串 匹配包含指定的字符串 ‘clo’ 匹配含有 clo 的字符串
p1|p2 匹配 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 匹配模式存储过程
1、存储过程是一组为了完成特定功能的SQL语句集合。
2、存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
3、存储过程的优点：
（1）执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
（2）SQL语句加上控制语句的集合，灵活性高
（3）在服务器端存储，客户端调用时，降低网络负载
（4）可多次重复被调用，可随时修改，不影响客户端调用
（5）可完成所有的数据库操作，也可控制数据库的信息访问权限
4、创建存储过程
DELIMITER $$ 将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE KIND1() 创建存储过程，过程名为Proc，不带参数
BEGIN 过程体以关键字 BEGIN 开始
select * from FARE; 过程体语句
END $$ 过程体以关键字 END 结束
DELIMITER ; 将语句的结束符号恢复为分号
CALL KIND1;
5、查看存储过程
show create procedure train_ticket.KIND1\G
show procedure status like '%KIND1%'\G
6、存储过程的参数
IN 输入参数，表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值（可以返回多个值，传出值只能是变量）
例：
DELIMITER !!
CREATE PROCEDURE KIND2(IN place char(20))
BEGIN
select * from FARE where site=‘beijing’;
END!!
DELIMITER ;
CALL KIND2('beijing');
DELIMITER !!
CREATE PROCEDURE KIND4(IN num int(10))
BEGIN
declare var int;
set var=num*2;
if var>=10 then
update FARE set money=money+1;
else
update FARE set money=money-1;
end if;
END!!
DELIMITER ;CALL KIND4(5);
CALL KIND4(4);
循环语句while
create table akg(id int);
DELIMITER !!
CREATE PROCEDURE KIND4()
BEGIN
declare var int;
set var=0;
while var<5 do
insert into akg values(var);
set var=var+1;
end while;
END!!
DELIMITER ;
CALL KIND4;
select * from akg;
