实验七T-SQL的应用

实验七 TSQL语言的应用实验内容 在已建立的TSGL数据库的基础上，按如下要求对数据库进行操作，保存操作代码和截图，命名要求同前。 1. 用界面方式和命令方式分别定义用户自定义telephone的数据类型。 2. 用界面方式和命令方式分别用telephone的数据类型定义一个电话号码字段。 3. 用界面方式和命令方式分别删除用户自定义数据类型telephone。 4. 创建一个名为SEX的局部变量，并在SELECT语句中使用该局部变量查找表XS中所有女同学的借书证号、姓名。5. 将查询结果赋给变量：声明一个变量var1并赋初值，查询TSGL数据库的XS表中的姓名，并将查询结果赋给变量var1，查询变量var1的值，检验将查询的多个结果如何赋予一个变量。6. 将查询结果直接赋给变量：声明一个变量var2并赋初值，查询TSGL数据库的XS表中指定学号的姓名，并将查询结果直接赋给变量var2，查询变量var2的值，检验学号存在与否的赋值情况。7. 将子查询结果赋给变量：声明一个变量var3并赋初值，将子查询“TSGL数据库的XS表中指定学号的姓名”的结果赋给变量var3，查询变量var3的值，检验学号存在与否的赋值情况。8. 使用游标读取“TSGL数据库的BOOK表中所有图书的ISBN、书名、作者信息”查询结果集的全部记录。分别使用游标函数FETCH_STATUS和Cursor_Rows来实现。 9. 在TSGL数据库中查询，如果“SQL SERVER实用教程”这一书籍的价格高于平均价格，则显示：“该SQL SERVER实用教程的价格高于平均价格”，否则显示 “该SQL SERVER实用教程的价格低于平均价格”。练习字符串的连接和数据类型的转换。 10. 显示字符串“Good Morning”中每个字符的ASCII值和字符。11. 在TSGL数据库中，使用CASE函数对查询的学生性别信息按男生和女生输出。12. 在TSGL数据库中，对XS表中的所有“计算机”专业的学生全部改为“计算机科学与技术”专业。13. 在TSGL数据库中，修改XS表中的所有“网络工程”专业学生的借书证号中专业代码号，将原来的代码号改为“042”。14查询图书表中的书名，只显示书名中最左边的6个非空字符。15检索库存量在58的ISBN、书名，并将库存量转换为char(20)。16定义一个按性别计算当前所有读者的平均借书数的函数并调用检查。17在TSGL数据库中，定义一个查询读者当前借阅情况的内嵌表值函数，并调用。18编写一用户自定义函数，用于实现根据作者姓名，查询图书有关信息。/* 1. 用界面方式和命令方式分别定义用户自定义telephone的数据类型。*/use TSGL203drop type dbo.telephonegocreate type telephonefrom char(12);/* 2. 用界面方式和命令方式分别用telephone的数据类型定义一个电话号码字段。*/alter table reader3drop column telephonegoalter table reader203add telephone telephone null/* 3. 用界面方式和命令方式分别删除用户自定义数据类型telephone。*/-无法直接删除类型telephone，由于它正由对象 reader203 引用drop type telephonego-方法二alter table reader203drop column telephonegodrop type telephone;/* 4. 创建一个名为SEX的局部变量，并在SELECT语句中使用该局部变量查找表XS中所有女同学的借书证号、姓名。*/declare SEX char(2)set SEX = 女select lno,namefrom reader203where sex = SEX/*5. 将查询结果赋给变量：声明一个变量var1并赋初值，查询TSGL数据库的XS表中的姓名，并将查询结果赋给变量var1，查询变量var1的值，检验将查询的多个结果如何赋予一个变量。*/declare var1 varchar(8)set var1 = 王一菲select var1 = namefrom reader203select var1 as NAME;/*6. 将查询结果直接赋给变量：声明一个变量var2并赋初值，查询TSGL数据库的XS表中指定学号的姓名，并将查询结果直接赋给变量var2，查询变量var2的值，检验学号存在与否的赋值情况。*/-学号存在时，变量var2为查询结果最后一个值declare var2 varchar(8)select var2 = 王一菲select var2 = namefrom reader203where lno = 200807035203select var2 as NAME;-学号不存在时，变量var2为初值declare var2 varchar(8)select var2 = 王一菲select var2 = namefrom reader203where lno = 201007035203select var2 as NAME;/*7. 将子查询结果赋给变量：声明一个变量var3并赋初值，将子查询“TSGL数据库的XS表中指定学号的姓名”的结果赋给变量var3，查询变量var3的值，检验学号存在与否的赋值情况。*/-学号存在时，变量var2为子查询结果最后一个值declare var3 varchar(8)select var3 = 王一菲select var3 = (select namefrom reader203where lno = 200807035203)select var3 as NAME;-学号不存在时，子查询结果返回 null ，故变量var2为nulldeclare var3 varchar(8)select var3 = 王一菲select var3 = (select namefrom reader203where lno = 201007035203)select var3 as NAME;/*8. 使用游标读取“TSGL数据库的BOOK表中所有图书的ISBN、书名、作者信息”查询结果集的全部记录。分别使用游标函数FETCH_STATUS和Cursor_Rows来实现。*/-函数FETCH_STATUSdeclare BOOK_CUR scroll cursorforselect ISBN,bname,authorfrom BOOK203for read onlyopen BOOK_CURwhile (FETCH_STATUS = 0)beginfetch next from BOOK_CURendclose BOOK_CURdeallocate BOOK_CUR-函数CURSOR_ROWS，使用CURSOR_ROWS函数必须为键集游标select CURSOR_ROWSdeclare BOOK_CUR cursor keyset for select ISBN,bname,authorfrom BOOK203for read onlyopen BOOK_CURwhile (FETCH_STATUS = 0)beginfetch next from BOOK_CURendselect CURSOR_ROWSclose BOOK_CURdeallocate BOOK_CUR/* 9. 在TSGL数据库中查询，如果“SQL SERVER实用教程”这一书籍的价格高于平均价格，则显示：“该SQL SERVER实用教程的价格高于平均价格”，否则显示 “该SQL SERVER实用教程的价格低于平均价格”。练习字符串的连接和数据类型的转换。*/declare BOOK_PRICE floatselect BOOK_PRICE = AVG(price)from book203select bname 书名,price 价格,BOOK_PRICE 平均价格,显示 =case when price BOOK_PRICE then 该书的价格高于平均价格when price BOOK_PRICE then 该书的价格低于平均价格else 该书的价格等于平均价格endfrom book203group by bname,price/* 10. 显示字符串“Good Morning”中每个字符的ASCII值和字符。*/declare position int,string char(12)set position = 1set string = Good Morningwhile (position = DATALENGTH(string)beginselect ASCII(SUBSTRING(string,position,1),CHAR(ASCII(SUBSTRING(string,position,1) AS 字符set position = position + 1 end/*11. 在TSGL数据库中，使用CASE函数对查询的学生性别信息按男生和女生输出。*/select lno,name,birdate,dept,spec,bornum,性别 =casewhen sex = 男 then 1when sex = 女 then 0else 无endfrom reader203order by sex/*12. 在TSGL数据库中，对XS表中的所有“计算机”专业的学生全部改为“计算机科学与技术”专业。*/update reader203set spec = 计算机科学与技术where spec like 计算机%;select *from reader203where spec like 计算机%;select lno 学号,replace(spec,spec,计算机科学技术)from reader203where spec like 计算机%;select *from reader203where spec like 计算机%;/*13. 在TSGL数据库中，修改XS表中的所有“网络工程”专业学生的借书证号中专业代码号，将原来的代码号改为“042”。*/-方法一select lno 原读者号,replace(lno,SUBSTRING(lno,7,3),042) 替换专业代码后读者号from reader203where spec like 网络%;-方法二-将reader203表中读者号存入新表select lnointo reader_lnofrom reader203where spec like 网络%;go-取消借阅表和历史借阅表的外键约束alter table lend203 nocheck constraint FK_lend_lno_0BC6C43Egoalter table history203 nocheck constraint FK_historytabl_lno_117F9D94go-更新reader203表中的读者号update reader203set lno = SUBSTRING(lno,1,6)+042+SUBSTRING(lno,10,3)from reader203where spec like 网络%;go-查询更新后读者信息select *from reader203where spec like 网络%; -更新借阅表和历史借阅表中读者信息update lend203set lno = SUBSTRING(lno,1,6)+042+SUBSTRING(lno,10,3)where lno in (select lnofrom reader_lno)goselect *from lend203go-历史借阅表update history203set lno = SUBSTRING(lno,1,6)+042+SUBSTRING(lno,10,3)where lno in (select lnofrom reader_lno)goselect *from history203-添加外键约束alter table lend203with checkcheck constraint FK_lend_lno_0BC6C43E; goalter table history203with check check constraint FK_historytabl_lno_117F9D94;/*14查询图书表中的书名，只显示书名中最左边的6个非空字符。*/-压缩图书表中书名中的空格select bname,REPLACE(bname, ,)from book203;-删除图书表中书名的前导空格，并返回字符串select bname,LTRIM(bname)from book203;-取压缩空格后书名中最左边的6个非空字符select bname,LEFT(REPLACE(bname, ,),6)from book203;-压缩前书名中最左边的6个非空字符select bname,LEFT(bname,7)from book203;/*15检索库存量在58的ISBN、书名，并将库存量转换为char(20)。*/select ISBN,bname,invnumfrom book203where CONVERT(char(20),invnum) like 5678;/*16定义一个按性别计算当前所有读者的平均借书数的函数并调用检查。*/create function avg_bornum(sex char(2)returns floatasbegindeclare avg_bornum floatselect avg_bornum =(select avg(bornum)from reader203where sex = sexgroup by sex)return avg_bornumendgodeclare sex char(2)-定义局部变量declare avg_bornum fl