sql查询

h3110_w0r1d

发布于 2024-02-19 19:15:15

1090

发布于 2024-02-19 19:15:15

文章被收录于专栏：用户9703952的专栏用户9703952的专栏

连接查询

注意

from字句后面的表名，可以用表原名，也可以为它起别名，一旦有了别名，整个查询语句中凡是涉及用表名的地方都要用表原名
查询语句中出现的所有列，若在所有涉及的表中是唯一的，则列明前可以不加表明前缀
如果不是唯一的，为了确定它的唯一性，必须在列名前加表名前缀

等值连接和非等值连接

两个表进行连接时，必须要有可比字段，两个可比字段的值进行逐一比较来决定当前两个元组是否可以连接

等值连接不会去重

select student.*,SC.*
from student,SC
where student.sno = SC.sno;

select SC.sno,SC.score,SG.grade
from SC,SG
where SC.score BETWEEN SG.minscore AND SG.maxscore;

内连接

显示内连接

on来连接两个连接列，where中写连接条件

显示内连接用inner join

内连接又叫自然连接，会去掉重复列的等值连接

select 目标列列表
from 表1 inner join 表2
on 表1.连接列=表2.连接列
where 查询条件

隐式内连接

where写连接列，and后跟条件

隐式内连接用’=’

select 目标列列表
from 表1，表2
where 表1.连接字段=表2.连接字段
and 其它查询条件

外连接

左外连接

以左边的表为基础，根据ON后面给出的两表的条件将两表连接起来

结果会将左表所有查询信息列出，右表只会列出ON后条件和左表满足的部分

语句left join… on

select a.device_id,question_id,result,level,gender,age
from
(
    select device_id,question_id,result,level
    from question_practice_detail
)a-表别名
left join
(
    select device_id,gender,age
    from user_profile
)b-表别名
on a.device_id=b.device_id;

左外连接的注意事项

在使用left join时，写在前面的表为匹配时的底表，使用on给出匹配条件，匹配条件可以不唯一

在表匹配时，一般我们会将要匹配的两段查询用括号括起来，并在括号末尾给予一串字母，作为表的别名

左外连接例题

select
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id),4) as avg_answer_cnt-- round(x,4)将x四舍五入至小数点后四位
    
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

group by university,difficult_level;-- group by university按学校分组，group by difficult_level按不同难度的用户平均答题量

右外连接

左外连接的反向连接

Select a.device_id,question_id,result,level,gender,age
from
(
Select device_id,gender,age
From user_profile
)a -- 表别名
Right join
(
Select device_id,question_id,result,level
From question_practice_detail
)b -- 表别名
On a.device_id = b.device_id —给出链接条件

全外连接

将左右两个表先左外连接，在右外连接，然后两个结果并在一起

select 目标列列表
from 表1 full join 表2
on 表1.链接列=表2.链接列
where 查询条件

自身链接

链接操作不仅可以在多个表之间进行链接，而且可以时一个表与其自身进行连接，这样的表称为自身连接

因为两个表的表名和列名都相同，为了加以区分，必须给他们起两个别名，且各列前必须要有****表别名前缀

select FIRST.cno,SECOND.cpno
from course FIRST inner join course SECOND
on FIRST.cpno = SECOND.cno;

笛卡尔积

在写表和表的连接关系时，大多数情况下都必须要限制匹配条件，如果在匹配时没有限制匹配条件，这时就会导致笛卡尔积。
笛卡尔积时关系代数里一个概念，表示两个表中的每一行数据任意组合
假设A中有n行数据，b中有m行数据，没有限制匹配条件的话，最终输出的结果就会有n*m行数据
在互联网大体量数据情况下，每个表可能有几百万几千万行数据，几千万*几千万最终得到的结果行数会是天文数字，所以在写匹配时，一定不要忘了加上匹配条件。

全称量词命题和存在量词命题

嵌套查询

嵌套查询的工作方式

先处理内查询，由内向外处理

外层查询利用内层查询的结果

select tag,count(tag) as tag_cnt
from exam_record
join examination_info on exam_record.exam_id = examination_info.exam_id
where uid in (
    select uid
    from exam_record
    where submit_time is not null
    group by score
    having count(exam_id) / count(distinct DATE_FORMAT(start_time,"%Y%m")) >= 3
)
group by tag
order by tag_cnt desc;