您的当前位置：首页正文

SQL 子查询，连接查询复习

2023-11-10 来源：哗拓教育

lianxi0720go--创建部门表create table bumen( bcode int primary key,--部门编号 bname varchar(20), --部门名称 bceo varchar(20), --部门负责人 btel varchar(20), --部门电话)go--创建人员表create table renyuan( code int primary key identity(10001,1),--人员编号,自增长用identity,从10001开始,每次加1 name varchar(20), --人员姓名 sex varchar(20), --人员性别 age int, --人员年龄 bc int --部门编号)go--设置好外键关系之后来插入数据--先插入部门的数据insert into bumen values(1001,‘财务部‘,‘张三‘,1234567)insert into bumen values(1002,‘销售部‘,‘李四‘,2345678)insert into bumen values(1003,‘人事部‘,‘王五‘,3456789)insert into bumen values(1004,‘技术部‘,‘赵六‘,4567890)go--查看所有信息select * from bumen--插入人员表的信息insert into renyuan values(‘张三‘,‘男‘,33,1001)insert into renyuan values(‘李四‘,‘女‘,27,1002)insert into renyuan values(‘王五‘,‘男‘,25,1003)insert into renyuan values(‘赵六‘,‘女‘,24,1004)insert into renyuan values(‘王祖蓝‘,‘男‘,38,1001)insert into renyuan values(‘马蓉‘,‘女‘,33,1002)insert into renyuan values(‘王宝强‘,‘男‘,36,1003)insert into renyuan values(‘杨颖‘,‘女‘,28,1004)insert into renyuan values(‘郑凯‘,‘男‘,29,1001)insert into renyuan values(‘范冰冰‘,‘女‘,40,1002)insert into renyuan values(‘张伟‘,‘男‘,30,1003)insert into renyuan values(‘蔡依林‘,‘女‘,37,1004)go--查看人员表所有信息select * from renyuan --查询年龄最大的认得部门名称select MAX(age) from renyuanselect bc from renyuan where age=40select bname from bumen where bcode=1002--子查询,查询出的结果不是唯一值的时候把等号改成inselect bname from bumen where bcode=(select bc from renyuan where code=(select code from renyuan where age=(select MAX(age) from renyuan)))--按照升序把年龄排序后的前三个人的所有信息select top 3 * from renyuan order by age--按照升序把年龄排序后的第6,7,8个人的所有信息select top 3 * from renyuan where code not in(select top 5 code from renyuan order by age)order by age--分页查询,要求每页显示5条信息--第一页select top 5 * from renyuan--第二页select top 5 * from renyuan where code not in(select top 5 code from renyuan)--第三页select top 5 * from renyuan where code not in(select top 10 code from renyuan)--总共有几页:count是int类型,5也是int类型,这样结果就会自动显示int类型,所以要把5改成5.0得到的结果取上线.select CEILING(COUNT(*)/5.0) from renyuan--查找销售部里面年龄大于35岁的人的全部信息select * from renyuan where code in(select code from renyuan where age>35 and bc=(select bcode from bumen where bname=‘销售部‘))--查看所有人员信息，将部门编号替代为部门名称select code,name,sex,age,(select bname from bumen where bumen.bcode=renyuan.bc) as 部门 from renyuan--连接查询--将人员表中的信息全部查出,并且该部门编号为部门名称--将每个人的主管添加上select code,name,sex,age,(select bname from bumen where bumen.bcode=renyuan.bc) as 部门,(select bceo from bumen where bumen.bcode=renyuan.bc)from renyuan

