实验5 其他查询.doc

1. 创建数据表1) 按下表的定义创建“俱乐部”表：表5-1 “俱乐部”表字段定义字段名称数据类型字段大小主键俱乐部编号文本4是俱乐部名称文本20成立日期日期/时间俱乐部主席文本5俱乐部地址文本20状态文本4人数IntegerCREATE TABLE 俱乐部(俱乐部编号 CHAR(4) PRIMARY KEY,俱乐部名称 CHAR(20) NOT NULL,成立日期 DATETIME,俱乐部主席 CHAR(5),俱乐部地址 CHAR(20),状态 CHAR(4)人数 INTEGER)注意：将俱乐部编号定义为主键。2) 按下表的定义创建“俱乐部成员”表：表5-2 “俱乐部成员”表字段定义字段名称数据类型字段大小主键俱乐部编号文本4是学号文本10是加入日期日期/时间CREATE TABLE 俱乐部成员(俱乐部编号 CHAR(4) NOT NULL,学号 CHAR(10) NOT NULL,加入日期 DATETIME,CONSTRAINT ABCD PRIMARY KEY (俱乐部编号,学号)注意：最后一项定义了名为ABCD的由俱乐部编号和学号组成的主键。2. 修改表结构1) 在俱乐部成员表中增加大小为5个字符的姓名字段：ALTER TABLE 俱乐部成员 ADD COLUMN 姓名 CHAR (5) NOT NULL2) 从俱乐部表中删除俱乐部地址字段：ALTER TABLE 俱乐部 DROP COLUMN人数3. 删除数据表请在确实不再使用俱乐部和俱乐部成员表时执行以下操作，或者给俱乐部和俱乐部成员表做了备份后执行以下操作。1) 删除俱乐部表：DROP TABLE 俱乐部2) 删除俱乐部成员表DROP TABLE 俱乐部成员1.1.1 数据更新1. 插入查询1) 增加一条编号为“0811”、名称为“足球终结者”的俱乐部记录：INSERT INTO 俱乐部(俱乐部编号,俱乐部名称,成立日期)VALUES (0811,足球终结者,#2008-3-16#)2) 增加一条编号为“0906”、名称为“海龙八极拳会”、俱乐部主席为“蔡小刀”的俱乐部记录：INSERT INTO 俱乐部(俱乐部编号,俱乐部名称, 俱乐部主席)VALUES (0906,海龙八极拳会, 蔡小刀)3) 增加一条今天参加了编号为“0906”的俱乐部、学号为“2005218141”、姓名为“东方鸿”的俱乐部成员记录：INSERT INTO 俱乐部成员(俱乐部编号, 学号,姓名,加入日期)VALUES (0906,2005218141,东方鸿,DATE()4) 学生名单中“031国贸3”班全部男生今天都加入了足球终结者俱乐部，请向俱乐部成员表中插入这些记录：INSERT INTO 俱乐部成员(俱乐部编号,学号,姓名,加入日期)SELECT 0811,学号,姓名,DATE() FROM 学生名单 WHERE 班级=031国贸3 AND 性别=男2. 更新查询1) 将“海龙八极拳会”更名为“恨地无环八极拳会”，并将其成立日期设置为#2009-1-2#：UPDATE 俱乐部SET 俱乐部名称=恨地无环八极拳会,成立日期=#2009-1-2# WHERE 俱乐部名称=海龙八极拳会2) 原“足球终结者”俱乐部中姓陈、姓李的成员全部在2009年3月6日转入“恨地无环八极拳会”，修改这些会员的记录：UPDATE 俱乐部成员SET 俱乐部编号=0906,加入日期=#2009-3-6# WHERE 俱乐部编号=0811 AND LEFT(姓名,1) IN (陈,李)3) 如果某俱乐部没有会员将该俱乐部的状态设置为“停止”UPDATE 俱乐部 SET 状态=停止WHERE NOT EXISTS (SELECT * FROM 俱乐部成员 WHERE 俱乐部编号=俱乐部.俱乐部编号)3. 删除查询1) 从俱乐部成员中删除许强的记录：DELETE FROM俱乐部成员WHERE 姓名=许强2) 删除足球终结者俱乐部全部成员：DELETE FROM 俱乐部成员 WHERE 俱乐部编号 IN (SELECT 俱乐部编号 FROM 俱乐部 WHERE 俱乐部名称=足球终结者)3) 删除没有会员的俱乐部：DELETE FROM 俱乐部 WHERE NOT EXISTS (SELECT * FROM 俱乐部成员 WHERE 俱乐部编号=俱乐部.俱乐部编号)1.1.2 生成表查询1. 从2003学生名单中选择出班级数据，并保存为2003班级表：SELECT DISTINCT 班级 INTO 2003班级FROM 2003学生名单;2. 从2003学生名单中选择出各班入学成绩最高的学生，并保存为2003各班最高成绩表：SELECT 班级