T-SQL语言的使用

变量的使用

--1.创建用户自定义表数据类型，包含表CJB中的所有列

CREATE TYPE CJB_tabletype

   AS TABLE

   {

      学号  char(6) NOT NULL,

      课程号  char(3) NOT NULL,

      成绩  int     NOT NULL,

      PRIMARY KEY(学号,课程号)

   }

--2.创建局部变量@var，@var2并赋值，然后输出变量的值。

DECLARE @var1 char(10),@var2 char(30)

SET @var1='中国'

SET @var2=@var1+'是一个伟大的国家'

SELECT @var1,@var2

 

--3.创建一个名为sex的局部变量，并在SELECT语句中使用该局部变量查找表XSB中所有女同学的学号,姓名

DECLARE @sex bit

SET @sex=0

   SELECT 学号,姓名

      FROM XSB

      WHERE 性别=@sex

 

--4.使用查询为变量赋值

DECLARE @student char(8)

SET @student=(SELECT 姓名 FROM XSB WHERE 学号='081101')

SELECT @student

--5.使用SELECT语句多为局部变量赋值

DECLARE @var1 nvarchar(30)

SELECT @var1='刘峰'

SELECT @var1 AS 'NAME'

--6.为局部变量赋空值

DECLARE @var1 nvarchar(30)

SELECT @var1='刘峰'

SELECT @var1 =

{

   SELECT 姓名

      FROM XSB

      WHERE 学号='089999'

}

SELECT @var1 AS 'NAME'

--7.使用游标

USE PXSCJ

GO

DECLARE @CursorVar CURSOR /*定义游标变量*/

SET @CursorVar=CURSOR SCROLL DYNAMIC /*为游标变量赋值*/

   FOR

   SELECT 学号,姓名

      FROM XSB

      WHERE 姓名 LIKE '王%'

OPEN @CursorVar /*打开游标*/

FETCH NEXT FROM @CursorVar

FETCH NEXT FROM @CursorVar /*通过游标读行记录*/

CLOSE @CursorVar

DEALLOCATE @CursorVar /*删除对游标的引用*/

--8.声明一个表数据类型变量并向变量插入数据

DECLARE @var_table

   AS TABLE

   {

      num char (6) NOT NULL PRIMARY KEY,

      name char(8) NOT NULL,

      sex bit NULL

   }

INSERT INTO @var_table

   SELECT 学号,姓名,性别 FROM XSB

SELECT * FROM @var_table

运算符

--9.在master数据库中,建立表bitop,并插入一行,然后将a字段和b字段列上值进行按位与运算

USE master

GO

CREATE TABLE bitop

{

   a int NOT NULL,

   b int NOT NULL

}

INSERT bitop VALUES(168,73)

select a % b,a |b, a ^ b

   FROM bitop

   GO

--10.多个字符串的连接

SELECT (学号+','+姓名) AS 学号及姓名

   FROM XSB

   WHERE 学号='081101'

程序流程控制语句

--11.以下程序用于查询总学分大于的学生人数

DECLARE @num int

SELECT @num=(SELECT COUNT(姓名) FROM XSB WHERE 总学分>42)

if @num<>0

   SELECT @num AS '总学分>42的人数'

--12.如果‘计算机基础’课程的平均成绩高于分，则显示‘平均成绩高于’

IF

(

   SELECT AVG(成绩)

      FROM XSB,CJB,KCB

      WHERE XSB.学号=CJB.学号

        AND CJB.课程号=KCB.课程号

        AND KCB.课程名='计算机基础'

)<75

   SELECT '平均成绩低于'

ELSE

   SELECT '平均成绩高于'

--13.使用第一种格式的CASE有，根据性别值输出'男'或'女'

SELECT 学号,姓名,专业,SEX=

      CASE 性别

        WHEN 1 THEN '男'

        WHEN 0 THEN '女'

        ELSE '无'

      END

   FROM XSB

   WHERE 总学分>48

--14.将学号为的学生的总学分使用循环修改到，每次只加，并判断循环了多少次

DECLARE @num INT

SET @num=0

WHILE (SELECT 总学分 FROM XSB WHERE 学号='081101')<60

BEGIN

   UPDATE XSB SET 总学分=总学分+2 WHERE 学号='081101'

   SET @num=@num+1

END

SELECT @num AS 循环数

 

--15.判断是否存在学号为的学生，如果存在则返回，不存在则插入号学生的信息

IF EXISTS (SELECT * FROM XSB WHERE 学号='081128')

   RETURN

ELSE

   INSERT INTO XSB VALUES('081128','张可',1,'1990-08-12','计算机',52,NULL)

  

--16.如下语句设定在早上八点执行查询语句

BEGIN

   WAITFOR TIME '8：'

   SELECT * FROM XSB

END

--系统内置函数

--17.显示abs函数对三个不同数字的效果

SELECT ABS(-5.0),ABS(0.0),ABS(8.0)

 

--18.以下程序通过RAND函数返回随机值

DECLARE @count int

SET @count=5

SELECT RAND(@count)

 

--字符串处理函数

--19.查找字符串'sql'最左端的ASCII值

SELECT ASCII('SQL')

