【个人笔记】SQL操作进阶

• 【CASE WHEN ELSE END】

  对查询出来的数据进行比对修改
  用法：
  第一种：值判断
  SELECT
  CASE [列名]
  WHEN [值] THEN [输出的值]
  …
  ELSE [输出的值]
  END
  FROM [表]
  第二种：条件判断
  SELECT
  CASE
  WHEN [列名+表达式] THEN [输出的值]
  …
  ELSE [输出的值]
  END
  FROM [表]
  注：1.多条WHEN语句一次执行，如C#中的if…else if…else…
  2.一个 CASE~END 代表一个列
  3.[输出的值] 类型要一致。可以使用 CAST(列名 AS 类型) 进行类型转换
  4.

• 【子查询】 嵌套查询

  在一个查询中有另一个查询，另一个查询称为这个查询的子查询
  如：SELECT * FROM STUDENT WHERE CID=(SELECT ID FROM CLASSES WHERE NAME=’四期班’)
  配合 IN 使用 子查询
  如：SELECT * FROM STUDENT WHERE CID IN (SELECT ID FROM CLASSES WHERE ID>(SELECT ID FROM CLASSES WHERE NAME=’九期班’))
  注：只有子查询返回的结果集是一行一列的才能当做值来用，才能用等值判断

[EXISTS]
EXISTS是引用了主查询的数据进行子查询的一种方式，子查询中，只做条件判断，不做任何的输出
例子：SELECT * FROM STUDENT WHERE EXISTS (SELECT * FROM CHASSES WHERE STUDENT.CID=CLASSES.ID)

• 【分页查询】

  1. ROW_NUMBER() 会在结果集的基础上加上一列，这一列从1开始，中间不会有间断的。
  2. 在分页查询中，使用 ROW_NUMBER() 与 BETWEEN AND 配合使用，效率比较高。
     例子：SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY AR_ID) AS NROWS FROM AREA) AS T WHERE T.NROWS BETWEEN 11 AND 20

• 【JOIN】

  [INNER JOIN]    即普通的 JOIN ，当用户使用 JOIN 时，系统默认识别为 INNER JOIN [LEFT JOIN]    在普通的 JOIN 基础上，优先显示左表的数据，如果能满足链接条件，就显示链接之后的右表的数据，如果不满足链接条件，还是会显示左表数据，右表数据部分就是NULL[RIGHT JOIN]    在普通的 JOIN 基础上，优先显示右表的数据，如果链接条件满足，就显示左表的数据，如果不满足就显示为NULL [CROSS JOIN]    CROSS JOIN 会得出两张表的乘积行，即‘一对多’显示为结果集

• 【视图】

  视图其实就是一个虚拟表，这张虚拟表的数据其实是来自于一个查询，这个查询查询了其他一个或多个表的数据。
  当我们查询一个视图的时候，其实就是在查询‘视图的查询的结果集’，这个结果集里有什么列，就能显示什么列。
  视图的本质就是一个查询语句。
  注意：
  1.对视图的删改，当只影响一张表时可执行，影响多张表时不允许执行。
  2.对视图的增操作，当只影响一张表且视图内包含该表所有不允许为空的列，则可执行；若影响多张表则不可执行。
  3.视图一般只用来做查询。
  [创建视图]
  CREATE VIEW 视图名 AS 查询语句
  [修改已存在视图]
  ALTER VIEW 视图名 AS 查询语句

• 【局部变量】 ： 一个查询窗口中的变量，以 @ 作为前缀，必须先声明然后赋值

  1.声明变量
  DECLARE @变量名 类型
  2.赋值
  SET @变量名=值
  SELECT @变量名=值
  SELECT @变量名=列名 FROM 表 WHERE 条件
  3.取值
  PRINT @变量名
  注：
  1.变量的默认值为 NULL （NULL不代表指针，只代表数据库不知该项为何值）
  2.定义+赋值 ： DECLARE @IDA INT = 3

  [输出变量的值]
  1.SELECT 以表格的方式输出，可以同时输出多个变量
  2.PRINT 以文本的方式输出，一次只能输出一个变量的值

• 【全局变量】 ： 由系统定义和维护，我们只能读取，不能修改变量的值，以 @@ 作为前缀

  常用系统变量：
  @@ERROR 最后一个T-SQL错误的错误号
  @@IDENTITY 最后一次插入的标识值
  @@ROWCOUNT 受上一个SQL语句影响的行数
  @@LANGUAGE 当前使用的语言的名称
  @@MAX_CONNECTIONS 可以同时创建的最大连接数
  @@SERVERNAME 本地服务器的名称
  @@VERSION SQL-Server的版本信息
  @@TRANCOUNT 当前连接打开的事务数

• 【IF ELSE】

  同C#中的IF ELSE。
  格式如下：
  IF( 表达式 )
  BEGIN
  [一条或多条语句]
  END
  ELSE
  BEGIN
  [一条或多条语句]
  END

• 【WHILE】

  同C#中WHILE循环。
  格式如下：
  WHILE( 条件表达式 )
  BEGIN
  [一条或多条语句]
  END
  注：CONTINUE 语句表示结束本次循环
  BREAK 语句表示退出整个循环

• 【存储过程】
  格式：
  [创建存储过程] ：
  CREATE PROC 过程名
  [一个或多个形参，可有默认值，可定义输出参数OUTPUT]
  AS
  BEGIN
  [一条或多条执行语句]
  END
  [修改存储过程] ：
  ALTER PROC 已存在的过程名
  [一个或多个形参，可有默认值，可定义输出参数OUTPUT]
  AS
  BEGIN
  [一条或多条执行语句]
  END
  [调用存储过程] ：
  EXEC [过程名] [需要传入的参数]
  EXEC [过程名] [@参数1=值，@参数2=变量，@参数3=变量 OUTPUT]
  注：
  1.常见存储过程格式也可将 BEGIN END 替换成在语句结束后的 GO

• 【事务】
  格式：
  BEGIN TRANSACTION –开始事务（告诉数据库，事务启动）
  [一条或多条执行语句]
  COMMIT TRANSACTION –提交事务（提交修改，保存修改）—成功
  ROLLBACK TRANSACTION –回滚事务（放弃事务，放弃操作）—失败
  注：
  1.每一条语句执行之后，系统都会更新@@ERROR变量的值，如果出现错误，值就是错误号，若没有出现错误则值为0
  2.系统在一个事务里不会自动累加错误号，因为错误号都是有意义的，不能随便乱赋值，所以我们要手动累加错误号，才能判断事务执行是否发生错误，若发生错误则回滚错误，若无错误则提交事务
  3.事务可配合if..else使用，如下实例：

        DECLARE @zhuan INT        SET @zhuan=99999        DECLARE @sumerr INT = 0        BEGIN TRANSACTION        UPDATE bank SET balance=balance-@zhuan WHERE cId='0001'        SET @sumerr=@sumerr+@@ERROR        UPDATE bank SET balance=balance+@zhuan WHERE cId='0002'        SET @sumerr=@sumerr+@@ERROR        IF(@sumerr<=0)        BEGIN        COMMIT TRANSACTION        END        ELSE        BEGIN        ROLLBACK TRANSACTION        END