数据库SQL编辑器编写SQL查询语句.docx
《数据库SQL编辑器编写SQL查询语句.docx》由会员分享,可在线阅读,更多相关《数据库SQL编辑器编写SQL查询语句.docx(29页珍藏版)》请在冰豆网上搜索。
数据库SQL编辑器编写SQL查询语句
数据库系统原理实验报告
一、实验目的
了解PostgreSQL查询编辑器的启动,熟悉如何在PostgreSQL查询编辑器查询记录。
掌握SELECT语句的基本语法和查询条件表示方法
掌握GROUPBY和ORDERBY子句的作用和使用方法
掌握连接查询和子查询的使用方法
加深对SQL数据更新(插入、修改及删除)语句的基本语法格式的掌握
掌握单个元组及多个元组的插入、修改及删除操作的实现过程
加深对更新操作时数据库中数据一致性问题的了解
加深对约束条件在数据更新操作执行中的作用问题的了解
二、实验内容
使用SQL编辑器编写SQL查询语句
使用SQL编辑器编写SQL数据更新语句
三、实验任务
1.对上节建立的表输入数据:
(1)departments:
代码:
Insertintodepartments_1511630117(DepartmentID,DepartmentName,Note)
values('1','财务部','财务部');
insertintodepartments_1511630117(DepartmentID,DepartmentName,Note)
values('2','研发部','研发部');
insertintodepartments_1511630117(DepartmentID,DepartmentName,Note)
values('3','人力资源部','人力资源部')
(2)Employee:
代码:
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('1001','李勇','19780312','0','河南','475001','3880378','','1');
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('1002','王敏','19801102','1','河南','475002','0378311','','1');
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('1003','刘晨','19780622','0','河南','475003','0378322','','1');
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('2001','张立','19780801','0','河南','475004','0378333','','2');
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('2002','刘毅','19820123','0','河南','475005','0378344','','2');
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('2003','张玫','19810315','1','河南','475006','0378355','','2');
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('3001','徐静','19760812','1','河南','475007','0378366','','3');
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('3002','赵军','19790219','0','河南','475008','0378377','','3')
(3)Salary:
代码:
insertintosalary_1511630117(employeeid,income,outcome)
values('1001','3600','1500');
insertintosalary_1511630117(employeeid,income,outcome)
values('1002','3300','1000');
insertintosalary_1511630117(employeeid,income,outcome)
values('1003','3700','1200');
insertintosalary_1511630117(employeeid,income,outcome)
values('2001','4000','1600');
insertintosalary_1511630117(employeeid,income,outcome)
values('2002','3800','1800');
insertintosalary_1511630117(employeeid,income,outcome)
values('2003','3800','1500');
insertintosalary_1511630117(employeeid,income,outcome)
values('3001','4200','2000');
insertintosalary_1511630117(employeeid,income,outcome)
values('3002','4100','1800');
2、练习下面简单的查询语句:
a)查询每个雇员的所有信息:
代码:
selectemployeeid,departmentid,name,birthday,sex,address,zip,PhoneNumber,EmailAddress
fromemployee_1511630117;
b)查询每个雇员的地点和电话:
代码:
selectdepartmentid,name,address,PhoneNumber
fromemployee_1511630117;
C)查询EmployeeID为1001的雇员的地址和电话:
selectaddress,PhoneNumber
fromemployee_1511630117whereemployeeid='1001';
D)查询女雇员地址和电话,并用AS子句将结果中各列的标题分别指定为“地址”和“电
话”:
代码:
selectaddressas地址,phonenumberas电话fromemployee_1511630117
wheresex='1'
E)计算每个雇员的实际收入
代码:
selectincome-outcomeas实际收入fromsalary_1511630117;
F)找出所有姓王的雇员的部门号:
代码:
selectdepartmentidfromemployee_1511630117wherenamelike'王%';
思考:
找出所有地址中含有“中山”的雇员的号码和部门号。
(Addresslike‘%中山%’)
代码:
selectemployeeid,departmentidfromemployee_1511630117whereaddresslike'%中山%';
3、练习多表连接查询和嵌套查询
a)查询每个雇员的情况及工资情况(工资=Income-Outcome)
代码:
selectemployee_1511630117.*,income-outcome工资
fromemployee_1511630117,salary_1511630117
whereemployee_1511630117.employeeid=salary_1511630117.employeeid;
B)查询财务部工资在2200元以上的雇员姓名及工资情况:
代码:
selectname,income-outcome工资fromemployee_1511630117,salary_1511630117,departments_1511630117
whereemployee_1511630117.employeeid=salary_1511630117.employeeidand
employee_1511630117.departmentid=departments_1511630117.departmentidand
departmentname='财务部'andincome-outcome>2200
C)查询研发部在1966年以前出生的雇员姓名及其工资详情
代码:
selectname,salary_1511630117.*fromemployee_1511630117,salary_1511630117,departments_1511630117
whereemployee_1511630117.employeeid=salary_1511630117.employeeidand
employee_1511630117.departmentid=departments_1511630117.departmentidand
departmentname='研发部'andbirthday<'19660101'
D)查询人力资源部雇员的最高和最低工资
代码:
selectmax(income-outcome)最大值,min(income-outcome)最小值
fromemployee_1511630117,salary_1511630117,departments_1511630117
whereemployee_1511630117.employeeid=salary_1511630117.employeeidand
employee_1511630117.departmentid=departments_1511630117.departmentidand
departmentname='人力资源部'
E)将各雇员的情况按工资由低到高排列
代码:
selectemployee_1511630117.*fromemployee_1511630117,salary_1511630117
whereemployee_1511630117.employeeid=salary_1511630117.employeeid
orderbyincome-outcomeasc
F)求各部门的雇员数
代码:
selectE.departmentid,count(*)各部门雇员数
fromemployee_1511630117E,departments_1511630117D
whereE.departmentid=D.departmentidgroupbyE.departmentid
G)找出所有在财务部和人力资源部工作的雇员的编号
代码:
selectemployeeidfromemployee_1511630117E,departments_1511630117D
whereE.departmentid=D.departmentidanddepartmentnamein('财务部','人力资源部')
H)统计人力资源部工资在2500以上雇员的人数
代码:
selectcount(employeeid)雇员人数from
employee_1511630117wheredepartmentidin(selectdepartmentidfrom
departments_1511630117whereemployeeidin(selectemployeeidfrom
salary_1511630117whereincome-outcome>'2500'anddepartmentname='人力资源部'))
I)求财务部雇员的总人数:
代码:
selectcount(employeeid)雇员总人数from
employee_1511630117whereemployeeidin(selectemployeeidfrom
employee_1511630117X,departments_1511630117YwhereX.departmentid=Y.departmentid
anddepartmentname='财务部')
J)求财务部雇员的平均工资:
代码:
selectavg(income-outcome)雇员平均工资from
salary_1511630117whereemployeeidin(selectemployeeidfrom
employee_1511630117X,departments_1511630117YwhereX.departmentid=Y.departmentid
anddepartmentname='财务部')
K)查找比所有财务部的雇员工资都高的雇员的姓名:
代码:
selectnamefromemployee_1511630117,salary_1511630117
whereemployee_1511630117.employeeid=salary_1511630117.employeeidandincome-outcome>all
(selectincome-outcomefromsalary_1511630117whereemployeeidin
(selectemployeeidfromemployee_1511630117X,departments_1511630117Y
whereX.departmentid=Y.departmentidanddepartmentname='财务部'))
anddepartmentid<>'1'
I)查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名:
代码:
selectnamefromemployee_1511630117wherebirthday<=all
(selectbirthdayfromemployee_1511630117wheredepartmentid='2')
anddepartmentid='1'
M)查找在财务部工作的雇员的情况:
代码:
select*fromemployee_1511630117
wheredepartmentidin(selectdepartmentidfromdepartments_1511630117
wheredepartmentname='财务部')
4.数据更新
(1)将员工编号为1003的income增加100:
代码:
updatesalary_1511630117
setincome=income+100
whereemployeeid='1003'
(2)将所有员工的Income增加100:
代码:
updatesalary_1511630117
setincome=income+100
(3)在Departments表中插入一条新的元组:
代码:
insertintodepartments_1511630117(departmentid,departmentname,note)
values('4','销售部','销售部');
(4)在Employee中插入一条新的元组:
代码:
insertintoemployee_1511630117(employeeid,name,birthday,sex,address,zip,phonenumber,emailaddress,departmentid)
values('3003','许秉圣','19961204','0','天津','475008','2604505','','3')
(5)在Salary表中插入一条新的元组:
代码:
insertintosalary_1511630117(employeeid,income,outcome)
values('3003','8888','888');
(6)将员工编号为2001的员工的department改为5:
代码:
updateemployee_1511630117
setdepartmentid=5
whereemployeeid='2001'
(7)将员工编号为2001的员工的department改为3:
代码:
updateemployee_1511630117
setdepartmentid=3
whereemployeeid='2001'
(8)删除Employee表中编号为1001的员工信息:
代码:
(再次代码执行之前,先执行了另一个代码,后面“遇到问题”中会进行说明)
deletefromemployee_1511630117
whereemployeeid='1001'
(9)删除Departments表中编号为2的信息:
代码:
deletefromsalary_1511630117
whereemployeeid='2001';
deletefromsalary_1511630117
whereemployeeid='2002';
deletefromsalary_1511630117
whereemployeeid='2003';
deletefromemployee_1511630117
whereemployeeid='2001';
deletefromemployee_1511630117
whereemployeeid='2002';
deletefromemployee_1511630117
whereemployeeid='2003';
deletefromdepartments_1511630117
wheredepartmentid='2';
三、记录在实验过程中遇到的问题、解决办法及心得体会。
1.遇到的问题
(1)在数据更新中的(6)改为5时,显示没有depatmenid=5的情况
解决办法:
代码:
insertintodepartments_1511630117(departmentid,departmentname,note)
values('4','销售部','销售部');
insertintodepartments_1511630117(departmentid,departmentname,note)
values('5','策划部','策划部');
执行之后可进行更新,更新后见数据更新(6)的图
(2)在数据更新(8)中删除Employee表中编号为1001的员工信息最初运行结果如下图
解决办法:
因为employee中的employeeid被salary所参考,所以需要先删除salary中employeeid=1001的相关数据
代码:
deletefromsalary_1511630117
whereemployeeid='1001';
deletefromemployee_1511630117
whereemployeeid='1001';
四、实验感想
通过这次试验熟练了向数据表中输入数据,学会了一些简单的查询数据表语句,还练习了多表连接查询和嵌套查询,最后练习了数据更新。
在过程中遇到的较重要的问题就是没有注意外键约束,以后试验时会更加关注于细节。