--
语
句
功
能
-- 数据操作
SELECT -- 从数据库表中检索数据行和列
INSERT -- 向数据库表添加新数据行
DELETE -- 从数据库表中删除数据行
UPDATE -- 更新数据库表中的数据
-- 数据定义
CREATE TABLE -- 创建一个数据库表
DROP TABLE -- 从数据库中删除表
ALTER TABLE -- 修改数据库表结构
CREATE VIEW -- 创建一个视图
DROP VIEW -- 从数据库中删除视图
CREATE INDEX -- 为数据库表创建一个索引
DROP INDEX -- 从数据库中删除索引
CREATE PROCEDURE -- 创建一个存储过程
DROP PROCEDURE -- 从数据库中删除存储过程
CREATE TRIGGER -- 创建一个触发器
DROP TRIGGER -- 从数据库中删除触发器
CREATE SCHEMA -- 向数据库添加一个新模式
DROP SCHEMA -- 从数据库中删除一个模式
CREATE DOMAIN -- 创建一个数据值域
ALTER DOMAIN -- 改变域定义
DROP DOMAIN -- 从数据库中删除一个域
-- 数据控制
GRANT -- 授予用户访问权限
DENY -- 拒绝用户访问
REVOKE -- 解除用户访问权限
-- 事务控制
COMMIT -- 结束当前事务
ROLLBACK -- 中止当前事务
SET TRANSACTION -- 定义当前事务数据访问特征
-- 程序化 SQL
DECLARE -- 为查询设定游标
EXPLAN -- 为查询描述数据访问计划
OPEN -- 检索查询结果打开一个游标
FETCH -- 检索一行查询结果
CLOSE -- 关闭游标
PREPARE -- 为动态执行准备 SQL 语句
EXECUTE -- 动态地执行 SQL 语句
DESCRIBE -- 描述准备好的查询
-- 数据操作
SELECT -- 从数据库表中检索数据行和列
INSERT -- 向数据库表添加新数据行
DELETE -- 从数据库表中删除数据行
UPDATE -- 更新数据库表中的数据
-- 数据定义
CREATE TABLE -- 创建一个数据库表
DROP TABLE -- 从数据库中删除表
ALTER TABLE -- 修改数据库表结构
CREATE VIEW -- 创建一个视图
DROP VIEW -- 从数据库中删除视图
CREATE INDEX -- 为数据库表创建一个索引
DROP INDEX -- 从数据库中删除索引
CREATE PROCEDURE -- 创建一个存储过程
DROP PROCEDURE -- 从数据库中删除存储过程
CREATE TRIGGER -- 创建一个触发器
DROP TRIGGER -- 从数据库中删除触发器
CREATE SCHEMA -- 向数据库添加一个新模式
DROP SCHEMA -- 从数据库中删除一个模式
CREATE DOMAIN -- 创建一个数据值域
ALTER DOMAIN -- 改变域定义
DROP DOMAIN -- 从数据库中删除一个域
-- 数据控制
GRANT -- 授予用户访问权限
DENY -- 拒绝用户访问
REVOKE -- 解除用户访问权限
-- 事务控制
COMMIT -- 结束当前事务
ROLLBACK -- 中止当前事务
SET TRANSACTION -- 定义当前事务数据访问特征
-- 程序化 SQL
DECLARE -- 为查询设定游标
EXPLAN -- 为查询描述数据访问计划
OPEN -- 检索查询结果打开一个游标
FETCH -- 检索一行查询结果
CLOSE -- 关闭游标
PREPARE -- 为动态执行准备 SQL 语句
EXECUTE -- 动态地执行 SQL 语句
DESCRIBE -- 描述准备好的查询
---
局部变量
declare @id char(10)
--set @id = '10010001'
select @id = '10010001'
declare @id char(10)
--set @id = '10010001'
select @id = '10010001'
---
全局变量
--- 必须以 @@ 开头
--- 必须以 @@ 开头
--IF ELSE
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' -- 打印字符串 'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' -- 打印字符串 'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'
--CASE
use pangu
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end
use pangu
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end
--WHILE CONTINUE BREAK
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x -- 打印变量 x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c -- 打印变量 c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x -- 打印变量 x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c -- 打印变量 c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end
--WAITFOR
-- 例 等待 1 小时 2 分零 3 秒后才执行 SELECT 语句
waitfor delay ’01:02:03’
select * from employee
-- 例 等到晚上 11 点零 8 分后才执行 SELECT 语句
waitfor time ’23:08:00’
select * from employee
-- 例 等待 1 小时 2 分零 3 秒后才执行 SELECT 语句
waitfor delay ’01:02:03’
select * from employee
-- 例 等到晚上 11 点零 8 分后才执行 SELECT 语句
waitfor time ’23:08:00’
select * from employee
***SELECT***
select *(
列名
) from table_name(
表名
) where column_name operator value
ex:( 宿主 )
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
stockname like '[a-zA-Z]%' --------- ([] 指定值的范围 )
stockname like '[^F-M]%' --------- (^ 排除指定范围 )
--------- 只能在使用 like 关键字的 where 子句中使用通配符 )
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stocksex = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序, desc- 降序, asc- 升序
order by 1,2 --------- by 列号
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查询
--------- 除非能确保内层 select 只返回一个行的值,
--------- 否则应在外层 where 子句中用一个 in 限定符
select distinct column_name form table_name --------- distinct 指定检索独有的列值,不重复
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
--------- group by 将表按行分组 , 指定列中有相同的值
having count(*) = 2 --------- having 选定指定的组
select *
from table1, table2
where table1.id *= table2.id -------- 左外部连接, table1 中有的而 table2 中没有得以 null 表示
table1.id =* table2.id -------- 右外部连接
ex:( 宿主 )
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
stockname like '[a-zA-Z]%' --------- ([] 指定值的范围 )
stockname like '[^F-M]%' --------- (^ 排除指定范围 )
--------- 只能在使用 like 关键字的 where 子句中使用通配符 )
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stocksex = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序, desc- 降序, asc- 升序
order by 1,2 --------- by 列号
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查询
--------- 除非能确保内层 select 只返回一个行的值,
--------- 否则应在外层 where 子句中用一个 in 限定符
select distinct column_name form table_name --------- distinct 指定检索独有的列值,不重复
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
--------- group by 将表按行分组 , 指定列中有相同的值
having count(*) = 2 --------- having 选定指定的组
select *
from table1, table2
where table1.id *= table2.id -------- 左外部连接, table1 中有的而 table2 中没有得以 null 表示
table1.id =* table2.id -------- 右外部连接
select stockname from table1
union [all] ----- union 合并查询结果集, all- 保留重复行
select stockname from table2
union [all] ----- union 合并查询结果集, all- 保留重复行
select stockname from table2
***insert***
insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
value (select Stockname , Stocknumber from Stock_table2)---value 为 select 语句
value (select Stockname , Stocknumber from Stock_table2)---value 为 select 语句
***update***
update table_name set Stockname = "xxx" [where Stockid = 3]
Stockname = default
Stockname = null
Stocknumber = Stockname + 4
Stockname = default
Stockname = null
Stocknumber = Stockname + 4
***delete***
delete from table_name where Stockid = 3
truncate table_name ----------- 删除表中所有行,仍保持表的完整性
drop table table_name --------------- 完全删除表
truncate table_name ----------- 删除表中所有行,仍保持表的完整性
drop table table_name --------------- 完全删除表
***alter table*** ---
修改数据库表结构
alter table database.owner.table_name add column_name char(2) null .....
sp_help table_name ---- 显示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ----- 实现删除列的方法(创建新表)
alter table table_name drop constraint Stockname_default ---- 删除 Stockname 的 default 约束
***function(/* 常用函数 */)***
sp_help table_name ---- 显示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ----- 实现删除列的方法(创建新表)
alter table table_name drop constraint Stockname_default ---- 删除 Stockname 的 default 约束
***function(/* 常用函数 */)***
----
统计函数
----
AVG -- 求平均值
COUNT -- 统计数目
MAX -- 求最大值
MIN -- 求最小值
SUM -- 求和
AVG -- 求平均值
COUNT -- 统计数目
MAX -- 求最大值
MIN -- 求最小值
SUM -- 求和
--AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id
--MAX
-- 求工资最高的员工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)
-- 求工资最高的员工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)
--STDEV()
--STDEV() 函数返回表达式中所有数据的标准差
--STDEV() 函数返回表达式中所有数据的标准差
--STDEVP()
--STDEVP() 函数返回总体标准差
--STDEVP() 函数返回总体标准差
--VAR()
--VAR() 函数返回表达式中所有值的统计变异数
--VAR() 函数返回表达式中所有值的统计变异数
--VARP()
--VARP() 函数返回总体变异数
--VARP() 函数返回总体变异数
----
算术函数
----
/***
三角函数
***/
SIN(float_expression) -- 返回以弧度表示的角的正弦
COS(float_expression) -- 返回以弧度表示的角的余弦
TAN(float_expression) -- 返回以弧度表示的角的正切
COT(float_expression) -- 返回以弧度表示的角的余切
/*** 反三角函数 ***/
ASIN(float_expression) -- 返回正弦是 FLOAT 值的以弧度表示的角
ACOS(float_expression) -- 返回余弦是 FLOAT 值的以弧度表示的角
ATAN(float_expression) -- 返回正切是 FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
-- 返回正切是 float_expression1 /float_expres-sion2 的以弧度表示的角
DEGREES(numeric_expression)
-- 把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) -- 把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) -- 返回表达式的指数值
LOG(float_expression) -- 返回表达式的自然对数值
LOG10(float_expression)-- 返回表达式的以 10 为底的对数值
SQRT(float_expression) -- 返回表达式的平方根
/*** 取近似值函数 ***/
CEILING(numeric_expression) -- 返回 >= 表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
FLOOR(numeric_expression) -- 返回 <= 表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
ROUND(numeric_expression) -- 返回以 integer_expression 为精度的四舍五入值返回的数据
-- 类型与表达式相同可为 INTEGER/MONEY/REAL/FLOAT 类型
ABS(numeric_expression) -- 返回表达式的绝对值返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
SIGN(numeric_expression) -- 测试参数的正负号返回 0 零值 1 正数或 -1 负数返回的数据类型
-- 与表达式相同可为 INTEGER/MONEY/REAL/FLOAT 类型
PI() -- 返回值为 π 即 3.1415926535897936
RAND([integer_expression]) -- 用任选的 [integer_expression] 做种子值得出 0-1 间的随机浮点数
SIN(float_expression) -- 返回以弧度表示的角的正弦
COS(float_expression) -- 返回以弧度表示的角的余弦
TAN(float_expression) -- 返回以弧度表示的角的正切
COT(float_expression) -- 返回以弧度表示的角的余切
/*** 反三角函数 ***/
ASIN(float_expression) -- 返回正弦是 FLOAT 值的以弧度表示的角
ACOS(float_expression) -- 返回余弦是 FLOAT 值的以弧度表示的角
ATAN(float_expression) -- 返回正切是 FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
-- 返回正切是 float_expression1 /float_expres-sion2 的以弧度表示的角
DEGREES(numeric_expression)
-- 把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) -- 把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) -- 返回表达式的指数值
LOG(float_expression) -- 返回表达式的自然对数值
LOG10(float_expression)-- 返回表达式的以 10 为底的对数值
SQRT(float_expression) -- 返回表达式的平方根
/*** 取近似值函数 ***/
CEILING(numeric_expression) -- 返回 >= 表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
FLOOR(numeric_expression) -- 返回 <= 表达式的最小整数返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
ROUND(numeric_expression) -- 返回以 integer_expression 为精度的四舍五入值返回的数据
-- 类型与表达式相同可为 INTEGER/MONEY/REAL/FLOAT 类型
ABS(numeric_expression) -- 返回表达式的绝对值返回的数据类型与表达式相同可为
--INTEGER/MONEY/REAL/FLOAT 类型
SIGN(numeric_expression) -- 测试参数的正负号返回 0 零值 1 正数或 -1 负数返回的数据类型
-- 与表达式相同可为 INTEGER/MONEY/REAL/FLOAT 类型
PI() -- 返回值为 π 即 3.1415926535897936
RAND([integer_expression]) -- 用任选的 [integer_expression] 做种子值得出 0-1 间的随机浮点数
---- 字符串函数 ----
ASCII() -- 函数返回字符表达式最左端字符的 ASCII 码值
CHAR() -- 函数用于将 ASCII 码转换为字符
-- 如果没有输入 0 ~ 255 之间的 ASCII 码值 CHAR 函数会返回一个 NULL 值
LOWER() -- 函数把字符串全部转换为小写
UPPER() -- 函数把字符串全部转换为大写
STR() -- 函数把数值型数据转换为字符型数据
LTRIM() -- 函数把字符串头部的空格去掉
RTRIM() -- 函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING() -- 函数返回部分字符串
CHARINDEX(),PATINDEX() -- 函数返回字符串中某个指定的子串出现的开始位置
SOUNDEX() -- 函数返回一个四位字符码
--SOUNDEX 函数可用来查找声音相似的字符串但 SOUNDEX 函数对数字和汉字均只返回 0 值
DIFFERENCE() -- 函数返回由 SOUNDEX 函数返回的两个字符表达式的值的差异
--0 两个 SOUNDEX 函数返回值的第一个字符不同
--1 两个 SOUNDEX 函数返回值的第一个字符相同
--2 两个 SOUNDEX 函数返回值的第一二个字符相同
--3 两个 SOUNDEX 函数返回值的第一二三个字符相同
--4 两个 SOUNDEX 函数返回值完全相同
QUOTENAME() --
函数返回被特定字符括起来的字符串
/*select quotename('abc', '{') quotename('abc')
运行结果如下
----------------------------------{
{abc} [abc]*/
/*select quotename('abc', '{') quotename('abc')
运行结果如下
----------------------------------{
{abc} [abc]*/
REPLICATE() --
函数返回一个重复
character_expression
指定次数的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
运行结果如下
----------- -----------
abcabcabc NULL*/
/*select replicate('abc', 3) replicate( 'abc', -2)
运行结果如下
----------- -----------
abcabcabc NULL*/
REVERSE() --
函数将指定的字符串的字符排列顺序颠倒
REPLACE() -- 函数返回被替换了指定子串的字符串
/*select replace('abc123g', '123', 'def')
运行结果如下
----------- -----------
abcdefg*/
REPLACE() -- 函数返回被替换了指定子串的字符串
/*select replace('abc123g', '123', 'def')
运行结果如下
----------- -----------
abcdefg*/
SPACE() --
函数返回一个有指定长度的空白字符串
STUFF() -- 函数用另一子串替换字符串指定位置长度的子串
STUFF() -- 函数用另一子串替换字符串指定位置长度的子串
---- 数据类型转换函数 ----
CAST() 函数语法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT() 函数语法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])
select cast(100+99 as char) convert(varchar(12), getdate())
运行结果如下
------------------------------ ------------
199 Jan 15 2000
运行结果如下
------------------------------ ------------
199 Jan 15 2000
----
日期函数
----
DAY() -- 函数返回 date_expression 中的日期值
MONTH() -- 函数返回 date_expression 中的月份值
YEAR() -- 函数返回 date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
-- 函数返回指定日期 date 加上指定的额外日期间隔 number 产生的新日期
DATEDIFF(<datepart> ,<number> ,<date>)
-- 函数返回两个指定日期在 datepart 方面的不同之处
DATENAME(<datepart> , <date>) -- 函数以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>) -- 函数以整数值的形式返回日期的指定部分
GETDATE() -- 函数以 DATETIME 的缺省格式返回系统当前的日期和时间
DAY() -- 函数返回 date_expression 中的日期值
MONTH() -- 函数返回 date_expression 中的月份值
YEAR() -- 函数返回 date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
-- 函数返回指定日期 date 加上指定的额外日期间隔 number 产生的新日期
DATEDIFF(<datepart> ,<number> ,<date>)
-- 函数返回两个指定日期在 datepart 方面的不同之处
DATENAME(<datepart> , <date>) -- 函数以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>) -- 函数以整数值的形式返回日期的指定部分
GETDATE() -- 函数以 DATETIME 的缺省格式返回系统当前的日期和时间
----
系统函数
----
APP_NAME() -- 函数返回当前执行的应用程序的名称
COALESCE() -- 函数返回众多表达式中第一个非 NULL 表达式的值
COL_LENGTH(<'table_name'>, <'column_name'>) -- 函数返回表中指定字段的长度值
COL_NAME(<table_id>, <column_id>) -- 函数返回表中指定字段的名称即列名
DATALENGTH() -- 函数返回数据表达式的数据的实际长度
DB_ID(['database_name']) -- 函数返回数据库的编号
DB_NAME(database_id) -- 函数返回数据库的名称
HOST_ID() -- 函数返回服务器端计算机的名称
HOST_NAME() -- 函数返回服务器端计算机的名称
IDENTITY(<data_type>[, seed increment]) [AS column_name])
--IDENTITY() 函数只在 SELECT INTO 语句中使用用于插入一个 identity column 列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() -- 函数判断所给定的表达式是否为合理日期
ISNULL(<check_expression>, <replacement_value>) -- 函数将表达式中的 NULL 值用指定值替换
ISNUMERIC() -- 函数判断所给定的表达式是否为合理的数值
NEWID() -- 函数返回一个 UNIQUEIDENTIFIER 类型的数值
NULLIF(<expression1>, <expression2>)
--NULLIF 函数在 expression1 与 expression2 相等时返回 NULL 值若不相等时则返回 expression1 的值
APP_NAME() -- 函数返回当前执行的应用程序的名称
COALESCE() -- 函数返回众多表达式中第一个非 NULL 表达式的值
COL_LENGTH(<'table_name'>, <'column_name'>) -- 函数返回表中指定字段的长度值
COL_NAME(<table_id>, <column_id>) -- 函数返回表中指定字段的名称即列名
DATALENGTH() -- 函数返回数据表达式的数据的实际长度
DB_ID(['database_name']) -- 函数返回数据库的编号
DB_NAME(database_id) -- 函数返回数据库的名称
HOST_ID() -- 函数返回服务器端计算机的名称
HOST_NAME() -- 函数返回服务器端计算机的名称
IDENTITY(<data_type>[, seed increment]) [AS column_name])
--IDENTITY() 函数只在 SELECT INTO 语句中使用用于插入一个 identity column 列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() -- 函数判断所给定的表达式是否为合理日期
ISNULL(<check_expression>, <replacement_value>) -- 函数将表达式中的 NULL 值用指定值替换
ISNUMERIC() -- 函数判断所给定的表达式是否为合理的数值
NEWID() -- 函数返回一个 UNIQUEIDENTIFIER 类型的数值
NULLIF(<expression1>, <expression2>)
--NULLIF 函数在 expression1 与 expression2 相等时返回 NULL 值若不相等时则返回 expression1 的值
以上是本人收藏的,作者我也不记得了。
1.
查看数据库的版本
select @@version
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数
sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print ''Server Name...............: '' + convert(varchar(30),@@SERVERNAME)
print ''Instance..................: '' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的 SQL
sp_renamedb ''old_dbname'', ''new_dbname''
6. 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时 , 可以用的 fix_orphan_user 脚本或者 LoneUser 过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] ''object'', [@newowner =] ''owner''
注意 : 更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用 add_login_to_aserver 脚本
7. 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8. 查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用 sp_toptables 过程看最大的 N( 默认为 50) 个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用 SP_NChelpindex 过程查看更详细的索引情况
SP_NChelpindex @objname
clustered 索引是把记录按物理顺序排列的,索引占的空间比较少。
对键值 DML 操作十分频繁的表我建议用非 clustered 索引和约束, fillfactor 参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9. 查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext ''@procedure_name''
查看包含某个字符串 @str 的数据对象名称
select distinct object_name(id) from syscomments where text like ''%@str%''
创建加密的存储过程或函数在 AS 前面加 WITH ENCRYPTION 参数
解密加密过的存储过程和函数可以用 sp_decrypt 过程
10. 查看数据库里用户和进程的信息
sp_who
查看 SQL Server 数据库里的活动用户和进程的信息
sp_who ''active''
查看 SQL Server 数据库里的锁的情况
sp_lock
进程号 1--50 是 SQL Server 系统内部用的 , 进程号大于 50 的才是用户的连接进程 .
spid 是进程编号 ,dbid 是数据库编号 ,objid 是数据对象编号
查看进程正在执行的 SQL 语句
dbcc inputbuffer ()
推荐大家用经过改进后的 sp_who3 过程可以直接看到进程运行的 SQL 语句
sp_who3
检查死锁用 sp_who_lock 过程
sp_who_lock
11. 收缩数据库日志文件的方法
收缩简单恢复模式数据库日志,收缩后 @database_name_log 的大小单位为 M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12. 分析 SQL Server SQL 语句的方法 :
set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划
在查询分析器 -> 查询 -> 显示估计的评估计划 (D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13. 出现不一致错误时, NT 事件查看器里出 3624 号错误,修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable(''@error_table_name'',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb(''@error_database_name'',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有 3 个参数 :
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,
以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。
修复完成后,请备份数据库。
repair_fast 进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。
这些修复可以很快完成,并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复,包括需要较长时间的修复(如重建索引)。
执行这些修复时不会有丢失数据的危险。
select @@version
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数
sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print ''Server Name...............: '' + convert(varchar(30),@@SERVERNAME)
print ''Instance..................: '' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的 SQL
sp_renamedb ''old_dbname'', ''new_dbname''
6. 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时 , 可以用的 fix_orphan_user 脚本或者 LoneUser 过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] ''object'', [@newowner =] ''owner''
注意 : 更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用 add_login_to_aserver 脚本
7. 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8. 查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用 sp_toptables 过程看最大的 N( 默认为 50) 个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用 SP_NChelpindex 过程查看更详细的索引情况
SP_NChelpindex @objname
clustered 索引是把记录按物理顺序排列的,索引占的空间比较少。
对键值 DML 操作十分频繁的表我建议用非 clustered 索引和约束, fillfactor 参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9. 查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext ''@procedure_name''
查看包含某个字符串 @str 的数据对象名称
select distinct object_name(id) from syscomments where text like ''%@str%''
创建加密的存储过程或函数在 AS 前面加 WITH ENCRYPTION 参数
解密加密过的存储过程和函数可以用 sp_decrypt 过程
10. 查看数据库里用户和进程的信息
sp_who
查看 SQL Server 数据库里的活动用户和进程的信息
sp_who ''active''
查看 SQL Server 数据库里的锁的情况
sp_lock
进程号 1--50 是 SQL Server 系统内部用的 , 进程号大于 50 的才是用户的连接进程 .
spid 是进程编号 ,dbid 是数据库编号 ,objid 是数据对象编号
查看进程正在执行的 SQL 语句
dbcc inputbuffer ()
推荐大家用经过改进后的 sp_who3 过程可以直接看到进程运行的 SQL 语句
sp_who3
检查死锁用 sp_who_lock 过程
sp_who_lock
11. 收缩数据库日志文件的方法
收缩简单恢复模式数据库日志,收缩后 @database_name_log 的大小单位为 M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12. 分析 SQL Server SQL 语句的方法 :
set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划
在查询分析器 -> 查询 -> 显示估计的评估计划 (D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13. 出现不一致错误时, NT 事件查看器里出 3624 号错误,修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable(''@error_table_name'',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb(''@error_database_name'',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有 3 个参数 :
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,
以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。
修复完成后,请备份数据库。
repair_fast 进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。
这些修复可以很快完成,并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复,包括需要较长时间的修复(如重建索引)。
执行这些修复时不会有丢失数据的危险。
介绍
为什么要将文件保存到数据库呢?理由很多,最直接的就是,将文件放入数据库以后,可以对文件进行更好的管理,特别是文本文件、图片等,如果不使用数据库,数量巨大的时候,很难做到有效的管理和区别。特别是需要作一些与文字密切相关的应用的时候,将文件全部放入数据库是最好的选择,对文字的处理、检索等,都可以直接利用数据库的一些功能,可以做到真正的有效管理。本文举例主要针对以文字为基础的文件,比如 WORD 等,但是,实际上对程序稍微修改,就可以上传所有文件类型。
数据表结构
现在,我们来看存放文件的数据库表结构,这里,我们给出建立表的标准 SQL 语句:
CREATE TABLE tblBooksUpload
(
DocID int NOT NULL IDENTITY Primary Key ,
DocTitle varchar (200) ,
Doc image,
DocType varchar (50) ,
Entrydate datetime Default GetDate()
)
以上的语句中,我们看到数据表 tblBooksUpload 包含五个字段:
字段 DocID 是表的关键字段,数据记录编号;
字段 DocTitle 是用来简单说明上传文件的,如果上传文本文件,我们一般将其设置为文件标题,图像、程序等,就设置为图像、程序的简单介绍;
字段 Doc 是用来存放我们上传的文件的字段,注意,这里将 Doc 字段的设置为 Image 类别;
字段 DocType 用来保存我们上传文件的类型,可能我们奇怪为什么要这个字段呢?其实,这个字段是很重要的,在用户从数据库取得数据的时候,这个字段将用来指定数据字段 Doc 中数据的类别,然后,浏览器根据这个字段来决定呈现给用户的数据;
字段 DateTime 是一个时间字段,我们可以看到该字段的值取自服务器的当前日期。
下面是插入数据的存储过程,我们来看具体代码:
CREATE PROCEDURE uSP_BooksUploadFile
@Title varchar(200),
@Doc image,
@DocType varchar(4)
AS
INSERT tblBooksUpload(DocTitle,Doc,DocType)
VALUES (@Title,@Doc,@DocType)
GO
上传文件的步骤
现在,我们先从文字上了解一下上传文件到数据库的具体步骤,再从代码上来实现:
首先,从客户端取得上传的文件,然后,我们将它放入数据流;
第二,服务器端读取数据流,然后将其保存到缓存;
第三,将缓存数据保存到数据库;
现在,我们一步步来看怎样在程序中实现这些功能。
第一步
当然,首先我们要实现用户在浏览器端自由选择文件,然后上传,这里用户选择文件,当然是要求标准的 Windows 方式,所以,我们在这里使用 Form 的 File 文件组件来给用户选择文件。注意,因为上传文件,所以,在 Form 的属性设置的时候,我们应该设置为: multipart/form-data ,这样,才可以正确上传文件。
下面是上传页面的主要代码:
<form id="frmUpload" method="post" enctype="multipart/form-data" runat="server">
<span>Title</span><br>
<asp:textbox id="txtTitle" runat="server" EnableViewState="False"></asp:textbox>
<asp:requiredfieldvalidator id="valrTitle" runat="server" ErrorMessage="*
Required" ControlToValidate="txtTitle">* Required</asp:requiredfieldvalidator>
<br>
<br>
<span>Docutment to Upload</span><br>
<input id="txtFileContents" type="file" runat="server" NAME="txtFileContents">
<br>
<br>
<asp:button id="btnSubmit" Text="Submit" Runat="server"></asp:button>
</form>
第二步
我们可以将上传的文件通过数据流保存到缓存,缓存的大小和文件的具体大小相同,我们可以使用以下的代码来取得文件的具体大小:
int intDocLen = txtFileContents.PostedFile.ContentLength;
然后,我们可以设置缓存的具体大小了:
byte[] Docbuffer = new byte[intDoclen];
这样设置以后,我们可以将上传文件的内容保存到缓存中:
Stream objStream;
objStream = txtFileContents.PostedFile.InputStream;
objStream.Read(Docbuffer,0,intDocLen);
在以上代码中,读取缓存的时候,从缓存的 0 位置开始,直到整个文件的长度,其实,这就是整个文件或者整个缓存的大小。
第三步
现在我们需要做的就是将缓存数据保存到数据库,我们已经直到数据表结构,这样,我们通过编写简单的 SQL 语句就可以实现这个功能。在上面的内容中,我们编写了一个存储过程,在程序中,我们只要建立 SqlCommand 对象并且将这个存储过程传递给它,并设置 “@Doc” 参数取得缓存数据就可以了:
cmdUploadDoc = new SqlCommand("uSP_BooksUploadFile",BooksConn);
cmdUploadDoc.CommandType = CommandType.StoredProcedure;
cmdUploadDoc.Parameters.Add("@Title ",SqlDbType.VarChar,200);
cmdUploadDoc.Parameters.Add("@Doc",SqlDbType.Image);
cmdUploadDoc.Parameters.Add("@DocType",SqlDbType.VarChar,4);
cmdUploadDoc.Parameters[0].Value = txtTitle.Text;
cmdUploadDoc.Parameters[1].Value = Docbuffer;
cmdUploadDoc.Parameters[2].Value = strDocType;
点击按钮处理代码
private void btnSubmit_Click(object sender, System.EventArgs e)
{
string strDocExt;
//strDocType 用于保存上传文件的类型
string strDocType;
// 用于保存文件大小
int intDocLen;
//Stream 用于读取上传数据
Stream objStream;
SqlConnection BooksConn;
SqlCommand cmdUploadDoc;
if(IsValid)
{
if(txtFileContents.PostedFile != null)
{
// 文件类型
strDocExt = CString.Right
(txtFileContents.PostedFile.FileName,4).ToLower();
switch(strDocExt)
{
case ".doc":
strDocType = "doc";
break;
case ".ppt":
strDocType = "ppt";
break;
case ".htm":
strDocType = "htm";
break;
case ".html":
strDocType = "htm";
break;
case ".jpg":
strDocType = "jpg";
break;
case ".gif":
strDocType = "gif";
break;
default:
strDocType = "txt";
break;
}
// 上传文件具体内容
intDocLen = txtFileContents.PostedFile.ContentLength;
byte[] Docbuffer = new byte[intDocLen];
objStream = txtFileContents.PostedFile.InputStream;
// 文件保存到缓存
// 缓存将保存到数据库
objStream.Read(Docbuffer ,0,intDocLen);
BooksConn = new
SqlConnection("Server=Server;UID=sa;Database=Books");
cmdUploadDoc = new
SqlCommand("uSP_BooksUploadFile",BooksConn);
cmdUploadDoc.CommandType = CommandType.StoredProcedure;
cmdUploadDoc.Parameters.Add("@Title ",SqlDbType.VarChar,200);
cmdUploadDoc.Parameters.Add("@Doc",SqlDbType.Image);
cmdUploadDoc.Parameters.Add("@DocType",SqlDbType.VarChar,4);
cmdUploadDoc.Parameters[0].Value = txtTitle.Text;
cmdUploadDoc.Parameters[1].Value = Docbuffer ;
cmdUploadDoc.Parameters[2].Value = strDocType;
BooksConn.Open();
cmdUploadDoc.ExecuteNonQuery();
BooksConn.Close();
}
}
}
总结
以上我们提到的方法,适合所有类型的文件,对以上代码作适当修改,我们就可以建立一个完全基于数据库的文件管理系统。
为什么要将文件保存到数据库呢?理由很多,最直接的就是,将文件放入数据库以后,可以对文件进行更好的管理,特别是文本文件、图片等,如果不使用数据库,数量巨大的时候,很难做到有效的管理和区别。特别是需要作一些与文字密切相关的应用的时候,将文件全部放入数据库是最好的选择,对文字的处理、检索等,都可以直接利用数据库的一些功能,可以做到真正的有效管理。本文举例主要针对以文字为基础的文件,比如 WORD 等,但是,实际上对程序稍微修改,就可以上传所有文件类型。
数据表结构
现在,我们来看存放文件的数据库表结构,这里,我们给出建立表的标准 SQL 语句:
CREATE TABLE tblBooksUpload
(
DocID int NOT NULL IDENTITY Primary Key ,
DocTitle varchar (200) ,
Doc image,
DocType varchar (50) ,
Entrydate datetime Default GetDate()
)
以上的语句中,我们看到数据表 tblBooksUpload 包含五个字段:
字段 DocID 是表的关键字段,数据记录编号;
字段 DocTitle 是用来简单说明上传文件的,如果上传文本文件,我们一般将其设置为文件标题,图像、程序等,就设置为图像、程序的简单介绍;
字段 Doc 是用来存放我们上传的文件的字段,注意,这里将 Doc 字段的设置为 Image 类别;
字段 DocType 用来保存我们上传文件的类型,可能我们奇怪为什么要这个字段呢?其实,这个字段是很重要的,在用户从数据库取得数据的时候,这个字段将用来指定数据字段 Doc 中数据的类别,然后,浏览器根据这个字段来决定呈现给用户的数据;
字段 DateTime 是一个时间字段,我们可以看到该字段的值取自服务器的当前日期。
下面是插入数据的存储过程,我们来看具体代码:
CREATE PROCEDURE uSP_BooksUploadFile
@Title varchar(200),
@Doc image,
@DocType varchar(4)
AS
INSERT tblBooksUpload(DocTitle,Doc,DocType)
VALUES (@Title,@Doc,@DocType)
GO
上传文件的步骤
现在,我们先从文字上了解一下上传文件到数据库的具体步骤,再从代码上来实现:
首先,从客户端取得上传的文件,然后,我们将它放入数据流;
第二,服务器端读取数据流,然后将其保存到缓存;
第三,将缓存数据保存到数据库;
现在,我们一步步来看怎样在程序中实现这些功能。
第一步
当然,首先我们要实现用户在浏览器端自由选择文件,然后上传,这里用户选择文件,当然是要求标准的 Windows 方式,所以,我们在这里使用 Form 的 File 文件组件来给用户选择文件。注意,因为上传文件,所以,在 Form 的属性设置的时候,我们应该设置为: multipart/form-data ,这样,才可以正确上传文件。
下面是上传页面的主要代码:
<form id="frmUpload" method="post" enctype="multipart/form-data" runat="server">
<span>Title</span><br>
<asp:textbox id="txtTitle" runat="server" EnableViewState="False"></asp:textbox>
<asp:requiredfieldvalidator id="valrTitle" runat="server" ErrorMessage="*
Required" ControlToValidate="txtTitle">* Required</asp:requiredfieldvalidator>
<br>
<br>
<span>Docutment to Upload</span><br>
<input id="txtFileContents" type="file" runat="server" NAME="txtFileContents">
<br>
<br>
<asp:button id="btnSubmit" Text="Submit" Runat="server"></asp:button>
</form>
第二步
我们可以将上传的文件通过数据流保存到缓存,缓存的大小和文件的具体大小相同,我们可以使用以下的代码来取得文件的具体大小:
int intDocLen = txtFileContents.PostedFile.ContentLength;
然后,我们可以设置缓存的具体大小了:
byte[] Docbuffer = new byte[intDoclen];
这样设置以后,我们可以将上传文件的内容保存到缓存中:
Stream objStream;
objStream = txtFileContents.PostedFile.InputStream;
objStream.Read(Docbuffer,0,intDocLen);
在以上代码中,读取缓存的时候,从缓存的 0 位置开始,直到整个文件的长度,其实,这就是整个文件或者整个缓存的大小。
第三步
现在我们需要做的就是将缓存数据保存到数据库,我们已经直到数据表结构,这样,我们通过编写简单的 SQL 语句就可以实现这个功能。在上面的内容中,我们编写了一个存储过程,在程序中,我们只要建立 SqlCommand 对象并且将这个存储过程传递给它,并设置 “@Doc” 参数取得缓存数据就可以了:
cmdUploadDoc = new SqlCommand("uSP_BooksUploadFile",BooksConn);
cmdUploadDoc.CommandType = CommandType.StoredProcedure;
cmdUploadDoc.Parameters.Add("@Title ",SqlDbType.VarChar,200);
cmdUploadDoc.Parameters.Add("@Doc",SqlDbType.Image);
cmdUploadDoc.Parameters.Add("@DocType",SqlDbType.VarChar,4);
cmdUploadDoc.Parameters[0].Value = txtTitle.Text;
cmdUploadDoc.Parameters[1].Value = Docbuffer;
cmdUploadDoc.Parameters[2].Value = strDocType;
点击按钮处理代码
private void btnSubmit_Click(object sender, System.EventArgs e)
{
string strDocExt;
//strDocType 用于保存上传文件的类型
string strDocType;
// 用于保存文件大小
int intDocLen;
//Stream 用于读取上传数据
Stream objStream;
SqlConnection BooksConn;
SqlCommand cmdUploadDoc;
if(IsValid)
{
if(txtFileContents.PostedFile != null)
{
// 文件类型
strDocExt = CString.Right
(txtFileContents.PostedFile.FileName,4).ToLower();
switch(strDocExt)
{
case ".doc":
strDocType = "doc";
break;
case ".ppt":
strDocType = "ppt";
break;
case ".htm":
strDocType = "htm";
break;
case ".html":
strDocType = "htm";
break;
case ".jpg":
strDocType = "jpg";
break;
case ".gif":
strDocType = "gif";
break;
default:
strDocType = "txt";
break;
}
// 上传文件具体内容
intDocLen = txtFileContents.PostedFile.ContentLength;
byte[] Docbuffer = new byte[intDocLen];
objStream = txtFileContents.PostedFile.InputStream;
// 文件保存到缓存
// 缓存将保存到数据库
objStream.Read(Docbuffer ,0,intDocLen);
BooksConn = new
SqlConnection("Server=Server;UID=sa;Database=Books");
cmdUploadDoc = new
SqlCommand("uSP_BooksUploadFile",BooksConn);
cmdUploadDoc.CommandType = CommandType.StoredProcedure;
cmdUploadDoc.Parameters.Add("@Title ",SqlDbType.VarChar,200);
cmdUploadDoc.Parameters.Add("@Doc",SqlDbType.Image);
cmdUploadDoc.Parameters.Add("@DocType",SqlDbType.VarChar,4);
cmdUploadDoc.Parameters[0].Value = txtTitle.Text;
cmdUploadDoc.Parameters[1].Value = Docbuffer ;
cmdUploadDoc.Parameters[2].Value = strDocType;
BooksConn.Open();
cmdUploadDoc.ExecuteNonQuery();
BooksConn.Close();
}
}
}
总结
以上我们提到的方法,适合所有类型的文件,对以上代码作适当修改,我们就可以建立一个完全基于数据库的文件管理系统。