数据库终极版.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班的学生信息,要