SQL Server基础

DB2数据库用OLE连接，如果没有提供对应的类则用它进行连接（Excel，Access）

 

建立多个数据文件速度更快（读取快）

 

存储过程没有返回结果，函数        

 

完整性一般都在代码中实现，而不是数据库

 

数据完整性包括：实体完整性（记录不能重复）、域完整性（数据类型和为空性）、引用完整性（外键）、用户自定义的完整性（业务规则所要求的完整性）

域是列（类型，长度）  实体是行（主键）

 

Nchar与Nvarchar的长度：使用len()函数判断长度时，无论任何情况都是相同的

 

主键分为：天然主键（易于识别，可能重复）；标识列代理主键（标识规范，没有意义不会重复）

 

尽量设置字段不能为空，并为它赋一个默认值

 

PD在物理数据库中设置标识规范、索引。（添加一个索引，指定唯一性，双击那一行，Column，添加列）

 

T-SQL是SQL的加强版：变量说明、流程控制、功能函数

 

检索的字段是数据库中的关键字时，使用中括号括起来[name]

 

“可以使用+把两个字段连接，任何一个字段为null时，结果为null”，SQL中的类型转换 Convert(varchar(5),sb_id)

 

where 中的否定条件改为肯定条件将提高性能（尽量使用肯定条件，=速度最快）。SQL语句前后加上select getdate() 可以判断SQL语句的执行时间

 

使用日期查询时需要注意 0：0：0，between 一般用于日期

 

同配符：_  %   []   [^]

Like会影响性能。如果需要频繁使用like，应启用全文索引

 

Case语句   简单CASE  case sex   when 1 then ‘男’   else ‘女’  end

           布尔CASE  case  when  sex =1 then ‘男’  else ‘女’  end  (when 后面是一个条件组合，当前面的when条件满足时后面的when将不再判断)

 

函数

Isnull函数 isnull(czy_id,0) 如果czy_id为null 则为它赋0（如果第一个参数为空，则返回第二个参数，否则返回自身）

 

时间函数 GetDate() 返回当前系统的时间

         DateAdd(datepart,number,date) 为日期添加 例：DateAdd(dd,1, GetDate())

         DateDiff(datepart,BeginDate,EndDate) 返回两个时间之间的间隔

 

字符串函数 SubString(,begin,end) 截取字符串

           CharIndex(expression1, expression2,startpositon)     找到expression1（字符）在expression2中的位置，从startpositon开始（where charindex(‘ ’,sb_yhxm,1)>0）

         （SQL中如果找不到返回0，C#中找不到返回-1）

         Right(expression,count)  Left(expression,count)  返回左或右的count个字符

         Len（expression） 返回字符串的长度

         Rtrim(expression)  Ltrim(expression)  去掉两边的空格

         Upper(expression) 转成大写    Lower(expression) 转成小写  (SQL 中的比较不区分大小写)

         Replace(source,search,replace)  从source中找到search用replace进行替换(source本身不会改变)

 

数据类型转换函数

  Cast(expression  as  date_type)

  Convert(date_type, expression)

 

数学函数

  ABS() 取绝对值

  Ceiling()  返回大于或等于所给数字表达式的最小整数 （B/S的分页中经常使用）

  Floor()    返回小于或等于所给数字表达式的最小整数

  Power(5,2) 取表达式的幂值

  Round()   四舍五入，（但小数点位数与原数值相同，不足补0）

  Sign()     对于正数返回+1，对于负数返回-1,对于0返回0

  Sqrt()     求平方根

 

 

左外联接和右外联接有时需要用到

自联接是将表与它自己进行联接。这种联接通常用于从递归关系中提取数据

全外联接等价于左外联接和右外联接，消去重复行 Full Join

交叉联接Cross Join 没有限制条件on , 返回两个表的所有可能组合

 

联合与联接不同

联合是将多个结果集放到一张较长的表中

合并 union （列必须完全相同且顺序一致，会消去重复的行）

交叉 intersect 两个结果集的交集

差   except

 

子查询中的共用表达式CTE（SQL2005的新功能）

在查询的开头使用CTE，在接着的一个SQL语句中可以多次使用，相当于声明了一个变量

例 

with cteEmp as

(

   Select * from employee where  depid=1

)

Select * from cteEmp

 

Avg（）求平均数之前先把数据转成float类型 , Avg(cast(col  as   float))

 

Where是针对每一行的   Having针对分组结果筛选

 

批量插入时如果有一条插入错误，会回滚。因为一条SQL语句默认有一个事务处理

 

插入数据时创建表

select(列名)  into<表名>  from<源表名>(新表的结果需要根据设计要求调整，主外键)

 

