西安交通大学数据库实验报告及代码

计算机的同学们，我这份是计试的实验报告，可能和你们的实验内容不一样

2024年补充：若你是研究生，选修了侯老师的《数据库理论与技术》课程并发现这篇文章，那么请看这篇博文：西安交通大学《数据库理论与技术》课程实验+期末考试资料全通关_西安交通大学数据库实验-CSDN博客

---

 第一次实验无需代码，第三次实验只用navicat点点按钮。

四次实验代码及报告下载：

链接: https://pan.baidu.com/s/13UwgODXT6oHqr5lyxmlYsw 提取码: uqga

---

实验要求：

（实验1）

根据给定的需求说明，以ERWIN 7.1 为工具完成以下数据库建模操作:

1) 完成数据库概念模型设计，绘制ER(IDFE1X)模式图。（具体题目见数据库建模实验（上）.doc）

2) 完成数据库逻辑模型设计，生成相关的SQL建表语句。

3) 提交以下数据库设计文档：

     a. ERWIN数据模型文件(即.erwin文件)；

     b. 由ERWIN生成的SQL建表语句文件（即.DDL文件）。

---

（实验2）

在MySQL样本数据库上完成下列查询：
(1)    查询每个部门(departments)的编号(dept_no),名称(dept_name),在该部门工作过的雇员(employees)人数,最低工资(salary),平均工资,最高工资及工资总额；
(2)    查询每个部门(departments)的编号(dept_no),名称(dept_name),及各个时间段(from_date,to_date)担任该部门经理(dept_manager)的雇员的编号(emp_no)和姓名(first_name+last_name)，并按时间段先后显式；
(3)    查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工资额(salary),并按时间段先后显式；
(4)    查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)的工作部门名称(dept_name),并按时间段先后显式；
(5)    查询每位雇员的编号(emp_no),姓名(first_name+last_name),及任职过的部门数；
(6)    查询每位雇员的编号(emp_no),姓名(first_name+last_name),及各个时间段(from_date,end_data)担任的职务(title),并按时间段先后显式；
(7)    查询担任每种职务(title)的雇员人数；
(8)    查询每个部门中担任每种职务(title)的雇员人数；
(9)    查询每位雇员的编号(emp_no),姓名(first_name+last_name),及工资额最高的时间段(from_date,end_data)及其工资额；
(10)    查询所有曾经担任过部门经理(dept_manager)的雇员的编号(emp_no)和姓名(first_name+last_name);
(11)    按时间段(from_date,end_data)先后列出每个部门(departments)的编号(dept_no),名称(dept_name),及其经理的姓名(first_name+last_name)；
(12)    查询所有曾经在‘Development’工作过雇员的编号(emp_no),姓名(first_name+last_name),及时间段(from_date,end_data)；
(13)    查询曾经在所有部门都工作过的雇员的编号(emp_no),姓名(first_name+last_name)；
(14)    在dept_emp表中插入适当数据使得至少3个以上雇员满足上一题的查询要求。

请写出上述每一个查询的SQL语句并将执行结果输出到文件results.txt中。

---

（实验3）

使用SQL EXPLAIN工具（具体方法见：MySQL 性能优化神器 Explain 使用分析）对上一题中的每一条SQL语句进行分析，生成相应的查询执行计划并将查询执行计划保存到文件sql_explians.txt中。

使用SQL EXPLAIN工具（具体方法见：MySQL 性能优化神器 Explain 使用分析）对下列SQL查询语句进行分析，判断哪些索引将可以对下列查询产生加速作用，并通过实验进行验证。
SELECT TITLE,DEPT_NAME,GENDER, COUNT(DISTINCT E.EMP_NO)
FROM DEPARTMENTS D,DEPT_EMP DE,EMPLOYEES E,SALARIES S,TITLES T
WHERE D.DEPT_NO=DE.DEPT_NO AND DE.EMP_NO=E.EMP_NO AND 
E.EMP_NO=S.EMP_NO AND E.EMP_NO=T.EMP_NO
GROUP BY  TITLE,DEPT_NAME,GENDER
ORDER BY  TITLE,DEPT_NAME,GENDER;
在红色上的列建索引
比较速度：
1.不建索引
2.建索引
给出实验过程和结果。