create database lianxi0903gouse lianxi0903go--创建供应商表create table gong( gcode int primary key,--供应商编号 gname varchar(20), --供应商名称 gtel varchar(20) --供应商电话)go--创建仓库表create table cang( ccode int primary key,--货品编号 cname varchar(20), --货品名称 cshu int, --货品数量 cprice decimal(18,2), --货品价格 cgc int --货品供应商编号)go--创建门店表create table men( mcode int primary key,--货品编号 mname varchar(20), --货品名称 mshu int, --货品数量 mprice decimal(18,2) --货品价格)go--先建立主外键关系再插入数据--首先插入供应商数据insert into gong values(1001,‘蒙牛‘,‘1234567‘)insert into gong values(1002,‘伊利‘,‘2345678‘)insert into gong values(1003,‘得益‘,‘3456789‘)go--再插入仓库数据insert into cang values(101,‘高钙纯牛奶‘,5,3,1001)insert into cang values(102,‘蒙牛酸牛奶‘,6,2.5,1001)insert into cang values(103,‘优酸乳‘,9,4,1001)insert into cang values(104,‘伊利纯牛奶‘,4,3.5,1002)insert into cang values(105,‘高钙酸牛奶‘,2,2.5,1002)insert into cang values(106,‘大果粒‘,7,4.5,1002)insert into cang values(107,‘得益纯牛奶‘,6,3,1003)insert into cang values(108,‘红枣酸奶‘,5,2,1003)insert into cang values(109,‘低脂酸奶‘,4,2.4,1003)go--再插入门店数据insert into men values(101,‘高钙纯牛奶‘,5,3)insert into men values(102,‘蒙牛酸牛奶‘,6,2.5)insert into men values(103,‘优酸乳‘,9,4)insert into men values(104,‘伊利纯牛奶‘,4,3.5)insert into men values(105,‘高钙酸牛奶‘,2,2.5)insert into men values(106,‘大果粒‘,7,4.5)insert into men values(107,‘得益纯牛奶‘,6,3)insert into men values(108,‘红枣酸奶‘,5,2)insert into men values(109,‘低脂酸奶‘,4,2.4)go--查看所有信息select * from men--查询蒙牛供应商提供的商品中价格最低的商品的所有信息select top 1 * from men where mname like(‘%蒙牛%‘) order by mpriceselect * from men where mprice in(select MIN(mprice) from men where mcode in(select ccode from cang where cgc in(select gcode from gong where gname=‘蒙牛‘)))and mname like(‘%蒙牛%‘)--将仓库表中的供应商编号替换为名称select ccode,cname,cshu,cprice,(select gname from gong where gong.gcode=cang.cgc) as 供应商名称 from cang--将商品的仓库编号,供应商编号添加上,并且供应商编号替换为名称select mcode,mname,mshu,mprice,(select ccode from cang where cang.ccode=men.mcode)as 仓库编号 from men

一、连接查询：通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。

常用的两个链接运算符：

1.join on

2.union

在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，随后通过连接进行查询。

二、子查询，又叫做嵌套查询。

将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。

子查询有两种类型：

一种是只返回一个单值的子查询，这时它可以用在一个单值可以使用的地方，这时子查询可以看作是一个拥有返回值的函数；

另外一种是返回一列值的子查询，这时子查询可以看作是一个在内存中临时存在的数据表。

SQL 子查询，连接查询复习

标签：

小编还为您整理了以下内容，可能对您也有帮助：

关于数据库原理 SQL查询——连接查询相关

其实不能说连接查询有什么优缺点
要非得说的话也许是
当需要即时计算聚集值并把该值用于外部查询中进行比较时，子查询就比连接更具有优势。
如果查询中的SELECT列表所包含的列来自于多个表，那么这时连接的优势要胜过子查询
以下是连接查询
你慢慢看吧

一．自连接查询:
一个表自己与自己建立连接称为自连接或自身连接。
进行自连接就如同两个分开的表一样，可以把一个表的某一行与同一表中的另一行连接起来。
例：
查询选学“101”课程的成绩高于“9505201”号学生成绩的所有学生记录，
并按成绩从高到低排列。
select x.* from sclass x,sclass y
where x.cno=''101'' and x.degree>y.degree and y.sno=''9505201'' and y.cno=''101''
order by x.degree desc
二. 内连接查询
内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。
1、等值连接：
所谓等值连接，是指表之间通过“等于”关系连接起来，产生一个临时表，
然后对该临时表进行处理后生成最终结果。其查询结果中列出被连接表中的所有列，
包括其中的重复列。
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
我们可以有两种方式，这两种是等效的
一种是：SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e,DeptTB AS d WHERE e.deptid=d.deptid
另外一个是：SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e INNER JOIN DeptTB AS d ON e.deptid=d.deptid
3、自然连接：
在等值连接中消除重复列就是自然连接。（state,city在两个表中都存在）
SELECT a.*,p.pub_id,p.pub_name,p.country
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
三外连接查询（左外连接、右外连接、全外连接）
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

