SQL与ADO.NET笔记总纲

1.T-SQL语句 { (1) use --use切换数据库的当前使用状态 在不适用use时，系统默认的是master数据库，要切换到自己的数据库 use  数据库名； (2) go --go表示将前面的SQL打一个包，一起执行 --go不属于T-SQL，它不是SQL语句的一部分 --go属于sqlcmd控制台命令 (3) create  <1>--创建数据库  --create database 数据库名  create database MyDatabase  go    --创建数据库后边要带一个go，那样系统里面才能有数据库，然后才能执行下面的操作  <2>--创建数据库并指定路径，大小等  create database 数据库名  on  (  name='数据库逻辑名', --既数据库的名字  filename='文件名.mdf', --既创建的路径。  size=大小, --单位可以是M  filegrowth=增长, --M为单位，或%为单位  maxsize...  --指定文件可增大到的最大大小  )  log on  (  name='数据库逻辑名_log',  filename='文件名.ldf', --log database file  size=大小, --单位可以是M  filegrowth=增长, --M为单位，或%为单位  maxsize...--指定文件可增大到的最大大小  )  create database MyDataBase  on  (  name = 'MyDataBase',  filename= 'G:\db\MyDataBase.mdf'  )  log on  (  name = 'MyDataBase_log',  filename='G:\db\MyDataBase.ldf'  )  <3>--建表  --create table 表名  --(  -- 字段名 类型名,  -- 字段名 类型名  --)  (4)drop  <1>--删除数据库drop database 数据库名

(5)db_id --检查数据库是否存在 --db_id('数据库名') if (DB_ID('MyDataBase')) is not null  drop database MyDatabase;

(6)OBJECT_ID --检查表是否存在的方法 --if (OBJECT_ID('表名', 'U')) is null --create table 表名 --( -- id int --)

(7)--增insert into 表名(字段名) values(值); insert into  tblClass(classId, className, classDescription) values  (1,       'DotNet基础', '基础的课程');

(8)--删除数据 --delete from 表名 where 条件 delete from tblClass (9)--修改数据 --update 表名 set 字段名 = 值 where 条件 update tblClass set className = 'DotNet高级'; (10)--查询数据 select * from tblClass --将字段一个个列出来，不要使用*，这是一个不太好的习惯 --表名，建议使用"架构名.表名"描述(Schame) (11)--清除表数据 --truncate table dbo.tblStudent (11)--添加列的别名 1.列 as 别名    （列可以使自己定义的'  '） ClassName  as  教室名字， 2.列 （空格） 别名 ClassName 教室名字， 3.别名=列 教室名字=ClassName, (12)--排序，使用 order by 字段名     select * from 表名 order by EmpAge,  默认升序     --在后面标注ASC和DESC表示 升序和降序   select * from 表面 order by EmpAge desc   (13)--TOP  紧跟select 假如要选出3个人 select top 3   假如只要前三个人   select top(3)* from 表面 order by EmpAge desc,   取出前3%   top(3) percent   (14)--Distinct 去除重复的数据  select distinct* from 表名   这里的*不能使用，因为重复数据的id不同，所有要使用各个字段名。   (15)--group by   group by 字段名   group by 中有的字段才能出现在Select     group by 对数据进行分组，但是应注意分组的数据才允许select检索   group by 应写在from后面，但写在order by 前面. (16)--  between and    表示条件范围   EmpAge between 18 and 23   (17) --in    字段 in(数值)   EmpAge in(17,18,21)     Emp Age in(18,21)   or   EmpAge between 5  and 16   (18)--模糊查询   _单个字符   % 多个字符   使用like   (19)--转义   T-SQL转义用[]   在方括号中，[^]也表示否定，就是不出现这个元素。、   name like '[^赵]%'   name not like '赵%'    (20)-- SQL逻辑   在SQL中，使用三值逻辑(true,false,unknow)   SQL中<> 不等于，在SQLServer 中允许！=    (21)--SQL 执行顺序    from -> where ->group by-> select-> having   (22)--空值处理 查询所有生日为null的姓名 -> select Fname from T_Student Fbirthday is null -> 判断null只能使用is或is not is null和is not null -> 函数 isnull(字段名, 替换值) 查询时专门为空值的字段显示处理 select isnull(字段,123) from 表名 -> 空字符串与null含义不同，空字符串依旧有内容 处理空字符可以使用len()函数和is null

(23)--having语句 ->having是group by的条件对分组后的数据进行筛选（与where类似，都是筛选，只不过having是用来筛选分组后的组的。） -> 在where中不能使用聚合函数，必须使用having，having要位于group by之后， -> 查询班级人数超过三个人的班级 select FClassId,count(FName) from T_Student group by FClassId having count(FName)>3 -> 注意having中不能使用未参与分组的列，having不能替代where。作用不一样，having是对组进行过滤。

(24)--联合结果集 -> union就是将多个结果集合并成一个结果集 -> 查询语句1 union 查询语句2 -> union会自动合并重复的数据 -> union all会保留重复数据 一般都是使用union all，效率会高点 -> 联合需要注意类型一致 cast()函数可以实现数据的类型转换 cast(1234 as varchar(10))

} 2.约束 {

数据库约束是为了保证数据的完整性(正确性)而实现的一套机制 非空约束 NOT NULL 主键约束 (PK)primary key constraint 唯一且不为空 唯一约束 (UQ)unique constraint 唯一，允许为空，但只能出现一次 默认约束 (DF)default constraint 默认值 检查约束 (CK)check constraint 范围以及格式限制 外键约束 (FK)foreign key constraint 表关系

--手动添加约束（设计器） -> 单击右键 -> 设计表 -> 主键 -> 唯一约束 -> 右键 -> 索引键 -> 添加 -> 常规 -> 唯一键 -> 命名“UQ_表名_字段名” -> 检查约束 -> 右键 -> check约束 -> 添加 -> 表达式 命名“CK_表名_列名”

--T-SQL实现添加约束 <1>新建表时添加约束 id int primary key,--主键约束 name nvarchar(10) unique, --唯一约束 age int check(age>=0 and age<=100),--检查约束 jiondate datetime default(getdate()) --默认约束 Fid int foreign key references MainTable(Fid) --外键约束

<2> -- 使用修改表结构方式实现 -> 修改表结构使用alter table -> alter table 表名 add constraint 约束名 约束类型与条件 -> 主键约束：primary key(字段) -> 唯一约束：unique(字段) 注意为空与空字符串 -> check约束：check(约束表达式) -> 默认约束：default(值) for 字段名

--添加检查约束 alter table T_CreateConstraint2 add constraint CK_CreateConstraint2_age  check ( age>=0 and age<=100 ) --添加主键约束 alter table T_CreateConstraint2 add constraint PK_CreateConstraint2_id  primary key(id)

--添加唯一约束 alter table T_CreateConstraint2 add constraint UQ_CreateConstraint2_name unique(name)

--添加默认约束

alter table T_CreateConstraint2 add constraint DF_CreateConstraint2_joinDate default (getdate()) for jionDate

--添加外键约束 alter table ForTabe1 add constraint FK_ForTable1_MainTable1_Fid foreign key(Fid) references MainTable1(Fid)

-- 添加多个约束（逗号隔开） alter table 表名 add constraint 约束名 约束类型与条件 … …

--删除约束 alter table 表名 drop constraint 约束名 可以删除多个约束，用,分隔开

} 3.函数 {  1.--聚合函数（重要）    --求总数   select COUNT(字段名字EmpName) from 表名   where 字段名字='';(EmpName='张三1')      --求最大最小平均值   select MAX(字段名),MIN(字段名),avg(字段名) from 表名   select MAX(字段名)over(),EmpName from 表名     聚合函数的使用常常与分组一起使用     --聚合函数的一个注意事项   create table tblScore   (   id int identity(1,1) primary key,   math int,   english int,   chinese int   )   insert into tblScore(math,english,chinese) values(52,67,62);   缺考了，则不写，显示的为NULL   select AVG(math) from tblScore   求平均值时，值为NULL的人不算在总人数中 2. --字符串函数 --求字符串长度 LEN('我是一个字符串') --字节数 DATALENGTH('我是一个字符串') --转换成小写 lower() --转换成大写 upper() --去掉左边空格 LTrim() --去掉右边空格 RTrim() --去掉两边空格 Rtrim(LTrim()) --截取子字符串 substring() select 'abcdefg',SUBSTRING('abcdefg',1,3)

3.--日期函数 --获得时间的部分信息 select YEAR('2012-07-11'), MONTH('2012-07-11'), DAY('2012-07-11') --获得年龄 year((Getdate()) -Year(birthday)) --获得30天后的时间 select GETDATE(),DATEADD(DAY,30,GETDATE()) --时间求差 select datediff(SECOND,'2012-7-11 11:47:25','2012-7-11 11:48:59') --数据转换 select '2012-12-21',CONVERT(nvarchar(20),GETDATE(),10)

--类型转换函数 CAST ( expression AS data_type) CONVERT ( data_type, expression,[style]) Select ‘您的班级编号’+ 1  错误这里+是数学运算符 SELECT FIdNumber, CAST(RIGHT(sNo,3) AS INTEGER) as 后三位的整数形式, CAST(RIGHT(sNo,3) AS INTEGER)+1 as 后三位加1, CONVERT(INTEGER,RIGHT(sNo,3))/2 as 后三位除以2 FROM student 对日期的转换。转换成各种国家格式的日期。 select convert(varchar(20),getdate(),104) Style的格式，查sql帮助。(输入convert函数查询) 将日期转换为指定格式的字符串。日期→字符串

} 4.ADO.NET {

<1> --SqlConnection -> 构造方法提供连接字符串 -> 属性提供连接字符串 -> 连接字符串 -> data source=实例名;initial catalog=数据库名;integrated security=true/user id=…;password=…; -> server=实例名;database=数据库名;integrated security=true/uid=…;pwd=…;

->当使用conn.open()时，应该为   if (conn.State == ConnectionState.Closed)                     {                         conn.Open();                     } <2>--SqlCommand 执行SQL语句需要知道在哪儿执行 -> SqlCommand需要提供SQL语句和连接通道 -> 常用三个方法 -> ExecuteNonQuery() 处理增、删、改，返回受影响行数 -> ExecuteScalar() 处理查询，返回首行首列 -> ExecuteReader() 处理查询，返回DataReader对象 <3>--ExecuteScalar > 使用ExecuteScalar()执行SQL语句，返回第一行第一列，object类型 -> 转换使用Convert，不要使用强转 -> 使用ExecuteScalar()一般执行聚合查询 -> 使用ExecuteScalar()亦可查询select getdate() -> 在添加数据的时候，得到自动增长id -> 在value前使用output inserted.主键字段 <4>--ExecuteReader 查询多行数据，返回一个DataReader对象 -> HasRows属性表示查询结果 -> 提供连接通道用来持续得到数据 -> 并未将数据全部倒入到内存 -> 如何处理DataReader -> 使用while循环，调用Read方法 -> 调一次，查一次 -> GetString()、GetInt32()、GetOrdinal()方法 -> 使用索引，索引器有重载 -> DataReader必须保证SqlConnection处于连接状态 --使用GET方法读出数据，既GET（类型名）（列的索引）     SqlDataReader reader = cmd.ExecuteReader();      if (reader.HasRows)          {           reader.Read();            // reader.GetBoolean(列)           int id = reader.GetInt32(0);           string name = reader.GetString(1);           string pwd = reader.GetString(2);           Console.WriteLine("{0}\t{1}\t{2}", id, name, pwd);           } --读出多行数据时，使用while(reader.read()) --使用reader[索引]读取数据 ，reader["name"]

<5>数据集dataset    DataSet ds = new DataSet("MyData1");  --就是一个数据库  --里面可以存表     DataTable dt = new DataTable("MyTable1");  -- 创建列   DataColumn dc1 = new DataColumn("id", typeof(int));   dc1.AutoIncrement = true;   dc1.AutoIncrementSeed = 1;   dc1.AutoIncrementStep = 1;  -- 将列加到table中  dt.Columns.Add(dc1);  dt.Columns.Add(dc2);  ds.Tables.Add(dt);  --来一行数据   DataRow r1 = dt.NewRow();   r1["name"] = "张三";   ataRow r2 = dt.NewRow();   r2["name"] = "李四";   DataRow r3 = dt.NewRow();   r3["name"] = "王五";   dt.Rows.Add(r1);   dt.Rows.Add(r2);   dt.Rows.Add(r3);   --数据创建好了，现在想遍历这个数据             foreach (DataRow r in dt.Rows)             {                 Console.WriteLine("{0}\t{1}", r["id"], r["name"]);             } <6>Adapter   SqlDataAdapter sda = new SqlDataAdapter(                     @"select top 10 * from Person order by Fid",                     @"server=.\sqlexpress;database=MyDataBase;uid=sa;pwd=123"                 );            -- Adapter有一个方法Fill             DataSet ds = new DataSet();             sda.Fill(ds);             ds.WriteXml("1.xml");

} 5.添加配置文件 {             // -> 添加应用配置文件             // -> 添加connectionString节点(name,connectionString)             // -> 添加引用Configuration             // -> 使用静态类ConfigurationManager <?xml version="1.0" encoding="utf-8" ?> <configuration>   <connectionStrings>     <add name="sql" connectionString="Data Source=.\sqlexpress;Initial Catalog=dataInOut;Integrated Security=True"/>   </connectionStrings> </configuration>

static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString; } 6.参数化查询 { --由于蛀虫漏洞的存在，在执行SQL语句时，应该在写SQL语句时使用变量 select count(*) from tblLogin where fname=@name and fpwd =@pwd -- 使用查询的时候需要为变量赋值   cmd.Parameters.Clear();   cmd.Parameters.Add(new SqlParameter("@name", txtUid.Text.Trim()));   cmd.Parameters.AddWithValue("@pwd", txtPwd.Text.Trim()); --parameters本身是一个集合，所以在循环等要执行clear方法 } 7.数据绑定 {       SqlDataAdapter sda = null;         DataSet ds;         private void btnInput_Click(object sender, EventArgs e)         {             // 导入数据             ds = new DataSet();             sda = new SqlDataAdapter(                     @"select * from Person",                     @"server=.\sqlexpress;database=MyDataBase;uid=sa;pwd=123"                 );             sda.Fill(ds);             dgw.DataSource = ds.Tables[0];    --将ds数据集中的第一个表的数据放入DataGridView中   }

} 8.SQLHelper {

-- 常用查询 -- ExecuteNonQuery()    返回int -- ExecuteScalar()      返回object -- ExecuteReader()      返回SqlDataReader对象 -- Adapter              返回DataSet

( namespace _05SQLHelper {     static class SQLHelper  --定义静态类     {         static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString;   --这里定义静态的连接字符串。 <1> --封装ExecuteNonQuery         public static int ExecuteNonQuery(string commandText, params SqlParameter[] para)         {--传递2个参数，一个是SQL语句，一个是可变的Sqlparameter 数组             using (SqlConnection conn = new SqlConnection(strConn))             {                 using (SqlCommand cmd = new SqlCommand(commandText, conn))                 {                     if (para != null && para.Length != 0)                     {                         cmd.Parameters.AddRange(para);                     }      --这里判断数组里有没有值，有的话给Parameters集合添加值。      --这里的Parameters其实是一个键值对集合，通过键查到值，然后传入SQL语句中                     if (conn.State == System.Data.ConnectionState.Closed)                     {                         conn.Open();                     }                     return cmd.ExecuteNonQuery();                 }             }         }  }   --使用ExecuteNonQuery方法             string sql = @"insert into dbo.T_test(name,gender) values(@name,@gender)";             SqlParameter[] par =             {                 new SqlParameter("@name",typeof(string)),                 new SqlParameter("@gender",typeof(string))             };             par[0].Value = "李四1";             par[1].Value = "女";             int res = SQLHelper.ExcuteNonQuery(sql, par);             Console.WriteLine(res); <2>--封装ExecuteScalar    public static object ExecuteScalar(string commandText, params SqlParameter[] para)  --返回object类型数据         {             using (SqlConnection conn = new SqlConnection(strConn))             {                 using (SqlCommand cmd = new SqlCommand(commandText, conn))                 {                     if (para != null && para.Length != 0)                     {                         cmd.Parameters.AddRange(para);                     }                     if (conn.State == System.Data.ConnectionState.Closed)                     {                         conn.Open();                     }                     return cmd.ExecuteScalar();                 }             }         } --使用ExecuteScalar             string sql1 = @"select count(name) from dbo.T_test";             object res1 = SQLHelper.ExcuteScalar(sql1);             Console.WriteLine(res1);     <3>--封装ExecuteReader   public static SqlDataReader ExecuteReader(string commandText, params SqlParameter[] para)--返回SqlDataReader类型         {             SqlConnection conn = new SqlConnection(strConn);  --由于读出数据的时候conn要保持连接，所以不能using             using (SqlCommand cmd = new SqlCommand(commandText, conn))             {                 if (para != null && para.Length != 0)                 {                     cmd.Parameters.AddRange(para);                 }                 if (conn.State == System.Data.ConnectionState.Closed)                 {                     conn.Open();                 }                 return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);      --这里调用ExecuteReader的构造方法，当ExecuteReader执行结束后自动关闭conn连接             }         } --使用ExecuteReader             string sql = @"select id, name, pwd from tblTest";             SqlDataReader reader = SQLHelper.ExecuteReader(sql, null);             using (reader)  --这里的reader要using,确定在使用完后释放资源和关闭连接。             {                 if (reader.HasRows)                 {                     while (reader.Read())                     {                         Console.WriteLine("{0}\t{1}\t{2}", reader[0], reader[1], reader[2]);                     }                 }             } <4>封装DataAdapter    public static DataSet DataAdapter(string commandText, params SqlParameter[] para)  --返回Data数据集         {             DataSet ds = new DataSet();--建立一个数据集             using (SqlDataAdapter sda = new SqlDataAdapter(commandText, strConn))  --实例化DataAdapter             {                 if (para != null)                 {                     sda.SelectCommand.Parameters.AddRange(para);                    }                 sda.Fill(ds);             }             return ds;         }

--使用DataAdapter             DataSet ds = SQLHelper.DataAdapter("select id, name, pwd from tblTest", null);             foreach (DataTable dt in ds.Tables)  --遍历表，获得表信息             {                 Console.WriteLine("{0}显示数据", dt.ToString());                 foreach (DataRow row in dt.Rows)  --遍历行，获得行信息                 {                     Console.WriteLine("{0}\t{1}\t{2}", row[0], row[1], row[2]);                 }             } <5>. -- ExecuteNonQuery 二次封装    public static int ExecuteNonQuery(string commandText, params object[] objs)         {             // 封装参数             string regex = @"@\w+";  --正则表达式匹配出变量             MatchCollection ms = Regex.Matches(commandText, regex);             List<SqlParameter> list = new List<SqlParameter>();  --定义泛型类型为SqlParameter             if (ms.Count != objs.Length)             {                 throw new Exception("SQL语句中变量与参数不匹配");   --查看匹配出来变量数量的是否等于传入进来的数组里的变量的数量             }             for (int i = 0; i < ms.Count; i++)             {                 list.Add(new SqlParameter(ms[i].Value, objs[i]));              }             return ExecuteNonQuery(commandText, list.ToArray());  --进行方法重载         } --使用             string sql = @"insert into tblTest(name, pwd) values(@n,@p);";             int res = SQLHelper.ExecuteNonQuery(sql, "张三", "12345");             Console.WriteLine("受影响行数为{0}", res); }

} 9.case函数的用法 {

-> 使用类似switch-case与if-else if -> 语法 --类似switch-case case [字段]     when 表达式 then 显示数据     when 表达式 then 显示数据     else 显示数据 end

select stuId, stuName, stuAge,  case stuGender   when 1 then '男'   when 0 then '女'  end as gender, scId from Student

--类似if-else if select stuId, stuName, stuAge,  case   when stuGender = 1 then '男'   else '女'  end as gender, scId from Student

-> then 后数据类型要一致

} 10.子查询 {

<1>---> 将查询出来的结果括起来作为一个查询的条件或显示结果(单值与列值) select * from dbo.Scor where stuId = (select stuId from Stu where stuName='张三')  --单值使用where <2>--多值查询 select * from dbo.Scor where stuId in (select stuId from Stu where stuGender='m')  --多值使用in <3>--几个注意点 子语句是作为一个整体，要用括号括起来 子语句查询得到的结果只能为一个(单值查询)或者一列(多值查询)

<4>---> 查询学生表中最高分与最低分和平均分 select (select MAX(scDataBase) from Scor) as 最高分, (select MIN(scDataBase) from Scor) as 最低分, (select AVG(scDataBase) from Scor) as 平均分 <5>---> 快速删除 delete from Person where Fid in (select Fid from Person where FAge = 20)

} 11.表连接Join { <1> --交叉连接(笛卡尔积) 语法：连接都是在from阶段进行的 交叉连接只需要写成 from 表1 cross join 表2 select t1.num, t2.num from tblNum1 as t1 cross join tblNum2 as t2 order by t1.num --交叉连接生成数字辅助表 create table tblNum (  num int ) go insert into tblNum values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

select  t1.num*10+t2.num +1 as num from  tblNum as t1  cross join  tblNum as t2 <2>--内连接inner join 首先进行交叉连接，然后进行筛选(on) 然后查看是否有为null的数据， 如果有为null的数据将删除这一行数据 --语法 select  t1.字段1，t1.字段2，t2.字段3.... from  表1 as t1  inner join  表2 as t2  on 条件  如t1.外键=t2.主键 --实例 select  t1.stuId, t1.stuName,   t2.stuId,t2.scId from  Stu as t1  inner join  Scor as t2  on t1.stuId = t2.stuId <3>外连接(左外连接、右外连接) --先执行笛卡尔积(cross join) --利用条件筛选 --依据连接属性(以哪一张表外主表)， --将主表中有的数据而另一张表中没有的数据加进来， --没数据的像默认为null --语法 select  t1.字段1，t1.字段2，t2.字段3.... from  表1 as t1  left(right) join  表2 as t2  on 条件  如t1.外键=t2.主键

} 12.表表达式  {

表表达式就是将一个查询得到的表结果作为一个新的表给其他语句进行使用 <1>--> 派生表(临时) 就是将一个select查询语句查出的表值结果集作为数据源进行第二次查询 作为数据源的查询语句，绝对不能包含order by语句(除非有top语句) 作为数据源的查询语句，一定得包含列名 派生表只是临时的，并不存在实际这样的表，只是当使用这个派生表时，进行SQL运算 派生表里面还可以再有派生表 <2>--公共表表达式(CTE) 发现派生表嵌套查询，比较混乱 一般将表定义在外边，其后直接引用 --语法 with 表名 as (  查询语句 ) 使用该表名的查询语句；

with t as (  select top 100 percent   t1.stuId,   t1.stuName,   YEAR(GETDATE())-Year(t1.stuBirthday) as stuAge,   case t1.stuGender    when 'm' then '男'    when 'f' then '女'   end as stuGender,   t1.stuPhone,   t1.stuEmail,   t1.stuAddress,   t1.stuSchool,   t1.stuInputTime,      t2.cName, t2.cDescription  from   Stu as t1   inner join   Course as t2   on t1.cId = t2.cId  where   t1.stuIsDel = 0  order by t1.stuId ) select * from t; go <3>--视图 视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上 视图在操作上和数据表没有什么区别，但两者的差异是其本质是不同:数据表是实际存储记录的地方，然而视图并不保存任何记录，它存储的实际上是查询语句 相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句） 视图的目的是方便查询，所以一般情况下不能对视图进行增删改 优点： 筛选表中的行 防止未经许可的用户访问敏感数据 降低数据库的复杂程度

--创建视图 create view vw_StuInfo as    SQL语句(得到一张派生表) go --使用试图 select * from vw_StuInfo

} 13.分页 {

--row_number() --row_number() over(order by 字段) --如果Fid是连续的自然数 --利用派生表，为表添加一列的表值集作为数据源 select * from    (  select ROW_NUMBER() over(order by Fid) as id  ) as t where t.id between 11 and 20

--将分页的数据源做成视图 go create view vw_TestFenYe as select ROW_NUMBER() over(order by Fid) as id, Fid,FName,FGender,FAge from Person

select * from vw_TestFenYe where  vw_TestFenYe.id  between (2-1)*3+1 and 2*3

select * from Person

--创建一个分页的表值函数(了解) go create function fn_FenYeTest (@index as int, @count as int) returns table as return     select * from vw_TestFenYe  where  vw_TestFenYe.id  between (@index-1)*@count+1 and @index*@count go

--使用这个表值函数 select * from fn_FenYeTest(2,10)

--总页数 select ceiling(7.0/3)

} 14.变量 {

--第一种方式 --声明变量declare declare @val int --再赋值set set @val = 10 --再使用 select @val

declare @index int declare @count int set @index = 3 set @count = 6

--第二种方式 Declare @count int select @count = COUNT(*) from Stu print @count

--在SQL2008中，可以这样声明 declare @num int = 101 select @num

--变量分为： --局部变量： 局部变量必须以标记@作为前缀 ，如@Age int 局部变量：先声明，再赋值 --全局变量(系统变量)： 全局变量必须以标记@@作为前缀，如@@version 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值 select @@VERSION select @@error select @@MAX_CONNECTIONS

} 15.if.else.while {

<1>--IF(条件表达式)                   BEGIN --相当于C#里的{                语句1  ……   END --相当于C#里的} ELSE  BEGIN     语句1     ……   END <2>--while WHILE(条件表达式)   BEGIN --相当于C#里的{     语句     ……  continue     BREAK   END --相当于C#里的}

) 16.事务 ( <1>--先看一个例子，有2个人，甲在银行有1000，乙在银行有10快，银行规定存储的钱不能少于10，现在甲要向乙汇款1000 create table bank (  cId char(4) primary key,  balance money,   --余额 )

alter table bank add constraint CH_balance check(balance >=10)

go --delete from bank insert into bank values('0001',1000) insert into bank values('0002',10) go

select * from bank --这里执行语句时，由于第一条语句有大于10的约束，所有执行失败，但是第二条语句确执行成功了 update bank set balance=balance - 1000 where cid='0001' update bank set balance=balance + 1000 where cid='0002' --那么就乙就多了1000了，而甲的钱没少 --这时，就需要有一个事务，来对这2条语句进行约束

 

<2>--事务：同生共死 指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行 这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行

<3>--语法步骤： 开始事务：BEGIN TRANSACTION 事务提交：COMMIT TRANSACTION 事务回滚：ROLLBACK TRANSACTION

<4>--判断某条语句执行是否出错： 全局变量@@ERROR； @@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；          例：SET @errorSum=@errorSum+@@error    --实例： begin transaction   declare @MyError int set @MyError = 0 update bank set balance=balance - 800 where cid='0001'; set @MyError += @@ERROR update bank set balance=balance + 800 where cid='0002' set @MyError += @@ERROR if(@MyError > 0) begin  rollback end else begin  commit end --这时候假如语句执行不成功报错，则会自动回滚，成功的语句也无效了

<5>--事务的异常处理 begin transaction begin try  update bank set balance=balance - 1000 where cid='0001'  update bank set balance=balance + 1000 where cid='0002'  commit      --没有出现异常则提交 end try begin catch  rollback   --出现异常进入catch ,进行回滚 end catch

} 17.存储过程.就像数据库中运行方法 {

<1> 和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。 前面学的if else/while/变量/insert/select 等，都可以在存储过程中使用 优点： 执行速度更快 – 在数据库中保存的存储过程语句都是编译过的 允许模块化程序设计 – 类似方法的复用 提高系统安全性 – 防止SQL注入 减少网络流通量 – 只要传输 存储过程的名称 系统存储过程 由系统定义，存放在master数据库中 名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头。 自定义存储过程 由用户在自己的数据库中创建的存储过程

<2>--语法 create proc usp_存储过程名字 as begin  T-SQL语句 end

CREATE  PROC[EDURE]  存储过程名     @参数1  数据类型 [= 默认值] [OUTPUT],     @参数n  数据类型 [= 默认值] [OUTPUT]     AS       SQL语句 参数说明： 参数可选 参数分为输入参数、输出参数 输入参数允许有默认值 EXEC  过程名  [参数]

------------------ create proc usp_Demo as  begin   select student.sId, sName, sAge, sSex, sNo, sBirthday, sClassId,english from Score inner join Student on Score.studentId = Student.sId where english < 60  end

<3>--修改存储过程 alter Proc usp_ZZ as begin     T-SQL end

<4>--把事务变为存储过程,带参数 create Proc usp_ZZpara @money money   --定义变量 as begin  begin transaction  --开启以后，其后的所有代码将作为一个整体  --并且等待提交commit，rollback  begin try   update bank set balance=balance - @money where cid='0001'   update bank set balance=balance + @money where cid='0002'   commit  end try  begin catch   rollback  end catch end

-------------------------------- --带输入参数 create proc usp_Demo1  @level int as  begin   select student.sId, sName, sAge, sSex, sNo, sBirthday, sClassId,english from Score inner join Student on Score.studentId = Student.sId where english < @level  end   ---------------------------------- exec usp_Demo1 45 --参数默认值 create proc usp_Demo2  @level int = 60 as  begin   select student.sId, sName, sAge, sSex, sNo, sBirthday, sClassId,english from Score inner join Student on Score.studentId = Student.sId where english < @level  end     <5>--使用带参数的存储过程 无参数的存储过程调用： Exec usp_upGrade 有参数的存储过程两种调用法： EXEC usp_upGrade2 60,55 ---按次序 EXEC usp_upGrade2 @english=55,@math=60 --参数名 参数有默认值时： EXEC usp_upGrade2 --都用默认值 EXEC usp_upGrade2 1  --第一个用默认值 EXEC usp_upGrade2 1,5   --不用默认值

exec usp_ZZpara @money=50；

<6>---带有返回值的存储过程 输出参数关键字：OUTPUT declare @a int exec usp_pp @canshu= @a output print @a

--------------------------- exec usp_Demo2 45 ---单输出参数的 create proc usp_Demo3  @level int,@count int output as  begin   select student.sId, sName, sAge, sSex, sNo, sBirthday, sClassId,english from Score inner join Student on Score.studentId = Student.sId where english < @level      select @count = COUNT(*) from Score where english < @level  end

create proc usp_Select1 @count int output   --output修饰代表是返回值 as  select @count = COUNT(*) from vw_TestFenYe

<7>--使用有返回值的存储过程

先定义一个变量 declare @c int 调用存储过程 exec usp_Select1 @c output print @c

} 18.触发器 {

<1>触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。 一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。 <2>触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。 <3>那究竟何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有2种：after(for)、instead of,分别应用于Insert , Update , Delete 事件(DML触发器) <4>deleted表存放由于执行delete或update语句而要从表中删除的所有行。 在执行delete或update操作时，被删除的行从激活触发器的表中被移动（move）到deleted 表，这两个表不会有共同的行。 <5>inserted表存放由于执行insert或update语句而要向表中插入的所有行。 在执行insert或update事物时，新的行同时添加到激活触发器的表中和inserted表中， inserted表的内容是激活触发器的表中新行的拷贝。 说明：update事务可以看作是先执行一个delete操作，再执行一个insert操作，旧的行首先 被移动到deleted表，让后新行同时添加到激活触发器的表中和inserted表中。

<6>语法 create trigger tr_触发器名 on 表名 for 针对什么行为 as  SQL脚本

create trigger tr_触发器名 on 表名 instead of  针对什么行为 as  SQL脚本

这里行为主要有：insert、delete、update <7>触发器-插入

CREATE TRIGGER tr_updateStudent ON score for INSERT AS Begin  declare @sid int,@scoreid int  set @sid = studentId,@ scoreid=sid from inserted  if exists(select * from student where sid=@sid)   print ‘插入成功’  else   delete from score where sid = @scoreId End Insert into score (studentId,english) values(100,100) <8>触发器-删除 CREATE TRIGGER tr_deleteStudent ON student for delete AS begin insert into backupStudent select * from deleted End

Delete from student where sId=1 <9>实例 create table Records (  rId int identity(1,1) primary key,  rType int ,  -- 1存钱  -1 取钱  rMoney money,  userId char(4) )

select * from bank --创建触发器 create trigger tr_Records on Records for insert as  declare @type int,@money money,@id char(4)  select @type = rType,@money=rMoney,@id=userId from inserted    update bank set balance = balance + @money*@type  where  cId = @id

--当插入数据的时候就会引发触发器 insert into Records values(-1,10,'0002')

select * from Records create trigger tr_del on student for delete as  delete from score where studentId in (select sid from deleted) }