用友维护人员常用SQL语句（下）

yyc7634

                                                                     用友维护人员常用SQL语句（下）

数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同
-----------------------------------------------------------------
取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)
-----------------------------------------------------------------
查看硬盘分区:
EXEC master..xp_fixeddrives
-----------------------------------------------------------------
比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
    =
   (select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
-----------------------------------------------------------------
杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
-----------------------------------------------------------------
记录搜索:
开头到N条记录
Select Top N * From 表
----------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID  Desc
----------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc
-----------------------------------------------------------------
修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
-----------------------------------------------------------------
获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
-----------------------------------------------------------------
获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
-----------------------------------------------------------------
查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
-----------------------------------------------------------------
查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
-----------------------------------------------------------------
查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
-----------------------------------------------------------------
查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
Select * From TableName Order By CustomerName
-----------------------------------------------------------------
--建表时自动建立主键约束：
create table a(b char(4),c int,constraint 主键名 primary key(c))
  
--或者：
create table a(a char(4) cint primary key)
--或者：
create table a(a char(4) cint constraint 主键名 primary key)
---------------------
创建:
create table 表名
add constraint 约束名
unique 约束条件
---------------------
修改:
alter table 表名
add constraint 约束名
unique 约束条件
---------------------
删除:
alert table 表名
drop constraint 约束名
如不知道或忘记了约束可用sp_help 来查询
-----------------------------------------------------------------
--创建索引：
create index 索引名 on 表名(字段名)
-----------------------------------------------------------------
--为一个表添加外键：
alter table 表名 add constraint 外键名 foreign key(字段）references 表名（字段）
-----------------------------------------------------------------
--创建一个标识列
identity(seed,range) 创建一个标识列，与null,not null并列。seed表示种子，即初始值；range表示增长幅度。
-----------------------------------------------------------------
使用一个数据库之前要引用他：use 数据库名
-----------------------------------------------------------------
exists()判断子查询的结果是否存在，返回true or false  
-----------------------------------------------------------------
object_id('对象名') 返回该对象名对应的Id,该id存储在sysobjects表中。
-----------------------------------------------------------------  
局部临时表 #xxx ，只能被当前会话访问，在该会话结束后自动消失。
-----------------------------------------------------------------

全局临时表 ##xx , 可以供多个用户使用，在该会话结束后自动消失。
-----------------------------------------------------------------
为表 添加/修改/删除列 ：alter table 表名 add 列名 type/alter column 列名 newtype/drop 列名  
-----------------------------------------------------------------
用系统过程 sp_reZGXX_XM 重新命名表和列： sp_reZGXX_XM 原表名，新表名 sp_reZGXX_XM "表名.原列名"，新列名  
-----------------------------------------------------------------
如果想显示的在identity列中插入值，则需要先设置：set identity_insert 表名 on
    插入完毕后最好设置：set identity_insert 表名 off  
-----------------------------------------------------------------
truncate table 表名 删除表格的所有数据，速度很快。  
-----------------------------------------------------------------
统计函数中除了count(*) 之外，都忽略空值（null).  
-----------------------------------------------------------------
由于text和image类型数据很长，在查询之前可以通过设置全局变量textsize来指定返回数据的长度，set textsize 50
    如果想查阅全局变量textsize的值：select @@textsize  
-----------------------------------------------------------------
通过reaDtext 读取text的数据：
     declare @var varbinary(16)
     select @var=textptr(c) from test where a=10
     readtext test.c @var 4 3  
-----------------------------------------------------------------
利用writetext往text 或 image列中写入值：
    declare @var varbinary(16)
    select @var=textptr(c) from test where a=10
    writetext test.c @var 'zhongguo '
-----------------------------------------------------------------
like 也是唯一可以在text列上使用的操作符
-----------------------------------------------------------------
逻辑操作符优先级：NOT > AND > OR  
-----------------------------------------------------------------
在group by 一个记录集时，所有的null组成一组。
-----------------------------------------------------------------
带有group by 子句的 select中可以有where子句，但是where 子句必须放在group by 前面。
-----------------------------------------------------------------
如果group by 子句中用了all,即 group by all xxx ,则不符合检索条件的记录也显示，但不参与统计。  
-----------------------------------------------------------------
having 中，只能包含 group by子句中 指定的列，也可以包含统计函数。where中可指定任何列，但是不能用统计函数  
-----------------------------------------------------------------
having 子句从最终结果中将不满足该条件的分组去掉  
-----------------------------------------------------------------
不带group by子句时也可以使用having子句，并将整个查询结果作为一个组，但是，由于出现在选择列表中的列
    和出现在having子句中的列必须是group by 子句中的列，所以，当不带group by子句时，不能在having子句和
    选择列表中直接使用列名，只能使用统计函数。  
-----------------------------------------------------------------
当在group by子句后指定order by子句时，只能在order by子句中指定group by子句中的列或者统计函数
-----------------------------------------------------------------
在进行union运算时，自动删除结果中的重复行，如果使用all选项 ，则可以将所有行显示在结果中：union all  
-----------------------------------------------------------------
在union时，合并结果集中的列名有第一个查询给出，所以后面进行排旬时一定要注意order by 子句中的字段名  
-----------------------------------------------------------------
可以通过 select fieldslist into 新表名 from 表名，来创建一个新表，并将当前表中的数据全部插入到新
    表中，但是做这个操作之前需要保证数据库选项 select into/bulkcopy 设置为true。方法如下：
    use master /*设置命令必须在master数据库中进行*/
    sp_dboption 数据库a名，"select into/bulkcopy",true /*设置数据库选项*/
    use 数据库a名
    checkpoint /*使设置结果生效*/  
-----------------------------------------------------------------
如果要将统计结果或者计算结果插入到新表中，必须以标题的形式给出列名，如：
    select a,b=avg(c) into mm from nn group by a
-----------------------------------------------------------------
随即取出N条记录的方法：select top N * from 表名 order by newid()  
-----------------------------------------------------------------
创建唯一约束：create table a(b int not nul constraint 约束名 unique,c char(10) null)
    或者：create table a(b int,c char(10),constraint 约束名 unique(b))  
-----------------------------------------------------------------
为变量赋值方法：set @xxx=??? ，如果变量的值取自一个查询的话，需要用select, 如：select @xxx=??? from ??? where ????
    如果要返回一个记录集，但是不是从一个表格，而是全部是系统变量或自定义变量组成。则不必写from子句：select ??,??,??  
-----------------------------------------------------------------
创建一个返回一张表的函数：
    create function fn_Tree(@Id int)
    returns table @tb (id int ,fid int)
    as
    begin
    insert @tb select id,fid from tablename where fid=@id
    while exists (select 1 from tablename where fid in (select id from @tb) and id not in (select id from @tb) )
    insert @tb select id,fid from tablename where fid in (select id from @tb) and id not in (select id from @tb)
    return
    end
    表的字段为id,fid
    insert @tb select 语句，将查询结果插入到当前的表格(@tb)中
    调用：
    select * from dbo.fn_Tree(0)
    go
    select * from dbo.fn_Tree(1
    go  
-----------------------------------------------------------------
删除表
Drop table命令用于删除一个表格或者表中的所有行。其语法格式为：
drop table "tablename"
下面举个例子：
drop table employee;
为了删除整个表（包括所有的行），可以使用drop table命令后加上tablename。Drop table命令跟从表中删除所有记录是不一样的：
　　删除表中的所有记录是留下表格（只是它是空的）以及约束信息；而drop table是删除表的所有信息，包括所有行、表格以及约束信息等等。
-----------------------------------------------------------------
升级问题解决方案
如果升级失败，请先打开升级日志：U8安装目录\Admin\下文件名为 "UFDATA_"+账套号+"_"+年度.txt，查看详细的错误信息；

如果升级提示错误为"错误信息:-2147217900"表示SQL Server 此时无法获取 LOCK 资源。请在活动用户数较少时重新运行您的语句，或者请求系统管理员检查 SQL Server 锁和内存配置。解决办法：
打开SQL Server查询分析器，在Master中运行以下语句：
sp_configure 'locks','2147483647'
reconfigure  with override
重启动Server。

在升级之前，建议先在查询分析器中执行 DBCC CHECKDB(年度库名称) 语句，检查年度库数据库是否有一致性错误，如果发现错误，请按照SQL Server的提示进行修复，修复后再进行升级。
-----------------------------------------------------------------
数据库置疑 及帐套年结情况分析:
年度数据库物理文件一般存放在下面命名规则的文件夹下
X:\U8SOFT\Admin\服务器名\ZT帐套号\年度
年度数据库文件命名：
数据库名为：UFDATA_帐套号_年度
逻辑文件名  物理文件名
------------------------------------
Ufmodel              UFDATA.MDF ----------数据文件
Ufmodel_LOG          UFDATA.LDF ----------日志文件
附：
====================================
另外对于帐套而言，每个帐套还需要一下文件
数据库名为：UFMeta_帐套号
逻辑文件名  物理文件名
--------------------------------
UFMeta       UFMeta.mdf ----------数据文件
UFMeta_Log  UFMeta.ldf ----------日志文件

直接拷贝覆盖与分离后附加是不一样的，不要直接拷贝覆盖（还会出现质疑）
另外，对于置疑和正常数据库不要在sql企业管理器中直接右键删除，否则数据库物理文件就没有了且无痕迹
use ufsystem
--查看帐套信息
select * from ua_account
--查看某帐套年度帐信息
select * from ua_account_sub where cacc_ID=010
--查看某帐套年结情况(bclosing为1表示已结)
--说明：如果是在其他机器上捉的年结直接拷贝过来的话，不会回写ua_account_sub表的bclosing字段，就需要手工加上
select cacc_id as 帐套,iyear as 年度,bclosing as 是否年结
from ua_account_sub where cacc_ID=010

--查看所有数据库（位置，大小，使用情况）
sp_helpdb
--查看指定数据库位置，大小，使用情况）
sp_helpdb 'ufdata_001_2010'

select * FROM UA_user
update UA_user set cPassword=null where cUser_Name='demo'

--查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
--或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
--查询所有数据库
select * from master..sysdatabases

--将数据库附加到服务器。
EXEC sp_attach_db @dbname = N'UFDATA_009_2008',
   @filename1 = N'D:\U8SOFT\ADMIN\SERVER1\ZT009\2008\UFMeta.mdf',
   @filename2 = N'D:\U8SOFT\ADMIN\SERVER1\ZT009\2008\UFMeta.ldf'

--将数据库从服务器分离。
EXEC sp_detach_db 'UFDATA_009_2008', 'true'

北斗七星

上是什么啊

webcore

SQL语句要配合数据词典用更好

zys1312

继续学习！谢谢分享！

yyc7634

zys1312 发表于 2012-6-12 10:01

                               
登录/注册后可看大图

继续学习！谢谢分享！

不客气，有用就好

zhengronghua

学习学习，非常感谢！

yyc7634

zhengronghua 发表于 2012-6-13 14:31

                               
登录/注册后可看大图

学习学习，非常感谢！

货与行家。。。。。。

873759373

好东西啊，呵呵，收藏

jwp2007

真好,我在学习SQL,谢谢分享!!

yanbeiyanyun

非常感谢！

tzdth

谢谢分享！谢谢

ZZ5201314

真是一个好东西的呢

huangbinbin

不错，谢谢楼主分享，怎么没有上？

余味

好东西。值得拥有

leo3208

不错