数据库SQL 编辑器编写 SQL 查询语句.docx

1、数据库SQL 编辑器编写 SQL 查询语句数据库系统原理实验报告1、实验目的了解 PostgreSQL 查询编辑器的启动，熟悉如何在 PostgreSQL 查询编辑器查询记录。 掌握 SELECT 语句的基本语法和查询条件表示方法 掌握 GROUP BY 和 ORDER BY 子句的作用和使用方法 掌握连接查询和子查询的使用方法 加深对 SQL 数据更新（插入、修改及删除）语句的基本语法格式的掌握 掌握单个元组及多个元组的插入、修改及删除操作的实现过程 加深对更新操作时数据库中数据一致性问题的了解 加深对约束条件在数据更新操作执行中的作用问题的了解 2、实验内容使用 SQL 编辑器编写 SQL

2、 查询语句 使用 SQL 编辑器编写 SQL 数据更新语句 3、实验任务1.对上节建立的表输入数据：（1）departments：代码：Insert into departments_1511630117(DepartmentID,DepartmentName,Note)values(1,财务部,财务部);insert into departments_1511630117(DepartmentID,DepartmentName,Note)values(2,研发部,研发部);insert into departments_1511630117(DepartmentID,DepartmentNa

3、me,Note)values(3,人力资源部,人力资源部)（2）Employee：代码：insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(1001,李勇,19780312,0,河南,475001,3880378,ly,1);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddre

4、ss,departmentid)values(1002,王敏,19801102,1,河南,475002,0378311,wm,1);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(1003,刘晨,19780622,0,河南,475003,0378322,le,1);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,

5、phonenumber,emailaddress,departmentid)values(2001,张立,19780801,0,河南,475004,0378333,zl,2);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(2002,刘毅,19820123,0,河南,475005,0378344,ly,2);insert into employee_1511630117(employeeid,name,bir

6、thday,sex,address,zip,phonenumber,emailaddress,departmentid)values(2003,张玫,19810315,1,河南,475006,0378355,zm,2);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(3001,徐静,19760812,1,河南,475007,0378366,xj,3);insert into employee_15116301

7、17(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(3002,赵军,19790219,0,河南,475008,0378377,zj,3)（3）Salary：代码：insert into salary_1511630117(employeeid,income,outcome)values(1001,3600,1500);insert into salary_1511630117(employeeid,income,outcome)values(1002,3300,1000

8、);insert into salary_1511630117(employeeid,income,outcome)values(1003,3700,1200);insert into salary_1511630117(employeeid,income,outcome)values(2001,4000,1600);insert into salary_1511630117(employeeid,income,outcome)values(2002,3800,1800);insert into salary_1511630117(employeeid,income,outcome)value

9、s(2003,3800,1500);insert into salary_1511630117(employeeid,income,outcome)values(3001,4200,2000);insert into salary_1511630117(employeeid,income,outcome)values(3002,4100,1800);2、练习下面简单的查询语句：a) 查询每个雇员的所有信息：代码：select employeeid,departmentid,name,birthday,sex,address,zip,PhoneNumber,EmailAddressfrom em

10、ployee_1511630117;b) 查询每个雇员的地点和电话：代码：select departmentid,name,address,PhoneNumberfrom employee_1511630117;C)查询 EmployeeID 为 1001 的雇员的地址和电话：select address,PhoneNumberfrom employee_1511630117 where employeeid=1001;D)查询女雇员地址和电话，并用 AS 子句将结果中各列的标题分别指定为“地址”和“电话”：代码：select address as 地址,phonenumber as 电话 f

11、rom employee_1511630117where sex=1E）计算每个雇员的实际收入代码：select income-outcome as 实际收入 from salary_1511630117;F）找出所有姓王的雇员的部门号:代码：select departmentid from employee_1511630117 where name like王%;思考：找出所有地址中含有“中山”的雇员的号码和部门号。 （Address like %中山%）代码：select employeeid,departmentid from employee_1511630117 where add

12、ress like%中山%;3、练习多表连接查询和嵌套查询a) 查询每个雇员的情况及工资情况（工资=Income - Outcome）代码：select employee_1511630117.*,income-outcome 工资 from employee_1511630117,salary_1511630117where employee_1511630117.employeeid=salary_1511630117.employeeid;B）查询财务部工资在 2200 元以上的雇员姓名及工资情况 ：代码：select name,income-outcome 工资 from employ

