第2章.txt

sql server 2000 数据库与应用

【例2-1】在数据库XSCJ中定义一个长度为12的字符串类型变量@student，对该变量进行赋值，并且查询出“学生基本信息表“中该变量所指定姓名的学生信息。
在查询分析器中运行如下命令：
USE XSCJ
GO
DECLARE @student CHAR(12)
SET @student = '王倩倩'
SELECT * FROM 学生基本信息表 WHERE 姓名 = @student
【例2-2】在数据库XSCJ中定义两个日期时间类型的变量@max_csrq、@min_csrq，分别用于查询“学生基本信息表”中“出生日期”的最大值、最小值。
USE XSCJ
GO
DECLARE @max_csrq DATETIME , @min_csrq DATETIME
SELECT @max_csrq=MAX(出生日期),@min_csrq=MIN(出生日期) FROM 学生基本信息表
PRINT @max_csrq
PRINT @min_csrq
【例2-3】计算XSCJ数据库中“学生基本信息表”的总行数。
USE XSCJ
GO
SELECT COUNT(*) AS '学生总人数' FROM 学生基本信息表
GO
【例2-4】计算XSCJ数据库中课程编号为003的课程总成绩和平均成绩。
USE XSCJ
GO
SELECT  SUM(成绩) AS '课程总分',AVG(成绩) AS '课程平均分' 
FROM 成绩表 WHERE 课程编号='003'
GO
【例2-5】在成绩表中查询课程编号为003的课程的最高分和最低分。
USE XSCJ
GO
SELECT MAX(成绩) AS '最高分',MIN(成绩) AS '最低分'
FROM 成绩表
WHERE 课程编号='003'
GO
【例2-6】分别输出2的3次幂、-1的绝对值、2的平方、3.14的整数部分。
PRINT POWER(2,3)
PRINT ABS(-1)
PRINT SQUARE(2)
PRINT FLOOR(3.14)
GO
【例2-7】使用LEN函数显示字符串常量以及字符串变量的长度。
提示：LEN函数用于计算字符串中所包含的字符个数，如果字符串尾部含有空格则会被忽略。
PRINT LEN('computer department')
DECLARE @s1 char(10)
DECLARE @s2 char(10)
SET @s1 = 'welcome'
SET @s2 = 'hellow    '
PRINT LEN(@s1)
PRINT LEN(@s2)
【例2-8】给定一个字符串’have a good time’，判断字符’g’在整个字符串中的位置。
提示：CHARINDEX函数用于在规定字符串中对子字符串进行查询。当返回值大于零时表示子字符串的起始位置，返回值为0时表明没有查询结果。
DECLARE @s CHAR(20)
SET @s ='have a good time'
PRINT CHARINDEX('g',@s)
【例2-9】获取系统时间信息，在查询分析器中分别显示系统时间中的年份、月份以及日期。
提示：GETDATE函数用于返回当前的系统时间，YEAR，MONTH，DAY函数可以取得时间中的年、月、日的数值。
在查询分析器中运行如下命令：
DECLARE @xtsj DATETIME
SET @xtsj = GETDATE()
SELECT YEAR(@xtsj)
SELECT MONTH(@xtsj)
SELECT DAY(@xtsj)
【例2-10】通过对“学生基本信息表”中的“出生日期”字段进行计算，查询每一位学生的年龄。
提示：利用DATEDIFF函数可以计算出两个日期之间的距离，该函数含有三个参数，第一个参数通常可以为yy（年）或mm（月）或dd（日），若第一个参数为yy时，该函数返回值为后两个日期参数之间年份的差距。在本例中，当前的日期由GETDATE函数获得后，计算与每一位学生的出生日期之间年份的差距，从而获得学生的年龄并在查询结果中显示。
SELECT 学号,姓名,DATEDIFF(yy,出生日期,GETDATE()) from 学生基本信息表
【例2-11】查询“学生基本信息表”中的学号、姓名、年龄，并且将这三个字段通过“+”运算符进行连结显示在查询结果中。
提示：由于计算学生年龄的结果为整数，而学号、姓名均为字符串类型的值，因而在运算之前,需要将年龄的计算结果转化为字符串，即CAST(DATEDIFF(yy , 出生日期 , GETDATE()) AS CHAR(2))。
SELECT 学号+姓名+'年龄：'+CAST(DATEDIFF(yy,出生日期,GETDATE()) AS CHAR(2))
FROM 学生基本信息表
【例2-12】将常量3.14分别转换为整数类型以及字符串类型并且输出结果。
PRINT CONVERT(INTEGER,3.14)
PRINT CONVERT(CHAR(4),3.14)
【例2-13】取得系统当前时间，并且将其转化为mm/dd/yyyy格式的字符串并且显示结果。
提示；由于mm/dd/yyyy格式所对应的参数为101，所以在使用CONVERT函数时，日期格式样式参数设定为101。
DECLARE @xtsj DATETIME
SET @xtsj = GETDATE()
PRINT CONVERT(CHAR(50),@xtsj,101)
【例2-14】显示XSCJ数据库信息。
sp_helpdb XSCJ
【例2-15】创建一个名称为somename的数据库，利用sp_renamedb存储过程将该数据库重命名为newname
CREATE DATABASE somename
GO
sp_renamedb 'somename','newname'
GO
【例2-16】显示超级用户dbo的用户信息。
sp_helpuser dbo
【例2-17】在系统中添加一个名为test、密码为1234的登录帐号。
sp_addlogin test,1234
【例2-18】在SQL Server中添加一个名称为test的用户。
sp_addlogin test
GO
sp_adduser test
GO
【例2-19】删除名称为test的用户。
sp_dropuser test
【例2-20】添加一个名为myrole的角色，并将test用户加入该角色中。
sp_addrole  myrole
GO
sp_addmember myrole , test
GO
【例2-21】删除已有的角色myrole。
sp_droprole  myrole
【例2-22】执行批处理程序，依次查询系部表、系部总数、班级表、班级总数。
USE XSCJ
GO
SELECT * FROM 系部表
SELECT COUNT(*) FROM 系部表
SELECT * FROM 班级表
SELECT COUNT(*) FROM 班级表
GO
【例2-23】在“学生基本信息表”中查询班级编号为20041001的班级中是否有党员。要求：如果有党员则显示党员的人数，否则，提示该班级没有党员。
USE XSCJ
GO
IF (SELECT COUNT(*) FROM 学生基本信息表 WHERE 班级编号 = '20041001' AND 政治面貌 = '党员') > 0
BEGIN
    DECLARE @dy INTEGER
    SELECT @dy = COUNT(*) FROM 学生基本信息表 WHERE 班级编号 = '20041001' AND 政治面貌 = '党员'
    PRINT '党员的人数为：'
    PRINT @dy
