数据库终极版.docx

数据库终极版.docx

《数据库终极版.docx》由会员分享，可在线阅读，更多相关《数据库终极版.docx（37页珍藏版）》请在冰点文库上搜索。

数据库终极版

创建一个名称为STUDENT3的数据库，该数据库的主文件逻辑名称为STUDENT3_data，物理文件名为STUDENT3.mdf，初始大小为3MB，最大尺寸为无限大，增长速度为15%；数据库的日志文件逻辑名称为STUDENT3_log，物理文件名为STUDENT3.ldf，初始大小为2MB，最大尺寸为50MB，增长速度为1MB；要求数据库文件和日志文件的物理文件都存放在E盘的DATA文件夹下。

实现的步骤如下：

（1）在E盘创建一个新的文件夹，名称是“DATA”。

（2）在SQLServerManagementStudio中新建一个查询页面。

（3）输入以下程序段并执行此查询：

createdatabaseSTUDENT3

onprimary

（name=STUDENT3_data,

filename='E:

\DATA\STUDENT3.mdf',

size=3,

maxsize=unlimited,

filegrowth=15%）

logon

（name=STUDENT3_log,

filename='E:

\DATA\STUDENT3.ldf',

size=2,

maxsize=50,

filegrowth=1）

创建一个指定多个数据文件和日志文件的数据库。

该数据库名称为STUDENTS，有1个5MB和1个10MB的数据文件和2个5MB的事务日志文件。

数据文件逻辑名称为STUDENTS1和STUDENTS2，物理文件名为STUDENTS1.mdf和STUDENTS2.ndf。

主文件是STUDENTS1，由PRIMARY指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。

事务日志文件的逻辑名为STUDENTSLOG1和STUDENTSLOG2，物理文件名为STUDENTSLOG1.ldf和STUDENTSLOG2.ldf，最大尺寸均为50MB，文件增长速度为1MB。

要求数据库文件和日志文件的物理文件都存放在E盘的DATA文件夹下。

实现的步骤如下：

（1）在E盘创建一个新的文件夹，名称是“DATA”。

（2）在SQLServerManagementStudio中新建一个查询页面。

（3）输入以下程序段并执行此查询：

createdatabaseSTUDENTS

onprimary

（name=STUDENTS1,

filename='E:

\DATA\STUDENTS1.mdf',

size=5,

maxsize=unlimited,

filegrowth=10%）,

（name=STUDENTS12,

filename='E:

\DATA\STUDENTS2.ndf',

size=10,

maxsize=100,

filegrowth=1）

logon

（name=STUDENTSLOG1,

filename='E:

\DATA\STUDENTSLOG1.ldf',

size=5,

maxsize=50,

filegrowth=1）,

（name=STUDENTSLOG2,

filename='E:

\DATA\STUDENTSLOG2.ldf',

size=5,

maxsize=50,

filegrowth=1）

删除已创建的数据库STUDENTS

实现的步骤如下：

（1）在SQLServerManagementStudio中新建一个查询页面。

（2）输入以下程序段并执行此查询：

dropdatabaseSTUDENTS说明：

当有别的用户正在使用此数据库时，则不能进行删除操作。

表4-2-1学生基本信息表（名为t_student）组成

实现的步骤如下：

（1）在SQLServerManagementStudio中新建一个查询页面。

（2）输入以下程序段并执行此查询：

USESTUDENT

go

Createtablet_student

（

s_numberchar（10）notnull,

s_namechar（8）,

sexchar

（2）default'男',

birthdaydatetime,

politychar（4）

）

go

如果系统的输出结果为：

“命令已成功完成。

”，则表明t_student数据表已经创建成功了。

为t_student2表中的s_number字段创建非空约束。

程序清单如下：

USESTUDENT

GO

CREATETABLEt_student2（

s_numberchar（10）NOTNULL,

s_namechar（8）,

sexchar

（2）,

phonenumint

）

在数据库STUDENT中创建一个t_student3表，并定义s_number为主键。

增加主键后，就限定了该记录中不能插入编号相同的两个或者多个数据信息，从而保证了表中的记录信息是唯一的。

程序清单如下：

USESTUDENT

go

Createtablet_student3（

s_numberchar（10）primarykey,

s_namechar（8）,

sexchar

（2）,

birthdaydatetime,

politychar（4）

）

创建一个学生信息表t_student4，其中s_name字段具有唯一性。

程序清单如下：

USESTUDENT

GO

CREATETABLEt_student4

（

s_numberchar（10）PRIMARYKEY,

s_namechar（8）,

sexchar

（2）,

birthdaydatetime,

politychar（4）,

constraintuk_nameunique（s_name）

）