---

（实验4）

嵌入式实验：
题目：
求身处两个或两个以上部门的员工信息（需要包含emp_np、first_name、emp_no_leader、dept_manager），并利用代码在mysql中创建一个新表并将结果输出进去（注：只需要保存多个部门中的第一个及多个领导中的第一个），并且要求代码运行耗时和时间复杂度尽可能的低。
要求：
任何操作都必须利用代码完成（需要利用嵌入式sql读取数据、建表、输出数据）
提示：
需要employees表（姓名）、dept_emp（工号、两个部门、及部门名称）、dept_manager（领导名称）
从dept_emp中找出在两个部门的人，进而确定他们的离职日期部门名称，依据他们的部门名称s去寻找领导名称s

编写上机实验报告。
 

---

第二次实验的代码：

2.
SELECT departments.dept_no, departments.dept_name, employees.emp_no,from_date, to_date, CONCAT(first_name,' ',last_name) 
FROM departments, dept_manager, employees
WHERE departments.dept_no=dept_manager.dept_no AND dept_manager.emp_no=employees.emp_no
ORDER BY departments.dept_no
INTO OUTFILE 'F:/mysql_result/2.txt'

3.
SELECT employees.emp_no, CONCAT(first_name,' ',last_name), from_date, to_date, salary 
FROM salaries, employees
WHERE employees.emp_no=salaries.emp_no
ORDER BY employees.emp_no
INTO OUTFILE 'F:/mysql_result/3.txt'

4.
SELECT employees.emp_no, CONCAT(first_name,' ',last_name), from_date, to_date, departments.dept_name 
FROM departments, employees,dept_emp
WHERE employees.emp_no=dept_emp.emp_no AND dept_emp.dept_no=departments.dept_no
ORDER BY employees.emp_no,from_date
INTO OUTFILE 'F:/mysql_result/4.txt'

5.
SELECT employees.emp_no, CONCAT(first_name,' ',last_name), COUNT(DISTINCT dept_emp.dept_no) 
FROM employees,dept_emp
WHERE employees.emp_no=dept_emp.emp_no
GROUP BY employees.emp_no
INTO OUTFILE 'F:/mysql_result/5.txt'

6.
SELECT employees.emp_no, CONCAT(first_name,' ',last_name), from_date,to_date,title
FROM employees,titles
WHERE employees.emp_no=titles.emp_no
ORDER BY employees.emp_no,from_date
INTO OUTFILE 'F:/mysql_result/6.txt'

7.
SELECT title,COUNT(title)
FROM titles
#WHERE employees.emp_no=titles.emp_no
GROUP BY title
INTO OUTFILE 'F:/mysql_result/7.txt'

8.
SELECT title,COUNT(DISTINCT titles.emp_no),dept_no
FROM titles,employees,dept_emp
WHERE employees.emp_no=titles.emp_no AND employees.emp_no=dept_emp.emp_no
GROUP BY dept_emp.dept_no,titles.title
ORDER BY dept_emp.dept_no
INTO OUTFILE 'F:/mysql_result/8.txt'

9.
SELECT CONCAT(first_name,' ',last_name),MAX(salary),employees.emp_no,from_date,to_date
FROM salaries,employees
WHERE employees.emp_no=salaries.emp_no
GROUP BY employees.emp_no
ORDER BY from_date
INTO OUTFILE 'F:/mysql_result/9.txt'

10.
SELECT DISTINCT(CONCAT(first_name,' ',last_name)),employees.emp_no
FROM dept_manager,employees
WHERE employees.emp_no=dept_manager.emp_no
#ORDER BY employees.emp_no
INTO OUTFILE 'F:/mysql_result/10.txt'

11.
SELECT departments.dept_no,dept_name,CONCAT(first_name,' ',last_name)
FROM dept_manager,employees,departments
WHERE employees.emp_no=dept_manager.emp_no AND departments.dept_no=dept_manager.dept_no
#GROUP BY departments.dept_no
ORDER BY departments.dept_no,from_date
INTO OUTFILE 'F:/mysql_result/11.txt'

