SqlServer 总结

创建一个数据库（库名：学生库）并设置两个数据文件和两个日志文件

create database 学生库

onprimary

( name=学生_data1, filename='e:\学生_data1.mdf',size=1mb, maxsize=3mb,filegrowth=1mb ),

( name=学生_data2, filename='e:\学生_data2.ndf',size=1mb, maxsize=3mb, filegrowth=10% )

log on

( name=学生_log1, filename='e:\学生_log1.ldf', size=1mb,maxsize=2mb, filegrowth=1mb ),

( name=学生_log2, filename='e:\学生_log2.ldf', size=1mb,maxsize=2mb, filegrowth=10% )

 

删除数据库：dropdatabase学生库（数据库名）

 

更改数据库中（数据文件学生_data1）的原始大小结构

alter database 学生库   modify file ( name=学生_data1,size=5mb )

 

更改数据库中（日志文件学生_log1）的原始大小结构

alter database 学生库   modify file ( name=学生_log1,size=2mb )

 

为数据库添加一个日志文件（学生_log3）

alter database 学生库

add file( name=学生_log3, filename='e:\学生_log3.ldf', size=1mb，maxsize=2mb, filegrowth=10% )

 

设置数据库（学生库）为只读：execsp_dboption '学生库','read only',true

 

设置在同一时间内只有一个用户访问数据库（学生库）：exec sp_dboption '学生库','single user'

 

自定义数据类型：execsp_addtype city（类型名） ,'nvarchar(15)',null

 

删除自定义数据类型：execsp_droptype city（类型名）

 

use 学生库               打开数据库（学生库）

create table学生表（表名）      创建一个学生表

( 学号 int not null, 姓名 varchar(20), 爱好 varchar(20), 出生日期 datetime)

 

删除表：droptable学生表（表名）

 

标识约束属性（两种方法）：

①在创建表（学生表）的同时，将学号设置标识

createtable 学生表

(学号int not nullidentity(1,1)primary key,姓名varchar(20)unique,爱好varchar(20),出生日期 datetime)

 

②更改学生表，添加学号标识

create table 学生表(姓名 varchar(20) unique,爱好 varchar(20),出生日期 datetime)

alter table 学生表  add 学号（字段名） int identity(1,1)

 

主键约束（两种方法）：

①在创建表（学生表）的同时，将学号设置主键

create table学生表(学号int not null primary key,姓名char(20),爱好char(20),出生日期datetime)

②更改学生表，将学号设为主键：alter table 学生表add constraint pk_学号primary key (学号)

 

唯一约束（两种方法）：

①更改学生表，将姓名设为唯一约束：alter table 学生表add constraint uq_姓名 unique (姓名)

②在创建表（学生表）的同时，将姓名设置唯一约束

create table学生表(学号int not null primary key,姓名char(20)unique,爱好char(20),出生日期 datetime)

外键约束（两种方法）：

①创建一个成绩表，学号字段设置外键,引用学生表表中的学号字段

create table 成绩表(学号int foreign key references 学生表(学号),姓名char(20),科目char(20),成绩 int)

②更改成绩表，外键，将引用“学生表”中的“学号”：

alter table 成绩表 add constraint fk_学号 foreign key 学号references 学生表(学号)

 

检查约束（两种方法）：

①更改成绩表，添加检查约束，将查找成绩在0—100之间

alter table 成绩表  add constraint ck_成绩 check ([成绩]>=0and [成绩]<=100)

 

②在创建表（成绩表）的同时，将成绩设置检查约束

create table 成绩表(姓名char(20),科目char(20),成绩int check([成绩]>=0 and [成绩]<=100),学号intforeign key references 学生表(学号))

 

使用insert插入数据：Insertinto表名 values〈表中所列的值〉

例如：insert into 成绩表 values ('张三','语文','98','1')

成绩表中的字段是：姓名 varchar(20), 科目 varchar(20), 成绩 int,学号 int

 

使用select into 将一个表中的数据添加到另一个表中

（把成绩表中的数据添加到成绩表1中）insert目标表的表名 select 从现表中选择的列 from  原表

create table 成绩表1( 姓名 nvarchar (20),科目 nvarchar (20),成绩 int )

insert 成绩表1 select 姓名,科目,成绩 from 成绩表

 

更新表中数据：

更新一行：update表名 set列名=values [ where〈查找条件〉]

