数据库6——sql的数据查询操作

数据查询

数据查询使用select语句，select语句的一般格式为：

select [all / distinct] 目标列表达式
from 表名
where 条件表达式
group by 列名 having 条件表达式  /* 聚合函数，属性值相同的为一组*/
order by 列名 [asc / desc] ;   /* 对结果排序 */

单表查询

查询表中若干列

关系代数中的投影运算。

select Sno,Sname 
from Student;

/*  查询表中全部列 使用通配符 “ * ”   */
select *
from Student;

/* 查询经过计算的值 */
select Sname,year(getdate()) - Sage /*查询姓名和出生年份 */
from Student;

/* 为查询出的列起别名*/
/* 查询出 NAME 和 BIRTHYEAR 两列*/
select Sname NAME,year(getdate()) - Sage AS BIRTHYEAR
from Student;

选择表中若干元组

/* 取消取值重复的行 */
select distinct Sno 
from Student;

/* 查询符合条件的元组 */
/* 通过where子句实现 */
select Sname
from Student
where Sname = '王二狗';

where子句常用的查询条件

其中like / not like 为字符匹配，后面跟匹配串，匹配串可以是字符串，其中可以有通配符“%”、“_”。
“ % ”代表任意长度（可以为零）的字符串，例如a%b代表以a开头，b为结尾长度任意的字符串。
“_”代表一个字符，例如a_b代表以a为开头，b为结尾的长度为3的字符串。
当字符串中有“%”或者“_”是，为了防止系统认为是通配符，要使用换码字符将通配符转义为普通字符，例如：

select Uname
from _user
where Uname = '%\_%' escape '\';/*查询用户名中有 _ 的用户名 */

order by 子句

对查询结构按照一个或多个属性列进行升序(ASC)或降序(DESC)排序，缺省值默认为升序。对于空值，如按升序排序，空值的元组将最后显示，如按降序排序，空值的元组将最先显示。

/* 查询全体学生情况，并按照系号排序，同一系的学生按年龄排序*/
select *
from Student
order by Sdept,Sage desc;

聚集函数

/* 查询选修了课程的学生人数 */
select count(distinct Sno)
from SC;

group by子句

如果查询所有学生的平均成绩，可以用聚集函数，但是当要查询每个学生的平均成绩时，仅仅使用聚集函数不能实现，这时就需要group by子句来细化聚集函数的作用对象。

未对查询结果分组，聚集函数作用于整个查询结果。
对查询结果分组后，聚集函数将分别作用于每个组。

注意：
1.使用group by子句后，select子句的目标列表达式列表只能出现分组属性和聚集函数。
2.若在分组后，还要按照一定的条件进行筛选，则需要使用having子句。

/* 查询每门课的选课人数 */
select Cno,count(*)
from SC
group by Cno;/* group by 子句中的属性名应出现在分组的属性中 */

/* 查询每个系男女生的平均年龄 */
select sdept,ssex,AVG(sage)
from student
group by sdept,ssex;

/* 查询选修了4门课以上的学生学号 */
select Sno from SC
group by Sno
having count(*) > 3;

/查询成绩90分以上的课程超过1门的学生学号和（90分以上的）课程数 */
select Sno,count(*)
form SC
where Grade >= 90
Group by Sno
Having Count(*) >= 2;
 /* 当同时有 where、group by、having 子句时，顺序为 where -- group -- having*/

多表查询

连接查询

在from子句中指定查询要涉及哪些表。
在where子句中指定连接两个表的条件（即连接谓词）。

连接操作是从笛卡尔积中选择满足条件的元组。

连接谓词的一般格式：

表名1.列名1 比较运算符 表名2.列名2;
/* 或 */
表名1.列名1 between 表名2.列名2 and 表名2.列名3;

·n个表进行连接查询，要有n-1个连接谓词。
·当列名是同名时，必须加表名区分，如果列名不同，可以省略表名。

连接操作的类型：

• 内连接：结果表只显示符合连接条件的元组，又分为等值连接，非等值连接和自身连接。
• 外连接：结果表会显示不符合连接条件但符合查询条件的元组（无数据的属性值补NULL），又分为左外连接、右外连接和全外连接。
• 交叉连接：交叉连接不带where子句，返回两个表的笛卡儿积。

/*
内连接：分为等值连接，非等值连接和自身连接
*/
/* 等值连接和非等值连接 */
select Student.* , SC.*
from Student,SC
where Student.Sno = SC.Sno;
/* 自身连接：需要使用别名区分 */
select first.cno,second.cpno
from course first, course second
where first.cpno = second.cno;