创建一个学生信息表t_student5，其中输入性别字段值时，只能接受“男”或者“女”，而不能接受其他数据。

程序清单如下：

USESTUDENT

GO

createtablet_student5（

s_numberchar（10）,

s_namechar（8）,

sexchar

（2）,

phonenumint,

constraintchk_sexcheck（sexin（'男','女'））

）

创建一个学生信息表t_student6，为sex字段创建默认约束“男”

程序清单如下：

USESTUDENT

GO

CREATETABLEt_student6（

s_numberchar（10）,

s_namechar（8）,

sexchar

（2）default'男',

phonenumint

）

创建t_score表，并为t_score创建外键约束，该约束把表t_score中的学生学号（s_number）字段和表t_student中的学生学号（s_number）字段关联起来，实现t_score中的学生学号（s_number）字段的取值要参照表t_student中的学生学号（s_number）字段的数据值。

程序清单如下：

USESTUDENT

GO

CREATETABLEt_score

（

s_numberchar（10）,

c_numberchar（10）,

scoreint,

constraintcon_s_number

foreignkey（s_number）

referencest_student（s_number）

）

GO

在数据库STUDENT中创建一个t_course1表，然后在表中增加一个c_pnumber字段，删除表中的credit字段，并且修改c_name字段的数据长度。

SQL语句的程序清单如下：

USESTUDENT

go

Createtablet_course1

（

c_numberchar（10）notnull,

c_namechar（30）,

hoursint,

creditreal

）

go

altertablet_course1altercolumnc_namechar（40）

altertablet_course1dropcolumncredit

altertablet_course1addc_pnumberchar（10）

go

（1）显示STUDENT数据库中所有对象的信息。

程序如下：

USESTUDENT

GO

Execsp_help

GO

（2）显示t_student数据表的信息。

程序如下：

USESTUDENT

GO

Execsp_helpt_student

GO

删除STUDENT数据库中的表t_student3和t_student4。

表5-2-2T_STUDENT表中的数据

（1）查询表中的所有记录

程序如下：

SELECT*FROMT_STUDENT

（2）查询前3条记录的S_NUMBER、S_NAME和BIRTHDAY字段

程序如下：

SELECTTOP3S_NUMBER,S_NAME,BIRTHDAYFROMT_STUDENT

（3）查询所有记录的SEX字段，并去掉重复值

程序如下：

SELECTDISTINCTSEXFROMT_STUDENT

（4）查询所有记录的S_NUMBER（别名为学号）、S_NAME（别名为姓名）和BIRTHDAY（别名为出生日期）字段

程序如下：

SELECT学号=S_NUMBER,S_NAMEAS姓名,BIRTHDAY出生日期FROMT_STUDENT

说明：

在上例中使用了别名的三种定义方法，分别为：

●列别名=列名

●列名AS列别名

●列名列别名

注意：

列别名的使用范围：

列别名只在定义的语句中有效。

（5）查询得到每个学生的年龄

程序如下：

SELECTS_NAME,YEAR（GETDATE（））-YEAR（BIRTHDAY）AS年龄FROMT_STUDENT说明：

上面的SELECT语句中使用到了两个系统函数，一个是YEAR（）函数，它完成的功能是求一个日期型数据的年份，另一个是GETDATE（）函数，它完成的功能是获取当前的系统日期。

（6）统计男同学的人数

程序如下：

SELECTcount（*）FROMT_STUDENTWHERESEX='男'

使用INTO子句创建一个新表（T_STUDENT1）。

程序如下：

USESTUDENT

GO

SELECTTOP20PERCENTS_NUMBER,S_NAME,POLITYINTOT_STUDENT1FROMT_STUDENT

SELECT*FROMT_STUDENT1

（1）查询表T_SCORE和表T_STUDENT中的数据，满足条件：

T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER

程序如下：

USESTUDENT

go

SELECTT_STUDENT.S_NUMBER,S_NAME,C_NUMBER,SCOREfromT_STUDENT,T_SCORE

whereT_STUDENT.S_NUMBER=T_SCORE.S_NUMBER

说明：

在进行多表查询时，为了明确指定表中的列，可以使用如下格式：

表名.列名当两个或多个数据表中有相同名称的字段时，必须要在字段的前面加上“表名.”作为此字段的前缀，否则由于系统不清楚应该使用哪个数据表中的同名字段，因此无法执行此查询，会提示错误信息。

（2）上述查询也可以用以下方法实现：

联合查询表T_SCORE和表T_STUDENT中的数据。

useSTUDENT

go

selectT_STUDENT.S_NUMBER,S_NAME,C_NUMBER,SCORE

fromT_STUDENTinnerjoinT_SCORE

