SQL常用操作

xiangmei0617

好就没有看SQL了，下来看下好了

yxbsir

不错不错....真是受益良多啊..............

lqsugang

两种常见的打开SQL数据库的方法

在用友软件使用过程中，常发生互斥、冲突等现象，主要原因是系统管理中有异常任务，如果清理不掉或清理不及时，会影响工作。本文就以在数据库中“清除异常任务”为例，讲述两种常见的打开SQL数据库的方法。
一、        安装了SQL企业版，含有“企业管理器”组件（例子中的SQL版本为2000）。
1、开始---程序---Microsoft SQL Server---企业管理器。

2、进入企业管理器，逐层展开控制台目录，直至看到数据库。

3、根据要修改的内容打开具体数据库即可。
以本文“清除异常任务”为例，打开UFSystem库，找到UA_Task（功能网络控制表），UA_TaskLog（站点网络控制表）两个表。

4、在要打开的表上右键，选择打开表---返回所有行。



5、看到表中的记录，进行编辑操作即可。
   以本文“清除异常任务”为例，最好是在所有用户都退出用友软件后，把全部记录选中，删除即可。即所有用户都退出用友软件，还能在该库中留有记录，即为异常任务。表中每个字段的含义，大家仔细观察都可以发现，不一一叙述了。
二、安装了MSDE，系统中有Office中的Access组件。
（一）、通过Access迅速打开某个库中的所有表。（例子中的Office版本为2000）
1、        开始---程序--- Microsoft Access。

2、        弹出打开数据库对话框，选取消。

3、        文件---新建，弹出对话框，选“项目（现有数据库）”，确定。

4、        把该数据库保存至任意位置，默认为“我的文档”。起个名字，选创建。
5、根据实际情况，选出要打开的数据库。
以本文“清除异常任务”为例，第1项选数据库名称，可以在下拉按钮中选出；第2项输入登录服务器的信息一般选“使用Windows NT集成安全设置”；第3项选具体要打开的数据库，即UFSystem，确定。

5、        显示出打开库中的所有表，双击表，进行编辑操作即可。
以本文“清除异常任务”为例，分别打开UA_Task，UA_TaskLog两个表，把所有记录选中，删除即可。

（二）、通过ODBC数据源打开某个库中的具体某个表。
1、开始---程序--- Microsoft Access。

2、        弹出打开数据库对话框，选“空Access数据库”，确定。

3、        把该数据库保存至任意位置，默认为“我的文档”。起个名字，选创建。

4、        在数据库对话框上，空白处右键，有链接表，选择。

5、        弹出对话框，文件类型中选最下面的ODBC Databases。

6、        弹出对话框，选机器数据源，点击右下角，新建。

7、        弹出对话框，直接下一步。

8、        拉滚动条到最下面，选SQL Server，下一步。

9、        提示“完成”，点击完成。第1行名称可随意写，第2行可不写，第3行服务器在下拉按钮中选出。下一步。

10、直接下一步即可。

11、在“更改默认的数据库为”处打勾，下拉按钮中选中要修改的数据库。下一步。
   以本文“清除异常任务”为例，选中UFSsytem。

12、提示“完成”，点击完成。再点击确定。

13、返回至选择数据源对话框。选中刚创建的连接，确定。

14、弹出的对话框中，选中要修改的表，确定。可以一次选多个表，依次单击即可。
    以本文“清除异常任务”为例，选中UA_Task，UA_TaskLog两个表。

15、返回数据库操作界面，双击表，进行编辑操作即可。
以本文“清除异常任务”为例，分别打开UA_Task，UA_TaskLog两个表，把所有记录选中，删除即可。

三、        两种方法的比较：
1、速度方面。如果在数据库不是很多的情况下，第一种方法是最快的，其次是第二种方法中的第1种方法，最后是第二种方法中的第2种方法。如果数据库很多，而且计算机配置不是很高，则第二种方法中的第1种方法最快，其次是第二种方法中的第2种方法，第一种方法最慢。
2、可操作性方面。第二种方法要通俗易懂些，容易上手。而第一种方法对于对SQL不熟悉的朋友来说，容易误操作，从而产生不良后果。
注意事项：直接对数据库进行操作，有一定的危险性。故大家在做任何数据库操作的时候，都要提前进行数据备份，以免产生不必要的损失。
此文完。

goodxing

好啊，下来有空研究研究。

bluesky310

不好下啊，怎么才能多加积分啊。

piaoxue2002

学习中，谢谢楼主

twtdnwfn

最近有客户对U8合同管理，有意向，
像大家求些U8合同管理的资料，为谢

xuehan5612

xuehan5612

:o :D

xuehan5612