例如：update 成绩表 set 成绩=90where 姓名='张三'

更新多行：update成绩表 set成绩=90

 

删除数据：

删除一行数据：delete from表名 [where <查找条件>]例如：delete from 成绩表 where 姓名='张三'

删除多行数据：delete from成绩表

使用truncate table 删除表中的所有数据：truncate table成绩表

 

查询数据——语法：select列名from 表名

例如：select 姓名,科目,成绩 from 成绩表 或  select* from 成绩表    查询成绩表中所有的数据

 

1.使用where子句：select* from表名 where<查找条件>或select列名 from 表名 where<查找条件>

例如：select * from 成绩表 where姓名='张三'或select成绩,科目 from 成绩表 where 姓名='张三'

 

2.使用order by子句（排序）升序（asc）降序（desc）

语法：select * from表名 order by 列名 或  select列名 from表名 orderby 列名

例如：select * from 成绩表 order by 成绩 asc或select姓名,成绩 from 成绩表 order by 成绩 desc

 

3.在查询中使用常量：select列名+’：’+列名+’->’+列名（数据类型应一致）

例如：select  姓名+'：'+科目 from 成绩表  或 select  姓名+'：'+科目 as 名单 from 成绩表

 

4.使用as子句命名列：select列名 as列名（显示的名）from表名

例如：select 成绩 as 成绩单from 成绩表

 

5.使用标识列：select identity（datatype标识列的数据类型，标识种子，递增量）as 列名（将要插入新表中的列的名称） into table2（现有新表）from table1（要添加标识值的表）（原来的表）

例如：select identity (int,1,1) as 编号 into 成绩表1 from 成绩表

 

6.使用top子句限制查询返回行数

语法：select top n列名 from 表名（显示n行）或 select top npercent列名 from表名（显示n%行）

例如：select top 2 成绩 from 成绩表 或 select top 50 percent 成绩 from 成绩表

 

聚合函数：

1.sum（求和）：select sum (成绩) as总分 from 成绩表

2.avg（求平均）：select avg (成绩) as平均分 from 成绩表

3.count（计数）：select count(成绩) as人数 from 成绩表

4.max（最大值）：select max (成绩) as最大值 from 成绩表

5.min（最小值）：select min (成绩) as最小值 from 成绩表

 

使用group by进行分组查询（与聚合函数配合使用）

select 列名1，聚合函数（列名）as 列名from 表名group by 列名1

例如：select 科目,max(成绩) as 最大值 from 成绩表 group by 科目

 

使用having子句选择行（顺序）where→group by→having在分组或使用聚合函数后对行进行筛选

例如：select 科目,max(成绩) as 最大值 from 成绩表 group by 科目 having max(成绩)>65

 

通配符和模糊查询：

1.like——select * from 成绩表 where 姓名like '张%'

2.in——select * from 成绩表 where 姓名in('张三')

3.between——select * from 成绩表 where 成绩between 80and 90

4.isnull——select * from 成绩表 where 姓名is null

 

select语句小结：

select [TOP][n]或[n percent]<目标列> [as 别名][,<目标列1> as 别名]…

from<表名或视图名>[as别名]…[where 条件表达式…][groupby<列名1>[having<条件表达式>]]

[order by<列名2>[ASC升序/DESC降序]]

 

系统函数：

1.获取指定字符串中左起第一个字符的ASC码——printascii('ABCDEF')   --结果为：65

2.根据给定的ASC码获取相应的字符——print char(65)    --结果为：A

3.获取给定字符串的长度——printlen('abcdef')     --结果为：6

4.大小写转换——printlower('ABCDEF') --结果为：abcdef；printupper('abcdef') --结果为：ABCDEF

5.去空格

左——printltrim('    abcd  dfd df  ') --结果为：'abcd  dfd df  '

右——printrtrim('    abcd  dfd df  ') --结果为：'   abcd  dfd  df'

6.求绝对值——printabs(-12)--结果为：12

7.幂——printpower(3,2) 3 的 2 次方，--结果为：9。printpower(3,3) --结果为：27

8.随机数（0—1000之间的随机数）——printrand() * 1000

9.获取圆周率——printpi()      --结果为：3.14159

10.获取系统时间——printgetdate()     --结果为：月日年时间，如：06 30 2006 9：40AM

 

获取3天前的时间——print dateadd(day, -3 , getdate())

获取3天后的时间——print dateadd(day, 3 , getdate())