A left join B 的连接的记录数与A表的记录数同
A right join B 的连接的记录数与B表的记录数同这种说法是错误的，只有当表A与表B是一对一时才成立。
首先我们做两张表：员工信息表和部门信息表，在此，表的建立只为讲述连接的概念，所以字段非常的简单
EmployeeTB（员工信息表）：

employeeid employeename deptid
0001 张三 01
0002 李四 01
0003 王五 02
0004 赵六 02
0005 郑七 NULL

DeptTB（部门信息表）
deptid deptname
01 技术部
02 市场部
03 工程部
1左外联结
但是有些情况下，我们需要知道所有员工的信息，即使他不属于任何部门。这样我们就可以采用外连接，在这里为左外连接，也就是连接中的左表的表中的记录，无论能不能在右表中找到匹配的项，都要检索，如果没有匹配的项目，那么右表中的字段值为NULL（空），在这里就代表，此员工不属于任何部门。
检索语句为：
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e LEFT OUTER JOIN DeptTB AS d ON e.deptid=d.deptid
检索的结果都是：

employeeid employeename deptname
0001 张三技术部
0002 李四技术部
0003 王五市场部
0004 赵六市场部
0005 郑七 NULL

但是在这里，工程部同样不会被检索，因为，deptname是在连接的右边的表中，“工程部”在左表中不存在任何的记录，所以不会被检索。这里关注的是“连接中的左边的表”

2、右外连接
有时，我们需要知道，全部部门的信息，即使它没有任何的员工。在我们的查询中部门表在连接的右边，如果我们想知道右边表中的所有记录信息，那么就可以采用右外连接，如果此记录在左边的表中找不到匹配项，则相应字段（employeeid,employeename)为NULL
检索语句为：
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e RIGHT OUTER JOIN DeptTB AS d ON e.deptid=d.deptid
检索的结果都是：

employeeid employeename deptname
0001 张三技术部
0002 李四技术部
0003 王五市场部
0004 赵六市场部
NULL NULL 工程部

但在这里，郑七是不会被检索了，因为它在右表中找不到匹配项，这里关注的是“连接中的右边的表”

3、完全外连接
如果我们想知道所有的记录呢？无论员工有没有部门，部门有没有员工，我们都需要检索。这里就可以使用完全外连接。关注连接中的两部分。如果没有部门，部门为空，没有员工，员工信息为空。
检索语句为：
SELECT e.employeeid,e.employeename,d.deptname FROM EmployeeTB AS e FULL OUTER JOIN DeptTB AS d ON e.deptid=d.deptid
检索的结果都是：

employeeid employeename deptname
0001 张三技术部
0002 李四技术部
0003 王五市场部
0004 赵六市场部
0005 郑七 NULL
NULL NULL 工程部
四．交叉连接

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数
据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等
于6*8=48行。

三个表的左连接示例：
准备数据：

表t1
字段名:t1_id,username,psw
表t2
字段名:t2_id,gname,t1_id //这里一个t1_id对应多个t2_id
t3
字段名:t3_id,realname,tel,t1_id //这里一个t1_id对应一个t3_id

关于数据库原理 SQL查询——连接查询相关

请教SQL联合查询和连接查询和子查询每个的特点和应用范围

概念分清就好了。

联合查询：你指union\union all？这个就是多个sql，由于某种目的要把结果集放在一起显示而已，效率按多个sql求和算，应用就是，从水果中查出烂掉的个数，要把苹果烂掉的加上桔子烂掉的个数；

连接查询：多个表连接查询，关系数据库中设计出来的表，一条信息大多是分布在多个表里的，当需要提出一条信息时，就需要连接查询，组装各属性了。效率看sql书写的水平，实际中很常用；

子查询：oracle里，字段上写的子查询，基本（也有不能的，很少）都可以与连接查询转化，当子表很小或子查询走索引的时候，子查询很快，其他情况不如连接查询，好处就是很好看，并且有的应用中，拼装sql的主查询的from后面是固定的，你就只能用子查询关联其他表了；如果是指放在where后面条件语句里的子查询，其实和字段上的子查询一样，只是写成子查询很符合你设计sql的逻辑，因为你是人。效率呢，看sql设计能力，应用就是符合人的思维习惯。

好了，今晚热情散发结束，玩游戏。