12.
SELECT employees.emp_no,CONCAT(first_name,' ',last_name),from_date,to_date
FROM dept_emp,employees,departments
WHERE employees.emp_no=dept_emp.emp_no AND departments.dept_name='Development' AND departments.dept_no=dept_emp.dept_no
#GROUP BY departments.dept_no
ORDER BY departments.dept_no,from_date
INTO OUTFILE 'F:/mysql_result/12.txt'

13.
SELECT DISTINCT(employees.emp_no),CONCAT(first_name,' ',last_name)
FROM dept_emp,employees
WHERE NOT EXISTS (
SELECT *
FROM departments
WHERE NOT EXISTS (
SELECT *
FROM dept_emp
WHERE departments.dept_no=dept_emp.dept_no AND dept_emp.emp_no=employees.emp_no
)
)
#GROUP BY departments.dept_no
ORDER BY employees.emp_no
INTO OUTFILE 'F:/mysql_result/13.txt'

---

第四次实验的代码：

import pymysql
 
# 打开数据库连接
db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='',
                     database='employees')
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()

index=0
'''
    sql_create_database = 
            CREATE TABLE if not exists `empWith2dep` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `song_id` int(11) DEFAULT NULL,
            PRIMARY KEY (`id`)
            )
'''

mylist=[]

# SQL 查询语句

sql_employees = "SELECT emp_no,first_name \
       FROM employees"
sql_dept_emp = "SELECT emp_no,dept_no,from_date \
       FROM dept_emp"
'''
sql_departments = "SELECT * \
       FROM departments"
'''
sql_dept_manager = "SELECT dept_no,emp_no,from_date,to_date \
       FROM dept_manager"

cursor.execute("DROP TABLE IF EXISTS RESULT")
sql_create = "CREATE TABLE result \
                (emp_no INT, \
                 first_name CHAR(14), \
                 dept_no CHAR(4), \
                 manager_no INT)"

cursor.execute(sql_create)
try:
   # 执行SQL语句
    cursor.execute(sql_employees)
   # 获取所有记录列表
    employees = cursor.fetchall()
   #同上 
    cursor.execute(sql_dept_emp)
    dept_emp = cursor.fetchall()

    cursor.execute(sql_dept_manager)
    dept_manager = cursor.fetchall()
    #遍历emloyees表
    for row in employees:
        emp_no = row[0]                   #记录员工编号数据和first_name
        first_name = row[1]
        '''
        debug代码

        if emp_no<=10010:
            print ("aaa")
            print("emp_no=",str(emp_no))
            print("index=",index)
        '''
        #防止越界
        #print("index=",index)
        if index>=len(dept_emp)-1:
            break
        #判断该员工是否满足曾在两个及以上的部门工作过
        if dept_emp[index][0]==dept_emp[index+1][0] and dept_emp[index][0]==emp_no:
            #print("bbb")
            #若有，记录员工在该部门的上岗日期和部门编号
            from_date=dept_emp[index][2]
            dept_no=dept_emp[index][1]
            manager_no=0
            #接下来遍历部门领导表，寻找该人的第一个领导
            for row1 in dept_manager:
                if row1[0]==dept_no and row1[2]<=from_date and row1[3]>=from_date:
                    manager_no=row1[1]
                    break
            #把结果写进mylist
            mylist.append([emp_no,first_name,dept_no,manager_no])
            sql_insert = "INSERT INTO result ( \
                            emp_no,first_name,dept_no,manager_no) \
                            VALUES ('%d','%s','%s','%d')" %(emp_no,first_name,dept_no,manager_no)
            cursor.execute(sql_insert)
            #db.commit()
            #更新index索引值，该值的只增不减保证了时间复杂度
            while index<len(dept_emp)-1 and dept_emp[index][0]==dept_emp[index+1][0]:
                #print("ccc")
                index+=1
        index+=1
        '''
        debug代码
        if emp_no<=10010:
            print("new index=",index)
        '''
#若出错，输出错误原因                
except Exception as e:
    print ("Error: unable to fetch data")
    print (e)
#结果保存到文件
f=open("F:/mysql_result/result.txt","w")

for line in mylist:
    f.write(str(line)+'\n')
db.commit()    
# 关闭数据库连接
db.close()