入门sql语句-牛客刷题

已于 2023-05-23 10:29:49 修改
阅读量869 收藏 3
点赞数
文章标签： sql 数据库 mysql
于 2023-04-03 01:14:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42422090/article/details/129833309
版权

基础查询

  1. 查询表所有列：
select * from user_profile;
  1. 查询表指定列数据：
select age from user_profile;
  1. 查询指定列并去重：
select 
	DISTINCT university 
from 
	user_profile;
	
select university
from user_profile 
group by university;
  1. 查询年龄数据并修改命名、输出前2条数据；
select age as new_age 
from user_profile 
limit 2;

条件查询

  1. 查询后排序
    升序：
select device_id,age 
from user_profile 
order by age;

降序：

select device_id,age 
from user_profile 
order by age desc;

查询后多列升序排序：

select device_id,gpa,age 
from user_profile 
order by gpa,age;

查询后多列降序排序；

select device_id,gpa,age 
from user_profile 
order by gpa desc,age desc;
  1. 查询除了某大学的用户信息，过滤空值
select 
	device_id, university 
from 
	user_profile 
where 
	university != '复旦大学';
#   not like '复旦大学';
#   not in ('复旦大学');
  1. 高级操作符混合
    and，or
select 
	device_id,gender,age,university,gpa 
from 
	user_profile 
where 
	gpa>3.5 and university ='山东大学' 
	or 
	gpa>3.8 and university='复旦大学';
  1. in，not in
select 
	device_id,gender,age,university,gpa 
from 
	user_profile 
where 
	university in ('复旦大学','北京大学','山东大学');
  1. 模糊查询
    查看学校名称中含北京的用户
    ——这道题主要考察的是模糊查询 字段名 like ‘匹配内容’

_ : 下划线 代表匹配任意一个字符；
% ：百分号 代表匹配0个或多个字符；
[]： 中括号 代表匹配其中的任意一个字符；
[^]: ^尖冒号 代表 非，取反的意思；不匹配中的任意一个字符。

select 
	device_id,age,university
from 
	user_profile 
where 
	university like '%北京%';

tips：面试常问的一个问题：你了解哪些数据库优化技术？
SQL语句优化也属于数据库优化一部分，而like模糊查询会引起全表扫描，速度比较慢，应该尽量避免使用like关键字进行模糊查询。

select 
	device_id,age,university
from 
	user_profile 
where 
	university regexp '北京';

LIKE 和 REGEXP之间的重要差别
LIKE 匹配整个列，如果被匹配的文本在列值中出现，LIKE 将不会找到它，相应的行也不会被返回（除非使用通配符）。
而 REGEXP 在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP 将会找到它，相应的行将被返回，并且 REGEXP 能匹配整个列值（与 LIKE> 相同的作用）

高级查询

  1. 查复旦大学学生gpa最大值
# 自己排序截取
select gpa 
from user_profile 
where university = '复旦大学' 
order by gpa desc limit 1;
# 函数
select max(gpa) 
from user_profile 
where university = '复旦大学'
  1. 计算总数和平均值（四舍五入）
select 
	count(gender) as male_num,
	round(avg(gpa),1) as avg_gpa 
from user_profile 
where gender='male';
  1. 分组计算
select
    gender, university,
    count(device_id) as user_num,
    round(avg(active_days_within_30),1) as avg_active_day,
    round(avg(question_cnt),1) as avg_question_cnt
from user_profile
group by
    gender,university;
  1. 分组过滤

题意：取出平均发贴数低于5的学校或平均回帖数小于20的学校（小数点后保留3位）
解题步骤
1）按条件分组
2）按分组条件 计算筛选
3）输出对应数据列

select
    university,
    round(avg(question_cnt), 3) as  avg_question_cnt,
    round(avg(answer_cnt), 3) as avg_answer_cnt	
from
    user_profile
group by
    university
having    # 聚合函数结果作为筛选条件时，不能用where，而是用having语法
    avg(question_cnt) < 5
    or avg(answer_cnt) < 20

select deployment, count(employee) as quantity
from company
group by deployment
where quantity > 100

select deployment
from company
group by deployment
having count(employee) > 100

  1. 分组排序

题意：不同大学的用户平均发帖情况，并按照平均发帖情况进行升序排列

