《SQL_Server_2005中文版基础教程》示例的T-SQL语句

1、SQL Server 2005中文版基础教程示例的T-SQL语句项目一 创建与使用SQL Server实例任务二 启动和连接“教育学院管理数”实例操作二在Windows的【命令提示符】中启动实例语法一net start “SQL Server (教育学院管理)”语法二net start MSSQL$教育学院管理项目二 管理数据库任务一 创建“学生管理数据库”操作二 用create database语句创建数据库USE masterGOcreate database 学生管理on primary( name=NPri_Student_Data,filename=ND:DBFilesPri_Stu

2、dent_Data.mdf,size=10MB,maxsize=unlimited,filegrowth=5MB)Log on( name=NStudent_log,filename=ND:DBFilesStudent_log.ldf,size=10MB,maxsize=unlimited,filegrowth=5MB)Collate Chinese_PRC_CI_ASgo任务二 修改数据库操作五 用alter database语句增加日志文件USE 学生管理go alter database 学生管理Add log file( name=Snd_Student_Log, filename=D

3、:DBFilesSnd_Student_Log.ldf,size=10MB,maxsize=unlimited,filegrowth=5MB)Go操作五 用alter database语句修改排序规则USE 学生管理GOALTER DATABASE 学生管理COLLATE Latin1_General_BINGO任务三 删除数据库操作二 用drop database语句删除数据库USE masterGODROP DATABASE 学生管理GO项目三 管理数据表任务一 创建“学生表”操作二 用create table语句创建表create table学生表(学生编号 varchar(10) NO

4、T NULL,学生姓名 char(10) NULL,所属专业编号char(2) NULL,所属班级编号char(3) NULL,性别 int NULL,出生日期 smalldatetime NULL,籍贯 varchar(20) NULL,入学日期 smalldatetime NULL)任务二 修改表结构操作二 用alter table语句修改表结构use 学生管理goalter table学生表alter column学生编号char(4) not nullgoalter table学生表alter column学生姓名varhar(10) nullgo alter table学生表alte

5、r column性别tinyint null goalter table学生表add 职务varchar(10) nullgo任务三 向“学生表”插入记录操作二 用insert语句插入记录insert into学生表(学生编号,学生姓名,所属专业编号,所属班级编号,性别,出生日期,籍贯,入学日期,职务)values (X001,宋小南,Z1,B01,0,1980-8-1,黑龙江省哈尔滨市,2001-9-1,班长)go任务四 修改“学生表”中的记录操作二 用update语句修改记录use 学生管理goupdate学生表set出生日期=1983-5-6,籍贯=黑龙江省齐齐哈尔市where学生编号=

