创建文件夹: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
创建一个数据库(库名:学生库)并设置两个数据文件和两个日志文件
create database 学生库
on primary
( 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% )
删除数据库:drop database 学生库(数据库名)
更改数据库中(数据文件学生_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% )
设置数据库(学生库)为只读:exec sp_dboption '学生库','read only',true
设置在同一时间内只有一个用户访问数据库(学生库):exec sp_dboption '学生库','single user'
自定义数据类型:exec sp_addtype city(类型名) ,'nvarchar(15)',null
删除自定义数据类型:exec sp_droptype city(类型名)
use 学生库 打开数据库(学生库)
create table 学生表(表名) 创建一个学生表
( 学号 int not null, 姓名 varchar(20), 爱好 varchar(20), 出生日期 datetime)
删除表:drop table 学生表(表名)
标识约束属性(两种方法):
①在创建表(学生表)的同时,将学号设置标识
create table 学生表
(学号int not null identity(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 ([成绩]>=0 and [成绩]<=100)
②在创建表(成绩表)的同时,将成绩设置检查约束
create table 成绩表(姓名char(20),科目char(20),成绩 int check([成绩]>=0 and [成绩]<=100),学号int foreign key references 学生表(学号))
使用insert插入数据:Insert into 表名 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 成绩=90 where 姓名='张三'
更新多行: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 表名 order by 列名
例如: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 n percent 列名 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 80 and 90
4.is null——select * from 成绩表 where 姓名 is null
select语句小结:
select [TOP][n]或[n percent] <目标列> [as 别名][,<目标列1> as 别名]…
from<表名或视图名>[as 别名]…[where 条件表达式…][group by<列名1>[having<条件表达式>]]
[order by<列名2>[ASC升序/DESC降序]]
系统函数:
1.获取指定字符串中左起第一个字符的ASC码——print ascii('ABCDEF') --结果为:65
2.根据给定的ASC码获取相应的字符——print char(65) --结果为:A
3.获取给定字符串的长度——print len('abcdef') --结果为:6
4.大小写转换——print lower('ABCDEF') --结果为:abcdef;print upper('abcdef') --结果为:ABCDEF
5.去空格
左——print ltrim(' abcd dfd df ') --结果为:'abcd dfd df '
右——print rtrim(' abcd dfd df ') --结果为:' abcd dfd df'
6.求绝对值——print abs(-12) --结果为:12
7.幂——print power(3,2) 3 的 2 次方,--结果为:9。print power(3,3) --结果为:27
8.随机数(0—1000之间的随机数)——print rand() * 1000
9.获取圆周率——print pi() --结果为:3.14159
10.获取系统时间——print getdate() --结果为:月日年时间,如: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) from titles
获取指定时间的特定部分:
年——print year(getdate());月——print month(getdate());日——print day(getdate())
获取指定时间的特定部分
年——print datepart(year, getdate()); 月——print datepart(month, getdate())
日——print datepart(day, getdate()); 小时——print datepart(hh, getdate())
分钟——print datepart(mi, getdate()); 秒——print datepart(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())
加月份——print dateadd(month, 5, getdate())
加天数——print dateadd(day, 5, getdate())
加小时——print dateadd(hour, 5, getdate())
加分钟——print dateadd(mi, 5, getdate())
加秒数——print dateadd(ss, 5, getdate())