数据库终极版.docx

1、数据库终极版创建一个名称为STUDENT3的数据库，该数据库的主文件逻辑名称为STUDENT3_data，物理文件名为STUDENT3.mdf，初始大小为3MB，最大尺寸为无限大，增长速度为15%；数据库的日志文件逻辑名称为STUDENT3_log，物理文件名为STUDENT3.ldf，初始大小为2MB，最大尺寸为50MB，增长速度为1MB；要求数据库文件和日志文件的物理文件都存放在E盘的DATA文件夹下。实现的步骤如下：（1）在E盘创建一个新的文件夹，名称是“DATA”。（2）在SQL Server Management Studio中新建一个查询页面。（3）输入以下程序段并执行此查询：cr

2、eate database STUDENT3on primary(name= STUDENT3_data,filename=E:DATASTUDENT3.mdf,size=3,maxsize=unlimited,filegrowth=15%)log on(name= STUDENT3_log,filename=E:DATASTUDENT3.ldf,size=2,maxsize=50,filegrowth=1)创建一个指定多个数据文件和日志文件的数据库。该数据库名称为STUDENTS，有1个5MB和1个10MB的数据文件和2个5MB的事务日志文件。数据文件逻辑名称为STUDENTS1和STUDE

3、NTS2，物理文件名为STUDENTS1.mdf和STUDENTS2.ndf。主文件是STUDENTS1，由PRIMARY指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为STUDENTSLOG1和STUDENTSLOG2，物理文件名为STUDENTSLOG1.ldf和STUDENTSLOG2.ldf，最大尺寸均为50MB，文件增长速度为1MB。要求数据库文件和日志文件的物理文件都存放在E盘的DATA文件夹下。实现的步骤如下：（1）在E盘创建一个新的文件夹，名称是“DATA”。（2）在SQL Server Management Studi

4、o中新建一个查询页面。（3）输入以下程序段并执行此查询：create database STUDENTSon primary(name=STUDENTS1,filename=E:DATASTUDENTS1.mdf,size=5,maxsize=unlimited,filegrowth=10%),(name= STUDENTS12,filename=E:DATASTUDENTS2.ndf,size=10,maxsize=100,filegrowth=1)log on (name=STUDENTSLOG1,filename=E:DATASTUDENTSLOG1.ldf,size=5,maxsize

5、=50,filegrowth=1),(name=STUDENTSLOG2,filename=E:DATASTUDENTSLOG2.ldf,size=5,maxsize=50,filegrowth=1)删除已创建的数据库STUDENTS实现的步骤如下：（1）在SQL Server Management Studio中新建一个查询页面。（2）输入以下程序段并执行此查询：drop database STUDENTS说明：当有别的用户正在使用此数据库时，则不能进行删除操作。表4-2-1 学生基本信息表（名为t_student）组成实现的步骤如下：（1）在SQL Server Management St