select
    university,
    avg(question_cnt) as  avg_question_cnt
from
    user_profile
group by
    university
order by    
    avg(question_cnt)

☆☆☆ 多表查询

题意：所有来自浙江大学的用户题目回答明细情况

  1. 子查询（嵌套查询、内部查询）
    一个查询语句嵌套在另一个查询语句内的查询。

第一种：创建一张临时表用，获取浙江大学device_id对用户题目回答明细进行过滤。

select 
    device_id,
    question_id,
    result
from
    question_practice_detail
where device_id in   
	(
	select device_id 
	from user_profile 
	where university ='浙江大学'
	)
  1. 连接查询

第二种：先将两张表关联在一起，然后再筛选出浙江大学的明细数据

select
    q.device_id,
    q.question_id,
    q.result
from
    question_practice_detail q
    left join user_profile u on q.device_id = u.device_id
where
    university = '浙江大学'
  1. 连接查询——统计每个学校的答过题的用户的平均答题数

参考解题思路

  • 按每个学校统计——分组，group by
  • 用户平均答题数计算：同一用户多次登录答题，需要对用户进行去重处理
  • 两张表的连接处理
select
    u.university,
    count(q.question_id) / count(distinct q.device_id) as avg_answer_cnt
from
    question_practice_detail as q
    left join user_profile as u on q.device_id = u.device_id
group by
    u.university;
  1. 连接查询——统计每个学校各难度的用户平均刷题数（三张表）
  • 分组查询 group by
  • 计算用户平均答题量 count(question_id)/count(disinct device_id)
  • 表连接 三张表
select 
    university,
    difficult_level,
    count(q.question_id)/count(distinct q.device_id) as avg_answer_cnt
from user_profile u 
    join question_practice_detail q on q.device_id = u.device_id 
    join question_detail d on q.question_id = d.question_id
group by university, difficult_level
  1. 组合查询——查找山东大学或者性别为男生的信息
  • 限定条件：学校为山东大学或者性别为男性的用户：university=‘山东大学’, gender=‘male’；
  • 分别查看&结果不去重 ：直接使用or不行，直接用union也不行，要用union all ，分别去查满足条件1的和满足条件2的，然后合在一起不去重
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'

常用函数

  1. 条件函数
  • 计算25岁及以上 和25岁以下 的用户数量

if语句使用：if(条件，条件真的值1，条件假的值2)

# if语句使用
select 
    if(age>=25, '25岁及以上','25岁以下') as age_cut,
    count(distinct device_id) as number
from user_profile
group by age_cut
# case 语句格式
select 
	case
		when age>=25 then '25岁及以上'
		else '25岁以下'
	end as age_cut,
    count(distinct device_id) as number
from user_profile
group by age_cut
  • 查看不同年龄段用户信息
# if语句格式
select
    device_id,
    gender,
    if (
    	age >= 25, '25岁及以上', if (age >= 20 and age <= 24, '20-24岁', '其他')
    ) as age_cut
from
    user_profile
# case 语句格式
# 	when... then... 
# 	else... 
# end as...
select
    device_id,
    gender,
    case
        when age>=25 then '25岁及以上'
        when age>=20 and age <=24 then '20-24岁'
        else '其他'
    end as 'age_cut'
from user_profile
  1. 日期函数
  • 计算用户2021年8月每天的练题数量
# 使用日期函数
select
    day (date) as ourday,
    count(question_id) as question_cnt
from
    question_practice_detail
where
    month (date) = 8 and year (date) = 2021
group by ourday
# 使用正则表达式
select
    day (date) as ourday,
    count(question_id) as question_cnt
from
    question_practice_detail
where 
    date regexp '2021-08'
group by ourday
  • 计算用户的平均次日留存率
    次日：涉及到前后两天，此处通过两个表来进行连接（自连接）；
    次日留存率：有前一天记录的日期统计量/所有记录的日期统计量
select
	count(q2.device_id)/count(q1.device_id)
from 
	(select distinct device_id,date from question_practice_detail) as q1
	left join
	(select distinct device_id,date from question_practice_detail) as q2
	on q2.device_id = q1.device_id and q2.date= date_add(q1.date, interval 1 day)
# 还可以用函数 datediff(q1.date,q2.date)=1
  1. 文本函数

