《sql语句培训文档》由会员分享,可在线阅读,更多相关《sql语句培训文档(10页珍藏版)》请在金锄头文库上搜索。
1、2008年10月,中国电力科学院配电与农电研究院,ORACLE数据库,SQL语句培训,(一)四类SQL语句,1、数据表创建语句,创建员工数据表:,CREATE TABLE Employee (Em_id number(8), Em_name varchar(20), dp_id number(8), salary number(6);,插入演示数据:,INSERT INTO Employee VALUES(1,陈红卫,1,4000); INSERT INTO Employee VALUES(2,骆誉明,1,2800); INSERT INTO Employee VALUES(3,汪金海,2,5
2、000); INSERT INTO Employee VALUES(4,王敏,2,3500); INSERT INTO Employee VALUES(5,杜康乐,2,1800); INSERT INTO Employee VALUES(6,赵方,8,8000); Commit;,1、数据表创建语句,创建部门数据表:,CREATE TABLE Department (dp_id number(8), dp_name varchar(20), Up_DepID number );,插入演示数据:,INSERT INTO Department VALUES(1,技术组,0); INSERT INT
3、O Department VALUES(2,服务组,0); INSERT INTO Department VALUES(3,工程组,0); INSERT INTO Department VALUES(6, 财务部,2); COMMIT;,说明:部门编号、部门名称、上级部门编号。,1、内关联 Inner Join,查询语句:,SELECT * FROM Employee EMP INNER JOIN Department DP ON EMP.dp_id=DP.DP_ID,1、左关联 Left Join,查询语句:,SELECT * FROM Employee EMP Left JOIN Depa
4、rtment DP ON EMP.dp_id=DP.DP_ID,说明:左关联查询,只对(Left)后面的数据表进行过滤。,查询结果:,1、右关联和全关联,Right Join:,SELECT * FROM Employee EMP Right JOIN Department DP ON EMP.dp_id=DP.DP_ID,说明:右关联查询,只对(Right)后面的数据表进行过滤。,Full Join:,提问:此时查询的结果如何?请写出查询结果。,SELECT * FROM Employee EMP Full JOIN Department DP ON EMP.dp_id=DP.DP_ID,说
5、明:Full查询,不再对两边的数据表进行过滤。,提问:此时查询的结果如何?请写出查询结果。,1、自然连接和Using语句,Natural Join:,SELECT * FROM Employee EMP NATURAL JOIN Department DP,说明:只有两个数据表中有相同的列,并且使用该列进行关联时,才可以使 用自然连接。 自然连接查询结果和内连接查询相同。,Using子句:,SELECT * FROM Employee ST INNER JOIN DEPMENT DP USING (dp_id),说明:两个数据表中有相同的列时,可以使用该查询语句。,1、子查询语句,In 语句:
6、,SELECT * FROM Employee WHERE dp_id IN (SELECT DP_ID FROM Department WHERE UP_DEPID=0),说明:查询部门编号为0(工程技术部)下级部门的人员。,EXISTS语句:,SELECT * FROM Employee A WHERE EXISTS (SELECT DP_ID FROM Department B WHERE UP_DEPID=0 AND A.dp_id=B.dp_id);,说明:和In语句查询出的结果一样,该语句查询的效率大大优于In语句。,1、第一章试题,1、阐述四种关联查询的区别; 2、自己独立编写,查询上级部门编号为0部门的员工,(普通关联查询、IN语句和Exist语句,三种不同的语句); 3、插入部门编号为0的部门信息(部门名称为:工程技术部;上级部门编号为:99); 4、查询最底部门(最末端的部门,如“工程组”、“服务组”等)的信息;,