学习计划-SQL 入门
第 1 天 选择
select name,population,area
from World
where area>=3000000 or population >=25000000
------------------------
select name,population,area
from World
where area>=3000000
UNION
select name,population,area
from World
where population >=25000000select product_id
from Products
where low_fats='Y' and recyclable ='Y'- = 或 ！= 只能判断基本数据类型
- is 关键字只能判断null
- ifnull(referee_id,0)!=2
select name
from customer
where referee_id!=2 or referee_id is null
--------------------------
select name
from customer
where referee_id<>2 or referee_id is null
--------------------------
#先把null转为0
select name
from customer
where ifnull(referee_id,0)!=2;select customers.name as customers
from customers
where id not in
(select customers.id
from customers,orders
where customers.id=orders.customerid)
--------------------------
select Name as Customers
from Customers
where Id not in(select CustomerId
from Orders)
第 2 天 排序 & 修改
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_idUpdate 语句用于修改表中的数据。
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
| 1 2 3 |
|
- case sex when 'm' then 'f' else 'm' end
update salary
set sex =
(
case sex when 'm' then 'f' else 'm' end
);DELETE 语句用于删除表中的行。
DELETE FROM 表名称 WHERE 列名称 = 值
delete a
from Person a , Person b
where a.id > b.id and a.email = b.email第 3 天 字符串处理函数/正则
CONCAT() 函数
CONCAT 可以将多个字符串拼接在一起。
LEFT(str, length) 函数
从左开始截取字符串，length 是截取的长度。
UPPER(str) 与 LOWER(str)
UPPER(str) 将字符串中所有字符转为大写
LOWER(str) 将字符串中所有字符转为小写
SUBSTRING(str, begin, end)
截取字符串，end 不写默认为空。
SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。
concat(upper(left(name,1)),lower(substr(name,2)))select user_id,
concat(upper(left(name,1)),lower(substr(name,2))) as name
from Users
order by user_id GROUP_CONCAT( [DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC] [separator ‘分隔符’] )
select sell_date,
count(distinct(product)) as num_sold,
group_concat( distinct product separator ',') as products
from Activities
group by sell_date
order by sell_dateselect *
from Patients
where conditions like "DIAB1%" or conditions like "% DIAB1%"第 4 天 组合查询 & 指定选取
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
------------------
select e.employee_id
from Employees e
left join Salaries s
on e.employee_id=s.employee_id
where salary is null
union
select s.employee_id
from Employees e
right join Salaries s
on e.employee_id=s.employee_id
where name is null
order by employee_id- 行转列用groupby+sumif，列转行用union all
- store1 as price from Products where store1 is not null
select product_id,'store1' store, store1 as price from Products where store1 is not null
union all
select product_id,'store2' store, store2 as price from Products where store2 is not null
union all
select product_id,'store3' store, store3 as price from Products where store3 is not null
select id,
case
when id = (select id from tree where p_id is null) then 'Root'
when id in (select p_id from tree) then 'Inner'
else 'Leaf'
end as Type
from tree
order by id
--------------------------
# 根节点：如果这个节点是整棵树的根，即没有父节点。
select id, 'Root' as type from tree where p_id is null
union all
# 内部节点：如果这个节点既不是叶子节点也不是根节点。
select id, 'Inner' as type
from tree
where p_id is not null and
id in (select p_id from tree where p_id is not null)
union all
# 叶子节点：如果这个节点没有任何孩子节点。
select id, 'Leaf' as type
from tree
where p_id is not null and
id not in (select p_id from tree where p_id is not null)
order by id
select ifnull((select distinct salary
from Employee
order by salary desc
limit 1 offset 1) ,null)
as SecondHighestSalary
----------------------
select ifnull((select distinct Salary
from Employee
order by Salary desc
limit 1,1),null)
as SecondHighestSalary
----------------------
select b.salary as SecondHighestSalary
from Employee a
left join Employee b
on a.salary>b.salary
order by a.salary desc,b.salary desc
limit 1第 5 天 合并
//A inner join B 取交集。
//A left join B 取 A 全部，B 没有对应的值为 null。
//A right join B 取 B 全部， A 没有对应的值为 null。
//A full outer join B 取并集，彼此没有对应的值为 null。
对应条件在 on 后面填写
select firstName,lastName,city,state
from Person p
left join Address a
on p.personId = a.personId select customer_id,count(customer_id) as count_no_trans
from Visits
where visit_id not in (select visit_id from Transactions)
group by customer_idselect distinct author_id as id
from Views
where author_id=viewer_id
order by id第 6 天 合并
DATEDIFF(expr1,expr2) 表示为从一个日期到另一个日期的天数。expr1 并且expr2是日期或日期和时间表达式。计算中仅使用值的日期部分。expr1 − expr2
#截至 2019-07-27（含）的 30 天内
datediff('2019-07-27',activity_date)<30
activity_date BETWEEN '2019-06-28' AND '2019-07-27'select b.id as id
from Weather a,Weather b
where datediff(b.recordDate, a.recordDate) = 1 and b.Temperature>a.Temperatureselect name
from SalesPerson
where sales_id not in (select sales_id
from Company,Orders
where Orders.com_id = Company.com_id
and Company.name ='RED')
-------------------------------------
select name
from SalesPerson
where sales_id not in (select sales_id
from Orders
where com_id =(select com_id
from Company
where name='RED'))第 7 天 计算函数
select activity_date as day,count(distinct user_id) as active_users
from Activity
where activity_date between '2019-06-28' and '2019-07-27'
group by activity_date
-----------------------------
#注意datediff(expr1,expr2)的顺序，为expr1 − expr2
#数据中可能还有大于 7.27 号的数据，第十三组测试样例就有 2019-8-25 的数据，所以还要保证日期差大于等于0
select activity_date as day,count(distinct user_id) as active_users
from Activity
where datediff('2019-07-27',activity_date)<30 and datediff('2019-07-27',activity_date)>=0
group by activity_dateselect date_id,make_name,count(distinct lead_id) as unique_leads,count(distinct partner_id) as unique_partners
from DailySales
group by date_id, make_nameselect user_id,count(distinct follower_id) as followers_count
from Followers
group by user_id
order by user_id第 8 天 计算函数
select customer_number
from Orders
group by customer_number
order by count(order_number) desc
limit 1select player_id,min(event_date) as first_login
from Activity
group by player_idselect user_id,max(time_stamp) as last_stamp
from Logins
where year(time_stamp)=2020
group by user_id
-------------------------------
select user_id,max(time_stamp) as last_stamp
from Logins
where time_stamp between'2020-01-01 00:00:00' and '2020-12-31 23:59:59'
group by user_idselect event_day as day,emp_id,sum(out_time-in_time) as total_time
from Employees
group by emp_id,event_day第 9 天 控制流
select stock_name,
sum(case when operation='buy' then -price
else price end ) as 'capital_gain_loss'
from Stocks
group by stock_nameselect name,sum(ifnull(distance,0)) as travelled_distance
from Users
left join Rides
on Rides.user_id=Users.id
group by user_id
order by travelled_distance desc,name select u.user_id as buyer_id ,u.join_date,ifnull(num,0) as orders_in_2019
from Users u
left join (select buyer_id,count(buyer_id) as num
from Orders
where year(order_date)=2019
group by buyer_id) as b
on u. user_id=b.buyer_id第 10 天 过滤
select email as Email
from Person
group by email
having count(id)>1select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(timestamp)>=3select name,sum(amount) as balance
from Transactions t
left join Users u
on u.account = t.account
group by u.account
having balance >10000
select product_id,product_name
from Product
where product_id in(select product_id
from Sales
where sale_date between '2019-01-01' and '2019-03-31')
and product_id not in(select product_id
from Sales
where sale_date not between '2019-01-01' and '2019-03-31')
-----------------------------------
select p.product_id, product_name
from product p
inner join sales s
on p.product_id = s.product_id
group by product_id
having min(sale_date) >= '2019-01-01' and max(sale_date) <= '2019-03-31';难度顺序：简单
181 577 596
select e1.name as Employee
from Employee e1, Employee e2
where e1.managerId = e2.id and e1.salary > e2.salary
---------------------------------
select a.name as Employee
from Employee a
left join Employee b
on a.managerId = b.id
where a.salary > b.salary
select e.name, b.bonus
from Employee e
left join Bonus b
on e.empId=b.empId
having bonus < 1000 or bonus is nullselect class
from Courses
group by class
having count(distinct student)>=5610 619 620
select x, y, z,
(case when x + y > z and x + z > y and z + y > x then 'Yes' else 'No' end) as triangle
from Triangleselect max(b.num) as num
from (select num
from MyNumbers
group by num
having count(num)=1) as b
select *
from cinema
where description != 'boring' and id%2!=0
order by rating desc1068 1075 1179
select product_name,year,price
from Sales s
left join Product p
on p.product_id=s.product_idselect project_id, round(avg(experience_years),2) as average_years
from Project p
left join Employee e
on p.employee_id = e.employee_id
group by project_id
---------------------
select project_id,round(sum(experience_years)/count(experience_years),2) as average_years
from Project p,Employee e
where p.employee_id =e.employee_id
group by project_id
https://leetcode.cn/problems/reformat-department-table/solution/group-byben-zhi-lun-by-loverxp-7mgy/select id,
sum(case month when 'Jan' then revenue end) as Jan_Revenue,
sum(case month when 'Feb' then revenue end) as Feb_Revenue,
sum(case month when 'Mar' then revenue end) as Mar_Revenue,
sum(case month when 'Apr' then revenue end) as Apr_Revenue,
sum(case month when 'May' then revenue end) as May_Revenue,
sum(case month when 'Jun' then revenue end) as Jun_Revenue,
sum(case month when 'Jul' then revenue end) as Jul_Revenue,
sum(case month when 'Aug' then revenue end) as Aug_Revenue,
sum(case month when 'Sep' then revenue end) as Sep_Revenue,
sum(case month when 'Oct' then revenue end) as Oct_Revenue,
sum(case month when 'Nov' then revenue end) as Nov_Revenue,
sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id1211 1251 1280
# rating<3用于判断，小于3就是返回1，否则返回0。avg求平均就跟把1和0求和再除以行数是一样的结果。
select query_name,
round(avg(rating/position),2) as quality,
round(avg(rating < 3) * 100,2) as poor_query_percentage
from Queries
group by query_name
----------------------------------
select query_name,
round(avg(rating/position),2) as quality,
round(sum(if((rating < 3),1,0))/count(*)*100,2) as poor_query_percentage
from Queries
group by query_name
-----------------------------
select query_name,
round(sum(rating/position)/count(result), 2) as quality,
round(sum(case when rating<3 then 1 else 0 end)/count(rating)*100, 2) as poor_query_percentage
from Queries
group by query_nameselect p.product_id as product_id,
round(sum(price*units)/sum(units),2) as average_price
from Prices p
left join UnitsSold u
on u.product_id=p.product_id and purchase_date between start_date and end_date
group by p.product_idselect st.student_id,st.student_name,su.subject_name,count(e.subject_name ) as attended_exams
from Students st
join Subjects su
left join Examinations e
on st.student_id=e.student_id and e.subject_name =su. subject_name
group by st.student_id,su.subject_name
order by st.student_id,su.subject_name
-----------------------
select temp1.student_id, temp1.student_name, temp1.subject_name, ifnull(attended_exams,0) as attended_exams
from (select student_id,student_name,subject_name
from Students st,Subjects su) as temp1
left join (select student_id,subject_name,count(student_id) as attended_exams
from Examinations group by student_id,subject_name) as temp2
on temp1.student_id=temp2.student_id and temp1.subject_name=temp2.subject_name
group by student_id,subject_name
order by student_id,subject_name
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
