--部門表 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,--入職時(shí)間 sal numeric(10,2),--薪水 comm numeric(10,2),--獎(jiǎng)金 deptno int foreign key references dept(deptno)--設(shè)置外鍵 ); 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語句,也叫嵌套查詢
■單行子查詢 單行子查詢是指只返回一行數(shù)據(jù)的子查詢語句
請思考：如何顯示與SMITH同一部門的所有員工? select * from emp where deptno=(select deptno from emp where ename=’SMITH’); 多行子查詢 多行子查詢指返回多行數(shù)據(jù)的子查詢 請思考:如何查詢和部門的工作相同的雇員的名字,、崗位,、工資、部門號 1,，先查詢10 號部門有哪些崗位 select distinct job from emp where deptno=10; 2,，顯示和他的崗位有一個(gè)相同的員工 select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10)
全連接select * from emp,dept; 自然查詢自然連接:將等值連接中的重復(fù)列去掉 select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno; 左連接和右連接左連接:left on, 依次遍歷左邊這個(gè)表，查詢在右表中是否有對應(yīng)的記錄,如果有對應(yīng)記錄,則匹配,否則顯示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并集該操作符用于取得兩個(gè)結(jié)果集的并集,。當(dāng)使用該操作符時(shí),，會(huì)自動(dòng)去掉結(jié)果集中重復(fù)行,。 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
對兩個(gè)結(jié)果集進(jìn)行“union”,"intersecrt","except"運(yùn)算這兩個(gè)結(jié)果集的列數(shù)必須相同. intersect交集使用該操作符用于取得兩個(gè)結(jié)果集的交集。 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差集使用該操作符用于取得兩個(gè)結(jié)果集的差集,，它只會(huì)顯示存在第一個(gè)集合中,，而不存在第二個(gè)集合中的數(shù)據(jù)。
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
|
|
|
