实验十SQLServer2000数据库综合操作(要求)

实验十 SQL Server 2000 的数据库综合操作一、实验目的进一步掌握 SQL Server 2000 数据库及其组成。

进一步熟悉主要数据库对象的操作。

提高应用企业管理器管理 SQL Server 2000 数据库的能力。

提高 T-SQL 语句的编写能力。

进一步明确数据库管理的主要功能。

二、实验要求创建一个数据库及数据库中对象。

三、实验步骤1. 打开查询分析器，以 T-SQL 语句完成后续操作。

2. 以默认方式创建数据库eshop。

在查询分析器中输入创建数据库eshop的SQL代码如下所示：CREATE DATABASE eshop3. 创建eshop数据库中表 members、products 和 orders（注意添加列和表的约束）。

在查询分析器中输入以下 SQL 代码以分别创建数据表 members、products 和 orders 及其相应的约束：USE eshopCREATE TABLE members (m_account VARCHAR(20) PRIMARY KEY, m_name VARCHAR(20),m_sex CHAR(2), m_birth DATETIME, m_address VARCHAR(50),m_salary MONEY, m_password VARCHAR(20))GOCREATE TABLE products (p_no CHAR(10) PRIMARY KEY, p_name VARCHAR(30),p_date DATETIME, p_quantity INT, p_price MONEY,p_information VARCHAR(50))GOCREATE TABLE orders(m_account VARCHAR(20), p_no CHAR(10), o_quantity INT,o_date DATETIME, o_confirm_state CHAR(1), o_pay_state CHAR(1),o_send_state CHAR(1) CONSTRAINT pk_Orders_mAccount_pNo_ODate PRIMARY KEY(m_account, p_no, o_date),CONSTRAINT fk_Orders_mAccount FOREIGN KEY(m_account) REFERENCES members(m_account), CONSTRAINT fk_Orders_pNo FOREIGN KEY(p_no) REFERENCES products(p_no))GO4. 创建商品价格不能超过 10000 的触发器 t_price。

当插入或更新数据表 products 时，如果价格超过 10000 ，则弹出消息“价格不合法”，创建该触发器的 SQL 代码：create TRIGGER t_price on productsafter insertaSdeclare @price moneyselect @price=p_pricefrom insertedif @price >1000beginrollback transactionraiserror('商品拆超出范围',16,10)end5. 创建年龄在 15 至 100 之间的触发器 t_birth。

当插入或更新数据表 members 时，如果年龄不在 15 至 100 之间，则弹出消息“年龄不合法”，创建该触发器的 SQL 代码：create TRIGGER t_birthon membersfor insert ,updateaSdeclare @age intselect @age=year(getdate())-year(m_birth)from insertedif @age not between 15 and 60beginrollback transactionraiserror('年龄不合法',16,10)end6. 往各表中添加样例数据，验证约束和触发器对数据完整性的作用。

