《SQLServer中文版基础教程》示例的TSQL语句.docx

《SQLServer中文版基础教程》示例的TSQL语句.docx

《《SQLServer中文版基础教程》示例的TSQL语句.docx》由会员分享，可在线阅读，更多相关《《SQLServer中文版基础教程》示例的TSQL语句.docx（24页珍藏版）》请在冰豆网上搜索。

《SQLServer中文版基础教程》示例的TSQL语句

SQLServer2005中文版基础教程

示例的T-SQL语句

项目一创建与使用SQLServer实例

任务二启动和连接“教育学院管理数”实例

操作二在Windows的【命令提示符】中启动实例

语法一

netstart“SQLServer（教育学院管理）”

语法二

netstartMSSQL$教育学院管理

项目二管理数据库

任务一创建“学生管理数据库”

操作二用createdatabase语句创建数据库

USE[master]

GO

createdatabase[学生管理]

onprimary

（name=N’Pri_Student_Data’,

filename=N’D:

\DBFiles\Pri_Student_Data.mdf’,

size=10MB,

maxsize=unlimited,

filegrowth=5MB

）

Logon

（name=N’Student_log’,

filename=N’D:

\DBFiles\Student_log.ldf’,

size=10MB,

maxsize=unlimited,

filegrowth=5MB

）

CollateChinese_PRC_CI_AS

go

任务二修改数据库

操作五用alterdatabase语句增加日志文件

USE[学生管理]

go

alterdatabase[学生管理]

Addlogfile

（name=’Snd_Student_Log’,

filename=’D:

\DBFiles\Snd_Student_Log.ldf’,

size=10MB,

maxsize=unlimited,

filegrowth=5MB

）

Go

操作五用alterdatabase语句修改排序规则

USE[学生管理]

GO

ALTERDATABASE[学生管理]

COLLATELatin1_General_BIN

GO

任务三删除数据库

操作二用dropdatabase语句删除数据库

USE[master]

GO

DROPDATABASE[学生管理]

GO

项目三管理数据表

任务一创建“学生表”

操作二用createtable语句创建表

createtable学生表

（

学生编号varchar（10）NOTNULL,

学生姓名char（10）NULL,

所属专业编号char

（2）NULL,

所属班级编号char（3）NULL,

性别intNULL,

出生日期smalldatetimeNULL,

籍贯varchar（20）NULL,

入学日期smalldatetimeNULL

）

任务二修改表结构

操作二用altertable语句修改表结构

use[学生管理]

go

altertable学生表

altercolumn学生编号char（4）notnull

go

altertable学生表

altercolumn学生姓名varhar（10）null

go

altertable学生表

altercolumn性别tinyintnull

go

altertable学生表

add职务varchar（10）null

go

任务三向“学生表”插入记录

操作二用insert语句插入记录

insertinto学生表

（学生编号,学生姓名,所属专业编号,所属班级编号,性别,出生日期,籍贯,入学日期,职务）

values

（‘X001’,’宋小南’,’Z1’,’B01’,0,’1980-8-1’,’黑龙江省哈尔滨市’,’2001-9-1,’班长’）

go

任务四修改“学生表”中的记录

操作二用update语句修改记录

use[学生管理]

go

update学生表

set出生日期=’1983-5-6’,

籍贯=’黑龙江省齐齐哈尔市’

where学生编号=’X001’

go

任务五删除“学生表”记录

操作二用delete语句删除记录

use[学生管理]

go

deletefrom学生表

where学生编号=’X007’

go

任务六删除“学生表”

操作二用droptable语句删除表

use[学生管理]

go

droptable学生表

go

项目四设置主键、外键和索引

任务一为“学生表”设置主键

操作三用createtable语句设置主键

use[学生管理]

go

createtable学生表

（

学生编号char（4）notnullprimarykey,

学生姓名varchar（10）,

所属专业编号char

（2）,

所属班级编号char（3）,

性别tinybit,

出生日期smalldatetime,

籍贯varchar（20）,

入学日期smalldatetime,