获取3年前的时间——print dateadd(year, -3 , getdate())

获取3年后的时间——print dateadd(year, 3 , getdate())

获取3月后的时间——print dateadd(month, 3 , getdate())

获取9小时前的时间——print dateadd(hour,-9 , getdate())

获取9分钟后的时间——print dateadd(minute, 9 , getdate())

获取指定时间之间相隔多少年——print datediff(year, '2005-01-01', '2008-01-01')

获取指定时间之间相隔多少月——print datediff(month, '2005-01-01', '2008-01-01')

获取指定时间之间相隔多少天——print datediff(day, '2005-01-01', '2008-01-01')

 

字符串合并——print'abc' + 'def'；print 'abc' + '456'(正确) ；print 'abc' + 456 (错误)

 

类型转换——print'abc' + convert(varchar(10), 456)     结果为：abc456

print convert(varchar(12), '2005-09-01',110)            结果为：2005-09-01

 

select title_id, type, price from titles     --字符串连接必须保证类型一致(以下语句执行将会出错)

类型转换——(错误)——select title_id + type + price from titles

(正确)——select title_id + type + convert(varchar(10), price) fromtitles

                                                 

获取指定时间的特定部分：

年­——printyear(getdate())；月——print month(getdate())；日——print day(getdate())

 

获取指定时间的特定部分

年——print datepart(year, getdate())；    月——printdatepart(month, getdate())

日——print datepart(day, getdate())；     小时——print datepart(hh, getdate())

分钟——print datepart(mi, getdate())；    秒——printdatepart(ss, getdate())

毫秒——print datepart(ms, getdate())

 

-- 获取指定时间的间隔部分——返回跨两个指定日期的日期和时间边界数

相差年数——print datediff(year, '2001-01-01','2008-08-08')

相差月数——print datediff(month,'2001-01-01', '2008-08-08')

相差天数——print datediff(day, '2001-01-01','2008-08-08')

相差小时数——print datediff(hour,'2001-01-01', '2008-08-08')

相差分钟数——print datediff(mi, '2001-01-01','2008-08-08')

相差秒数——print datediff(ss, '2001-01-01','2008-08-08')

 

-- 在向指定日期加上一段时间的基础上，返回新的 datetime 值

加年份——print dateadd(year, 5, getdate())       加月份——printdateadd(month, 5, getdate())

加天数——print dateadd(day, 5, getdate())     加小时——printdateadd(hour, 5, getdate())

加分钟——print dateadd(mi, 5, getdate())      加秒数——printdateadd(ss, 5, getdate())

 

 

 

 

创建文件夹：exec xp_cmdshell'md 盘符:\文件夹名称', no_output

例如：在D盘创建名为：“资料”的文件夹：exec xp_cmdshell 'md d:\资料', no_output

 

 

 

查看文件：exec xp_cmdshell'dir盘符:\文件夹名称'。例如：exec xp_cmdshell'dir d:\资料'

判断数据库是否存在：ifexists(select * from sysdatabases where name='数据库名称')

 

drop database 数据库名称

 

 

判断表是否存在——ifexists(select * from sysobjects where name='表名')   drop table 表名

 

 

添加约束（5种）——语法：alter table 表名 addconstraint 约束名约束类型 具体的约束说明

1.主键约束：alter table 表名 addconstraint pk_约束名 primary key(主键约束字段名)

2.外键：alter table表名add constraintfk_约束名foreign key(外键字段名) references 关系表(主键名)

3.唯一约束：alter table 表名 addconstraint uq_约束名 unique (唯一约束字段名)

 

4.检查约束：alter table 表名 addconstraint ck_约束名 check (约束检查字段名)   --表达式

5.默认约束：alter table 表名 addconstraint df_约束名 default (默认值) for 默认约束字段名

 

 

删除约束语法：alter table 表名 drop constraint 约束名

例如：删除stuInfo表中地址的默认约束：alter table stuInfo drop constraint 约束名(df_stuAddress)

 

 

 

第一道大门——创建登录帐户（2种）

第一种：SQL账户：exec sp_addlogin '用户名','密码'

 

第二种：Windows账户：exec sp_grantlogin 'windows域名\域账户'

第二道大门——创建数据库用户 ：exec sp_grantdbaccess '登录账户','数据库用户' 

--“数据库用户”为可选参数，默认为登录账户，即数据库用户默认和登录账户同名。

