--部门表

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

 

 

版权声明：本文为yijieyufu原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/yijieyufu/p/11985996.html