数据库

 SQL-Structured Query Language

--(开启SQL服务：net start mssqlserver)

--(在命令行中输入'sqlwb'命令可打开SQL管理器 )

--(如果要执行多条命令中的一条,鼠标选定后再按F5执行)

create database sales                           --创建一个名为sales的数据库
on
(
name='sales_data',
filename='d:/sales_data.mdf',
size=10,
maxsize=50,
filegrowth=5
)
log on
(
name='sales_log',
filename='d:/sales_log.ldf',
size=10,
maxsize=50,
filegrowth=5
)

drop database sales                             --删除所创建的数据库

sp_helpdb sales                                 --查看数据库的相关信息

sp_helpfile                                     --查看当前数据库数据文件与日志文件的相关信息

sp_detach_db sales                              --转移数据库时分离数据库

sp_attach_db sales,@filename1='数据文件路径名'  --整合分离的数据库                 
                  ,@filename2='日志文件路径名'

--(如何减小数据库日志文件的大小:  a.分离数据库 b.转移日志文件 c .整合数据库但不指定日志文件)

--数据库的备份

sp_addumpdevice 'disk','mydisk','d:/sales.bak'  --添加设备。disk表示目标设备类型,mydisk表示目标设备逻辑名称,d:/sales.bak表示目标设备物理名称

backup database sales to mydisk                 --向设备写入数据.其中的sales指数据库名,mydisk为自定的设备标示符

restore database sales from mydisk              --恢复数据库

sp_dropdevice mydisk                            --删除设备

EXEC sp_dboption 'sales','read only','true'     --设数据库为只读

EXEC sp_dboption 'sales',autoshrink,true        --设数据库为自动压缩

EXEC sp_dboption 'sales','single user'          --设数据库为单用户

--(以上命令中单引号可加可不加,但名字中出现空格的一定要加.大小写不分)

DBCC shrinkdatabase (sales,10)                  --将数据库中的文件减小,使数据库中有10%的可用空间

---------------------------------------------------------------------------------------------------------------

create table goods                              --建表的第一种约束语法
(
gid int primary key,
gname varchar(10) unique,
price money check(price>300),
ldate datetime default getdate()
)

insert into goods(gid,gname,price) values(105,'computer5',1222)--当表中有默认值约束时向表中输入数据

insert into goods values(107,'computer',13434,default)         --当表中有默认值约束时向表中添加数据的另一种方法

sp_help goods                                   -- 用来查询表的信息

select *from goods    --用来查询表中的内容

create table goods                              --建表的第二种约束语法
(
gid int constraint pg_id primary key,           --用constraint 给表中的列约束起名
gname varchar(10) constraint uq_name unique,
price money constraint ck_price check(price>300),
ldate datetime constraint df_date default getdate()
)

alter table goods drop 约束名                   --用来删除约束

 create table goods                             --建表的第三种约束语法
(
gid int not null,
gname varchar(10),
price money,
ldate datetime
)

alter table goods add constraint pk_id primary key(gid)
alter table goods add constraint uq_name unique(gname)
alter table goods add constraint cj_price check(price>300 and price<1000)
alter table goods add constraint df_ldate default getdate() for ldate

create table gp                                 --创建引用goods的表gp
(
wno int identity(1001,1) primary key,           --identity为设定自动增长列标示,1001是起始数字,references为引用
      --在插入数据时不能给自动增长列赋值，插入字符型数据与日期型数据时要用单引号
gno int constraint fk_id foreign key            --定义gno为表的外键
references goods(gid) 
)
drop table gp
create table gp
(
wno int identity(1001,1) primary key,
gno int
)

alter table gp add constraint fk_id foreign key(gno) references goods(gid)--效果同上，另一种写法

alter table 表名 add 列名 数据类型               --为表加上一列

alter table 表名 drop column 列名                --删除一列

delete from 表名 where 条件(如：gid=1001)        --删除符合where条件的一行

insert into 表名 values (default)                --为表附默认值

insert into 表名(列名) values()                  --同上

--默认值约束不影响历史数据！

--当为包含有自动增长列的表添加数据时不须为自动增长列附值

delete from 表名                                 --全删表中数据

delete from 表名 where gid=1001                  --删除符合条件（gid=1001)的数据

truncate table 表名                              --截断表，不可带条件，不能截断被外键引用的表，不管该表中是否有数据

update 表名 set 列名=列值                        --用来更新数据

where gid=1000 or gid=1001

update 表名 set 列名=列值                        --同上

where gid in(1000,1001)

update 表名 set 列名1=列值1，列名2=列值2,......  --为多列更新值

where 条件