END
ELSE
    PRINT '该班级没有党员'
【例2-24】根据系统时间判断当前日期所对应的星期值并且输出结果。
DECLARE @dt DATETIME
SET @dt = DATEPART(w,GETDATE())
SELECT 
CASE @dt
   WHEN 1 THEN  '星期天'
   WHEN 2 THEN  '星期一'
   WHEN 3 THEN  '星期二'
   WHEN 4 THEN  '星期三'
   WHEN 5 THEN  '星期四'
   WHEN 6 THEN  '星期五'
   WHEN 7 THEN  '星期六'
END
【例2-25】取得系统时间，并且判断当前时间在一天中所处的时间段，并且在查询分析器中输出提示信息。
DECLARE @sj DATETIME
SET @sj = DATEPART(hh,GETDATE())
SELECT 
CASE
   WHEN @sj>=20 and @sj<24 THEN  '晚上'
   WHEN @sj>=14 and @sj<20 THEN  '下午'
   WHEN @sj>=12 and @sj<14 THEN  '中午'
   WHEN @sj>=10 and @sj<12 THEN  '早晨' 
   WHEN @sj>=0 and @sj<10 THEN  '临晨'  
END
【例2-26】利用循环计算1+2+3+…+99+100的值。
DECLARE @sum int , @i int
SET @sum = 0
SET @i = 1
WHILE @i<=100
BEGIN
   SET @sum = @sum + @i
   SET @i = @i + 1
END
PRINT @sum
【例2-27】利用WHILE循环计算当前时间距离2007年1月1日之间的天数。
DECLARE @num int , @sj DATETIME
SET @sj = GETDATE()
PRINT '当前时间为：' + CAST( @sj AS CHAR(20))
SET @num=0 WHILE 1=1 BEGIN
   SET @sj = DATEADD(day,1,@sj)
   SET @num = @num + 1
   IF DATEPART( yy , @sj ) = 2007
     BREAK
END 
PRINT '距离2007年1月1日还有' + CAST(@num AS VARCHAR(10)) + '天'