数据库实验示例

示例1实验一实验名称：SQL语句的应用（6课时）一、实验目的通过使用SQL SERVER企业管理器建立表。

模式为人事表、客户表、销售表、销售明细表、产品表。

理解数据库模式的概念，理解主键约束、外键约束、UNIQUE约束和CHECK 约束。

通过SQL SERVER企业管理器建立表间的约束。

将得到的表生成脚本，保存。

二、实验环境是MS SQL SERVER 2000的中文客户端。

三、实验示例create table employee1(emp_no char(5) not null,constraint emp_nochk check(emp_no like '[E-F][0-9][0-9][0-9][0-9]'),emp_name char(10) not null,emp_sex char(1) not null,constraint emp_sexchk check(emp_sex in ('m','f')),emp_phone char (13) not null,constraint emp_phonechk check(emp_phone like '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), emp_add varchar(60) not null,emp_salary char(5) not null,constraint emp_salarychk check (emp_salary between 60000 and 80000))go四、实验内容与步骤1、建立五张表，每张表至少需要20条记录。

（1）/*员工人事表employee */（2）/*客户表customer */（3）/*销售主表sales */（4）/*销货明细表sale_item */（5）/*产品名称表product */2、建立表的同时创建表的约束。

（1）为每张表建立主键约束。

（2）通过拖放操作加入外键。

（3）在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。

（4）为销售主表sales中的发票编号字段建立UNIQUE约束。

3、通过快捷菜单得到脚本。

五、实验报告实验二实验名称：使用SELECT、UPDATE/INSERT/DELETE语句。

（2课时）一、实验目的理解SELECT语句的操作和基本使用方法，熟练使用UPDATE/INSERT/DELETE语句进行表操作。

二、实验环境三、实验示例1、查找出职称为经理和职称为职员的女员工的姓名、职称、性别。

select emp_name,title,sexfrom employeewhere title='经理'or title='职员'and sex='F'2、选取销售数量最多的前5条订单订单号、数量。

select top 5 with ties order_no,sup_id,qtyfrom sale_itemorder by qty desc3、计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。

select prod_id,qty,unit_price,(qty*unit_price)tot_amtfrom sale_itemorder by tot_amt desc四、实验内容与步骤1、查找所有经理的姓名、职称、薪水。

2、查找出姓“王”并且姓名的最后一个字为“功”的员工。

3、添加一条记录至employee表（用INSERT…..INTO）。

4、将每个员工的薪水上调3%。

5、查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。

6、在表sales中挑出销售金额大于等于10000元订单。

7、选取订单金额最高的前10%的订单数据。

8、查找出职称为经理或职称为职员的女员工的信息。

9、删除sales表中作废的订单（其发票号码为I000000004），其订货明细数据也一并删除。

10、计算出一共销售了几种产品。

11、显示sale_item表中每种个别产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。

12、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。

五、实验报告实验三实验名称：表连接JOIN（2课时）一、实验目的理解JOIN语句的操作和基本使用方法，掌握内连接、外连接、自身连接的概念和使用。

二、实验环境三、实验示例1、检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。

select a.prod_id,a.qty,a.unit_price,b.prod_namefrom sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/on (a.prod_id=b.pro_id) and a.qty>2order by a.prod_id2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。

select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addrfrom employee as a inner join employee as bon (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr)四、实验内容与步骤1、查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。

2、检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。

3、检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。

4、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。

并分析比较检索的结果。

五、实验报告实验四实验名称：SELECT的嵌套使用，实现复杂查询（2课时）一、实验目的掌握SELECT语句的嵌套使用，实现表的复杂查询，进一步理解SELECT语句的高级使用方法。

二、实验环境三、实验示例1、由employee表中查找出薪水最高的员工信息。

select *from employeewhere salary=(select max(salary )from employee )2、由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接每一张订单的金额”的所有订单。

select *from saleswhere tot_amt>all(select tot_amtfrom saleswhere sale_id='E0013'and order_date='1996/10/15')order by tot_amt3、利用begin...end语句实现数据查询，查询出employee表中所有女职工的平均工资和住址在＂上海市＂的所有女职工的平均工资beginselect avg(salary)from employeewhere sex like 'f'unionselect avg(salary)from employeewhere sex like 'f' and addr like '上海市%'end四、实验内容与步骤1、由sales表中查找出销售金额最高的订单。

2、由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。

3、找出公司女业务员所接的订单。

4、找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。

5、找出目前业绩未超过200000元的员工。

6、计算公司内各个部门的工资支出总和。

7、计算每一产品销售数量总和与平均销售单价。

五、实验报告实验五实验名称：视图的建立与使用（2课时）一、实验目的掌握视图的定义与工作原理。

二、实验环境三、实验示例1、create view emp_view asselect emp_no,emp_name,salaryfrom employeewhere sex='f'2、create view sale_item_view asselect sales.order_no,sales.order_date,prod_id,qtyfrom sales,sale_itemwhere sales.order_no=sale_item.order_no/*注意：插入数据时要考虑数据列是否有默认值或允许为空，否则INSERT失败。

*/insert into sale_item_view(order_no,order_date)values(10010,'1996/12/20')insert into sale_item_view(prod_id,qty)values('p0010',8)四、实验内容与步骤1、创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。

2、对视图添加一条记录数据。

（注意：分别查看customer表和该视图的结果。

）3、删除视图中所有姓“王”的客户数据。

4、通过视图修改表内某一客户的姓名。

5、有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。

6、将上述视图中订单号为10001的记录的销售总金额改为60000。

7、给上述视图添加一条记录数据。

8、删除上述视图。

五、实验报告实验六实验名称：存储过程建立与调用（含带参存储过程的建立与调用）（2课时）一、实验目的理解存储过程的概念、建立和调用方法。

二、实验环境三、实验示例1、模糊查询create procedure sp_empname @E_name varchar(10) asselect a.emp_name,a.dept,b.tot_amtfrom employee a inner join sales bon a.emp_no=b.sale_idwhere a.emp_name like @E_namegoexec sp_empname '陈%'2、利用存储过程计算出’E0014’业务员的销售总金额。