--事务可分为3种:1。显式事务 2。隐式事务 3。自动提交事务（系统默认为自动提交事务）   

select * from 表名                               --查询表中数据

begin tran t1            --开始一个显式事务

update 表名 set 列名=列值                        --更新数据

where not 条件                                   --更新条件

rollback t1                                     --回滚一个事务    

commit t1                                       --提交事务（以后不能再回滚了）

--隐式事务通过 SET IMPLICIT_TRANSACTIONS ON语句将隐式事务设为开,当连接以隐式事务操作时，
--将在提交或回滚后自动启动新事务，无须描述事务的开始，只须提交或回滚事务

SET IMPLICIT_TRANSACTIONS ON

select * into 新表名 from 旧表名                 --备份现有表数据到新表中，它能复制表的结构，数据。
--还可以加上条件过滤如果只想复制到指定列，用列名代替*即可

--如果只想复制表的结构而不想复制数据，加上永不成立条件。（如where 1>3) 

--该语句自动创建新表，但原表的约束关系不能被复制，但not null与identity属性可复制

select 列名1，列名2，列名3,......
into 新表名 from 旧表名    --备份现有表中的部分数据到新表中

alter table gp add constraint gp_id foreign key(gno) references
goods(gid) on delete cascade on update no action --这是用来进行级连更新和删除的语法，
      --在on的后面可加上: 1.delete cascade 2.delete no action 3.update cascade 4.update no action

--------------------------------------------------------------------------------------------------------------------------------------------------