例如：在数据库中添加一个用户：

exec sp_grantdbaccess'zhang','zhangUser'     --'zhang为登录账户','zhangUser为数据库用户'

 

 

第三道大门——向数据库用户授权：grant 权限 on 表名 to 数据库用户

例如：为zhangsanUser授予对表stuInfo的增删该查的权限

grant select,update,delete,insert on stuInfoto zhangsanUser

 

建表的权限：grant create tableto zhangsanUser

 

 

----------------------------------第三章 T—SQL编程----------------------------------

 

声明局部变量，语法：declare @局部变量名数据类型     例如：declare @sum int

变量赋值，方法（2种）

第一种：使用set语句：set @变量名=值           例如：set @sum=1

第二种：使用select语句：select @变量名=值   例如：select @sum=1

 

 

全局变量：注意：有两个@@

 

@@error           --错误号                       @@identity     --最后一次插入的标识值

@@language     --当前使用的语言名称          @@max_connections --可以创建的同时连接的最大数目

@@rowcount     --受上一个SQL语句影响的行数      @@servername  --本地服务器的名称

@@servicename --该计算机上的SQL服务的名称      @@timeticks       --当前计算机上每刻度的微秒数

@@trancount       --当前连接打开的事务数       @@version      --SQL server的版本信息

 

 

输出语句

第一种方法：print 局部变量或字符串——例如：print '服务器名称:'+convert(varchar(20),@@servername)

第二种方法：select 局部变量 as 自定义别名     --例如：select @@servername as 服务器名称

 

 

 

 

 

 

 

 

 

 

 

 

逻辑控制语句

1.if_eles语句

if(条件)

 

  begin

 

    语句或语句块

  end      

 

else

 

  begin

 

    语句或语句块

  end

 2.while语句

while(条件)   --注意：“1=1”为条件永远成立

 begin

 

    语句或语句块

   break

 

  end

 3.case多分支语句

case

 

   when 条件1 then 结果1

 

   when 条件2 then 结果2

 

   else 其他结果

end

 

 

批处理语句可提高语句的执行效率，结束的标志为：go

 

 

---------------------------------------第四章高级查询---------------------------------------

 

一般来说，表连接都可以用子查询替换，但子查询不一定能用表连接来替换

子查询返回的值只有一个，可以用“=，!=,>,>=,<,<=”

in 和not in 子查询——子查询的返回值多于一个

--例如：

select stuName(表中字段) from stuInfo (表名) where stuNo(表中字段) in (select stuNo(表中字段) fromstuMarks(表名))

 

 

 

select stuName(表中字段) from stuInfo(表名) where  stuNo(表中字段) not in(select stuNo(表中字段) fromstuMarks(表名))

 

 

 

exists和 not exists子查询——存在检测的子查询语句

基本语法：if exists 或 not exists(子查询)  语句

 

 

表联接：1.内联接(inner join) 2.左外联接(left outerjoin)  3.右外联接(right outer join)

 

4.完全外联接(full outer join)

 

5.自联接(inner join)（同一个表）

select * from 表名1 别名1 inner join 表名1 别名2 on 别名1.公共字段=别名2.公共字段

 

 

表联接查询：select 字段名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段

表联接更新：

update 表1 set 表1的字段名更新 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段

 

 

生成表查询：select * into 生成表名 from 原来的表      例：select * into stuInfo1 from stuInfo

向生成表中插入数据：insert into 生成表名 select * from 原来的表

 

 

----------------------------------第五章事务、索引、视图--------------------------------

 

事务——是单个的工作单元，一组数据库操作命令，同时成功、同时失败。

开始事务：begintransaction(tran)以作为事务的开始

提交事务：committransaction(tran)

回滚(撤销)事务：rollbacktransaction(tran)

 

 

 

事务中须设置，累计错误变量，用于累计是否有错误。即：

declare @errorSum int    --定义累计错误变量

set @errorSum=0      --设初值为0，即无错误

set @errorSum=@errorSum+@@error --错误累计

 

 

 

 

 

 

判断

if(@errorSum<>0)  --表明有错误

 begin

 

   print '有错误，事务回滚'

 

   rollback tran

 

  end

 

else

 

 begin

 

   print '无错误，提交成功'

 

   commit tran

 

  end

 

 

 

索引——提高查询速度

创建索引的语法：create 索引类型 index 索引名 on 表名 (字段名) with fillfactor=数值

