SQL笔记-CSDN博客

本文链接：https://blog.csdn.net/qq_40088655/article/details/124848870

SQL笔记

DDL

DDL，Data Definition Language，数据库定义语言。通过DDL语句定义不同的数据段、数据库、表、列、索引等数据库对象的元数据信息。通常由数据库管理员（DBA）使用。
DDL常用的功能是增删改，主要语法有CREATE, ALTER, DROP。主要作用的对象有database, table, view, index, column, event, trigger等。DDL不需要COMMIT就可以完成任务。

CREATE 创建表

CREATE TABLE [table_name] (字段名 数据类型，... ..., ...)

e.g.

CREATE TABLE player (
	player_id int(11) NOT NULL AUTO_INCREMENT, #最大有效显示长度为11的整数，不能为空，自动增长
	player_name varchar(255) NOT NULL #最大长度255的可变字符串
);

通过工具

删除数据

DROP

DROP命令用于彻底删除整个数据库或表，删除后无法恢复，使用应非常小心。

# 删除数据库
DROP DATABASE database_name;
# 删除表
DROP TABLE table_name;

DELETE

DELETE命令用于删除表格中的行，可以根据特定条件删除部分数据。DELETE的删除操作会留下一些空间，数据被标注为“已删除”，并未实际删除。即数据在逻辑上已被删除，但物理上依然存在。

# 删除名字为John的行
DELETE FROM table_name where name = 'John';

TRUNCATE

TRUNCATE与DELETE命令类似，用于删除表格中的行，但TRUNCATE是删除删除表格中所有行，并释放占用的空间。TRUNCATE不删除表结构，还是将表格重置为初始状态，无法像DELETE一样删除部分数据。

# 删除表格中的所有数据并释放空间
TRUNCTE TABLE table_name;

排序

MySQL默认ORDER BY为正序(从小到大)，即ASC排序使用order by，倒序desc（从大到小）。

select 列名 from 表名 order by 排序的列名 DESC/ASC

多字段排序

order by column_name1 ASC，column_name2 DESC;

比较符号

= 等于， >= 大于等于， <= 小于等于，<>不等于，!< 不小于，!= 不等于，!> 不大于

联查

JOIN联查

join字段独立出现是等价于inner join的。

例子：

#1
select * from a join b on a.id = b.id
#2
select * from a inner join b on a.id = b.id
#3
select * from a, b where a.id = b.id

这三种写法是等价的。

试题：
设有一张学生表student，其中学生编号s_id, 学生姓名s_name, 学生出生年月s_birth, 学生性别s_sex；

设有一张成绩表，设有学生编号s_id，课程编号c_id，成绩得分s_score；

student、score表如下，想要找出01课程比02课程成绩高的学生信息及课程分数：
在这里插入图片描述

答：
第一种写法：
先联查student与01编号课程，再联查02编号课程，最后寻找s_id对应与成绩大于的；

select student.*, a.s_score as 01_score, b.s_score as 02_score
from
student join score a
	on a.c_id = '01'
join score b
	on b.c_id = '02'
where student.s_id = a.s_id 
	and student.s_id = b.s_id
	and a.s_score> b.s_score

（推荐）第二种写法：
先联查student与01课程s_id对应的，再联查与02课程s_id对应的，最后在其中寻找01乘积大于01成绩的；

select student.*, a.s_score as 01_score, b.s_score as 02_score
from 
student join score a
	on student.s_id = a.s_id and a.c_id = '01'
join score b
	on student.s_id = b.s_id and b.c_id = '02'
where a.s_core > b.score

这里两种写法的区别就是s_id对应的位置，第一种，是在联查后的结果通过where进行s_id的对应；第二种s_id的对应是在联查的时候进行；

注：不加限制条件的联查

这里如果写为：

select student.*, a.s_score as 01_score, b.s_score as 02_score
from 
student join score a
	on a.c_id = '01'
join score b
	on b.c_id = '02'

查询结果：
inner join会从最后的一个join为基准，从后向前逐级遍历。如这里联查的顺序为student、score a（a.c_id = '01'）、score b（b.c_id = '02'）,遍历的层序为从最后向前，先保持score b的值，再保持score a的值，最后遍历student的值；这里一共有四个人，每个人都有一份01成绩、02成绩（4份01成绩，4份02成绩），所以一共就有4 * 4 * 4 = 64条数据；
在这里插入图片描述
如果写为left join:

select student.*, a.s_score as 01_score, b.s_score as 02_score
from 
student left join score a
	on a.c_id = '01'
left join score b
	on b.c_id = '02'

查询结果：
在这里插入图片描述
联查就会以最上层的left join开始，这里是从student先开始遍历，再从score a (a.c_id = '01')，最后score b (b.c_id = '02');

这里比较特殊的情况是right join，这里只进行一级联查，方便理清思路。

select student.*, a.s_score as 01_score
from 
student right join score a
	on a.c_id = '01'

在这里插入图片描述
我们正常希望得到的结果数量是16，但是这里我们却得到了24条结果。每4个结果之后就有除了score字段其他字段全为null的字段。通过数值我们可以看出，这两个字段是c_id = '02', c_id = '03'字段的，因为在联查添加了限制条件on c_id = '01'，本来不应该显示其他俩个科目（c_id = '02', c_id = '03'）的字段，但是由于right join的性值会将所有score显示，将联查右侧socre的字段全部显示了出来，所以左侧非on c_id = '01'的字段直接显示null了。