6、X001go任务五 删除“学生表”记录操作二 用delete语句删除记录use 学生管理godelete from学生表where学生编号=X007go任务六 删除“学生表”操作二 用drop table语句删除表use 学生管理godrop table学生表go项目四 设置主键、外键和索引任务一 为“学生表”设置主键操作三 用create table语句设置主键use 学生管理gocreate table学生表( 学生编号 char(4) not null primary key, 学生姓名 varchar(10),所属专业编号char(2),所属班级编号char(3), 性别 tinybi

7、t, 出生日期 smalldatetime, 籍贯 varchar(20),入学日期 smalldatetime,职务 varchar(10)go操作四 用alter table语句设置主键alter table学生表add primary key (学生编号)go操作五 用alter table语句删除主键alter table学生表drop PK_学生表_15502E78go任务二 为“学生成绩表”设置外键操作二 用create table语句设置外键create table学生表(学生编号 char(4) not nullconstraint FK_学生成绩表_学生表foreign ke

8、y 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语

9、句删除索引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()

10、-year(出生日期)<=30操作四 用like关键字实现模糊查询select学生姓名,籍贯,出生日期from学生表where籍贯like 黑龙江省%操作五 用betweenand设置闭合区间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任务二

11、 用SQL Server函数管理操作一 用distinct消除相同行select distinct籍贯from学生表操作二 用casewhen函数分类处理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

12、) as出生日期from学生表项目六 用聚合函数统计成绩任务一 统计成绩操作一 用max和min查询最高成绩和最低成绩select K001 as 课程编号max(成绩) as最高成绩, min(成绩) as最低成绩from成绩表where选修课程编号=K001select 学生姓名,ascii(学生姓名) as 学生姓名的ASCII值from 学生表order by ascii(学生姓名)select max(学生姓名),min(学生姓名)from 学生表select max(year(入学日期)-year(出生日期) as 最大入学年龄, min(year(入学日期)-year(出生日期)

13、 as 最小入学年龄from 学生表goselect 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 课程编

14、号,sum(成绩) as总成绩,count(*) as参与考试总人数,sum(成绩)/ count(*) as平均成绩from成绩表where课程编号=K005select K005 as 课程编号,sum(成绩) as总成绩,count(*) as参与考试总人数,avg(成绩) as平均成绩from成绩表where课程编号=K005任务二 分组和筛选统计结果操作一 用group by子句对统计结果分组select 'K001' as 课程编号, sum(成绩) as 总成绩, count(*) as 参与考试总人数, avg(成绩) as 平均成绩from 学生成绩表wher

15、e 课程编号='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 学生表g

16、roup by 所属班级编号, 性别操作二 用having子句筛选分组统计结果select 课程编号, sum(成绩) as 总成绩, count(*) as 总人数, avg(成绩) as 平均成绩from 学生成绩表where avg(成绩)>=80group by 课程编号order by 课程编号 descselect 课程编号, sum(成绩) as 总成绩, count(*) as 总人数, avg(成绩) as 平均成绩from 学生成绩表group by 课程编号having avg(成绩)>=80order by avg(成绩) descselect 课程编号,

17、sum(成绩) as 总成绩, count(*) as 参与考试总人数, avg(成绩) as 平均成绩from 学生成绩表where 课程编号=K001 or 课程编号=K002group by 课程编号select 课程编号, sum(成绩) as 总成绩, count(*) as 参与考试总人数, avg(成绩) as 平均成绩from 学生成绩表having 课程编号=K001 or 课程编号=K002任务三 用子查询作成绩对比操作一 使用any的子查询select 课程编号, 学生编号, 成绩from学生成绩表where课程编号=K001and学生编号 in (X001,X002,X

18、003)and成绩>any(select成绩from学生成绩表where课程编号=K001and学生编号in (X009,X010)操作二 使用all的子查询select 课程编号, 学生编号, 成绩from学生成绩表where课程编号=K001and学生编号 in (X001,X002,x003)and成绩>all(select成绩from成绩表where课程编号=K001and学生编号 in (X009,X010)项目七 多表联接查询管理教学计划任务一 两个表的内联接查询操作一 “教师表”和“选修课程表”的内联接查询select K.课程编号,K.课程名称,J.教师编号,J.教

19、师姓名from选修课程表 Kinner join教师表 Jon K.教师编号=J.教师编号select K.课程名称,K.课时,J.教师编号,J.教师姓名Z.专业编号,Z.专业名称,from 选修课程表 Kinner join教师表 Jon K.授课教师编号=J.教师编号inner join专业表 Zon J.所属专业编号=Z.专业编号操作二 “选修课程表”与“教师表”的自然联接select 学生编号, 学生姓名, 学生编号, 课程编号, 成绩from 学生表X inner join 学生成绩表C on X.学生编号=C.学生编号select K.课程编号,K.课程名称,J.教师编号,J.教师

20、姓名from 选修课程表 Kinner join教师表 Jon K.授课教师编号=J.教师编号 任务二 两个表的外联接查询操作一 “教师表”与“课程表”的左联接查询select J.教师编号,J.教师姓名,isnull(K.课程名称,无授课安排) as授课安排from教师表 Jleft join 选修课程表 Kon J.教师编号=K.授课教师编号order by教师姓名select J.教师编号, J.教师姓名, K.课程名称 as 授课安排, Z.专业名称 as 教师所属专业from 教师表 J left join 选修课程表 K on J.教师编号=K.授课教师编号 left join 专

21、业表 Z on J.所属专业编号=Z.专业编号where K.课程名称 is not nullorder by 教师名称操作二 “教师表”与“选修课程表”的右联接查询select isnull(J.教师姓名,无授课教师) as授课教师,K.课程编号,K.课程名称 ,K.课时 from教师表 Jright join课程表 Kon J.教师编号=K.授课教师编号操作三 “教师表”与“课程表”的全联接查询select isnull(J.教师姓名,无授课教师) as授课教师,isnull(K.课程名称,无授课安排) as授课安排, isnull(K.课时,0) as 课时from教师表 Jfull j

22、oin课程表 Kon J.教师编号=K.授课教师编号操作四 用交叉连接生成“学生考勤记录”select X.学生编号,X.学生姓名,Q.考勤日期,Q.签到时间,Q.签退时间from学生表 Xcross join考勤表 Qorder by X.学生姓名select X.学生编号, X.学生姓名, Q.考勤日期, Q.签到时间, Q.签退时间into 学生考勤表from 学生表X cross join 考勤表Qorder by X.学生姓名任务三 用子查询检查教学计划操作一 使用in的子查询select教师编号,教师姓名, case when 性别=0 then 女 when 性别=1 then

23、男 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

24、教师编号from课程表where授课教师编号is not null)项目八 创建和使用视图任务一 创建视图操作三 用create view语句创建“学籍管理”视图use 学生管理gocreate view学籍管理as select所属班级编号as ClassNo,学生姓名as StudentName,职务as Dutyfrom学生表go任务二 使用视图操作一 查询“住宿管理”视图select StudentName as姓名,NativePlace as籍贯,case Sex when 1 then 男when 0 then 女end as性别from住宿管理视图where Birthday > 1980-1-1操作二 用“住宿管理”视图对“学生表”添加记录use 学生管理goinsert into住宿管理(StudentNo,Student