数据格式
一：查询语言
结构：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 指定条件去重问题
网友评论