[SQL]牛客网-SQL快速入门练习题笔记

最新推荐文章于 2022-10-21 15:16:52 发布
最新推荐文章于 2022-10-21 15:16:52 发布
阅读量855 收藏 4
点赞数 1
文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45867071/article/details/124275000
版权

SQL快速入门

Chamberlain Tao

牛客网-SQL快速入门

  1. 查询所有列
SELECT * FROM user_profile
  1. 查询多列
SELECT device_id,gender,age,university FROM user_profile
  1. 查询结果去重
SELECT DISTINCT university FROM user_profile
  • 用DISTINCT关键字可以去掉结果中的重复行。
  1. 查询结果限制返回行数
SELECT device_id FROM user_profile LIMIT 0, 2

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

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

  • 检索记录行 6-10
SELECT * FROM table LIMIT 5,5
  • 检索记录行 11-last
SELECT * FROM table LIMIT 10,-1
  • 检索前 5 个记录行
SELECT * FROM table LIMIT 5
  1. 将查询后的列重新命名
SELECT device_id as user_infos_example FROM user_profile LIMIT 2
二. 条件查询
  1. 查找学校是北大的学生信息
SELECT 	device_id, university 
FROM user_profile 
WHERE university = '北京大学'

图片说明

  1. 查找年龄大于24岁的用户信息
SELECT device_id, gender, age, university 
FROM user_profile 
WHERE age > 24
  1. 查找某个年龄段的用户信息
SELECT device_id, gender, age 
FROM user_profile
WHERE age >= 20 AND age <= 23
  1. 查找除复旦大学的用户信息
SELECT device_id, gender, age, university
FROM user_profile
WHERE university != '复旦大学'
  1. 用where过滤空值练习
SELECT device_id, gender, age, university
FROM user_profile
WHERE age IS NOT NULL
  1. 高级操作符练习(1)
SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE gender = 'male' AND gpa > 3.5
  1. 高级操作符练习(2）
SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE university = '北京大学' OR gpa > 3.7
  1. Where in 和Not in
SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE university IN ('北京大学', '复旦大学', '山东大学')
  1. 操作符混合运用
SELECT device_id, gender, age, university, gpa
FROM user_profile
WHERE (gpa > 3.5 AND university = '山东大学') OR (gpa > 3.8 AND university = '复旦大学')
  1. 查看学校名称中含北京的用户
SELECT device_id, age, university
FROM user_profile
WHERE university LIKE "%北京%"

字符匹配

一般形式为：

列名 [NOT ] LIKE

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

  • 查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况
SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%’
三.高级查询
  1. 查找GPA最高值
# 方法一
SELECT MAX(gpa) FROM user_profile WHERE university = '复旦大学'
# 方法二
SELECT gpa FROM user_profile 
WHERE university = '复旦大学' ORDER BY gpa DESC LIMIT 1
  1. 计算男生人数以及平均GPA
SELECT COUNT(gender) as male_num, round(AVG(gpa), 1) as avg_gpa
FROM user_profile
WHERE gender = 'male'

round函数

SELECT ROUND(column_name,decimals) FROM table_name

column_name 要舍入的字段
decimals 要返回的小数位数

count :计数

avg :平均值

  1. 分组计算练习题
select 
    gender, university, 
    count(gender) as user_num, 
    avg(active_days_within_30) as avg_active_day, 
    avg(question_cnt) as avg_question_cnt
from user_profile
group by university, gender

分组

作用：可以控制计算的级别：对全表还是对一组。
目的：细化计算函数的作用对象。
分组语句的一般形式：
[GROUP BY ]
[HAVING ]

GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列的别名。
带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。

  • 统计每门课程的选课人数，列出课程号和人数。
SELECT 学号, 
COUNT(*) 选课门数,
AVG(成绩) 平均成绩
FROM 成绩表
GROUP BY 学号

19.分组过滤练习题

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

使用HAVING

HAVING子句用于对分组后的结果再进行过滤，
它的功能有点像WHERE子句，但它用于组而不是单个记录。
在HAVING子句中可以使用统计函数，但在WHERE子句中则不能。

HAVING通常与GROUP BY子句一起使用。

  • 查询学生表中人数大于等于3的班号和人数
SELECT 班号, COUNT(*) 人数
FROM 学生表
GROUP BY 班号
HAVING COUNT(*) >= 3
  1. 分组排序练习题
select university, avg(question_cnt) as avg_question_cnt
from user_profile
group by university
order by avg_question_cnt asc
  1. 浙江大学用户题目回答情况
# 1.
select qpd.device_id, qpd.question_id, qpd.result
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id = up.device_id
where university = '浙江大学'
# 2.
select device_id, question_id, result
from question_practice_detail
where device_id = (select device_id 
                   from user_profile 
                   where university = '浙江大学')

多表查询-多表连接

  • 查询每个学生及其班级的详细信息。
SELECT * FROM 学生表
INNER JOIN 班级表 ON 学生表.班号=班级表.班号

表别名

可以为表提供别名，其格式如下：
<源表名> [ AS ] <表别名>

SELECT 姓名, 课程号, 成绩
FROM 学生表 S JOIN 成绩表 g 
ON S.学号 = g.学号
WHERE 状态 = ‘重修’

注：如果为表指定了别名，则查询语句中其他所有用到表名的地方都要使用别名

  • 查询软件工程系所有学生的情况，要求列出学生姓名和所在的系。
SELECT 姓名, 系名
FROM 学生表 s JOIN 班级表 bjb
ON s.班号 = bjb.班号
JOIN 系表 xb ON bjb.系号 = xb.系号
WHERE 系名= '软件工程系
  1. 统计每个学校的答过题的用户的平均答题数
select up.university, count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from user_profile as up
join question_practice_detail as qpd
on up.device_id = qpd.device_id
group by university
  1. 统计每个学校各难度的用户平均刷题数
select university, qd.difficult_level, round(count(qpd.question_id) / count(distinct up.device_id), 4) as avg_answer_cnt
from user_profile as up
join question_practice_detail as qpd
on up.device_id = qpd.device_id
join question_detail as qd
on qpd.question_id = qd.question_id
group by university, difficult_level
  1. 统计每个用户的平均刷题数
select university, qd.difficult_level, round(count(qpd.question_id) / count(distinct up.device_id), 4) as avg_answer_cnt
from user_profile as up
join question_practice_detail as qpd
on up.device_id = qpd.device_id
join question_detail as qd
on qpd.question_id = qd.question_id
where university = '山东大学'
group by difficult_level
  1. 计每个用户的平均刷题数
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。

UNION(并)

使用 UNION可以实现将多个查询结果集合并为一个结果集。

所有查询语句中列的个数和列的顺序必须相同。
所有查询语句中对应列的数据类型必须兼容。
ORDER BY语句要放在最后一个查询语句的后边。

  • 查询系号是1和2的班级的班号、班名、系号，系号是1 的记录在前，2在后。
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 1
UNION
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 2

等价于

SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号 IN (1,2) ORDER BY 系号
  1. 计算25岁以上和以下的用户数量
# case
select case when age  >= 25 then '25岁及以上'
            when age < 25 or age is null then '25岁以下'
            end as age_cut, count(*) as number
from user_profile
group by age_cut
# if
select if (age >= 25, '25岁及以上', '25岁以下') as age_cut, count(*) as number
from user_profile
group by age_cut
case

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

简单CASE函数
CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
  • 查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：
    当系号为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
  1. 查看不同年龄段的用户明细
select device_id, gender, 
    case when age < 20 then '20岁以下'
         when age >= 20 and age <= 24 then '20-24岁'
         when age >= 25 then '25岁及以上'
         else '其他'
    end as age_cut
from user_profile
  1. 计算用户8月每天的练题数量

题意：

2021年8月每天用户练习题目的数量

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
  • year ，month，day函数
  1. 计算用户的平均次日留存率(hard)
select count(date2) / count(date1) as avg_ret
from (
    select 
        distinct qpd.device_id, 
        qpd.date as date1,
        uniq_id_date.date as date2
    from question_practice_detail as qpd
    left join(
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
    on qpd.device_id = uniq_id_date.device_id
        and date_add(qpd.date, interval 1 day) = uniq_id_date.date
) as id_last_next_date
  1. 统计每种性别的人数
# if
select if (profile like '%female', 'female', 'male') as gender, count(*) as number
from user_submit
group by gender

# substring_index
select substring_index(profile, ',', -1) as gender, count(*) as number
from user_submit
group by gender
  1. 提取博客URL中的用户名
select device_id, substring_index(blog_url, '/', -1) as user_name
from user_submit
  1. 截取出年龄
select substring_index(substring_index(profile, ',', 3), ',', -1) as age, count(*) as number
from user_submit
group by age

注：两次截取

  1. 找出每个学校GPA最低的同学(hard)
  1. 统计复旦用户8月练题情况
select up.device_id, up.university, 
       count(question_id) as question_cnt, 
       sum(if (qpd.result = 'right', 1, 0)) as right_question_cnt

from user_profile as up
join question_practice_detail as qpd
on up.device_id = qpd.device_id

where up.university = '复旦大学' and month(qpd.date) = 8
group by up.device_id
  1. 浙大不同难度题目的正确率
select qd.difficult_level, 
       sum(if (qpd.result = 'right', 1, 0)) / count(qpd.question_id) as correct_rate 
from  
    user_profile as up
    inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id
    inner join question_detail as qd
    on qpd.question_id = qd.question_id
where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc
  1. 查找后排序
SELECT device_id, age
from user_profile
order by age asc
  1. 查找后多列排序
select device_id, gpa, age
from user_profile
order by gpa, age
  1. 查找后降序排列
select device_id, gpa, age
from user_profile
order by gpa desc, age desc
  1. 21年8月份练题总数
# 1
select count(distinct device_id) as did_cnt, count(question_id) as question_cnt
from question_practice_detail
where date like "2021-08-%"
# 2
select count(distinct device_id) as did_cnt, count(question_id) as question_cnt
from question_practice_detail
where date between '2021-08-01' and '2021-08-30'
  • List item
Chamberlain T
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
T-SQL阅读笔记和习题
08-17
但是对于SQL语言不是很熟悉，于是找了一些书来看，经过多方阅读和对比，最后看到一本《MICROSOFT SQL SERVER 2008技术内幕：T-SQL语言基础》。这本书非常适合于SQL语言初学者，内容比较全面，其次是示例比较简单，...
数据库实验6 数据库的分组查询和统计查询
热门推荐
gl620321的博客
06-22 1万+
实验6 数据库的分组查询和统计查询 6.1实验目的及要求 熟练掌握数据查询中的分组、统计、计算和集合的操作方法 6.2实验内容 使用聚集函数查询、分组计算查询、集合查询 6.3实验步骤 6.3.1使用聚集函数 1.查询学生总人数； Select Count(*) as 学生总数 from student; 2.查询选修了选修课程的学生总数 Select count(distinct sno)as 选课学生总数 from sc; 3.查询所有课程的总学分数和平均学分数，以及最高
SQLServer学习笔记七：分组查询
少儿编程乔老师
03-21 4563
使用GROUP BY进行分组查询，从不同方面将数据进行分组汇总。
查询学生表中姓“张”，“李”，“刘”的学生的情况。
小羊的博客
04-11 8250
SELECT * FROM 你的表名 WHERE xm LIKE '张%' or xm LIKE '李%' or xm LIKE '刘%';
【sql语句查询】
ghost_angel2的博客
10-21 3767
select查询（希望有用‘）
MYSQL--DQL--单表查询
weixin_48085301的博客
07-06 167
条件查询(WHERE) 分组查询(GROUP BY) 排序查询(ORDER BY) 分页查询(LIMIT))ctrl+shift+r 只运行选中的sql语句 ctrl+r 运行查询窗口的sql语句 ctrl+/ 注释sql语句 ctrl+shift +/ 解除注释 1.查询年龄大于20岁的学员信息 2.查询年龄大于等于20岁的学员信息 3.查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息 4.查询入学日期在’1998-09-01’ 到 ‘1999-09-01’ 之间的学员信息 5.查询年龄
SQL2005 学习笔记 窗口函数(OVER)
12-15
1.简介： SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。 窗口函数功能非常强大，使用起来也十分容易。可以使用这个技巧立即得到大量...
SQL相关操作笔记（附习题）
12-14
SQL相关操作 插入 删除 更新 触发器 视图 索引 权限 授权图 插入 ABC 删除 delete from workson where empno=(select empno from employee where empname='\u5218\u56fd\u98ce') and projectno=(select ...
T-SQL电子书包含PPT及学习笔记、课后作业 非常好的sql学习资料
03-27
T-SQL中级教程，包括PPT、学习笔记及课后作业，非常经典的教程 --建库之前先建路径的代码 exec xp_cmdshell'mkdir d:\mydata', --也可以用exec xp_cmdshell'md d:\mydata' no_output exec xp_cmdshell'rd d:\...
2子查询习题笔记.sql
10-26
小白比较适合看的一种笔记 建议在数据库中打开看 因为是sql格式的
测试需要掌握的数据库sql知识(三)：十个必须掌握的关键字查询
曲鸟
07-21 2742
一、前言 未看过文章一的朋友，需要准备测试数据 测试数据sql如下： SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `id` int(11) NOT NULL,
Oracle实验二 SQL语言复习
Airycho的博客
01-02 4704
Oracle实验二 SQL语言复习 第1关：DML之增删改 ps:不要忘记commit 1、向Course表中插入一行 insert into Course(cno,cname,ccredit,snumber) values('C50','Oracle',4,100); commit; 2、使用Insert语句为CS系学生学生选择必修课C50。 insert into SC(sno,cno) select sno,'C50' from Student where sdept='CS' and not e
软件测试第一阶段：web前端技术基础-7- mysql数据库中的简单查询
weixin_67254534的博客
04-06 1612
一丶查询 1、数据的备份还原 导出：先对数据库进行备份，然后提取备份文件中的sql语句 导入： 方法1：创建和原始数据库同名的数据，然后执行从备份文件中导出的sql语句【此方法不需要修改导出sql语句】 方法2：在数据库中直接运行导出的sql语句【此方法往往需要先修改数据库文件】 测试用的数据库：hellodb students：学生表 classes：班级表 courses：课程表 socres：成绩表 t
数据库-查询语句
weixin_45725778的博客
01-04 876
查询语句结构 SELECT <列名> FROM <表名> [WHERE <行选择条件>] [GROUP BY <分组依据列>] [HAVING <组选择条件>] [ORDER BY <排序依据列>] 1、单表查询 （1）查询指定的列 例 查询全体学生的学号和姓名 SELECT Sno, Sname FROM Student （2）查询全部列 例 询全体学生的的详细信息 SELECT * FROM Student （3）查询经过计.
MySQLday03
qq_34159161的博客
04-28 803
#二、sql99语法 /* 语法： select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】 【group by 分组】 【having 筛选条件】 【order by 排序列表】 分类： 内连接（★）：inner 外连接 左外(★):left ...
测试开发面经（五）SQL查询进阶
sunnygao的博客
12-21 1809
文章目录测试开发面经（五）SQL查询-进阶一、SQL代码6.查询38).查询班级人数最多的班的学生情况 （count的应用）42).查询比软件学院学生年龄都小的其他系学生的信息。43).查询比数信学院学院学生年龄大的教育学院学生信息。45).查询计算机导论平均成绩最高的班级。 测试开发面经（四）SQL基础 测试开发面经（五）SQL查询-进阶 一、SQL代码 6.查询 36).查询籍贯是河北省的教师所教的课程信息 select teacher.tname,teacher.tno,teacher.home,c
图解SQL面试题：多表如何查询？
周鹏飞的IT学习之路
04-24 1597
图解SQL面试题：多表如何查询？ 1.【题目】 现在有两个表，“学生表”记录了学生的基本信息，有“学号”、“姓名”。 “成绩”表记录了学生选修的课程，以及对应课程的成绩。 这两个表通过“学号”进行关联。 现在要查找出所有学生的学号，姓名，课程和成绩。 2.【解题思路】 1.确定查询结果 题目要求查询所有学生的姓名，学号，课程和成绩信息 select 学号,姓名,课程,成绩 查询结果的列名“学号”...
SQL语句
立志须存千载想，谈吐应有鸿鹄梦。
04-14 1653
---分组------- --分组的目的：对数据进行汇总，统计.  与聚合函数一块使用。 select COUNT(*) from dbo.TblStudent --对表进行分组了，作为一组。 select * from TblStudent select tSGender, COUNT(*) from TblStudent group by tSGende
数据库MYSQL
m0_54382779的博客
10-17 4454
select sno,grade from course,sc where course.cno=sc.cno and cname='数据库';select sno,sname from student where sname in('马云','林伟','张燕红');('201215122', '刘晨', '女',19, 'CS ') ,('201215123', '王敏', '女',18, 'MA ') ,('201215121', '李勇', '男',20, 'CS '),
mysql sql练习题_Mysql----sql语句练习题(一)
最新发布
06-11
好的，我可以为您提供一些MySQL SQL语句练习题。以下是一些简单的练习题： 1. 创建一个名为"students"的表，包含以下列：id, name, age, gender。 2. 向表"students"中插入以下数据： | id | name | age | gender | |----|-------|-----|--------| | 1 | Alice | 20 | Female | | 2 | Bob | 22 | Male | | 3 | Carol | 19 | Female | 3. 查询表"students"中所有的数据。 4. 查询表"students"中年龄在20岁以上的学生。 5. 查询表"students"中所有女性学生的姓名和年龄。 6. 将表"students"中所有学生的年龄增加1岁。 7. 删除表"students"中所有年龄小于20岁的学生。 希望这些练习题能够帮助到您。如果您有更多的问题，请随时告诉我。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值