第六章 数据表的数据操作(DML)(第二部分)

此篇是select的高级操作

6.1 表连接

　　在实际查询应用中用户需要的数据并不全在一个表中可能存在多个表中这时就需要使用多表查询

　　在数据库应用中,经常需要从多个相关的表中查询数据,这就需要进行表连接

　　使用内部连接

　　　　内部连接是比较常用的连接查询方式.有两种语法形式

--使用where类型的连接
select select_list 
from 表名1,表名2
where 表1.列 = 表2.列


--使用join on类型的连接
select select_list
from 表名1 [inner] join 表名2
on 表1.列 = 表2.列

　　6.1.1 多表查询的连接的原则

　　　　select 子句列表中,每个目标列都加上基表名称

　　　　from 子句查询包括所有使用的基表

　　6.1.2多表查询的案例

　　　　案例1:查询学生学号,性别,以及所在的班级名称和年级

　　　　　　分析:这里用到两个表 学生信息表和班级信息表  其中能将两表的共同属性有 班级编号

-- 写法1 where写法
select 学生信息.学号,学生信息.姓名,学生信息.性别,班级信息.班级名称,班级信息.年级
from 学生信息表,班级信息表
where 学生信息.班级编号 = 班级信息.班级编号

-- 写法2 where写法(简化基表)
select 学生信息.学号,姓名,性别,班级信息.班级名称,年级
from 学生信息表,班级信息表
where 学生信息.班级编号 = 班级信息.班级编号

-- 写法3  jion写法
select 学生信息.学号,姓名,性别,班级信息.班级名称,年级
from 学生信息表 join 班级信息表 
on 学生信息.班级编号 = 班级信息.班级编号

-- 写法4  jion写法 别名写法
select s.学号,姓名,性别,c.班级名称,年级
from 学生信息表 as 's' join 班级信息表 as 'c'
on s.班级编号 = c.班级编号

　　　　案例2:查询籍贯是beijing 班级是以'pu'开头的学生的学号,姓名,性别以及所在的班级名称和年级

select s.学号,姓名,性别,c.班级名称,年级
from 学生信息表 as 's' join 班级信息表 as 'c'
on s.班级编号 = c.班级编号
where s.籍贯 = 'beijing' and c.班级名称 like 'pu%'

 6.2 表的内连接类型

　　一般用inner join 或 join 来确定内连接.内连接又可以被分为1.等值连接,2.非等值连接

　　6.2.1等值连接

　　　　是在连接条件中使用比较运算符等于号 (=),来比较连接列的列值,其查询结果中列出被连接表中所有的列

　　　　例:查询学生学号,性别,以及所在的班级名称和年级

select s.学号,姓名,性别,c.班级名称,年级
from 学生信息表 as 's' join 班级信息表 as 'c'
on s.班级编号 = c.班级编号

　　　　在这个例子中连接条件是 s.班级编号 = c.班级编号所以此为等值连接

　　6.2.2 非等值连接

　　　　是在连接条件中使用比较运算符不为等于号 而使用如> < 这类符号,来比较连接列的列值,其查询结果中列出被连接表中所有的列　 

 　　　　例:查询成绩大于40的学生的信息并按照成绩降序排列

select s.学号,姓名,性别,c.成绩
from 学生信息 as 's' and 成绩表 as 'c'
on s.学号 = c.学号 and c.成绩 >=40
order by c.成绩 desc

　　　　在这个例子中连接条件是s.学号 = c.学号 and c.成绩 >=40,所以此为非等值连接

6.3 使用表的外部连接

　　外部连接会返回from子句中提到的至少一个表格和视图中所有的行

　　外部连接分为1.左外部连接(left outer join),2.右外部连接(right outer join)和3.全外部连接(out join)

　　　　6.3.1.左外部连接:对左表不加限制

　　　　　　使用关键字left out join 对两个表进行连接,左外连接的查询结果包含指定左表的所有行,而不仅仅是连接到列所匹配的行,如果左表的某行在右表中没有找到匹配的行则结果集中在游标的相对位置为null

　　　　　　例:查询出所有学生的学号,姓名,课程标号,成绩

select s.学号,姓名,c.课程编号,成绩
from 学生信息 as 's' left outer join 成绩表 'c'
on s.学号 = c.学号

　　　　6.3.2.右外部连接:对右表不加限制

　　　　　　使用关键字right out join 对两个表进行连接,右外连接的查询结果包含指定右表的所有行,而不仅仅是连接到列所匹配的行,如果右表的某行在左表中美誉哦找到匹配的行则结果集中在游标的相对位置为null

　　　　　　例:根据成绩表查询出学生对应的个人信息

select s.学号,姓名,课程编号,c.成绩
from 学生信息表 as 's' right outer join 成绩表 as 'c'
on s.学号 = c.学号

 

6.4 完全连接

　　使用full outer join 关键字对两个表进行连接,这种连接方式返回左表和右表中所有行.当某行在一个表中没有匹配行是时,则另一个表与之对应的值为null.如果表之间有匹配的行,则震哥哥结果集包含表的数据值

　　查询出每个班的所有课程情况

select class.班级名称,年级,人数,course.班主任,课程名称,开课系列    
from 班级信息 as 'class' full outer join 课程信息 as 'course'   
on class.班级编号 = course.班级编号

 

6.5 子查询(针对多个表)

　　子查询在其他查询的结果的基础上提供了一种有效的方式来表示where子句的条件

　　子查询是可以嵌套在select,insert,update,delete语句中

　　子查询的select查询总是使用圆括号括起来

　　子查询分为两种:嵌套子查询 相关子查询

　　6.5.1嵌套子查询

　　　　在sql中子查询是可以嵌套使用的并且可以在一个查询中嵌套任意多个子查询,使一个子查询中还可以包含另一个子查询,这种查询方式称为嵌套子查询

　　　　例:查询计算机

select * 
from sc
where sno in
(
   select sno
   from student   
   where sdept = '计算机系'
)

　　　　使用子查询进行比较

　　　　例:查询选修了'c02'课程并且成绩高于词课程的平均成绩的学生的学号和成绩

select sno,grade
from 成绩表
where cno = 'c02'
and grade >
(   
   select avg(grade)
   from sc
   where cno = 'c02'   
)

　　　　相关子查询(单值子查询)

　　　　这样的子查询只返回一个值,然后将一列值与查询返回的值进行比较

　　　　例:查询和'ff'在同一个班的学生信息

select s.学号,姓名,性别,c.编辑名称
from 学生信息表 as 's' join 班级信息表 as 'c'
on s.班级编号 = c.班级编号
where s.班级编号 in 
(   
   select 班级
   from 学生信息表
   where 姓名 = 'ff'
)

 

6.6 在查询的基础上创建新表

 　　使用select...into 语句可以再查询的基础上创建新表

　　例:将学生信息表查询的结果保存为学生信息表2

/*
语法:
select 选择列表
into 新表名
from 目标表
*/
select * 
into 学生信息表2
from 学生信息表

　　例:将成绩表查询的结果保存成绩表2

select 学号,成绩
into 成绩表2
from 成绩表