SQL语句对程序员的重要性在于：
数据操作基础：SQL是操作关系型数据库的标准工具，程序员通过SQL实现与数据库的交互，包括查询、增删改查等基本操作。
业务逻辑处理：很多应用程序的核心业务逻辑基于数据库的数据处理，SQL语句的设计直接决定了这些逻辑能否高效正确地执行。
性能影响显著：SQL查询效率直接影响到整个应用系统的响应速度和资源利用率。良好的SQL编写习惯和优化技巧可以极大地提高程序性能。
项目需求必备：几乎所有的软件开发项目都需要与数据库打交道，掌握SQL是现代程序员的基本功，也是完成项目任务的基础能力。
跨平台兼容：尽管不同的数据库系统在SQL的具体实现上可能有差异，但标准SQL语法具有广泛的适用性，使得程序员能够在不同环境下迁移和适应。
因此，无论是在日常开发维护、性能优化还是复杂业务场景下，编写高质量的SQL语句都是衡量一个程序员专业素养和技术实力的重要指标。
目录
SQL1 查询所有列
低效率写法：
select *
from user_profile正常写法：
select id, device_id, gender, age, university, province
from user_profileSQL2 查询多列
select device_id, gender, age, university
from user_profileSQL3 查询结果去重
select distinct university
from user_profileSQL4 查询结果限制返回行数
写法1：
select device_id
from user_profile
limit 0, 2写法2：
select device_id
from user_profile
where id <= 2SQL5 将查询后的列重新命名
select device_id as user_infos_example
from user_profile
limit 0, 2SQL6 查找学校是北大的学生信息
select device_id, university
from user_profile
where university = '北京大学'SQL7 查找年龄大于24岁的用户信息
select device_id, gender, age, university
from user_profile
where age > 24SQL8 查找某个年龄段的用户信息
写法1：
select device_id, gender, age
from user_profile
where age >= 20 and age <= 23写法2：
select device_id, gender, age
from user_profile
where age between 20 and 23SQL9 查找除复旦大学的用户信息
select device_id, gender, age, university
from user_profile
where university != '复旦大学'SQL10 用where过滤空值练习
select device_id, gender, age, university
from user_profile
where age is not nullSQL11 高级操作符练习(1)
select device_id, gender, age, university, gpa
from user_profile
where gender = 'male' and gpa > 3.5SQL12 高级操作符练习(2）
select device_id, gender, age, university, gpa
from user_profile
where university = '北京大学' or gpa > 3.7SQL13 Where in 和Not in
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
university in ("北京大学","复旦大学","山东大学")
SQL14 操作符混合运用
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
(gpa > 3.5 and university = '山东大学')
or (gpa > 3.8 and university = '复旦大学')
SQL15 查看学校名称中含北京的用户
select
device_id,
age,
university
from
user_profile
where
university like '%北京%'
SQL16 查找GPA最高值
select
round(max(gpa),1)
from
user_profile
where
university = '复旦大学'
SQL17 计算男生人数以及平均GPA
select count(*) as male_num, round(avg(gpa), 1) as avg_gpa
from user_profile
where gender = 'male'SQL18 分组计算练习题
select
gender,
university,
count(*) as user_num,
round(avg(active_days_within_30),1) as avg_active_day,
round(avg(question_cnt),1) as avg_question_cnt
from
user_profile
group by university, genderSQL19 分组过滤练习题
select
university,
round(avg(question_cnt), 1) as avg_question_cnt,
round(avg(answer_cnt), 1) as avg_answer_cnt
from
user_profile
group by
university
having
avg_question_cnt < 5
or avg_answer_cnt < 20
SQL20 分组排序练习题
select
university,
round(avg(question_cnt),4) as avg_question_cnt
from
user_profile
group by
university
order by avg_question_cnt ascSQL21 浙江大学用户题目回答情况
写法1：连接查询
select
up.device_id,
question_id,
result
from
question_practice_detail qpd,
user_profile up
where
qpd.device_id = up.device_id
and university = '浙江大学'
order by
question_id
写法2：子查询
select
device_id,
question_id,
result
from
question_practice_detail
where
device_id in (
select
device_id
from
user_profile
where
university = '浙江大学'
)
SQL22 统计每个学校的答过题的用户的平均答题数
写法1：
select
university,
count(question_id) / count(distinct up.device_id) as avg_answer_cnt
from
user_profile up,
question_practice_detail qpd
where
up.device_id = qpd.device_id
group by
university
order by
university写法2：
select
university,
count(question_id) / count(distinct up.device_id) as avg_answer_cnt
from
user_profile up inner join
question_practice_detail qpd
on up.device_id = qpd.device_id
group by
university
order by
university
SQL23 统计每个学校各难度的用户平均刷题数
select
university,
difficult_level,
count(qpd.question_id) / count(distinct up.device_id) as avg_answer_cnt
from
user_profile up,
question_practice_detail qpd,
question_detail qd
where
up.device_id = qpd.device_id
and qpd.question_id = qd.question_id
group by
university,
difficult_level
SQL24 统计每个用户的平均刷题数
select
university,
difficult_level,
count(difficult_level) / count(distinct up.device_id)
from
user_profile up
inner join question_practice_detail qpd on up.device_id = qpd.device_id
inner join question_detail qd on qpd.question_id = qd.question_id
where
university = '山东大学'
group by
difficult_level
SQL25 查找山东大学或者性别为男生的信息
union all:并集不去重 union：并集去重
select device_id, gender, age, gpa
from user_profile
where university = '山东大学'
union all
select device_id, gender, age, gpa
from user_profile
where gender = 'male' SQL26 计算25岁以上和以下的用户数量
select
case
when age >= 25 then '25岁及以上'
else '25岁以下'
end age_cut,
count(*) number
from
user_profile
group by
age_cut
SQL27 查看不同年龄段的用户明细
select
device_id,
gender,
case
when age < 20 then '20岁以下'
when (
age >= 20
and age <= 24
) then '20-24岁'
when age >= 25 then '25岁及以上'
else '其他'
end as age_cnt
from
user_profile
SQL28 计算用户8月每天的练题数量
select
DAY (date) as day,
count(question_id) as question_cnt
from
question_practice_detail qpd
where
YEAR (date) = '2021'
and MONTH (date) = '08'
group by
day
select
DAY (date) as day,
count(question_id) as question_cnt
from
question_practice_detail qpd
where
date like '2021-08%'
group by
day
SQL29 计算用户的平均次日留存率
略...
SQL30 统计每种性别的人数
select
substring_index (profile, ',', -1) as gender,
count(device_id) as number
from
user_submit
group by
gender
select
case
when profile like '%,male' then 'male'
when profile like '%,female' then 'female'
end as gender,
count(device_id) as number
from
user_submit
group by
gender
SQL31 提取博客URL中的用户名
select device_id, substring_index(blog_url,'/',-1) as user_name
from user_submit
SQL32 截取出年龄
select
substring_index (substring_index (profile, ',', 3), ',', -1) as age,
count(device_id) as number
from
user_submit
group by
age
SQL33 找出每个学校GPA最低的同学
select device_id, university, gpa
from user_profile u1
where gpa <= all (select u2.gpa from user_profile u2 where u1.university = u2.university )
order by universityselect
device_id,
university,
gpa
from
user_profile u1
where
gpa = (
select
min(u2.gpa)
from
user_profile u2
where
u1.university = u2.university
)
order by
university
select
device_id,
university,
gpa
from
user_profile u1
where
(gpa,university) in (
select
min(u2.gpa),
u2.university
from
user_profile u2
group by u2.university
)
order by
universitySQL34 统计复旦用户8月练题情况
select
up1.device_id,
university,
count(question_id) question_cnt,
count(if ((result = 'right'), 1, null)) right_question_cnt
from
question_practice_detail qpd
right outer join (
select
device_id,
university
from
user_profile
where
university = '复旦大学'
) up1 on up1.device_id = qpd.device_id
and month (date) = '08'
group by
up1.device_id
SQL35 浙大不同难度题目的正确率
select
difficult_level,
count(if ((result = 'right'), 1, null)) / count(*) as correct_rate
from
question_practice_detail qpd
inner join question_detail qd on qpd.question_id = qd.question_id
inner join user_profile up on up.device_id = qpd.device_id
where
university = '浙江大学'
group by
difficult_level
order by
correct_rate asc
SQL36 查找后排序
select device_id , age
from user_profile
order by age asc SQL37 查找后多列排序
select device_id, gpa, age
from user_profile
order by gpa, ageSQL38 查找后降序排列
select device_id, gpa, age
from user_profile
order by gpa desc, age descSQL39 21年8月份练题总数
select
count(distinct device_id) as did_cnt,
count(question_id) as question_cnt
from
question_practice_detail
where
month (date) = '08'
1788
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