更新语句中可以使用from (更新时会用到令一张表中的数据)

Update Employee

Set  a.Salary=a.Salary*(1+b.RaiseFactor)

From  Employee a , Department b

Where  a.DepID=b.DepID

 

删除语句中也可以使用From

Delete Employee

From  Employee a , Department b

Where  a.DepID=b.DepID and b.[Name]=’后勤部’

 

Truncate 是彻底删除，不可恢复（没在日志中记录，通常不使用该方法） truncate Table Student

       删除之后标识列从1开始

 

2NF 消除非主属性对码的部分依赖

3NF 消除非主属性对码的传递依赖

 

Sysobjectsb表是系统视图中的一张视图表，用于记录用户创建的表

If exists(select  *  from  Sysobjectsb  where  [Name]=’Team’)

  Drop Table Team

 

约束类型

主键、唯一(允许有一个空值)、检查(Check)、默认、外键

 

设置权限分为三道门

总的安全性——数据库安全性——为用户添加安全对象

 

视图中的ＳＱＬ语句返回的列中不能有重复列

　　使用原则：不能单独使用order by，而应结合Top使用（避免在视图中使用order by）

             只包含用户感兴趣的列

             创建更通用的视图，避免创建专用视图

             避免在更新或删除语句中使用视图，可能造成视图被多次访问，每访问一次视图，就会执行视图中的查询语句，如果的确需要在更新或删除中用到一个查询结果，可以考虑使用临时表

 

索引

  填充因子：FillFactor是指填满索引页空间所占的百分比（通常小于70%）

  组合索引（列的排列顺序非常重要，第一列应该为经常在where语句中出现的列）

  应依据where和order by 创建索引

 

事务

   Begin Transaction

   Begin Try

         Code---------

   End Try

  

   Begin Catch //如果有任何一个事务出错

      If(@@trancount>0)

           RollBack Tran

   End Catch

 

   If(@@trancount>0)

           Commint  Transaction

  事务中最好只写核心任务

  事务更新表的数据最好能一致

 

存储过程

  可以为参数提供默认值

  C#中调用存储过程与调用SQL语句非常类似，只有command对象的类型不同

  Set NoCount ON—取消指出操作影响行数，提高性能

 

函数的目的就是返回值（少用）

Create function FunctionName(bianliang)

Returns  DataType

As

Begin

  Code

  Return

End

Go

函数的调用需要加dbo

自定义函数一致性很差，如果需要将数据库移植到其他平台，就避免自定义函数

 

Go是批处理的标志，将这些语句编译为一个执行单元（任何语法错误都导致整个批处理失败）

 

声明变量后，如果不赋值，则默认值为null。声明之后要有分号

 

@@Error 返回最后一个T-SQL错误的错误号（如果要捕获某个错误，只能写在这个语句的后面）

@@Erroro 获取自增列

@@TranCount  当前连接打开的事务数

 

SQL中只有while循环

 

临时表

Select * into #EmployeeTemp

From employee

创建之后可以直接使用

如果临时表不再需要，就手动将其删除，drop table #EmployeeTemp

临时表与视图相似，但临时表中有数据，只是保存在了内存中，可多次使用

 

动态SQL

Exec Sp_ExecuteSQL

      N ‘select * from Student’  N是不可少的，表示使用的字符串是Unicode编码

与存储过程结合使用

如果查询的表名、条件等需要根据执行时的值来确定，这时考虑使用动态SQL

 

错误处理

Begin try

   Code

End try

 

Begin catch

  Code

End catch

 

触发器（不建议使用）

  触发器隐蔽性太强，不容易发现

  若触发器太多，其他人不知道

 

 

 

 

 

 

 

SQL作业练习

随机返回5条记录（sql server的随机函数newID()和RAND()）

 Select  top  5  *  from  SB  order  by  newid()  （newid()每次返回的值都是不同的，NEWID 对每台计算机返回的值各不相同。所显示的数字仅起解释说明的作用。）

（select  floor(RAND()*100) ---0到99之间的随机数 select ceiling(RAND()*100)---1到100之间的随机数）

删除重复的记录

  先按照某个字段分组；然后留下ID最小的记录，其余的删掉

　 Delete Department

Where DepId Not In(Select Min(DepId) From Department Group by [Name])

求平均工资，但要求去掉最低和最高工资

最高或最低工资可能对应多条记录

找到所有最高工资对应的工资

找到所要最低工资对应的工资

求平均值的时候将上面的工资去掉

找到当前月的第一天和最后一天

用当前日期减当前日期的天加一

Select DateAdd(d,-Day(GetDate())+1,Getdate())

