MySQL操作之select（2）

一、别名------栏位別名，表格別名

语法：SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";
例：
select A.Region "地区",A.Store_name "名称" from text as "A"

二、子查询：连接表格

• 子WHERE子句或HAVING子句中插入另一个SQL语句

语法：
#外查询
SELECT "栏位1" FROM "表格1" WHERE "栏位2" [比较运算符]
#内查询
(SELECT "栏位1" FROM "表格2" WHERE "条件");
#可以是符号的运算符，例如：=、>、<、>=、<= ；也可以是文字的运算符，例如 LIKE、IN、BETWEEN、

select sum(sales) from text2 where store_name in (select store_name from  text where region='west');
#下面这个句子就是上面的简化版
select sum(sales) from text2 where store_name in('Los Angeles','Houston');

select * from text A where A.store_name in (select store_name from text2 B where A.store_name=B.store_name);

三、EXISTS------用来测试内查询有没有产生任何结果

• 类似布尔值是否为真
• 如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。

语法：SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
例：
select store_name from text where exists (select store_name from text2 where store_name='Houston');

四、连接查询

1、inner join（等值相连）

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

SELECT * FROM 表1 表1的别名 INNER JOIN 表2 表2的别名 on 表1别名.栏位 = 表2别名.栏位;  #这里的栏位名称相同
例：
select * from text A inner join text2 B on A.store_name=B.store_name;

2、left join（左连接）

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

SELECT * FROM 表1 表1的别名 LEFT JOIN 表2 表2的别名 on 表1别名.栏位 = 表2别名.栏位;  #这里的栏位名称相同
例：
select * from destination a left join info b on a.place_name = b.place_name;

3、right join（右连接）

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

SELECT * FROM 表1 表1的别名 RIGHT JOIN 表2 表2的别名 on 表1别名.栏位 = 表2别名.栏位;  #这里的栏位名称相同
例：

五、Create view视图

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

语法：CREATE VIEW "视图表名" AS "SELECT 语句";
例：
create view V_text3 as select A.store_name View_store_name,B.sales View_sales from text A inner join text2 B on A.store_name=B.store_name;

select * V_text3;
drop view V_text3;

六、MySQL之联集、交集值、无交集值、case

1、联集

• 将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类

Union

UNION ：生成结果的资料值将没有重复，且按照字段的顺序进行排序
语法：[SELECT 语句 1] UNION [SELECT 语句 2];

例:
select store_name from text union select store_name from text2;

UNION ALL

UNION ALL ：将生成结果的资料值都列出来，无论有无重复
语法：[SELECT 语句 1] UNION ALL [SELECT 语句 2];

例：
select store_name from text union ALL select store_name from text2;

2、交集

• 取两个SQL语句结果的交集

select A.store_name from text A inner join text2 B on A.store_name=B.store_name;

select A.store_name from text A inner join text2 B using(store_name);

• 取两个SQL语句结果的交集，且没有重复

select A.store_name from text A inner join text2 B using(store_name) group by store_name;

select distinct A.store_name from text A inner join text2 B using(store_name);

3、无交集值

• 显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复

select distinct store_name from text where (store_name) not in (select store_name from text2);

select distinct a.place_name from destination a left join info b using(place_name) where b.place_name is null;

4、case

• CASE是 SQL 用来做为 if-then-else 之类逻辑的关键字

语法：
SELECT CASE ("栏位名")
  WHEN "条件1" THEN "结果1"
  WHEN "条件2" THEN "结果2"
  ...
  [ELSE "结果N"]
  END
FROM "表名";

# "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。

例如：
select store_name,case store_name
 when 'Houston' then sales - 500 
 when 'Boston' then sales + 500 
 else sales +1000 
 end
 "new sales",Date 
 from text2;
#"New Sales" 是用于 CASE 那个栏位的栏位名。

七、排序

• 首先需要先准备一张新表report

1、算排名

• 表格自我连结 (Self Join)，然后将结果依序列出，算出每一行之前 (包含那一行本身) 有多少行数

select A.name,A.score,count(B.score) rank from report A,report B where A.score < B.score or (A.score = B.score and A.name=B.name) group by A.name,A.score order by A.score desc;

2、算中位数

select score middle from(
select A.name,A.score,count(B.score) rank from report A,report B 
where A.score < B.score or (A.score = B.score and A.name=B.name) 
group by A.name,A.score order by A.score desc) C
where C.rank=(select (count(*)+1)div 2 from report);

3、算累积总计

• 表格自我连结 (Self Join)，然后将结果依序列出，算出每一行之前 (包含那一行本身) 的总合

select A.name,A.score,sum(B.score) rank from report A,report B where A.score < B.score or (A.score = B.score and  A.name=B.name) group by A.name,A.score order by A.score desc;

4、算总合百分比

select A.name,A.score,round(A.score/(select sum(score) from report)*100) || '%' 百分比 from report A,report B where ere A.score < B.score or (A.score = B.score and A.name=B.name) group by A.name,A.score order by A.score desc;

5、算累积总合百分比

select A.name,A.score,sum(B.score) 累计和,round(sum(B.score)/(select sum(score) from report)*100) || '%' 总和百 分比 from report A,report B where A.score < B.score or (A.score = B.score and  A.name=B.name) group by A.name,A.score order by A.score desc;