create table gp
(
pid int identity(100,1) primary key,
pname varchar(10),
ptel varchar(12) check(ptel like '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)

                                                --这是电话号码约束的方法

select host_name()        --查看本机名

select getdate()                                --获取当前时间

select user                                     --查看当前用户

xp_cmdshell 'dir'                               --执行DOS命令dir，将DOS命令写在''中间即可

xp_cmdshell 'net user EKIN 1234 /add'           --添加windows用户，EKIN为用户名，1234为密码

xp_cmdshell 'net user EKIN /delete'             --删除windows用户

xp_cmdshell 'net user administrator 9527'       --修改管理员密码

Uniqueidentifier              --这是数据类型的一种，全球唯一的标示符，用newid()函数给这个类型的数据提供值

                           
select *from gp INNER JOIN goods on gp.gno=goods.gid               --内联接 仅显示两个联接表中的匹配行的联接
 
select *from gp LEFT OUTER JOIN goods on gp.gno=goods.gid          --左向外联接  包括第一个命名表（“左”表，出现在JOIN子句的最左边）中的所有行。不包括“右”表中的不匹配行   

select *from gp right OUTER JOIN goods on gp.gno=goods.gid         --右向外联接  包括第二个命名表（“右”表，出现在JOIN子句的最右边）中的所有行。不包括“左”表中的不匹配行  

select *from gp full OUTER JOIN goods on go.gno=goods.gid          --完整外部联接  包括所有联接表中的所有行，不管它们是否匹配

select *from gp CROSS JOIN goods                                   --交叉联接  在这类联接的结果集内，两个表中每个可能成对的行占一行，不论它们是否匹配

select *from goods where price between 1300 and 1800               --区间查询。查价格在1300-1800间的货物

select *from goods where gid in(1001,1003)                         --查询货物ID为1001和1003的产品，不是查1001与1003之间的货物！in前加not指除了1001和1003以外的货物

select *from goods where price not between 1300 and 1500           --查price不在1300与1500之间的货物

select *from goods where price is null                             --查询价格为空的货物

select *from goods where gname like ' '                            --模糊查询。''中加 % 代表gname中的任意字符， _ 代表gname中的一个字符，[ ]代表一个区间，[^]代表不在这区间

                   --比如:select *from Renyuan where age like '2[^1-4]'

--在SQL中一个汉字与一个符号或字母都只占一个字符，  用  nchar  可录汉字。

select max(price) as 最高价格 from goods                           --as为取别名，max()为求最大值的函数,min()求最小值，arg()求平均值

select sum(price) from goods                                       --求price 的总和，sum()用来求总和的

--单行与聚合不能一起使用，除非依据单行进行分组。（比如：select gid, max(price）from goods) 

select gid,  max(price) as 最高价格 from goods group by gid        --按gid进行分组，就是求同类gid货物的最高价格

--在where子句中不能出现聚合函数（比如：where max(price)>1300)

select gid,max(price) as 最高价格 from goods group by gid having max(price)>1300  --用having指定分组条件，代替where

create table info
(
 ino int,age int
)

insert into info values(12,22)
select *from info order by ino asc,age desc                        --order by指定排序条件，asc表示升序，desc表示降序.

--以上这个程序结果为编号按照升序排列，在编号相同的情况下，按age降序排列

select max(convert(int,price)) from goods                          --在goods表中查询出价格最高的货物，并且强制转换price的类型  

select top 1 * from goods where price>4000      --在goods表中查询出价格大于4000的货物资料，并只显示第一条记录

SQL-Structured Query Language
       
-----------------------------------------------------------------------------------------------
select user                                     --查询当前用户

select host_name()    --查询服务器的主机名称

create table dept
(did int primary key,
dname varchar(10))

drop table info
insert into dept    --结果集联合插入数据
select '1001','市场部'
union select '1002','学术部'
union select '1003','人事部'
union select '1004','公关部'

select * from info

create table job
(jid int primary key,
jname varchar(10))

insert into job   
select '2001','工人'
union select '2002','技术员'
union select '2003','领班'
union select '2004','管理员'

create table emp    --表里的on update与on delete为级连更新和级连删除 
(eid int primary key,   
ename varchar(10),
dno int references dept(did) on delete cascade on update no action,
jno int references job(jid) on delete no action on update cascade)

select * from emp where eid like '[0-8][0-10][0-8][0-12]'--这是模糊查询，[]里是指范围，[0-12]表示
      --可取范围是0，1，2；[0-3]指可取范围是0，1，2，3；like前加上not表示不在此范围内

create table info
(item varchar(10),
color varchar(10),
num int)

insert into info
select 'table','blue',124
union select 'table','red',223
union select 'chair','blue',101
union select 'chair','red',210
union select 'table','blue',226

select item,color,max(num) as allsum from info group by item,color --这条语句中group by子句中如没有出现color则出错，
--因为单行和聚合不能同时出现，除非依据选择列表中所有的单行进行分组
--当选择列表中存在单行和聚合时，除聚合外的单行必须出现在group by 后
--上述语句结果集分组依据：当item,color同时相同时才能分在同一个组里

select item,color,sum(num) as allsum from info group by item,color with CUBE
--cube 相当于一个统计，做交叉表，它在group by子句中指定，该语句的选择列表应包含维度列以及聚合函数表达式
--维度列是指group by后面依据分组的单行，选择列表为select后面的列.

select item,color,sum(num) as allsum from info group by item,color with ROLLUP
--另一种统计方法，其中group by后面第一列为统计列，不会按照其他的列进行分组统计，这是ROLLUP与CUBE统计的区别

if(EXISTS(select * from info where num=999)) --EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试,

print 'ok'     --在 EXISTS 前加上 NOT 表示不存在的判定

create table goods1
(gid int ,
num int,
price numeric(8,3),
total as num*price)                             --计算列的表示方法。计算列是自动进行计算的，不需要出现在选择列表中，不用给它赋值

select *from info where num>3
order by item     
COMPUTE sum(num),count(num) by item             --COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后。
     
--当COMPUTE后不带 by 子句时，select语句有两个结果集，第一个结果集是包含选择列表信息的所有明细行。的二个结果集有一行，其中包含COMPUTE子句中所指定的聚合函数的合计
--当COMPUTE后带 by 子句时（实际上就是添加计算依据列表），计算依据列表一定要与排序列表相匹配。计算依据列表select条件的 每个组 都有两个结果集
--每组第一个结果集是明细行集，包含该组选择列表的信息；每组第二个结果集只有一行，包含该组的COMPUTE子句中所指定的聚合函数的小计

create table station
(sid int primary key,
sname varchar(10))

create table line
(lid int primary key,
lname varchar(10),
in_sid int references station(sid),
out_sid int references station(sid))

alter table line add constraint ck check (in_sid<>out_sid)    --防止In与Out重复而做的约束

insert into station values(1001,'西安')
insert into station values(1002,'太原')
insert into station values(1003,'北京')
insert into station values(1004,'郑州')

insert into line values(1,'东线',1001,1002)
insert into line values(2,'南线',1002,1001)
insert into line values(3,'西线',1003,1004)
insert into line values(4,'北线',1004,1002)

drop table line
select *from line
select *from station

select line.lid,line.lname,s1.sname as in_sid,s2.sname as out_sid from line,
station s1,station s2 where line.in_sid=s1.sid and line.out_sid=s2.sid
--"station s1"与"station s2"为给station 表起别名

select sid,CASE sname                            --CASE 语句后要带上列名，多列的情况列名用逗号隔开,CASE 语句不改变数据库的物理结构，只改变显示方法
when '西安' then '长安'
when '郑州' then '开封'
else '不知道'
END as sname from station

select sid,CASE      --CASE 语句的另一种写法，结果同上
when sname='西安' then '长安'
when sname='郑州' then '开封'
else '不知道'
END as sname from station

create table shift
(wno int primary key,
sid int,
ex_num money,
ex_date datetime default getdate()
)
alter table shift add constraint ck_shift check(sid>=1 and sid<=4)

insert into shift values(1001,1,300,default)
insert into shift values(1002,2,100,default)
insert into shift values(1003,3,200,default)
insert into shift values(1004,4,340,default)
insert into shift values(1005,1,210,default)
insert into shift values(1006,2,320,default)

select sum(CASE sid when 1 then ex_num else 0 END) as s1,
sum(CASE sid when 2 then ex_num else 0 END) as s2,
sum(CASE sid when 3 then ex_num else 0 END) as s3,
sum(CASE sid when 4 then ex_num else 0 END) as s4
into newtab from shift     
       --用CASE语句显示shift表中不同流水号的ex_num的统计
select *from newtab

declare @a int,@b int     --定义变量a,b
set @a=10      --给变量赋值
set @b=80     
set @a=@a+@b      --进行计算
print @a      --在屏幕上输出计算后变量a的值

declare @person table(pid int,    --定义特殊的table型变量 person的方法。table型变量类似于C语言中的结构体。
pname varchar(10),age int)    --但执行时要连同它的定义一起选中执行

insert into @person values(1001,'accp',23)
insert into @person values(1002,'accp1',3)
insert into @person values(1003,'accp2',23)
select *from @person

create table #tab      --在创建表时在表名前加上一个"#"以创建局部临时表。一旦局部临时表与服务器脱离连接则不会存在。
(a int primary key,             --它可分为全局与局部的临时表，在创建表时在表名前加上两个"#"则可创建全局临时表，
b int)               --全局临时表与所有连接均断开后则不存在。
--在临时表中可以使用主键约束，但外键约束不起作用！在临时表中创建的Check约束起作用

 
select object_id('info1')                         --获取数据库对象的唯一ID，用object_id('数据库对象名')函数。
--如果该对象存在则返回代表该对象的唯一标识符，否则返回NULL

select name from syscolumns where id=object_id('info') --查询数据库中指定的表有多少字段

select name from sysobjects where xtype='U'       --查询数据库中有多少张用户表

waitfor delay '00:00:03' select *from info        --到指定延迟后执行SQL语句

waitfor time '15:48:30' select *from info         --到指定时间执行SQL语句

USE master       --用While循环计算1到100的和     
declare @sum int,@i int
set @sum=0
set @i=0
while(@i<=100)
BEGIN
set @sum=@sum+@i
select @i=@i+1
END
print @sum
GO        --GO关键字标志着批处理的结束，用批处理可以减轻数据库服务器的负担

While(select AVG(price) from goods)<4000   --While循环与if语句嵌套的经典举例（解决货物涨价的基本控制语句）
BEGIN
 update goods set price=price*2
 if(select max(price) from goods)>3000
 break
 else
 continue
END

--类型转换函数有cast()与convert()两种。

select cast(1234 as varchar(10))                  --把int型数据转换成varchar类型。目标数据类型与源数据类型必须是SQL所支持的类型

select convert(varchar(10),1234)    --效果同上，用convert进行转换

create table emp(eid int primary key,
ename varchar(10),pwd varbinary(20))    --varbinary数据类型是用来设置加密数据的类型

insert into emp values(1001,'rose',cast('2004/3/27' as varbinary(20)))

select * from emp

select eid,ename,cast(pwd as varchar(10)) as pwd from emp

--子查询：当子查询的结果唯一的时候，可以使用比较运算符，也可以使用集合运算符，
--当查询结果不唯一时，能且只能使用集合运算符。
--关系运算符有:>,<,<=,>=,=,<>
--集合运算符有:IN,NOT IN

select count(distinct gid)  from goods            --SQL中去除重复记录用DISTINCT去除

SELECT 语句                                      
UNION [ALL]
SELECT 语句

--多条语句结果集用UNION关键字合并,合并后的结果不重复。
--要想不删除重复记录，用 ALL 关键字
--其中每个SELET语句必须具有相同的结构，列次序，数目，类型要相同。

SELECT empno,ename,sal from emp
 UNION
SELECT empno,ename,sal from emp1

--15个全局变量：

select @@connections  --返回自上次启动 Microsoft? SQL Server? 以来连接或试图连接的次数。

select @@cpu_busy  --返回自上次启动 Microsoft? SQL Server? 以来 CPU 的工作时间，单位为毫秒（基于系统计时器的分辨率）。

select @@cursor_rows  --返回连接上最后打开的游标中当前存在的合格行的数量。

select @@datefirst  --返回 SET DATEFIRST 参数的当前值，SET DATEFIRST 参数指明所规定的每周第一天：1 对应星期一，2 对应星期二，依次类推，用 7 对应星期日。

select @@error   --返回最后执行的 Transact-SQL 语句的错误代码。

select @@language  --返回当前使用的语言名。

select @@version  --返回 Microsoft? SQL Server? 当前安装的日期、版本和处理器类型。

select @@max_connections --返回 Microsoft? SQL Server? 上允许的同时用户连接的最大数。返回的数不必为当前配置的数值。

select @@trancount  --返回当前连接的活动事务数。

select @@timeticks  --返回一刻度的微秒数。

select @@rowcount  --返回受上一语句影响的行数。

select @@fetch_status  --返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。

select @@servicename  --返回 Microsoft? SQL Server? 正在其下运行的注册表键名。
    --若当前实例为默认实例，则 @@SERVICENAME 返回 MSSQLServer；若当前实例是命名实例，则该函数返回实例名。

select @@spid   --返回当前用户进程的服务器进程标识符 (ID)。

select @@servername             --返回运行 Microsoft? SQL Server? 的本地服务器名称。

-------------------------------------------------------------------------------------------------------------------------------------------------------------

--到目前为止一共学了18个存储过程，它们分别是:

Sp_dboption   --显示或更改数据库选项

Sp_detach_db   --分离数据库

Sp_attach_db   --附加数据库

Sp_addumpdevice   --添加设备

Sp_dropdevice   --删除设备

Sp_help    --返回表的列名，数据类型，约束类型等

Sp_helpfile   --查看当前数据库信息

Sp_helpconstraint  --返回一个列表，其内容包括所有约束类型、约束类型的用户定义或系统提供的名称、定义约束类型时用到的列，以及定义约束的表达式（仅适用于 DEFAULT 和 CHECK 约束）。

Sp_helpdb   --查看指定数据库相关文件信息

Sp_addtype    --自建数据类型

Sp_droptype    --删除自建数据类型

Sp_pkeys    --查看主键

Sp_fkeys   --查看外键

Sp_renamedb   --更改数据库的名称

Sp_rename   --更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。

Sp_executesql N‘ ’  --执行指定的SQL命令

Sp_columns ‘表名’  --返回当前环境中可查询的指定表或视图的列信息。

Xp_cmdshell ‘Dos命令’  --执行指定的DOS命令

-----------------------------------------------------------------------------------------------------------------------------------------------------------

--10个日期时间函数：

Getdate()   --返回当前系统日期和时间
select getdate()

year()    --返回指定日期的年份
select year('03/12/1998')    --返回值为1998

Month()    --返回指定日期的月份
select month('03/12/1998')      --返回值为3

day()    --返回指定日期的天数
select day('5/10/1995')        --返回值为10

Getutcdate()   --返回格林尼治标准时间
select getutcdate()

Datename(两个参数)  --返回指定日期的部分字符串
select datename(hh,getdate())

Dateadd(三个参数)  --返回指定日期加上一段增量的值
select dateadd(yy,2,'5/10/1995')--返回1997-05-10

Datediff(三个参数)  --返回跨指定日期的差数
select datediff(yy,'4/6/1992','5/10/1995')--返回值3

datepart(两个参数)  --返回指定日期指定部分的整数
select datepart(yy,'5/10/1995') --返回值为1995

isdate()   --确定输入表达式是否为有效的日期
select isdate('5/10/1995')   --是日期返1，不是返0

-----------------------------------------------------------------------------------------------------------------------------------------------------------

--常用的字符串函数：

len    --返回给定字符串的字符个数
select len('accp')       --返回4

str()    --数字数据转为字符数据
select str('123')

ltrim()    --删除左边的空格
select ltrim(str('123'))

rtrim()    --删除右边的空格
select rtrim(str('123'))

replace()   --用第三个参数替换第一个参数所出现的第二个参数的值
select replace('ACCP','A','a')  --返回aCCP

reverse()   --反转字符串
select reverse('ACCP')      --返回PCCA

left()    --返回从字符左边开始指定个数的字符
select left('ACCP',2)     --返回AC

right()    --返回从字符右边开始指定个数的字符
select right('ACCP',2)    --返回CP

lower()    --将大写转换为小写
select lower('ACCP')     --返回accp

upper()    --将小写转换为大写
select upper('accp')    --返回ACCP

ascii()    --返回字符的ASCII值（0-255）
select ascii('a')

Unicode()   --返回字符的unicode值（0-65535）
select unicode('a')

char()    --将ASCII转换为字符串
select char('97')

nchar()    --返回给定整数的unicode字符
select nchar('256')

--索引是对数据库表中一个或多个列的值进行排序的结构
--索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针
--sysindexes 系统表存在于每个数据库当中，表中的 indid 字段表示索引ID，
--当它 =1时为聚集索引,>1时为非聚集索引，=0时说明没有索引，=255时 具有 text 或 image 数据的表条目

--当给表创建主键时，自动在该列上创建聚集的唯一索引，索引名称与主键名称相同。

drop table emp

create table emp(eid int constraint pk primary key,ename varchar(10))

select *from sysindexes where name='pk'  --name的值：如果有索引则用索引名称，没有索引则用表的名字

--当给表添加唯一约束时，自动在该列上创建非聚集的唯一索引   

create table emp(eid int not null constraint uq unique,ename varchar(10))

select *from sysindexes where id=object_id('emp')

sp_helpindex 'emp'    --查询指定表的索引创建在哪些列上

--只有在表上创建主键或唯一约束才可以影响索引
--适合创建索引的列：1。该列频繁使用进行搜索 2。该列用于对数据进行排序
--不适合创建索引的列：1。列中仅有几个不同的值（低基数列）

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]  --创建索引的语法，[]中为可选参数，默认为非聚集。
 INDEX index_name
     ON table_name ( column_name [ ,column_name ]... )

