Access-实验要求

实验一Access数据库表的建立和维护1建立数据库 创建一个Access数据库，文件名为“学生.accdb”，在其中建立表“students”。 根据表7-1所示的内容，在“字段名称”栏中输入字段名，并为其设置数据类型、字段大小。 建好数据库结构后，定义“学号”为主键，定义方法为，单击表中“学号”一行，然后单击工具栏中“主键”图标按钮。创建好的表 students的结构如图7-3所示。表7-1students表的结构字段名称数据类型字段大小学号文本9个字符姓名文本5个字符性别文本1个字符党员是/否1位班级文本10个字符出生年月日期/时间8个字节奖学金货币8个字节助学金货币8个字节照片OLE对象最多1G字节图7-1 创建的students表结构2数据库的管理与维护（1）向表中输入数据。双击刚创建的表students，进入数据表视图，依次输入数据，如图7-4所示。若要添加照片，可选中照片一栏，然后单击右键，从快捷菜单中选择“插入对象” “由文件创建”“浏览”选项，选择照片即可。图7-2 数据表视图（2）表结构的修改。选定表，单击“视图”工具下的设计视图，即可进入设计视图来对表的结构进行修改。对字段名称、字段类型和字段属性，可以进行插入、删除、移动等操作，还可以重新设置主键。（3）数据的导入、导出。导出：采用“外部数据”选项卡“导出”工具组中的命令，可将表格数据导出，保存成Excel文件、文本文件等。导入：采用“外部数据”选项卡“导入”工具组中的命令，可将Excel、Access、ODBC数据库中的数据导入到表格中。例如，将students表中的数据导出，以文本文件的形式保存在D盘中。 打开表students，单击“外部数据”选项卡下“导出”工具组中的“文本文件”命令，打开“导出-文本文件”对话框。 选择保存位置为D盘，导出文件名为students.txt，根据需要选择“指定导出选项”，如选择“导出数据时包含格式和布局”。 单击“确定”按钮后，在弹出的“对students的编码方式”对话框中，选择用于保存该文件的编码方式为“Windows（默认）”，单击“确定”按钮即可完成数据的导出。导出结果如图7-5所示。图7-3 数据导出结果（4）表的复制、删除。将表students复制为students1，然后将其删除。 选定表students，选择右键快捷菜单中的“复制”和“粘贴”命令。 在弹出的“粘贴表方式”对话框中，输入表名students1，选择“粘贴选项”为“结构和数据”，如图7-6所示。单击“确定”按钮后，即可在数据库中产生一个与表students完全相同的表。 选中表students1，右击，从弹出的快捷菜单中选择“删除”命令，即可从数据库中删除此表。图7- 4 “粘贴表方式”对话框3SQL的数据更新命令（1）INSERT命令格式：INSERT INTO 表名 VALUES(常量1，常量2，常量n)例如，向students表中插入记录：201253007, 朱元东, 男, TRUE, 航海12级03班, #2/22/1993#,800,200 单击“创建”选项卡下的“查询设计”选项，在打开的对话框中不作任何的修改，直接关闭对话框，目的是建立一个空查询。 单击“查询工具”“设计”选项卡中的选项，切换到SQL视图。 在“查询1”视图中，输入SQL命令，如图7-7所示。 单击“运行”命令，则执行相应的插入操作。 打开students表，可以看到在该表的最后添加了一条新的记录。图7-7 输入INSERT命令（2）UPDATE命令UPDATE命令用于修改数据。格式：UPDATE 表名 SET 字段1表达式1，字段n表达式n WHERE 条件例如：将表studenst中的“王刚”改为“王浩”。UPDATE students SET 姓名 = 王浩 WHERE 姓名=王刚例如：将表students中奖学金低于1000元的学生的助学金加100元。UPDATE students SET 助学金 = 助学金+100WHERE 奖学金1000（3）DELETE命令格式：DELETE FROM 表名 例如：将表students中学号为201253007的记录删除。DELETE FROM students WHERE 学号=201253007；做完该操作后再执行一次（1）创建的查询，并保存数据库。练习1建立数据库 创建一个名为Studyaccdb的数据库，将其存放在D盘“access练习”文件夹中。 在数据库文件Studyaccdb中创建表students，该表结构如表7-2所示，其中学号设为主键。表7-2students的结构字段名称字段类型字段宽度学号Text9个字符姓名Text4个字符性别Text1个字符专业Text20个字符出生年月Date/Time8字节奖学金Currency8字节 将表7-3中的数据输入students表中。表7-3students的数据学号姓名性别专业出生年月奖学金201200001丁宁男计算机1994-6-2￥1000201201002于海男企业管理1994-2-22￥2000201201005马卫东男通信工程1993-10-15￥1500201201012王子男法学1993-8-22￥500201202025王晓娜女音乐1994-7-3￥1000201202021东方明女计算机1993-11-12￥1000201203009刘勇男计算机1993-10-2￥3000201203014刘东东女通信工程1994-9-25￥1500201204006杨阳女企业管理1994-5-22￥500 创建表（未知表结构）。根据表7-4中的数据，先确定表Scores的结构，然后在Study.accdb中创建该表。表7-4Scores的数据学 号课 程成 绩201200001数据库概论87201200001高等数学85201201002管理学基础75201201005模拟电路64201201005大学英语56201201012民法78201201012经济法88201202025乐理75201202021数据库概论79201202021高等数学88201202021大学英语75201203009数据库概论46201203009C+程序设计60201203014数字电路65201203014大学英语90201204006管理学基础88 复制表。将表Students复制为Students1和Students2。2修改表的结构例如，修改表Students1的结构。 修改字段的长度：将姓名字段的宽度由4改为6。 修改字段的名称：将专业字段的名称改为所学专业。 添加新的字段：字段名称为“籍贯”，数据类型为文本型（Text），字段长度为6，并为各个记录输入相应的籍贯信息。 调整字段的位置：将“出生年月”字段移到“专业”字段之前。3使用SQL命令对数据库进行操作（1）使用SQL命令建立、修改和删除表。 创建表：根据表7-5所示的结构，在Study.accdb中创建表Courses。表7-5Courses的结构字段名称数据类型字段宽度课号Text6个字符课程名Text10个字符任课教师姓名Text4个字符 复制表：为表Courses复制一个备份Courses1。 修改字段的长度：将表Courses1课号字段的长度改为8字符。 增加一个新的字段：在表Courses1增加一个字段，字段名为教师所在院系，字段类型为Text 、字段长度为10。 删除表：用SQL命令删除备份Courses1。（2）使用SQL的数据更新命令对表进行操作。 对表Courses用INSERT语句插入一条记录，记录内容如下：10001 高等数学 王宏 对表Courses用INSERT语句插入一条记录，记录内容如下：10002 数据库概论 李伟 在表Students1中，用DELETE语句删除女生中奖学金低于1000元的记录。 在表Students1中，用UPDATE语句将姓名“刘勇”修改为“刘睿”。实验二SQL语句1基本句式格式：SELECT 字段名表 FROM表名 查询所有学生的基本情况。对实验一中所建的数据库文件“学生.accdb”进行如下操作。SELECT 学号,姓名,性别,党员,班级号,出生年月,奖学金,助学金,照片 FROM students;用符号*表示所有的字段，则上述语句可改为：SELECT * FROM students ;查询结果如图7-8所示。图7-8 基本情况查询结果字段名表可以是SQL库函数的表达式，如SQL常用函数如表7-6所示。表7-6SQL常用函数函数名描 述AVG 计算查询的指定字段中所包含的一组值的算术平均值COUNT计算查询所返回的记录数SUM返回查询的指定字段中包含的一组值的总和MAX与MIN返回查询的指定字段中包含的一组值的最大值或最小值 查询学生人数、最低奖学金、最高奖学金、平均奖学金和平均助学金。SELECT COUNT(*) AS 人数,MIN(奖学金) AS 最低奖学金，MAX(奖学金) AS 最高奖学金,AVG(奖学金) AS 平均奖学金, AVG(助学金) AS 平均助学金FROM students;查询结果如图7-9所示。图7-9 使用SELECT语句及函数的查询结果 查询所有的班级号。SELECT DISTINCT 班级 FROM students;查询结果如图7-10所示，如果删除DISTINCT，则查询结果如图7-11所示。 图7-10 有DISTINCT选择结果 图7-11 无DISTINCT选择结果 查询所有人的学号、姓名、班级、奖学金+助学金的总金额。SELECT 学号,姓名,班级号,奖学金+助学金 AS 总金额 FROM students;查询结果如图7-12所示。图7-12 带运算符的选择结果2WHERE子句 查询航海2012级01班学生的学号、姓名、性别、党员、奖学金和班级号。SELECT 学号,姓名,性别,党员,奖学金,班级FROM students WHERE 班级=航海12级01班;查询结果如图7-13所示。图7-13 航海12级01班查询结果 查询1994年以后出生的女生学号、姓名、性别、出生年月。SELECT 学号,姓名,性别,出生年月FROM students WHERE 出生年月#1/1/1994# AND 性别=女；查询结果如图7-14所示。图7-14 1994年后出生的女生查询结果3ORDER BY子句ORDER BY子句用于指定查询结果的排列顺序。ASC表示升序，DESC表示降序。它可以指定多个列作为排序关键字。例如，查询所有学生的学号、姓名、班级号，并按奖学金从小到大，班级从大到小排序。SELECT 学号,姓名,班级,奖学金 FROM studentsORDER BY 奖学金 ASC,班级 DESC;查询结果如图7-15所示。图7-15 排序查询结果4GROUP BY子句和HAVING子句 查询男女同学人数、最低奖学金、最高奖学金、平均奖学金和平均助学金。SELECT COUNT(学号) AS 人数, 性别, MIN(奖学金) AS 最低奖学金, MAX(奖学金) AS 最高奖学金, AVG(奖学金) AS 平均奖学金, AVG(助学金) AS 平均助学金 FROM students GROUP BY 性别;查询结果如图7-16所示。图7-16 分组查询结果图7-17 HAVING子句过滤后的查询结果 查询奖学金大于等于800元的学生的姓名。SELECT 姓名 FROM studentsGROUP BY 姓名 HAVING MIN(奖学金)=800;查询结果如图7-17所示。5连接查询为说明连接查询，在数据库“学生.accdb”中，创建另一个表class，其数据结构如表7-7所示，数据如图7-18所示。表7-7class表的结构字段名称字段类型字段宽度班级文本10专业文本20学制文本2班主任文本4班长文本6图7-18 class表例如，查询所有学生的学号、姓名、专业、班主任和学制。SELECT students.学号,students.姓名,class.专业,class.学制,class.班主任FROM students,class WHERE students.班级=class.班级;查询结果如图7-19所示。图7-19 连接查询结果6嵌套查询（1）选择所有和“张博”一个班的学生的学号、姓名、性别和班级号。SELECT students.学号,students.姓名,students.性别,students.班级FROM students WHERE 班级IN(SELECT students.班级FROM students WHERE students.姓名=张博)；查询结果如图7-20所示。图7-20 嵌套查询1结果（2）若选择和张博不在一个班的学生，则用如下