SQL语法整理[8]——SQL语言编程

 

SQL语法整理[8]——SQL语言编程

/*使用变量查询学生成绩信息，以学生号为变量查询*/

DELCARE    @id      INT         /*使用DECLARE来定义变量*/

SET  @id=(SELECT  TOP   1  id   FROM   student)        /*为变量赋值*/

SELECT   *     FROM   result   WHERE   student_id=@id

GO

注意：每一行的结尾都没有逗号。

 

SQL语言详述
(1)数据定义(DDL---Data Definition Language)，如CREATE TABLE命令。

(2)数据操作(DML---Data Manipulation Language)，如SELECT命令。

(3)数据控制(DCL---Data Control Language)，如GRANT命令。

 

GRANT CREATE TABLE TO user_one//授予用户user_one创建表权限。

REVOKE CREATE TABLE FROM user_one//撤消用户user_one创建表权限。

DENY SELECT ON student user_one//拒绝用户user_one在表student上的查询权限。

 

变量的使用

变量是可以赋值的SQL语句元素，在SQL Server中有局部与系统变量两种。

(1)局部变量

局部变量的声明需要使用DECLARE语句。语法如下：

DECLARE {@variable_name     datatype[,…n]}

 

局部变量必须以@开头，例如：

DECLARE   @age     INT      --声明一个整数型的变量

SET  @age=23                  --为变量赋值

--或者使用     SELECT   @age=23

SELECT   *    FROM   student    WHERE   age=@age

GO

（局部变量首先需要声明，一方面要声明其变量名称，另一方面要声明变量的数据类型。变量声明以后需要赋值，这样才能使用。赋值的方法有两种：SET和SELECT关键字。局部变量仅仅作用在语句块中，跨出了这个范围就失效了。）

 

系统变量

系统变量是由SQL Server系统提供的预先声明好的变量，通过在名称前保留两个(@@)符号区别于局部变量。在SQL Server中系统变量以函数的形式出现。

SELECT   @@VERSION  AS  ‘SQL Server版本’

Go

 

变量的使用

变量的范围是非常大的，在SQL语句块、视图、存储过程、函数中都可以使用。

例如：创建一函数，要求在输入学生编号时输出年龄，如果年龄为0或者负值则警报。

CREATE    FUNCTION   fun_age(@id  INT)

RETURNS   VARCHAR(20)

AS

   BEGIN

           DECLARE  @age   INT,@result  VARCHAR(20)

           SET  @age=(SELECT   age  FROM  student   WHERE   id=@id)

           IF(@age<0 OR @age=0)

              SET @result='错误的年龄'

           IF(@age>0)

              SET @result='正确年龄'

           RETURN  @result

   END

GO

 

 

运算符

运算符是进行数据计算、字符操作、常量和变量比较的符号。SQL Server主要使用的运算符包括：算术运算符、比较运算符、逻辑运算符和字符串串联运算符(+)。

 

控制流程语句

(1)IF ELSE

IF ELSE条件判断     

IF logical_expression

   expressions1     

[ELSE expressions2] 

 

(2)BEGIN END

使用BEGIN END封装的是SQL语句块,其每个语句块都是完整的单元,在SQL Server中允许使用嵌套的BEGIN END语句块.

 

(3)WHILE

WHILE logical_expression

BEGIN                 

     Expression       

     [BREAK]          

        [CONTINUE]    

END                   

 

(4)CASE

CASE

    [WHEN logical_expression THEN result_expression][…n]

ELSE   result_expression                               

END

 

/*对grade字段使用CASE关键字*/

SELECT   id   AS  ‘学生编号’,

         name   AS  ‘学生姓名’,

         CASE   grade

                 WHEN    1     THEN   ‘一年级’

                 WHEN    2     THEN   ‘二年级’

                 WHEN    3     THEN   ‘三年级’

         END

FROM    student

GO

 

以上语句改为如下语句也对.但第三列没列名

SELECT   id   AS  学生编号,

         name   AS  学生姓名,

         CASE  

            WHEN    grade=1     THEN   '一年级'

            WHEN    grade=2     THEN   '二年级'

            WHEN    grade=3     THEN   '三年级'

         END

FROM    student

GO

 

以上语句改为如下语句也对.但第三列有列名

SELECT   id   AS  学生编号,

         name   AS  学生姓名,

         年级=

         CASE  

            WHEN    grade=1     THEN   '一年级'

            WHEN    grade=2     THEN   '二年级'

            WHEN    grade=3     THEN   '三年级'

         END

FROM    student

GO

 

(5)GO

(6)RETURN

 

其他SQL

(1)EXISTS

EXISTS使用

存在判断函数，如果表达式返回至少一行记录，则判断为存在,返回1;如果返回的结果是空集，则判断为不存在，返回0.

IF    EXISTS(SELECT   COUNT(*)   FROM    result)

BEGIN

     PRINT(‘学生成绩表格中存储了数据’)

END

ELSE

BEGIN

     PRINT(‘学生成绩表格中没有存储数据’)

END

GO

 

(2)INSERT INTO

使用INSERT INTO语句一次性插入多条数据

前提数据库中,必须有表存储了要插入的数据,或者可以通过系统函数来提供(比如使用GETDATE()来提供当前系统时间).表数据类型以及长度必须满足要插入的数据.

例如:将学生表中的数据插入到一张新的表中。

1. 创建名称为student_demo表

2. 将student表中的数据插入到student_demo中

INSERT    INTO    student_demo

(学生编号,姓名,性别,年龄,年级,班级,老师编号,家长编号,毕业时间,生源地,身份证号)

SELECT    id,name,age,gender,grade,class,teacher_id,parents_id,graduation,city,cardno     FROM  student