create table emp(eid int not null,ename varchar(10))

create index in_name on emp(ename)  --该语句创建了非聚集非唯一的索引
      --聚集索引不一定唯一，但如果在 CLUSTERED 前加上 unique 约会则为唯一了

--FREETEXT是个谓词，用于搜索含有基于字符的数据类型的列，其中的值符合在搜索条件中所指定文本的含义，
--但不符合表达方式。使用 FREETEXT 时，全文查询引擎内部将 freetext_string 拆分为若干个搜索词，
--并赋予每个词以不同的加权，然后查找匹配。

--语法
FREETEXT ( { column | * } , 'freetext_string' )

--1。全文索引存放在全文目录中
--2。当全文索引的表内容变化的时候，全文目录必须手动更新！（选完全填充）

--示例
--使用 FREETEXT 搜索包含指定字符值的单词
--下例搜索产品描述中含有与 bread、candy、dry 和 meat 相关的词语的所有产品类别，如 breads、candies、dried 和 meats 等。

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE FREETEXT (Description, 'sweetest candy bread and dry meat' )
GO

--CONTAINS一个谓词，用于搜索包含基于字符的数据类型的列，该列与单个词和短语，
--以及与另一个词一定范围之内的近似词精确或模糊（不太精确的）匹配或者加权匹配。