这里是引用

substring_index(str,分隔符,序号)
序号为正数，表示从左往右 截取目标分隔符之前的字符串；1, 2, 3...
序号为负数，表示从右往左 截取目标分隔符之后的字符串；-1, 2, -3...

# 复习 if 和case 逻辑语句
# 复习 like 模糊查询
# 复习 regexp 正则匹配
###############################################
select 
    if(profile regexp 'female','female','male') as 'gender',
    count(device_id) as number
from user_submit
group by gender
###############################################
select 
    if(profile like '%female','female','male') as 'gender',
    count(device_id) as number
from user_submit
group by gender
###############################################
select 
    case 
        when profile regexp 'female' then 'female'
        when profile regexp 'male' then 'male'
    end as 'gender',
    count(device_id) as number
from user_submit
group by gender

# 新学习：字符串提取函数substring_index
select
    substring_index (profile, ',', -1) as gender,
    count(device_id) as number
from user_submit
group by gender
  • 截取年龄
select
    substring_index(substring_index(profile,',',-2),',',1) as age,
    count(device_id) as number
from user_submit
group by age
  1. 窗口函数
    row_number() over() 解析
    基本功能：分组和排序功能
    语法格式: row_number() over(partition by 分组列 order by 排序列)
  • 找出每个学校分数最低的学生
# 方式一：通过子查询语句和连接语句
select
    u2.device_id,
    u1.university,
    u1.gpa
from
    (
        select
            university,
            min(gpa) gpa
        from
            user_profile
        group by
            university
    ) as u1
    left join user_profile u2 on u1.university = u2.university
    and u1.gpa = u2.gpa
order by
    u1.university
    
# 方式二：通过窗口函数
select
    device_id,
    university,
    gpa
from (
    select     
        device_id,
        university,
        gpa,
        row_number() over(
            partition by university order by gpa
        ) as rank
    from user_profile
    ) as u_rk
where u_rk.rank =1

综合练习

select
    q2.difficult_level,
    sum(if (q1.result = 'right', 1, 0)) / count(result) as correct_rate  # 主要看这一步统计方式
from question_practice_detail q1 
    left join user_profile u on q1.device_id=u.device_id  # 注意此处连接的顺序
    left join question_detail q2 on q2.question_id = q1.question_id 
where
    university = '浙江大学'
group by
    difficult_level
order by
    correct_rate
