sqlserver 综合总结

面向抽象编程
接口-抽象类-父类-具体类
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 别名 
	as
	(
		结果集
	)
	查询



3、 考虑派生表和CTE都是临时的，而且比较臃肿，所以希望将常用的查询结构定义到数据库中
	每次使用的时候，直接使用数据库中的这个对象，这个对象中记录了这个复杂的查询规范
	由数据库执行查询
	
	那么就有了视图(虚拟表)
	
	create view vw_视图名
	as
		查询
		
	注意:视图不具备存储数据的能力，但是使用的时候就好像一张表一样



4、 (*介绍)内联表值函数
	带有参数的试图
		create function fn_名字
		(@参数 类型, ...) returns table
		as
			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;
			go



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]
    AS
	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 
  as 
     insert into bank  select * from deleted;
  go