--语法
CONTAINS ( { column | * } , '< contains_search_condition >')
   
--示例
--使用带有 <simple_term> 的 CONTAINS
--下面的示例查找包含词"bottles"且价格为 $15.00 的所有产品。

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
   AND CONTAINS(QuantityPerUnit, 'bottles')
GO

--视图：是一张虚拟表，其内容由查询定义。同真实的表一样，试图包含一系列带有名称的列与行数据。
--但是视图并不在数据库中以存储的数据值集的形式存在。行与列数据来自由定义视图的查询所用的表，
--并且在引用视图时动态生成。
--视图中不保存数据，但是在符合一定条件的前提下可以通过视图对基表的数据进行更新，录入，删除，查询。

create table dept(did int primary key,dname varchar(10))

insert into dept select 1001,'财务部'
union select 1002,'行政部'
union select 1003,'人事部'
union select 1004,'市场部'

drop table dept

drop view a

create view a   --创建视图的语法中必须有 AS 关键字
as   
select *from dept  --* 表示所有的列

--A.当视图中包含了基表所有的非空列的时候:
--1。通过视图向基表进行数据录入

insert into a values(1005,'组织部')

select *from dept

--2。更新数据

update a set dname='accp' where did=1005

--3。删除数据

delete from a where did=1005