/*
  外连接：选择一个表为主体表，将主体表没有满足连接条件的元组也输出，没有属性值的补null.
  
  选择主体表可以选左面的，右面的，也可以两个都选择，对应的连接就是左外连接，右外连接，全外连接。
  
  外连接关键词的outer可以省略，使用on来表明连接条件。
*/
/* 左外连接 */
select s.sno sname,ssex,sage,sdept,cno,grade
from (student s left outer join sc on s.sno = sc.sno);
/* 右外连接 */
select s.sno,sname,ssex,sage,sdept,cno,grade
from (student s right outer join sc on s.sno = sc.sno);
/* 全外连接 */
select *
from (student full outer join sc on student.sno = sc.sno);

/*
  交叉连接：返回笛卡儿积
*/
select sno,sname,cno,cname
from student,course;
/*或*/
select sno,sname,cno,cname
from student cross join course;

连接操作执行过程
循环嵌套法:

• 在表1中找到第一个元组
• 从头扫描表2的元组，找到符合连接条件的元组，与表1的元组拼接后成结果表的元组。
• 在表1中寻找剩下的元组，重复第二步，直到处理完毕。

排序合并法：
常用于 = 连接

• 首先对表1和表2进行排序。
• 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，拼接起来形成结果的第一个元组，或者第一次遇到大于的元组，查询不再继续，进行下一步。
• 对表1之后元组，在表2中从上一步的中断点开始查找，找到满足条件的元组或者大于的元组，重复此步骤，直到表1中所有元组处理完毕。

索引连接：

• 对表2按连接字段建立索引
• 对表1中的每个元组，依次根据其连接字段查询表2的索引，从中找到满足条件的元组，找到后就将表1中的相应元组与该元组拼接起来，形成结果表中一个元组。

嵌套查询

定义：

• 查询块：一个select–from–where语句称为一个查询块。
• 嵌套查询：将一个查询块嵌套到另一个查询块的where子句或者having补语的条件中的查询称为嵌套查询。

作用：

• 嵌套查询是我们可以用多个简单查询构成复杂的查询，从而增强sql的查询功能。

限制：

• 子查询不能使用order by 子句。

例如：

/* 选择课程号为 2 的学生名字 */
select Sname
from Student
where Sno in /*外层查询 / 父查询 */
(select Sno  /*内层查询 / 子查询 */
from SC
where Cno = '2');

分类：

• 不相关子查询：子查询的查询条件不依赖于父查询。求解方法：由里向外逐层处理。
• 相关子查询：子查询的查询条件依赖于父查询的而某个属性值。求解方法：由外向里，依次选取父查询表中的元组看是否符合子查询条件。

带有IN谓词的子查询

如果子查询的返回值不止一个，而是一个集合，可以采用谓词IN，或者在比较运算符和子查询之间插入ANY或ALL。

例如：

/*
  查询与“刘晨”不在同一个系学习的学生。
*/
select Sno,Sname,Sdept
from Student
where Sdept not in
(select Sdept
from Student
where Sname = '刘晨');

带有比较运算符的子查询

当已经知道子查询的返回值一定只有一个时，可以使用比较运算符（ = ，>，<，>=，<=，!=）将父查询和子查询连接起来。

例如：

/*
  找出与200215121同龄的学生
*/
select *
from Student
where sage =  /*子查询一定要放在比较运算符后面 */
(select sage
from Student
where sno = '200215121');

带有ANY(SOME)或ALL谓词的子查询

谓词语义：

• any：任意一个值
• all：所有值

需要配合使用比较运算符：

父查询与多值子查询之间的比较需要用any来连接。

例如：

/*
  查询其他系中比信息系某一学生年龄小的学生姓名及年龄。
*/
select Sname,Sage
from Student
where Sage < any
(select Sage
from Student
where Sdept = 'IS')
and sdept <> 'IS';

/*
  查询其他系中比信息系所有学生年龄都小的学生姓名及年龄
*/
select Sname,Sage
from Student
where Sage < all
(select Sage
from Student
where Sdept = 'IS')
and Sdept <> 'IS';

带有EXISTS谓词的子查询

exists谓词：

• 存在谓词，带有exists谓词的子查询不返回任何数据，只返回’true’或者’false’.
• 若查询结果为空，外层where子句返回假值，若为非空，则为真值。
• exists的子查询，通常目标列表达式用 * ，因为它只返回真值或假值，所以给出列名无意义。

例如：

/*
  列出选修了1号课程的学生学号、姓名
*/
select Sno,Sname
from Student
where exists(
	select * 
	from SC
	where SC.Sno = Student.Sno and Cno = '1'
);

不同形式的查询间的替换：

• 一些带exists后not exists谓词的子查询不能被其他形式的子查询等价替换。
• 所有带in谓词、比较运算符、any和all谓词的子查询都能用带exists谓词的子查询等价替换。