1.日期差
DATEDIFF(a.recordDate,b.recordDate) a-b
或
TIMESTAMPDIFF
TIMESTAMPDIFF能干什么，可以计算相差天数、小时、分钟和秒，相比于datediff函数要灵活很多。
格式是时间小的前，时间大的放在后面。 计算相差天数：
select w1.Id
from Weather as w1, Weather as w2
where TIMESTAMPDIFF(DAY, w2.RecordDate, w1.RecordDate) = 1
AND w1.Temperature > w2.Temperature
统计不重复数据个数
count(distinct user_id)
2.条件语句
IF(条件表达式,值1,值2)
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
例
UPDATE Salary SET sex =
CASE sex
WHEN 'm' THEN 'f'
WHEN 'f' THEN 'm'
ELSE sex END
3.IFNULL.
IFNULL(b.num, 0)
如果b.num为null，返回0，否则返回b.null自身的值
4.group_concat()
SELECT
dept_id,
GROUP_CONCAT(name ORDER BY age DESC SEPARATOR '*') -- 分组中的name中的多行数据将按照age降序进行连接，分隔符为 *
FROM employee2
GROUP BY dept_id;
得到
1 | 小肖*小玉*张山*小张*李四*小肖
2 | 小东*小肖*小胡*王武*小林*猪小屁
3 | 小非
4 | 晓飞
例
select sell_date,
count(distinct product) as num_sold,
GROUP_CONCAT( distinct product ORDER BY product separator ',') as products
注意若需要合并的数据有重复 要加 distinct
5.like
写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1
select *
from Patients
where conditions like 'DIAB1%' or conditions LIKE '% DIAB1%'
like模糊查询，支持%和下划线匹配，%匹配多个字符，_下划线：任意一个字符
示例：
1、查询名字中含有张的学生信息
select * from student where sname like ‘%张%’；
2、查询名字以张开头的学生信息
select * from student where sname like ‘张%’；
3、查询名字以人结尾的学生信息
select * from student where sname like ‘%人’；
4、查询名字中第二个字为心的学生信息
select * from student where sname like ‘_心%’；
5、查询名字中第三个字为心的学生信息
select * from student where sname like ‘__心%’；
因为下划线在sql中有特殊含义，所以当查询姓名中有下划线的学生信息时需要转义
示例：
select * from student where sname like ‘%_%’;
6. 顺序问题
一般，WHERE在前，GROUP BY在后，即先进行筛选，然后进行分组；
HAVING只能跟在GROUP BY之后，对分组后的聚合结果进行筛选；
HAVING的前提是分组；WHERE在最前，最先对原始数据进行一遍筛选；
WHERE的条件里只能用已有的列进行条件判断，不允许使用聚合函数。
HAVING之后可以允许使用聚合函数；
聚合函数包括count(),sum(),avg(),max(),min()
SELECT A.name,COUNT(order_number) AS count_sum
FROM orders A, customer B
WHERE A.coustomer_id=B.id
GROUP BY customer_id
HAVING COUNT(order_number)>5
ORDER BY count_sum DESC
LIMIT 5;
7. 字符串相关操作
length(a)-------获取字符串a的长度
concat(a,b..)---组合a,b..等参数
upper(a)-------将字符串a转成大写
lower(a)-------将字符串a转成小写
----------------取左边count个字符，比如left(a,2), 则取左边两个字符
left(a, count)--取左边count个字符，比如left(a,2), 则取左边两个字符
right(a,n) -----取右边长度为n的字符串，
比如right(a, length(a) - 2)，则取从左边第三个字符串开始的字符串
substring(a, start)-------------截取从start开始的右边的字符串
substring(a, start, length)-----截取从start开始长度为length的字符串
注意
substring(a, 1, 1) 等效于 left(a, 1)
8. UNION运算符
UNION运算符用于组合两个或更多SELECT语句的结果集，而不返回任何重复的行。
1.UNION中的每个SELECT语句必须具有相同的列数
2.这些列也必须具有相似的数据类型
3.每个SELECT语句中的列也必须以相同的顺序排列
4.每个SELECT语句必须有相同数目的列表达式
5.但是每个SELECT语句的长度不必相同
注释：默认情况下，UNION运算符选择一个不同的值。如果允许重复值，请使用UNION ALL。
-----例：
select name,population,area from World
where population > 25000000 or area > 3000000
-----等效于
select name,population,area from World
where population > 25000000
union
select name,population,area from World
where area > 3000000
----------例如：----------
输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store | price |
+------------+--------+-------+
| 0 | store1 | 95 |
| 0 | store2 | 100 |
| 0 | store3 | 105 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
+------------+--------+-------+
解释：
产品0在store1，store2,store3的价格分别为95,100,105。
产品1在store1，store3的价格分别为70,80。在store2无法买到。
select product_id, 'store1' as store, store1 as price
from Products
where store1 is not null
union
select product_id, 'store2' as store, store2 as price
from Products
where store2 is not null
union
select product_id, 'store3' as store, store3 as price
from Products
where store3 is not null
9. LIMIT
1、当 limit后面跟一个参数的时候，该参数表示要取的数据的数量
例如 select* from user limit 3 表示直接取前三条数据
2、当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如
select * from user limit 1,3;
就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
3、当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。
例如select * from user limit 3 offset 1;表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
10. 排名
-
rank() over
作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7 -
dense_rank() over
作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6 -
row_number() over
作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。
说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6
使用小提示
dense_rank() over 后面跟排序的依据的列，下面是用了一个排序好的列(order by score desc)。
注意：如果select中有一列是用rank()这类函数，其他的列都会按着他这列规定好的顺序排。
select score,
dense_rank() over (order by score desc) as 'rank'
#这个rank之所以要加引号，因为rank本身是个函数，直接写rank会报错
from scores;
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
11. 前后函数：LAG(expr,n)、LEAD(expr,n)
用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值（以当前行为原点）
--寻找连续出现三行的值
select distinct num as ConsecutiveNums
from (select num,
lag(num, 1, null) over (order by id) lag_num, //取前一行的num
lead(num, 1, null) over (order by id) lead_num//取后一行的num
from logs) l
where l.Num = l.lag_num // 条件：三行值相同
and l.Num = l.lead_num
407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