SQL server 常用操作。希望对各位有所帮助
（一） 修复数据库
ALTER  DATABASE  [dvbbs]  SET  SINGLE_USER
GO
DBCC  CHECKDB(''dvbbs'',repair_allow_data_loss)  WITH  TABLOCK
GO
ALTER  DATABASE  [dvbbs]  SET  MULTI_USER
GO

（二） 删除数据库中重复数据的几个方法

declare  @max  integer,@id  integer
declare  cur_rows  cursor  local  for  select  主字段,count(*)  from  表名  group  by  主字段  having  count(*)  >  1
open  cur_rows
fetch  cur_rows  into  @id,@max
while  @@fetch_status=0
begin
select  @max  =  @max  -1
set  rowcount  @max
delete  from  表名  where  主字段  =  @id
fetch  cur_rows  into  @id,@max
end
close  cur_rows
set  rowcount  0

（三） 数据库的备份和恢复措施
一、备份数据库  

1、打开SQL企业管理器，在控制台根目录中依次点开Microsoft  SQL  Server
2、SQL  Server组-->双击打开你的服务器-->双击打开数据库目录
3、选择你的数据库名称（如论坛数据库Forum）-->然后点上面菜单中的工具-->选择备份数据库
4、备份选项选择完全备份，目的中的备份到如果原来有路径和名称则选中名称点删除，然后点添加，如果原来没有路径和名称则直接选择添加，接着指定路径和文件名，指定后点确定返回备份窗口，接着点确定进行备份  

二、还原数据库

1、打开SQL企业管理器，在控制台根目录中依次点开Microsoft  SQL  Server
2、SQL  Server组-->双击打开你的服务器-->点图标栏的新建数据库图标，新建数据库的名字自行取
3、点击新建好的数据库名称-->然后点上面菜单中的工具-->选择恢复数据库
4、 在弹出来的窗口中的还原选项中选择从设备-->点选择设备-->点添加-->然后选择你的备份文件名-->添加后点确定返回，这 时候设备栏应该出现您刚才选择的数据库备份文件名，备份号默认为1（如果您对同一个文件做过多次备份，可以点击备份号旁边的查看内容，在复选框中选择最新 的一次备份后点确定）-->然后点击上方常规旁边的选项按钮
5、在出现的窗口中选择在现有数据库上强制还原，以及在恢复完成状态中选择使数 据库可以继续运行但无法还原其它事务日志的选项。在窗口的中间部位的将数据库文件还原为这里要按照你SQL的安装进行设置（也可以指定自己的目录），逻辑 文件名不需要改动，移至物理文件名要根据你所恢复的机器情况做改动，如您的SQL数据库装在D:\Program  Files\Microsoft  SQL  Server\MSSQL\Data，那么就按照您恢复机器的目录进行相关改动改动，并且最后的文件名最好改成您当前的数据库名（如原来是 zw0001.mdf，现在的数据库是zw0002，就改成zw0002.mdf），日志和数据文件都要按照这样的方式做相关的改动（日志的文件名是. ldf结尾的），这里的恢复目录您可以自由设置，前提是该目录必须存在（如您可以指定d:\sqldata\zw0002.mdf或者d:\ sqldata\zw0002.ldf），否则恢复将报错
6、修改完成后，点击下面的确定进行恢复，这时会出现一个进度条，提示恢复的进度，恢复 完成后系统会自动提示成功，如中间提示报错，请记录下相关的错误内容并询问对SQL操作比较熟悉的人员，一般的错误无非是目录错误或者文件名重复或者文件 名错误或者空间不够或者数据库正在使用中的错误，数据库正在使用的错误您可以尝试关闭所有关于SQL窗口然后重新打开进行恢复操作，如果还提示正在使用的 错误可以将SQL服务停止然后重起看看，至于上述其它的错误一般都能按照错误内容做相应改动后即可恢复

三、设定每日自动备份数据库

强烈建议有条件的用户进行此操作！
1、打开企业管理器，在控制台根目录中依次点开Microsoft  SQL  Server-->SQL  Server组-->双击打开你的服务器
2、然后点上面菜单中的工具-->选择数据库维护计划器
3、下一步选择要进行自动备份的数据-->下一步更新数据优化信息，这里一般不用做选择-->下一步检查数据完整性，也一般不选择
4、下一步指定数据库维护计划，默认的是1周备份一次，点击更改选择每天备份后点确定
5、 下一步指定备份的磁盘目录，选择指定目录，如您可以在D盘新建一个目录如：d:\databak，然后在这里选择使用此目录，如果您的数据库比较多最好选 择为每个数据库建立子目录，然后选择删除早于多少天前的备份，一般设定4－7天，这看您的具体备份要求，备份文件扩展名一般都是bak就用默认的
6、下一步指定事务日志备份计划，看您的需要做选择-->下一步要生成的报表，一般不做选择-->下一步维护计划历史记录，最好用默认的选项-->下一步完成
7、完成后系统很可能会提示Sql  Server  Agent服务未启动，先点确定完成计划设定，然后找到桌面最右边状态栏中的SQL绿色图标，双击点开，在服务中选择Sql  Server  Agent，然后点击运行箭头，选上下方的当启动OS时自动启动服务
8、这个时候数据库计划已经成功的运行了，他将按照您上面的设置进行自动备份  

