数据库实验98235

合集下载

数据库实验报告实验步骤

一、实验目的1. 熟悉数据库的基本概念和结构。

2. 掌握数据库的创建、修改、删除等操作。

3. 学会使用SQL语句进行数据的查询、插入、更新和删除。

4. 掌握数据库的备份和恢复。

二、实验环境1. 操作系统：Windows 102. 数据库管理系统：MySQL 5.73. 开发工具：MySQL Workbench三、实验步骤1. 创建数据库（1）打开MySQL Workbench，选择“新建连接”。

（2）输入主机名、用户名、密码等信息，点击“连接”。

（3）在左侧菜单栏选择“数据库”，点击“创建数据库”。

（4）在弹出的对话框中输入数据库名称，点击“创建”。

2. 创建表（1）在左侧菜单栏选择“数据库”，然后选择刚刚创建的数据库。

（2）点击“创建表”。

（3）在弹出的对话框中输入表名，设置字段类型、长度、约束等属性。

（4）点击“保存”完成表的创建。

3. 插入数据（1）选择创建的表，点击“插入数据”。

（2）在弹出的对话框中输入数据，点击“保存”。

4. 查询数据（1）选择创建的表，点击“查询”。

（2）在弹出的对话框中输入SQL语句，例如：SELECT FROM 表名 WHERE 条件。

（3）点击“执行”查看查询结果。

5. 更新数据（1）选择创建的表，点击“更新数据”。

（2）在弹出的对话框中输入SQL语句，例如：UPDATE 表名 SET 字段=值 WHERE条件。

（3）点击“执行”完成数据的更新。

6. 删除数据（1）选择创建的表，点击“删除数据”。

（2）在弹出的对话框中输入SQL语句，例如：DELETE FROM 表名 WHERE 条件。

（3）点击“执行”完成数据的删除。

7. 备份和恢复数据库（1）在左侧菜单栏选择“数据库”，然后选择要备份的数据库。

（2）点击“备份”。

（3）在弹出的对话框中设置备份文件路径和名称，点击“备份”完成备份操作。

（4）在左侧菜单栏选择“数据库”，然后选择“还原”。

（5）在弹出的对话框中设置还原路径和名称，点击“还原”完成数据库的恢复。

2018年数据库实验报告(安工大周兵老师)-精选word文档 (27页)

本文部分内容来自网络整理，本司不为其真实性负责，如有异议或侵权请及时联系，本司将立即删除！== 本文为word格式，下载后可方便编辑和修改！ ==数据库实验报告(安工大周兵老师)《数据库系统概论》实验报告书专业班级学号姓名指导教师安徽工业大学计算机学院软件工程***班********* *** 周兵实验一：数据定义语言[ 实验日期 ] 年月日 [ 实验目的 ]熟悉Oracle上机环境及Oracle客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引。

[ 实验内容 ]Oracle上机环境以及Oracle客户端的配置参见附录。

1． SQL数据定义语句：例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。

一、建表1：创建表Student CREATE TABLE Student ( SNO CHAR(5) ,SNAME CHAR(10) NOT NULL, SDEPT CHAR(2) NOT NULL, SCLASS CHAR(2) NOT NULL, SSEX CHAR(2), SAGE NUMBER(2),CONSTRAINT SNO_PK PRIMARY KEY(SNO))； 2：创建表Course CREATE TABLE Course (CNO CHAR(3),CNAME CHAR(16) UNIQUE, CTIME NUMBER(3),CONSTRAINT Cno_pk PRIMARY KEY(CNO)); 3：创建表Teach CREATE TABLE Teach (TNAME CHAR(8), TSEX CHAR(2), CNO CHAR(3), TDATE DATE, TDEPT CHAR(2),CONSTRAINT Tname_pk PRIMARY KEY(TNAME,CNO,TDEPT),CONSTRAINT Cno_fk FOREIGN KEY(CNO) REFERENCES Course(CNO)); 4：创建表Score CREATE TABLE Score (SNO CHAR(5), CNO CHAR(3), SCORE NUMBER(5,2),CONSTRAINT SCno_fk FOREIGN KEY(CNO) REFERENCES Course(CNO), CONSTRAINT SSno_fk FOREIGN KEY(SNO) REFERENCES Student(SNO)); 二、插入数据 1：StudentINSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('96001','马小燕','CS','01','女',21);INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('96002','黎明','CS','01','男',18);INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('96003','刘东明','MA','01','男',18);INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('96004','赵志勇','IS','02','男',20);INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('97001','马蓉','MA','02','女',19);INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('97002','李成功','CS','01','男',20);INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('97003','黎明','IS','03','女',19);INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('97004','李丽','CS','02','女',19);INSERT INTO Student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE)VALUES('96005','司马志明','CS','02','男',18); 2：CourseINSERT INTO Course VALUES('001','数学分析',144); INSERT INTO Course VALUES('002','普通物理',144); INSERT INTO Course VALUES('003','微机原理',72); INSERT INTO Course VALUES('004','数据结构',72); INSERT INTO Course VALUES('005','操作系统',64); INSERT INTO Course VALUES('006','数据库原理',64); INSERT INTO Course VALUES('007','DB_Design',48);INSERT INTO Course VALUES('008','程序设计',56); 3：TeachINSERT INTO Teach VALUES('王成刚','男','004','05-9月-1999','CS'); INSERT INTO Teach VALUES('李正科','男','003','05-9月-1999','CS'); INSERT INTO Teach VALUES('严敏','女','001','05-9月-1999','MA'); INSERT INTO Teach VALUES('赵高','男','004','05-9月-1999','IS'); INSERT INTO Teach VALUES('李正科','男','003','23-2月-00','MA'); INSERT INTO Teach VALUES('刘玉兰','女','006','23-2月-00','CS'); INSERT INTO Teach VALUES('王成刚','男','004','23-2月-00','IS'); INSERT INTO Teach VALUES('马悦','女','008','06-9月-00','CS'); INSERT INTO Teach VALUES('王成刚','男','007','05-9月-1999','CS'); 4：ScoreINSERT INTO Score VALUES('96001','001',77.5); INSERT INTO Score VALUES('96001','003',89); INSERT INTO Score VALUES('96001','004',86); INSERT INTO Score VALUES('96001','005',82); INSERT INTO Score VALUES('96002','001',88); INSERT INTO ScoreVALUES('96002','003',92.5); INSERT INTO ScoreVALUES('96002','006',90); INSERT INTO Score VALUES('96005','004',92); INSERT INTO Score VALUES('96005','005',90); INSERT INTO Score VALUES('96005','006',89); INSERT INTO Score VALUES('96005','007',76); INSERT INTO Score VALUES('96003','001',69); INSERT INTO Score VALUES('96001','001',96); INSERT INTO Score VALUES('96001','008',95); INSERT INTO Score VALUES('96004','001',87); INSERT INTO Score VALUES('96003','003',91); INSERT INTO Score VALUES('96002','003',91); INSERT INTO Score(SNO,CNO) VALUES('96002','004'); INSERT INTO Score VALUES('96002','006',92); INSERT INTO Score VALUES('96004','005',90); INSERT INTO Score VALUES('96004','006',85); INSERT INTO Score VALUES('96004','008',75);INSERT INTO Score VALUES('96003','001',59); INSERT INTO Score VALUES('96003','003',58);例1-2： (修改数据库表) 在Student表中增加SEX(C,2) 字段。

