1、数据库SQL编辑器编写SQL查询语句数据库系统原理实验报告一、实验目的 了解 PostgreSQL 查询编辑器的启动,熟悉如何在 PostgreSQL 查询编辑器查询记录。 掌握 SELECT 语句的基本语法和查询条件表示方法 掌握 GROUP BY 和 ORDER BY 子句的作用和使用方法 掌握连接查询和子查询的使用方法 加深对 SQL 数据更新(插入、修改及删除)语句的基本语法格式的掌握 掌握单个元组及多个元组的插入、修改及删除操作的实现过程 加深对更新操作时数据库中数据一致性问题的了解 加深对约束条件在数据更新操作执行中的作用问题的了解 二、实验内容使用 SQL 编辑器编写 SQL 查
2、询语句 使用 SQL 编辑器编写 SQL 数据更新语句 三、实验任务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,DepartmentName
3、,Note)values(3,人力资源部,人力资源部)(2)Employee:代码:insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(1001,李勇,19780312,0,河南,475001,3880378,1);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,de
4、partmentid)values(1002,王敏,19801102,1,河南,475002,0378311,1);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(1003,刘晨,19780622,0,河南,475003,0378322,1);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber
5、,emailaddress,departmentid)values(2001,张立,19780801,0,河南,475004,0378333,2);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(2002,刘毅,19820123,0,河南,475005,0378344,2);insert into employee_1511630117(employeeid,name,birthday,sex,address
6、,zip,phonenumber,emailaddress,departmentid)values(2003,张玫,19810315,1,河南,475006,0378355,2);insert into employee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)values(3001,徐静,19760812,1,河南,475007,0378366,3);insert into employee_1511630117(employeeid,name,birt
7、hday,sex,address,zip,phonenumber,emailaddress,departmentid)values(3002,赵军,19790219,0,河南,475008,0378377,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);insert into salary_15116
8、30117(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)values(2003,3800,1500);insert i
9、nto 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 employee_1511630117;b) 查询每个雇
10、员的地点和电话:代码: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 电话 from employee_1511630117whe
11、re 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 address like%中山%;3、练习多表连接查询和嵌
12、套查询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 employee_1511630117,salary_15116
13、30117,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.* from employee_151163011
14、7,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(employeeid) 雇员总人数 from emp
15、loyee_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 employeeid from employee_1511630
16、117 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-outcome from salary_151163
17、0117 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 birthday from employee_1511630117
18、 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 where employeeid=1003(2)将
19、所有员工的 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,sex,address,zip,phonenumber,
20、emailaddress,departmentid)values(3003,许秉圣,19961204,0,天津,475008,2604505, ,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 employeeid=2001(7)将员工编号为 2001
21、 的员工的 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_1511630117 where employeeid=200
22、1; 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=2003;delete from departments_151
23、1630117 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,策划部,策划部);执行之后可进行更新,更新后见数据更新(6)
24、的图(2)在数据更新(8)中删除 Employee 表中编号为 1001 的员工信息 最初运行结果如下图解决办法:因为employee中的employeeid被salary所参考,所以需要先删除salary中employeeid=1001的相关数据代码:delete from salary_1511630117 where employeeid=1001;delete from employee_1511630117 where employeeid=1001;四、实验感想 通过这次试验熟练了向数据表中输入数据,学会了一些简单的查询数据表语句,还练习了多表连接查询和嵌套查询,最后练习了数据更新。在过程中遇到的较重要的问题就是没有注意外键约束,以后试验时会更加关注于细节。
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1