本文已参与「新人创作礼」活动，一起开启掘金创作之路。
你还在玩手机吗?开始刷题!
关注我,每天和我一起"读书破万卷(Juan 3)"
1.where 和having
having是用来筛选组，where是用来筛选记录
通俗点讲：where搜索条件在分组操作之前应用，having搜索条件在进行分组操作之后应用
题目:编写一个SQL查询来报告 至少有5个学生 的所有班级。
select class
from Courses
group by class
having count(*) >=5
2.要用is null
MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。
select name from customer where referee_id<>2 or referee_id is null
is null 必须写出来,否则会作为unkonw而不输出
\
3.选择语句的多种不同的解法
1.UNION + NOT LIKE
SELECT employee_id ,salary AS bonus
FROM Employees
WHERE employee_id%2!=0 AND name NOT LIKE ('M%')
UNION
SELECT employee_id ,salary*0 AS bonus
FROM Employees
WHERE employee_id%2=0 OR name LIKE ('M%')
ORDER BY employee_id;
2.] IF +RIGHT +MOD
SELECT employee_id,
IF(MOD(employee_id,2)!=0 AND LEFT(name,1)!='M',salary,0) bonus
FROM Employees
ORDER BY employee_id
3.CASE+RIGHT +MOD
SELECT employee_id,
(CASE WHEN MOD(employee_id,2)!=0 AND LEFT(name,1)!='M' THEN salary
WHEN MOD(employee_id,2)=0 OR LEFT(name,1)='M' THEN 0END) bonus
FROM Employees
ORDER BY employee_id
\
4.面对不同的数出现不同的变化
case .. when .. else .. then
5.删除重复的数据
DELETE from person
where id not in (
SELECT min(id) as id
from person
group by Email
)
---->报错You can't specify target table 'person' for update in FROM clause
因为你不能直接从一个表中插入然后又删除,需要在外面做个虚拟表
DELETE from person
where id not in
(
SELECT id from
(SELECT min(id) as id
from person
group by Email)t
)
\
这样就对了
6.把第一个字符串变成大写,其他都是小写(改名字)
select
user_id,concat(upper(left(name,1)),lower(substr(name,2))) as name #left是取单个,substr是取从参数开始的很多
from Users
order by user_id
7.农产品排序
实现product在group_concat内的排序,以下是错误写法
select
sell_date,count(*) as num_sold,group_concat(product) as products
from
(
select * from Activities order by product desc
) t
group by sell_date
正确写法
select
sell_date,
**获取“不同的”产品数【count(distinct product)】**
count(distinct product) as num_sold,
**“不同的”【distinct product】产品按照字典排序【order by product】 & “,”分割【separator ','】**
group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date
order by sell_date;
\
8.查找类似开头的东西
select * from Patients
where conditions like 'DIAB1%' or conditions like '% DIAB1%'
9.查找两个表中没有都出现的元素
mysql没有full join
select employee_id from Employees where employee_id not in (select employee_id from Salaries) #别忘了在这里加上括号
union
select employee_id from Salaries where employee_id not in( select employee_id from Employees)
order by employee_id
另外的方法
10.行转列 union操作符合并的结果集，不会允许重复值，如果允许有重复值的话，使用UNION ALL.
输入：
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无法买到。
解法1:
select product_id,'store1' store ,store1 as price from Products where store1 is not null #注意上面的代名,引号的就是store1要写在表中,store是化名,有重复就用union all
union all
select product_id,'store2',store2 as price from Products where store2 is not null
union all
select product_id,'store3',store3 as price from Products where store3 is not null
\
解法2:
select product_id,lower(store) as store,price
from Products
unpivot
( price for store in(store1,store2,store3)) #寻找对应关系
10.做树，找到哪些是根哪些是叶
解法1：
select * from
(
select id,'Root' type from tree where p_id is null
union
\
select
b.id,'Inner'
from tree a left join tree b on a.p_id=b.id
where b.p_id is not null and a.p_id is not null and a.id is not null and b.id is not null
\
union
select
a.id,'Leaf'
from tree a left join tree b on a.id=b.p_id
where b.id is null and b.p_id is null and a.p_id is not null and a.id is not null
\
)t
order by t.id
\
解法2：
select
id,
case when p_id is null then 'Root'
when id not in (select p_id from tree where p_id ) then 'Leaf'
else 'Inner' end
as 'type'
from
tree
order by
id
11.得到第二大的薪水（小心重复），在只有一个长度的节点返回null
ifnull（a,b）--->如果a不是null，返回a，否则返回b
select ifnull((
select distinct salary
from Employee
order by salary desc limit 1 offset 1)
,null) SecondHighestSalary