职务varchar（10）

）

go

操作四用altertable语句设置主键

altertable学生表

addprimarykey（学生编号）

go

操作五用altertable语句删除主键

altertable学生表

dropPK_学生表_15502E78

go

任务二为“学生成绩表”设置外键

操作二用createtable语句设置外键

createtable学生表

（

学生编号char（4）notnull

constraintFK_学生成绩表_学生表

foreignkeyreferences学生表（学生编号）,

课程编号char（5）notnull,

成绩tinyintnull

）

go

操作三用altertable语句设置外键

altertable学生成绩表

addforeignkey（学生编号）references学生表（学生编号）

go

操作五用altertable语句删除外键

altertable学生成绩表

dropFK_学生成绩表_学生表

go

任务三为“学生表”定义索引

操作二用createindex语句创建索引

createuniqueindexIX_选修课程表

on选修课程表（课程名称）

go

操作三用dropindex语句删除索引

dropindexIX_选修课程表

go

项目五用对单一表查询实现学籍管理

任务一用简单查询显示学生信息

操作一用select子句查询指定列

select学生姓名,

职务,

入学日期

from学生表

操作二用表达式计算学生年龄

select学生姓名,

籍贯,

出生日期,

year（getdate（））-year（出生日期）

from学生表

操作三用where子句限制返回行

select学生姓名,

所属班级编号,

职务,

year（getdate（））-year（出生日期）as年龄

from学生表

where（year（getdate（））-year（出生日期））>=25

and（year（getdate（））-year（出生日期））<=30

操作四用like关键字实现模糊查询

select学生姓名,

籍贯,

出生日期

from学生表

where籍贯like‘黑龙江省%’

操作五用between…and…设置闭合区间

select学生姓名,

所属班级编号,

职务,

year（getdate（））-year（出生日期）as年龄

from学生表

whereyear（getdate（））-year（出生日期）between25and30

操作六用orderby对查询结果排序

select学生姓名,

籍贯,

出生日期,

year（getdate（））-year（出生日期）as年龄

from学生表

orderby出生日期asc

任务二用SQLServer函数管理

操作一用distinct消除相同行

selectdistinct籍贯

from学生表

操作二用case…when…函数分类处理

select学生姓名,

籍贯,

性别,

case性别when0then‘女’

when1then‘男’

endas学生性别

from学生表

操作三用convert函数转换数据类型

select学生姓名,

籍贯,

‘生日为‘+convert（varchar,出生日期,21）as出生日期

from学生表

操作四用substring函数截取字符串

select学生姓名,

籍贯,

‘生日为‘+substring（convert（varchar,出生日期,121）,1,10）as出生日期

from学生表

项目六用聚合函数统计成绩

任务一统计成绩

操作一用max和min查询最高成绩和最低成绩

select‘K001’as课程编号

max（成绩）as最高成绩,

min（成绩）as最低成绩

from成绩表

where选修课程编号=’K001’

select学生姓名,

ascii（学生姓名）as学生姓名的ASCII值

from学生表

orderbyascii（学生姓名）

selectmax（学生姓名）,

min（学生姓名）

from学生表

selectmax（year（入学日期）-year（出生日期）as最大入学年龄,

min（year（入学日期）-year（出生日期）as最小入学年龄

from学生表

go

selectmax（year（getdate（））-year（入职日期）as最长工龄,

min（year（getdate（））-year（入职日期）as最短工龄

from学生表

go

操作二用sum函数计算总成绩

select‘K001’as课程编号,

sum（成绩）as总成绩

from学生成绩表

where课程编号=’K001’

操作三用count函数计算参与考试的学生总数

select‘K005’as课程编号,

count（*）as参与考试总人数

from成绩表

where课程编号=’K005’

操作四用avg函数计算平均成绩

select‘K005’as课程编号,

sum（成绩）as总成绩,

count（*）as参与考试总人数,

sum（成绩）/count（*）as平均成绩

from成绩表

where课程编号=’K005’

select‘K005’as课程编号,

sum（成绩）as总成绩,

