教学示例操作的TSQL语句.docx

教学示例操作的TSQL语句.docx

《教学示例操作的TSQL语句.docx》由会员分享，可在线阅读，更多相关《教学示例操作的TSQL语句.docx（20页珍藏版）》请在冰豆网上搜索。

教学示例操作的TSQL语句

教学示例操作的T-SQL语句

项目二创建和管理数据库

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

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

createdatabase教学管理数据库

onprimary

（name=Pri_教学管理1,

filename=’D:

\教学管理\Pri_教学管理1.mdf’,

size=10MB,

maxsize=60MB,

filegrowth=5MB

）,

filegroupUserFleGrp

（name=Snd_教学管理1,

filename=’d:

\教学管理\Snd_教学管理1.ndf’,

size=10MB,

maxsize=60MB,

filegrowth=5MB

）

logon

（name=LF_教学管理1,

filename=’d:

\教学管理\LF_教学管理1.ldf’,

size=10MB,

maxsize=60MB,

filegrowth=5MB

）

任务二修改数据库

操作二用alterdatabase语句修改数据库

（1）向主要文件组增加数据文件

alterdatabase教学管理数据库

Addfile

（name=Pri_教学管理2,

filename='d:

\教学管理\Pri_教学管理2.ndf',

size=10MB,

maxsize=60MB,

filegrowth=5MB

）

（2）向用户定义文件组增加数据文件

alterdatabase教学管理数据库

Addfile

（name=Snd_教学管理2,

filename='d:

\教学管理\Snd_教学管理2.ndf',

size=10MB,

maxsize=60MB,

filegrowth=5MB

）

tofilegroupUserFleGrp

（3）增加事务日志文件

alterdatabase教学管理数据库

Addlogfile

（name=LF_教学管理2,

filename='d:

\教学管理\LF_教学管理2.ldf',

size=10MB,

maxsize=60MB,

filegrowth=5MB

）

（4）修改排序规则

alterdatabase教学管理数据库

collateChinese_PRC_CI_AS

任务三删除数据库

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

dropdatabase教学管理数据库

项目三创建与管理表

任务一创建“学生表”

操作二用createtable语句创建表

createtable学生表

（

学生编号char（4）NOTNULL,

学生姓名varchar（10）,

所属专业编号char（3）,

所属班级编号char（3）,

职务varchar（6）,

性别int,

出生日期datetime,

籍贯varchar（50）,

入学日期datetime

）

任务二修改表结构

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

altertable学生表

altercolumn学生编号char（4）

altertable学生表

altercolumn学生编号char（4）

altertable学生表

altercolumn性别bit

altertable学生表

altercolumn籍贯varchar（50）

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

操作二用insert语句插入记录

insertinto学生表

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

values

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

insertinto学生表

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

values

（'X010','包海中','Z03','B05','班长',1,'1980-11-8','新疆乌鲁木齐市','2003-9-1'）

insertinto学生表

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

values

（'X011','郑贤淑','Z03','B05','学生',0,'1984-9-7','北京市','2003-9-1'）

insertinto学生表

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

values

（'X012','王忆浦','Z03','B06','班长',0,'1982-3-21','云南省昆明市','2004-9-1'）

insertinto学生表

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

values

（'X013','薛智','Z03','B06','学生',1,'1976-1-1','上海市','2004-9-1'）

--向“学生表”插入记录

go

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

操作二用update语句修改记录

update学生表

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

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

where学生编号=’X006’

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

操作二用delete语句删除记录

deletefrom学生表

where学生编号=’X009’

任务六删除表

操作二用droptable语句删除表

droptable学生表

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

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

操作三在createtable语句中设置主键

createtable学生表

（

学生编号char（4）primarykey,

学生姓名varchar（10）,

所属专业编号char（3）,

所属班级编号char（3）,

职务varchar（6）,

性别bit,

出生日期datetime,

籍贯varchar（50）,

入学日期datetime

）

操作四在altertable语句中设置主键

altertable学生表

addprimarykey（学生编号）

操作五在altertable语句中移除主键

altertable学生表

dropPK_学生表

任务二定义“学生表”与“专业表”的关系

操作二在createtable语句中定义关系

createtable专业表

（

专业编号char（3）primarykey,

专业名称varchar（30）

）

createtable学生表

（

学生编号char（4）primarykey,

学生姓名varchar（10）,

所属专业编号char（3）constraintFK_学生表_专业表foreignkeyreferences专业表（专业编号）,

所属班级编号char（3）,

职务varchar（6）,

性别bit,

出生日期datetime,

籍贯varchar（50）,

入学日期datetime

）

go

操作三在altertable语句中定义关系

altertable学生表

addforeignkey（所属专业编号）references专业表（专业编号）

操作五在altertable语句中删除关系

altertable学生表

dropFK_学生表_专业表

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

操作二用createindex语句定义索引

createuniqueindexIX_课程表

on课程表（课程名称）

操作三用dropindex语句删除索引

dropindexIX_课程表

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

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

操作一显示“学生姓名”、“职务”和“入学日期”等指定列

select学生姓名,

职务,

入学日期

from学生表

操作二使用表达式对列计算并显示学生的年龄

select学生姓名,

籍贯,

出生日期,

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

from学生表

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

select学生姓名,

所属班级编号,

职务,

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

from学生表

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

操作四使用like的模糊查询显示籍贯为黑龙江省的学生记录

select学生姓名,

籍贯,

出生日期

from学生表

where籍贯like‘黑龙江省%’

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

select学生姓名,

籍贯,

出生日期,

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

from学生表

orderby出生日期asc

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

select学生姓名,

籍贯,

出生日期,

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

from学生表

orderby出生日期asc