--返回课程名最左边的个字符

SELECT LEFT(课程名,4)

   FROM KCB

   ORDER BY 课程号

  

--20.使用LTRIM字符变量中的起始空格

DECLARE @string varchar(40)

SET @string='    中国，一个古老而伟大的国家'

SELECT LTRIM(@string)

SELECT @string

 

--21.如下程序在一列中返回表XSB中的姓氏，在另一列中返回表中学生的姓名

SELECT SUBSTRING(姓名,1,1),SUBSTRING(姓名,2,LEN(姓名)-1)

   FROM XSB

   ORDER BY 姓名

  

--22.显示字符串‘CHAINA’中每个字符的ASCII值和字符

DECLARE @position int,@string char(8)

SET @position=1

SET @string='China'

WHILE @position<=DATALENGTH(@string)

   BEGIN

      SELECT ASCII(SUBSTRING(@string,@position,1)) AS ASCLL码,

        CHAR(ASCII(SUBSTRING(@string,@position,1))) AS 字符

      SET @position=@position+1

   END

游标函数

--23.声明一个游标，并用SELECT显示@@CURSOR_ROWS

USE PXSCJ

GO

SELECT @@CURSOR_ROWS

DECLARE student_cursor CURSOR

   FOR SELECT 姓名 FROM XSB

OPEN student_cursor

FETCH NEXT FROM student_cursor

SELECT @@CURSOR_ROWS

CLOSE student_cursor

DEALLOCATE student_cursor

 

--24.用@@FETCH_STATUS控制在一个WHILE循环中的游标活动

DECLARE @name char(20),@st_id char(6)

DECLARE student_Cursor CURSOR

   FOR

   SELECT 姓名,学号 FROM PXSCJ.dbo.XSB

OPEN student_Cursor

FETCH NEXT FROM student_Cursor INT @name,@st_id

SELECT @name,st_id

WHILE @@FETCH_STATUS=0

BEGIN

   FETCH NEXT FROM student_Cursor

END

CLOSE student_Cursor

DEALLOCATE student_Cursor

 

--用户定义函数

--25.创建用户定义函数，实现计算全体学生某门功课平均成绩的功能

CREATE FUNCTION average(@cnum char(20)) RETURN int

   AS

   BEGIN

      DECLARE @aver int

      SELECT @aver=

      (

        SELECT @aver

           FROM CJB

           WHERE 课程号=@cnum

          

           GROUP BY 课程号

      )

      RETURN @aver

   END

   GO

  

--26.调用上列定义的函数average

DECLARE @course1 char(10)

DECLARE @aver1 int

SELECT @course1='101'

SELECT @aver1=dbo.average(@course1)

SELECT @aver1 AS '101课程的平均成绩'

 

--27.建立一个表course，并将一个字段定义为计算机

CREATE TABLE course

(

   cno int,

   cname nchar(20)

   credit int,

   aver AS

      (

      dbo.average(cno)

      )

)

 

--28.内嵌表值函数。为了让学生查询其各科成绩及学分，可以利用XSB,KCB,CJB三个表创建视图

CREATE VIEW ST_VIEW

   AS

   SELECT dbo.XSB.学号,dbo.XSB.姓名,dbo.KCB.课程名,dbo.CJB.成绩

   FROM dbo.KCB

   INNER JOIN dbo.CJB ON dbo.KCB.课程号=dbo.CJB.课程号

   INNER JOIN dbo.XSB ON dbo.CJB.学号=dbo.XSB.学号

   --然后在此基础上定义如下内嵌函数

CREATE FUNCTION st_score(@student_ID char(6))

RETURNS table

AS RETURN

(

   SELECT *

      FROM PXSCJ.dbo.ST_VIEW

      WHERE dbo.ST_VIEW.学号=@student_ID

)

 

--29.调用st_score函数，查询学号为的学生的各科成绩及学分

SELECT *

   FROM PXSCJ.[dbo].st_score(081101)

  

--30.多语句表值函数。在pxscj数据库中创建返回表的函数，通过以学号作为实参调用该函数

--可以显示该学生哥们功课的成绩和学分

CREATE FUNCTION score_table(@id char(6))

RETURNS @score TABLE

(

   XS_ID char(6)

   XS_NAME char(8)

   KC_NAME char(16)

   CJ    tinyint

   XF    tinyint

)

AS

   BEGIN

      INSERT @score

   SELECT S.学号,S.姓名,P.课程名,O.成绩,P.学分

   FROM FROM PXSCJ.[dbo].XSB AS S

      INNER JOIN PXSCJ.[dbo].CJB AS O ON (S.学号=O.学号)

      INNER JOIN PXSCJ.[dbo].KCB AS P ON (O.课程号=P.课程号)

   WHERE S.学号=@id

RETURN

END

--系统函数

--31.将检索总学分在~59分的学生姓名，并将总学分转换为char(20)

SELECT 姓名,总学分

   FROM XSB

   WHERE CAST (总学分 AS char(20)) LIKE '5_' AND 总学分>=50

SELECT 姓名,总学分

   FROM XSB

   WHERE CONVERT(char(20),总学分)LIKE '5_' AND 总学分>=50