Sql入门练习题

1、基础查询

查询所有列

现在运营想要查看用户信息表user_profile中所有的数据，请你取出相应结果。

select * from user_profile;

查询多列

现在运营同学想要获取用户的设备device_id对应的性别gender、年龄age和学校university的数据，请你从用户信息表中user_profile取出相应数据。

select 
	device_id,
	gender,
	age,
	university 
from 
	user_profile;

查询结果去重

现在运营需要查看用户来自于哪些学校university，请从用户信息表user_profile中取出学校的去重数据。

//使用group by按照university进行分组
select 
	university 
from 
	user_profile 
group by university;

//使用distinct(关键词distinct用于返回唯一不同的值)
select distinct 
	university 
from 
	user_profile;

查询结果限制返回行数

现在运营只需要查看前2个用户明细设备ID数据device_id，请你从用户信息表 user_profile 中取出相应结果。

//效率高
select 
	device_id 
from 
	user_profile limit 0,2;

//效率中等
select 
	device_id 
from 
	user_profile limit 2 offset 0;

//效率低
select 
	device_id 
from 
	user_profile limit 2;

将查询后的列重新命名

现在你需要查看前2个用户明细设备ID数据device_id，并将列名改为 ‘user_infos_example’,，请你从用户信息表user_profile取出相应结果。

select 
	device_id as "user_infors_example" 
from 
	user_profile limit 0,2;

//1.as 可省略，索引为0可省略
//2.别名加不加引号（单双）都可
    //加引号：别名就是引号内的内容。
    //不加引号：别名如果为小写，会解析为大写。 

2、条件查询

查找后排序

现在运营想要取出用户信息表user_profile中的设备device_id和用户年龄age，请取出相应数据，并按照年龄升序排序。

select 
	device_id,
	age 
from 
	user_profile 
order by age asc; 
//先查找，后排序
//结尾加 desc 是降序,结尾加 asc 是升序，不加默认升序。

查找后多列排序

现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄age升序排序输出，请取出相应数据。

select
    device_id,
    gpa,
    age
from 
	user_profile
order by gpa,age;
//先查找，后排序
//结尾加 desc 是降序,结尾加 asc 是升序，不加默认升序。

查找后降序排列

现在运营想要取出用户信息表user_profile中对应的数据，并先按照gpa、年龄age降序排序输出，请取出相应数据。

select
    device_id,
    gpa,
    age
from 
	user_profile
order by gpa desc,age desc;

//先查找，后排序
//结尾加 desc 是降序,结尾加 asc 是升序，不加默认升序。

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

现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表user_profile中取出满足条件的数据，结果返回设备device_id和学校university。

SELECT 
	device_id,
	university 
from 
	user_profile 
where 
	university='北京大学';
//或者使用条件 where university like '%北京%';

//select语句中使用的索引覆盖所有需要查询的字段不适合
select 
	device_id,
	university 
from 
	user_profile 
where 
	university = "北京大学" 
	and device_id = user_profile.device_id;
//这个题目不适合



//覆盖索引的原理：就是查询字段在 二级索引中全部找到，不需要回表查询

//覆盖索引只是特定于具体select语录而言的联合索引。也就是说一个联合索引对于某个select语句，通过索引可以直接获取查询结果，而不再需要回表查询啦，就称该联合索引覆盖了这条select语句。

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

现在运营想要针对24岁以上的用户开展分析，请你从用户信息表user_profile取出满足条件的设备device_id、性别、年龄age、学校university。

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

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

现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备device_id、性别gender、年龄age。

//用and的来连接条件范围
 select 
 	device_id, 
 	gender, 
	age
 from 
 	user_profile 
 where 
 	age>=20 and age <=23;

//between 在列值某与某之间
 select 
 	device_id, 
 	gender, 
	age
 from 
 	user_profile 
 where 
 	age between 20 and 23;

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

现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据。

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

用where过滤空值练习

现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备device_id，性别gender，年龄age，学校university的信息。

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 !='';

高级操作符练习1

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

select 
	device_id,
	gender,
	age,
	university,
	gpa 
from 
	user_profile 
where 
	gender='male' and gpa>3.5;

高级操作符练习2

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

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

Where_in和Not_in

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

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

操作符混合运用

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

//虽然短，但是执行用时长
select
    device_id,
    gender,
    age,
    university,
    gpa
from
    user_profile
where
    university = '山东大学'
    and gpa > 3.5
    or university = '复旦大学'
    and gpa > 3.8

//子查询的方式 --运行时间短
select
    device_id,
    gender,
    age,
    university,
    gpa
from
    user_profile
where
    device_id in (
        select
            device_id
        from
            user_profile
        where
            gpa > 3.5
            and university = '山东大学'
    )
    or device_id in (
        select
            device_id
        from
            user_profile
        where
            gpa > 3.8
            and university = '复旦大学'
    )

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

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

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



//字符匹配
// 1、_ ：匹配一个字符 例如：查找姓张且名字为三个字的学生信息 //select * from student where name like '张__'

//2、%：匹配0个或多个字符串 例如：查找姓张的学生的信息 
//select * from student where name like '张%

//3、[]匹配框中的任意一个字符 例如查找姓李、张、宋的学生信息 //select * from student where name like '[张李宋]%'

//4、不匹配[]中的字符 例如找出除姓张、唐之外的学生信息 
//select * from student where name like '[^张唐]%'

//5、查询姓名中第二个字为大或小的学生信息 
//seletc * from student where name like '_[小大]%'

3、高级查询

查找GPA最高值

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

// 选择最大值作为gpa
select
    max(gpa) as gpa
from
    user_profile
where
    university = '复旦大学';

 
// 先选取，降序，取第一个
select
    gpa
from
    user_profile
where
    university = '复旦大学'
order by
    gpa desc
limit
    1

计算男生人数以及平均GPA

现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。

//限定条件为 男性用户
//有多少人，明显是计数，count函数
//平均gpa，求平均值用avg函数
//表头重命名，用as语法
//浮点数的平均值可能小数点位数很多，按照示例保存一位小数，用round函数
select
    count(gender) as male_num,
    round(avg(gpa), 1) as avg_gpa
from
    user_profile
where
    gender = "male";

分组计算练习题

现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、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

分组过滤练习题

现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

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
    university,
    avg(question_cnt) as avg_question_cnt
from
    user_profile
group by
    university
order by
    avg_question_cnt