第五章 Transact—SQL 语句

【例5.1】创建名称为test_db数据库，输入语句如下。
CREATE DATABASE [test_db ON  PRIMARY 
(
NAME = test_data1',       --数据库逻辑文件名称
FILENAME = 'D:\SS2008Data\test_data.mdf',    --主数据文件存储位置
SIZE = 5120KB ,      --主数据文件大小
MAXSIZE =20,      --主数据文件最大增长空间为20MB
FILEGROWTH =1     --文件增长大小设置为1MB
)

【例5.2】在test_db数据库中创建员工表tb_emp1，输入语句如下：
USE test_db
CREATE TABLE tb_emp1
(
id      INT PRIMARY KEY,
name   VARCHAR(25) NOT NULL,
deptId  CHAR(2) NOT NULL,
salary  SMALLMONEY NULL
);

【例5.3】删除test数据库中的person表，输入语句如下：
USE test
GO
DROP TABLE dbo.person

【例5.4】删除test数据库，输入语句如下：
DROP DATABASE test

【例5.5】将test_db数据库的名称修改为company，输入语句如下：
ALTER DATABASE test_db
MODIFY NAME=company

【例5.6】在更改过名称的company数据库中，向tb_emp1数据表中添加名称为birth的字段列，数据类型为date，要求非空，输入语句如下：
USE company
GO
ALTER TABLE tb_emp1
ADD  birth DATE NOT NULL

【例5.7】删除tb_emp1表中的birth字段列，输入语句如下：
USE company
GO
ALTER TABLE tb_emp1
DROP COLUMN birth

【例5.8】向teacher表中插入一条新记录，输入语句如下：
INSERT INTO teacher VALUES(1, '张三', '1978-02-14', '男', '0018611')  --插入一条记录
SELECT * FROM teacher

【例5.9】向teacher表中插入多条新记录，输入语句如下：
SELECT * FROM teacher
INSERT INTO teacher 
VALUES (2, '李四', '1978-11-21','女', '0018624') ,
(3, '王五','1976-12-05','男', '0018678') ,
(4, '赵纤','1980-6-5','女', '0018699') ;
SELECT * FROM teacher

【例5.10】在teacher表中，更新id值为2的记录，将birth字段值改为’1980-8-8’，将cellphone字段值改为’0018600’， 输入语句如下：
SELECT * FROM teacher WHERE id =1;
UPDATE teacher 
SET birthday = '1980-8-8',cellphone='0018600' WHERE id = 1;
SELECT * FROM teacher WHERE id =1;

【例5.11】在teacher表中，将所有老师的电话都修改为’01008611’，输入语句如下：
SELECT * FROM teacher;
UPDATE teacher SET cellphone='01008611';
SELECT * FROM teacher;

【例5.12】删除teacher表中id等于1的记录，输入语句如下：
DELETE FROM teacher WHERE id=1;
SELECT * FROM teacher WHERE id=1;

【例5.13】删除teacher表中所有记录，输入语句如下。
SELECT * FROM teacher;
DELETE FROM teacher;
SELECT * FROM teacher;

【例5.14】查询stu_info表中的所有学生信息，输入语句如下。
SELECT * FROM stu_info;

【例5.15】查询stu_info数据表中学生的姓名和成绩，输入语句如下。
SELECT s_name, s_score FROM stu_info;

【例5.16】不修改数据表，查询并显示所有学生的成绩降低5分后的成绩，输入语句如下。
SELECT s_name, s_score, s_score – 5 AS new_score FROM stu_info;

【例5.17】查询stu_info中所有的记录，但只显示前3条，输入语句如下。
SELECT TOP 3 * FROM stu_info;

【例5.18】查询stu_info表中所有性别为‘男’的学生的信息，输入语句如下。
SELECT * FROM stu_info WHERE s_sex='男';

【例5.19】查询stu_info表中所有性别为‘男’的学生的信息，输入语句如下。
SELECT * FROM stu_info WHERE NOT  s_sex='男';

【例5.20】查询stu_info表中性别为‘男’并且成绩大于80的学生信息，输入语句如下。
SELECT * FROM stu_info WHERE s_sex='男' AND s_score > 80;

【例5.21】查询stu_info表中成绩大于80，或者年龄大于18的学生信息，输入语句如下。
SELECT * FROM stu_info WHERE s_score > 80 OR s_age>18;

【例5.22】在stu_info数据表中，查询所有姓‘马’的学生信息，输入语句如下。
SELECT * FROM stu_info WHERE s_name LIKE '马% '

【例5.23】查询stu_info表中所有姓张、姓王、姓李的学习信息，输入语句如下。
SELECT * FROM stu_info WHERE s_name LIKE '[张王李]% '

【例5.24】查询stu_info表中成绩大于50小于90的学生信息，输入语句如下。
SELECT * FROM stu_info WHERE s_score BETWEEN 50 AND 90;

【例5.25】查询stu_info表中所有学生信息，并按照成绩由低到高进行排序，输入语句如下。
SELECT * FROM stu_info ORDER BY s_score DESC;

【例5.26】对名称为recruit的用户进行授权，允许其对stu_info数据表执行查询和更新权限，输入语句如下。
GRANT SELECT,UPDATE ON stu_info
TO recruit WITH GRANT OPTION

【例5.27】禁止recruit用户对stu_info表的操作更新权限，输入语句如下。
DENY UPDATE ON stu_info TO recruit;

【例5.28】收回recruit用户对stu_info表的查询权限，输入语句如下。
REVOKE SELECT ON stu_info FROM recruit;

【例5.29】声明两个局部变量，名称为username和pwd，并为这两个变量赋值，输入语句如下。
DECLARE @username VARCHAR(20)
DECLARE @pwd VARCHAR(20)
SET    @username = 'newadmin'
SELECT @pwd = 'newpwd'
SELECT '用户名：'+@username +'  密码：'+@pwd

【例5.30】查询stu_info表中的学生成绩，并将其保存到局部变量stuScore中，输入语句如下。
DECLARE @stuScore INT
SELECT  s_score FROM stu_info
SELECT  @stuScore  = s_score FROM stu_info 
SELECT  @stuScore AS Lastscore

【例5.31】定义字符串变量name和整数变量age，使用PRINT输出变量和字符串表达式值，T-SQL代码如下。
DECLARE @name VARCHAR(10)='小明'
DECLARE @age INT = 21
PRINT '姓名    年龄'
PRINT @name+'     '+CONVERT(VARCHAR(20), @age)

【例5.32】定义局部变量@count，如果@count值小于10，执行WHILE循环操作中的语句块，T-SQL代码如下。
DECLARE @count INT;
SELECT @count=0;
WHILE @count < 10
BEGIN
     PRINT 'count = ' + CONVERT(VARCHAR(8), @count)
     SELECT @count= @count +1
END
PRINT 'loop over count = ' + CONVERT(VARCHAR(8), @count);

【例5.33】IF…ELSE流程控制语句的使用，输入语句如下。
DECLARE @age INT;
SELECT @age=40
IF  @age <30
    PRINT 'This is a young man!'
ELSE 
    PRINT 'This is a old man!'

【例5.34】使用CASE语句根据学生姓名判断各个学生在班级的职位，
USE test
SELECT s_id,s_name,
CASE s_name
    WHEN '马华' THEN '班长' 
    WHEN '许三' THEN '学习委员'
    WHEN '刘杰' THEN '体育委员'
    ELSE '无'
END 
AS '职位'
FROM stu_infoFROM stu_info

【例5.35】使用CASE语句对考试成绩进行评定，输入语句如下。
SELECT s_id,s_name,s_score,
CASE
    WHEN s_score > 90 THEN '优秀' 
    WHEN s_score > 80 THEN '良好'
    WHEN s_score > 70 THEN '一般'
    WHEN s_score > 60 THEN '及格'
    ELSE '不及格'
END 
AS '评价'
FROM stu_info

【例5.36】WHILE循环语句的使用，输入语句如下。
DECLARE @num INT;
SELECT @num=10;
WHILE @num > -1
BEGIN
     If @num > 5 
        BEGIN
           PRINT '@num 等于' +CONVERT(VARCHAR(4), @num)+ '大于5 循环继续执行';
           SELECT @num = @num - 1;
           CONTINUE;
        END
     else
        BEGIN
           PRINT '@num 等于'+ CONVERT(VARCHAR(4), @num);
           BREAK;
        END
END

PRINT '循环终止之后@num 等于' + CONVERT(VARCHAR(4), @num);

【例5.37】GOTO语句的使用，输入语句如下。
Use test;
BEGIN
  SELECT s_name FROM stu_info;
GOTO jump
  SELECT s_score sFROM stu_info;
jump:
  PRINT '第二条SELECT语句没有执行';
END

【例5.38】10秒钟的延迟后执行SET语句，输入语句如下。
DECLARE @name VARCHAR(50);
SET @name='admin';
BEGIN
WAITFOR DELAY '00:00:10';
PRINT @name;
END;

8、课后习题

第一题
create database zooDB_data
on
(
name=zooDB_data,
filename='D:\用户目录\我的文档\SQL Server Management Studio\zooDB_data.mdf',
maxsize=15MB,
filegrowth=5%
)
go
drop database zooDB_data

第二题
declare @var int
declare @Cupid varchar(20)
select @var =3;
select @Cupid =
case when @var=1 then  'var is 1'
when @var=2 then  'var is 2'
else  'var is not 1 or 2'
end


select @Cupid