Python_听夏
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
牛客SQL刷题 Day-07
12-14
目录 1、获取select * from employees对应的执行计划 2、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分 3、创建一个actor表，包含如下列信息 4、对于表actor批量插入如下...
牛客刷题记录--Mysql
Cheryl_Xu的博客
05-29 211
系列文章目录 提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加 例如：第一章 Python 机器学习入门之pandas的使用 提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录系列文章目录一、简单类1、SQL15 查找employees表emp_no与last_name的员工信息----奇数、为某值 一、简单类 1、SQL15 查找employees表emp_no与last_name的员工信息----奇数、为某值 where emp_no%2=1 and las
开发基础----牛客SQL速成
chirou_的博客
01-09 1260
如何使用非聚合字段在MySQL中，使用非聚合字段的方法非常简单，只需要在SELECT语句中将需要查询的字段列出即可。我们有一个学生表，其中包含学生的姓名、年龄、性别、班级等信息，我们需要统计每个班级的学生数量，那么班级字段就是聚合字段，而姓名、年龄、性别等字段就是非聚合字段。查找每个学校用户的平均答题数目(说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)根据示例，你的查询应返回以下结果（结果保留4位小数），注意：结果按照university升序排序！
【牛客刷题】SQL篇笔记
leo788的博客
10-28 353
※substring_index()函数截取的不是第几个字符而是第一个至第几个字符，想要截取中间位置的字符，可嵌套substring_index()方法。※SQL语句优化也属于数据库优化一部分，而我们的like模糊查询会引起全表扫描，速度比较慢，应该尽量避免使用like关键字进行模糊查询。qpd:id 用户id 问题id 结果(主要结果来源 作为表格保留)其他要求正常查询，最后在判断条件处使用group by分组即可。，将满足条件1和条件2的合在一起不去重。关键字：结果不去重，想到使用联合查询。
【牛客刷题笔记】SQL非技术快速入门篇①
lyy_sunshine的博客
04-16 715
【牛客刷题笔记】SQL篇①
牛客刷题总结.docx
01-28
牛客刷题总结.docx
关于牛客刷题总结全部汇总
02-21
关于牛客刷题总结全部汇总
C++面试--牛客(1).pdf
03-21
C++面试--牛客(1).pdf
剑指offer（java版）----牛客.zip
09-20
剑指offer-牛客-Java版本 。
SQL Server的基本操作示例
最新发布
weixin_58566539的博客
04-29 469
SQL Server的基本操作示例
攻防演练作为红方：postgresql提权之UDF提权 ，及其好用的工具
keyboard专栏
04-28 404
在 PostgreSQL 中，用户定义的函数（UDF）提权是一种常见的提升权限的方法，尤其是当你已经获得了数据库的访问权限但需要更高级别系统权限时。UDF 提权涉及在数据库中创建或使用函数，这些函数可以执行系统级别的命令，从而允许攻击者在数据库服务器上执行任意代码。
SQL底层执行过程
qq_14886109的博客
04-28 810
SQL底层执行过程，INNODB，二阶段提交
优化SQL的方法
weixin_44139651的博客
04-26 1301
使用join实现，但不适合join太多表，阿里巴巴开发者手册的规定，join表的数量不应该超过3个，join表数量太多时，会导致mysql在选错索引。一般SQL优化第一考虑的是索引优化，可使用explain命令，查看MySQL的执行计划，确认SQL是否有走索引。相对连接查询，子查询使用in关键字实现，具有结构化，相对简单，但是需要创建和删除临时表，增加资源消耗。索引可提升SQL效率，但索引需要额外的存储空间，而且还会有一定的性能消耗。left join，两个表的交集，以及左表剩余的数据，左表为驱动表。
SQL的基础语句
qq_51321722的博客
04-22 1537
因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。包含左边表的全部行（不管右边的表中是否存在与他们匹配的行），以及右边表中全部匹配的行。包含右边表的全部行（不管右边的表中是否存在与他们匹配的行），以及左边表中全部匹配的行。
【SQL Server】入门教程-基础篇(二)
m0_74229735的博客
04-29 756
本章要讲的就是Sql Server的高级语言，还有后续。
实验 1--SQL Server2008数据库开发环境
立方体Cube的博客
04-22 488
(2)在 SMSS 对象资源管理器中选中“表”，将列出Report Server 数据库的所有表(包括系统表和用户表)，以用户表 dbo.roles 为例，选中该表，单击鼠标右键，弹出快捷菜单选择“编辑前 200行”命令，打开该表，查看其数据内容。(1)在 SMSS对象资源管理器的树形目录中展开数据库，找到Report Server 数据库并展开，列出该数据库的所有对象，如表、视图、存储过程、默认和规则等;(2)选择数据库Report Server的下级节点，逐一查看各项，并尝试使用;
PostgreSQL 怎么查看记录数据的查看或者变更的数据库日志
keyboard专栏
04-28 560
在 PostgreSQL 中，可以通过多种方式配置和查看记录数据变更或查询的日志。
【SQL】ACID事务与隔离级别
KX_52517的博客
04-29 879
本文是对数据库事务acid特性和隔离级别的介绍。
牛客刷题 jsnode 输入输出
08-25
在牛客网上刷题，使用 JavaScript 和 Node.js 进行输入输出操作是非常常见的。下面是一个简单的示例，演示如何使用 Node.js 读取标准输入并输出结果： ```javascript // 导入内置的 `readline` 模块 const readline = require('readline'); // 创建 `readline` 接口 const rl = readline.createInterface({ input: process.stdin, // 设置输入流为标准输入 output: process.stdout // 设置输出流为标准输出 }); // 提示用户输入 rl.question('请输入一个数字：', (input) => { // 处理用户输入 const number = parseInt(input); // 输出结果 console.log(`输入的数字是：${number}`); // 关闭读取器 rl.close(); }); ``` 使用以上代码，你可以在命令行中输入一个数字，然后将其输出。 请注意，以上示例仅展示了如何进行简单的输入输出操作。在实际的题目中，你可能需要根据具体的需求进行更复杂的输入输出处理。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值