onT_STUDENT.S_NUMBER=T_SCORE.S_NUMBER

从表T_SCORE中查询不及格学生的信息。

程序如下：

USESTUDENT

GO

SELECT*FROMT_SCOREWHERESCORE<60

从表T_STUDENT中查询是党员的男学生的信息。

程序如下：

USESTUDENT

GO

SELECT*FROMT_STUDENTWHEREPOLITY='党员'ANDSEX='男'

从表T_SCORE中查询成绩在80~90分的学生的信息。

程序如下：

USESTUDENT

GO

SELECT*FROMT_SCOREWHERESCOREBETWEEN80AND90

从表T_STUDENT中查询所有非党员的学生信息。

程序如下：

USESTUDENT

GO

SELECT*FROMT_STUDENTWHEREPOLITYin（'团员','群众'）

从表T_STUDENT中查询所有姓张的学生信息。

程序如下：

USESTUDENT

GO

SELECT*FROMT_STUDENTWHERES_NAMElike'张%'

对表T_STUDENT中的记录按性别进行分组，查询相应的人数。

程序如下：

USESTUDENT

GO

SELECTSEX,count（SEX）人数FROMT_STUDENTGROUPBYSEX

在表T_SCORE中求选修了两门及以上课程的学生学号。

USESTUDENT

GO

SELECTS_NUMBER,COUNT（C_NUMBER）选修课程数FROMT_SCORE

GROUPBYS_NUMBER

HAVINGCOUNT（C_NUMBER）>=2

在表T_SCORE中查询选修了“10010218”课程的学生成绩，并按成绩的降序进行排序。

：

USESTUDENT

GO

SELECT*FROMT_SCORE

WHEREC_NUMBER='10010218'

ORDERBYSCOREDESC

假设表T_STUDENT1中的数据如表5-2-4所示。

对表T_STUDENT1和表T_STUDENT进行联合查询。

程序如下：

USESTUDENT

GO

SELECT*FROMT_STUDENT1

UNION

SELECT*FROMT_STUDENT

查询选修了两门及以上课程的学生的基本信息。

程序如下：

USESTUDENT

GO

SELECT*FROMT_STUDENTWHERES_NUMBER=ANY

（SELECTS_NUMBERFROMT_SCORE

GROUPBYS_NUMBER

HAVINGCOUNT（C_NUMBER）>=2

）

利用INSERT语句向表T_STUDENT中插入一行数据，只包含S_NUMBER、S_NAME和BIRTHDAY三列。

程序清单如下：

USESTUDENT

GO

INSERTINTOT_STUDENT

（S_NUMBER,S_NAME,BIRTHDAY）

VALUES（'B0451111','张宇天','85-10-20'）

利用INSERT语句向表T_STUDENT中插入一行数据，所有的字段都要给出相应的值。

程序清单如下：

USESTUDENT

GO

INSERTT_STUDENT

VALUES（'B0451112','赵源','男','84-5-16','团员'）

说明：

如果向一个表中的所有字段都插入数据值，则既可以列出所有字段的名称，也可以省略不写，如上题所示，此时要求给出的值的顺序要与数据表的结构相对应。

利用INSERT语句向表T_STUDENT中插入一批数据，数据来源于另一个已有的数据表。

程序清单如下：

USESTUDENT

GO

INSERTINTOT_STUDENT

（S_NUMBER,S_NAME,SEX,BIRTHDAY,POLITY）

SELECTS_NUMBER,S_NAME,SEX,BIRTHDAY,POLITYFROMT_STUDENT1

一个带有WHERE条件的修改语句。

程序清单如下：

USESTUDENT

GO

UPDATET_STUDENT

SETS_NUMBER='G9823117',POLITY='党员'

WHERES_NAME='张宇天'

一个简单的修改语句。

程序清单如下：

USESTUDENT

GO

UPDATET_STUDENT1

SETPOLITY='党员'

说明：

如果没有WHERE子句，则UPDATE将会修改表中的每一行数据。

一个简单的删除语句。

程序如下：

USESTUDENT

GO

DELETEFROMT_STUDENT1

WHERES_NAME='王华'

一个没有WHERE条件的删除语句。

程序如下：

USESTUDENT

GO

DELETEFROMT_STUDENT1

说明：

当不指定WHERE子句时，将删除表中的所有行的数据。

要清除表中的所有数据，只留下数据表的定义还可以使用TRUNCATE语句。

与DELETE语句相比，通常TRUNCATE执行的速度快，因为TRUNCATE是不记录日志的删除表中全部数据的操作。

使用TRUNCATE语句清空表T_STUDENT1中的数据。

USESTUDENT

GO

TRUNCATETABLET_STUDENT1