用当前日期加上一个月，再减去下一个月的天数

Select DateAdd(dd, -Day(DateAdd(mm,1, GetDate())),DateAdd(mm,1, GetDate()))

行变列

  有几个需要变成列，就有几个CASE

 Select sum(ISNULL(部门1,0)), sum(ISNULL(部门2,0))

From

(

 Select

Case DeoID

      When 1 Then SUM(Salary)

  End as 部门1,

  Case

      When 1 Then SUM(Salary)

  End as 部门2

 From Employee

 Group by DepID

)

 

 

 

 

 

使用存储过程    通过编程语言生成唯一编号

 

 

计算字符在字符串中出现的次数（sdf,sdfs,gfh,efgh,hjdfg,df）

declare @zfc char(50),@zfc_th char(50)

select @zfc='adf,sdfdfg,dfg,hr,sd,fh,hj,fgh,sd'

select @zfc_th=Replace(@zfc,',','')

select len(@zfc)-len(@zfc_th)

查询水表中的奇数行（以水表ID排序，rownumber）

select *

from (select *,row_number() over(order by SB_ID) as RowNumber from SB) a

where a.RowNumber%2=1

 

 

 

 

删除数据库中所有的表

declare @sql varchar(8000)

select @sql=isnull(@sql+' drop table ','')+'['+name+']'

from (select name from sysobjects where xtype='u')t

exec('drop table '+ @sql)

select * from Team
select * from Student

select *
from Team a,Student b
where a.TID=b.TID

--between在日期中的使用
select * from employee
where DateHire between '2008-11-21' and '2008-11-21 23:23:59:59'

--in 的使用
select * from student
where Address not in('北京')

--通配符
select * from Student where Name like '张%'
select * from Student where Name like '张_'
select * from Student where Name like '[张李]%'
select * from Student where Name like '[^张]%'

--排序
select * from Student
order by Address ASC,[Name] ASC

select * from Student
order by Address+[Name] DESC

--排序
select top 3 * from Employee
order by Salary

select top 10 percent * from Employee
order by Salary

--Case
select [name],case sex  when '男' then '先生' else '女士' end as 性别
from Student

select [name],
case
 when salary<1000 then '三级工资'
 when salary<2000 then '二级工资'
 else '特级工资'
end as '工资水平'
from Employee

--空值函数
select [Name],Isnull(Email,'none')
from Friend

select * from Friend where Email is null

--日期和时间函数
select GetDate()

select DateAdd(mm,1,Getdate())

select DateDiff(mm,'1988',getDate())

select substring(Email,1,5) from Student

select charIndex('@','skadl@163.com',1)

select right('skadl@163.com',3)
select left('skadl@163.com',5)

select '   skadl  @163.com  '
select ltrim('   skadl  @163.com  ')
select Rtrim('   skadl  @163.com  ')
select replace('   skadl  @163.com  ',' ','')

--数据类型转换
select cast('123' as int)
select convert(int,'123')

--数学函数
select ABS(-5)
select Ceiling(5.2)
select floor(5.3)
select power(5,2)
select Round(3.14159,1)
select sign(-9)
select sqrt(8)

--内联接
select * from Table1 a inner join Table2 b on a.ID1=b.ID1

--右联接
select * from Customer a Left Join OrderForm b on a.CustomerID=b.CustomerID

--全外联接（等价于进行左外联接和右外联接，再将重复行变为单一行）
select * from Table1 a Full Join Table2 b on a.ID1=b.ID1

--交叉联接（笛卡尔积）
select * from Student a Cross Join Employee b

--联合（联接沿水平方向扩展行，联合将多个结果集放到一张较长的表中）

--合并Union（重复的行被合并为一行）
select [Name],Address from Student
Union
select [Name],Address from Employee

--交 Intersect
select [Name],Address from Student
Intersect
select [Name],Address from Employee

--差 Except(返回只在第一个表中出现的数据)
select [Name],Address from Employee
Except
select [Name],Address from Student

--子查询：简单子查询,共用表达式（CTE）

--用作标量值
select * from OrderDetail
where ProductID=(select ProductID from Product where [Name]='笔记本电脑')

--用作列表
select * from Student
where [Name] in (select [Name] from Friend)

--用作表
select a.[Name],b.人数
from Team a,(select count(*) 人数,TID from Student Group by TID) b
where a.TID=b.TID

--共用表达式-CTE
with CTETable as
(
   select * from Employee where Salary>1000
)
select sum(salary) from CTETable

--使用Exists(与In相似)
select * from Student a
where exists (select 1 from Employee where a.[Name]=[Name])

--简单聚合:sum() avg() min() max() count()