大二数据库实验报告

大二数据库实验报告1.引言1.1 概述概述部分的内容应该对整个实验和实验报告进行简要介绍和概括。

可以从以下几个方面进行撰写：概述部分主要从以下几个方面进行撰写：1. 引入数据库实验的背景：可以描述数据库实验是大学计算机科学专业中重要的实践环节之一，通过实验可以加深对数据库管理系统的理解和应用，并提高学生的实践能力和问题解决能力。

2. 对实验目标的概述：可以说明本次实验的主要目的是通过设计和实现一个小规模数据库系统，熟悉数据库的基本操作和编程接口，掌握数据库设计和管理的基本要点。

3. 对实验内容的概述：可以简要介绍实验涉及的主要内容，如数据库的概念和基本原理、关系型数据库的设计和实现、SQL语言的基本操作、数据库表的创建和查询等方面。

同时，可以提及实验所使用的工具和技术，如MySQL数据库管理系统、SQL编程语言等。

4. 对实验报告结构的概述：可以提及实验报告的整体结构，介绍本报告的章节组成和每个章节的主要内容，以帮助读者了解报告的组织架构和阅读顺序。

以上是概述部分的内容撰写建议，可以结合实际情况进行适当调整和扩充。

文章结构部分的内容：本实验报告共包含三个主要部分，即引言、正文和结论。

首先，引言部分（Chapter 1）是整篇实验报告的开篇之章，用于引入该实验的背景和目的，使读者对实验的内容有一个初步的了解。

在引言部分，我们将首先对本次实验进行概述（1.1 概述），介绍该实验的基本背景、研究领域和应用场景。

然后，我们将对本报告的文章结构进行介绍（1.2 文章结构），概括性地列出报告的主要章节和各个章节的内容概要。

最后，我们将明确本次实验的目的（1.3 目的），说明在本次实验中我们需要实现的具体目标和解决的问题。

接下来，正文部分（Chapter 2）是实验报告的核心，包含了本次实验的详细过程、实验设计、实验结果以及相应的分析和讨论。

在正文部分的第一个要点（2.1 第一个要点）中，我们将详细介绍本次实验的背景和相关的理论知识，对数据库的概念、结构和操作进行深入阐述。

《数据库系统原理及应用》实验报告实验一数据库定义 -回复

《数据库系统原理及应用》实验报告实验一数据库定义-回复数据库系统原理及应用实验一数据库定义数据库是用来存储和管理数据的系统。

在现代信息化时代，数据的规模不断增大，因此需要数据库来对数据进行高效的组织、存储和管理。

本实验通过使用SQL语言创建数据库表，学习了数据库的定义过程。

在数据库系统中，最基本的单位是表。

表由行和列组成，每一行代表一个记录，每一列代表一个字段。

首先，我们需要使用SQL语言定义表的结构，包括表的名字、字段名以及每个字段的数据类型等信息。

在这个实验中，我们以中括号内的内容为主题，详细介绍了如何使用SQL 语言定义一个简单的数据库表。

1. 创建数据库及表的设计首先，我们需要创建一个数据库，用于存放表及相关数据。

在SQL中，可以使用CREATE DATABASE语句来创建数据库。

然后，我们设计表的结构，包括表名、字段名、数据类型等。

在这个实验中，我们以[学生信息管理]为主题来设计一个学生信息表。

2. 创建数据库表在SQL中，使用CREATE TABLE语句可以创建表。

语句的基本格式如下：CREATE TABLE table_name (column1 datatype constraint,column2 datatype constraint,...);根据我们的设计，我们可以创建一个名为[Student]的表，包含学生的学号、姓名、性别和年龄等字段。

表的创建语句如下：CREATE TABLE Student (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,gender VARCHAR(10) NOT NULL,age INT);其中，id是主键，name和gender是必填字段，age是可选字段。

3. 插入数据在表创建完成后，我们可以使用INSERT INTO语句将数据插入到表中。

语句的基本格式如下：INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...);根据我们的设计，我们可以插入一些学生的信息。

数据库实验九_查询优化

数据库原理实验报告实验名称查询优化实验环境硬件平台：Intel Core i5-3210M操作系统：Windows 8.1数据库管理系统（DBMS）：MySQL Server 5.5实验内容及步骤1.由于实验需要对不同情况下的查询效果进行评估，因此进行此实验的先行条件是需要有大量的数据条目，前面实验手动添加的数据库表内容由于数目太少而无法适用于本次实验。

我们通过运用上一次实验中JDBC的知识，编写一个程序来自动向表stu5000中添加5000条项目，用来存放5000名学生的信息，而每一名学生的信息都由程序随机生成。