我们可以改写为如下，left join与right join联查一定要加上on限制条件，这里设置了1 = 1，直接进行两个表的笛卡尔积（乘法遍历联查），再在联查后的结果通过where进行a.c_id = ‘01’的限制，可以看到4 * 4 = 16条的效果如下。

select student.*, a.s_score as 01_score
from 
student right join score a
	ON 1 = 1
where a.c_id = '01'

在这里插入图片描述
上述的步骤并不是联查的最终结果，只是为了让大家更好的理解联查的过程：）

SQL35.统计浙大不同题目的正确率

在这里插入图片描述

三个表，希望统计返回’浙江大学’的不同题目难度的正确率，按正确率升序排列。

答：三表联查

select difficult_level, 
        sum(if(qpd.result='right', 1, 0)) / count(*) as correct_rate
from
    (user_profile u
    inner join question_practice_detail qpd
        on u.device_id = qpd.device_id
    inner join question_detail qd
        on qpd.question_id = qd.question_id)
where u.university = '浙江大学'
group by difficult_level
order by correct_rate

详解：

1.先三表联查，对应好用户设备id，答题id

from
	(user_profile u
    inner join question_practice_detail qpd
        on u.device_id = qpd.device_id
    inner join question_detail qd
        on qpd.question_id = qd.question_id)

2.限定用户大学，之后进行对题目难度进行分组

where university = '浙江大学'
group by difficult_level

3.计算难度题目正确率，先通过if(条件, 满足, 不满足)对答题是否正确进行判断，若正确+1，再对所有结果加和sum(if(qpd.result='right', 1, 0))。再除以此难度的所有题目count(*)，即为当前难度的正确率

select difficult_level, 
        sum(if(qpd.result='right', 1, 0)) / count(*) as correct_rate

4.最后按正确率升序排列

order by correct_rate

SQL34 统计复旦用户8月练题情况

在这里插入图片描述

两表联查希望得到如下结果，复旦大学每个用户的练题情况

答：

这道题和上一道题很像，先给出答案

select
  up.device_id,
  university,
  count(*),
  sum(if(result = 'right', 1, 0))
from
  (
    user_profile up
    inner join question_practice_detail qpd on up.device_id = qpd.device_id and university = '复旦大学'
  )
# 如果ineer中没有加大学的限制条件，需要在外部通过where来限定大学
# where
#   university = '复旦大学'
group by
  device_id

这里需要说明的是，当两个表联查时，如果用到的字段唯一，可以只写字段名，不用表明是哪个表的字段。 若两个表都拥有的字段，比如device_id，在select中声明过是要哪个表的，在后面的group by中，可以不用指出表名，直接标明字段即可。

UNION ALL组合查询

在这里插入图片描述

返回信息是要求不去重的

使用UNION可以实现多个查询结果集合合并为一个结果集，查询语句中对应列的数据类型必须兼容，ORDER BY放在最后一个查询语句的后边。在这里插入图片描述

答：

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'

case

CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

在这里插入图片描述

SQL26 计算25岁以上和以下的用户数量

在这里插入图片描述

答:

select case when age < 25 or age is null then '25岁以下'
            when age >= 25 then '25岁及以上'
            end age_cut,
       count(*) number
from user_profile
group by age_cut

查询两个字段，age_cut通过case进行提取。第二个number分别统计两种情况下的数量，通过group by分组，再通过count(*)函数统计。

GROUP BY分组

项目中用过分组函数吗，SQL语句中select, from, where, group by, having这几部分的执行顺序是怎么样的？

答：先是from确定数据从哪张表来，然后是where基于指定条件对数据进行筛选过滤，再是group by将筛选后的数据划分多组（分组条件可以有多个，按字段顺序依次分组），再是having对结果集进行过滤，最后是select数据呈现出来。

SQL18 多分组计算

在这里插入图片描述

答：

select
    gender,
    university,
    count(*) as user_name,
    avg(active_days_within_30) as avg_active_days,
    avg(question_cnt) as avg_quesion_cnt
from user_profile 
group by gender, university

这里使用了两个分组字段，gender，university，gender中有两种情况male，female会分别再对university进行分组，male有的是北京大学、吉林大学…，female有的是北京大学、吉林大学…。这样就对同一个大学中的不同性别进行数据统计了。

最开始尝试只通过gender进行分组，select gender, university中这样是不可取的，因为university是多样的（可能是北京大学，吉林大学,），只对gender进行分组，只能分出男女两行数据，这样是无法直接显示university是什么的。

EXISTS IN

in 只能返回一个字段，not in是in的对立，对于较小的表和少量值情况，是简单高效的方法；对于大型表和大量值，会导致性能问题，因为需要遍历整个表判断每个值是否匹配。

select column_name
from table_name
where column_name in (value1, value2, ...);

EXISTS 使用子查询执行条件筛选，返回字段无数量要求，因为exists强调的是是否返回结果集，不要求知道返回什么。not exists是exists的对立。此方法避免了构建大型临时列表的开销，处理大型表和大量值情况更加高效。

select column_name
from table_name
where exists(
	select column_name2 from table_name2
	where condition
)