sql查询语句

1. limit

使用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
#前两行
select * from table limit 0,2
select * from table limit 2

2. BETWEEN AND AND

对于日期类型的数据也可以使用基于范围的查找。

例14．查询所有系号在2和3之间的班号和班名。

SELECT 班号,班名 FROM 班级表 WHERE 系号 BETWEEN   2   AND   3

等价于：

SELECT 班号,班名 FROM 班级表 WHERE 系号 >=  2   AND 系号<=  3

例15．查询所有系号不在2和3之间的班号和班名。

SELECT 班号,班名 FROM 班级表 WHERE 系号 NOT BETWEEN   2   AND   3

等价于：

SELECT 班号,班名 FROM 班级表 WHERE 系号 <  2   AND 系号 >  3

例16.查询1995年3月出生的学生信息：

SELECT 学号, 姓名, 性别, 出生日期 FROM 学生表  WHERE 出生日期 BETWEEN   '1995/3/1'   AND '  1995  /  3  /  31

3. not in in != 和 and or 有关系

IN：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录。
NOT IN：当列中的值与某个常量值相等时，结果为False，表明此记录为不符合查询条件的记录。

例17.查询所有班名是“电子信息工程技术”、“电子声像”或“电子组装技术”的班号和班名。

SELECT   班号, 班名   FROM   班级表  WHERE   班名   IN   (  '电子信息工程技术'   ,   '电子声像‘, '  电子组装技术')

等价于：

SELECT   班号,班名   FROM   班级表  WHERE   班名= ‘电子信息工程技术’   OR  班名 = ‘电子声像’   OR  班名 = ‘电子组装技术’

例18.查询班名既不是“电子信息工程技术”、也不是“电子声像”和“电子组装技术”的班号和班名。

SELECT   班号,班名   FROM   班级表  WHERE   班名   NOT   IN   (  '电子信息工程技术'   ,   '电子声像‘, '  电子组装技术')

等价于：

SELECT   班号,班名   FROM   班级表  WHERE   班名!= ‘电子信息工程技术’   AND  班名!= ‘电子声像’   AND  班名!= ‘电子组装技术’

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

SELECT   device_id,gender,age,university   FROM   user_profile  WHERE   university   NOT   IN   (  '复旦大学'  )

等价于: !=

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

4. is not null is null !=‘’

涉及空值的查询

空值（NULL）在数据库中表示不确定的值。
例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。
判断某个值是否为NULL值，不能使用普通的比较运算符。
判断取值为空的语句格式为：

列名   IS   NULL

判断取值不为空的语句格式为：

列名   IS   NOT   NULL

例19.查询还没有考试的学生的学号和相应的课程号。

SELECT   学号,课程号   FROM   成绩表  WHERE   成绩   IS   NULL

例20.查询所有已经考试了的学生的学号和课程号。

SELECT   学号,课程号   FROM   成绩表  WHERE   成绩   IS   NOT   NULL

题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户明细数据。

SELECT   device_id,gender,age,university   FROM   user_profile   WHERE   age   IS   NOT   NULL

age !=‘’ 也算空值查询

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

5. like字符匹配

一般形式为：

列名 [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]'

6. distinct 写在字段名前面

7. 使用聚合函数汇总数据

SQL提供的统计函数有：
COUNT(【Shift+8】)：统计表中元组个数；
COUNT([DISTINCT] <列名>)：统计本列列值个数；
SUM( <列名> )：计算列值总和；
AVG( <列名> )：计算列值平均值；
MAX( <列名> )：求列值最大值；
MIN( <列名> )： 求列值最小值。
上述函数中除COUNT(【Shift+8】)外，其他函数在计算过程中均忽略NULL值。

统计函数不能出现在WHERE子句中。
例如，查询成绩最高的学生的学号，如下写法是错误的：
SELECT 学号 FROM 成绩表
WHERE 成绩 = MAX(成绩)

例29.统计学生总人数。

SELECT   COUNT  (*)   FROM   学生表

例30.统计选修了课程的学生的人数。

SELECT   COUNT   (  DISTINCT   学号)  FROM   成绩表

例31.计算学号为“11214D24”的学生的考试总成绩之和。

SELECT   SUM  (成绩)   FROM   成绩表  WHERE   学号 = ‘11214D24 '

例32.计算“M01F011”课程的学生的考试平均成绩。

SELECT   AVG  (成绩)   FROM   成绩表  WHERE   课程号 = ‘M01F011 ‘

例33.查询选修了“M01F011” 课程的最高分和最低分。

SELECT   MAX  (成绩) 最高分,  MIN  (成绩) 最低分   FROM   成绩表  WHERE   课程号 = ‘M01F011 '

题解

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

SELECT   MAX  (gpa)gpa   FROM   user_profile   WHERE   university =   '复旦大学'

8. group by having

对查询结果进行分组计算

作用：可以控制计算的级别：对全表还是对一组。
目的：细化计算函数的作用对象。
分组语句的一般形式：
[GROUP BY ]
[HAVING ]
GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列的别名。
带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。

例34.统计每门课程的选课人数，列出课程号和人数。

SELECT   课程号,   COUNT  (课程号)   AS   选课人数  FROM   成绩表  GROUP   BY   课程号

该语句首先对查询结果按课程号的值分组，所有具有相同课程号值的元组归为一组，然后再对每一组使用COUNT函数进行计算，求得每组的学生人数。

例35.查询每名学生的选课门数和平均成绩。

SELECT   学号,  COUNT  (*) 选课门数,  AVG  (成绩) 平均成绩  FROM   成绩表  GROUP   BY   学号

题解

题目：现在运营想要对每个学校*不同性别的用户活跃情况和发帖数量进*行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

COUNT (*)user_num 确定数量

SELECT   gender,university,  COUNT  (*)user_num,  AVG  (active_days_within_30)avg_active_days,  AVG  (question_cnt)avg_quesition_cnt  FROM   user_profile  GROUP   BY   gender,university

关于分组函数 where having

  1. 当题目出现关键词“每”，“各”的时候，我们就可以判断结果集是需要进行分组的，我们就想到利用分组函数来解答我们的需求
  2. 可能有人问，为啥后面用having ，不能用where吗？

这里需要对聚合函数的结果进行比较，where后面是不能使用聚合函数的字段的。

group by 后面可以跟 聚合字段和正常字段

  1. tips：在排序中order by 字段 asc --其中升序为asc(可以不写，默认就是按照升序排列),降序为desc（必须写）
  2. 分组一般会结合聚合函数使用，就是要取得分组后每个组的统计值（max/min/avg等等）；当然也可以只用分组，不用聚合，那就是对分组去重
select university,avg(question_cnt) avg_question_cnt
from user_profile group by university  
 order by  avg(question_cnt)

having 后面不用加order by

9. group by规则

官方文档是 ：所有select的字段，除聚合函数中的字段，都必须在group by中出现。且group

那上面的字段举例子，执行如下语句一定会报错

select university,device_id,count(gpa)
from user_profile
group by university;

原因是，我们使用了聚合函数，而聚合函数一旦与group by一起使用，那么再除去聚合函数列后我们所查询的表段信息的列名必须全部放到group by里
如下才是正确使用

select university,device_id,count(gpa)
from user_profile
group by university,device_id;

only_full_group_by的作用

之所以会出现这种情况，是因为我们sql的配置文件my.ini/my.conf做了默认限制限制
sql_mode=“ONLY_FULL_GROUP_BY”

#可以通过如下命令去查看
select @@global.sql_mode;
#ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode，通过这个sql_mode来提供SQL语句GROUP BY合法性的检查。
在MySQL的sql_mode是非ONLY_FULL_GROUP_BY语义时，一条select语句，MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式，这个表达式的值可能在经过group by操作后变成undefined。
简单点说就是为了数据查询结果的严谨性才设置如此用法，mysq也有可能是跟风，因为5.7以前的mysql允许出现语义不明确的列查询结果，所以就不默认使用ONLY_FULL_GROUP_BY
MySQL 5.7版本开始默认使用这个语义，就是我们所说的ONLY_FULL_GROUP_BY语义

10. asc 默认升序 desc 降序

在排序中order by 字段 asc --其中升序为asc(可以不写，默认就是按照升序排列),降序为desc（必须写）

表示先用gpa进行 升序或降序排序,再用age 进行 升序或降序排序

一般有两个数据的gpa相同才能对age进行排序

SELECT device_id,gpa,age from user_profile order by gpa,age;默认以升序排列
SELECT device_id,gpa,age from user_profile order by gpa,age asc;
SELECT device_id,gpa,age from user_profile order by gpa asc,age asc;

11. union 结果去重和不去重

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 all，去重就用 union。
where university=‘山东大学’ or gender="male"的话，也是去重的。

12. case 语法 CASE函数

是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。
可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。
分为：
简单CASE函数
搜索CASE函数

简单 CASE函数
select 
CASE   测试表达式 
WHEN   简单表达式1   THEN   结果表达式1 
WHEN   简单表达式2   THEN   结果表达式2 …  
WHEN   简单表达式n   THEN   结果表达式n 
[   ELSE   结果表达式n+1 ]
END

计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。
如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。
如果所有简单表达式的值与测试表达式的值都不相等，
若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；
若没有指定ELSE子句，则返回NULL。

例48. 查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：
当系号为1时，显示 “计算机系”；
当系号为2时，显示 “软件工程系”；
当系号为3时，显示 “物联网系”。

SELECT   班号 ,班名,  
CASE   系号  
WHEN   1   THEN   '软件工程系'  
WHEN   2   THEN   '计算机系'  
WHEN   3   THEN   '物联网系'  
END   AS   系号,班主任号  
FROM   班级表
搜索CASE函数
CASE
WHEN   布尔表达式1   THEN   结果表达式1  
WHEN   布尔表达式2   THEN   结果表达式2 …  
WHEN   布尔表达式n   THEN   结果表达式n  
[   ELSE   结果表达式n+1 ]  END

按从上到下的书写顺序计算每个WHEN子句的布尔表达式。
返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。
如果没有取值为TRUE的布尔表达式，
则当指定了ELSE子句时,返回ELSE子句中指定的结果；
如果没有指定ELSE子句，则返回NULL。

例48用搜索CASE来做：

SELECT   班号 ,班名,  
CASE 
WHEN   系号=1   THEN   '软件工程系' 
WHEN   系号=2   THEN   '计算机系'  
WHEN   系号=3   THEN   '物联网系'  
END   AS   系号,班主任号  
FROM   班级表

例49.查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。

注意

SELECT   学号,课程号,  
CASE    
case 的前面有一个 逗号 "  ,"
SELECT   学号,课程号,  
CASE 
WHEN   成绩 >= 90   THEN   '优' 
WHEN   成绩   BETWEEN   80   AND   89   THEN   '良' 
WHEN   成绩   BETWEEN   70   AND   79   THEN   '中'  
WHEN   成绩   BETWEEN   60   AND   69   THEN   '及格'  
WHEN   成绩 <60   THEN   '不及格'  
END   成绩 
FROM   成绩表  
WHERE   课程号 =   'M01F011'

CASE函数(续)

例50.统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。

SELECT   班号,  COUNT  (  CASE   WHEN   性别=‘男’   THEN   ‘男’   END  ) 男生数,  COUNT  (  CASE   WHEN   性别=‘女’   THEN   ‘女’   END  ) 女生数  FROM   学生表   GROUP   BY   班号

例51.判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。

SELECT 
CASE  
WHEN   GRADE   BETWEEN   85   AND   100   THEN   '优'  
WHEN   GRADE   BETWEEN   70   AND   84   THEN   '良' 
WHEN   GRADE   BETWEEN   60   AND   69   THEN   '及格' 
ELSE   '不及格' 
END   等级,   COUNT  (*) 人数  
FROM   SC 
GROUP   BY 
CASE  
WHEN   GRADE   BETWEEN   85   AND   100   THEN   '优' 
WHEN   GRADE   BETWEEN   70   AND   84   THEN   '良'  
WHEN   GRADE   BETWEEN   60   AND   69   THEN   '及格' 
ELSE   '不及格'  END

13. 日期函数

日期函数

DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

select   DAYOFWEEK(  '1998-02-03'  )    -> 3
WEEKAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

mysql>   select   WEEKDAY(  '1997-10-04 22:23:00'  );   -> 5
DAYOFMONTH(date)

返回date的月份中日期，在1到31范围内。

mysql>   select   DAYOFMONTH(  '1998-02-03'  );   -> 3
DAYOFYEAR(date)

返回date在一年中的日数, 在1到366范围内。

mysql>   select   DAYOFYEAR(  '1998-02-03'  );    -> 34
MONTH(date)

返回date的月份，范围1到12。

mysql>   select   MONTH  (  '1998-02-03'  );    ->2
DAYNAME(date)

返回date的星期名字。

mysql>   select   DAYNAME(  "1998-02-05"  );       -> 'Thursday'
MONTHNAME(date)

返回date的月份名字。

mysql>   select   MONTHNAME(  "1998-02-05"  );     -> 'February'
QUARTER(date)

返回date一年中的季度，范围1到4。

mysql>   select   QUARTER(  '98-04-01'  );     -> 2
WEEK(date)

对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。

mysql>   select   WEEK(  '1998-02-20'  );     -> 7
WEEK(date,first)

2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，
如果第二个参数是1，从星期一开始。

mysql>   select   WEEK(  '1998-02-20'  ,0);   -> 7
mysql>   select   WEEK(  '1998-02-20'  ,1);   -> 8
YEAR(date)

返回date的年份，范围在1000到9999。

mysql>   select   YEAR  (  '98-02-03'  );    -> 1998
HOUR(time)

返回time的小时，范围是0到23。

mysql>   select   HOUR  (  '10:05:03'  );    -> 10
MINUTE(time)

返回time的分钟，范围是0到59。

mysql>   select   MINUTE  (  '98-02-03 10:05:03'  );   -> 5
SECOND(time)

回来time的秒数，范围是0到59。

mysql>   select   SECOND  (  '10:05:03'  );    -> 3
PERIOD_ADD(P,N)

增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。

mysql>   select   PERIOD_ADD(9801,2);   -> 199803
PERIOD_DIFF(P1,P2)

返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。

mysql>   select   PERIOD_DIFF(9802,199703);

14. substring_index 字符串截取

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-w3cD1VEj-1660227624046)(http://zwj.birenyin.cn/4EF7B5DAE69AD54DE81C49D58020D7A4)]

题目

  • 限定条件：2021年8月份，匹配date字段即可，匹配方法主要有三种：
    （1）like语法： date like “2021-08%”
    （2）year、month函数： year(date)=‘2021’ and month(date)=‘08’ ；
    （3）date_format函数： date_format(date, ‘%Y-%m’)=‘2021-08’;
  • 2：总用户数：count函数计数，因为用户有重复，所以需要distinct去重，即 count(distinct device_id)

题意明确：2021年8月每天用户练习题目的数量

问题分解：

  • 限定条件：2021年8月，写法有很多种，比如用year/month函数的 year(date)=2021 and month(date)=8 ，比如用date_format函数的 date_format(date, “%Y-%m”)=“202108”
  • 每天：按天分组 group by date
  • 题目数量：count(question_id)

细节问题：

  • 表头重命名：as
  • 输出示例中每天的字段只取了几号，要去掉年月，用day函数即可

完整代码：

select      day  (  date  )   as   day  ,      count  (question_id)   as   question_cnt  from   question_practice_detail  where   month  (  date  )=8   and   year  (  date  )=2021  group   by   date

将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

# if判断
SELECT IF(age<25 OR age IS NULL,'25岁以下','25岁以及上') age_cut,COUNT(device_id) Number
FROM user_profile
GROUP BY age_cut

# 联合查询
SELECT '25岁以下' age_cut,COUNT(device_id) Number
FROM user_profile
WHERE age < 25 OR age IS NULL
UNION
SELECT '25岁以及上' age_cut,COUNT(device_id) Number
FROM user_profile
WHERE age >= 25

# case 查询
SELECT CASE WHEN age < 25 OR age IS NULL THEN '25岁以下'
            WHEN age >= 25 THEN '25岁及以上'
            END age_cut,COUNT(*)number
FROM user_profile
GROUP BY age_cut

分组一般会结合聚合函数使用，就是要取得分组后每个组的统计值（max/min/avg等等）；

当然也可以只用分组，不用聚合，那就是对分组去重

题解：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

SELECT   CASE   WHEN   age < 25   OR   age   IS   NULL   THEN   '25岁以下'          WHEN   age >= 25   THEN   '25岁及以上'          END   age_cut,  COUNT  (*)number  FROM   user_profile  GROUP   BY   age_cut
select que.device_id,que.question_id,que.result 
from question_practice_detail as que,user_profile as  user 
where que.device_id= user .device_id and  user .university='浙江大学'
order by question_id



select device_id,question_id,result from question_practice_detail 
where device_id in(
select device_id from user_profile 
    where university='浙江大学'
)
order by question_id


select que.device_id,que.question_id,que.result
from question_practice_detail que 
inner join user_profile  user 
on que.device_id= user .device_id
where  user .university='浙江大学'
order by que.question_id

select up.university,qd.difficult_level,
round (count (qpd.question_id) / count (distinct qpd.device_id),4) as avg_answer_cnt
from user_profile up
left join question_practice_detail qpd
on up.device_id=qpd.device_id
left join question_detail qd
on qd.question_id=qpd.question_id
group by university,difficult_level