1、数据去重
高级且优雅的自连接
输入:
Person 表:
±—±-----------------+
| id | email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
±—±-----------------+
输出:
±—±-----------------+
| id | email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
±—±-----------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。
链接:https://leetcode.cn/problems/delete-duplicate-emails
DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id
2、列转行
输入:
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无法买到。
链接:https://leetcode.cn/problems/rearrange-products-table
select product_id, 'store1' store, store1 price from products where store1 is not null
union
select product_id, 'store2' store, store2 price from products where store2 is not null
union
select product_id, 'store3' store, store3 price from products where store3 is not null
3、行转列
输入:
Products table:
±-----------±-------±------+
| product_id | store | price |
±-----------±-------±------+
| 0 | store1 | 95 |
| 0 | store2 | 100 |
| 0 | store3 | 105 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
±-----------±-------±------+
输出:
| product_id | store1 | store2 | store3 |
±-----------±-------±-------±-------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
±-----------±-------±-------±-------+
SELECT product_id,
SUM(IF(store = 'store1', price, NULL)) 'store1',
SUM(IF(store = 'store2', price, NULL)) 'store2',
SUM(IF(store = 'store3', price, NULL)) 'store3'
FROM
Products1
GROUP BY product_id ;
4、对日期的一些处理(函数)
1、在一个时间上加上一个时间
例如:在‘2017-10-10’加上10天
select date_add("2017-10-10", interval 10 day);
->2017-10-20
在‘2017-10-10 15:25:18’加上15分钟
select date_add("2017-10-10 15:25:18", interval 15 minute);
->2017-10-10 15:40:18
2、两日期之间的差值
例如:2001-01-01与2001-02-02相差多少天
select datediff('2001-01-01','2001-02-02');
->-32
#说明:这相当于前一个日期减去后一个日期,可以为负也可以为正
5、模糊查询,匹配查询
经常在各个博客上看到说模糊查询会使索引失效,类似 like ‘关键词%’ 不会失效,但是like ‘%关键词%’ 会失效。然后就有使用全文检索的说法,但是全文检索只支持myisam、并且不支持联表查询。于是对于这种需求应该怎么做才能走索引,或者说怎么做才能使查询更快?
explain SELECT * FROM user where username like '%ab%'
explain select * from user where instr(username, 'ab')
explain select * from user where locate('ab', username)
可以看到使用上面三个sql执行结果分析不同在于filtered,对于这个列的解释为越大越好。所以可以得出结论,使用函数比使用like + %的性能 要好,至于走不走索引就不得而知了