MySQL高级语句（二）

MySQL高级语句（二）

MySQL高级语句（二）

一、等值、左、右联接

等值联接 inner join

只返回两个表中联接字段相等的行

SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;

左联接

返回包括左表中所有记录和右表中联接字段相等的记录

SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

右联接

返回包括右表中的所有记录和左表中联接字段相等的记录

SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

例如：

update FARE set site='xingjiang' where money='2200'; 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 视图

视图：可被当作虚拟表或存储查询。视图跟表格不同的是，表格中有实际存储资料，而视图是建立在表格之上的一个架构，它本身并不实际存储资料。 临时表在用户退出或者同数据库的连接断开后就自动消失了，而视图不会。 视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表格进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦，用视图将几个表格连接起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

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;

删除视图表

三、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语句结果的交集

select * from FARE; insert into FARE values ('xingjiang',2200,'2021-02-05'); 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; 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 'changsha' then money + 500 when 'tianjin' then money - 500 else money * 2 end A,site from FARE;

七、算排名

表格自我连接（self join），然后将结果依序列出，算出每一行之前（包括那一行本身）有多少行数

例: create table SCORE (name char(10),score int(4)); insert into SCORE values('zhangsan',95); insert into SCORE values('lisi',96); insert into SCORE values('wangwu',75); insert into SCORE values('zhaoliu',80); insert into SCORE values('qianqi',90); select * from SCORE;

select A1.name,A1.score,count(A2.score) rank from SCORE A1,SCORE 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;

当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

这样再显示A1的name字段分组，A1的score字段降序排序 ，rank字段则为下表所示

八、算中位数

select * from (select A1.name,A1.score,count(A2.score) rank from SCORE A1,SCORE 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 SCORE); select score mid from (select A1.name,A1.score,count(A2.score) rank from SCORE A1,SCORE 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 SCORE);

九、算累计总计

例： select A1.*,sum(A2.score) sum_socore from SCORE A1,SCORE 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 SCORE) z_sum from SCORE A1,SCORE 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 SCORE) Z from SCORE A1,SCORE 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 SCORE),2) ||'%' Z from SCORE A1,SCORE A2 where A1.score < A2.score or (A1.score=A2.score and A11.name=A2.name) group by A1.name order by A1.score desc;

十二、空值（null）和无值（’’）的区别

无值的长度为0，不占用空间；而空值null 的长度是null，是占用空间的 IS NULL或者IS NOT NULL，是用来判断字段是不是NULL或者不是NULL，是不能查出是不是无值的 无值的判断使用=’‘或者<>’'来处理。<>代表不等于 在通过count()指定字段统计又多少行数时，如果遇到NULL值会自动忽略掉，遇到空值会自动加入记录中进行计算

create table SITE(site varchar(20)); insert into SITE values('nanjing'); insert into SITE values('beijing'); insert into SITE values(''); 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 <> '';

十三、正则表达式

语法 SELECT 字段 FROM 表名 WHERE 字段 REGEXP 匹配模式

select * from FARE where site regexp '^[n]'; select * from FARE where site regexp '[n]'; select * from FARE where site regexp 'jin|jing';

十三、存储过程

他是一组忘了完成特定功能的SQL语句集合

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

优点：

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

##查看存储过程 show create procedure [数据库.]储存过程名; #查看某个储存过程的具体信息 show create procedure XXX; show procedure status [like '%XXX%'] \G

DELIMITER !! CREATE PROCEDURE KIND1() BEGIN select * from FARE; END!! DELIMITER ; CALL KIND1;

show create procedure train_ticket.KIND1\G show procedure status like '%KIND1%'\G

存储过程的参数IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）OUT 输出参数：表示过程向调用者传出值（可以返回多个值，传出值只能是变量）

DELIMITER !! CREATE PROCEDURE KIND2(IN place char(20)) BEGIN select * from FARE where site=place; END!! DELIMITER ; CALL KIND2('beijing');

存储过程的条件语句

DELIMITER !! CREATE PROCEDURE KIND3(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 KIND3(5); CALL KIND3(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;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。