SQL实战篇

内容来源于牛客网在线编程SQL篇，全为笔者归纳。

SQL入门篇

1、基础查询

• 查询所有列

 select * from 表名;

• 查询多列

 select 列1,列2,列3 from 表名;

• 查询结果去重

 select 列名 from 表名 group by 列名;

• 查询结果限制返回行数

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

 select device_id from user_profile where id = 1 or id = 2;
 select device_id from user_profile where id<3;
 --limit m,n：从第m+1条开始，取n条数据
 select device_id from user_profile limit 0, 2;
--limit n：从第0+1(m=0)条开始，取n条数据，是limit 0,n的缩写
 select device_id from user_profile limit 2;
--limit n offset m：从第m+1条开始，取n条数据
 select device_id from user_profile limit 2 offset 0;
 select device_id from user_profile where id between 1 and 2;
 select device_id from user_profile where id in(1,2) 

• 将查询后的列重新命名

 select 列名(旧) as 列名(新) from 表名;
 select 列名(旧) 列名(新) from 表名;

2、条件查询

基础排序

• 查找后排序

题目：现在运营想要取出用户信息表（user_profile）中的用户年龄，请取出相应数据，并按照年龄升序排序。

id	device_id	gender	age	university

查询返回结果：

device	age

 select device_id,age from user_profile order by age asc; --asc可省略

• 查找后多列排序

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

id	device_id	gender	age	university	gpa

查询返回结果：

device_id	gpa	age

 select device_id,gpa,age from user_profile order by gpa asc,age asc;
 --两处asc皆可省略，降序的话asc改为desc即可

基础操作符

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

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

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

• 参照年龄大于24岁的用户信息（用户信息表：user_profile）

 select * from user_profile where age > 24;

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

题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。（用户信息表：user_profile）

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

补充：操作符 between ... and 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期

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

题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据（用户表：user_profile）

 select * from user_profile where university!="复旦大学";

• 用where过滤空值练习

题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的的所有信息。（用户表：user_profile）

 select * from user_profile where age is not null;
 select * from user_profile where age != 'null';
 select * from user_profile where age != ' ';

高级操作符

• where in 和 not in

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

 select * from user_profile
 where university in ('北京大学','复旦大学','山东大学');
 
 select * from user_profile
 where university = "北京大学" or university="复旦大学" or university="山东大学";
 -- 单引号或者双引号不影响

• 操作符混合运用

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

 SELECT...FROM user_profile
 where gpa>3.5 and university= '山东大学'
 or gpa>3.8 and university= '复旦大学';
 
 select ... from user_profile
 where (gpa>3.5 and university="山东大学") or (gpa>3.8 and university="复旦大学")

• SQL通配符

  通配符	描述
  %	替代 0 个或多个字符
  _	替代一个字符
  [charlist]	字符列中的任何单一字符
  [^charlist] 或 [!charlist]	不在字符列中的任何单一字符

  MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

  • 选取 url 以字母 "https" 开始的所有网站：

  SELECT * FROM Websites WHERE url LIKE 'https%';
  

  • 选取 url 包含模式 "oo" 的所有网站：

   SELECT * FROM Websites
   WHERE url LIKE '%oo%';
  

  • 选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户：

   SELECT * FROM Websites
   WHERE name LIKE '_oogle';
  

  

  • 选取 name 以 "G" 开始，然后是一个任意字符，然后是 "o"，然后是一个任意字符，然后是 "le" 的所有网站：

   SELECT * FROM Websites
   WHERE name LIKE 'G_o_le';
  

  • 选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

  SELECT * FROM Websites
  WHERE name REGEXP '^[GFs]';
  

  

  • 选取 name 以 A 到 H 字母开头的网站：

   SELECT * FROM Websites
   WHERE name REGEXP '^[A-H]';
  

  

  • 选取 name 不以 A 到 H 字母开头的网站：

   SELECT * FROM Websites
   WHERE name REGEXP '^[^A-H]';
  

  

高级查询

• 查询gpa最高值（用户信息表：user_profile）

 select max(gpa) from user_profile;

• 计算男生人数以及平均GPA，平均值结果保留到小数点后面1位(1位之后的四舍五入)

 select count(gender),round(avg(gpa),1)
 from user_profile where gender="male";

• having

多表查询

子查询

链接查询

组合查询

必会的常用函数

条件函数

日期函数

文本函数

窗口函数

待续