【牛客刷题】SQL篇笔记

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

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值