数据库实验.docx

1、数据库实验 集团文件版本号：（M928-T898-M248-WU2669-I2896-DQ586-M1988）数据库实验数据库原理实验指导实验前准备：请设计一个企业销售管理据库，其中需要保存的信息如下：员工信息，包括：员工编号、员工姓名、性别、所属部门、职称、到职日、生日、薪水、填表日期；客户信息，包括：客户号，客户名称，客户住址，客户电话、邮政编码；产品信息，包括：产品编号，产品名称；员工和客户可以签订订单，每签订一个订单，就要保存订单信息，包括：订单编号、客户号、业务员编号、订单金额、订货日期、出货日期、发票号码。此外，每个订单可能涉及到多种产品，每种产品可能被多个订单订购。因此需要每个订

2、单中每类产品的销售明细，包括每种产品的销售数量、单价、订单日期；要求：（1）给出系统的ER图（可以用word或其它画图工具，如Visio画），要求画出所有的实体，联系，属性以及联系的类型；（2）将ER图转换为关系模型；实验一实验名称：数据定义（2课时）一、实验目的1、理解数据库模式的概念，通过使用SQL SERVER企业管理器或者My Sql建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQL SERVER企业管理器的使用，并将得到的表生成脚本，然后保存。2、理解上述基本表之间的关系，建立关系表。3、掌握修改表结构的基本方法4、掌握索引和视图的创建方法二、实验环境

