深圳大学数据库实验二SQL的多表连接查询以及视图-526互联

实验目的：掌握postgresql的服务管理、命令行服务管理，熟悉集群服务配置管理方式；熟悉并掌握数据库查询与数据库视图的基础原理；掌握利用SQL语句进行多表连接查询、建立并操纵视图的方法。实验要求： 1、练习postgresql服务管理配置； 2、练习典型的多表连接查询SQL语句、聚合函数； 3、练习视图的创建查询、插入、更新、删除等操作
实验环境：软件：liunx系统，centos （根据自己实验环境填写）
实验内容在服务器端练习postgresql的系统配置、用户环境配置、集群服务配置： 1. 实现服务的自启动，将“源码包/contrib/start-scripts”目录下的脚本重命名为linux1后放到/etc/init.d目录下，并使用sudo chkconfig --add linux1指令添加服务自启动（图1-1-1）。对操作系统进行配置如图1-1-2所示，对Linux资源限制调整如图1-1-3所示，对系统防火墙配置如图1-1-4所示。图1-1-1 图1-1-2 图1-1-3 图1-1-4 2. 配置环境变量（图1-1-5），测试pg_ctl（图1-1-6），成功。图1-1-5 图1-1-6 3.pg_hba.conf配置（图1-1-7），postgresresql.conf配置与重新启动pg_ctl（图1-1-8），进入postgres后输入show all （图1-1-9）以及select语句（图1-1-10）查看当前所有配置。图1-1-7 图1-1-8 图1-1-9 图1-1-10 在实验一的基础上完成EX2-EX7的SQL练习题 Ex2 1.Find the name and salary of employees in Luton. 语句： select emp2017303010.ENAME,emp2017303010.SAL from emp2017303010 inner join dep2017303010 on emp2017303010.DEPTNO = dep2017303010.DEPTNO where dep2017303010.loc='LUTON'; 结果（图2-2-1）：图2-2-1 4.List all departments that do not have any employees. * use join and no subquery 语句： select dep2017303010.deptno,dep2017303010.dname,dep2017303010.loc from emp2017303010 right join dep2017303010 on emp2017303010.DEPTNO =dep2017303010.DEPTNO where empno is null; 结果（图2-2-4）：图2-2-4 5.For each employee whose salary exceeds his manager's salary, list the employee's name and salary and the manager's name and salary. 语句: select e.ename,e.sal,m.ename,m.sal from emp2017303010 e,emp2017303010 m where e.mgr=m.empno and e.sal>m.sal; 结果（图2-2-5）： 6.List the employees who have BLAKE as their manager. 语句： select * from emp2017303010 e where e.mgr=(select empno from emp2017303010 where ename= 'BLAKE'); 结果（图2-2-6）：图2-2-6 Ex3 2.Compute the average annual income (income is salary plus commission) for all salesmen 语句： select AVG(e.sal+e.comm) from emp2017303010 e where job='SALESMAN'; 结果（图2-3-2）：图2-3-2 3.Find the number of characters in the longest department name 语句： select max(length(dname)) from dep2017303010; 结果（图2-3-3）：图2-3-3 5.Count the number of people in department 30 who receive a salary and the number of people who receive a commission (single statement). 语句： Select * from (select count() as both_comm_man from (select e.empno from emp2017303010 e where e.deptno = 30 and e.comm is not null)temp1) tempa, (select count() as salary_man from (select e.empno from emp2017303010 e where e.sal is not null and e.deptno = 30)temp2)tempb; 结果（图2-3-5）：图2-3-5 8.Compute the daily and hourly salary for employees in department 30, round to the nearest penny. Assume there are 22 working days in a month and 8 working hours in a day.(Use function round()) 语句： select ,round((sal)/22) as Daily_salary,round(((sal)/(228))) as Hour_salary from emp2017303010 e where deptno=30; 结果（图2-3-8）图2-3-8 Ex4 1.Select the name, job, and date of hire of the employees in department (Format the HIREDATE column to MM/DD/YY) 语句： select ename,job,to_char(hiredate,'MM/DD/YY') from emp2017303010; 结果（图2-4-1）：图2-4-1 2.Then format the HIREDATE column into DoW (day of the week), Day (day of the month), MONTH (name of the month) and YYYY(year) 语句： Select ename,job,to_char(hiredate,'DoW,Day,MONTH,YYYY') from emp2017303010; 结果（图2-4-2）：图2-4-2 3.Which employees were hired in April? 语句： select ,to_char(hiredate,'MONTH') as hire_month from emp2017303010 where to_char(hiredate,'MONTH') like 'APRIL%'; 结果（图2-4-3）：图2-4-3 5.Are there any employees who have worked more than 30 years for the company? 语句： select from emp2017303010 where date_part('year',now())-date_part('year',hiredate)>30; 结果（图2-4-5）：图2-4-5 6.Show the weekday of the first day of the month in which each employee was hired. (plus their names) 语句： select ename,hiredate,extract(DOW FROM cast ( to_date ( to_char ( date_trunc ( 'month' , hiredate ) , 'YYYY/MM/DD' ) , 'YYYY/MM/DD' ) as TIMESTAMP)) as First_weekday from emp2017303010; 结果（图2-4-6）：图2-4-6 7.Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names) 此题为附加题语句： 1、find_firstsalday函数 Create or replace function find_firstsalday(d1 date) returns Date as $$ DECLARE D2 date; D3 timestamp; Begin D3= date_trunc('month',d1 + interval '1 MONTH - 1 DAY'); D2 = to_date(to_char(D3,'YYYY/MM/DD'),'YYYY/MM/DD'); while extract(DOW FROM cast(D2 as TIMESTAMP)) <> 5 loop D2 = D2 - 1 'day'; end loop; return D2 ; end ; $$ LANGUAGE plpgsql; 2、select语句 select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname,d.loc,find_firstsalday(hiredate) as First_salday from emp2017303010 e inner join dep2017303010 d on e.deptno = d.deptno; 结果（图2-4-8、图2-4-9）：图2-4-8 图2-4-9 Ex5* 2. Divide all employees into groups by department and by job within department. Count the employees in each group and compute each group's average annual salary. 语句： select d.dname,e.job,count(ename),round(AVG(sal),2) from emp2017303010 e inner join dep2017303010 d on e.deptno = d.deptno group by d.dname,e.job; 结果（图2-5-2）：图2-5-2 5.Find all departments with an average commission greater than 25% of average salary. 语句： select d.deptno,d.dname,round(AVG(e.comm),2) as comm_avg,round(AVG(e.sal),2) as sal_avg from emp2017303010 e inner join dep2017303010 d on e.deptno = d.deptno group by d.deptno,d.dname having AVG(e.comm)>AVG(e.sal)0.25; 结果（图2-5-5）：图2-5-5 6.Find each department's average annual salary for all its employees except the managers and the president. 语句： select e.deptno,round(AVG(e.sal),2),count() as sal_avg from emp2017303010 e where job <> 'MANAGER' and job <> 'PRESIDENT' group by e.deptno; 结果（图2-5-6）：图2-5-6 Ex6 1.List the name and job of employees who have the same job as Jones. 语句： select ename,job from emp2017303010 where job = (select job from emp2017303010 where ename = 'JONES') and ename <> 'JONES'; 结果（图2-6-1）：图2-6-1 3.List the name, job, and department of employees who have the same job as Jones or a salary greater than or equal to Ford. 语句： select e.ename,d.dname,e.job,e.sal from emp2017303010 e inner join dep2017303010 d on e.deptno = d.deptno where e.job = (select job from emp2017303010 where ename = 'JONES') or e.sal >= (select sal from emp2017303010 where ename = 'FORD') and e.ename <> 'FORD' and e.ename <> 'JONES'; 结果（图2-6-3）：图2-6-3 4.Find all employees in department 10 that have a job that is the same as anyone in the Sales department 语句： select * from emp2017303010 where deptno=10 and job = (select job from dep2017303010 where dname = 'SALES');; 结果（图2-6-4）：图2-6-4 7.Find all the employees that earn more than JONES, using temporary labels to abbreviate table names. 语句： select * from emp2017303010 e where e.sal>(select e.sal from emp2017303010 e where e.ename = 'JONES'); 结果（图2-6-7）：图2-6-7 Ex7 1.Create a new table called loans with columns named LNO NUMERIC (3), EMPNO NUMERIC (4), TYPE CHAR(1), AMNT NUMERIC (8,2) Don’t forget to create constraints 语句： create table loans (LNO NUMERIC(3),EMPNO NUMERIC(4),TYPE CHAR(1),AMNT NUMERIC(8,2)); alter table loans add primary key (lno); 结果（图2-7-1）：图2-7-1 2.Insert the following data LNO EMPNO TYPE AMNT 23 7499 M 20000.00 42 7499 C 2000.00 65 7844 M 3564.00 语句： INSERT INTO loans (lno,empno,type,amnt) VALUES (23, 7499, 'M', 20000.00),(42, 7499, 'C', 2000.00),(65, 7844, 'M', 3564.00); 结果（图2-7-2）：图2-7-3 4.The Loans table must be altered to include another column OUTST NUMERIC(8,2) 语句： alter table loans add outst NUMERIC (8,2); 结果（图2-7-4）：图2-7-4 5.Add 10% interest to all M type loans 语句： update loans set amnt = amnt1.1 where type = 'M'; 结果（图2-7-5）：图2-7-5 6.Remove all loans less than £3000.00 语句： delete from loans where amnt < 3000; 结果（图2-7-6）：图2-7-6 7.Change the name of loans table to accounts 语句： alter table loans rename to accounts; 结果（图2-7-7）：图2-7-7 8.Change the name of column LNO to LOANNO 语句： alter table accounts rename LNO to LOANNO; 结果（图2-7-8）：图2-7-8 9.Create a view for use by personnel in department 30 showing employee name, number, job and hiredate 语句： create view view2017303010 as select ename,empno,job,hiredate from emp2017303010 where deptno=30; 结果（图2-7-9）：图2-7-9 10.Use the view to show employees in department 30 having jobs which are not salesman 语句： select * from view2017303010 where job <> 'SALESMAN'; 结果（图2-7-10）：图2-7-10
实验结论或体会：实验结论：实验收获： 1、在bash界面启动pg_ctl时，仍然报错（图5-1），根据网上查阅的资料未解决问题，猜测可能是自己的路径没写对（图5-2、图5-3、图5-4），修改后如图5-5所示，但是仍然报错，后来向老师提问才明白，我是用的yum安装postgres而不是用的源码安装，因此并不存在pg_ctl，因为yum安装已经自带了，所以启动不了，用户权限不够。图5-1 图5-2 图5-3 图5-4 图5-5 （给出实验结论，介绍实验过程中遇到的困难，总结实验收获！）