--分组（可根据多列进行分组）
select DepID,sum(salary),avg(cast(Salary as Float)),Min([Name]),count(*)
from Employee
group by DepId,Address

--对分组结果筛选 Having

--插入数据时创建表
select * into NewTable
from Student

-- 更新数据时引用多个表
update Employee
set Salary=a.Salary*(1+b.RaiseFactor)
from Employee a,Department b
where a.DepID=b.DepID

--删除数据时引用多个表
delete Employee
from Employee a,Department b
where a.DepID=b.DepID and b.[Name]='后勤部'
                         
--Truncate：彻底删除，不可恢复。标识列从1开始
Truncate Table Student

--添加约束
alter table Student Add Constraint PK_stuNo Primary Key(StudentID)
alter table Student Add Constraint UQ_stuID Unique(stuID)
alter table Student Add Constraint DF_stuAddress Default('地址不详') for Address
alter table Student Add Constraint CK_stuAge Check(Email like '%@%')
alter table Student Add Constraint FK_stuNo Foreign Key(TID) References Team(TID)

--@@Error 返回最后一个T-SQL错误的错误号
update Product set Price=300 where ProductId=2
insert into OrderDetail values(5,7,12)
print @@Error

--临时表的使用
select * into #employee2 from Employee
select * from #Employee2
drop table #employee2

--动态SQL
exec Sp_ExecuterSQL
    N'Select * from Student Where StudentID=@StudentID',
    N'@StudentID INT',
    @StudentID=1

--错误处理
Begin Try
   code;
End Try
Begin Catch
   code;
End Catch
--错误函数 Error_Message() 错误消息文本；Error_Number() 错误编号；Error_Severrity() 错误的严重程度

--视图

--索引
create unique NonClustered on Student(Email) with fillfactor=50

--事务
Begin Transaction
Begin Try
   code;--语句之间用分号隔开
End Try
   if(@@trancount>0)
      RollBack Tran
Begin Catch
End Catch
if(@@trancount>0)
   commit Tran

--存储过程：存储过程像方法一样，为了完成某个任务将一些SQL语句组织在一起，可以有参数，也可以有返回结果；能够被随时调用
--例一
create proc sp_SelectStudentAll
as
select a.*,b.[Name] as class
from student a,team b
where a.TID=b.TID

exec sp_SelectStudentAll
--例二（修改订单发货状态的存储过程）
select * from OrderForm
select * from OrderDetail
select * from Product
create proc sp_SendOrderFormID
@orederFormId int
as
begin tran
begin try
    update OrderForm set IsSend='Y' where OrderFormID=1; --分号
    update Product Set Quantity=b.Quantity-a.Quantity --这儿的写法需注意
    from OrderDetail a,Product b
    where a.ProductID=b.ProductID and a.OrderFormID=@orederFormId
end try

begin catch
  if(@@trancount>0)
  begin
    print error_message()
    rollBack tran
  end
end catch
 
  if(@@trancount>0)
      commit tran

exec sp_SendOrderFormID 1

--带参数的输入和输出存储过程
create proc sp_InsertIntoEmployee
@empId int output,
@depId int,
@name varchar(50),
@salary money,
@dataHire datetime=null,
@address varchar(50)
as
set NoCount ON
if(@dataHire is null)
   set @dataHire=GetDate()
insert into employee(DepID,[Name],Salary,DateHire,Address)
values(@depId,@name,@salary,@dataHire,@address)
select @empId=@@identity

declare @empId int
exec sp_InsertIntoEmployee @empId output,@depId=1,@name='王',@salary='100',@dataHire='2010-6-7',@address='唐山'
select @empId

--自定义函数
create function f_GetBirthday(@identity char(18))
returns DateTime --注意
as
begin
   declare @temp Char(8)
   set @temp=substring(@identity,7,8)
   return Convert(DateTime,@temp)
end

select dbo.f_GetBirthday('156324198501110625') --注意dbo

--触发器 for/after；instead of

--分页的存储过程
Create procedure u_pro_page_tables
(
   @pageindex int,
   @pagesize int,
   @sqltables varchar(500),
   @fields varchar(200),
   @where varchar(500)='',
   @order varchar(100)='',
)
as
  declare @sql nvarchar(4000)
  if(@where='')
     set @where='1=1'
  set @sql='with temp as (select'+@fields+',row_number() over(order by '+@order
          +') as RowNumber from '+@sqltables+' where '+@where+')select * from temp where RowNumber between '+
          convert(varchar(20),@pagesize*@pageindex+1)+' and '+convert(varchar(20),@pagesize*@pageindex+@pagesize)
 execute sp_executesql @sql