一直玩一直爽,补补基础火葬场
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