开发者社区> 隐姓埋名4869> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL 数据库SQL语句——高阶版本2

简介: 1、EXISTS 2、inner join、left join、right join 3、CREATE VIEW 视图 4、UNION 联集 6、交集值 7、无交集值 8、case
+关注继续查看

实验准备 数据库表配置:

mysql -uroot -p
show databases;
 
create database train_ticket;
use train_ticket;
create table REGION(region varchar(10),site varchar(20));
create table FARE(site varchar(20),money int(10),date varchar(15));
 
desc REGION;
desc FARE;
 
insert into REGION values ('south','changsha');
insert into REGION values ('south','nanchang');
insert into REGION values ('north','beijing');
insert into REGION values ('north','tianjin');
 
insert into FARE values ('changsha',1000,'2021-01-30');
insert into FARE values ('nanchang',700,'2021-01-30');
insert into FARE values ('beijing',1500,'2021-01-30');
insert into FARE values ('tianjin',1200,'2021-01-30');
insert into FARE values ('beijing',2200,'2021-02-05');
 
select * from REGION;
select * from FARE;

2387773-20210901172824352-912139318.png


MySQL高级SQL语句:


1、EXISTS:

用来测试内查询有没有产生任何结果,类似布尔值是否为真

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

语法:SELECT 字段1 FROM 表1 WHERE EXISTS (SELECT * FROM 表2 WHERE 条件);


例如:

select region from REGION where exists (select * from FARE where money = '700');

2387773-20210901173432625-391529157.png

2387773-20210901173438085-115532874.png


2、inner join、left join、right join :


inner join(等值相连) :

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

语法:SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段; <em id="__mceDel"> <br></em>


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='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;

2387773-20210901194222175-1311917835.png



3、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;

2387773-20210901194237772-83489577.png

2387773-20210901194242606-1755325950.png


4、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;

2387773-20210901194252637-2140105449.png

2387773-20210901194258936-1606625290.png


6、交集值:

取2个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);

2387773-20210901194306688-2100261096.png

2387773-20210901194311846-145935085.png

2387773-20210901194318448-1388313249.png


7、无交集值:

显示第一个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;

2387773-20210901194327625-1247210420.png


8、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;

2387773-20210901194340216-904207413.png


算排名 :


表格自我连接(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;

2387773-20210901205442453-993236782.png


#######当A1的score字段值小于A2的score字段值、或者两表score字段值相等并且name字段值相等时,<br>从A1和A2表中 查询A1的name字段值、A1的score字段值、和A2的score字段的非空值 rank是别名 ,<br>并为A1的name字段分组,A1的score字段降
A1.score为75时,A2.score可为75、80、90、95、96 则 count(A2.score)为5 <br>A1.score为80时,A2.score可为80、90、95、96 则 count(A2.score)为4 <br>A1.score为90时,A2.score可为90、95、96 则 count(A2.score)为3 <br>A1.score为95时,A2.score可为95、96 则 count(A2.score)为2 <br>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);

2387773-20210901205451777-768702990.png


算累积总计:


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;

2387773-20210901205458752-1785375226.png


算总合百分百:

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 sum(socre) from SCORE 是为了算出字段值总合,然后每一行一一除以总合,算出每行的总合百分比。 


 2387773-20210901205511880-1928623746.png


算累计总合百分百:

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;

2387773-20210901205519396-957534524.png


2387773-20210901205524693-1352447801.png


空值(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('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;


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;

2387773-20210901205535456-1980840843.png

2387773-20210901205540467-1411267998.png


正则表达式:(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 匹配模式 

例如:

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


存储过程(与Shell函数差不多,代码的复用) :

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

 

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

 

 

 存储过程的优点 :

  1. 执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率
  2. SQL语句加上控制语句的集合,灵活性高
  3. 在服务器端存储,客户端调用时,降低网络负载
  4. 可多次重复被调用,可随时修改,不影响客户端调用
  5. 可完成所有的数据库操作,也可控制数据库的信息访问权限
语法:
DELIMITER !!            #将语句的结束符号从分号;临时修改,以防出问题,可以自定义
CREATE PROCEDURE XXX()      #创建存储过程,过程名自定义,()可带参数
    BEGIN           #过程体以关键字BEGIN开始
    select * from xxx;      #过程体语句
    END!!           #过程体以关键字END结尾
DELIMITER ;             #将语句的结束符号恢复为分号
 
call XXX;           #调用存储过程
 
-----查看存储过程-----
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


存储过程的条件语句:

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;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【C/C++学院】(21)Mysql数据库编程--开发简介/SQL语句
<h1><span style="line-height:1.5"><span style="font-size:18px">一.mysql安装与管理</span></span></h1> <div><span style="line-height:1.5; font-size:18px; font-family:'Microsoft YaHei UI','Microsoft YaHei
889 0
MySQL的常用SQL语句
修改密码 这是常见的大家一般都要用的   首先安装成功了打开cmd –> mysql -u root -p –>输入你的密码 –>修改mysql root用户密码    格式:mysql> set password for 用户名@localhost = password(‘新密码’);    举例:mysql> set password for root@localhost = password(‘root’); 上面例子将用户root的密码更改为root; 如果是学习使用一般密码该简单些,如root,123等,这样便于记忆。
1075 0
利用tcpdump抓取mysql sql语句
这个脚本是我之前在网上无意间找个一个利用tcpdump 抓包工具获取mysql流量,并通过过滤把sql 语句输入。 脚本不是很长,但是效果很好。 #!/bin/bash #this script used montor mysql network traffic.
524 0
MySQL关于SQL语句的一些题目
MySQL关于SQL语句的一些题目 1、用一条SQL语句查询出每门课都大于80的学生的姓名 (1)SELECT NAME FROM score GROUP BY NAME HAVING MIN(grade) > 80 (2)SELECT DISTINCT NAME FROM score WH.
1388 0
MySQL系列专题(2)-MySQL的SQL语句和高级特性
MySQL系列专题(2)-MySQL的SQL语句和高级特性
0 0
Mysql常用sql语句(13)- having 过滤分组结果集
Mysql常用sql语句(13)- having 过滤分组结果集
0 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MySQL 5.7让优化更轻松
立即下载
好的 MySQL 兼容可以做到什么程度
立即下载
MySQL表和索引优化实战
立即下载