欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

关系数据库——sql查询

程序员文章站 2022-05-08 17:30:25
...

关系数据库——sql查询

关系数据库——sql查询

关系数据库——sql查询

单表查询

简单的查询操作:

--投影,select后边指明所选的列,from指明所访问的表
select sno, sname, sdept
from student;

--选择指定的列,可以加算术表达式,并且为其添加新的属性名
select sno, 2019-sage as birthday
from student;

--投影后,修改属性名
select sno num, 2019-sage birthday
from student;

--*代表选中所有列
select *
from student;

--除了int,smallint,其余的数据类型需要单引号''
select sname,'2017' year
from student;

--字符串的拼接
--5)查询全体学生的姓名、联系电话,并在前面加上字符串‘联系方式’
select sname, '联系方式'+tel
from student;

--select后加函数
--count函数空值不计,重复值重复计
--当count函数作用在全部列上时以元组计数
select COUNT(sno)
from student;

--COUNT的含义是计数,*表示所有列,COUNT(*)表示元组数,某个或部分属性列为空值不影响count统计结果
select COUNT(*)		
from student;

--去重,distinct作用域是整个元组,是所有指定列组成的元组的去重
select distinct sno, cno
from sc;

--查询性别为女的学生的学号,姓名
select sno, sname
from student
where ssex = '女';

--查询学分为4学分的课程的名字
select cname
from course
where ccredit = 4;

--查询成绩在85分以上的学生的学号(学号不重)
select distinct sno
from sc
where grade > 85;

--查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECT Sname,Sdept,Sage	
FROM Student
WHERE Sage BETWEEN 20 AND 23;

--不能写成sdept='CS','IS','MA'
select sname,ssex
from student
where sdept='CS' or sdept='IS' or sdept = 'MA';

--IS NULL, IS NOT NULL空值:只能用“is”,不能用“=”
--WHERE NOT Sage >= 20;not必须在 sage前边,而不是>=前边

select sname,ssex
from student
where sdept in ('CS','IS','MA');

--模糊查询
--%任意长度,_单个字符,__表示两个或两个以内
--转义字符\,需要加escape '\'标注
select *
from student
where sname like '刘__';

select *	--如果字符中本身带有下划线,用任意字符当转义字符
from Course
where Cname like 'DB\_%i_ _' escape '\';

order by子句:

--order by子句
--选择sno列,从sc表中,cno为'3',按成绩降序给出
--desc表示降序排列,asc表示升序排列,当缺省的时候表示asc
--在SqlServer中当排序的属性列中存在空值的时候,升序排列默认空值在元组最先显示,desc(降序)空值元组在最后显示
select sno
from sc
where cno = '3'
order by grade desc;

--多属性列排序,首先按ccredit进行升序排列,当ccredit相等的时候,按cpno进行降序排列
select *
from course
order by ccredit, cpno desc;

注意:

  • 首先将符合where子句的元组筛选出来,然后根据order by子句进行排序。

聚集函数:

--count统计sc表中sno的数目,空值不计,重复值重复计
--distinct关键字,在计算式取消重复列中的重复值
select count (distinct sno)
from sc;

--统计元组的数目
select count(*)
from sc;

--求该列值的总和
select sum (grade)
from sc;

--求平均值,结果向下取整
select AVG (grade)
from sc;

--求最小
select min (grade)
from sc;

--求最大
select max (grade)
from sc;

注意:

  1. 聚集函数全部都忽略空值
  2. where子句中不能使用聚集函数作为条件表达式,聚集函数只能用在select子句或者group by中的having子句。
  3. 聚集函数作用的元组是满足where子句中的条件的元组

group by子句:

--GROUP BY
--在有GROUP BY的语句中,select子句后边只可以出现分组属性列或者聚集函数,其他列名不可以,
--平均值计算略过空值
--首先按cno进行分组,分别统计每组中sno的数量和每组中的平均值,最后给每一新列起别名
select cno, count(sno) cnt,AVG (grade) av
from sc
group by cno;

--可以使用HAVING短语筛选最终输出结果,作用于组,从中选择满足条件的组
--同上首先通过cno分组,在通过having语句选出满足指定条件的组
select cno, count(sno) cnt, AVG (grade) av
from sc
group by cno
having COUNT(sno)>=2;

--查询选修了3门以上课程的学生学号
--首先使用group by进行分组,然后使用having选择满足条件的组
select sno,COUNT(cno)
from sc
group by sno
having COUNT(cno)>=3

注意:

  1. where子句作用于基表或视图,从中选择满足条件的元组。
  2. having短语作用于组,从中选择满足条件的元组
  3. 使用group by子句后,select子句的列名列表中只能出现分组属性和聚集函数

连接查询

基本概念

  1. 连接谓词中的列名称为连接字段

  2. 连接条件中的各连接字段类型必须是可比的,但不必是相同的

  3. SQL没有自动去掉重复列的功能

基本操作

--from涉及两个表,不加连接条件,得到的是广义的笛卡尔积,select后跟的是最终显示的列
select student.*,sc.* 
from student,sc;

--加上连接条件,得到的是从广义笛卡尔积中选择满足指定条件的元组
--select后跟的是最终所显示的列,对于两个表公共的属性列需要使用表名进行区分,不属于公共列的不需要
--SqlServer没有自动去掉重复列的功能
select student.*,cno,grade  --去掉重复列
from student,sc
where student.sno=sc.sno;  --连接条件:不然是广义笛卡尔积

