牛客题霸——SQL入门篇

牛客题霸——SQL入门篇

基础查询

基础查询

• SQL1 查询所有列
  select * from user_profile;
• SQL2 查询多列
  select device_id, gender, age, university from user_profile;

简单处理查询结果

• SQL3 查询结果去重
  用 distinct 关键字可以去掉结果中的重复行。
  distinct关键字放在 select 词的后边、目标列名序列的前边。
  select distinct university from user_profile;
• SQL4 查询结果限制返回行数
  limit 子句可以被用于强制 select 语句返回指定的记录数。
  limit 接受一个或两个数字参数。参数必须是一个整数常量。
  如果只给定一个参数，它表示返回最大的记录行数目。
  如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
  为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1。
  初始记录行的偏移量是 0。
  只需要查看前2个用户明细设备ID数据：
  select device_id from user_profile limit 2;
• SQL5 将查询后的列重新命名
  查看前2个用户明细设备ID数据，并将列名改为 ‘user_infos_example’：
  select device_id (as) user_infos_example from user_profile limit 2;

条件查询

基础排序

• SQL36 查找后排序
  select device_id, age from user_profile order by age asc;
  asc升序（默认）、desc降序
• SQL37 查找后多列排序
  select device_id, gpa, age from user_profile order by gpa, age;
• SQL38 查找后降序排列
  select device_id, gpa, age from user_profile order by gpa desc, age desc;

基础操作符

• SQL6 查找学校是北大的学生信息
  select device_id, university FROM user_profile where university = “北京大学” and device_id = user_profile.device_id;
  回表查询
  先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
  使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引就会回表。
  索引覆盖
  只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
  实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。
  索引下推
  MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
  如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作。
• SQL7 查找年龄大于24岁的用户信息 >
  select device_id, gender, age, university from user_profile where age > 24;
• SQL8 查找某个年龄段的用户信息 and | between and
  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 23;
• SQL9 查找除复旦大学的用户信息 !=
  select device_id, gender, age, university from user_profile where university != “复旦大学”;
• SQL10 用where过滤空值练习 is not null
  select device_id, gender, age, university from user_profile where age is not null;

高级操作符

• SQL 11 男性且GPA在3.5以上(不包括3.5)的用户 and
  select device_id, gender, age, university, gpa from user_profile where gender = “male” and gpa > 3.5;
• SQL 12 学校为北大或GPA在3.7以上(不包括3.7)的用户 or
  select device_id, gender, age, university, gpa from user_profile where university = “北京大学” or gpa > 3.7;
• SQL13 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 查看学校名称中含北京的用户 like % 模糊匹配
  select device_id, age, university from user_profile where university like “%北京%”;
  四种匹配模式：
  1. _：表示任意单个字符，用于匹配单个任意字符
  2. %：表示任意0个或多个字符,用于匹配任意类型和长度的字符
  3. [ ]：指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个
  4. [^ ] ：表示不在括号所列之内的单个字符

高级查询

计算函数

• SQL16 查找GPA最高值 聚合函数 max()
  select max(gpa) from user_profile where university = “复旦大学”;
• SQL17 计算男生人数以及平均GPA count() avg()
  select count(gender) male_num, avg(gpa) avg_gpa from user_profile where gender = “male”;
  count
  • COUNT(column_name) 语法
    COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
  • COUNT(*) 语法
    COUNT(*) 函数返回表中的记录数
  • COUNT(1) 语法
    count（*） 、count（1）：这两个的使用方法和结果是相同的。表示返回所有的行，经常使用在没有where条件的语句中，速度较快。
  • COUNT(DISTINCT column_name) 语法
    COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目（NULL 不计入）

分组查询

• SQL 18 分组计算练习题
  每个学校不同性别的用户活跃情况和发帖数量
  select gender, university, count(id) user_num, avg(active_days_within_30) avg_active_day, avg(question_cnt) avg_question_cnt from user_profile group by university, gender;
• SQL19 分组过滤练习题
  每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校
  select university, avg(question_cnt) avg_question_cnt, avg(answer_cnt) avg_answer_cnt from user_profile group by university having avg_question_cnt < 5 or avg_answer_cnt < 20;
  聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可
• SQL20 分组排序练习题
  select university, avg(question_cnt) avg_question_cnt from user_profile group by university order by avg_question_cnt;

多表查询

子查询

• SQL21 浙江大学用户题目回答情况
  select user_profile.device_id, question_id, result
  from user_profile, question_practice_detail
  where user_profile.device_id = question_practice_detail.device_id
  and user_profile.device_id in (select device_id from user_profile where university = “浙江大学”) order by question_id;

链接查询

• SQL22 统计每个学校的答过题的用户的平均答题数
  select university, count(question_id)/count(distinct up.device_id) avg_answer_cnt
  from user_profile as up inner join question_practice_detail as qpd on qpd.device_id = up.device_id
  group by university
  order by university;
• SQL23 统计每个学校各难度的用户平均刷题数
  select university, difficult_level, count(qpd.question_id) / count(distinct up.device_id) ang_aswer_cnt
  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
  group by university, difficult_level;
• SQL24 统计每个用户的平均刷题数 山东大学的用户在不同难度下的平均答题题目数
  select university, difficult_level, count(qd.question_id) / count(distinct up.device_id) avg_answer_cnt
  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 查找山东大学或者性别为男生的信息
  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”;
  UNION会将组合后的结果进行去重处理
  UNION ALL会将组合的结果直接拼接，并不进行去重处理