GO

 

(3)SELECT INTO

能够实现快速地转移数据,或者以现有的数据快速地创建一张表,起到一个快速备份的作用。

a.使用单表数据创建表

/*使用SELECT INTO关键字创建teacher_bak的表格*/

SELECT    *    INTO   teachers_bak

FROM    teachers

GO

b.使用多表数据创建表

例如:创建一张同时有学生表与家长表数据的表。

/*创建表格family*/

SELECT    s.id   AS   ‘学生编号’,

         s.name   AS    ‘学生姓名’,

         s.age     AS    ‘学生年龄’,

         s.grade   AS    ‘年级’,

         s.class    AS    ‘班级’,

         p.id      AS    ‘家长编号’,

         p.father   AS    ‘父亲名称’,

         p.mother  AS    ‘母亲名称’

INTO     family

FROM   student   s

INNER  JOIN

parents   p

ON

s.parents_id=p.id

GO

 

 (4)TRUNCATE(截断)和DELETE

TRUNCATE和DELETE命令都是删除数据,但TRUNCATE将表中所有数据都删除,而DELETE则可以有选择地删除,也同样可以将表中所有数据清除.

DELETE删除数据是记录在日志中的,可以通过其将数据恢复,而TRUNCATE命令则是将整个表截断,其操作是不记录在日志中的.所以，TRUNCATE命令清空表数据更加快捷.

表在录入数据以后,占用了一定的系统磁盘空间,当使用DELETE命令以后，并不是将数据原来占有的空间收回,而是继续保留一段时间.而通过TRUNCATE命令则是将表所有的空间收回到系统中,这是TRUNCATE与DELETE命令最根本的区别。

 

动态SQL语句执行

/*在school数据库中创建create_table存储过程*/

CREATE  PROCEDURE    create_table   @table_name   VARCHAR(30)

AS

BEGIN

       DECLARE   @v_sql    VARCHAR(200)

       SET  @v_sql=’CREATE  TABLE ’+@table_name+’(

                     id  INT,

                     [name]  CHAR(20),

                     city CHAR(20),

                     age  INT)’

       EXEC(@v_sql)  /*  将字符串转化为语句并执行*/

END

GO

注意:不要漏掉空格,否则会报错.

/*使用存储过程create_table创建linshi表*/

EXEUTE    create_table  ‘linshi’

GO

（动态的SQL语句，主要是在存储过程与函数中调用了DDL语言，由于通过正常的方式无法实现这些功能，只有通过动态的SQL来实现.动态SQL语句,在所有的大型数据库中都有,通过这种方式，能够实现一般SQL语句无法实现的功能.原理是将普通的SQL语句(主要是DDL语句)封装在一个长的字符串中，通过EXEC命令来调用。）

 

嵌套查询

基于列的查询

例如:要查找在学生表中,出现的所有老师的信息。

/*以teacher_id为嵌套查询的内容*/

SELECT   id    AS   ‘老师编号’,

          name  AS   ‘老师姓名’,

          age    AS   ‘老师年龄’,

        subject   AS    ‘老师所教的科目’,

     teaching_age  AS  ‘教学年龄’

FROM    teachers 

WHERE  id     IN(

     SELECT    teacher_id   FROM  student

)

GO

 

基于比较的查询

例如:在成绩表中找出大于平均成绩的信息.

/*通过对AVG()函数求得学生的平均年龄来对比*/

SELECT    *    FROM    result

WHERE

result>(

    SELECT   AVG(result)   FROM   result

)

GO

 

 

游标的使用

数据库中的游标类似于C语言的指针.特别是交互式联机应用程序,其并不总能将整个结果集作为一个单元来有效地处理.这些应用程序需要一种机制,以便每次处理一行或部分.游标就是提供这种机制的对结果集的一种扩展。

(1)声明游标

DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR

FOR                                           

select_statement                              

[FOR {READ ONLY|UPDATE[OF column_name[,…n]]}]

例如:

DECLARE cursor_student CURSOR

FOR select * from student

 

(2)使用游标

a.打开/关闭/释放游标

使用游标之前必须要能够打开.语法:OPEN cursor_name

游标在使用完毕以后要关闭.语法:CLOSE cursor_name.关闭游标后,可以再次打开,在一个批处理过程中,也可以多次打开和关闭。

游标本身会占用一定的计算机资源，在使用完后,为了能回收占有的系统资源,应该将其释放。语法:DEALLOCATE cursor_name.当游标释放完毕，如果要重新使用那么就需要重新声明.

 

b.使用游标取数

在打开游标以后，就可以使用游标取数.

FETCH                                         

[[NEXT|PRIOR|FIRST|LAST                       

|ABSOLUTE{n|@nvar}                            

|RELATIVE{n|@nvar}                            

]                                             

FROM                                          

]                                             

{{[GLOBAL] cursor_name}|@cursor_variable_name}

[INTO @variable_name[,…n]]

 

c.使用游标

例如:使用游标来将家长表中的信息输出。

/*以表parents为游标的基准*/

USE   school

GO

/*声明游标*/

DECLARE    cursor_parents     CURSOR    FOR

SELECT  id,father,mother,f_telephone,m_telephone

FROM    parents

/**打开游标/

OPEN      cursor_parents

/*第一次使用游标取数*/

FETCH    NEXT    FROM     cursor_parents

/*检查@@FETCH_STATUS以确定是否还可以继续取数*/

WHILE    @@FETCH_STATUS=0

BEGIN

    FETCH  NEXT   FROM     cursor_parents

END

/*关闭游标*/

CLOSE     cursor_parents

 

——大二的期末考试整理 [e-mail]