《SQL Server 2005中文版基础教程》示例的T-SQL语句
SQL Server 2005中文版基础教程
示例的T-SQL语句
项目一创建与使用SQL Server实例
任务二启动和连接“教育学院管理数”实例
操作二在Windows的【命令提示符】中启动实例
语法一
net start “SQL Server (教育学院管理)”
语法二
net start MSSQL$教育学院管理
项目二管理数据库
任务一创建“学生管理数据库”
操作二用create database语句创建数据库
USE [master]
GO
create database [学生管理]
on primary
( name=N?Pri_Student_Data?,
filename=N?D:\DBFiles\Pri_Student_Data.mdf?,
size=10MB,
maxsize=unlimited,
filegrowth=5MB
)
Log on
( name=N?Student_log?,
filename=N?D:\DBFiles\Student_log.ldf?,
size=10MB,
maxsize=unlimited,
filegrowth=5MB
)
Collate Chinese_PRC_CI_AS
go
任务二修改数据库
操作五用alter database语句增加日志文件USE [学生管理]
go
alter database [学生管理]
Add log file
( name=?Snd_Student_Log?,
filename=?D:\DBFiles\Snd_Student_Log.ldf?,
size=10MB,
maxsize=unlimited,
filegrowth=5MB
)
Go
操作五用alter database语句修改排序规则USE [学生管理]
GO
ALTER DATABASE [学生管理]
COLLATE Latin1_General_BIN
GO
任务三删除数据库
操作二用drop database语句删除数据库USE [master]
GO
DROP DATABASE [学生管理]
GO
项目三管理数据表
任务一创建“学生表”
操作二用create table语句创建表
create table学生表
(
学生编号varchar(10) NOT NULL,
学生姓名char(10) NULL,
所属专业编号char(2) NULL,
所属班级编号char(3) NULL,
性别int NULL,
出生日期smalldatetime NULL,
籍贯varchar(20) NULL,
入学日期smalldatetime NULL
)
任务二修改表结构
操作二用alter table语句修改表结构
use [学生管理]
go
alter table学生表
alter column学生编号char(4) not null
go
alter table学生表
alter column学生姓名varhar(10) null
go
alter table学生表
alter column性别tinybit null
go
alter table学生表
add 职务varchar(10) null
go
任务三向“学生表”插入记录
操作二用insert语句插入记录
insert into学生表
(学生编号,学生姓名,所属专业编号,所属班级编号,性别,出生日期,籍贯,入学日期,职务) values
(…X001?,?宋小南?,?Z1?,?B01?,0,?1980-8-1?,?黑龙江省哈尔滨市?,?2001-9-1,?班长??)
go
任务四修改“学生表”中的记录
操作二用update语句修改记录
use [学生管理]
go
update学生表
set出生日期=’1983-5-6’,
籍贯=’黑龙江省齐齐哈尔市’
where学生编号=’X006’
go
任务五删除“学生表”记录
操作二用delete语句删除记录
use [学生管理]
go
delete from学生表
where学生编号=?X007?
go
任务六删除“学生表”
操作二用drop table语句删除表
use [学生管理]
go
drop table学生表
go
项目四设置主键、外键和索引任务一为“学生表”设置主键
操作三用create table语句设置主键
use [学生管理]
go
create table学生表
(
学生编号char(4) not null primary key,
学生姓名varchar(10),
所属专业编号char(2),
所属班级编号char(3),
性别tinybit,
出生日期smalldatetime,
籍贯varchar(20),
入学日期smalldatetime,
职务varchar(10)
)
go
操作四用alter table语句设置主键
alter table学生表
add primary key (学生编号)
go
操作五用alter table语句删除主键
alter table学生表
drop PK_学生表_15502E78
go
任务二为“学生成绩表”设置外键
操作二用create table语句设置外键
create table学生表
(
学生编号char(4) not null
constraint FK_学生成绩表_学生表
foreign key references 学生表(学生编号), 课程编号char(5) not null,
成绩tinyint null
)
go
操作三用alter table语句设置外键
alter table学生成绩表
add foreign key (学生编号) references学生表(学生编号)
go
操作五用alter table语句删除外键
alter table学生成绩表
drop FK_学生成绩表_学生表
go
任务三为“学生表”定义索引
操作二用create index语句创建索引
create unique index IX_选修课程表
on选修课程表(课程名称)
go
操作三用drop index语句删除索引
drop index IX_选修课程表
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学生表
where year(getdate())-year(出生日期) between 25 and 30 操作六用order by对查询结果排序
select学生姓名,
籍贯,
出生日期,
year(getdate())-year(出生日期) as年龄
from学生表
order by出生日期asc
任务二用SQL Server函数管理
操作一用distinct消除相同行
select distinct籍贯
from学生表
操作二用case…when…函数分类处理
select学生姓名,
籍贯,
性别,
case性别when 0 then …女?
when 1 then …男?
end as学生性别
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 学生表
order by ascii(学生姓名)
select max(学生姓名),
min(学生姓名)
from 学生表
select max(year(入学日期)-year(出生日期) as 最大入学年龄,
min(year(入学日期)-year(出生日期) as 最小入学年龄
from 学生表
go
select max(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?
任务二分组和筛选统计结果
操作一用group by子句对统计结果分组
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成绩表
group by课程编号
select 所属班级编号,
case when 性别=0 then …男?
when 性别=1 then …女?
end as 性别,
count(*) as 人数
from 学生表
group by 所属班级编号,
性别
操作二用having子句筛选分组统计结果select 课程编号,
sum(成绩) as 总成绩,
count(*) as 总人数,
avg(成绩) as 平均成绩
from 学生成绩表
where avg(成绩)>=80
group by 课程编号
order by 课程编号desc
select 课程编号,
sum(成绩) as 总成绩,
count(*) as 总人数,
avg(成绩) as 平均成绩
from 学生成绩表
group by 课程编号
having avg(成绩)>=80
order by avg(成绩) desc
select 课程编号,
sum(成绩) as 总成绩,
count(*) as 参与考试总人数,
avg(成绩) as 平均成绩
from 学生成绩表
where 课程编号=?K001? or 课程编号=?K002?
group by 课程编号
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?))
项目七多表联接查询管理教学计划任务一两个表的内联接查询
操作一“教师表”和“选修课程表”的内联接查询select K.课程编号,
K.课程名称,
J.教师编号,
J.教师姓名
from选修课程表K
inner join教师表J
on K.教师编号=J.教师编号
select K.课程名称,
K.课时,
J.教师编号,
J.教师姓名
Z.专业编号,
Z.专业名称,
from 选修课程表K
inner join教师表J
on K.授课教师编号=J.教师编号
inner join专业表Z
on J.所属专业编号=Z.专业编号
操作二“选修课程表”与“教师表”的自然联接select 学生编号,
学生姓名,
学生编号,
课程编号,
成绩
from 学生表X inner join 学生成绩表C
on X.学生编号=C.学生编号
select K.课程编号,
K.课程名称,
J.教师编号,
J.教师姓名
from 选修课程表K
inner join教师表J
on K.授课教师编号=J.教师编号
任务二两个表的外联接查询
操作一“教师表”与“课程表”的左联接查询select J.教师编号,
J.教师姓名,
isnull(K.课程名称,?无授课安排?) as授课安排from教师表J
left join 选修课程表K
on J.教师编号=K.授课教师编号
order by教师姓名
select J.教师编号,
J.教师姓名,
K.课程名称as 授课安排,
Z.专业名称as 教师所属专业
from 教师表J
left join 选修课程表K
on J.教师编号=K.授课教师编号
left join 专业表Z
on J.所属专业编号=Z.专业编号
where K.课程名称is not null
order by 教师名称
操作二“教师表”与“选修课程表”的右联接查询select isnull(J.教师姓名,?无授课教师?) as授课教师,
K.课程编号,
K.课程名称,
K.课时
from教师表J
right join课程表K
on J.教师编号=K.授课教师编号
操作三“教师表”与“课程表”的全联接查询select isnull(J.教师姓名,?无授课教师?) as授课教师,
isnull(K.课程名称,?无授课安排?) as授课安排,
isnull(K.课时,0) as 课时
from教师表J
full join课程表K
on J.教师编号=K.授课教师编号
操作四用交叉连接生成“学生考勤记录”
select X.学生编号,
X.学生姓名,
Q.考勤日期,
Q.签到时间,
Q.签退时间
from学生表X
cross join考勤表Q
order by X.学生姓名
select X.学生编号,
X.学生姓名,
Q.考勤日期,
Q.签到时间,
Q.签退时间
into 学生考勤表
from 学生表X
cross join 考勤表Q
order by X.学生姓名
任务三用子查询检查教学计划
操作一使用in的子查询
select教师编号,
教师姓名,
case when 性别=0 then …女?
when 性别=1 then …男?
end as 性别
from教师表
where rtrim(教师编号) not in (select distinct rtrim(教师编号)
from课程表
where教师编号is not null) 操作二使用exist的子查询
select 教师编号,
教师姓名,
case when 性别=0 then …女?
when 性别=1 then …男?
end as 性别,
year(getdate())-year(入职日期) as工龄
from教师表
where exists (select *
from选修课程表
where授课教师编号is null)
and教师编号not in (select distinct教师编号
from课程表
where授课教师编号is not null)
项目八创建和使用视图
任务一创建视图
操作三用create view语句创建“学籍管理”视图
use 学生管理
go
create view学籍管理
as
select所属班级编号as ClassNo,
学生姓名as StudentName,
职务as Duty
from学生表
go
操作一查询“住宿管理”视图
select StudentName as姓名,
NativePlace as籍贯,
case Sex when 1 then …男?
when 0 then …女?
end as性别
from住宿管理视图
where Birthday > …1980-1-1?
操作二用“住宿管理”视图对“学生表”添加记录
use 学生管理
go
insert into住宿管理
(StudentNo,StudentName,Sex,NativePlace,Birthday)
values
(…X020?,?吕子布?,1,?黑龙江省哈尔滨市?,?1982-6-6?)
go
insert into住宿管理视图
(StudentNo,StudentName,Sex,NativePlace,Birthday)
values
(…X021?,?边复哲?,1,?黑龙江省齐齐哈尔市?,?1983-7-7?) 操作三用“学籍管理”视图修改“学生表”的记录
use 学生管理
go
update学籍管理
set ClassNo=?B03?
where StudentName=?薛智?
and ClassNo=?B05?
go
操作四用“学籍管理”视图删除“学生表”的记录
use 学生管理
go
delete from学籍管理
where ClassNo=?B04?
and StudentName=?曹万里?
go
操作二用drop view语句删除“学籍管理”视图use 学生管理
go
drop view学籍管理视图
go
项目十备份和还原数据库
任务一备份“学生管理”数据库
操作二用sp_addumpdevice创建备份设备use master
go
exec sp_addumpdevice
…disk?,
…学生管理数据库备份设备?,
…D:\DB_Backup\学生管理数据库备份设备.bak?
操作四用backup database语句备份数据库use master
go
backup database 学生管理
file=?Pri_Student_Data?,
file=?Snd_Student_Data?,
filegroup=?PRIMARY?,
filegroup=?SecondFilesGroup?
to 学生理数据库备份设备
go
任务二还原“学生管理”数据库
操作二用restore database语句还原数据库use master
go
restore database 学生管理
from 学生管理数据库备份设备
with replace
go