sql server 综合总结

网络整理 - 08-18

　　面向抽象编程

　　接口-抽象类-父类-具体类

　　1、复习T-SQL

　　-> 数据库的建立、表的建立和约束(DDL)

　　-> 建库

　　if(db_id(数据库名)) is not null

　　drop database 数据库名;

　　create database 数据库名;

　　-> 建表

　　if(object_id('表名', 'U')) is null

　　create table 表名

　　(

　　字段类型 [约束],

　　字段类型 [约束]

　　);

　　-> 建约束

　　alter table 表名 add constraint 约束名约束条件

　　-> 主键约束(PK_表名_字段名)

　　primary key(字段)

　　-> 默认约束(DF_表名_字段名)

　　default(值) for 字段

　　-> 检查约束(CK_表名_字段名)

　　check(表达式)

　　-> 唯一约束(UQ_表名_字段名)

　　unique(字段)

　　-> 外键约束(FK_外键表_主键表_字段名)

　　alter table 外键表 add

　　constraint FK_外键表_主键表_字段名

　　foreign key(字段名) references 主键表(字段名);

　　-> 数据库操作语言(DML)

　　-> 基本语言

　　增

　　insert into 表名(字段列表) values(值列表);-- 表是存在的

　　select 字段列表 into 新表名 from 旧表; -- 要求新表不存在

　　insert into 表名(字段列表) select查询;

　　删(主外键关联的时候，删除有时会出问题)

　　delete from 表名 where 条件;

　　truncate table 表名;-- 所有的数据归零

　　drop database 数据库;

　　drop table 表名;

　　改

　　update 表名 set 字段=值，字段=值， … where条件;

　　查(***)

　　-> from子句

　　-> where子句

　　-> group by子句

　　-> having子句

　　-> select top distinct 以及运算表达式

　　-> order by子句

　　核心记住流程

　　-> 高级部分

　　case语法

　　-> if else if结构

　　case

　　when 表达式1 then 显示1

　　when 表达式2 then 显示2

　　…

　　else 显示n

　　end

　　-> switch-case结构

　　case 字段

　　when 值1 then 显示1

　　when 值2 then 显示2

　　…

　　else 显示n

　　end

　　连接

　　-> 交叉联接

　　ANSI-89的语法：select * from 表1, 表2;

　　ANSI-92的语法：select * from 表1 cross join 表2;

　　-> 内连接

　　ANSI-89的语法：select * from 表1, 表2 on 条件;

　　ANSI-92的语法：select * from 表1 inner join 表2 on 条件;

　　-> 外连接

　　ANSI-92的语法：

　　select * from 表1 left join 表2 on 条件;

　　select * from 表1 right join 表2 on 条件;

　　-> 自连接、多表连接

　　表1

　　inner join

　　表2

　　on 条件

　　inner join

　　表3

　　on 条件

　　子查询

　　将一个查询的结果作为另一个查询的条件

　　分清出内部查询(子查询)和外部查询

　　-> 独立标量子查询字段 = (子查询)

　　-> 独立多值子查询字段 in (子查询)

　　表表达式

　　-> 派生表

　　将查询的"结果集"作为数据源 --不能使用order by

　　select * from

　　(

　　select

　　row_number() over(order by stuId) as num,

　　from

　　Student

　　) as tbl

　　where

　　tbl.num between @count * (@page - 1) + 1 and @count * @page;

　　2、公用表表达式(CTE)

　　就是将一个查询得到的结果集用一个表的形式临时存放，并用一个变量名指定

　　后面再查询直接使用变量名即可

　　with 别名

　　(

　　结果集

　　)

　　查询

　　3、考虑派生表和CTE都是临时的，而且比较臃肿，所以希望将常用的查询结构定义到数据库中

　　每次使用的时候，直接使用数据库中的这个对象，这个对象中记录了这个复杂的查询规范

　　由数据库执行查询

　　那么就有了视图(虚拟表)

　　create view vw_视图名

　　查询

　　注意：视图不具备存储数据的能力，但是使用的时候就好像一张表一样

　　4、 (*介绍)内联表值函数

　　带有参数的试图

　　create function fn_名字

　　(@参数类型， …) returns table

　　return 查询

　　5、 SQL中的变量与流程控制

　　-> SQL是一个脚本语言，可以认为基本的编程语句都支持

　　-> 定义变量

　　declare @变量名类型名;