count（*）as参与考试总人数,

avg（成绩）as平均成绩

from成绩表

where课程编号=’K005’

任务二分组和筛选统计结果

操作一用groupby子句对统计结果分组

select'K001'as课程编号,

sum（成绩）as总成绩,

count（*）as参与考试总人数,

avg（成绩）as平均成绩

from学生成绩表

where课程编号='K001'

select'K002'as课程编号,

sum（成绩）as总成绩,

count（*）as参与考试总人数,

avg（成绩）as平均成绩

from学生成绩表

where课程编号='K002'

select课程编号,

sum（成绩）as总成绩,

count（*）as总人数,

avg（成绩）as平均成绩

from成绩表

groupby课程编号

select所属班级编号,

casewhen性别=0then‘男’

when性别=1then‘女’

endas性别,

count（*）as人数

from学生表

groupby所属班级编号,

性别

操作二用having子句筛选分组统计结果

select课程编号,

sum（成绩）as总成绩,

count（*）as总人数,

avg（成绩）as平均成绩

from学生成绩表

whereavg（成绩）>=80

groupby课程编号

orderby课程编号desc

select课程编号,

sum（成绩）as总成绩,

count（*）as总人数,

avg（成绩）as平均成绩

from学生成绩表

groupby课程编号

havingavg（成绩）>=80

orderbyavg（成绩）desc

select课程编号,

sum（成绩）as总成绩,

count（*）as参与考试总人数,

avg（成绩）as平均成绩

from学生成绩表

where课程编号=’K001’or课程编号=’K002’

groupby课程编号

select课程编号,

sum（成绩）as总成绩,

count（*）as参与考试总人数,

avg（成绩）as平均成绩

from学生成绩表

having课程编号=’K001’or课程编号=’K002’

任务三用子查询作成绩对比

操作一使用any的子查询

select课程编号,

学生编号,

成绩

from学生成绩表

where课程编号=’K001’

and学生编号in（’X001’,’X002’,’X003’）

and成绩>any（select成绩

from学生成绩表

where课程编号=’K001’

and学生编号in（’X009’,’X010’））

操作二使用all的子查询

select课程编号,

学生编号,

成绩

from学生成绩表

where课程编号=’K001’

and学生编号in（‘X001’,’X002’,’x003’）

and成绩>all（select成绩

from成绩表

where课程编号=’K001’

and学生编号in（’X009’,’X010’））

项目七多表联接查询管理教学计划

任务一两个表的内联接查询

操作一“教师表”和“选修课程表”的内联接查询

selectK.课程编号,

K.课程名称,

J.教师编号,

J.教师姓名

from选修课程表K

innerjoin教师表J

onK.教师编号=J.教师编号

selectK.课程名称,

K.课时,

J.教师编号,

J.教师姓名

Z.专业编号,

Z.专业名称,

from选修课程表K

innerjoin教师表J

onK.授课教师编号=J.教师编号

innerjoin专业表Z

onJ.所属专业编号=Z.专业编号

操作二“选修课程表”与“教师表”的自然联接

select学生编号,

学生姓名,

学生编号,

课程编号,

成绩

from学生表Xinnerjoin学生成绩表C

onX.学生编号=C.学生编号

selectK.课程编号,

K.课程名称,

J.教师编号,

J.教师姓名

from选修课程表K

innerjoin教师表J

onK.授课教师编号=J.教师编号

任务二两个表的外联接查询

操作一“教师表”与“课程表”的左联接查询

selectJ.教师编号,

J.教师姓名,

isnull（K.课程名称,’无授课安排’）as授课安排

from教师表J

leftjoin选修课程表K

onJ.教师编号=K.授课教师编号

orderby教师姓名

selectJ.教师编号,

J.教师姓名,

K.课程名称as授课安排,

Z.专业名称as教师所属专业

from教师表J

leftjoin选修课程表K

onJ.教师编号=K.授课教师编号

leftjoin专业表Z

onJ.所属专业编号=Z.专业编号

whereK.课程名称isnotnull

