Task 1. 基于finalshell建立的SSH隧道，实现Python代码连接天翼云数据库
（1）给出finalshell的配置
如下图：
为了登录安全起见，将ssh登录端口和数据库监听端口进行了修改。
（2）给出Python连接天翼云数据库的代码
Python代码如下：
import psycopg2
conn = psycopg2.connect( dbname = 'a2513210112',
user = '*******',
password = '*******',
host = '127.0.0.1',
port = 9929,
options = '-c search_path="S-T",public')
print("connection is ready")为了数据库安全，将user和password进行了隐藏处理，连接时并指定连接“S-T”模式下。
（1）给出查询上次实验中建立的“S-T”模式中，student表中所有的数据，并在界面打印出来
Python代码如下：
import psycopg2
conn = psycopg2.connect(dbname = 'a2513210112',
user = '*******',
password = '*******',
host = '127.0.0.1',
port = 9929,
options = '-c search_path="S-T",public')
#print("connection is ready")
cur = conn.cursor()
cur.execute("SELECT * FROM student;")
rows = cur.fetchall()
for row in rows:
print(row)
cur.close()
conn.close()
Task2 根据给定的excel表格中的表结构信息，建三个表，选择在S-Tschema下建表
--部门建表
CREATE TABLE dept
(
deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20)
);
--员工建表
CREATE TABLE emp
(
empno INT PRIMARY KEY,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT,
hiredate DATE,
sal FLOAT(16),
comm FLOAT(16),
deptno INT,
FOREIGN KEY (deptno) REFERENCES dept(deptno)
);
CREATE TABLE salgrade
(
grade INT,
losal FLOAT(16),
hisal FLOAT(16)
);Task3.写Python代码，嵌入SQL语句，将给的excel表格中的数据，导入数据库
（1）通过python连接云上数据库，并执行嵌入的sql脚本和完整可执行程序
import psycopg2
conn = psycopg2.connect(dbname = 'a2513210112',
user = '*******',
password = '*******',
host = '127.0.0.1',
port = 9929,
options = '-c search_path="S-T",public')
print("connection is ready")
cur = conn.cursor()#获取游标
#对部门表插入数据
sql="insert into dept values(%s,%s,%s)"
# 每一个值都作为一个元组，整个参数集作为一个元组
param=((10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON')
)
cur.executemany(sql,param)
# 提交
conn.commit()
conn.commit()
cur.execute("select * from dept")
rows = cur.fetchall()
for row in rows:
print(row)执行结果：
#对员工表插入数据
sql = "insert into emp values(%s,%s,%s,%s,%s,%s,%s,%s)"
param =( (7369,'SMITH', 'CLERK',7902,'1980-12-17',800,None,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD', 'SALESMAN', 7698,'1981-02-22',1250,500,30),
(7566,'JONES', 'MANAGER', 7839, '1981-04-02',2975,None,20),
(7654,'MARTIN', 'SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850,None,30),
(7782, 'CLARK','MANAGER',7839 ,'1981-06-09', 2450,None,10),
(7788, 'SCOTT','ANALYST', 7566, '1987-07-13', 3000,None,20),
(7839, 'KING', 'PRESIDENT',None,'1981-11-07', 5000,None,10),
(7844, 'TURNER','SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK',7788, '1987-07-13', 1100,None,20),
(7900, 'JAMES','CLERK', 7698, '1981-12-03',950 ,None,30),
(7902, 'FORD','ANALYST',7566, '1981-12-03',3000,None,20),
(7934, 'MILLER','CLERK',7782,'1982-01-23',1300,None,10)
)
cur.executemany(sql,param)
# 提交
conn.commit()
cur.execute("select * from emp")
rows = cur.fetchall()
for row in rows:
print(row)
sql = "insert into salgrade values(%s,%s,%s)"
param = (
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999)
)
cur.executemany(sql,param)
# 提交
conn.commit()
cur.execute("select * from salgrade")
rows = cur.fetchall()
for row in rows:
print(row)
（2）查询结果
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
Task 4. 根据问题，写SQL代码，实现相应的操作。
4.1列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
SELECT emp.ename,cn.dname, cn.部门人数
FROM emp
JOIN (SELECT emp.deptno ,dname, COUNT(empno) "部门人数"
FROM emp , dept
WHERE emp.deptno = dept.deptno
GROUP BY emp.deptno, dept.dname) cn
ON emp.deptno = cn.deptno AND job = (select job from emp where ename = 'SCOTT') AND emp.ename <> 'SCOTT'
4.2列出薪金高于"在部门30工作的所有员工的薪金"的员工姓名和薪金、部门名称。
SELECT e.ename,e.sal,d.dname
FROM emp e
JOIN dept d
ON e.deptno = d.deptno
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);
4.3列出每个部门的员工数量、平均工资和平均服务期限。
按年：
import psycopg2
conn = psycopg2.connect(dbname = 'a2513210112',
user = '*******',
password = '*******',
host = '127.0.0.1',
port = 9929,
options = '-c search_path="S-T",public')
print("connection is ready")
cur = conn.cursor()#获取游标
cur.execute("select deptno,COUNT(empno),avg(sal) as 平均薪资 ,avg(2023 - extract(year from hiredate)) from emp group by deptno ")
rows = cur.fetchall()
for row in rows:
print(row)
cur.close()
conn.close()
按天：
import psycopg2
conn = psycopg2.connect(dbname = 'a2513210112',
user = '*******',
password = '*******',
host = '127.0.0.1',
port = 9929,
options = '-c search_path="S-T",public')
print("connection is ready")
cur = conn.cursor()#获取游标
cur.execute("select deptno, COUNT(empno),AVG(sal) as 平均薪资, AVG(CURRENT_DATE - hiredate) from emp group by deptno ")
rows = cur.fetchall()
for row in rows:
print(row)
cur.close()
conn.close()
4.4列出所有员工的姓名、部门名称和工资。
import psycopg2
conn = psycopg2.connect(dbname = 'a2513210112',
user = '*******',
password = '*******',
host = '127.0.0.1',
port = 9929,
options = '-c search_path="S-T",public')
print("connection is ready")
cur = conn.cursor()#获取游标
cur.execute("SELECT e.ename,d.dname,e.sal FROM emp e JOIN dept d ON e.deptno = d.deptno ")
rows = cur.fetchall()
for row in rows:
print(row)
cur.close()
conn.close()4.5列出所有部门的详细信息和部门人数。
import psycopg2
conn = psycopg2.connect(dbname = 'a2513210112',
user = '*******',
password = '*******',
host = '127.0.0.1',
port = 9929,
options = '-c search_path="S-T",public')
print("connection is ready")
cur = conn.cursor()
cur.execute("select d.*, b.cn from dept d left join (select deptno , COUNT(*) cn from emp group by deptno) b on d.deptno = b.deptno")
rows = cur.fetchall()
for row in rows:
print(row)
cur.close()
conn.close()
4.6列出各种工作的最低工资及从事此工作的雇员姓名。
SELECT e.job ,e.sal ,e.ename
FROM emp e
WHERE sal IN (SELECT min(sal) FROM emp GROUP BY job);
4.7列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
SELECT e.ename, d.dname,dc.cn,ms.min_sal 最低薪金
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
JOIN (SELECT deptno, MIN(sal) min_sal FROM emp WHERE job = 'MANAGER' GROUP BY deptno) ms
ON ms.deptno = e.deptno
JOIN (select deptno , COUNT(*) cn from emp group by deptno) dc
ON e.deptno = dc.deptno AND e.job = 'MANAGER'
4.8列出所有员工的姓名以及其直接上级的姓名
SELECT e0.ename,e1.ename 上级
FROM emp e0
JOIN emp e1
ON e0.mgr =e1.empno
4.9列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
select e.empno , e.ename , d.dname
from emp e
join dept d
on e.deptno = d.deptno
left join emp e1
on e.mgr = e1.empno
where e.hiredate < e1.hiredate;