数据库上机实验报告.doc

数据库上机实验报告院系：专业：班级：姓名：学号： 指导老师： 年月日一、 实验目的熟练掌握SQL语句的插入、修改、删除、查询等相关语法。能够使用MicrosoftSQLServer和MySQL软件进行相关的数据库操作。 二、 实验内容1数据库的创建和使用；2表的创建和使用；3数据的插入、删除和修改；4数据的查询；5.视图的创建和使用。实验一、创建学生成绩管理数据库在学生成绩管理数据库中，包括基本的三个关系：student，course，sc 。（1）student（sno，sname，ssex，sage，sdept），表示学号，姓名，性别，年龄，所在系。主键学号，姓名唯一，系 默认cs。（2）Course (cno，cname，cpno，ccredit)，表示课程号，课程名，先行课程号，学分。主键 课程号（3）SC (sno，cno，grade)，表示学号，课程号，成绩。外键，学号 和课程号。创建该数据库代码及其最后结果图如下：create table student(sno char(20) primary key,sname char(10) unique, ssex char(2),sage smallint, sdept char(12) defaultcs)create table course(cno char(10) primary key,cname char(20), cpno char(10),ccredit char(6)create table sc(sno char(20),cno char(10),grade smallint,primary key(sno,cno),foreign key(sno) references student(sno),foreign key(cno) references course(cno)实验二、在实验一的结果上进行各种操作练习及其代码一 修改表结构，对学生表增加，电话号码一列，删除年龄一列。alter table studentadd pnumber char(22)alter table studentdrop column sage二 简单 查询例1.查询全体学生详细记录 select *from student例2.查询信息系所有男生的学号、姓名、出生年份select sno,sname,sagefrom studentwhere ssex=男例3.查询选修过课的学生的学号select snofrom sc例4. 查询年龄在25-30之间的学生姓名及性别。select sname,ssexfrom studentwhere sage between 25 and 30例5. 查询姓“欧阳”的学生 。select snamefrom studentwhere sname like 欧阳%例6. 查询信息系IS，数学系MA和计算机系CS的学生。select snamefrom studentwhere sdept=is or sdept=ma or sdept=cs例7 查询所有学生的成绩及姓名select grade,snamefrom student,scwhere sc.sno=student.sno例8 查询95001学生的所选的课程，成绩和专业select cname,grade,sdeptfrom student,sc,coursewhere student.sno=sc.sno and o=o and student.sno=95001实验三、四关系模式如下：数据内容如下：创建数据库并使用数据库create database Enterprise;use Enterprise;创建表EMPLOYEE并插入数据create table EMPLOYEE(FNAME char(20),MINIT char(1),LNAME char(20),SSN char(9) primary key,BDATE datetime,ADDRESS char(50),SEX char(1) check(SEX = M or SEX = F),SALARY int,SUPERSSN char(9),DNO smallint,foreign key(SUPERSSN) references EMPLOYEE(SSN);insert into EMPLOYEE values(James,E,Bong,888665555,1937-11-10,450 Stone,Houston,TX,M,55000,null,1);insert into EMPLOYEE values(Franklin,T,Wong,333445555,1955-12-08,638 Voss,Houston,Tx,M,40000,888665555,5);insert into EMPLOYEE values(Jennifer,S,Wallace,987654321,1941-06-20,291 Berry,Bellaire,TX,F,43000,888665555,4);insert into EMPLOYEE values(Ramesh,K,Narayan,666884444,1962-09-15,975 Fire Oak,Humble,TX,M,38000,333445555,5);insert into EMPLOYEE values(Joyce,A,English,453453453,1972-07-31,5631 Rice,Houston,TX,F,25000,333445555,5);insert into EMPLOYEE values(John,B,Smith,123456789,1965-01-09,731 Fondren,Houston,TX,M,30000,333445555,5);insert into EMPLOYEE values(Alicia,J,Zelaya,999887777,1968-07-19,3321 Castle,Spring,TX,F,25000,987654321,4);insert into EMPLOYEE values(Ahmad,V,Jabbar,987987987,1969-03-29,980 Dallas,Houston,TX,M,25000,987654321,4);创建表DEPARTMENT并插入数据create table DEPARTMENT(DNAME char(20),DNUMBER smallint primary key,MGRSSN char(9),MGRSTARTDATE datetime);insert into DEPARTMENT values(Research,5,333445555,1988-05-22);insert into DEPARTMENT values(Administration,4,987654321,1995-01-01);insert into DEPARTMENT values(Headquarters,1,888665555,1981-06-19);创建表PROJECT并插入数据create table PROJECT(PNAME char(20),PNUMBER smallint primary key,PLOCATION char(20),DNUM smallint,foreign key(DNUM) references DEPARTMENT(DNUMBER);insert into PROJECT values(ProductX,1,Bellaire,5);insert into PROJECT values(ProductY,2,Sugarland,5);insert into PROJECT values(ProductZ,3,Houston,5);insert into PROJECT values(Computerization,10,Stafford,4);insert into PROJECT values(Reorganization,20,Houston,1);insert into PROJECT values(Newbenefits,30,Stafford,4);创建表WORKS_ON并插入数据create table WORKS_ON(ESSN char(9),PNO smallint,HOURS float(1),primary key(ESSN,PNO),foreign key(ESSN) references EMPLOYEE(SSN),foreign key(PNO) references PROJECT(PNUMBER);insert into WORKS_ON values(123456789,1,32.5);insert into WORKS_ON values(123456789,2,7.5);insert into WORKS_ON values(666884444,3,40.0);insert into WORKS_ON values(453453453,1,20.0);insert into WORKS_ON values(453453453,2,20.0);insert into WORKS_ON values(333445555,2,10.0);insert into WORKS_ON values(333445555,3,10.0);insert into WORKS_ON values(333445555,10,10.0);insert into WORKS_ON values(333445555,20,10.0);insert into WORKS_ON values(999887777,30,30.0);insert into WORKS_ON values(999887777,10,10.0);insert into WORKS_ON values(987987987,10,35.0);insert into WORKS_ON values(987987987,30,5.0);insert into WORKS_ON values(987654321,30,20.0);insert into WORKS_ON values(987654321,20,15.0);insert into WORKS_ON values(888665555,20,null);创建表DEPT_LOACTION并插入数据create table DEPT_LOCATION(DNUMBER smallint,DLOCATION char(20),primary key(DNUMBER,DLOCATION),foreign key(DNUMBER) references DEPARTMENT(DNUMBER);insert into DEPT_LOCATION values(1,Houston);insert into DEPT_LOCATION values(4,Stafford);insert into DEPT_LOCATION values(5,Bellaire);insert into DEPT_LOCATION values(5,Sugarland);insert into DEPT_LOCATION values(5,Houston);创建表DEPENDENT并插入数据create table DEPENDENT(ESSN char(9),DEPENDENT_NAME char(20),SEX char(1),BDATE datetime,RELATIONSHIP char(10),primary key(ESSN,DEPENDENT_NAME),foreign key(ESSN) references EMPLOYEE(SSN);insert into DEPENDENT values(333445555,Alice,F,1986-04-05,DAUGHTER);insert into DEPENDENT values(333445555,Theodore,M,1983-10-25,SON);insert into DEPENDENT values(333445555,Joy,F,1958-05-03,SPOUSE);insert into DEPENDENT values(987654321,Abner,M,1942-02-28,SPOUSE);insert into DEPENDENT values(123456789,Michael,M,1988-01-04,SON);insert into DEPENDENT values(123456789,Alice,F,1988-12-30,DAUGHTER);insert into DEPENDENT values(123456789,Elizabeth,F,1967-05-05,SPOUSE);查询操作Retrieve the birthdate and address of the employee whose name is John B. Smith.select BDATE,ADDRESS from EMPLOYEEwhere FNAME=John and MINIT=B and LNAME=Smith;Retrieve the name and address of all employees who work for the Research department.select FNAME,ADDRESS from EMPLOYEE,DEPARTMENTwhere DEPARTMENT.MGRSSN = EMPLOYEE.SSNand DEPARTMENT.DNAME = Research;For every project located in Stafford, list the project number, the controlling department number, and the department managers last name, address, and birthdate.select PNUMBER,DNAME,LNAME,BDATE,ADDRESSfrom PROJECT,DEPARTMENT,DEPT_LOCATION,EMPLOYEEwhere DEPT_LOCATION.DLOCATION=Staffordand DEPT_LOCATION.DNUMBER = PROJECT.DNUMand DEPT_LOCATION.DNUMBER = DEPARTMENT.DNUMBERand DEPARTMENT.MGRSSN = EMPLOYEE.SSN;For each employee, retrieve the employees name, and the name of his or her immediate supervisor.select EP.FNAME,EP.MINIT,EP.LNAME,ES.FNAME,ES.MINIT,ES.LNAMEfrom EMPLOYEE EP,EMPLOYEE ESwhere EP.SUPERSSN = ES.SSN;Retrieve the SSN values for all employees.select SSN from EMPLOYEE;Retrieve the names of employees who have no dependents.select SSN from EMPLOYEEwhere not exists(select ESSN from DEPENDENTwhere EMPLOYEE.SSN = ESSN);Retrieve the name of each employee who works on all the projects controlled by department number 5.select distinct FNAME,LNAMEfrom EMPLOYEE,PROJECT,DEPARTMENT,WORKS_ONwhere DEPARTMENT.DNUMBER=5and PROJECT.DNUM = DEPARTMENT.DNUMBERand PROJECT.PNUMBER = WORKS_ON.PNOand EMPLOYEE.SSN = WORKS_ON.ESSN;Make a list of all project numbers for projects that involve an employee whose last name is Smith as a worker or as a manager of the department that controls the project.select distinct WORKS_ON.PNOfrom EMPLOYEE,WORKS_ON,PROJECT,DEPARTMENTwhere EMPLOYEE.LNAME = Smithand (EMPLOYEE.SSN = WORKS_ON.ESSNor PROJECT.DNUM = DEPARTMENT.DNUMBERand DEPARTMENT.MGRSSN = EMPLOYEE.SSN);Retrieve the social security numbers of all employees who work on project number 1, 2, or 3.select distinct ESSN from WORKS_ON,PROJECTwhere WORKS_ON.PNO=PROJECT.PNUMBERand (PROJECT.PNUMBER=1 or PROJECT.PNUMBER=2 or PROJECT.PNUMBER=3)Retrieve the names of all employees who do not have supervisorsselect FNAME from EMPLOYEEwhere SUPERSSN is nullFind the maximum salary, the minimum salary, and the average salary among all employees.select max(SALARY) maximum salary,min(SALARY) minimum salary,avg(SALARY) average salaryfrom EMPLOYEEFind the maximum salary, the minimum salary, and the average salary among employees who work for the Research department.select max(SALARY) maximum salary,min(SALARY) minimum salary,avg(SALARY) average salaryfrom EMPLOYEE,DEPARTMENTwhere EMPLOYEE.DNO = DEPARTMENT.DNUMBERand DEPARTMENT.DNAME = Research;Retrieve the total number of employees in the companyselect count(FNAME) total number from EMPLOYEE;Retrieve the number of employees in the Research departmentselect count(FNAME) total number from EMPLOYEE,DEPARTMENTwhere EMPLOYEE.DNO = DEPARTMENT.DNUMBERand DEPARTMENT.DNAME = Research;For each department, retrieve the department number, the number of employees in the department, and their average salary.select DEPARTMENT.DNUMBER,count(EMPLOYEE.DNO) count,avg(EMPLOYEE.SALARY) average salaryfrom DEPARTMENT,EMPLOYEEwhere DEPARTMENT.DNUMBER = EMPLOYEE.DNOgroup by DEPARTMENT.DNUMBERFor each project, retrieve the project number, project name, and the number of employees who work on that project.select PROJECT.PNAME,count(PROJECT.PNUMBER) countfrom PROJECT,WORKS_ONwhere PROJECT.PNUMBER = WORKS_ON.PNOgroup by PROJECT.PNAMEFor each project on which more than two employees work , retrieve the project number, project name, and the number of employees who work on that project.select PROJECT.PNAME,count(PROJECT.PNUMBER) countfrom PROJECT,WORKS_ONwhere PROJECT.PNUMBER = WORKS_ON.PNOgroup by PROJECT.PNAMERetrieve all employees whose address is in Houston, Texas. Here, the value of the ADDRESS attribute must contain the substring Houston,TX.select FNAME,MINIT,LNAME,ADDRESSfrom EMPLOYEEwhere EMPLOYEE.ADDRESS like %Houston,TX;Retrieve all employees who were born during the 1950s. Here, 5 must be the 9th character of the string (according to our format for date), so the BDATE value is _5_, with each underscore as a place holder for a single arbitrary character.select FNAME,MINIT,LNAME,BDATEfrom EMPLOYEEwhere convert(varchar,BDATE,110) like _5_Show the effect of giving all employees who work on the ProductX project a 10% raise.Retrieve a list of employees and the projects each works in, ordered by the employees department, and within each department ordered alphabetically by employee last name.实验五、附加MSSQL2000的示例数据库pubs数据库1使用演示脚本创建表、插入数据，查找以x%开头的数据。-建表create table test(col varchar(10)-插入数据insert into test values(x_yz)insert into test values(xyz)insert into test values(x%yz)insert into test values(xyz)练习及其代码如下：-练习1-找出pubs数据库titles表中计算机类图书中价格最高的图书的价格。USEpubsSELECTmax(price)FROMtitleswheretype=popular_comp-练习2-查询titles表中有几类图书。USEpubsSELECTcount(distincttype)FROMtitles-练习3-按照州进行分类，查找每个州有几名作者。USEpubsSELECTstate,count(*)FROMauthorsgroupbystateorderby1-练习4-要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和（ytd_sales）。USEpubsSELECTpub_id,sum(ytd_sales)FROMtitlesgroupbypub_idorderby1-练习5-在pubs数据库的titles表中，找出平均价格大于18美元的书的种类。USEpubsSELECTpub_id,avg(price)平均价格FROMtitlesGROUPBYpub_idHAVINGavg(price)18 -练习6 -在pubs数据库的titles表中，找出最高价大于20美元的书的种类。 USE pubs SELECT type,max(price) 平均价格 FROM titles GROUP BY type HAVING max(price) 20 -练习7 - 找出title_id和pub_name的对应关系。 Use pubs Select titles.title_id, publishers.pub_name From titles JOIN publishers ON titles.pub_id=publishers.pub_id -练习8 -找出title_id, title和pub_name的对应关系。 Use pubs Select titles.title_id, titles.title, publishers.pub_name From titles JOIN publishers ON titles.pub_id=publishers.pub_id -练习9 -查询每个作者的编号，姓名，所出的书的编号，并对结果排序。 Use pubs Select authors.au_id, authors.au_fname + . + authors.au_lname name, titleauthor.title_id From authors JOIN titleauthor ON authors.au_id=titleauthor.au_id order by authors.au_id10. 从authors表中选择state,city列，从publisher表中选择state,city列，并把两个查询的结果合并为一个结果集，并对结果集按city列、state列进行排序。 use pubs select state,city from publishers union select state,city from authors order by 1,2 11. 对上面的查询语句作修改，保留所有重复的记录。 Selectauthors.city,authors.state,publishers.city,publishers.state from publishers,authors12显示所有来自CA州的作家的全部作品和作家代号。（使用IN，和连接两种方法） use pubs select title_id,au_id from titleauthor where au_id in ( select au_id from authors where state = CA) order by title_id use pubs select t.title_id,t.au_id from titleauthor t join authors a on t.au_id = a.au_id where a.state = CA order by title_id 13查找由位于以字母 B 开头的城市中的任一出版商出版的书名：(使用exists和in两种方法) USE pubs SELECT title FROM titles WHERE EXISTS (SELECT * FROM publishers WHERE pub_id = titles.pub_id AND city LIKE B%) USE pubsSELECT title FROM titles WHERE pub_id IN (SELECT pub_id FROM publishers WHERE city LIKE B%) 实验六、修改数据练习一用 sql命令插入数据（1）创建一个表STU，有三列：id int，name varchar(10)，class_id char；要求id列为自动增长，class_id列具有默认值0801。create table stu(id int identity(1,1),name varchar(10),class_id char(10) default(0801)（2）并为此表插入数据：zhj,0801Wxm,0801Mbb,0801Wd,0802insert into stu(name,class_id)values(zhj,0801)insert into stu(name,class_id)values(Wxm,0801)insert into stu(name,class_id)values(Mbb,0801)insert into stu(name,class_id)values(Wd,0802)二：使用insert select语句，建立一个新表：CA_author，这个表只包含家在CA州的作者的au_id, name（包括fname和lnam