任务二使用函数管理学籍

操作一用distinct消除相同行

selectdistinct籍贯

from学生表

orderby籍贯

操作二用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查询最高成绩和最低成绩

selectmax（成绩）as最高成绩,

min（成绩）as最低成绩

from成绩表

where选修课程编号='K01'

操作二用sum计算总成绩

selectsum（成绩）as总成绩

from成绩表

where选修课程编号='K01'

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

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

from成绩表

where班级编号='B01'

and选修课程编号='K01'

操作四用avg计算平均成绩

selectsum（成绩）as总成绩,

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

avg（成绩）as平均成绩

from成绩表

where班级编号='B01'

and选修课程编号='K01'

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

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

select班级编号,

sum（成绩）as总成绩,

count（*）as总人数,

avg（成绩）as平均成绩

from成绩表

where选修课程编号='K01'

groupby班级编号

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

select班级编号,

选修课程编号,

sum（成绩）as总成绩,

count（*）as总人数,

avg（成绩）as平均成绩

from成绩表

groupby班级编号,选修课程编号

havingavg（成绩）>=80

orderby班级编号asc,

选修课程编号desc

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

操作一使用any的子查询

select*

from成绩表

where班级编号='B02'

and选修课程编号='K01'

and成绩>any（select成绩

from成绩表

where班级编号='B01'

and选修课程编号='K01'）

操作二使用all的子查询

select*

from成绩表

where班级编号='B02'

and选修课程编号='K01'

and成绩>all（select成绩

from成绩表

where班级编号='B01'

and选修课程编号='K01'）

项目七创建和使用视图

任务一创建视图

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

createview学籍管理视图

as

select所属班级编号asClassNo,

学生姓名asStudentName,

职务asDuty

from学生表

任务二使用视图

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

selectStudentNameas姓名,

NativePlaceas籍贯,

caseSexwhen1then'男'

when0then'女'

endas性别

from住宿管理视图

whereBirthday>'1980-1-1'

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

insertinto住宿管理视图

（StudentNo,StudentName,Sex,NativePlace,Birthday）

values

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

insertinto住宿管理视图

（StudentNo,StudentName,Sex,NativePlace,Birthday）

values

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

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

update学籍管理视图

setClassNo='B03'

whereStudentName='郑贤淑'

andClassNo='B05'

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

deletefrom学籍管理视图

whereClassNo='B06'

andStudentName='薛智'

任务三删除视图

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

dropview学籍管理视图

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

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

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

selectA.课程名称,

A.课时,

A.教师编号,

B.教师姓名as授课教师姓名

from课程表A

innerjoin教师表B

onA.教师编号=B.教师编号

selectA.课程名称,

A.课时,

A.教师编号,

B.教师姓名as授课教师姓名

from课程表A,

教师表B

whereA.教师编号=B.教师编号

selectA.课程名称,

A.课时,

A.所属专业编号,

C.专业名称,

A.教师编号,

B.教师姓名as授课教师姓名

from课程表A

innerjoin教师表B

onA.教师编号=B.教师编号

innerjoin专业表C

onA.所属专业编号=C.专业编号

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

select课程名称,

课时,

所属专业编号,

专业名称,

教师姓名as授课教师姓名

from课程表A

innerjoin教师表B

onA.教师编号=B.教师编号

innerjoin专业表C

onA.所属专业编号=C.专业编号

操作三对“课程表”的自联接

selectA.课程编号as课程编号A,

A.课程名称as课程名称A,

A.教师编号as教师编号A,

B.课程编号as课程编号B,

B.课程名称as课程名称B,

B.教师编号as教师编号B

from课程表A

innerjoin课程表B

onA.教师编号=B.教师编号

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

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

selectA.教师编号,

A.教师姓名,

A.职务,

isnull（B.课程名称,'无授课安排'）as授课安排

from教师表A

leftjoin课程表B

onA.教师编号=B.教师编号

orderby教师姓名

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

selectisnull（A.教师姓名,'无授课教师'）as授课教师,

B.课程编号,

B.课程名称,

B.课时

from教师表A

rightjoin课程表B

onA.教师编号=B.教师编号

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

selectisnull（A.教师姓名,'无授课教师'）as授课教师,

isnull（B.课程名称,'无授课安排'）as授课安排

from教师表A

fulljoin课程表B

onA.教师编号=B.教师编号

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

selectA.学生编号,

A.学生姓名,

B.考勤日期,

B.上学时间,

B.放学时间

from学生表A

crossjoin考勤表B

orderbyA.学生姓名

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

操作一使用in的子查询

select教师编号,

教师姓名,

职务

from教师表

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

from课程表

where教师编号isnotnull）

操作二使用exist的子查询

select教师姓名,

职务,

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

from教师表

whereexists（select*

from课程表

where教师编号isnull）

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

from课程表

where教师编号isnotnull）

项目九备份和还原数据库

任务一创建“教学管理数据库备份设备”

操作二用sp_addumpdevice创建备份设备

execsp_addumpdevice

'disk',

'教学管理数据库备份设备',

'D:

\备份\教学管理数据库备份设备.bak'

任务二备份“教学管理数据库”

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

backupdatabase教学管理数据库

file='Pri_教学管理1',

file='Snd_教学管理1',

filegroup='PRIMARY',

filegroup='UserFleGrp'

to教学管理数据库备份设备

任务三还原“教学管理数据库”

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

restoredatabase教学管理数据库

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

restoredatabase教学管理数据库

file=‘Pri_教学管理1’,

file=‘Snd_教学管理1’

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

restoredatabase教学管理数据库

filegroup=‘PRIMARY’,

filegroup=‘UserFleGrp’

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

restorelog教学管理数据库

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