　　-> 赋值

　　set @变量名 = 值;

　　-> 补充

　　使用select进行赋值

　　select @变量=字段 from 表名…(支持多值查询)

　　使用子查询进行赋值

　　set @变量 = (单值子查询); (只支持单值查询)

　　>sql 2008 新的语法

　　declare @name varchar(5)='大鑫';

　　select @name;

　　-> 判断

　　if(bool表达式) begin end就等于程序中的大括号()

　　begin

　　-- 语句

　　end

　　else if(表达式)

　　begin

　　-- 语句

　　end

　　else

　　begin

　　-- 语句

　　end

　　-> 循环

　　while(bool表达式)

　　begin

　　-- 语句

　　end

　　循环练习

　　--求1到100的和

　　declare @jkSum int;

　　declare @i int;

　　set @jkSum = 0;

　　set @i = 1;

　　while (@i <= 100)

　　begin

　　set @jkSum = @jkSum + @i;

　　--set @i = @i + 1;

　　****--sql里面循环执行后要把@i+1 赋值然后执行下一次循环提供的新值

　　set @i += 1;

　　end

　　select @jkSum;

　　6、为什么要有事务，事务是什么

　　事务，就是SQL中一个独立执行过程

　　这结果是会影响到数据库的数据结果的

　　这个事务(执行过程)与其他的执行过程无关

　　事务执行的时候是一个整体，要么全部成功，也么全部失败

　　事务的原子性、持久性、隔离性、一致性(不能违反约束)

　　SQL中每一个SQL语句实际上都是一个事务(隐式事务)

　　事务就是一个最小的执行单位

　　常见的全局变量 @@version, @@error(最后一个错误的T_SQL的信息)

　　@@identity (最后的一个插入表的自增id)

　　//查询中文的错误信息试图

　　select * from sys.messages where where language_id=2052;

　　显示声明事务

　　begin transaction -- 简写begin tran

　　事务内部

　　-- 提交事务或回滚事务

　　commit transaction-- 表示事务内部的所有事情执行成功

　　rollback transaction-- 表示撤销事务内部所有的事情

　　-> 使用try-catch块处理事务中的异常错误

　　begin try

　　end try

　　begin catch

　　end catch

　　7、存储过程就是将一些列的执行步骤记录到数据库中，有点类似于方法

　　-> 无参数，无返回值的存储过程

　　-> 有参数，无返回值的存储过程

　　-> 带有默认参数的存储过程

　　-> 带有默认参数与参数返回值的存储过程

　　声明一个带默认参数@stuNameTemp nvarchar(20)='all'

　　---> []可以有，可以无

　　---> 多条语句必须要加begin end 如果是一条语句可以省略begin end

　　CREATE PROC[EDURE] USP_存储过程名

　　@参数1 数据类型 [= 默认值] [OUTPUT],

　　@参数n 数据类型 [= 默认值] [OUTPUT]

　　BEGIN

　　SQL语句

　　END

　　exec 存储过程参数;

　　exec 存储过程参数名=参数;

　　exec 存储过程参数，参数n output;

　　8、使用ADO.net执行SQL的方法

　　-> 没有返回值的存储过程

　　-> 带有参数返回值的存储过程

　　-> 连接字符串

　　-> SQL语句(存储过程的名字)

　　-> 创建参数

　　如果是执行参数返回值的存储过程

　　定义要返回的参数，不要赋值，设定Direction

　　string sql = "usp_TransMoney";

　　SqlParameter[] ps =

　　{

　　new SqlParameter("@from",from)，

　　new SqlParameter("@to", to)，

　　new SqlParameter("@money",money)，

　　new SqlParameter("@isAccess", SqlDbType.Int)

　　};

　　ps[3].Direction = ParameterDirection.Output;

　　-> 连接通道SqlConnection

　　-> 创建执行对象SqlCommand(设定CommandType)

　　-> 加入参数

　　-> 打开连接

　　-> 执行方法

　　9.触发器

　　->特殊的存储过程。 inserted表和 deleted表 2个临时表

　　insert into back(cid,balance) output inserted.*

　　values('0004',10000);

　　for 或者 after 触发器是执行完才能触发

　　instead of 代替

　　--添加一个触发器，删除所有数据后，在把数据插回来

　　create trigger tr_DelUseInfo on 表名(bank)

　　for delete

　　insert into bank select * from deleted;