--或者--
select sc.sno,sname,sage,ssex,sdept,cno,grade   --去掉重复列
from student,sc
where student.sno=sc.sno;

一种可能的执行过程:

1)首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组 。

2)表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组。

3)重复上述操作,直到表1中的全部元组都处理完毕。

自身连接

select first.cno, second.cpno
from course first, course second
where first.cpno = second.cno;

注意:

  1. 由于两张表的所有属性列的名字相同,所以需要起别名进行加以区分。
  2. 上述代码表示将第一张自己表和第二张自己表做广义笛卡尔积,然后选择第一张自己表的cpno等于第二张自己的cno的元组对应的属性列。

外连接

--外连接
select sc.sno,sname,sage,ssex,sdept,cno,grade
from student full outer join sc
on student.sno = sc.sno;

--左外连接
select sc.sno,sname,sage,ssex,sdept,cno,grade
from student left outer join sc
on student.sno = sc.sno;

--右外连接
select sc.sno,sname,sage,ssex,sdept,cno,grade
from student right outer join sc
on student.sno = sc.sno;

注意:

  • 原先的条件where变为on
  • 表名后的外连接操作符指明了主体表。

嵌套查询

基本概念

查询块:一个select-from-where语句

嵌套查询:将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询

子查询不能使用order by子句,因为嵌套查询返回的是一个集合或者布尔值,排序没有任何意义,所以规定不能使用order by语句(胡扯)

一些嵌套查询可以使用连接查询代替,但是一些不可以。

不相关子查询:子查询的查询条件不依赖于父查询

相关子查询:子查询的查询条件依赖于父查询

不相关子查询的可能执行过程:由里向外逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。

相关子查询可能的执行过程:1)首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若where子句返回值为真,则取此元组放入结果表;2)然后再取外层表的下一个元组;3)重复这一过程,直至外层表全部检查完为止。

带有IN谓词的子查询

一个典型的例子:

关系数据库——sql查询

该查询为不相关子查询,即查询过称为,1)在course表中查找出信息系统的课程号;2)根据查找出的课程号,在sc表中查询出该学生的学号;3)根据查询到的学号,在student表中查询出相应学生的姓名。

带有比较运算符的子查询

适用条件:当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!=或< >)

例如:

找出每个学生超过他选修课程平均成绩的课程号。

    SELECT Sno,  Cno
    FROM  SC  x
    WHERE Grade >=
               (SELECT AVG(Grade) 
                FROM  SC y
                WHERE y.Sno = x.Sno);

查询过程:

关系数据库——sql查询

关系数据库——sql查询

该例为一个相关子查询。

带有any或者all谓词的子查询

声明:

any:表示任意一个就行,> any表示大于任意一个就可以

all:表示所有,> all表示需要大于所有的值

常见的谓词解释:

关系数据库——sql查询

 

 

带有EXISTS谓词的子查询:

介绍:

存在量词

exists

1)不返回任何数据,只返回true或者false。当内层的查询非空时返回true,当内层的查询为空时返回false。

2)由EXISTS引出的子查询,其目标列表达式通常都用*,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义(还是胡扯)

not exists

1)若内层查询结果非空,则外层的WHERE子句返回假值

2)若内层查询结果为空,则外层的WHERE子句返回真值

例子:

exists的例子:

关系数据库——sql查询

 

not exists的例子:

关系数据库——sql查询

难点1:使用exists/not exists实现全称量词

1)查询选修了全部课程的学生姓名

--说法转换:即对于某个学生来说,没有哪一门课程,使自己没有选的
select sname
from student
where not exists(
	select *
	from course
	where not exists(
    	select s*
    	from sc
    	where student.sno = sno and cno = course.cno));

解释:

(1)对于第一个not exists里边查询的是当前student是否有未选修的课程,如果当前学生有未选修的课程,经过not exists返回false,即该学生的信息不会被记录。

(2)对于第二个not exists里边查询的是当前学生对象,对于当前的课程,如果选了该门课程,not exists里边为真,经过not exists返回false,该门课程不会被记录,反之,如果该学生未选该门课程,该门课程将会被记录。

(3)有一点像双重for循环,依次遍历所有的student中的元组,在每一个student的情况下,在遍历course,最后在一个not exists中的where子句中进行判断。

难点2:使用exists/not exists实现逻辑蕴涵

2)查询至少选修了学生201215122选修的全部课程的学生号码。

SELECT  DISTINCT Sno
FROM  SC SCX
WHERE NOT EXISTS
	(SELECT *
	 FROM SC SCY
	 WHERE SCY.Sno = '201215122'  AND
	 NOT EXISTS
		(SELECT *
		 FROM SC SCZ
		 WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno));

解释:

(1)基本同上

集合查询

并操作:

查询计算机科学系的学生及年龄不大于19岁的学生。

/*使用UNION取并集*/
SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

交操作:

查询计算机科学系的学生与年龄不大于19岁的学生的交集(INTERSECT)。

/*使用INTERSECT实现交操作*/
SELECT *
FROM Student
WHERE Sdept='CS' 
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;

差操作:

查询计算机科学系的学生与年龄不大于19岁的学生的差集。

/*使用EXCEPT实现差操作*/
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT  *
FROM Student
WHERE Sage <=19;