sqlserver查询（子查询，全连接，等值连接，自然连接，左右连，交集，并集，差集）

  • Z时代
  • 2024-01-10
  • 分类：综合

database

--部门表
createtable dept(
   deptno intprimarykey,--部门编号
   dname nvarchar(30),--部门名
   loc nvarchar(30)--地址
);
--雇员表
createtable emp(
   empno intprimarykey,--雇员号
   ename nvarchar(30),--员工姓名
   job   nvarchar(30),--雇员工作
   mrg int,--雇员上级
   hiredate datetime,--入职时间
   sal numeric(10,2),--薪水
   comm numeric(10,2),--奖金
   deptno intforeignkeyreferences dept(deptno)--设置外键
);
insertinto dept values (10,"ACCOUNTING","NEW YORK");
insertinto dept values (20,"RESEARCH","DALLAS");
insertinto dept values (30 ,"SALES","CHICAGO");
insertinto dept values (40, "OPERATIONS","BOSTON");
insertinto emp values (7369,"SMITH","CLERK",7902,"1980-12-17",800.00,null,20);
insertinto emp values(7499,"ALLEN","SALESMAN",7698,"1981-2-20",1600.00,300.00,30);
insertinto emp values(7521,"WARD","SALESMAN",7698,"1981-2-22",1250.00,500.00,30);
insertinto emp values(7566,"JONES","MANAGER",7839,"1981-4-2",2975.00,null,20);
insertinto emp values(7654,"MARTIN","SALESMAN",7698,"1981-9-28",1250.00,1400.00,30);
insertinto emp values(7698,"BLAKE","MANAGER",7839,"1981-5-1",2850.00,null,30);
insertinto emp values(7782,"CLARK","MANAGER",7839,"1981-6-9",2450.00,null,10);
insertinto emp values(7788,"SCOTT","ANALYST",7566,"1987-4-19",3000.00,null,20);
insertinto emp values(7839,"KING","PRESIDENT",null,"1981-11-17",5000.00,null,10);
insertinto emp values(7844,"TURNER","SALESMAN",7698,"1981-9-8",1500.00,0.00,30);
insertinto emp values(7876,"ADAMS","CLERK",7788,"1987-5-23",1100.00,null,20);
insertinto emp values(7900,"JAMES","CLERK",7698,"1981-12-3",950.00,null,30);
insertinto emp values(7902,"FORD","ANALYST",7566,"1981-12-3",3000.00,null,20);
insertinto emp values(7934,"MILLER","CLERK",7782,"1982-1-23",1300.00,null,10);

 

子查询

■什么是子查询

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询

 

■单行子查询

单行子查询是指只返回一行数据的子查询语句

 

请思考：如何显示与SMITH同一部门的所有员工?

select*from emp where deptno=(select deptno from emp where ename=’SMITH’);

多行子查询

多行子查询指返回多行数据的子查询

请思考:如何查询和部门的工作相同的雇员的名字、岗位、工资、部门号

1，先查询10 号部门有哪些岗位

selectdistinct job from emp where deptno=10;

2，显示和他的岗位有一个相同的员工

select ename,job,sal,deptno from emp where job in(selectdistinct job from emp where deptno=10)

 

全连接

select*from emp,dept;

自然查询

自然连接:将等值连接中的重复列去掉
select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno;

左连接和右连接

左连接:lefton, 依次遍历左边这个表，查询在右表中是否有对应的记录,如果有对应记录,则匹配,否则显示null
select student.sno,sname,ssex,sage,sdept,cno,grade from student leftjoin sc on(student.sno=sc.sno);
右连接:rigth on,以右边的表为参照
select student.sno,sname,ssex,sage,sdept,cno,grade from student rightjoin sc on(student.sno=sc.sno);

 

union并集

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

select ename,sal,job from emp where sal>2500
union
select ename,sal,job from emp where job="MANAGER";

select*from student where sage>20
union
select*from student where sage<22

 

 

 对两个结果集进行“union”,"intersecrt","except"运算这两个结果集的列数必须相同.

intersect交集

使用该操作符用于取得两个结果集的交集。

select ename,sal,job from emp where sal>2500
intersect
select ename,sal,job from emp where job="manager";

 

select*from student where sage>20
intersect
select*from student where sage<22

 

 

 

except差集

使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。

 

select ename,sal,job from emp where sal>2500
minus 
select ename,sal,job from emp where job="manager";

 

select*from student where sage>20
except
select*from student where sage>22

 

 

以上是 sqlserver查询（子查询，全连接，等值连接，自然连接，左右连，交集，并集，差集） 的全部内容， 来源链接： utcz.com/z/531681.html

回到顶部