数据库练习题(牛客网sql入门篇部分)

数据库练习题 SQL1:查询结果去重

去重关键字 DISTINCT
select DISTINCT university
from user_profile;

group by 是分组查询
SELECt university 
FROM user_profile GROUP BY user_profile;

SQL2:查询结果限制返回行数

select device_id
from  user_profile 
limit 0,2

select device_id 
from user_profile 
limit 2;

select device_id 
from user_profile 
limit 2 offset 0;

select device_id 
from user_profile 
where id in(1,2);

select device_id 
from user_profile 
where id <=2;
或where id <3;

select device_id 
from user_profile 
where id=1 or id=2;

SQL3:将查询后的列重新命名

select  device_id as user_infos_example 
from  user_profile 
limit 2

select  device_id as user_infos_example 
from  user_profile 
limit 0,2

select  device_id as user_infos_example 
from  user_profile 
where id in(1,2);

SQL4:查找学校是北大的学生信息

SELECt device_id,university 
FROM user_profile 
WHERe university LIKE '北京大学%'

SELECt device_id,university
FROM user_profile
WHERe university="北京大学"

SQL5:查找年龄大于24岁的用户信息

select device_id,gender,age ,university
from user_profile
where age>24

SQL6:从不订购的客户

select c.Name as Customers 
from Customers c 
where c.Id not in (select distinct o.CustomerId from Orders o);

SQL7查找某个年龄段的用户信息

select 	device_id,gender,age
from user_profile
where age >=20 and age <=23;

select 	device_id,gender,age
from user_profile
where age between 20 and 24

SELECt device_id,gender,age 
from user_profile 
where age between 20 and 23

SQL8 查找除复旦大学的用户信息

select device_id,gender,age,university
from user_profile
where university not in ('复旦大学');

select device_id,gender,age,university
from user_profile
where university != '复旦大学';

select device_id,gender,age,university
from user_profile
where university <> '复旦大学';

SQL10 用where过滤空值练习

过滤空值的三种方法：

(1) Where 列名 is not null

(2) Where 列名 != ‘null’

(3) Where 列名 <> ‘null’

SELECt device_id,gender,age,university 
FROM user_profile 
where age is not NULL

SELECt device_id,gender,age,university 
FROM user_profile 
where age <>'null' 正则

SELECt device_id,gender,age,university 
FROM user_profile 
where age !='null' 比较

SQL11 高级操作符练习(1)

现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。

SELECt device_id, gender, age, university,gpa
FROM user_profile
WHERe gpa >3.5 AND gender in('male')

SELECt device_id,gender,age,university,gpa 
FROM user_profile
WHERe gpa>3.5 AND gender IN(SELECt gender FROM user_profile WHERe gender='male')

