从零开始的MySQL生活
SQL查询大全 - 牛客SQL在线编程(1)条件查询
牛客练习地址:
https://www.nowcoder.com/ta/sql-quick-study
参考:
SQL语句查询🔗
SQL查询语句大全🔗(个人总结)
博主小声嘟囔:其实参考的各种大全并不是很齐全,也经常不适用有时候看不明白,最好的方法还是百度
- 查询指定列
SELECT device_id,gender,age,university FROM user_profile
- 查询结果去重:DISTINCT
SELECT DISTINCT university FROM user_profile
- SQL4 查询结果限制返回行数: LIMIT 2
SELECT device_id FROM user_profile LIMIT 2
- 将查询后的列重新命名: 查询列+空格+别名
SELECT device_id user_infors_example FROM user_profile
- 查找后排序: ORDER BY 排序列 ASC或DESC
SELECT device_id,age FROM user_profile ORDER BY age ASC
- 查找后多列排序: ORDER BY 排序列1 ASC或DESC,排序列2 ASC或DESC
SELECT device_id,gpa,age FROM user_profile ORDER BY gpa ASC,age ASC
-
2021年8月份所有练习过题目的总用户数和练习过题目的总次数(查询多列的统计值,某些去重某些不去重,时间范围为2021-08-xx):
统计总数:COUNT(DISTINCT去重 列名1) 结果列别名1,COUNT(列名2) 结果列别名2
规定时间:YEAR(date)=‘2021’ and MONTH(date)=‘08’
或:WHERE date>‘2021-07-31’ and date<‘2021-09-01’
SELECT COUNT(DISTINCT device_id) did_cnt,COUNT(question_id) question_cnt
FROM question_practice_detail
WHERE YEAR(date)='2021' and MONTH(date)='08'
- 查找某个年龄段的用户信息: BETWEEN 左端点 and 右端点 (between为闭区间)
SELECT device_id,gender,age FROM user_profile WHERE age BETWEEN 20 and 23
- 查找除复旦大学的用户信息(查询除了指定列为xx之外的记录): WHERE 列名!=‘xx’
SELECT device_id,gender,age,university FROM user_profile WHERE university!='复旦大学'
- 用where过滤空值: WHERE age<>‘null’
或:WHERE age is not NULL
或:WHERE age!=""
SELECT device_id,gender,age,university FROM user_profile WHERE age<>'null'