使用GO语句的例子。

程序清单如下：

--第一个批处理完成打开STUDENT数据库的操作

USESTUDENT

GO

/*GO是批处理结束标志*/--第二个批处理查询T_STUDENT表中的数据

SELECT*FROMT_STUDENT

GO--第三个批处理查询T_STUDENT表中

--姓张的男学生的学号、姓名和性别

SELECTS_NUMBER,S_NAME,SEX

FROMT_STUDENT

WHERES_NAMElike'张%'

ANDSEX='男'

GO注意：

GO语句本身并不是Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端命令。

在程序中使用注释的例子。

程序清单如下：

--本程序是一个使用注释的例子。

USESTUDENT--打开STUDENT数据库

GO

/*下面的SQL语句完成在T_STUDENT表中查询

05541班学生的学号、姓名和出生日期三个字段

的记录，要求按姓名的降序排序*/

SELECTS_NUMBER,S_NAME,BIRTHDAY

FROMT_STUDENT

WHERESUBSTRING（T_STUDENT.S_NUMBER,1,5）='05541'

ORDERBYS_NAMEDESC

GO/*下面的SQL语句完成在T_STUDENT表中查询

B04511班性别为男的学生记录*/

SELECT*FROMT_STUDENT

WHERESUBSTRING（T_STUDENT.S_NUMBER,1,6）='B04511'

ANDSEX='男'

GO--下面的SQL语句完成在T_COURSE表中

--插入一条新的记录

INSERTINTOT_COURSE/*此表共有四个字段：

C_NUMBER,C_NAME,HOURS,CREDIT*/

（C_NUMBER,C_NAME,HOURS,CREDIT）

VALUES

（'20050418','Pascal程序设计',72,3）

GO--修改上面插入的记录

UPDATET_COURSE

SETHOURS=64,

Credit=2.5

WhereC_NUMBER='20050418'

创建了一个变量@CurrentDateTime，然后将GETDATE（）函数的值放在变量中，最后输出@CurrentDateTime变量的值。

程序清单如下：

--声明变量@CurrentDateTime

DECLARE@CurrentDateTimechar（30）

--给变量@CurrentDateTime赋值

SELECT@CurrentDateTime=GETDATE（）

--显示变量@CurrentDateTime的值

SELECT@CurrentDateTimeAS'当前的日期和时间'

GO注意：

变量只在定义它的批处理中有效，因此，在上例中的程序中间不能写入GO语句。

查询T_STUDENT表，将返回的记录数赋给变量@RowsReturn。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO--声明变量

DECLARE@RowsReturnint--给变量赋值

SET@RowsReturn=（SELECTCOUNT（*）FROMT_STUDENT）

--显示变量的值

SELECT@RowsReturnAS'SELECT返回的记录数'

GO

在SELECT语句中使用由SET赋值的变量。

程序清单如下：

--打开STUDENT数据库

USESTUDENT

GO--声明变量

DECLARE@StuSexchar

（2）--给变量赋值

SET@StuSex='女'--根据变量@StuSex的值进行查询

SELECTS_NUMBER,S_NAME,SEX,BIRTHDAY

FROMT_STUDENT

WHERESEX=@StuSex

GO

在T_SCORE表中，求05541班学生高等数学课程的最高分和最低分的学生信息，包括学号、姓名、课程名、成绩四个字段。

--打开

STUDENT数据库

USESTUDENT

GO--声明变量

DECLARE@MaxScorereal,@MinScorereal--给变量赋值

SELECT@MaxScore=MAX（SCORE）,

@MinScore=MIN（SCORE）

FROMT_SCORE,T_COURSE

WHERET_SCORE.C_NUMBER=T_COURSE.C_NUMBER

ANDSUBSTRING（T_SCORE.S_NUMBER,1,5）='05541'

ANDT_COURSE.C_NAME='高等数学'

--根据变量@MaxScore和@MinScore的值进行查询，

--查询学生的学号、姓名、课程名称、考试分数

SELECTT_SCORE.S_NUMBER,S_NAME,C_NAME,SCORE

FROMT_STUDENT,T_COURSE,T_SCORE

WHERET_STUDENT.S_NUMBER=T_SCORE.S_NUMBER

ANDT_SCORE.C_NUMBER=T_COURSE.C_NUMBER

AND（

SCORE=@MaxScore

OR

SCORE=@MinScore

）

GO

计算表达式的值，并将结果赋给变量@ExpResult。

程序清单如下：

--定义变量

DECLARE@ExpResultnumeric--给变量赋值

SET@ExpResult=67%31--显示变量的值

SELECT@ExpResultAS'表达式计算结果'

查询05541班的学生信息，要