数据库实验.docVIP

.数据库原理实验指导实验前准备：请设计一个企业销售管理据库，其中需要保存的信息如下：员工信息，包括：员工编号、员工姓名、性别、所属部门、职称、到职日、生日、薪水、填表日期；客户信息，包括：客户号，客户名称，客户住址，客户电话、邮政编码；产品信息，包括：产品编号，产品名称；员工和客户可以签订订单，每签订一个订单，就要保存订单信息，包括：订单编号、客户号、业务员编号、订单金额、订货日期、出货日期、发票号码。此外，每个订单可能涉及到多种产品，每种产品可能被多个订单订购。因此需要每个订单中每类产品的销售明细，包括每种产品的销售数量、单价、订单日期；要求：（1） 给出系统的ER图（可以用word或其它画图工具，如Visio画），要求画出所有的实体，联系，属性以及联系的类型；（2） 将ER图转换为关系模型；实验一实验名称：数据定义（2课时）一、实验目的1、理解数据库模式的概念，通过使用SQL SERVER企业管理器或者My Sql建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQL SERVER企业管理器的使用，并将得到的表生成脚本，然后保存。2、理解上述基本表之间的关系，建立关系表。3、掌握修改表结构的基本方法4、掌握索引和视图的创建方法二、实验环境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(getdate()操作日期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, constraint 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_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 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_pricenumeric(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), CONSTRAINT 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约束：输入的员工编号必须以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属性、升序；员工表建立聚集索引：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 sale_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);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/87000,杭州,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/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);- customerINSERT customer VALUES(K0001,李立,泰州,1902847477,225700);INSERT customer VALUES(K0002,李才,苏州,1322847472,295702);INSERT customer VALUES(K0003,王吉,苏州,1362847489,235701);INSERT customer VALUES(K0004,王嘉,扬州,1342847477,225400);INSERT customer VALUES(K0005,王洛,苏州,1379847477,221706);INSERT customer VALUES(K0006,端木镇,泰州,1382847477,215709);INSERT customer VALUES(K0007,端木释俗,北京,1262847477,225230);INSERT customer VALUES(K0008,孙可,上海,1322847477,225245);INSERT customer VALUES(K0009,孙立,北京,1762847477,225764);INSERT customer VALUES(K0010,孙苏,苏州,1092347477,212403);- salesINSERT sales VALUES(S0010,K0001,E0001,7477,2018/4/29,2018/5/29,201947292);INSERT sales VALUES(S0009,K0002,E0002,6477,2018/4/22,2018/5/29,201947232);INSERT sales VALUES(S0008,K0003,E0003,5432,2018/5/9,2018/5/29,201947235);INSERT sales VALUES(S0007,K0004,E0004,9432,2018/4/15,2018/5/29,201956235);INSERT sales VALUES(S0006,K0005,E0005,5432,2018/5/9,2018/5/29,201007235);INSERT sales VALUES(S0005,K0006,E0006,8432,2018/5/19,2018/5/29,201940235);INSERT sales VALUES(S0004,K0007,E0007,9432,2018/5/19,2018/5/29,208947205);INSERT sales VALUES(S0003,K0008,E0008,6432,2018/5/24,2018/5/29,201947260);INSERT sales VALUES(S0002,K0009,E0009,5890,2018/5/2,2018/5/29,201947209);INSERT sales VALUES(S0001,K0010,E0010,10383,2018/5/7,2018/5/29,201947468);- productINSERT 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 product 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);INSERT 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 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=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_amtLIMIT 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_moneyFROM 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表中查找出订单金额大于“E0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。/E0008，2018-05-24SELECT sale_id,tot_amtFROM salesWHERE tot_amt(SELECT tot_amtFROM salesWHERE 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_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 emp_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 SERVER 2000以上版本三、实验内容与步骤（1）T-SQL的流程控制语句使用编写程序完成以下功能，在查询分析器中执行程序，并记录结果。l 在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;l 在employee表中先插入三条新记录，其中的dept字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示为“未分配”SELECT emp_no,emp_name, (CASE WHEN dept= THEN 未分配ELSE deptEND) AS 分配情况FROM employee;l 查询employee表中年龄，并根据年龄输出所属阶段（青年、中年、老年）。SELECT emp_no,emp_name,CASE (2018-YEAR(birthday)/10WHEN 5 THEN 老年WHEN 4 THEN 中年else 青年ENDFROM employee;（2）存储过程的创建与调用按要求完成以下功能，并记录结果。l 创建一个存储过程info_1，完成的功能是在表employee、表customer和表sales中查询以下字段：部门、业务员编号、业务员姓名、客户姓名、订单金额、订货日期。CREATE PROCEDURE info_1()BEGIN SELECT dept,emp_name,cust_name,tot_amt,order_dateFROM employee,customer,salesWHERE employee.emp_no=sales.sale_id AND sales.cust_id=customer.cust_id;END;l 创建一个带有参数的存储过程emp_info，该存储过程根据传入的业务员编号，在employee中查询此业务员全部的信息。BEGINSELECT *FROM employeeWHERE emp_no=sale_id;ENDl 创建一个带有参数的存储过程emp_age，该存储过程根据传入的业务员编号，在employee中计算此业务员的年龄，并根据程序执行结果返回不同的值，程序执行成功，返回整数0，如果执行错误，则返回错误号。/此阶段使用SQL语句编写，MySQL语法并不是很了解create procedure emp_age 编号 char(10)sasselect (2018-year(生日)年龄from 员工人事表where 员工编号=编号if(编号 not in (select 员工编号 from 员工人事表 ) return 1 return 0l 执行上述存储过程declare return1 char(10)exec return1= emp_age E0001 select return1l 删除存储过程emp_infodrop procedure emp_age/在SQL server上另新建表代码为：create table 员工人事表(员工编号 char(5) Not null primary key,员工姓名 char(10) Not null,性别 char(1) Not null,所属部门 char(4),职称 char(6),到职日 datetime,生日 datetime,薪水 int,住址 char(50),操作日期 datetime Default(getdate()create table 客户表(客户号 char(5) Not null primary key,客户名称 char(20) Not null,客户住址 char(40) Not null,客户电话 char(10) Not null,邮政编码 char(6)create table 销售主表(订单编号 char(5) Not null primary key,客户号 char(5) Not null,业务员编号 char(5) Not null,订单金额 numeric(9,2) default(0),订货日期 datetime,出货日期 datetime,发票号码 char(10),foreign key(客户号) references 客户表(客户号)alter table 销售主表 add foreign key (业务员编号) references 员工人事表(员工编号)create table 产品名称表(产品编号 char(5) not null primary key,产品名称 char(20)not null)create table 销货明细表(订单编号 char(5) Not null,产品编号 char(5) Not null,销售数量 int Not null default(0),单价 numeric(9,2) Not null default(0),订单日期 datetime,constraint c1 primary key (订单编号,产品编号),constraint c2 foreign key (订单编号)references 销售主表(订单编号),constraint c3 foreign key(产品编号)references 产品名称表(产品编号),)- 员工人事表INSERT 员工人事表 VALUES(E0001,赵三,男,销售部,经理,2013/3/4,1992/3/4,8000,杭州,2013/4/2);INSERT into 员工人事表 values(E0002,赵四,M,销售部,成员,2017/3/5,1994/2/3,2500,泰州,2018/4/2);INSERT 员工人事表 VALUES(E0003,钱四,男,销售部,组长,2015/3/4,1991/3/4,8000,杭州,2015/4/2);INSERT 员工人事表 VALUES(E0004,钱行,男,后勤部,经理,2014/8/4,1981/9/8,10000,杭州,2015/4/2);INSERT 员工人事表 VALUES(E0005,欧阳泽明,男,后勤部,组长,2016/8/4,1986/7/87000,杭州,2017/4/2);INSERT 员工人事表 VALUES(E0006,欧阳凤,女,后勤部,成员,2012/5/4,1989/3/5,7000,杭州,2016/4/2);INSERT 员工人事表 VALUES(E0007,欧阳峰,男,宣传部,副经理,2013/5/4,1989/3/9,9000,泰州,2016/4/2);INSERT 员工人事表 VALUES(E0008,欧阳创正,男,宣传部,成员,2017/8/8,1999/7/25,4000,泰州,2016/4/2);INSERT 员工人事表 VALUES(E0009,王阳凤,女,宣传部,经理,2012/7/13,1985/3/6,9090,杭州,2016/4/2);INSERT 员工人事表 VALUES(E0010,王航,女,联络部,成员,2017/5/4,1997/8/15,4000,扬州,2016/4/2);INSERT 员工人事表 VALUES(E0011,王凤,女,联络部,经理,2015/7/13,1989/3/6,9090,扬州,2016/4/2);INSERT 员工人事表 VALUES(E0012,王立,男,联络部,副经理,2013/8/14,1989/5/19,9000,泰州,2016/4/2);- 客户表INSERT 客户表 VALUES(K0001,李立,泰州,1902847477,225700);INSERT 客户表 VALUES(K0002,李才,苏州,1322847472,295702);INSERT 客户表 VALUES(K0003,王吉,苏州,1362847489,235701);INSERT 客户表 VALUES(K0004,王嘉,扬州,1342847477,225400);INSERT 客户表 VALUES(K0005,王洛,苏州,1379847477,221706);INSERT 客户表 VALUES(K0006,端木镇,泰州,1382847477,215709);INSERT 客户表 VALUES(K0007,端木释俗,北京,1262847477,225230);INSERT 客户表 VALUES(K0008,孙可,上海,1322847477,225245);INSERT 客户表 VALUES(K0009,孙立,北京,1762847477,225764);INSERT 客户表 VALUES(K0010,孙苏,苏州,1092347477,212403);- 销售主表sINSERT 销售主表s VALUES(S0010,K0001,E0001,7477,2018/4/29,2018/5/29,201947292);INSERT 销售主表s VALUES(S0009,K0002,E0002,6477,2018/4/22,2018/5/29,201947232);INSERT 销售主表s VALUES(S0008,K0003,E0003,5432,2018/5/9,2018/5/29,201947235);INSERT 销售主表s VALUES(S0007,K0004,E0004,9432,2018/4/15,2018/5/29,201956235);INSERT 销售主表s VALUES(S0006,K0005,E0005,5432,2018/5/9,2018/5/29,201007235);INSERT 销售主表s VALUES(S0005,K0006,E0006,8432,2018/5/19,2018/5/29,201940235);INSERT 销售主表s VALUES(S0004,K0007,E0007,9432,2018/5/19,2018/5/29,208947205);INSERT 销售主表s VALUES(S0003,K0008,E0008,6432,2018/5/24,2018/5/29,201947260);INSERT 销售主表s VALUES(S0002,K0009,E0009,5890,2018/5/2,2018/5/29,201947209);INSERT 销售主表s VALUES(S0001,K0010,E0010,10383,2018/5/7,2018/5/29,201947468);- 产品名称表INSERT 产品名称表 VALUES(C0001,短袖);INSERT 产品名称表 VALUES(C0002,短裤);INSERT 产品名称表 VALUES(C0003,长袖);INSERT 产品名称表 VALUES(C0004,牛仔裤);INSERT 产品名称表 VALUES(C0005,七分裤);INSERT 产品名称表 VALUES(C0006,五分裤);INSERT 产品名称表 VALUES(C0007,外套);INSERT 产品名称表 VALUES(C0008,短裙);INSERT 产品名称表 VALUES(C0009,连衣裙);INSERT 产品名称表 VALUES(C0010,衬衫);- 销货明细表INSERT 销货明细表 VALUES(S0010,C0001,77,1,2018/5/9);INSERT 销货明细表 VALUES(S0009,C0002,146,12,2018/5/9);INSERT 销货明细表 VALUES(S0008,