13、ee_1511630117,salary_1511630117,departments_1511630117 where employee_1511630117.employeeid=salary_1511630117.employeeid and employee_1511630117.departmentid=departments_1511630117.departmentid anddepartmentname=财务部 and income-outcome2200C）查询研发部在 1966 年以前出生的雇员姓名及其工资详情代码：select name,salary_1511630117

14、.* from employee_1511630117,salary_1511630117,departments_1511630117where employee_1511630117.employeeid=salary_1511630117.employeeid and employee_1511630117.departmentid=departments_1511630117.departmentid anddepartmentname=研发部 and birthday2500 and departmentname=人力资源部)I）求财务部雇员的总人数：代码：select count(

15、employeeid) 雇员总人数 from employee_1511630117 where employeeid in( select employeeid from employee_1511630117 X,departments_1511630117 Y where X.departmentid=Y.departmentidand departmentname=财务部)J）求财务部雇员的平均工资：代码：select avg(income-outcome) 雇员平均工资 from salary_1511630117 where employeeid in( select employ

16、eeid from employee_1511630117 X,departments_1511630117 Y where X.departmentid=Y.departmentidand departmentname=财务部)K）查找比所有财务部的雇员工资都高的雇员的姓名:代码：select name from employee_1511630117,salary_1511630117 where employee_1511630117.employeeid=salary_1511630117.employeeid and income-outcome all(select income-

17、outcome from salary_1511630117 where employeeid in(select employeeid from employee_1511630117 X, departments_1511630117 Y where X.departmentid=Y.departmentid and departmentname=财务部)and departmentid 1I)查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名：代码：select name from employee_1511630117 where birthday=all(select birthda

18、y from employee_1511630117 where departmentid=2)and departmentid=1M)查找在财务部工作的雇员的情况：代码：select * from employee_1511630117where departmentid in (select departmentid from departments_1511630117where departmentname=财务部)4.数据更新（1）将员工编号为 1003 的 income 增加 100：代码：update salary_1511630117 set income=income+100

19、 where employeeid=1003（2）将所有员工的 Income 增加 100：代码：update salary_1511630117 set income=income+100（3）在 Departments 表中插入一条新的元组 ：代码：insert into departments_1511630117(departmentid,departmentname,note)values(4,销售部,销售部);（4）在 Employee 中插入一条新的元组 ：代码：insert into employee_1511630117(employeeid,name,birthday,se

20、x,address,zip,phonenumber,emailaddress,departmentid)values(3003,许秉圣,19961204,0,天津,475008,2604505,xbs ,3)（5）在 Salary 表中插入一条新的元组：代码：insert into salary_1511630117(employeeid,income,outcome)values(3003,8888,888);（6）将员工编号为 2001 的员工的 department 改为 5： 代码：update employee_1511630117 set departmentid=5 where

21、employeeid=2001（7）将员工编号为 2001 的员工的 department 改为 3 ：代码：update employee_1511630117 set departmentid=3 where employeeid=2001（8）删除 Employee 表中编号为 1001 的员工信息 ：代码：（再次代码执行之前，先执行了另一个代码，后面“遇到问题”中会进行说明）delete from employee_1511630117 where employeeid=1001（9）删除 Departments 表中编号为 2 的信息：代码：delete from salary_15

22、11630117 where employeeid=2001; delete from salary_1511630117 where employeeid=2002; delete from salary_1511630117 where employeeid=2003; delete from employee_1511630117 where employeeid=2001; delete from employee_1511630117 where employeeid=2002; delete from employee_1511630117 where employeeid=200

23、3;delete from departments_1511630117 where departmentid=2;三、记录在实验过程中遇到的问题、解决办法及心得体会。1.遇到的问题（1）在数据更新中的（6）改为5时，显示没有depatmenid=5的情况解决办法：代码：insert into departments_1511630117(departmentid,departmentname,note)values(4,销售部,销售部);insert into departments_1511630117(departmentid,departmentname,note)values(5,策

24、划部,策划部);执行之后可进行更新，更新后见数据更新（6）的图（2）在数据更新（8）中删除 Employee 表中编号为 1001 的员工信息 最初运行结果如下图解决办法：因为employee中的employeeid被salary所参考，所以需要先删除salary中employeeid=1001的相关数据代码：delete from salary_1511630117 where employeeid=1001;delete from employee_1511630117 where employeeid=1001;四、实验感想 通过这次试验熟练了向数据表中输入数据，学会了一些简单的查询数据表语句，还练习了多表连接查询和嵌套查询，最后练习了数据更新。在过程中遇到的较重要的问题就是没有注意外键约束，以后试验时会更加关注于细节。