MySQL 高级语句二

表连接查询

MYSQL数据库中常用的表连接有三种

• inner join(内连接)只返回两个表中联结字段相等的行有交集的值

• left join(左连接)返回包括左表中的所有记录和右表中联结字段相等的记录
• A left join B A为左表B为右表
  
• right join(右连接)返回包括右表中的所有记录和左表中联结字段相等的记录
• A right join B A为左表 B为右表
  

内连接 inner join

select 左表别名.字段 from 左表 坐标别名 inner join 右表 右表别名 on 坐标别名.字段 = 右表别名.字段 

select * from location a inner join store_info b on a.store_name = b.store_name;

其他实现内连接的方式

select * from location a, store_info b where a.store_name = b.store_name;

select * from location a inner join store_info b using(store_name);

左连接 left join

select * from location a left join store_info b on a.store_name = b.storetore_name;

右连接 right join

select * from location a right join store_info b on a.store_name = b.store_name;

表的自我连接 算排名

对下面的表进行saving比较并且进行排名通过表的自我连接进行实现
表的自我连接达到排名的原理分析及操作思路

1. 以上面的数据表为例假设共有四个人他们手中的金额各不相同。我们已经进行表的自我连接。

2. 使用count计数只计数大于等于自身手上金额的人数比如2000的小明大于等于他的人数只有1个就计数值也可以当作他的排名

3. 再比如800的小红大于等于她的有4个人就该计数值为4同理可以证明她排名第四。

SELECT A.name, A.saving, count(B.saving) rank FROM paiming A, paiming B WHERE A.saving < B.saving OR (A.saving = B.saving AND A.Name = B.Name) GROUP BY A.name, A.saving ORDER BY rank;

view 的视图的运用

视图可以被当作是虚拟表或存储查询。

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

格式

CREATE VIEW "视图表名" AS "SELECT 语句";   #创建视图表
 ​
 DROP VIEW "视图表名";                     #删除视图表

create view v_region_sales as select a.region region,sum(b.sales) sales from location a inner join store_info b on a.store_name = b.store_name group by region;

查看view表

注意 创建view表时如果没有对加入的sql语句的结果进行任何的聚合函数group by 等修改 则可以通过修改view表中的数据否则不可以进行修改

union 联级

union联集将两个SQL语句的结果合并起来两个SQL语句所产生的字段需要是同样的数据记录种类。

union合并后去重

生成结果的数据记录值将没有重复且按照字段的顺序进行排序。#合并后去重

格式[select 语句1] UNION [select 语句2];

select store_name from location union select store_name from store_info;

union all 合并后不去重

select store_name from location union all select store_name from store_ine_info;

多种方式求表与表的交集值

取两个SQL语句结果的交集

SELECT A.Store_Name FROM location A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name;

SELECT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name);

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

SELECT DISTINCT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name);

SELECT DISTINCT Store_Name FROM location WHERE (Store_Name) IN (SELECT Store_Name FROM Store_Info);

SELECT DISTINCT A.Store_Name FROM location A LEFT JOIN Store_Info B USING(Store_Name) WHERE B.Store_Name IS NOT NULL;

SELECT A.Store_Name FROM (SELECT B.Store_Name FROM location B INNER JOIN Store_Info C ON B.Store_Name = C.Store_Name) A 
GROUP BY A.Store_Name;

SELECT A.Store_Name FROM 
(SELECT DISTINCT Store_Name FROM location UNION ALL SELECT DISTINCT Store_Name FROM Store_Info) A 
GROUP BY A.Store_Name HAVING COUNT(*) > 1;

内连接求去重后的交集

使用左连接求交集

使用子查询的方式求交集值

SELECT DISTINCT Store_Name FROM location WHERE (Store_Name) IN (SELECT Store_Name FROM store_info);

取非交集值

1联级方法中 count*<=1
(2)左右内连接 将is not null 改为 is null
3子查询 外连接查询 not in 内连接查询

case 条件选择查询语句

 SELECT CASE ("字段名")
     WHEN "条件1" THEN "结果1"
     WHEN "条件2" THEN "结果2"
     [ELSE "结果N"]
     END
 FROM "表名";
     
 # "条件"可以是一个数值或是公式。ELSE子句则并不是必须的。

select store_name,case store_name when 'los angeles' then sales * 2 when 'bos,Date from store_info;

空值(NULL) 和 无值(‘’) 的区别

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

sql正则表达式的常见种类

select "字段" from "表名" where "字段" regexp '正则表达式';