数据库实验 报告交.doc

实 验 报 告数据库原理与应用上机实验20082009学年第 二 学期学院（部）管理学院班级代号姓 名成 绩指导教师汪明艳实验一一、实验名称：建立数据库和基本表结构（2课时）二、实验目的： 理解数据库模式的概念，通过使用SQL SERVER企业管理器建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQL SERVER企业管理器的使用，并将得到的表生成脚本，然后保存。另外，帮助学生理解上述基本表之间的关系，从而正确、合理地建立表结构，并输入相应记录信息。三、实验内容（1）/*员工人事表employee */emp_nochar(5)Not nullprimary key员工编号emp_namechar(10)Not null员工姓名sexchar(1)Not null性别deptchar(4)Not null所属部门titlechar(6)Not null职称date_hireddatetimeNot null到职日birthdaydatetimeNull生日salaryintNot null薪水addrchar(50)null住址Mod_datedatetimeDefault(getdate()操作者 create table employee (emp_no char(5) not null check(emp_no between E0000andE9999) primary key, emp_name char(10) UNIQUE not null, sex char(1) not null check(sex=M or sex=F), dept char(4) not null, title char(6) Not null, date_hired datetime Not null, birthday datetime Null, salary int Not null, addr char(50) null, Mod_date datetime Default(getdate() );（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_nointNot nullprimary key订单编号cust_idchar(5)Not null,客户号sale_idchar(5)Not null,业务员编号tot_amtnumeric(9,2)Not null,订单金额order_datedatetimeNot null,订货日期ship_datedatetimeNot null,出货日期invoice_nochar(10)Not null发票号码 create table sales (order_no int Not null primary key, cust_id char(5) Not null, sale_id char(5) Not null, tot_amt numeric(9,2) Not null, order_date datetime Not null, ship_date datetime Not null, invoice_no char(10) Not null unique foreign key(cust_id) references customer(cust_id),foreign key(sale_id) references employee (emp_no) );（4）/*销货明细表sale_item */order_nointNot null,primary key订单编号prod_idchar(5)Not null,产品编号qtyintNot null销售数量unit_pricenumeric(9,2)Not null单价order_datedatetimenull订单日期 create table sale_item (order_no int Not null , prod_id char(5) Not null , qtyint Not null, unit_price numeric(9,2) Not null, order_date datetime null , primary key(order_no,prod_id), constraint sales_sale_item foreign key(order_no) references sales(order_no), constraint product_sale_item 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 );四、实验小结在用命令建表结构时，要注意标点符号是否在半角状态下输入，否则命令是无法操作的。还要分清楚每张表的主码是哪个（或哪几个）属性列，注意属性值的唯一性。建立这些内容还是比较简单的，但是在建立外码和check约束时就比较麻烦，一开始没有分清楚哪些是主键表，哪些是外键表，造成了关系图建立的错误，所以一定要在仔细阅读表内容，分清主从关系后，再建立关系图。在建立表结构的过程中，使我们对SQL SERVER的操作更为熟悉，为以后命令的编译打下了基础。实验二一、实验名称：添加表记录（4课时）二、实验目的掌握修改表结构的基本方法，熟练使用INSERT语句为每张表追加表记录。三、实验内容与步骤1、 通过企业管理器修改表结构。2、 初步理解表之间的关系，用insert命令输入有效记录（每张表至少需要8条记录）。(1) 对employee表的数据插入:insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary,addr)values(E0001,王晓明,M,业务,职员,1988/01/21,43200,上海)insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary,addr)values(E0002,刘平,M,业务,职员,1980/02/21,43590,上海)insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary,addr)values(E0003,张钰,F,销售,职员,1993/02/14,31557,浙江)insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary,addr)values(E0004,朱丹,F,业务,职员,1995/04/06,30890,上海)insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary)values(E0005,王凯超,M,生产,职员,1985/09/11,38583)insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary)values(E0006,顾云,M,生产,职员,1987/10/21,37564)insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary,addr)values(E0007,卢晓东,M,生产,经理,1985/07/07,54588,上海)insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary,addr)values(E0013,李维,F,业务,经理,1987/09/20,59573,上海) (2) 对customer表的数据插入:insert into customer (cust_id,cust_name,addr,tel_no, zip)values(0285,王凤,上海,65646822,201800)insert into customer (cust_id,cust_name,addr,tel_no)values(0295,张海,武汉,68239451)insert into customer (cust_id,cust_name,addr,tel_no, zip)values(0357,刘兴成,上海,65387452,201800)insert into customer (cust_id,cust_name,addr,tel_no)values(0391,顾叔荣,北京,82696227)insert into customer (cust_id,cust_name,addr,tel_no)values(0581,王如梦,青岛,38941037)insert into customer (cust_id,cust_name,addr,tel_no)values(0784,邵成,河南,26470782)insert into customer (cust_id,cust_name,addr,tel_no)values(0745,斐圣辰,北京,74503294)insert into customer (cust_id,cust_name,addr,tel_no, zip)values(0816,沈尚逸,上海,65646822,201800)(3) 对sales表的数据插入:insert into sales (order_no, cust_id, sale_id, tot_amt, order_date, ship_date, invoice_no)values(10001,0285,E0002,19854,1999/01/09,1999/01/15,00001)insert into sales (order_no, cust_id, sale_id, tot_amt, order_date, ship_date, invoice_no)values(10002,0295,E0001,24087,1999/01/21,1999/02/02,00002)insert into sales (order_no, cust_id, sale_id, tot_amt, order_date, ship_date, invoice_no)values(10003,0357,E0004,34581,1999/02/11,1999/02/22,00003)insert into sales (order_no, cust_id, sale_id, tot_amt, order_date, ship_date, invoice_no)values(10004,0391,E0002,18724,1999/02/24,1999/03/07,00004)insert into sales (order_no, cust_id, sale_id, tot_amt, order_date, ship_date, invoice_no)values(10005,0581,E0013,45213,1999/03/05,1999/03/12,00005)insert into sales (order_no, cust_id, sale_id, tot_amt, order_date, ship_date, invoice_no)values(10006,0784,E0001,51274,1999/03/15,1999/03/21,00006)insert into sales (order_no, cust_id, sale_id, tot_amt, order_date, ship_date, invoice_no)values(10007,0745,E0004,49572,2001/03/17,2001/03/25,00007)insert into sales (order_no, cust_id, sale_id, tot_amt, order_date, ship_date, invoice_no)values(10008,0816,E0013,20578,1996/10/15,1996/10/20,00008)(4) 对sale_item表的数据插入:insert into sale_item (order_no, prod_id, qty, unit_price)values(10001,1001,66,23)insert into sale_item (order_no, prod_id, qty, unit_price)values(10002,1002,30,20)insert into sale_item (order_no, prod_id, qty, unit_price)values(10003,1003,100,3)insert into sale_item (order_no, prod_id, qty, unit_price)values(10004,1004,22,42)insert into sale_item (order_no, prod_id, qty, unit_price)values(10005,1005,27,36)insert into sale_item (order_no, prod_id, qty, unit_price)values(10006,1006,76,15)insert into sale_item (order_no, prod_id, qty, unit_price)values(10007,1007,14,41)insert into sale_item (order_no, prod_id, qty, unit_price)values(10008,1008,17,35)(5) 对product表的数据插入:insert into product (prod_id, prod_name)values(1001,洗发水)insert into product (prod_id, prod_name)values(1002,护发素)insert into product (prod_id, prod_name)values(1003,肥皂)insert into product (prod_id, prod_name)values(1004,沐浴露)insert into product (prod_id, prod_name)values(1005,爽肤水)insert into product (prod_id, prod_name)values(1006,牙膏)insert into product (prod_id, prod_name)values(1007,润肤露)insert into product (prod_id, prod_name)values(1008,美白霜)四、实验小结在用insert命令输入表信息时，一定要按照建表时各列属性的型来输入内容，不能随意输入，否则会发生错误，还要注意输入值的长度，不要超过限制。更要注意属性的一些特殊约束，不要违反约束条件，有些属性不允许为空，所以一定要有值，插入数据时不能让它为空。表与表之间是通过外码建立相应的关系连接的，所以在输入值时也要注意参照完整性的约束，外码要根据主码建立。在插入表记录的过程中，使我们对insert的语句有了熟练地掌握，也对每张表的基本信息有了更全面的了解。在记录插入的过程中还要注意与实验三中的查询相对应，有些信息是必须要有的，否则接下来的实验就无法进行了。实验三一、实验名称：简单查询（2课时）二、实验目的理解SELECT语句的操作和基本使用方法，掌握简单查询、连接查询、嵌套查询、视图。(4课时)三、实验内容与步骤1查找表中所有姓刘的职工的工号，部门，薪水。select emp_no,dept,salaryfrom employeewhere emp_name like 刘%2查找所有定单金额高于20000的所有客户编号。select cust_idfrom sales where tot_amt20000; 3查找出职称为经理和职称为职员的女员工的姓名、职称、性别。select emp_name, title,sexfrom employeewhere title in(经理,职员) and sex=F;4、选取销售数量最多的前5条订单订单号、数量。select top 5 order_no, qty from sale_item order by qty desc5、按部门进行汇总，统计每个部门的总工资select sum(salary),deptfrom employeegroup by dept6、检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。select d_id,prod_name,qty,unit_pricefrom product,sale_itemwhere qty2and d_id=sale_d_id7、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。select emp_name,sex,title,salary,addr from employee where addr=上海8、由employee表中查找出薪水最高的员工信息。select *from employeewhere salary=( select max(salary)from employee)9、由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接每一张订单的金额”的所有订单。select order_nofrom saleswhere tot_amt(select tot_amtfrom saleswhere sale_id=E0013and order_date=1996-10-15)11、创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。create view s_customer as select cust_id, cust_name, addr from customer where add