6、udio中新建一个查询页面。（2）输入以下程序段并执行此查询：USE STUDENTgoCreate table t_student (s_number char(10) not null,s_name char(8),sex char(2) default 男 ,birthday datetime,polity char(4)go如果系统的输出结果为：“命令已成功完成。”，则表明t_student数据表已经创建成功了。为t_student2表中的s_number字段创建非空约束。程序清单如下：USE STUDENTGOCREATE TABLE t_student2(s_number char

7、(10) NOT NULL,s_name char(8),sex char(2),phonenum int)在数据库STUDENT中创建一个t_student3表，并定义s_number为主键。增加主键后，就限定了该记录中不能插入编号相同的两个或者多个数据信息，从而保证了表中的记录信息是唯一的。程序清单如下：USE STUDENTgoCreate table t_student3(s_number char(10) primary key,s_name char(8),sex char(2),birthday datetime,polity char(4)创建一个学生信息表t_student4

8、，其中s_name字段具有唯一性。程序清单如下：USE STUDENTGOCREATE TABLE t_student4(s_number char(10) PRIMARY KEY,s_name char(8),sex char(2) ,birthday datetime,polity char(4),constraint uk_name unique(s_name)创建一个学生信息表t_student5，其中输入性别字段值时，只能接受“男”或者“女”，而不能接受其他数据。程序清单如下：USE STUDENTGOcreate table t_student5(s_number char(10)

9、,s_name char(8),sex char(2),phonenum int, constraint chk_sex check(sex in (男,女)创建一个学生信息表t_student6，为sex字段创建默认约束“男”程序清单如下：USE STUDENTGOCREATE TABLE t_student6(s_number char(10),s_name char(8),sex char(2) default 男,phonenum int)创建t_score表，并为t_score创建外键约束，该约束把表t_score中的学生学号（s_number）字段和表t_student中的学生学号

10、（s_number）字段关联起来，实现t_score中的学生学号（s_number）字段的取值要参照表t_student中的学生学号（s_number）字段的数据值。程序清单如下：USE STUDENTGOCREATE TABLE t_score(s_number char(10),c_number char(10) ,score int ,constraint con_s_numberforeign key (s_number)references t_student(s_number) GO在数据库STUDENT中创建一个t_course1表，然后在表中增加一个c_pnumber字段，删除

11、表中的credit字段，并且修改c_name字段的数据长度。SQL语句的程序清单如下：USE STUDENTgoCreate table t_course1(c_number char(10) not null,c_name char(30),hours int,credit real)goalter table t_course1 alter column c_name char(40) alter table t_course1 drop column creditalter table t_course1 add c_pnumber char(10)go（1）显示STUDENT数据库中所

12、有对象的信息。程序如下：USE STUDENTGOExec sp_help GO（2）显示t_student数据表的信息。程序如下：USE STUDENTGOExec sp_help t_studentGO删除STUDENT数据库中的表t_student3和t_student4。表5-2-2 T_STUDENT表中的数据（1）查询表中的所有记录程序如下：SELECT * FROM T_STUDENT（2）查询前3条记录的S_NUMBER、S_NAME和BIRTHDAY字段程序如下：SELECT TOP 3 S_NUMBER,S_NAME,BIRTHDAY FROM T_STUDENT（3）查询

13、所有记录的SEX字段，并去掉重复值程序如下：SELECT DISTINCT SEX FROM T_STUDENT（4）查询所有记录的S_NUMBER（别名为学号）、S_NAME（别名为姓名）和BIRTHDAY（别名为出生日期）字段程序如下：SELECT 学号=S_NUMBER,S_NAME AS 姓名,BIRTHDAY 出生日期 FROM T_STUDENT说明：在上例中使用了别名的三种定义方法，分别为： 列别名=列名 列名 AS 列别名 列名 列别名注意：列别名的使用范围：列别名只在定义的语句中有效。（5）查询得到每个学生的年龄程序如下：SELECT S_NAME,YEAR(GETDATE(

14、)-YEAR(BIRTHDAY) AS 年龄 FROM T_STUDENT说明：上面的SELECT语句中使用到了两个系统函数，一个是YEAR（）函数，它完成的功能是求一个日期型数据的年份，另一个是GETDATE（）函数，它完成的功能是获取当前的系统日期。（6）统计男同学的人数程序如下：SELECT count(*) FROM T_STUDENT WHERE SEX=男使用INTO子句创建一个新表（T_STUDENT1）。程序如下：USE STUDENTGOSELECT TOP 20 PERCENT S_NUMBER,S_NAME,POLITY INTO T_STUDENT1 FROM T_ST

15、UDENTSELECT * FROM T_STUDENT1（1）查询表T_SCORE和表T_STUDENT中的数据，满足条件：T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER程序如下：USE STUDENTgoSELECT T_STUDENT.S_NUMBER,S_NAME,C_NUMBER,SCORE from T_STUDENT,T_SCOREwhere T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER说明：在进行多表查询时，为了明确指定表中的列，可以使用如下格式：表名.列名当两个或多个数据表中有相同名称的字段时，必须要在字段的前面加上“表名.”

16、作为此字段的前缀，否则由于系统不清楚应该使用哪个数据表中的同名字段，因此无法执行此查询，会提示错误信息。（2）上述查询也可以用以下方法实现：联合查询表T_SCORE和表T_STUDENT中的数据。use STUDENTgoselect T_STUDENT.S_NUMBER,S_NAME,C_NUMBER,SCORE from T_STUDENT inner join T_SCORE on T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER从表T_SCORE中查询不及格学生的信息。程序如下：USE STUDENT GOSELECT * FROM T_SCORE WHERE S

17、CORE=2在表T_SCORE中查询选修了“10010218”课程的学生成绩，并按成绩的降序进行排序。：USE STUDENT GOSELECT * FROM T_SCORE WHERE C_NUMBER=10010218ORDER BY SCORE DESC假设表T_STUDENT1中的数据如表5-2-4所示。对表T_STUDENT1和表T_STUDENT进行联合查询。程序如下：USE STUDENT GOSELECT * FROM T_STUDENT1UNION SELECT * FROM T_STUDENT查询选修了两门及以上课程的学生的基本信息。程序如下：USE STUDENT GOS

18、ELECT * FROM T_STUDENT WHERE S_NUMBER=ANY (SELECT S_NUMBER FROM T_SCOREGROUP BY S_NUMBERHAVING COUNT(C_NUMBER)=2)利用INSERT语句向表T_STUDENT中插入一行数据，只包含S_NUMBER、S_NAME和BIRTHDAY三列。程序清单如下：USE STUDENTGOINSERT INTO T_STUDENT(S_NUMBER,S_NAME,BIRTHDAY)VALUES(B0451111,张宇天,85-10-20)利用INSERT语句向表T_STUDENT中插入一行数据，所有的

19、字段都要给出相应的值。程序清单如下：USE STUDENTGOINSERT T_STUDENT VALUES(B0451112,赵源,男,84-5-16,团员)说明：如果向一个表中的所有字段都插入数据值，则既可以列出所有字段的名称，也可以省略不写，如上题所示，此时要求给出的值的顺序要与数据表的结构相对应。利用INSERT语句向表T_STUDENT中插入一批数据，数据来源于另一个已有的数据表。程序清单如下：USE STUDENTGOINSERT INTO T_STUDENT(S_NUMBER,S_NAME,SEX,BIRTHDAY,POLITY)SELECT S_NUMBER,S_NAME,SE

20、X,BIRTHDAY,POLITY FROM T_STUDENT1一个带有WHERE条件的修改语句。程序清单如下：USE STUDENTGOUPDATE T_STUDENTSET S_NUMBER = G9823117, POLITY = 党员 WHERE S_NAME = 张宇天一个简单的修改语句。程序清单如下：USE STUDENTGOUPDATE T_STUDENT1SET POLITY = 党员说明：如果没有WHERE子句，则UPDATE将会修改表中的每一行数据。一个简单的删除语句。程序如下：USE STUDENTGODELETE FROM T_STUDENT1WHERE S_NAME

21、 = 王华一个没有WHERE条件的删除语句。程序如下：USE STUDENTGODELETE FROM T_STUDENT1说明：当不指定WHERE子句时，将删除表中的所有行的数据。要清除表中的所有数据，只留下数据表的定义还可以使用TRUNCATE语句。与DELETE语句相比，通常TRUNCATE执行的速度快，因为TRUNCATE是不记录日志的删除表中全部数据的操作。使用TRUNCATE语句清空表T_STUDENT1中的数据。USE STUDENTGOTRUNCATE TABLE T_STUDENT1使用GO语句的例子。程序清单如下：-第一个批处理完成打开STUDENT数据库的操作USE ST

22、UDENTGO/* GO是批处理结束标志*/-第二个批处理查询T_STUDENT表中的数据SELECT * FROM T_STUDENTGO-第三个批处理查询T_STUDENT表中-姓张的男学生的学号、姓名和性别SELECT S_NUMBER, S_NAME, SEXFROM T_STUDENTWHERE S_NAME like 张%AND SEX=男GO注意：GO语句本身并不是Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端命令。在程序中使用注释的例子。程序清单如下：-本程序是一个使用注释的例子。USE STUDENT -打开STUDENT数据库GO/* 下面的SQ

23、L语句完成在T_STUDENT表中查询05541班学生的学号、姓名和出生日期三个字段的记录，要求按姓名的降序排序*/SELECT S_NUMBER,S_NAME,BIRTHDAY FROM T_STUDENTWHERE SUBSTRING(T_STUDENT.S_NUMBER,1,5)=05541ORDER BY S_NAME DESC GO/* 下面的SQL语句完成在T_STUDENT表中查询B04511班性别为男的学生记录*/SELECT * FROM T_STUDENTWHERE SUBSTRING(T_STUDENT.S_NUMBER,1,6)=B04511AND SEX=男GO-下面

24、的SQL语句完成在T_COURSE表中-插入一条新的记录INSERT INTO T_COURSE /* 此表共有四个字段：C_NUMBER,C_NAME,HOURS,CREDIT */(C_NUMBER,C_NAME,HOURS,CREDIT) VALUES(20050418,Pascal程序设计,72,3)GO- 修改上面插入的记录UPDATE T_COURSESET HOURS=64,Credit=2.5Where C_NUMBER=20050418创建了一个变量CurrentDateTime，然后将GETDATE()函数的值放在变量中，最后输出CurrentDateTime变量的值。程序

25、清单如下：-声明变量CurrentDateTimeDECLARE CurrentDateTime char(30)-给变量CurrentDateTime赋值SELECT CurrentDateTime = GETDATE()-显示变量CurrentDateTime的值SELECT CurrentDateTime AS 当前的日期和时间GO注意：变量只在定义它的批处理中有效，因此，在上例中的程序中间不能写入GO语句。查询T_STUDENT表，将返回的记录数赋给变量RowsReturn。程序清单如下：-打开STUDENT数据库USE STUDENTGO-声明变量DECLARE RowsReturn

26、 int-给变量赋值SET RowsReturn = (SELECT COUNT(*) FROM T_STUDENT)-显示变量的值SELECT RowsReturn AS SELECT 返回的记录数GO在SELECT语句中使用由SET赋值的变量。程序清单如下：-打开STUDENT数据库USE STUDENTGO-声明变量DECLARE StuSex char(2)-给变量赋值SET StuSex =女-根据变量StuSex的值进行查询SELECT S_NUMBER,S_NAME,SEX,BIRTHDAYFROM T_STUDENTWHERE SEX = StuSexGO在T_SCORE表中，

27、求05541班学生高等数学课程的最高分和最低分的学生信息，包括学号、姓名、课程名、成绩四个字段。-打开STUDENT数据库USE STUDENTGO-声明变量DECLARE MaxScore real,MinScore real-给变量赋值SELECT MaxScore = MAX(SCORE), MinScore = MIN(SCORE)FROM T_SCORE,T_COURSE WHERE T_SCORE.C_NUMBER=T_COURSE.C_NUMBERAND SUBSTRING(T_SCORE.S_NUMBER,1,5)=05541 AND T_COURSE.C_NAME=高等数学-

28、根据变量MaxScore和MinScore的值进行查询，-查询学生的学号、姓名、课程名称、考试分数SELECT T_SCORE.S_NUMBER,S_NAME,C_NAME,SCOREFROM T_STUDENT,T_COURSE,T_SCOREWHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBERAND T_SCORE.C_NUMBER=T_COURSE.C_NUMBERAND (SCORE=MaxScore ORSCORE=MinScore )GO计算表达式的值，并将结果赋给变量ExpResult。程序清单如下：-定义变量DECLARE ExpResult numeric-给变量赋值SET ExpResult=67%31-显示变量的值SELECT ExpResult AS 表达式计算结果查询05541班的学生信息，要