前言
- 本文实验环境及数据承接上一条博客
- 传送门:MySQL 数据库–SQL 语句(一)(高阶运用)(图文详解)
一、SQL 语句
1.EXISTS
- 用来测试内查询有没有产生任何结果,类似布尔值是否为真
- 如果有的话,系统就会执行外查询中的SQL语句
- 若是没有,那整个SQL语句就不会产生任何结果
- 用法:
SELECT 字段1 FROM 表1 WHERE EXISTS (SELECT * FROM 表2 WHERE 条件);
例:
use train_ticket;
select region from REGION where exists (select * from FARE where money = '800');
select site from REGION where exists (select * from FARE where money = '801');
select * from FARE where money = '800';
select * from FARE where money = '801';
select site from REGION;
2.连接查询
2.1 inner join——等值相连
- 只返回两个表中联接字段相等的行
- 用法:
SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;
例:
select * from REGION AS A inner join FARE AS B on A.site = B.site;
2.2 left join——左联接
- 返回包括左表中所有记录和右表中联接字段相等的记录
- 例:
SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;
例:
select * from REGION AS A left join FARE AS B on A.site = B.site;
2.3 right join——右联接
- 返回包括右表中的所有记录和左表中联接字段相等的记录
- 例:
SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
例:
update FARE set site='nanjing' where money='2000';
select * from REGION AS A right join FARE AS B on A.site = B.site;
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 ST 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 ST;
drop view ST;
#删除视图表
4.UNION 联集
- 将两个SQL语句的结果合并起来,两个SQL语句产生的字段需要是同样的资料种类
- UNION:生成结果的资料值将没有重复,且按照字段的顺序进行排序
SELECT语句1 UNION SELECT语句2;
或
SELECT语句1 UNION ALL SELECT语句2;
#UNION ALL:将生成结果的资料值都列出来,无论有无重复
例:
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;
5.交集值
- 取两个SQL语句结果的交集
- 例:
select * from FARE;
select * from FARE;
select site from (select A.site from FARE A inner join REGION B on A.site = B.sit