创建文件夹:exec xp_cmdshell 'md 盘符:/文件夹名称', no_output
例如:在D盘创建名为:“资料”的文件夹:exec xp_cmdshell 'md d:/资料', no_output
查看文件:exec xp_cmdshell 'dir盘符:/文件夹名称'。例如:exec xp_cmdshell 'dir d:/资料'
判断数据库是否存在:if exists(select * from sysdatabases where name='数据库名称')
drop database 数据库名称
判断表是否存在——if exists(select * from sysobjects where name='表名') drop table 表名
添加约束(5种)——语法:alter table 表名 add constraint 约束名约束类型 具体的约束说明
1.主键约束:alter table 表名 add constraint pk_约束名 primary key(主键约束字段名)
2.外键:alter table表名add constraint fk_约束名foreign key(外键字段名) references 关系表(主键名)
3.唯一约束:alter table 表名 add constraint uq_约束名 unique (唯一约束字段名)
4.检查约束:alter table 表名 add constraint ck_约束名 check (约束检查字段名) --表达式
5.默认约束:alter table 表名 add constraint 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 stuInfo to zhangsanUser
建表的权限:grant create table to 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(表中字段) from stuMarks(表名))
select stuName(表中字段) from stuInfo(表名) where stuNo(表中字段) not in(select stuNo(表中字段) from stuMarks(表名))
exists和 not exists子查询——存在检测的子查询语句
基本语法:if exists 或 not exists(子查询) 语句
表联接:1.内联接(inner join) 2.左外联接(left outer join) 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 原来的表
----------------------------------第五章 事务、索引、视图--------------------------------
事务——是单个的工作单元,一组数据库操作命令,同时成功、同时失败。
开始事务:begin transaction(tran)以作为事务的开始
提交事务:commit transaction(tran)
回滚(撤销)事务:rollback transaction(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 表名.索引名
视图——一种虚拟表,不是数据库中存储的数据值的集合,而是实际表中各个字段的位置
判断视图是否存在——if exists(select * from sysobjects where name='view_视图名')
drop view view_视图名
创建视图——create view view_视图名 as <select 语句>
查询视图——select * from view_视图名
-----------------------------------第六章 存储过程--------------------------------------
存储过程——是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 表名 --查表的约束
例如:exec sp_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 表名 --查看表的主键信息
判断存储过程是否存在——if exists(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语句。
检测触发器是否存在——if exists(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 sysobjects where name='备份表')
创建备份表
select * into 备份表 from deleted --从deleted表中获取被删除的数据
else
将被删除的数据插入到备份表中
insert into 备份表 select * from deleted