3、MS SQL SERVER或者My Sql。三、实验内容与步骤1、建立一个数据库和五张表的表结构。（1）/*员工人事表employee */emp_nochar(5)Not nullprimary key员工编号emp_namechar(10)Not null员工姓名sexchar(1)Not null性别deptchar(4)null所属部门titlechar(6)null职称date_hireddatetimenull到职日birthdaydatetimeNull生日salaryintnull薪水addrchar(50)null住址Mod_datedatetimeDefault(getda

4、te()操作日期Create datebase sale;create table employee( emp_no char(5) Not null primary key, emp_name char(10) Not null, sex char(1) Not null, dept char(4) null, title char(6) null, date_hired datetime null, birthday datetime null, salary int null, addr char(50) null, Mod_date datetime Default 0, constr

5、aint c1 check(性别 in(男,女) );（2）/*客户表customer */cust_idchar(5)Not nullprimary key客户号cust_namechar(20)Not null,客户名称addrchar(40)Not null,客户住址tel_nochar(10)Not null,客户电话zipchar(6)null邮政编码create TABLE customer( cust_id char(5) Not null primary key, cust_name char(20) Not null, addr CHAR(40) Not null, tel_

6、no char(10) Not null, zip char(6) null);（3）/*销售主表sales */order_noChar(5)Not nullprimary key订单编号cust_idchar(5)Not null,客户号sale_idchar(5)Not null,业务员编号tot_amtnumeric(9,2)null,默认0订单金额order_datedatetimenull,订货日期ship_datedatetimenull,出货日期invoice_nochar(10)null发票号码create TABLE sales( order_no char(5) Not

7、null primary key, cust_id char(5) Not null, sale_id CHAR(5) Not null, tot_amt numeric(9,2) null, order_date datetime null, ship_date datetime null, invoice_no char(10) null);（4）/*销货明细表 sales_item*/order_noChar(5)Not null,primary key订单编号prod_idchar(5)Not null,产品编号qtyintNot null默认0销售数量unit_pricenumeri

8、c(9,2)Not null默认0单价order_datedatetimenull订单日期create TABLE sales_item( order_no char(5) Not null , prod_id char(5) Not null, primary key(order_no,prod_id), qty INT Not null, unit_price numeric(9,2) Not null, order_date datetime null, CONSTRAINT FK_1 FOREIGN key(order_no) REFERENCES sales(order_no), C

9、ONSTRAINT FK_2 FOREIGN key(prod_id) REFERENCES product(prod_id);（5）/*产品名称表product */prod_idchar(5)Not nullprimary key产品编号prod_namechar(20)Not null产品名称create TABLE product( prod_id char(5) Not null primary key, prod_name char(20) Not null );2、建立5张表的关系图3、修改表结构，通过SQL语句修改表约束。?在表employee加入CHECK约束：输入的员工编号

10、必须以E开头的5位数编号，性别只能为M/F。/check对于MySQL不起作用，只能在插入数据的基础上修改use sale;ALTER TABLE employee add CHECK(sex LIKE E%);UPDATE employee SET sex=FWHERE sex=女;UPDATE employee SET sex=MWHERE sex=男;为销售主表sales中的发票编号字段建立UNIQUE约束。ALTER TABLE sales MODIFY invoice_no CHAR(10) UNIQUE NULL;所有性别属性，限制取值为f或m4、员工表建立唯一索引：emp_no属

11、性、升序；员工表建立聚集索引：emp_name属性、升序；CREATE UNIQUE INDEX emp_index ON employee(emp_no ASC);/MYSQL不支持此类语法5、创建视图：?视图只含上海客户信息，即客户号、客户姓名、住址。CREATE VIEW 上海客户信息 ASSELECT cust_id,cust_name,addrFROM customerWHERE addr LIKE 上海%;有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。CREATE VIEW emp_sales ASSELECT sal

12、e_id,emp_name,order_no,tot_amtFROM employee,salesWHERE employee.emp_no=sales.sale_id;四、实验报告实验二实验名称： 数据操纵（4课时）一实验目的1、要求学生熟练掌握添加、修改、删除数据的操作。2、要求学生熟练掌握数据查询操作。二、实验环境MS SQL SERVER 2000或者My SQL三、实验内容与步骤1.数据更新在每个表中插入若干条记录；- employeeINSERT employee VALUES(E0001,赵三,男,销售部,经理,2013/3/4,1992/3/4,8000,杭州,2013/4/2

13、);INSERT into employee values(E0002,赵四,M,销售部,成员,2017/3/5,1994/2/3,2500,泰州,2018/4/2);INSERT employee VALUES(E0003,钱四,男,销售部,组长,2015/3/4,1991/3/4,8000,杭州,2015/4/2);INSERT employee VALUES(E0004,钱行,男,后勤部,经理,2014/8/4,1981/9/8,10000,杭州,2015/4/2);INSERT employee VALUES(E0005,欧阳泽明,男,后勤部,组长,2016/8/4,1986/7/87

14、000,杭州,2017/4/2);INSERT employee VALUES(E0006,欧阳凤,女,后勤部,成员,2012/5/4,1989/3/5,7000,杭州,2016/4/2);INSERT employee VALUES(E0007,欧阳峰,男,宣传部,副经理,2013/5/4,1989/3/9,9000,泰州,2016/4/2);INSERT employee VALUES(E0008,欧阳创正,男,宣传部,成员,2017/8/8,1999/7/25,4000,泰州,2016/4/2);INSERT employee VALUES(E0009,王阳凤,女,宣传部,经理,2012

15、/7/13,1985/3/6,9090,杭州,2016/4/2);INSERT employee VALUES(E0010,王航,女,联络部,成员,2017/5/4,1997/8/15,4000,扬州,2016/4/2);INSERT employee VALUES(E0011,王凤,女,联络部,经理,2015/7/13,1989/3/6,9090,扬州,2016/4/2);INSERT employee VALUES(E0012,王立,男,联络部,副经理,2013/8/14,1989/5/19,9000,泰州,2016/4/2);- customer- sales- productINSER

16、T product VALUES(C0001,短袖);INSERT product VALUES(C0002,短裤);INSERT product VALUES(C0003,长袖);INSERT product VALUES(C0004,牛仔裤);INSERT product VALUES(C0005,七分裤);INSERT product VALUES(C0006,五分裤);INSERT product VALUES(C0007,外套);INSERT product VALUES(C0008,短裙);INSERT product VALUES(C0009,连衣裙);INSERT produc

17、t VALUES(C0010,衬衫);- sales_itemINSERT sales_item VALUES(S0010,C0001,77,1,2018/5/9);INSERT sales_item VALUES(S0009,C0002,146,12,2018/5/9);INSERT sales_item VALUES(S0008,C0003,126,18,2018/5/9);INSERT sales_item VALUES(S0007,C0004,124,9,2018/5/9);INSERT sales_item VALUES(S0006,C0005,128,12,2018/5/9);IN

18、SERT sales_item VALUES(S0005,C0006,124,42,2018/5/9);INSERT sales_item VALUES(S0004,C0007,446,52,2018/5/9);INSERT sales_item VALUES(S0003,C0008,846,12,2018/5/9);INSERT sales_item VALUES(S0002,C0009,1277,22,2018/5/9);INSERT sales_item VALUES(S0001,C0010,2240,30,2018/5/9); 将所有员工的薪水增加100；UPDATE employee

19、 SET salary=salary+100;将产品名称为A的产品的单价改为10/A为短袖UPDATE sales_itemSET unit_price=10WHERE prod_id=(SELECT prod_idFROM productWHERE prod_name=短袖);删除所有女性员工销售记录；DELETE sales_itemFROM salesLEFT JOIN employee ON sales.sale_id=employee.emp_no INNER JOIN sales_item ON sales.order_no=sales_item.order_noWHERE sex

20、=f;DELETE salesFROM salesLEFT JOIN employee ON sales.sale_id=employee.emp_no WHERE sex=f;删除订单金额小于100000的订单。/小于6000的订单DELETE FROM sales_itemWHERE order_no IN(SELECT order_noFROM salesWHERE tot_amt6000);DELETE FROM salesWHERE tot_amt7000;(5)选取订单金额最高的前10%的订单数据。/已恢复原始删除数据SELECT *FROM salesORDER BY tot_a

21、mtLIMIT 0,(SELECT COUNT(*) FROM sales)*0.2) ;(6)查找出职称为经理或职称为职员的女员工的信息。/成员SELECT *FROM employeeWHERE sex=f AND (title=经理 OR title=成员);(7)计算出一共销售了几种产品。SELECT COUNT(DISTINCT prod_id)FROM sales_item;(8)显示sale_item表中每种个别产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。SELECT prod_id , SUM(qty*unit_price) AS sale_mo

22、neyFROM sales_itemGROUP BY prod_idORDER BY sale_money;(9)计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。SELECT prod_id, SUM(qty*unit_price) AS sale_money,order_dateFROM sales_itemGROUP BY prod_idORDER BY order_date DESC,prod_id DESC;(10)由sales表中查找出销售金额最高的订单。SELECT MAX(tot_amt)FROM sales;(11)由sales表中查找出订单金额大于“E0

23、013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。/E0008，2018-05-24SELECT sale_id,tot_amtFROM salesWHERE tot_amt(SELECT tot_amtFROM sales WHERE sale_id=E0008 AND order_date=2018-05-24);(12)找出公司女业务员所接的订单。SELECT emp_name,sex,sale_id,order_no,tot_amtFROM sales,employeeWHERE sex=f AND sales.sale

24、_id=employee.emp_no;(13)找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。INSERT employee VALUES(E0015,王立,f,销售部,成员,2015/8/14,1989/5/21,5000,上海,2016/4/2);/已插入姓名相同的员工SELECT *FROM employee a,employee bWHERE a.emp_name=b.emp_name AND a.emp_nob.emp_no;(14)找出目前业绩未超过20000元的员工。/8000元SELECT emp_no,emp_nameFROM employeeWHERE e

25、mp_no IN (SELECT sale_idFROM salesWHERE tot_amt8000);(15)计算公司内各个部门的工资支出总和。SELECT dept,SUM(salary)FROM employeeGROUP BY dept;(16)计算每一产品销售数量总和与平均销售单价。SELECT prod_id,SUM(qty),avg(unit_price)FROM sales_itemGROUP BY prod_id;四、实验报告实验三实验名称：T-SQL编程（2课时）一、实验目的 1、掌握T-SQL编程方法。 2、掌握触发器、存储过程创建方法和原理二、实验环境MS SQL S

26、ERVER 2000以上版本三、实验内容与步骤（1）T-SQL的流程控制语句使用编写程序完成以下功能，在查询分析器中执行程序，并记录结果。 在employee 表中求某个部门年龄最大和最小的员工的信息，包括：编号，姓名，年龄。/每个部门SELECT dept,emp_no,emp_name,MAX(2018-YEAR(birthday) 最大年龄FROM employeeGROUP BY dept;SELECT dept,emp_no,emp_name,MIN(2018-YEAR(birthday) 最小年龄FROM employeeGROUP BY dept; 在employee表中先插入三条新记录，其中的dept字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示为“未分配”SELECT emp_no,emp_name, (CASE WHEN dept= THEN 未分配ELSE deptEND) AS 分配情况FROM employee; 查询employee表中年龄