SQL12 高级操作符练习(2）

题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）

select device_id,gender,age,university,gpa 
from user_profile 
where gpa>3.7 or university='北京大学';

select device_id,gender,age,university,gpa 
from user_profile 
where university in('北京大学') || gpa >3.7

SQL13 Where in 和Not in

题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

SELECt device_id,gender,age,university,gpa
FROM user_profile
WHERe university IN ("北京大学","复旦大学","山东大学")

select device_id,gender,age,university,gpa
from user_profile
WHERe university="北京大学" or university="复旦大学" or university="山东大学"

select device_id,gender,age,university,gpa
from user_profile
where find_in_set(university,'北京大学,复旦大学,山东大学');

SQL14 操作符混合运用

题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

SELECt device_id,gender,age,university,gpa
FROM user_profile
user_profile where gpa > 3.8 and university = '复旦大学' 
UNIOn
SELECt device_id, gender, age, university,gpa 
from user_profile 
where gpa > 3.5 and university = '山东大学'

SELECt  device_id, gender, age, university,gpa 

from user_profile 

where (gpa > 3.8 and university = '复旦大学') or (gpa > 3.5 and university = '山东大学')

#法1：使用 OR 和 AND 混合运算
SELECt device_id,gender,age,university,gpa 
FROM user_profile
WHERe gpa>3.5 AND university='山东大学' OR gpa>3.8 AND university='复旦大学'

#法2：使用union all（需要排序）
SELECt device_id,gender,age,university,gpa 
FROM user_profile
WHERe gpa>3.5 AND university='山东大学'
UNIOn ALL
SELECt device_id,gender,age,university,gpa 
FROM user_profile
WHERe gpa>3.8 AND university='复旦大学'
ORDER BY device_id ASC

SQL15 查看学校名称中含北京的用户

题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。

select device_id,age,university
from user_profile
where university LIKE '%北京%';

SELECt device_id,age,university
FROM user_profile 
WHERe university regexp "北京";

LIKE 和 REGEXP之间的重要差别

-- LIKE 匹配整个列，如果被匹配的文本在列值中出现，LIKE 将不会找到它，相应的行也不会被返回（除非使用通配符）。而 REGEXP 在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP 将会找到它，相应的行将被返回，并且 REGEXP 能匹配整个列值（与 LIKE 相同的作用）。

SQL16 查找GPA最高值

题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据

select max(gpa)
from user_profile
where university = '复旦大学'

select round(max(gpa),1)
from user_profile
where university = '复旦大学'

SELECt gpa
FROM user_profile
WHERe university='复旦大学' 
ORDER BY gpa DESC
LIMIT 0,1

select gpa
from(select gpa,row_number()over(partition by university order by gpa desc) as ranking
from user_profile
where university = '复旦大学') as t
where t.ranking = 1;

SELECt gpa
FROM user_profile
WHERe university ="复旦大学"
ORDER BY gpa DESC
LIMIT 1;

SQL17 计算男生人数以及平均GPA

select count(gender) as male_num,
Round(avg(gpa),1)as avg_gpa
from user_profile
where gender='male';

select
  count(gender)  male_num,
  round(avg(gpa),1)  avg_gpa
from user_profile GROUP BY gender having gender ='male'

select 
    count(id) male_num,avg(gpa) avg_gpa 
from 
    user_profile 
group by 
    gender 
having 
    gender='male'

SQL18 分组计算练习题

现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

select gender,university,
       count(device_id)as user_num,
       avg(active_days_within_30)as avg_active_days,
       avg(question_cnt)as avg_question_cnt
       from user_profile
       group by gender,university

select l.gender,l.university,
      count(gender) as user_num ,
      avg(l.active_days_within_30) as avg_active_day,
      avg(l.question_cnt) as avg_question_cnt
      FROM user_profile l 
      group by l.university ,l.gender

select gender,university,
       count(gender) as user_num,
       ROUND(avg(active_days_within_30),1),
        ROUND(avg(question_cnt),1)
from user_profile
group by gender,universit

SQL19 分组过滤练习题

取出平均发贴数低于5的学校或平均回帖数小于20的学校

聚合函数结果作为筛选条件时，不能用where，而是用having语法

select  university,
    avg(question_cnt) as avg_question_cnt,
    avg(answer_cnt) as avg_answer_cnt
    
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20

# select查询字段中有非聚合函数和聚合函数
# 一定是按照非聚合函数字段进行分组
 
# 按照执行顺序，是from -> where -> group by -> having -> select -> order by -> limit
# 理论上来说，select中的聚合函数字段无法在having中使用，
# 但是MySQL会做自动优化的替换，无论定义在哪，聚合的操作执行一次，having里和select里都可以使用

SQL20 分组排序练习题

现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。

需要单独命名avg_question
select university,
avg(question_cnt) as avg_question_num
from user_profile
group by university
order by avg_question_num

不需要单独命名
select university,avg(question_cnt) 
from user_profile
group by university 
order by avg(question_cnt)

操作能运行成功的原因是 order by后面可以加聚合函数。但要注意，group by后面不可。

有三类后面可以加聚合函数

1、select

2、order by

3、having

由于SQL语句执行顺序如下：
FROM - ON - JOIN - WHERe 
- GROUP BY - WITH - HAVINg - SELECT - DISTINCT - ORDER BY - LIMIT

数据库练习题(牛客网sql入门篇部分)

Java相关栏目本月热门文章