【牛客刷题】SQL篇笔记

lexi7a

已于 2023-10-23 19:00:08 修改

阅读量425

点赞数 1

分类专栏：网络安全文章标签： sql

于 2022-10-28 14:36:34 首次发布

本文链接：https://blog.csdn.net/leo788/article/details/127544137

版权

网络安全专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一直玩一直爽，补补基础火葬场

SQL

知识点补充

组成（四部分）

DDL(Data Definition Language): 数据定义语言（对结构进行修改的）
用来定义数据库对象:数据库，表，列等。关键字：create,drop, alter等。

CREATE

DROP删除整个结构(删除处理效率：drop》truncat》delete)
DROP VIEW 删除视图
ALTER（add、modify、change、drop）
增加列：ALTER TABLE 表名 add 字段名
修改列：ALTER TABLE 表名 modify 字段名
删除列：ALTER TABLE 表名 drop 字段名

DML(Data Manipulation Language): 数据操作语言
用来对数据库中的表进行增删改操作。关键字：insert,delete,update等。

DQL(Data Query Language): 数据查询语言
用来查询数据库中表的记录(数据)。关键字:select, where等。

select <字段名> from <表或视图名> where <查询条件>

DCL(Data Control Language): 数据控制语言
用来定义数据库的访问控制权限和安全级别，及创建用户。关键字: grant, revoke（收回权限），rollback，commit等

GRANT：授权
ROLLBACK ：SQL>ROLLBACK，回滚回某一状态
COMMIT：提交

去重结果查询

在这里插入图片描述
使用关键字distinct得到不重复的结果。

查询结果限制返回行数

SELECT device_id from user_profile limit 2
limit关键字使用：

默认其顺序
//表示选取前n行
limit 0,n  
limit n
//表示选取中间n-m行
limit m-n offset n

将查询后的列重新命名

SELECT device_id as user_infos_example from user_profile limit 2
as关键字的使用：

字段名 [as] 别名 //其实as可以省略

查找除条件外信息

where 字段名 [NOT] in （‘条件’）
等价于
where 字段名！=‘条件’
//查询年龄不为空时
where age !=''
where age is not NULL
注意：在MySQL中不能使用 !=NULL的用法进行不为空的查询。（IS NULL/IS NOT NULL）

模糊查询

方法一：like关键字的使用：
like '%北京%'
※SQL语句优化也属于数据库优化一部分，而我们的like模糊查询会引起全表扫描，速度比较慢，应该尽量避免使用like关键字进行模糊查询。
方法二：REGEXP关键字的使用：
※使用REGEXP运行速度会更快一点。
SELECT device_id,gender,age,university,gpa FROM user_profile where university REGEXP '北京'

查找最高值（排序）

方法一：统计函数使用(不能嵌套使用)

函数名称	描述
COUNT（条件）	统计满足条件的个数
COUNT(*)	统计行数,包括空值所在行数
COUNT(1)	统计行数，效果同COUNT(*)
COUNT(列名)	统计行数，排除空值行数
SUM（）	计算列值总和
AVG（）	计算列值平均值
MAX（）	计算列值最大值
MIN（）	计算列值最小值
ROUND（）	为结果保留一位小数

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

方法二：递减排序后取第一位
order by 字段名 desc LIMIT 1
order by不加desc时即为递增（升序）排序。
多列排序：

order by A,B
order by A desc,B desc

分组查询

GROUP BY 字段名其他要求正常查询，最后在判断条件处使用group by分组即可。

SELECT
  gender,
  university,
  COUNT(*) user_num,
  AVG(active_days_within_30) avg_active_day,
  AVG(question_cnt) avg_question_cnt
FROM
  user_profile
group by
  gender,
  university;

分组过滤查询
HAVING子句用于对分组后的结果进行过滤
※Having里可以使用统计函数但是Where里不能使用。

关联表（子查询）

在这里插入图片描述

SELECT
  qpd.device_id,
  qpd.question_id,
  qpd.result
FROM
  question_practice_detail as qpd
  inner join user_profile as up on up.device_id = qpd.device_id
  and up.university = '浙江大学'
order by
  question_id;

** join关键字的使用：**

inner join 子查询（包含关系）
inner join <表名> on <连接条件>
SELECT * FROM 学生表
INNER JOIN 班级表 ON 学生表.班号=班级表.班号
on代表inner join关联条件
//除此之外还有left join和right join，分别是以左表为基准和以右表为基准。
左表及右表的拼接是相同行数的拼接。

left join a
在这里插入图片描述
left join b
full join(有点像^)，mysql不支持

在这里插入图片描述


排序条件：多学科总分最高sum（score）
select student_table.*,sum(score) from{
select stu_id,sum(score) from score_table  order by sum(score) desc limit 1
}
join student_table 
on student_table.id=score_table.stu_id

在这里插入图片描述

select Adep，sum（Score） form{
select sum（SCore）from score 
}
join athlete

分别查看&结果不去重

关键字：结果不去重，想到使用联合查询union all，将满足条件1和条件2的合在一起不去重。去重就使用union。

//SQL25
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'

条件函数

SQL 入门 26 #计算25岁以上和以下的用户数量#
case关键字的使用：

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

SELECT
  case
  WHEN age<'25'or age is NULL THEN "25岁以下"
  WHEN age>='25' THEN "25岁及以上"
  END as age_cut,COUNT(*)number
FROM
  user_profile
group by
  age_cut

日期函数

按天分组：group by date

日期函数	描述
DAYOFWEEK（date）	输出为给定年月日的星期
WEEKDAY（date）
DAYOFMONTH(date)
YEAR(date)	输出为年份
MONTH(date)	输出为月份

文本函数

条件：需要在字段内截取文本
substring_index()关键字的使用：
在这里插入图片描述
※substring_index()函数截取的不是第几个字符而是第一个至第几个字符，想要截取中间位置的字符，可嵌套substring_index()方法。

比较难的题—及其分析

//SQL35.了解浙江大学的用户在不同难度题目下答题的正确率情况
up:id 用户id 性别年龄学校 gpa 活跃天数问答数回复数
qpd:id 用户id 问题id 结果(主要结果来源作为表格保留)
qd:问题id 难度
正确率计算：(答对)/(同一难度用户答题总数)
sum(if(qpd.result='right',1,0))/count(qpd.device_id)

SELECT
  qd.difficult_level,
  sum(if(qpd.result='right',1,0))/count(qpd.device_id)correct_rate
FROM
  question_practice_detail as qpd
  left join user_profile as up on up.device_id = qpd.device_id
  left join question_detail as qd on qd.question_id=qpd.question_id
where
  up.university='浙江大学'
GROUP BY qd.difficult_level
ORDER BY correct_rate

窗口函数

在这里插入图片描述

over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
partition by 列名，分区与group by 类似
order by 列名，排序
rows between 开始位置 and 结束位置，指定窗口范围
rank()按照值序产生一个自增编号，值相等时会重复，会产生空位

外连接

left join

lexi7a

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【牛客刷题】SQL篇笔记

※substring_index()函数截取的不是第几个字符而是第一个至第几个字符，想要截取中间位置的字符，可嵌套substring_index()方法。※SQL语句优化也属于数据库优化一部分，而我们的like模糊查询会引起全表扫描，速度比较慢，应该尽量避免使用like关键字进行模糊查询。qpd:id 用户id 问题id 结果(主要结果来源作为表格保留)其他要求正常查询，最后在判断条件处使用group by分组即可。，将满足条件1和条件2的合在一起不去重。关键字：结果不去重，想到使用联合查询。
复制链接

扫一扫

专栏目录