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

SQLServer2005中文版基础教程示例的T-SQL语句项目一创建与使用SQLServer实例任务二启动和连接“教育学院管理数”实例操作二在Windows的【命令提示符】中启动实例语法一netstart“SQLServer(教育学院管理)”语法二netstartMSSQL$教育学院管理项目二管理数据库任务一创建“学生管理数据库”操作二用createdatabase语句创建数据库USE[master]GOcreatedatabase[学生管理]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_ASgo任务二修改数据库操作五用alterdatabase语句增加日志文件USE[学生管理]goalterdatabase[学生管理]Addlogfile(name=’Snd_Student_Log’,filename=’D:\DBFiles\Snd_Student_Log.ldf’,size=10MB,maxsize=unlimited,filegrowth=5MB)Go操作五用alterdatabase语句修改排序规则USE[学生管理]GOALTERDATABASE[学生管理]COLLATELatin1_General_BINGO任务三删除数据库操作二用dropdatabase语句删除数据库USE[master]GODROPDATABASE[学生管理]GO项目三管理数据表任务一创建“学生表”操作二用createtable语句创建表createtable学生表(学生编号varchar(10)NOTNULL,学生姓名char(10)NULL,所属专业编号char(2)NULL,所属班级编号char(3)NULL,性别intNULL,出生日期smalldatetimeNULL,籍贯varchar(20)NULL,入学日期smalldatetimeNULL)任务二修改表结构操作二用altertable语句修改表结构use[学生管理]goaltertable学生表altercolumn学生编号char(4)notnullgoaltertable学生表altercolumn学生姓名varhar(10)nullgoaltertable学生表altercolumn性别tinyintnullgoaltertable学生表add职务varchar(10)nullgo任务三向“学生表”插入记录操作二用insert语句插入记录insertinto学生表(学生编号,学生姓名,所属专业编号,所属班级编号,性别,出生日期,籍贯,入学日期,职务)values(‘X001’,’宋小南’,’Z1’,’B01’,0,’1980-8-1’,’黑龙江省哈尔滨市’,’2001-9-1,’班长’)go任务四修改“学生表”中的记录操作二用update语句修改记录use[学生管理]goupdate学生表set出生日期=’1983-5-6’,籍贯=’黑龙江省齐齐哈尔市’where学生编号=’X001’go任务五删除“学生表”记录操作二用delete语句删除记录use[学生管理]godeletefrom学生表where学生编号=’X007’go任务六删除“学生表”操作二用droptable语句删除表use[学生管理]godroptable学生表go项目四设置主键、外键和索引任务一为“学生表”设置主键操作三用createtable语句设置主键use[学生管理]gocreatetable学生表(学生编号char(4)notnullprimarykey,学生姓名varchar(10),所属专业编号char(2),所属班级编号char(3),性别tinybit,出生日期smalldatetime,籍贯varchar(20),入学日期smalldatetime,职务varchar(10))go操作四用altertable语句设置主键altertable学生表addprimarykey(学生编号)go操作五用altertable语句删除主键altertable学生表dropPK_学生表_15502E78go任务二为“学生成绩表”设置外键操作二用createtable语句设置外键createtable学生表(学生编号char(4)notnullconstraintFK_学生成绩表_学生表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(出生日期))=25and(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学生表goselectmax(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(成绩)=80groupby课程编号orderby课程编号descselect课程编号,sum(成绩)as总成绩,count(*)as总人数,avg(成绩)as平均成绩from学生成绩表groupby课程编号havingavg(成绩)=80orderbyavg(成绩)descselect课程编号,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学生编