修改计划：
1、 打开企业管理器，在控制台根目录中依次点开Microsoft  SQL  Server-->SQL  Server组-->双击打开你的服务器-->管理-->数据库维护计划-->打开后可看到你设定的计划，可以进行修改或者删 除操作  

五、数据的转移（新建数据库或转移服务器）

一般情况下，最好使用备份和还原操作来进行转移数据，在特殊情况 下，可以用导入导出的方式进行转移，这里介绍的就是导入导出方式，导入导出方式转移数据一个作用就是可以在收缩数据库无效的情况下用来减小（收缩）数据库 的大小，本操作默认为您对SQL的操作有一定的了解，如果对其中的部分操作不理解，可以咨询动网相关人员或者查询网上资料
1、将原数据库的所有表、存储过程导出成一个SQL文件，导出的时候注意在选项中选择编写索引脚本和编写主键、外键、默认值和检查约束脚本选项
2、新建数据库，对新建数据库执行第一步中所建立的SQL文件
3、用SQL的导入导出方式，对新数据库导入原数据库中的所有表内容
利用数据库日志恢复数据到时间点的操作
可能有不少朋友遇到过这样的问题：
update或delete语句忘带了where子句，或where子句精度不够，执行之后造成了严重的后果，
这种情况的数据恢复只能利用事务日志的备份来进行，所以如果你的SQL没有进行相应的全库备份
或不能备份日志（truncate  log  on  checkpoint选项为1），那么就无法进行数据的恢复了，或者
只能恢复到最近一次的备份的数据了。

以下简单说明恢复数据方法：
1，如果误操作之前存在一个全库备份（或已有多个差异备份或增量备份），首先要做的事就是进
进行一次日志备份（如果为了不让日志文件变大而置trunc.  log  on  chkpt.选项为1那你就死翘了）
backup  log  dbName  to  disk='fileName'
2，恢复一个全库备份，注意需要使用with  norecovery，如果还有其他差异或增量备份，则逐个恢
复
restore  database  dbName  from  disk='fileName'  with  norecovery
3，恢复最后一个日志备份即刚做的日志备份，指定恢复时间点到误操作之前的时刻
restore  log  dbName  from  disk='fileName'

69873366

谢谢搂住啊。对我挺有用的

zjl7612

好啊，下来有空研究研究。

pump

好啊，谢谢

kyzy3721

看看吧真希望是我想要的~
那种啊！

idiocyjun

网上很多吖。。我空间里就有相关的操作，鉴于很多人下不了（其实我也老下不了。。唉。太笨）我直接发上来好了

一、基础
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

二、提升
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

三、技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1” 是表示选择全部   “where 1=2”全部不选，
如：
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
我们可以直接写成
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere
2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
5、检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT
USE     tablename             -- 要操作的数据库名
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名
@MaxMinutes = 10,               -- Limit on time allowed to wrap log.
        @NewSize = 1                  -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
  FROM sysfiles
  WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
  FROM sysfiles
  WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
  (DummyColumn char (8000) not null)
DECLARE @Counter   INT,
        @StartTime DATETIME,
        @TruncLog  VARCHAR(255)
SELECT  @StartTime = GETDATE(),
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  
      AND (@OriginalSize * 8 /1024) > @NewSize  
  BEGIN -- Outer loop.
    SELECT @Counter = 0
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
      BEGIN -- update
        INSERT DummyTrans VALUES ('Fill Log')  
        DELETE DummyTrans
        SELECT @Counter = @Counter + 1
      END   
    EXEC (@TruncLog)  
  END   
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
  FROM sysfiles
  WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner 'tablename','dbo'
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name   as NVARCHAR(128)
DECLARE @Owner  as NVARCHAR(128)
DECLARE @OwnerName  as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name'   = name,
  'Owner'   = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN  curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN     
if @Owner=@OldOwner
begin
  set @OwnerName = @OldOwner + '.' + rtrim(@Name)
  exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
   insert into test (userid) values(@i)
   set @i=@i+1
end

[ 本帖最后由 idiocyjun 于 2007-8-13 17:25 编辑 ]