--部门表
create table dept(
deptno int primary key,--部门编号
dname nvarchar(30),--部门名
loc nvarchar(30)--地址
);
--雇员表
create table emp(
empno int primary key,--雇员号
ename nvarchar(30),--员工姓名
job nvarchar(30),--雇员工作
mrg int,--雇员上级
hiredate datetime,--入职时间
sal numeric(10,2),--薪水
comm numeric(10,2),--奖金
deptno int foreign key references dept(deptno)--设置外键
);
insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30 ,'SALES','CHICAGO');
insert into dept values (40, 'OPERATIONS','BOSTON');
insert into emp values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,null,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600.00,300.00,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250.00,500.00,30);
insert into emp values(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,null,20);
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,1400.00,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,null,30);
insert into emp values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,null,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000.00,null,20);
insert into emp values(7839,'KING','PRESIDENT',null,'1981-11-17',5000.00,null,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,0.00,30);
insert into emp values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100.00,null,20);
insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-3',950.00,null,30);
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-3',3000.00,null,20);
insert into 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 号部门有哪些岗位
select distinct job from emp where deptno=10;
2，显示和他的岗位有一个相同的员工
select ename,job,sal,deptno from emp where job in(select distinct 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;
左连接和右连接
左连接:left on, 依次遍历左边这个表，查询在右表中是否有对应的记录,如果有对应记录,则匹配,否则显示null
select student.sno,sname,ssex,sage,sdept,cno,grade from student left join sc on(student.sno=sc.sno);
右连接:rigth on,以右边的表为参照
select student.sno,sname,ssex,sage,sdept,cno,grade from student right join 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
Original: https://www.cnblogs.com/yijieyufu/p/11985996.html
Author: Aquiet
Title: sqlserver查询（子查询，全连接，等值连接，自然连接，左右连，交集，并集，差集）
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/581830/
转载文章受原作者版权保护。转载请注明原作者出处！