orderby教师名称

操作二“教师表”与“选修课程表”的右联接查询

selectisnull（J.教师姓名,’无授课教师’）as授课教师,

K.课程编号,

K.课程名称,

K.课时

from教师表J

rightjoin课程表K

onJ.教师编号=K.授课教师编号

操作三“教师表”与“课程表”的全联接查询

selectisnull（J.教师姓名,’无授课教师’）as授课教师,

isnull（K.课程名称,’无授课安排’）as授课安排,

isnull（K.课时,0）as课时

from教师表J

fulljoin课程表K

onJ.教师编号=K.授课教师编号

操作四用交叉连接生成“学生考勤记录”

selectX.学生编号,

X.学生姓名,

Q.考勤日期,

Q.签到时间,

Q.签退时间

from学生表X

crossjoin考勤表Q

orderbyX.学生姓名

selectX.学生编号,

X.学生姓名,

Q.考勤日期,

Q.签到时间,

Q.签退时间

into学生考勤表

from学生表X

crossjoin考勤表Q

orderbyX.学生姓名

任务三用子查询检查教学计划

操作一使用in的子查询

select教师编号,

教师姓名,

casewhen性别=0then‘女’

when性别=1then‘男’

endas性别

from教师表

wherertrim（教师编号）notin（selectdistinctrtrim（教师编号）

from课程表

where教师编号isnotnull）

操作二使用exist的子查询

select教师编号,

教师姓名,

casewhen性别=0then‘女’

when性别=1then‘男’

endas性别,

year（getdate（））-year（入职日期）as工龄

from教师表

whereexists（select*

from选修课程表

where授课教师编号isnull）

and教师编号notin（selectdistinct教师编号

from课程表

where授课教师编号isnotnull）

项目八创建和使用视图

任务一创建视图

操作三用createview语句创建“学籍管理”视图

use学生管理

go

createview学籍管理

as

select所属班级编号asClassNo,

学生姓名asStudentName,

职务asDuty

from学生表

go

任务二使用视图

操作一查询“住宿管理”视图

selectStudentNameas姓名,

NativePlaceas籍贯,

caseSexwhen1then‘男’

when0then‘女’

endas性别

from住宿管理视图

whereBirthday>‘1980-1-1’

操作二用“住宿管理”视图对“学生表”添加记录

use学生管理

go

insertinto住宿管理

（StudentNo,StudentName,Sex,NativePlace,Birthday）

values

（‘X020’,’吕子布’,1,’黑龙江省哈尔滨市’,’1982-6-6’）

go

insertinto住宿管理视图

（StudentNo,StudentName,Sex,NativePlace,Birthday）

values

（‘X021’,’边复哲’,1,’黑龙江省齐齐哈尔市’,’1983-7-7’）

操作三用“学籍管理”视图修改“学生表”的记录

use学生管理

go

update学籍管理

setClassNo=’B03’

whereStudentName=’薛智’

andClassNo=’B05’

go

操作四用“学籍管理”视图删除“学生表”的记录

use学生管理

go

deletefrom学籍管理

whereClassNo=’B04’

andStudentName=’曹万里’

go

任务三删除视图

操作二用dropview语句删除“学籍管理”视图

use学生管理

go

dropview学籍管理视图

go

项目十备份和还原数据库

任务一备份“学生管理”数据库

操作二用sp_addumpdevice创建备份设备

usemaster

go

execsp_addumpdevice

‘disk’,

‘学生管理数据库备份设备’,

‘D:

\DB_Backup\学生管理数据库备份设备.bak’

操作四用backupdatabase语句备份数据库

usemaster

go

backupdatabase学生管理

file=’Pri_Student_Data’,

file=’Snd_Student_Data’,

filegroup=’PRIMARY’,

filegroup=’SecondFilesGroup’

to学生理数据库备份设备

go

任务二还原“学生管理”数据库

操作二用restoredatabase语句还原数据库

usemaster

go

restoredatabase学生管理

from学生管理数据库备份设备

withreplace

go