索引类型包括：

1.unique(唯一索引)。

2.clustered(聚集索引)，一个表中只能有一个。表中各行的物理顺序与键值的逻辑（索引）顺序相同。

3.nonclustered(非聚集索引)，可以有多个，最多249个。表中各行的物理顺序与键值的逻辑顺序不相同。

 

 

fillfactor为填充因子：0—100之间的值，该值指示索引页填满的空间所占的百分比。

 

 

删除索引——drop index 表名.索引名

 

 

视图——一种虚拟表，不是数据库中存储的数据值的集合，而是实际表中各个字段的位置

判断视图是否存在——ifexists(select * from sysobjects where name='view_视图名')

 

drop view view_视图名

创建视图——create view view_视图名  as  <select 语句>

 

查询视图——select * fromview_视图名

 

 

-----------------------------------第六章存储过程--------------------------------------

 

存储过程——是SQL语句和控制流语句的预编译集合，减少网络流量，提高访问速度。

常用的系统存储过程

1.exec sp_databases             --列出当前系统中的数据库

2.exec sp_renamedb '原来的数据库名','改后新数据库名'      --更改数据库名称(单用户访问)

 

exec sp_rename '原来名','改后新名'    --更改对象名称(单用户访问)对象可为：表、视图、存储过程等

例如：

exec sp_rename '原来的表名','改后新表名'     --更改表名称(单用户访问)

 

exec sp_rename '原来的视图名','改后新视图名'     --更改视图名称(单用户访问)

 

3.exec sp_tables             --当前数据库中可查询对象的列表

4.exec sp_columns 表名   --查表中列的信息     例如：exec sp_columns stuInfo--查表stuInfo中列的信息

5.exec sp_help 表名      --查看表的信息；    例如：exec sp_help stuInfo    --查看表stuInfo的信息

6.exec sp_helpconstraint 表名   --查表的约束

例如：execsp_helpconstraint stuInfo --查表stuInfo的约束

7.exec sp_helpindex 表名 --查看表的索引； 例如：exec sp_helpindex stuInfo  --查看表stuInfo的索引

8.exec sp_helptext 视图名       --查看视图的语句文本

9.exec sp_stored_procedures --返回当前数据库中的存储过程列表

10.exec sp_helpdb 数据库名      --报告有关指定数据库或所有数据库的信息

例如：exec sp_helpdb --所有数据库；    exec sp_helpdb Tours     --指定数据库Tours

11.exec xp_logininfo         --查看当前登录信息

12.exec sp_pkeys 表名         --查看表的主键信息

 

 

判断存储过程是否存在——ifexists(select * from sysobjects where name='proc_存储过程名')

drop proc proc_存储过程名

创建存储过程（两种） --输出参数须注明output

 

第一种：(不带参数)

 

create proc proc_存储过程名

as

 

SQL语句

调用存储过程——exec 存储过程名

 第二种：(带参数)

 

create proc proc_存储过程名

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

 

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

 

as SQL语句

调用存储过程——exec 存储过程名 [参数]output

 

 

处理错误信息——raiserror('提示信息',错误级别,错误状态)   --例如：raiserror('提示信息',16,1)

 

 

 

------------------------------第七章触发器---------------------------------

 

触发器——是在对表进行插入、更新、删除操作时自动执行的存储过程。包括三个触发器：

1.insert触发器：向表中插入数据时触发，自动执行触发器所定义的SQL语句。

2.update触发器：更新表中某列、多列数据时触发，自动执行触发器所定义的SQL语句。

3.delete触发器：删除表中数据时触发，自动执行触发器所定义的SQL语句。

 

 

检测触发器是否存在——ifexists(select name from sysobjects where name='trig_触发器名')

drop trigger trig_触发器名

 

 

创建触发器

create trigger trig_触发器名

on 表名

[with encryption]加密    --可省略

for 触发器[insert,update,delete]    --可多选，中间用“，”分隔

as

SQL语句

 

 

注意：应用delete触发器时，要应用备份表

判断备份表是否存在(不存在则创建，存在则将数据插入到备份表中)

 

if not exists(select name from sysobjectswhere name='备份表')

 

    创建备份表

   select * into 备份表 fromdeleted   --从deleted表中获取被删除的数据

else

 

    将被删除的数据插入到备份表中

   insert into 备份表 select *from deleted

 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/langman2001xy/archive/2009/10/17/4688262.aspx