表单符号注意⚠️

中文使用双引号 "" ；英语使用单引号''

查询结果去重

输入：
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

输出：
北京大学
复旦大学
浙江大学
山东大学

• select distinct university from user_profile;
• select university from user_profile group by university;

查询结果限制返回行数

select device_id from user_profile limit 0,2---运行效率更高

select device_id from user_profile limit 2 ---运行效率低

也可结合 limit offset： 一起使用时，limit表示要取的数量，offset表示跳过的数量

select device_id from user_profile limit 2 offset 0 // 跳过0条，从第一条数据开始取，取两条数据 ---运行效率中

使用LIMIT限制结果集

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。 LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。 如果只给定一个参数，它表示返回最大的记录行数目。 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1。 初始记录行的偏移量是 0(而不是 1)。

例5.检索记录行 6-10

SELECT` `* ``FROM` `table` `LIMIT 5,5

例6.检索记录行 11-last

SELECT` `* ``FROM` `table` `LIMIT 10,-1

例7.检索前 5 个记录行

SELECT` `* ``FROM` `table` `LIMIT 5

用where过滤空值练习

SELECT device_id,gender,age,university FROM user_profile where age IS NOT NULL;

查询NULL时，不能使用比较运算符(=或者< >)，需要使用IS NULL运算符或者IS NOT NULL运算符。

NULL要大写

select device_id,gender,age,university from user_profile where age !=''

Where in 和Not in

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

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

字符匹配

一般形式为：

列名 [NOT ] LIKE

匹配串中可包含如下四种通配符： _：匹配任意一个字符； %：匹配0个或多个字符； [ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；

[^ ]：不匹配[ ]中的任意一个字符。

例23．查询学生表中姓‘张’的学生的详细信息。

SELECT` `* ``FROM` `学生表 ``WHERE` `姓名 ``LIKE` `‘张%’

例24．查询姓“张”且名字是3个字的学生姓名。

SELECT` `* ``FROM` `学生表 ``WHERE` `姓名 ``LIKE` `'张__’

如果把姓名列的类型改为nchar(20)，在SQL Server 2012中执行没有结果。原因是姓名列的类型是char(20)，当姓名少于20个汉字时，系统在存储这些数据时自动在后边补空格，空格作为一个字符，也参加LIKE的比较。可以用rtrim()去掉右空格。

SELECT` `* ``FROM` `学生表 ``WHERE` `rtrim(姓名) ``LIKE` `'张__'

例25.查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。

SELECT` `* ``FROM` `学生表 ``WHERE` `姓名 ``LIKE` `'[张李刘]%’

例26.查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号。

SELECT` `姓名,学号 ``FROM` `学生表 ``WHERE` `姓名 ``LIKE` `'_[小大]%'

例27.查询学生表中所有不姓“刘”的学生。

SELECT` `姓名 ``FROM` `学生 ``WHERE` `姓名 ``NOT` `LIKE` `'刘%’

例28.从学生表表中查询学号的最后一位不是2、3、5的学生信息。

SELECT` `* ``FROM` `学生表 ``WHERE` `学号 ``LIKE` `'%[^235]'

计算男生人数以及平均GPA

精华题解

发表于 2021-08-31 18:03

题意明确：题目要求得到『男性用户有多少人』以及『他们的平均gpa是多少』。

---

问题分解：

1. 限定条件为 男性用户；
2. 有多少人，明显是计数，count函数；
3. 平均gpa，求平均值用avg函数；

---

细节问题：根据输出示例，有两个问题需要注意：

1. 表头重命名，用as语法
2. 浮点数的平均值可能小数点位数很多，按照示例保存一位小数，用round函数

---

因此完整代码呼之欲出：

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

分组过滤练习题

题意明确：

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

---

问题分解：

• 限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
• 按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university

---

细节问题：

• 表头重命名：as
• 用having不用where

完整代码：

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