大型数据库资料打印.doc

《大型数据库资料打印.doc》由会员分享，可在线阅读，更多相关《大型数据库资料打印.doc（22页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、【例】创立一个students数据库，该数据库有1个10MB、1个20MB的数据文件与2个10MB的日志文件。数据文件的逻辑名称为：student1与student2；物理名称为：student1.mdf与student2.ndf；主文件是student1，由PRIMARY指定；两个数据文件的最大尺寸分别为无限大与100MB,增长速度分别为10%与1MB。事务日志文件逻辑名称为studentlog1与 studentlog2, ，物理文件名为studentlog1.ldf与 studentlog2.ldf,最大尺寸分别为50MB,增长速度分别为1MB。 数据文件存放在D:data下,日志文件存

2、放在E:data 。 create database students ON PRIMARY(NAME=STUDENT1,D:dataSTUDENT1.MDF,SIZE=10MB,MAXSIZE=UNLIMITED,(NAME=STUDENT2,D:dataSTUDENT2.NDF,SIZE=20MB,MAXSIZE=100MB,LOG ON(NAME=STUDENTLOG1,e:dataSTUDENTLOG1.LDF,SIZE=10MB,MAXSIZE=50MB,(NAME=STUDENTLOG2,e:dataSTUDENTLOG2.LDF,SIZE=10MB,MAXSIZE=50MB,GO

3、工程实训创立名为“goods的数据库，要求：1、主数据文件名为goods_data.MDF，存放在e：目录下，初始值 大小为5MB，增长方式为按照10%的比例增长；2、次数据文件名为goods_data1.NDF与goods_data2.NDF，都存放在D：目录下，初始大小为1MB，增长方式为按2MB的增量增长，文件组为aa ；3、日志文件名goods_log.LDF，都存放在E：目录下，初始大小为3MB，增长方式为按照1MB的增量增长。 要求分别使用企业管理器与Transact-SQL语句，并记录完整语句。4、对上述数据库分别进展查看、别离、附加、脱机、联机、收缩、删除等操作。CREATE

4、DATABASE GOODSON PRIMARY(NAME=goods_data.MDF, C:goods_data.MDF, SIZE=5, aa(NAME=good_data2.NDF, D:good_data2.NDF, SIZE=1,(NAME=good_data1.NDF, D:good_data1.NDF, SIZE=1,LOG ON(NAME=goods_log.LDF, E:goods_log.LDF,SIZE=3,在数据库xscj中的表“学生根本信息表中定义学生性别列只能是“男或“女，如果用户输入其它值，系统均提示用户输入无效。use xscjALTER TABLE 学生根本

5、信息表add CONSTRAINT ck_stud_info CHECK(性别=N男 or 性别=N女) /*删除约束名为“CK_stud_info的约束的语句是：*/ALTER TABLE 学生根本信息表DROP CONSTRAINT ck_stud_info例：在Employees表中给EmployeeID字段添加Check约束，使EmployeeID100ALTER TABLE Employees WITH NOCHECK ADD CONSTRAINT ID_check CHECK (EmployeeID 100)WITH NOCHECK防止根据现有记录验证该约束default约束的创立

6、、查看、删除/* 为表stud_info创立一个约束名为“de_gender的DEFAULT约束，要求性别(gender)的默认值为“男。其T-SQL语句*/ALTER TABLE stud_infoADD CONSTRAINT de_gender DEFAULT N男 FOR gender/* 删除DEFAULT约束的语句：*/ALTER TABLE stud_infoDROP CONSTRAINT de_genderPrimary key 约束的创立、查看、删除/* 为表stud_info中的学号(stud_id)定义主键。其T-SQL语句如下：*/ALTER TABLE stud_inf

7、oADD CONSTRAINT PK_XH PRIMARY KEY CLUSTERED(stud_id)/* 删除该主键的T-SQL语句 */ALTER TABLE stud_infoDROP CONSTRAINT PK_XH例：在Employees表中，给字段Name增加PRIMARY KEY约束，并设为非聚集索引默认主键列为聚集索引ALTER TABLE Employees ADD CONSTRAINT PK_Name PRIMARY KEY NONCLUSTERED (Name)外键约束的创立、查看、删除/* 为学生成绩表stud_grade与课程信息表lesson_info创立外键约束

8、，其T-SQL语句如下：*/-为lesson_info表中的course_id列建立主键约束ALTER TABLE lesson_infoADD CONSTRAINT pk_course_id PRIMARY KEY CLUSTERED(course_id)-为stud_grade表中的course_id列建立外键约束ALTER TABLE stud_gradeADD CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES lesson_info (course_id)UNIQUE约束的创立、查看、删除/* 针对学生根本信息表的“

9、号码创立UNIQUE约束。具体语句如下： */ALTER TABLE stud_infoADD CONSTRAINT UN_telcode UNIQUE(telcode)/* 删除UNIQUE约束的T-SQL语句如下：*/ALTER TABLE stud_infoDROP CONSTRAINT UN_telcode查询少数民族学生的根本情况。USE XSCJSELECT * FROM 学生根本信息表WHERE 族别汉族4、 TOP关键字的使用用于提取前几条，或者前百分比数据。语法：TOP(EXPRESSION)PERCENTl 例：查询前三名同学的所有信息SELECTTOP (3) * FRO

10、M student5、 DISTINCT关键字的使用例：查询所有学生所在的城市，要求没有重复信息SELECTDISTINCT CityFROM student例：查询所有学生的姓名、学号以及出生年并按姓名升序与出生年降序排列SELECT Name, Numb, YearFROM student ORDER BY Name, Year DESC例 查询所有男生学号、姓名与年龄，并按出生日期进展排列(升序)的语句：USE studentGOSELECT stud_id 学号, name 姓名, year(getdate()-year(birthday) 年龄, birthday 出生日期FROM

11、stud_infoWHERE gender = N男ORDER BY birthday ASC例、统计计算机工程系各个专业的学生的平均入学成绩的语句：USE studentGOSELECT substring(stud_id,5,2) 专业编号, avg(mark) 平均入学成绩FROM stud_infoWHERE substring(stud_id,3,2) =01GROUP BY substring(stud_id,5,2)-1、在学生根本信息表中查询所有姓王的学生的学号、姓名、家庭住址。select 学号,姓名,家庭住址from 学生根本信息表where 姓名like 王%-2、查询所

12、有出生日期在1985-03-10到1986-03-10之间学生的学号与姓名。select 学号,姓名,出生日期from 学生根本信息表where 出生日期between 03/10/1985 and 03/10/1986-3、查询课程编号为002号课程成绩高于80分的学生学号,姓名,课程编号,成绩。from 学生根本信息表a,成绩表bwhere a.学号=b.学号and 课程编号=002 and 成绩80-4、从学生根本信息表中查找所有新疆学生的记录。select * from 学生根本信息表where 家庭住址like %新疆%-5、从学生根本信息表中检索学号末位数字是奇数的学生。selec

13、t * from 学生根本信息表where right(rtrim(学号),1)%20-6、查询学生的学号,姓名，课程编号,成绩，并按成绩降序、姓名升序排列from 学生根本信息表a,成绩表border by 成绩desc,姓名-7、统计各民族男女生的学生总数，输出结果包含：性别、族别、人数。select 性别,族别,count(*) as 人数from 学生根本信息表group by 族别, 性别-8、在成绩表中统计每个学生的学号,平均成绩及总成绩。select 学号,avg(成绩) 平均成绩,sum(成绩) 总成绩from 成绩表group by 学号-9、查询总成绩小于500分的学生学号

14、、姓名、总成绩及所在班级select a.学号,a.姓名,c.班级名称,sum(成绩) 总成绩from 学生根本信息表a,成绩表b,班级表cgroup by a.学号,a.姓名,c.班级名称 having sum(成绩)=60-11、列出成绩表中各门课程的学生学号、课程编号与成绩以及该门课程的平均分、最高分与最低分。select 学号,成绩,课程编号from 成绩表order by课程编号compute avg(成绩),max(成绩),min(成绩) by 课程编号-12、列出C语言程序设计课程成绩高于该门课程平均分的学生信息。select a.* from 学生根本信息表a,成绩表b,课程信

15、息表c where a.学号=b.学号and b.课程编号=c.课程编号and c.课程名称=C语言程序设计 and b.成绩(select avg(成绩) from 成绩表a,课程信息表b where a.课程编号=b.课程编号and b.课程名称=C语言程序设计)-13、查询选修了课程名称为C语言程序设计课程的学生根本信息。select * from 学生根本信息表where 学号in ( select a.学号 from 成绩表a, 课程信息表b where a.课程编号=b.课程编号and b.课程名称=C语言程序设计)-14、查询总成绩高于500分的学生根本信息select * fr

16、om 学生根本信息表where 学号in ( select 学号from 成绩表group by 学号having sum(成绩)500)-15、查询未选修课程编号为003的课程的学生情况。select * from 学生根本信息表where 学号not in( select 学号from 成绩表where 课程编号=003)-16、查询比所有回族的学生年龄都大的学生记录。select * from 学生根本信息表where year(getdate()-year(出生日期)( select max( year(getdate()-year(出生日期) from 学生根本信息表 where 族

17、别=回族)-17、查询课程编号为004且成绩不低于课程编号为003的最低成绩的学生的姓名，系部名称与班级名称from 学生根本信息表a,班级表b,系部表cwhere a.班级编号=b.班级编号and b.系部编号=c.系部编号and a.学号in( select 学号 from 成绩表 where 课程编号=004 and 成绩=( select max(成绩) from 成绩表 where 课程编号=003)-18、对每个学生的选修的所有课程的成绩求平均值，并显示详细记录。select * from 成绩表order by 学号compute avg(成绩) by 学号-19、查询选修课程超

18、过门且成绩都在分以上的学生的学号。select 学号,count(*) as 人数from 成绩表group by 学号having min(成绩)60 and count(*)2-20、查询信息与计算机科学系学生的学号、姓名、家庭住址，并产生一个学生总人数行。from 学生根本信息表a,班级表b,系部表cwhere a.班级编号=b.班级编号and b.系部编号=c.系部编号and 系部名称=信息与计算机科学compute count(学号) 使用INSERT语句插入数据 例1： 在表stud_info中插入一条记录(0401040125,张琳, 女)。具体语句如下： INSERT INTO

19、 stud_info (stud_id,name,gender) VALUES(0401040125,N张琳, N女)将查询结果插入到表中l INSERT TABLE_NAMEl SELECT COLUMN_NAMEl FROM TABLE_LISTl WHERE SEARCH_CONDITIONS例5：student表，假设现在有newStudent表，该表用于统计03001班学生的信息：Name、Numb、Year、City、Class。求怎样从student表中选取记录插入到newtable表中。CREATE TABLE newStudent Name nchar(20) , Numb

20、nchar(20) not null, Class nchar(30) , City nchar(25), Year intINSERT newStudentSELECT Name,Numb,Class,City,YearFROM studentWHERE Class = 03001清空people表中的数据 TRUNCATE TABLEpeople在数据库student中的stud_grade表中stud_id列上创立名为stud_id_index的聚集索引。USE studentGOCREATE CLUSTERED INDEX stud_id_index ON stud_grade (st

21、ud_id)在数据库student中的stud_grade表中course_id列上创立名为CourseIndex的非聚集索引USE studentGOCREATE NONCLUSTERED INDEX CourseIndex ON stud_grade (course_id)-1、创立一个能向学生表中插入一条记录的存储过程Insert_student,该过程需要5个参数，分别用来传递学号、姓名、性别、班级编号、出生日期5个值。use xscjgocreate proc insert_student xuehao char(8),name char(12),xingb char(2),bjbh

22、 char(8),birthday datetimeasbegininsert 学生根本信息表(学号,姓名,性别,班级编号,出生日期)values(xuehao,name,xingb,bjbh,birthday)end-2、写出执行存储过程Insert_student的SQL语句，向学生表中插入一个新同学，并提供相应的实参值实参值自定。exec insert_student 22100011,郑佩佩,女,20051003,2012/01/02-3、创立一个向课程表中插入一门新课程的存储过程Insert_course，该存储过程需要四个参数，分别用来传递课程号、课程名、学分、系部编号，-但允许参

23、数“学分的默认值为2，即当执行存储过程Insert_course时，未给参数“学分提供实参值时，存储过程将按默认值2进展运算。gocreate proc insert_course kch char(4),kcm char(30),xbbh char(2),xf decimal(3,0)=2asinsert 课程信息表(课程编号,课程名称,系部编号,学分) values(kch,kcm,xbbh,xf)-4、执行存储过程Insert_course，向课程表Course中插入一门新课程。分两种情况写出相应的SQL命令： -第一种情况：提供四个实参值执行存储过程Insert_course exec

24、 insert_course 1104,手机编程,01,3 -第二种情况：只提供三个实参值执行存储过程Insert_course，即：不提供与参数“学分对应的实参值。 exec insert_course 1105,嵌入式开发,01-5、创立一个名为Query_student的存储过程，该存储过程的功能是根据学号查询学生表中某一学生的姓名、班级编号、性别及出生日期。gocreate proc query_student xuehao char(8)as select 姓名,班级编号,性别,出生日期 from 学生根本信息表 where 学号=xuehao-6、执行存储过程Query_stude

25、nt，查询学号为 000003”的学生的学号、班级号、性别及出生日期。写出完成此功能的SQL命令。exec query_student 000003-7、问题：请创立存储过程，查看001课程考试平均分以及未通过考试的学员名单gocreate proc 成绩查询asbeginselect avg(成绩) as 平均分 from 成绩表 where 课程编号=001select a.学号,姓名,成绩 from 学生根本信息表 a,成绩表 bwhere a.学号=b.学号 and b.课程编号=001 and 成绩60end-执行存储过程 exec 成绩查询-8、问题：修改上例：由于每次考试的难易程

26、度不一样，每次的及格线可能随时变化不再是60分，这导致考试的评判结果也相应变化。-分析：上述存储过程添加1个输入参数writtenPass 及格线 gocreate proc 成绩查询2 writtenPass intasbeginselect avg(成绩) as 平均分 from 成绩表 where 课程编号=001select a.学号,姓名,成绩 from 学生根本信息表 a,成绩表 bwhere a.学号=b.学号 and b.课程编号=001 and 成绩=writtenPassselect passcount=count(*) from 学生根本信息表 a,成绩表 bwhere

27、a.学号=b.学号 and b.课程编号=001 and 成绩writtenPassend-执行存储过程declare 人数 intexec 成绩查询3 70,人数 outputselect 人数-10、思考：如何返回及格率？提示：用输出参数存储过程中用查询赋值语句分别求出及格人数与总人数，再求出及格率。gocreate proc 成绩查询4 writtenPass decimal(5,2),passcount int output,passrate decimal(6,2) outputasdeclare zongrenshu decimal(5,0)beginselect zongrens

28、hu=count(*) from 学生根本信息表 a,成绩表 bwhere a.学号=b.学号 and b.课程编号=001 select passcount=count(*) from 学生根本信息表 a,成绩表 bwhere a.学号=b.学号 and b.课程编号=001 and 成绩writtenPassset passrate=passcount/zongrenshuprint passcount/zongrenshuend-执行存储过程declare 及格人数 int,及格比率 decimal(6,2)exec 成绩查询4 70, 及格人数 output,及格比率 outputpr

29、int 001课程及格人数为+cast(及格人数 as varchar(50)+ +及格比率为+cast(及格比率*100 as varchar(50)+%-触发器题目-1.创立一个当学生表中插入一个新同学信息时能自动列出全部同学学生信息的触发器Display_triggergoCreate trigger display_triggerOn 学生根本信息表For insertAsSelect * from 学生根本信息表-2.调用上次实验创立的存储过程Insert_student，向学生表中插入一新同学，看触发器Display_trigger是否被执行。exec insert_student

30、 22100009,王佩佩,女,20051003,2021-01-02 09:06:18-3.练习与管理触发器。-1建立数据库testdb，并在数据库中建立两个表：-Txl(ID int, Name char(10), Age int)-Person_ counts(Person_ count int)create database textdbuse textdbcreate table tx1(id int,name char(10),age int)create table person_counts(person_count int)-2使用T-SQL编写一个触发器tr_person_

31、ins，每当txl表中插入一行数据时，表Person_ counts中对应的数量也相应地发生变化。-因为表person_counts中无记录，所以先向里面插入一条记录，person_count值为0insert person_counts values(0)-创立触发器create trigger tr_person_inson tx1for insertasdeclare jilushu intif exists(select * from inserted)beginSELECT jilushu=COUNT(*) from tx1update person_counts set perso

32、n_count=jilushu endelse rollback tran-验证触发器insert tx1 values(2,sandy2,40)goselect * from person_counts-3使用企业管理器创立一个触发器tr_person_del，每当txl表中删除记录时，表Person_ counts中对应的数量也相应地发生变化。gocreate trigger tr_person_delon tx1for deleteasdeclare jilushu intif exists(select * from deleted)beginSELECT jilushu=COUNT(

33、*) from tx1update person_counts set person_count=jilushu endelse rollback tran-验证触发器delete tx1 where id=2goselect * from person_counts-4使用企业管理器查看触发器tr_person_del的内容，并将该触发器的内容加密。exec sp_helptext tr_person_delgoalter trigger tr_person_delon tx1with encryptionfor deleteasdeclare jilushu intif exists(select * from deleted)beginSELECT jilushu=COUNT(*) from tx1update person_counts set person_count=jilushu endelse rollback tran第 22 页