分别往数据表 members 、products 和 orders 中添加样例数据，SQL 代码如下所示：USE eshopINSERT INTO members VALUES('Jinjin', '津津有味', '女', '1982-04-14', '北京市', 8200.0, 'jinjin')INSERT INTO members VALUES('Lfz', '刘法治', '男', '1976-08-26', '天津市', 4500.0, 'lfz0826')INSERT INTO members VALUES('liuzc518', '刘志成', '男', '1972-05-18', '湖南株洲', 3500.0, 'liuzc518')INSERT INTO members VALUES('Wangym', '王咏梅', '女', '1974-08-06', '湖南长沙', 4000.0, 'wangym0806')INSERT INTO members VALUES('Zhangzl', ' 张自梁', '男', '1975-04-20', '湖南株洲', 4300.0, 'zhangzl')INSERT INTO members VALUES('zhao888', ' 赵爱云', '男', '1972-02-12', '湖南株洲', 5500.0, 'zhao888')INSERT INTO products VALUES('0130810324', '清华同方电脑', '2005-12-11', 7, 8000.0, '优惠多多 ')INSERT INTO products VALUES('0140810330', '洗衣粉', '2005-05-31', 1000, 8.6, '特价销售')INSERT INTO products VALUES('0140810332', '红彤彤腊肉', '2005-05-20', 43, 15.0, '是一种卫生食品')INSERT INTO products VALUES('0140810333', '力士牌香皂', '2005-05-06', 22, 6.0, '是一种清洁畜品')INSERT INTO products VALUES('024*******', '电动自行车', '2005-05-31', 10, 1586.0, '价廉物美')INSERT INTO products VALUES('024*******', '自行车', '2005-05-31', 10, 586.0, '价廉物美')INSERT INTO products VALUES('0910810001', '爱国者 MP3', '2005-05-31', 100, 450.0, '价廉物美')INSERT INTO products VALUES('0910810002', '商务通', '2005-05-20', 10, 850.0, '价廉物美')INSERT INTO products VALUES('0910810003', '名人好记星', '2005-05-31', 100, 550.0, '价廉物美')INSERT INTO products VALUES('0910810004', '奥美嘉 U 盘', '2005-05-31', 100, 350.0, '价廉物美')INSERT INTO orders VALUES('jinjin', '0910810004', 2, '2005-06-06', '1', '0', '0') INSERT INTO orders VALUES('jinjin', '0910810004', 1, '2005-08-09', '1', '1', '1') INSERT INTO orders VALUES('lfz', '0910810001', 1, '2005-08-09', '0', '0', '0') INSERT INTO orders VALUES('lfz', '0910810004', 2, '2005-06-06', '1', '1', '1') INSERT INTO orders VALUES('lfz', '0910810004', 2, '2005-08-09', '1', '1', '1') INSERT INTO orders VALUES('liuzc518', '0140810324', 1, '2005-10-09', '0', '0', '0') INSERT INTO orders VALUES('liuzc518', '0910810001', 1, '2005-10-09', '1', '1', '0') INSERT INTO orders VALUES('liuzc518', '0910810004', 2, '2005-10-09', '1', '1', '0') INSERT INTO orders VALUES('wangym', '0910810001', 1, '2005-08-09', '1', '0', '0') INSERT INTO orders VALUES('zhao888', '024*******', 2, '2005-06-06', '1', '1', '0')但是当输入以下插入数据的 SQL 代码：INSERT INTO products VALUES('0910810004', '奥美嘉 U 盘', '2005-05-31', 100, 10350.0, '价廉物美')执行此段 SQL 插入数据代码后，将在结果框中显示以下消息，表示触发了数据表 products 中的触发器 t_price：服务器: 消息 50000 ，级别 16，状态 10，过程 t_price ，行 15价格不合法7．查询地址为“湖南株洲”的会员详细信息，写出SQL 代码：USE eshopselect *from memberswhere m_address LIKE '湖南株洲'8.查询订购了商品号“0910810004 ”的会员的姓名、商品名称、订购日期和订购数量，写出SQL代码（多表查询操作）：USE eshopselect * from orders,products,memberswhere orders.p_no='0910810004'9.查询商品的订购总额（总量*单价）排列在前 3 的商品号和订购总额，写出SQL 代码（多表查询操作）：USE eshopselect top 3 orders.p_no,o_quantity from orders,products,membersorder by o_quantity desc10.创建根据指定日期和商品号，显示商品订购总数量和订购总额（总量*单价）的存储过程p_date_productinfo，写出SQL 代码（此题涉及的查询代码可部分参考前一题）：use eshopgocreate procedure p_date_productinfo @account varchar(20)asselect * from orderswhere m_account =@account11. 执行存储过程 p_date_productinfo 以查看商品号为“0910810004 ”在“2005-08-09 ”的订购情况，写出SQL 代码：USE eshopExec pr_buy ’liuzc518’四、报告要求：将以上要求填写的SQL语句写在实验报告中。