SQL39道常见题型

SQL1 查询所有列

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

select * from user_profile

结果：

SQL2 查询多列

还是上面那个输入，题目换成：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据。

select device_id, gender,age,university from user_profile

选择指定的列： SELECT 字段名 FROM 表名;

SQL3 查询结果去重

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

两种方式:

1.distinct 关键字

select DISTINCT university FROM user_profile;

select distinct university from user_profiledistinct去重，放在列的前面使用。

2.分组group by 来筛选出去重的结果

当你对某个列使用 GROUP BY 时，数据库会将该列中相同值的行分为一组。对于每一组，只返回一个记录。因此，在对特定列进行 GROUP BY 操作时，相当于对该列进行去重。

select university FROM user_profile group by university;

SQL4 查询结果限制返回行数

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

select device_id from  user_profile LIMIT 2;

其他用法：

取按特定顺序排序的前几条记录

SELECT * FROM user_profile ORDER BY registration_date DESC LIMIT 2;

选择中间的任意几行

SELECT * FROM table LIMIT count OFFSET offset;

• count：要返回的行数。
• offset：要跳过的行数。

SELECT * FROM table LIMIT 3 OFFSET 2;

• OFFSET 2：跳过前2行。
• LIMIT 3：返回接下来的3行。

.检索记录行 6-10

SELECT * FROM table LIMIT 5,5

检索记录行 11-last

SELECT * FROM table LIMIT 10,-1

SQL5 将查询后的列重新命名

SELECT device_id AS user_infos_example FROM user_profile LIMIT 2;

AS可以省略

SELECT device_id user_infos_example FROM user_profile LIMIT 2;

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

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

select device_id,university from user_profile where university='北京大学';

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

'%北京%' 的意思是匹配所有包含“北京”这个子字符串的字符串，不论它出现在字符串的开头、中间还是结尾。它使用 SQL 中的 LIKE 子句来查找包含特定子字符串的行。

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

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

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

严谨起见，可以加上 age is not null 的条件

select device_id,gender,age,university from user_profile where age is not null and age>24;

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

现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄.
1.between 在列值得某与某之间

select device_id,gender,age from user_profile where age between 20 and 23;

2.用and的来连接条件范围

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

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

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

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

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

如果你想对查询结果进行去重，确保每个学校和id等信息只出现一次，可以使用 DISTINCT 关键字来实现。DISTINCT 用于返回不同的唯一值。

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

SQL10 用where过滤空值练习

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

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 is not null and age <> "";

 返回所有 age 列值既不为空（NULL）也不等于空字符串 "" 的记录。

select device_id,gender,age,university from user_profile where age is not null and age <> 0;

SQL11 高级操作符练习(1) -AND 

现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研。

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

IN 操作符用于匹配多个可能的值，在这里只匹配 'male'，所以 gender = 'male' 和 gender IN ('male') 是等效的。 

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

SQL12 高级操作符练习(2）-OR

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

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

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

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='山东大学';

SQL14 操作符混合运用

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

select device_id,gender,age,university,gpa from user_profile where gpa>3.5 and university='山东大学' or gpa >3.8 and university='复旦大学';

AND 的优先级高于 OR。这意味着在没有括号的情况下，AND 运算会比 OR 运算先被计算。

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('%北京%');

LIKE 的用法

1. 百分号 (%):

   • 用法：用于匹配零个或多个字符。
   • 示例：
     • LIKE 'A%' 匹配以 'A' 开头的所有记录。
     • LIKE '%A' 匹配以 'A' 结尾的所有记录。
     • LIKE '%A%' 匹配包含 'A' 的所有记录。
     • LIKE 'A%B' 匹配以 'A' 开头，'B' 结尾的记录，中间可以有零个或多个字符。

2. 下划线 (_):

   • 用法：用于匹配一个单独的字符。
   • 示例：
     • LIKE 'A_' 匹配以 'A' 开头，后跟一个字符的记录。
     • LIKE '_A' 匹配以一个字符开头，后跟 'A' 的记录。
     • LIKE '_A_' 匹配一个字符，后跟 'A' 和一个字符的记录。

3. 转义字符:

   • 用法：用于在模式中匹配实际的百分号 (%) 或下划线 (_) 字符。这些字符通常被视为通配符，但可以使用转义字符来匹配它们。
   • 示例：
     • LIKE '100\%' ESCAPE '\' 匹配 '100%' 字符串，其中 \ 是转义字符。
     • LIKE 'A\_B' ESCAPE '\' 匹配 'A_B' 字符串，其中 \ 用来转义下划线。

SQL16 查找GPA最高值

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

因最高的gpa，max(gpa)求出最大值

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

因最高的gpa，进行order by gpa desc降序，取第一行的数据即可 

select gpa from user_profile where university='复旦大学' order by gpa desc limit 1;