--B.当创建的视图没有全部包含基表所有的非空列的时候
--1。录入数据将失败

alter view a    --修改视图的语法
as
select dname from dept

insert into a values('组织部')  --这条语句将不能执行！

--2。删除数据（当指定视图中部分不包含的列的时候，删除失败；当指定视图中包含的列的时候，删除成功）

delete from a where dname='行政部'   

--3。更新数据（同上述要求）

update a set dname='accp' where dname='市场部'

--4。查询数据(同上要求)

select * from a

alter view a   --修改视图
as
select dname from dept
where did<1003   --指定视图中只包含did<1003的部门

select *from a

--A.当创建的视图中带有where条件的时候（视图中不包含基表中所有的非空列的情况）
--录入，删除，更新，查询数据的时候不能指定视图中不存在但是基表中存在的列

--B.当视图中包含基表所有非空列时，如带有where条件

alter view a
as
select *from dept  --用*表示包含了基表中所有非空列
where did<1003

--1。录入数据（在创建视图时所带的where条件对视图录入数据不起作用，数据会录入到基表中的）

insert into a values(1005,'accp')

--可以在创建视图时加上 with check option 使where在录入数据时也起作用

alter view a
as
select *from dept
where did<1003
with check option

--2。删除数据与更新数据时where条件起作用

