常用SQL整理

数据格式

一：查询语言

结构：SELECT COLUMNS FROM TABLE WHERE ...
            GROUP BY ... HAVING ... ORDER BY ...

• 条件选择多列

例子：
SELECT dates,house,feaue,money FROM Joint_rent 
WHERE (dates IN ("2020-02-07","2020-02-14")) AND (money BETWEEN 1000 AND 1200)

> < = <> 
and or not in between like limit
#BETWEEN 包含边界 NOT BETWEEN 不包含边界

#模糊查询 通配符 
#'%' ：0个多多个字符  '_'：任意单个字符 '[ ]' :[]内任意一个 '[^]'：不在内任意单个
SELECT * FROM Joint_rent WHERE subway LIKE '%'地铁
SELECT * FROM Joint_rent WHERE region LIKE '工业园%'
SELECT * FROM Joint_rent WHERE feaue LIKE '%独立阳台%'
#'%字符串' 以某字符串结尾  '字符串%' 以某字符串结开头 '%字符串%' 包含某字符串

SELECT * FROM Joint_rent WHERE floor LIKE '[低中]楼层'
SELECT * FROM Joint_rent WHERE area LIKE '吴_区'

SELECT * FROM `Joint_rent` LIMIT 5,10
#从第五行开始u选取10个数据   LIMIT 5 是LIMIT 0,5的缩写

• 排序、去重、分组聚合

排序：
# DESC 降序 null值排在末尾
SELECT * FROM Joint_rent ORDER BY money
SELECT * FROM Joint_rent ORDER BY money DESC

#去重后统计个数
SELECT COUNT(DISTINCT house) FROM Joint_rent

#count(*)和count(列名)区别：前者统计包含null值 ，后者不包含
SELECT COUNT(*) from Joint_rent
SELECT COUNT(house) from Joint_rent

#分组常与聚合函数一起使用
SELECT dates,AVG(money) AS 均价 FROM Joint_rent WHERE dates < '2020-02-10' 
GROUP BY dates HAVING 均价 < 1200 ORDER BY 均价
#注意点：
#使用GROUP BY 后，SELECT 后面除了分组的列，其它必须使用聚合函数
#不然无法判断返回的值是出自哪里 
#GROUP BY 后面也可以接条件。
#WHERE 语句和GROUP BY 配合使用  WHWERE在前
#顺序：SELECR FROM WHERE GROUP BY  HAVING ORDER BY
#WHERE 过滤针对的是行并且不能选择聚合列，HAVING过滤针对的是组。

• 子查询

#一个查询的结果在另一个查询中使用就叫做子查询
SELECT * FROM table_name 
WHERE  age  < (SELECT  avg(age)  FROM table_name );

#查询某一列小于平均值
SELECT * FROM table_name_1 
WHERE  id  in  (SELECT  id  FROM table_name_2 );

#关联子查询：是指在内查询中需要借助于外查询，而外查询离不开内查询的执行。
#首先执行外部提取第一行，然后将数据中的region传递给内查询，内查询根据region，查询平均工资。最后外查询取第一行数据中money比较内查询查出的平均值。
SELECT * FROM Zu_Fang  a
WHERE  money  < (SELECT  avg(money)  FROM table_name  WHERE  region = a.region);

• 联合查询

# union和union all区别在于 union all 允许重复值，union会筛选掉重复值
# union all 比 union快
SELECT <Column List> FROM <Table1>
UNION
SELECT <Column List> FROM <Table2>

• 连接查询

#inner join left join right join  full join
SELECT Orders.LastName, Countries.CountryName
FROM Orders 
INNER JOIN Countries 
ON Orders.CountryID = Countries.ID
# inner join 两边匹配到的 则返回
# left join     左边有的就返回，左边没有匹配到的用null填充
# right join   右边有的就返回，右边没有匹配到的用null填充
# full join  全返回，没有的用null 填充

二：数据更新

INSERT、 UPDATE 、DELETE、 ALTER

• INSERT

insert into  表名 (column1,columns2...) values(...)

条件复制到：
create table table2 like table1;
insert into table2 select *from table1;

• DELETE

删除整表数据：
delete from table 

删除部分数据：
delete from table where...

删除表内数据和结构：
drop table 表名
drop database 数据库名

• UPDATE

update 表名 set 列名1 = 表达式1，列2 = 表达式2 where  行条件

• ALTER

alter和update的区别在于update更改的是表的数据，alter更改的是表的结构

alter table 表名 add 列名 datatype  #添加字段
alter table 表名 drop column 列名  #删除字段3

三：常用函数

算术函数：
mod：取余
abs  ：绝对值
round ：四舍五入

字符串函数：
concat(str1,str2...)：拼接
upper lower：大小写转化
replace(str,old_str,new_str)：替换字符串中的部分字符
substr(str,start_position_char_num)：截取字符串中的一部分

转化函数：
cast(值,像转换的类型）

四：谓词

返回值是真值的函数
like
between
in
not in
exists  判断是否存在某个条件的记录,存在则返回true，记录的是索引
exists 运用了关联子查询

select * from Zu_Fang a where exists(select 1 from Zu_Fang b where 
a.region=b.region and a.money <b.money)
#exists子句不在乎返回什么，而是在乎是不是有结果集返回
#exists 就是存在  not exists 就是不存在


select * from Zu_Fang a where not exists(select 1 from Zu_Fang b where 
a.house=b.house and a.money <b.money)
#首先从外查询取出第一行数据，传递给内部，内查询执行select * from Zu_Fang b 
#where a.house=b.house and a.money < b.money 看是否查询到结果 存在就不返回


exists子句不在乎返回什么，而是在乎是不是有结果集返回
exists 与 in 最大的区别在于 in引导的子句只能返回一个字段
select * from Zu_Fang a where id in (select id from...)


case 语句
case when 条件 then 表达式 else 表达式 end
THEN后边的值与ELSE后边的值类型应一致，否则会报错
例子:

SELECT house,(CASE WHEN money <1200 THEN "价格合理" ELSE "不合理" END) AS price  FROM Zu_Fang


SELECT dates,SUM(CASE WHEN region = '工业园区' THEN 1 ELSE 0 END) 
AS nums FROM Zu_Fang GROUP BY dates

五：窗口函数

语法：<窗口函数>over (partition by<用于分组的列名>order by<用于排序的列名>)
<窗口函数>位置：
1 专用窗口函数：rank row_number  dense_rank
2 一些聚合函数：sum. avg, count, max, min

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能
group by分组汇总后改变了表的行数，一行只有一个类别
而partiition by和rank函数不会减少原表中的行数

应用场景：
一 ：组内排名
1
select *, rank() over(partition by region order by money desc)as ranking from Zu_Fang
rank row_number dense_rank区别：
rank： 1 1 1 4 排名  如果有相同名次 会占用下一个名词
dense_rank：1 1 1 2  如果有相同名词 不会占用下面的名词
row_number：1 2 3 4 不存在相同排名

总结：
同时具有分组和排序的功能，且不改变原来行数。
主要用来解决组内排名问题

六：常用例子

1 留存率问题
2 指定条件去重问题