程序代码如下：import java.sql.*;import java.util.Random;public class mainclass {public static void Insert_stu5000(){Connection conn = null;try{//注册驱动程序Class.forName("com.mysql.jdbc.Driver").newInstance();//获取连接，root用户，口令空conn = DriverManager.getConnection("jdbc:mysql:"+ "//localhost:3306/test?useUnicode=true&"+ "characterEncoding=gbk","root","");System.out.println("数据库连接成功！");Statement st=conn.createStatement();int numint=1000;String num = new String();String name = new String();String sex = new String();int age;String dept = new String();for(int i=0;i<5000;i++){num = Integer.toString(numint);name = randomName(6);sex = randomSex();age = randomAge();dept = randomDept();st.executeUpdate("insert into haoran.stu5000"+ " values('"+num+"','"+name+"','"+sex+"',"+age+",'"+dept+"');");System.out.println("添加项目"+numint+"成功！");numint++;}conn.close();}catch(Exception e){System.out.println("Error : " + e.toString());}}public static final String randomName(int length) {Random randGen = null;char[] numbersAndLetters = null;if (length < 1) {return null;}if (randGen == null) {randGen = new Random();numbersAndLetters = ("abcdefghijklmnopqrstuvwxyz" +"ABCDEFGHIJKLMNOPQRSTUVWXYZ").toCharArray();}char [] randBuffer = new char[length];for (int i=0; i<randBuffer.length; i++) {randBuffer[i] = numbersAndLetters[randGen.nextInt(51)];}return new String(randBuffer);}public static final String randomSex() {Random randGen = new Random();if(randGen.nextBoolean())return "男";elsereturn "女";}public static final int randomAge() {Random randGen = new Random();return randGen.nextInt(20)+15;}public static final String randomDept() {Random randGen = new Random();int i = randGen.nextInt(3);if (i==0)return "CS";else if(i==1)return "SE";else if (i==2)return "IS";elsereturn "AI";}public static void main(String args[]){Insert_stu5000();System.out.println("运行结束！");}}2.开始进行查询优化的实验。

数据库原理实验报告

重庆交通大学信息科学与工程学院设计性实验报告专业：计算机科学与技术班级： 2015 级 4 班学号： 631507020418姓名：张再朝课程名称：数据库原理课程实验实验项目性质：设计性实验实验所属课程：《数据库原理B》实验室(中心)：软件与通信实验室指导教师：王家伟实验完成时间： 2017 年 11 月 25 日此页必须保留评分标准及成绩总体实验要求：1 排版要求标题用黑体四号，段前、段后距6磅（或0.3行）；正文用小四号宋体，行距为1.5倍行距。

2 设计报告需要打印，装订，1份/人，理论课考试时交设计报告。

3 各班学习委员收集每位同学做的成果，包含数据库的备份、程序源代码和运行配置说明，并将每位同学的成果拷贝到软件与通信实验室的教师机上。

第一部分实验四目录报告正文 (4)一、实验目的 (4)二、实验题目 (4)三、实验涉及仪器设备和材料清单（或软件） (4)四、实验原理 (5)五、实验内容 (5)六、实验步骤及代码分析 (6)七、实验结果及分析 (8)实验体会 (11)实验二表查询 (13)一实验要求 (13)二实验结果 (14)实验三对象的建立与数据更新操作 (17)一实验要求 (17)二实验结果 (19)报告正文一、实验目的1、掌握ODBC的概念并学习使用ODBC访问数据库；2、学习Visual C++下如何通过ODBC访问数据库；3、学习用Visual C++制作简单数据库应用程序的方法。

二、实验题目某大学需要使用计算机管理学生信息、成绩信息等，要求如下：1、学生信息管理，包含学生的基本信息，如学号、姓名、性别、出生日期等；还要包含学生的简历、社会关系等；2、课程管理，包含整个学校开设的所有课程；3、教学计划管理，包含设定各系各专业在哪学期开设什么样的课程，学分是多少；4、学生选课管理，包含学生在某学期选择什么样的课程；5、学生成绩管理，包含学生各课程的成绩、是否补考、补考成绩；6、学生奖惩管理，管理学生在校期间所获得的奖励和惩罚记录。

数据库实验报告授权(3篇)

第1篇报告编号：_______实验日期：_______实验者：_______指导教师：_______一、实验目的本次数据库实验旨在通过实际操作，使学生掌握数据库的基本概念、设计方法、实现过程以及数据库管理系统的使用。

通过本次实验，学生应能够：1. 理解数据库的基本概念和特点；2. 掌握数据库设计的基本方法和步骤；3. 熟练使用数据库管理系统进行数据库的创建、查询、更新和删除操作；4. 了解数据库安全性和完整性控制的基本知识；5. 培养数据库应用开发的基本能力。

二、实验内容1. 数据库基本概念的学习与理解；2. 数据库设计方法的掌握；3. 数据库管理系统的安装与配置；4. 数据库的创建、表结构的定义；5. 数据的插入、查询、更新和删除操作；6. 数据库视图的创建与使用；7. 数据库索引的创建与维护；8. 数据库的备份与恢复；9. 数据库安全性与完整性控制；10. 数据库应用开发的基本实践。

三、实验步骤1. 熟悉数据库管理系统，如MySQL、Oracle、SQL Server等；2. 学习数据库基本概念，如数据库、表、记录、字段等；3. 根据实验要求，设计数据库结构，包括表结构、字段类型、约束等；4. 使用数据库管理系统创建数据库和表，并定义表结构；5. 插入数据，进行数据的查询、更新和删除操作；6. 创建数据库视图，实现数据的筛选和展示；7. 创建索引，提高数据查询效率；8. 学习数据库备份与恢复的基本操作；9. 学习数据库安全性与完整性控制的方法；10. 完成数据库应用开发的基本实践。

四、实验报告授权本人保证以下内容为本人真实实验报告，未经他人帮助，独立完成实验任务。

1. 实验报告中所涉及的数据、图表、代码等均为本人实际操作所得，无抄袭、剽窃他人成果；2. 实验报告中的内容真实、准确，未虚构实验过程和结果；3. 实验报告中所使用的数据库管理系统、工具和资源均为合法获取，未侵犯他人知识产权；4. 实验报告中的内容不涉及国家机密、商业秘密和个人隐私。

数据库实验报告

数据库实验报告一、实验目的本次数据库实验的主要目的是通过实际操作和实践，深入理解数据库的基本概念、原理和技术，掌握数据库的设计、创建、管理和操作方法，提高对数据库的应用能力和解决实际问题的能力。

二、实验环境本次实验使用的数据库管理系统为 MySQL 80，操作系统为Windows 10。

实验工具包括 MySQL Workbench 80 和命令行终端。

三、实验内容（一）数据库设计1、需求分析根据给定的业务场景，对数据库的需求进行了详细的分析。

例如，对于一个学生管理系统，需要存储学生的基本信息（学号、姓名、性别、出生日期等）、课程信息（课程编号、课程名称、学分等）以及学生的选课信息（学号、课程编号、成绩等）。

2、概念模型设计使用 ER 图（实体关系图）对系统中的实体和关系进行了建模。

明确了各个实体的属性和实体之间的联系，为后续的逻辑设计奠定了基础。

3、逻辑模型设计将 ER 图转换为关系模型，确定了各个表的结构，包括表名、字段名、数据类型、约束条件等。

4、物理模型设计考虑了数据库的存储结构、索引、分区等物理存储方面的设计，以提高数据库的性能和存储空间的利用率。

（二）数据库创建1、使用 MySQL Workbench 创建数据库在 MySQL Workbench 中，通过图形界面操作，创建了指定名称的数据库，并设置了相应的字符集和校对规则。

2、使用 SQL 语句创建表使用 CREATE TABLE 语句，根据逻辑模型设计的结果，创建了各个数据表，并定义了主键、外键、唯一约束、非空约束等。

（三）数据操作1、数据插入使用 INSERT INTO 语句向表中插入了大量的测试数据，以验证数据库的功能和性能。

2、数据查询使用 SELECT 语句进行了各种复杂的查询操作，包括单表查询、多表连接查询、子查询、聚合函数的使用等。

3、数据更新使用 UPDATE 语句对表中的数据进行了修改操作，确保数据的准确性和完整性。

数据库课程设计实验报告

SJTU-CS数据库课程设计实验报告姓名王亮谢明敏学号5040309458班级F0403016 F0403014 完成日期2007/7/5C O N T E N T1. Introduction (1)2. WaxBase Design (4)2.1 The Thought of Design (4)2.2 Project Part Introduce (5)2.2.1 The Paged File Component (5)2.2.1.1 Buffer_Data (6)2.2.1.2 PF_Manager (7)2.2.1.3 PF_FileHandle (8)2.2.1.4 PF_PageHandle (11)2.2.2 The Record Management Component (13)2.2.2.1 RM_Manager (13)2.2.2.2 RM_FileHandle (14)2.2.2.3 RM_FileScan (15)2.2.2.4 RM_Record (17)2.2.2.5 RM_RID (17)2.2.3 The Indexing Component (19)2.2.3.1 IX_Manager Class (19)2.2.3.2 IX_IndexHandle Class (20)2.2.3.3 IX_IndexScan Class (21)2.2.4 The System Management Component (23)2.2.4.1 SM_Manager Class (23)2.2.5 The Query Language Component (26)2.2.5.1 QL_Manager Class (26)2.3 The Algorithm Used In Program (29)2.3.1 LRU algorithm (29)2.3.2 Hashing Table (29)2.3.3 B+ Tree (29)3. Usage of WaxBase (32)3.1 Create a database (32)3.2 Destroy a database (32)3.3 Open a database (32)3.4 The commands we support in database: (32)3.4.1 DDL Commands (32)3.4.1.1 Create Table (32)3.4.1.2 Drop Table (32)3.4.1.3 Create Index (33)3.4.1.4 Drop Index (33)3.4.2 The RQL Select command (33)3.4.2.1 The syntax of the one data retrieval command in RQL (33)3.4.2.2 An alternative form of the Select command (33)3.4.3 The RQL Insert Command (33)3.4.4 The RQL Delete Command (34)4. Conclusions (35)5. Reference (36)1.IntroductionA database management system (DBMS) is computer software designed for the purpose of managing databases. Typical examples of DBMSs include Oracle, DB2, Microsoft Access, Microsoft SQL Server, Postgres, MySQL and FileMaker. DBMSs are typically used by Database administrators in the creation of Database systems.A DBMS is a complex set of software programs that controls the organization, storage and retrieval of data in a database. A DBMS includes:1. A modeling language to define the schema(relational model) of each database hosted in theDBMS, according to the DBMS data model.●The three most common organizations are the hierarchical, network and relational models. Adatabase management system may provide one, two or all three methods. Inverted lists andother methods are also used. The most suitable structure depends on the application and onthe transaction rate and the number of inquiries that will be made. The dominant model inuse today is the ad hoc one embedded in SQL, a corruption of the relational model byviolating several of its fundamental principles. Many DBMSs also support the OpenDatabase Connectivity API that supports a standard way for programmers to access theDBMS.2.Data structures (fields, records and files) optimized to deal with very large amounts of data storedon a permanent data storage device (which implies very slow access compared to volatile main memory).3. A database query language and report writer to allow users to interactively interrogate thedatabase, analyze its data and update it according to the users privileges on data.●It also controls the security of the database.●Data security prevents unauthorized users from viewing or updating the database. Usingpasswords, users are allowed access to the entire database or subsets of it called subschemas.For example, an employee database can contain all the data about an individual employee,but one group of users may be authorized to view only payroll data, while others are allowedaccess to only work history and medical data.●If the DBMS provides a way to interactively enter and update the database, as well asinterrogate it, this capability allows for managing personal databases. However, it may notleave an audit trail of actions or provide the kinds of controls necessary in a multi-userorganization. These controls are only available when a set of application programs arecustomized for each data entry and updating function.4. A transaction mechanism, that ideally would guarantee the ACID properties, in order to ensuredata integrity, despite concurrent user accesses (concurrency control), and faults (fault tolerance).●It also maintains the integrity of the data in the database.第1页The DBMS can maintain the integrity of the database by not allowing more than one user to update the same record at the same time. The DBMS can help prevent duplicate records viaunique index constraints; for example, no two customers with the same customer numbers(key fields) can be entered into the database. See ACID properties for more information(Redundancy avoidance).The components of a DBMS:The DBMS accepts requests for data from the application program and instructs the operating system to transfer the appropriate data.When a DBMS is used, information systems can be changed much more easily as the organization's information requirements change. New categories of data can be added to the database without disruption to the existing system.Organizations may use one kind of DBMS for daily transaction processing and then move the detail onto another computer that uses another DBMS better suited for random inquiries and analysis. Overall systems design decisions are performed by data administrators and systems analysts. Detailed database design is performed by database administrators.Database servers are specially designed computers that hold the actual databases and run only the DBMS and related software. Database servers are usually multiprocessor computers, with RAID disk arrays第2页used for stable storage. Connected to one or more servers via a high-speed channel, hardware database accelerators are also used in large volume transaction processing environments.DBMS's are found at the heart of most database applications. Sometimes DBMSs are built around a private multitasking kernel with built-in networking support although nowadays these functions are left to the operating system.Features and Abilities of DBMS: One can characterize a DBMS as an "attribute management system" where attributes are small chunks of information that describe something. For example, "color" is an attribute of a car. The value of the attribute may be a color such as "red", "blue", "silver", etc. Lately databases have been modified to accept large or unstructured (pre-digested or pre-categorized) information as well, such as images and text documents. However, the main focus is still on descriptive attributes.DBMS roll together frequently-needed services or features of attribute management. This allows one to get powerful functionality "out of the box" rather than program each from scratch or add and integrate them incrementally.第3页2.WaxBase Design2.1The Thought of DesignIn general, a Database managerment system will include many components, but because of the short of peoples and time, our WaxBase will include these components:Legend:●PF: Paged File●RM: Record Management●IX: Indexing●SM: System Management (DDL, utilities)●QL: Query Language●EX: Extension第4页2.2 Project Part Introduce2.2.1 The Paged File ComponentThe paged file component is the "bottom" component of the WaxBase system. This component provides facilities for higher-level client components to perform file I/O in terms of pages. In the PF component, methods are provided to create, destroy, open, and close paged files, to scan through the pages of a given file, to read a specific page of a given file, to add and delete pages of a given file, and to obtain and release pages for scratch use.The name of each class in paged file component begins with the prefix PF. Each method in the PF component except constructors and destructors returns an integer code; the same will be true of all of the methods you will see. A return code of 0 indicates normal completion. A nonzero return code indicates that an exception condition or error has occurred. Positive nonzero return codes indicate non-error exception conditions (such as reaching the end of a file) or errors from which the system can recover or exit gracefully (such as trying to close an unopened file). Negative nonzero return codes indicate errors from which the system cannot recover.Accessing data on a page of a file requires first reading the page into a buffer pool in main memory, then manipulating (reading or writing) its data there. While a page is in memory and its data is available for manipulation, the page is said to be "pinned" in the buffer pool. A pinned page remains in the buffer pool until it is explicitly "unpinned." A client unpins a page when it is done manipulating the data on that page. Unpinning a page does not necessarily cause the page to be removed from the buffer -- an unpinned page is kept in memory as long as its space in the buffer pool is not needed.If the PF component needs to read a new page into memory and there are no free spaces left in the buffer pool, then the PF component will choose an unpinned page to remove from the buffer pool and will reuse its space. The PF component uses a Least-Recently-Used (LRU) page replacement policy. When a page is removed from the buffer pool, it is copied back to the file on disk if and only if the page is marked as "dirty." Dirty pages are not written to disk automatically until they are removed from the buffer. However, a PF client can always send an explicit request to force (i.e., write to disk) the contents of a particular page, or to force all dirty pages of a file, without removing those pages from the buffer.It is important not to leave pages pinned in memory unnecessarily. The PF component clients that you will implement can be designed so that each operation assumes none of the pages it needs are in the buffer pool: A client fetches the pages it needs, performs the appropriate actions on them, and then unpins them, even if it thinks a certain page may be needed again in the near future. (If the page is used again soon then it will probably still be in the buffer pool anyway.) The PF component does allow the same page to be pinned more than once, without unpinning it in between. In this case, the page won't actually be unpinned until the number of unpin operations matches the number of pin operations. It is very important that each time you第5页fetch and pin a page, you don't forget to unpin it when you're done. If you fail to unpin pages, the buffer pool will slowly fill up until you can no longer fetch any pages at all (at which point the PF component will return a negative code).The description of classes in the PF component is in the below:2.2.1.1 Buffer_DataThe Buffer_Data class handles the buffer pool action. We use an array of char to store the buffer. The whole number of block is 40. Meanwhile, we use a hash table to save which page saved in our buffer now. In buffer, we use a chain to implement the LRU algothrim. If the block is pined in the buffer, it will not in the chain. Every time if we want to use a block in buffer to save the page, we will use the LRU place to save the page.Int getderty (int num)This method is to get if the num block in buffer is derty.Int getfname (int num)This method is to get the filename of the num block in buffer.Int getdata (int num)This method is to get the data of the num block in buffer.Int delMRU ()This method is to delete the data of the most recently used block in buffer.第6页Int addLRU (int num)This method is to add the num block in buffer to the LRU place.Int addLRU (int num)This method is to add the num block in buffer to the MRU place.Int DelChain (int num)This method is to delete the num block in buffer from the chain.Int WriteBack (int num)This method is to write back the num block in buffer from the buffer to disk, but not delete from the buffer.Int WriteBackWithDel (int num)This method is to write back the num block in buffer from the buffer to disk, and delete from the buffer.Int addMap (string str, int num)This method is to add the map of str to num in the hash table.Int delMap (string str, int num)This method is to delete the map from the hash table.Int delMap (int num)This method is to get the map from the hash table.2.2.1.2PF_ManagerThe PF_Manager class handles the creation, deletion, opening, and closing of paged files, along with the allocation and disposal of scratch pages. Your program should create exactly one instance of this class, and all requests for PF component file management should be directed to that instance. Below, the public methods of the class declaration are shown first, followed by descriptions of the methods. The first two methods in the class declaration are the constructor and destructor methods for the class; they are not explained further. Each method except the constructor and destructor methods returns a value of type RC (for "return code" -- actually an integer). A return code of 0 indicates normal completion. A nonzero return code indicates that an exception condition or error has occurred.RC CreateFile (const char *fileName)第7页This method creates a paged file called fileName. The file should not already exist.RC DestroyFile (const char *fileName)This method destroys the paged file whose name is fileName. The file should exist.RC OpenFile (const char *fileName, PF_FileHandle &fileHandle)This method opens the paged file whose name is fileName. The file must already exist and it must have been created using the CreateFile method. If the method is successful, the fileHandle object whose address is passed as a parameter becomes a "handle" for the open file. The file handle is used to manipulate the pages of the file (see the PF_FileHandle class description below).RC CloseFile (PF_FileHandle &fileHandle)This method closes the open file instance referred to by fileHandle. The file must have been opened using the OpenFile method. All of the file's pages are flushed from the buffer pool when the file is closed. It is a (positive) error to attempt to close a file when any of its pages are still pinned in the buffer pool.RC AllocateBlock (char *&buffer)This method allocates a "scratch" memory page (block) in the buffer pool and sets buffer to point to it. The scratch page is automatically pinned in the buffer pool.RC DisposeBlock (char *buffer)This method disposes of the scratch page in the buffer pool pointed to by buffer, which must have been allocated previously by PF_Manager::AllocateBlock. Similar to pinning and unpinning, you must call PF_Manager::DisposeBlock for each buffer block obtained by calling PF_Manager::AllocateBlock; otherwise you will lose pages in the buffer pool permanently.2.2.1.3 PF_FileHandleThe PF_FileHandle class provides access to the pages of an open file. To access the pages of a file, a client first creates an instance of this class and passes it to the PF_Manager::OpenFile method described above. As before, the public methods of the class declaration are shown first, followed by descriptions of the methods. The first two methods in the class declaration are the constructor and destructor methods and are not explained further.第8页PF_FileHandle (const PF_FileHandle &fileHandle)This method is the copy constructor, called if a new file handle object is created from an existing one. When a new file handle object is created from a file handle object that refers to an open file instance, the file is not opened an additional time. Instead, both file handle objects refer to the same open file instance. It is sufficient to call PF_Manager::CloseFile with one of the file handle objects to close the file.PF_FileHandle& operator= (const PF_FileHandle &fileHandle)This method overloads the = operator when it is used to assign one file handle object to another. It is not a good idea to assign one file handle object to another if the file handle object on the left-hand side of the = already refers to an open file. As with the copy constructor, if the file handle object on the right-hand side of the = refers to an open file instance, the file is not opened an additional time. Instead, both file handle objects refer to the same open file instance, and it is sufficient to call PF_Manager::CloseFile with one of the file handle objects to close the file.RC GetFirstPage (PF_PageHandle &pageHandle)For this and the following methods, it is a (positive) error if the PF_FileHandle object for which the method is called does not refer to an open file. This method reads the first page of the file into the buffer pool in memory. If the page fetch is successful, the pageHandle object becomes a handle for the page. The page handle is used to access the page's contents (see the PF_PageHandle class description below). The page read is automatically pinned in the buffer pool and remains pinned until it is explicitly unpinned by第9页calling the UnpinPage method (below). This method returns the positive code PF_EOF if end-of-file is reached (meaning there is no first page).RC GetLastPage (PF_PageHandle &pageHandle)This method reads the last page of the file into the buffer pool in memory. If the page fetch is successful, the pageHandle object becomes a handle for the page. The page read is automatically pinned in the buffer pool and remains pinned until it is explicitly unpinned by calling the UnpinPage method (below). This method returns the positive code PF_EOF if end-of-file is reached (meaning there is no last page).RC GetNextPage (PageNum current, PF_PageHandle &pageHandle)This method reads into memory the next valid page after the page whose number is current. If the page fetch is successful, pageHandle becomes a handle for the page. The page read is pinned in the buffer pool until it is unpinned by calling the UnpinPage method. This method returns PF_EOF if end-of-file is reached (meaning there is no next page). Note that it is not an error if current does not correspond to a valid page (e.g., if the page numbered current has been disposed of).RC GetPreviousPage (PageNum current, PF_PageHandle &pageHandle)This method reads into memory the valid page previous to the page whose number is current. If the page fetch is successful, pageHandle becomes a handle for the page. The page read is pinned in the buffer pool until it is unpinned by calling the UnpinPage method. This method returns PF_EOF if end-of-file is reached (meaning there is no previous page). Note that it is not an error if current does not correspond to a valid page (e.g., if the page numbered current has been disposed of).RC GetThisPage (PageNum pageNum, PF_PageHandle &pageHandle)This method reads into memory the page specified by pageNum. If the page fetch is successful, pageHandle becomes a handle for the page. Parameter pageNum must be a valid page number. As usual, the page read is pinned in the buffer pool until it is explicitly unpinned.RC AllocatePage (PF_PageHandle &pageHandle)This method allocates a new page in the file, reads the new page into memory, and pins the new page in the buffer pool. If successful, pageHandle becomes a handle for the new page.RC DisposePage (PageNum pageNum)This method disposes of the page specified by pageNum. After this method is executed, if you scan over the pages of the file, the page numbered pageNum will no longer appear. It is a (positive) error to attempt to dispose of a page that is pinned in the buffer pool.RC MarkDirty (PageNum pageNum)This method marks the page specified by pageNum as "dirty," indicating that the contents of the page have been or will be modified. The page must be pinned in the buffer pool. A page marked as dirty is written back to disk when the page is removed from the buffer pool. (Pages not marked as dirty are never written back to disk.)RC UnpinPage (PageNum pageNum)第10页This method tells the PF component that the page specified by pageNum is no longer needed in memory.RC ForcePages (PageNum pageNum = ALL_PAGES)This method copies the contents of the page specified by pageNum from the buffer pool to disk if the page is in the buffer pool and is marked as dirty. The page remains in the buffer pool but is no longer marked as dirty. If no specific page number is provided (i.e., pageNum = ALL_PAGES), then all dirty pages of this file that are in the buffer pool are copied to disk and are no longer marked as dirty. Note that page contents are copied to disk whether or not a page is pinned.2.2.1.4 PF_PageHandleThe PF_PageHandle class provides access to the contents of a given page. To access the contents of a page, a client first creates an instance of this class and passes it to one of the PF_FileHandle methods described above.PF_PageHandle (const PF_PageHandle &pageHandle)This method is the copy constructor. When a new page handle object is created from a page handle object that refers to a pinned page in the buffer pool, the page is not pinned a second time.PF_PageHandle& operator= (const PF_PageHandle &pageHandle)This method overloads the = operator when it is used to assign one page handle object to another. As with the copy constructor, if the page handle object on the right-hand side of the = refers to a pinned page, the page is not pinned a second time.RC GetData (char *&pData) constThis method provides access to the actual contents of a page. The PF_PageHandle object for which this method is called must refer to a page that is pinned in the buffer pool. If the method is successful, pData is set to point to the contents of the page in the buffer pool.RC GetPageNum (PageNum &pageNum) const第11页This method sets pageNum to the number of the page referred to by the PF_PageHandle object for which this method is called. The page handle object must refer to a page that is pinned in the buffer pool.第12页2.2.2 The Record Management ComponentThe RM component provides classes and methods for managing files of unordered records. All class names, return codes, constants, etc. in this component should begin with the prefix RM. The RM component is a client to the PF component: RM methods will make calls to the PF methods we have provided.2.2.2.1 RM_ManagerThe RM_Manager class handles the creation, deletion, opening, and closing of files of records in the RM component.RC CreateFile (const char *fileName, int recordSize)This method will call PF_Manager::CreateFile to create a paged file called fileName. The records in this file will all have size recordSize. This method will initialize the file by storing appropriate information in the header page. Although recordSize will usually be much smaller than the size of a page, you should compare recordSize with PF_PAGE_SIZE and return a nonzero code if recordSize is too large for your RM component to handle.RC DestroyFile (const char *fileName)This method should destroy the file whose name is fileName by calling PF_Manager::DestroyFile.RC OpenFile (const char *fileName, RM_FileHandle &fileHandle)This method should open the file called fileName by calling PF_Manager::OpenFile. If the method is successful, the fileHandle object should become a "handle" for the open RM component file. As in the PF component, it should not be an error if a client opens the same RM file more than once, using a different fileHandle object each time. Each call to the OpenFile method should create a new instance of the open file. You may assume if a file has more than one opened instance then each instance of the open file may be read but will not be modified. If a file is modified while opened more than once, you need not guarantee the integrity of the file or the RM component. You may also assume that DestroyFile will never be called on an open file.RC CloseFile (RM_FileHandle &fileHandle)This method should close the open file instance referred to by fileHandle by calling PF_Manager:: CloseFile.第13页2.2.2.2 RM_FileHandleThe RM_FileHandle class is used to manipulate the records in an open RM component file. To manipulate the records in a file, a client first creates an instance of this class and passes it to the RM_Manager::OpenFile method described above. Descriptions of the constructor and destructor methods are not included for this class.RC GetRec (RID &rid, RM_Record &rec)For this and the following methods, it should be a (positive) error if the RM_FileHandle object for which the method is called does not refer to an open file. This method should retrieve the record with identifier rid from the file. It should be a (positive) error if rid does not identify an existing record in the file. If the method succeeds, rec should contain a copy of the specified record along with its record identifier (see the RM_Record class description below).RC InsertRec (char *pData, RID &rid)This method should insert the data pointed to by pData as a new record in the file. If successful, the return parameter &rid should point to the record identifier of the newly inserted record.RC DeleteRec (RID &rid)This method should delete the record with identifier rid from the file. If the page containing the record becomes empty after the deletion, you can choose either to dispose of the page (by calling PF_Manager::DisposePage) or keep the page in the file for use in the future, whichever you feel will be more efficient and/or convenient.RC UpdateRec (RM_Record &rec)This method should update the contents of the record in the file that is associated with rec (see the RM_Record class description below). This method should replace the existing contents of the record in the file with the current contents of rec.第14页RC ForcePages (PageNum pageNum = ALL_PAGES) constThis method should call the corresponding method PF_FileHandle::ForcePages in order to copy the contents of one or all dirty pages of the file from the buffer pool to disk.2.2.2.3 RM_FileScanThe RM_FileScan class provides clients the capability to perform scans over the records of an RM component file, where a scan may be based on a specified condition. As usual, the constructor and destructor methods are not described.RC OpenScan (const RM_FileHandle &fileHandle, AttrType attrType, int attrLength, int attrOffset, CompOp compOp, void *value, ClientHint pinHint = NO_HINT)This method should initialize a scan over the records in the open file referred to by parameter fileHandle. During the scan, only those records whose specified attribute satisfies the specified condition (a comparison with a value) should be retrieved. If value is a null pointer, then there is no condition and all records are retrieved during the scan. If value is not a null pointer, then value points to the value that attributes are to be compared with.Parameters attrType and attrLength indicate the type and length of the attribute being compared: either a 4-byte integer, a 4-byte floating point number, or a character string with a length between 1 and MAXSTRINGLEN bytes. (MAXSTRINGLEN = 255 is defined in waxbase.h.) Type AttrType is defined in第15页waxbase.h as follows: INT for integer, FLOAT for floating point number, and STRING for character string. You will need to cast the value into the appropriate type for the attribute (or, in the case of an integer or float, copy it into a separate variable to avoid alignment problems). If a character string has length n, then the attribute and the value will each be exactly n bytes long. They will not be <= n bytes, i.e., no "padding" is required, and they are not null-terminated. Parameter attrOffset indicates where the attribute is found within the contents of each record. Parameter compOp indicates the way that the record's attribute value should be compared with the value parameter. The different values for compOp are defined in waxbase.h as follows:file scan can suggest a specific page-pinning strategy for the RM component to use during the file scan, to achieve maximum efficiency. Type ClientHint is defined in waxbase.h, and you will need to define constants in addition to NO_HINT if you plan to use it. You are free to implement only one page-pinning strategy and ignore the pinHint parameter, or you may implement more than one strategy based on pinHint values now, or you may implement one strategy now and consider adding new strategies later when you implement clients of the RM component. Please note that using pinHint is optional, and only default value NO_HINT will be passed to OpenScan in the TA's test suite.RC GetNextRec (RM_Record &rec)This method should retrieve a copy of the next record in the file scan that satisfies the scan condition. If this method succeeds, rec should contain a copy of the record along with its record identifier. This method should return RM_EOF (which you should define) if there are no records left satisfying the scan condition. You may assume that RM component clients will not close the corresponding open file instance while a scan is underway.RC CloseScan ()This method should terminate the file scan.第16页。

2023年北邮大三下第次数据库实验报告

试验三数据查询试验一、试验内容（1）数据库关系表查询:（2）简朴旳查询操作, 包括单表旳查询、选择条件、成果排序等旳练习；（3）多表旳连接查询, 包括等值连接、自然连接等；（4）复杂旳查询操作, 包括使用分组函数等库函数旳查询操作；（5）练习带有IN、比较符旳嵌套查询。

二、试验规定(1)用SQL语句完毕以上操作(2)规定学生独立完毕以上内容。

(3)试验完毕后完毕规定旳试验汇报内容。

三、试验环境Windows 7系统Kingbase数据库管理平台四、试验环节及成果分析1.在简朴查询试验中, 在sql语句完毕如下查询操作:（1）查询“数据库原理”课程旳学分；select creditfrom coursewhere cname='数据库原理'（2）查询选修了课程编号为“C01”旳学生旳学号和成绩, 并将成绩按降序输出；select sno,gradefrom scwhere cno='C01'order by grade desc（3）查询学号为“31401”旳学生选修旳课程编号和成绩；select cno,gradefrom scwhere sno='31401'（4）查询选修了课程编号为“C01”且成绩高于85分旳学生旳学号和成绩。

select sno,gradefrom scwhere cno='C01' and grade>852.在多表连接旳查询试验中, 在SQL SERVER提供旳交互式语言环境下用Transact SQL语句完毕如下查询操作:（1）查询选修了课程编号为“C01”且成绩高于85分旳学生旳学号、姓名和成绩；select sc.sno,student.sname,sc.gradefrom student,scwhere o='C01' and sc.grade>85 and student.sno=sc.sno（2）查询所有学生旳学号、姓名、选修旳课程名称和成绩；select student.sno,student.sname,ame,sc.gradefrom student,course,scwhere o=o and sc.sno=student.sno3.在复杂查询试验中, 用SQL语句完毕如下查询操作: （1）查询至少选修了三门课程旳学生旳学号和姓名；select student.sno,student.snamefrom student,scwhere student.sno=sc.snogroup by student.sno,student.snamehaving count(*)>2（2）查询所有学生旳学号和他选修课程旳最高成绩, 规定他旳选修课程中没有成绩为空旳。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

数据库原理实验指导实验前准备：请设计一个企业销售管理据库，其中需要保存的信息如下：员工信息，包括：员工编号、员工姓名、性别、所属部门、职称、到职日、生日、薪水、填表日期；客户信息，包括：客户号，客户名称，客户住址，客户电话、邮政编码；产品信息，包括：产品编号，产品名称；员工和客户可以签订订单，每签订一个订单，就要保存订单信息，包括：订单编号、客户号、业务员编号、订单金额、订货日期、出货日期、发票号码。

此外，每个订单可能涉及到多种产品，每种产品可能被多个订单订购。

因此需要每个订单中每类产品的销售明细，包括每种产品的销售数量、单价、订单日期；要求：（1）给出系统的ER图（可以用word或其它画图工具，如Visio画），要求画出所有的实体，联系，属性以及联系的类型；（2）将ER图转换为关系模型；实验一实验名称：数据定义（2课时）一、实验目的1、理解数据库模式的概念，通过使用SQL SERVER企业管理器或者My Sql建立数据库和基本表。

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

熟悉SQL SERVER 企业管理器的使用，并将得到的表生成脚本，然后保存。

2、理解上述基本表之间的关系，建立关系表。

3、掌握修改表结构的基本方法4、掌握索引和视图的创建方法二、实验环境MS SQL SERVER或者My Sql。

三、实验内容与步骤1、建立一个数据库和五张表的表结构。

（1）/*员工人事表employee */Create datebase sale;create table employee(emp_no char(5) Not null primary key,emp_name char(10) Not null,sex char(1) Not null,dept char(4) null,title char(6) null,date_hired datetime null,birthday datetime null,salary int null,addr char(50) null,Mod_date datetime Default 0,constraint c1 check(性别in('男','女')));（2）/*客户表customer */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 */create TABLE sales(order_no char(5) Not null primary key,cust_id char(5) Not null,sale_id CHAR(5) Not null,tot_amt numeric(9,2) null,order_date datetime null,ship_date datetime null,invoice_no char(10) null);（4）/*销货明细表sales_item*/create TABLE sales_item(order_no char(5) Not null ,prod_id char(5) Not null,primary key(order_no,prod_id),qty INT Not null,unit_price numeric(9,2) Not null,order_date datetime null,CONSTRAINT FK_1 FOREIGN key(order_no) REFERENCES sales(order_no), CONSTRAINT FK_2 FOREIGN key(prod_id) REFERENCES product(prod_id) );（5）/*产品名称表product */create TABLE product(prod_id char(5) Not null primary key,prod_name char(20) Not null);2、建立5张表的关系图3、修改表结构，通过SQL语句修改表约束。

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

//check对于MySQL不起作用，只能在插入数据的基础上修改use sale;ALTER TABLE employee add CHECK(sex LIKE 'E%');UPDATE employeeSET sex='F'WHERE sex='女';UPDATE employeeSET sex='M'WHERE sex='男';②为销售主表sales中的发票编号字段建立UNIQUE约束。

ALTER TABLE sales MODIFY invoice_no CHAR(10) UNIQUE NULL;③所有性别属性，限制取值为f或m4、员工表建立唯一索引：emp_no属性、升序；员工表建立聚集索引：emp_name属性、升序；CREATE UNIQUE INDEX emp_index ON employee(emp_no ASC);//MYSQL不支持此类语法5、创建视图：①视图只含上海客户信息，即客户号、客户姓名、住址。

CREATE VIEW 上海客户信息ASSELECT cust_id,cust_name,addrFROM customerWHERE addr LIKE '上海%';②有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。

CREATE VIEW emp_sales ASSELECT sale_id,emp_name,order_no,tot_amtFROM employee,salesWHERE employee.emp_no=sales.sale_id;四、实验报告实验二实验名称：数据操纵（4课时）一．实验目的1、要求学生熟练掌握添加、修改、删除数据的操作。

2、要求学生熟练掌握数据查询操作。

二、实验环境MS SQL SERVER 2000或者My SQL三、实验内容与步骤1.数据更新①在每个表中插入若干条记录；-- employeeINSERT employee VALUES('E0001','赵三','男','销售部','经理','2013/3/4','1992/3/4',8000,'杭州','2013/4/2');INSERT into employee values('E0002','赵四','M','销售部','成员','2017/3/5','1994/2/3',2500,'泰州','2018/4/2');INSERT employee VALUES('E0003','钱四','男','销售部','组长','2015/3/4','1991/3/4',8000,'杭州','2015/4/2');INSERT employee VALUES('E0004','钱行','男','后勤部','经理','2014/8/4','1981/9/8',10000,'杭州','2015/4/2');INSERT employee VALUES('E0005','欧阳泽明','男','后勤部','组长','2016/8/4','1986/7/8'7000,'杭州','2017/4/2');INSERT employee VALUES('E0006','欧阳凤','女','后勤部','成员','2012/5/4','1989/3/5',7000,'杭州','2016/4/2');INSERT employee VALUES('E0007','欧阳峰','男','宣传部','副经理','2013/5/4','1989/3/9',9000,'泰州','2016/4/2');INSERT employee VALUES('E0008','欧阳创正','男','宣传部','成员','2017/8/8','1999/7/25',4000,'泰州','2016/4/2');INSERT employee VALUES('E0009','王阳凤','女','宣传部','经理','2012/7/13','1985/3/6',9090,'杭州','2016/4/2'); INSERT employee VALUES('E0010','王航','女','联络部','成员','2017/5/4','1997/8/15',4000,'扬州','2016/4/2'); INSERT employee VALUES('E0011','王凤','女','联络部','经理','2015/7/13','1989/3/6',9090,'扬州','2016/4/2'); INSERT employee VALUES('E0012','王立','男','联络部','副经理','2013/8/14','1989/5/19',9000,'泰州','2016/4/2'); -- customerINSERT customer VALUES('K0001','李立','泰州','1902847477','225700');INSERT customer VALUES('K0002','李才','苏州','1322847472','295702');INSERT customer VALUES('K0003','王吉','苏州','1362847489','235701');INSERT customer VALUES('K0004','王嘉','扬州','1342847477','225400');INSERT customer VALUES('K0005','王洛','苏州','1379847477','221706');INSERT customer VALUES('K0006','端木镇','泰州','1382847477','215709');INSERT customer VALUES('K0007','端木释俗','北京','1262847477','225230');INSERT customer VALUES('K0008','孙可','上海','1322847477','225245');INSERT customer VALUES('K0009','孙立','北京','1762847477','225764');INSERT customer VALUES('K0010','孙苏','苏州','1092347477','212403');-- salesINSERT sales VALUES('S0010','K0001','E0001','7477','2018/4/29','2018/5/29','201947292');INSERT sales VALUES('S0009','K0002','E0002','6477','2018/4/22','2018/5/29','201947232');INSERT sales VALUES('S0008','K0003','E0003','5432','2018/5/9','2018/5/29','201947235');INSERT sales VALUES('S0007','K0004','E0004','9432','2018/4/15','2018/5/29','201956235');INSERT sales VALUES('S0006','K0005','E0005','5432','2018/5/9','2018/5/29','201007235');INSERT sales VALUES('S0005','K0006','E0006','8432','2018/5/19','2018/5/29','201940235');INSERT sales VALUES('S0004','K0007','E0007','9432','2018/5/19','2018/5/29','208947205');INSERT sales VALUES('S0003','K0008','E0008','6432','2018/5/24','2018/5/29','201947260');INSERT sales VALUES('S0002','K0009','E0009','5890','2018/5/2','2018/5/29','201947209');INSERT sales VALUES('S0001','K0010','E0010','10383','2018/5/7','2018/5/29','201947468');-- productINSERT product VALUES('C0001','短袖');INSERT product VALUES('C0002','短裤');INSERT product VALUES('C0003','长袖');INSERT product VALUES('C0004','牛仔裤');INSERT product VALUES('C0005','七分裤');INSERT product VALUES('C0006','五分裤');INSERT product VALUES('C0007','外套');INSERT product VALUES('C0008','短裙');INSERT product VALUES('C0009','连衣裙');INSERT product VALUES('C0010','衬衫');-- sales_itemINSERT sales_item VALUES('S0010','C0001',77,1,'2018/5/9');INSERT sales_item VALUES('S0009','C0002',146,12,'2018/5/9');INSERT sales_item VALUES('S0008','C0003',126,18,'2018/5/9');INSERT sales_item VALUES('S0007','C0004',124,9,'2018/5/9');INSERT sales_item VALUES('S0006','C0005',128,12,'2018/5/9');INSERT sales_item VALUES('S0005','C0006',124,42,'2018/5/9');INSERT sales_item VALUES('S0004','C0007',446,52,'2018/5/9'); INSERT sales_item VALUES('S0003','C0008',846,12,'2018/5/9'); INSERT sales_item VALUES('S0002','C0009',1277,22,'2018/5/9'); INSERT sales_item VALUES('S0001','C0010',2240,30,'2018/5/9');②将所有员工的薪水增加100；UPDATE employee SET salary=salary+100;③将产品名称为'A'的产品的单价改为10//A为短袖UPDATE sales_itemSET unit_price=10WHERE prod_id=(SELECT prod_idFROM productWHERE prod_name='短袖');④删除所有女性员工销售记录；DELETE sales_itemFROM salesLEFT JOIN employee ON sales.sale_id=employee.emp_no INNER JOIN sales_item ON sales.order_no=sales_item.order_no WHERE sex='f';DELETE salesFROM salesLEFT JOIN employee ON sales.sale_id=employee.emp_no WHERE sex='f';⑤删除订单金额小于100000的订单。