delete from a where did=1006

update a set dname='bccp' where did=1006

select *from dept

--创建多表连接视图

create table emp
(eid int primary key,ename varchar(10),sal money default 3456,dno int references dept(did))

insert into emp values(1,'rose',default,1001)
insert into emp values(2,'jack',3,1002)
insert into emp values(3,'tom',default,1003)
insert into emp values(4,'mike',1234,1004)

drop table emp

select *from emp

create view a
as
select *from emp,dept
where emp.dno=dept.did

select *from a

--在多表视图中录入数据，当修改会影响到多个基表时，录入数据会失败。

insert into a values(5,'ekin',999,1005,1005,'bccp')

--修改不影响多个基表时，录入数据成功

insert into a(did,dname) values(1005,'cccp')

--删除，更新数据道理同上

--查询数据不受限制。

--游标语法

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

--全局变量 @@FETCH_STATUS 返回被FETCH语句执行的最后游标的状态，而不是任何当前被连接打开的游标状态
--它的值 =0 时，语句成功； =-1 时，语句失败或此行不在结果集中；=-2 时被提取的行不存在。

--SCROLL关键字：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。
--如果在 SQL-92 DECLARE CURSOR 中未指定 SCROLL，则 NEXT 是唯一支持的提取选项。
--如果指定 SCROLL，则不能也指定 FAST_FORWARD。

--select_statement是定义游标结果集的标准 SELECT 语句。
--在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

--游标过程举例

declare cr scroll cursor  --游标的声明
for
select *from emp  
open cr     --打开游标
fetch last from cr   --游标的推进
close cr    --关闭游标
deallocate cr    --释放游标

select *from emp where 1>2
select @@error    --用 @@error 判断sql语句是否正确执行

select @@rowcount   --受上一sql语句影响的函数。它的值若为-则表示不返回任何行

declare employee_cursor cursor
for
select lastname,firstname
from employees for update of lastname
open employee_cursor
fetch next from employee_cursor
while @@fetch_status=0
begin
fetch next from employee_cursor
update employees set firstname='jack' where 1>2
end
close employee_cursor
deallocate employee_cursor

--以上程序为游标应用的更新。从上往下的第四行表示只能更新lastname.
--(实际此例并不是真正通过游标进行更新的！)

begin tran
declare employee_cursor cursor
for
select lastname,firstname
from employees for update of lastname
open employee_cursor
fetch next from employee_cursor
while @@fetch_status=0
begin
fetch next from employee_cursor
update employees set firstname='jack'
where current of employee_cursor  --通过这个where来实现真正的游标更新
end
close employee_cursor
deallocate employee_cursor
rollback

declare employee_cursor cursor
for
select lastname,firstname
from employees
open employee_cursor
fetch next from employee_cursor
while @@fetch_status=0
begin
fetch next from employee_cursor
delete from employees    --通过游标删除数据
where current of employee_cursor
end
close employee_cursor
deallocate employee_cursor

select *from employees

-----------------------------------------------------------------------------------------------------

--存储过程

create proc p1    --这程序本意是让@a1与@a2的值进行交换，
@a int,@b int    --但结果并非如此，因为向过程传递参数时用的是值传递。
as
begin
declare @temp int
set @temp=@a
set @a=@b
set @b=@temp
end

declare @a1 int,@a2 int
set @a1=1
set @a2=2
EXEC p1 @a1,@a2
print @a1
print @a2

alter proc p1    --修改一个过程
@a int output,@b int output  --用output可以实现按地址传递
as
begin
declare @temp int
set @temp=@a
set @a=@b
set @b=@temp
end

declare @a1 int,@a2 int
set @a1=1
set @a2=2
EXEC p1 @a1 output,@a2 output  --向过程传递参数时也要用output
print @a1
print @a2

alter proc p1
@a int,@b int    --一个过程的返回值不能通过参数列表看出来
as
begin
if(@a>@b) return 0   --用return表示返回值，该返回值描述一种状态
return -1
end

declare @res int   --变量@res用来保存返回值
EXEC @res=p1 1,3
print @res

alter proc p1    --结合表的存储过程
as
begin
declare @tab table(eid int)
insert into @tab select eid from emp
select * from @tab
end

exec p1

alter proc p1
@a int=90,@b int   --存储过程参数有默认值的情况
as
begin
declare @c int
select @c=@a+@b
print @c
end         --参数有默认值时传递参数有以下两种方式
   
exec p1 default,10   --1。使用 fefault 传递

exec p1 @b=10    --2。使用定义时的参数名称传递

------------------------------------------------------------------------------------------------------------]

--触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。

create trigger tr   --tr为触发器名称
on emp for insert   --insert 为指定的触发动作，其他动作还有：delete,update，多个动作之间用逗号分隔
as
begin     --begin 与 and 之间为触发器语句主题，其内部可以用事务控制语句
print '不能录入数据！'
rollback
end     --同一个动作可以定义多个触发器，但其执行的次序是随机的

insert into emp select 1001,'rose',2005 --用这条语句录入数据时触发器只触发一次 ，insert触发器触发几次要看有多少insert语句
 union select 1002,'jack',2006

--逻辑表 inserted介绍
    
--录入数据时，数据首先会被存放在inserted逻辑表中，接着等待用户回退或提交的命令，
--如果用户回退，则清空该表数据；如果用户提交，则将该表数据永远写入数据库

--逻辑表结构与触发器结构完全相同，存在于内存中。逻辑表主要用来做数据的审计。

select *into audit_emp from emp where 1>2   --audit_emp表为用来审计的表
alter table audit_emp add descr varchar(20) default '录入数据'

alter trigger tr
on emp for insert
as
begin
print '录入数据'
insert into audit_emp(eid,ename,sal) select *from inserted 
end

--逻辑表 deleted 介绍

--结构和触发器表的结构完全相同，当删除数据的时候该数据首先会被放在deleted逻辑表中，接着等待用户提交或回退命令，
--如果提交则清空该表数据，如果用户回退，则把该表数据放回原处，以保证了数据的一致性

--删除动作不涉及 inserted，录入动作也不涉及 deleted 。

--update 动作发生的时候，历史数据首先会被放在deleted表中，新数据会被放在inserted表中，接着等待用户提交或回退命令。
--如果用户提交，则新数据从inserted表中录入，并同时清空deleted表；如果回退，则将的deleted表中数据恢复，同时清空inserted表。

--使用 OUTPUT 游标参数
OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。

首先，创建以下过程，在 titles 表上声明并打开一个游标：

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles

OPEN @titles_cursor
GO

接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。

USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
   FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

--存储过程、函数、视图和触发器定义
在 syscomments 系统表中保存存储过程、函数、触发器或视图的定义时，可以对其进行加密（例如，如果 SQL Server 系统包含专有的存储过程、函数、触发器或视图，则其定义不应由用户和第三方查看）。

--使用 WITH ENCRYPTION 选项
WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'encrypt_this' AND type = 'P')
   DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO

EXEC sp_helptext encrypt_this

下面是结果集：

The object's comments have been encrypted.

接下来，选择加密存储过程内容的标识号和文本。

SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
   ON c.id = o.id
WHERE o.name = 'encrypt_this'
下面是结果集：

说明  text 列的输出显示在单独一行中。执行时，该信息将与 id 列信息出现在同一行中。

id         text                                                       
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????

(1 row(s) affected)

--触发器加密同存储过程加密

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]    --在这为触发器加密
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}

--加密视图
下例使用 WITH ENCRYPTION 选项并显示计算列、重命名列以及多列。

USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'accounts')
   DROP VIEW accounts
GO
CREATE VIEW accounts (title, advance, amt_due)
WITH ENCRYPTION
AS
SELECT title, advance, price * royalty * ytd_sales
FROM titles
WHERE price > $5
GO

--创建用户自定义函数

--语法
--标量函数

CREATE FUNCTION [ owner_name.] function_name
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS scalar_return_data_type

[ WITH < function_option> [ [,] ...n] ]

[ AS ]

BEGIN
    function_body
    RETURN scalar_expression
END

--内嵌表值函数

CREATE FUNCTION [ owner_name.] function_name
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS TABLE

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

RETURN [ ( ] select-stmt [ ) ]

--多语句表值函数

CREATE FUNCTION [ owner_name.] function_name
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

BEGIN
    function_body
    RETURN
END

< function_option > ::=
    { ENCRYPTION | SCHEMABINDING }  --为函数加密

< table_type_definition > ::=
    ( { column_definition | table_constraint } [ ,...n ] )