sql数据库命令大全|江阴雨辰互联

2023年7月13日发(作者：)

sql数据库命令大全

SQLServer和Oracle的常用函数对比

1.绝对值

S:select abs(-1) value

O:select abs(-1) value from dual

2.取整(大)

S:select ceiling(-1.001) value

O:select ceil(-1.001) value from dual

3.取整（小）

S:select floor(-1.001) value

O:select floor(-1.001) value from dual

4.取整（截取）

S:select cast(-1.002 as int) value

O:select trunc(-1.002) value from dual

5.四舍五入

S:select round(1.23456,4) value 1.23460

O:select round(1.23456,4) value from dual 1.2346

6.e为底的幂

S:select Exp(1) value 2.70451

O:select Exp(1) value from dual 2.71828182

7.取e为底的对数

S:select log(2.70451) value 1 O:select ln(2.70451) value from dual; 1

8.取10为底对数

S:select log10(10) value 1

O:select log(10,10) value from dual; 1

9.取平方

S:select SQUARE(4) value 16

O:select power(4,2) value from dual 16

10.取平方根

S:select SQRT(4) value 2

O:select SQRT(4) value from dual 2

11.求任意数为底的幂

S:select power(3,4) value 81

O:select power(3,4) value from dual 81

12.取随机数

S:select rand() value

O:select _(0,1) value from dual;

13.取符号

S:select sign(-8) value -1

O:select sign(-8) value from dual -1

----------数学函数

14.圆周率

S:Select PI() value 3.97931

O:不知道 ,cos,tan 参数都以弧度为单位

例如：select sin(PI()/2) value 得到1（SQLServer）

,Acos,Atan,Atan2 返回弧度

17.弧度角度互换(SQLServer，Oracle不知道)

DEGREES：弧度-〉角度

RADIANS：角度-〉弧度

---------数值间比较

18. 求集合最大值

S:select max(value) value from

(select 1 value

union

select -2 value

union

select 4 value

union

select 3 value)a

O:select greatest(1,-2,4,3) value from dual

19. 求集合最小值

S:select min(value) value from

(select 1 value

union

select -2 value

union select 4 value

union

select 3 value)a

O:select least(1,-2,4,3) value from dual

20.如何处理null值(F2中的null以10代替)

S:select F1,IsNull(F2,10) value from Tbl

O:select F1,nvl(F2,10) value from Tbl

--------数值间比较

21.求字符序号

S:select ascii('a') value

O:select ascii('a') value from dual

22.从序号求字符

S:select char(97) value

O:select chr(97) value from dual

23.连接

S:select '11'+'22'+'33' value

O:select CONCAT('11','22')||33 value from dual

23.子串位置 --返回3

S:select CHARINDEX('s','sdsq',2) value

O:select INSTR('sdsq','s',2) value from dual

23.模糊子串的位置 --返回2,参数去掉中间%则返回7

S:select patindex('%d%q%','sdsfasdqe') value

O:oracle没发现，但是instr可以通过第四霾问刂瞥鱿执问?BR>select INST R('sdsfasdqe','sd',1,2) value from dual 返回6

24.求子串

S:select substring('abcd',2,2) value

O:select substr('abcd',2,2) value from dual

25.子串代替返回aijklmnef

S:Select STUFF('abcdef', 2, 3, 'ijklmn') value

O:Select Replace('abcdef', 'bcd', 'ijklmn') value from dual

26.子串全部替换

S:没发现

O:select Translate('fasdbfasegas','fa','我' ) value from dual

27.长度

S:len,datalength

O:length

28.大小写转换 lower,upper

29.单词首字母大写

S:没发现

O:select INITCAP('abcd dsaf df') value from dual

30.左补空格（LPAD的第一个参数为空格则同space函数）

S:select space(10)+'abcd' value

O:select LPAD('abcd',14) value from dual

31.右补空格（RPAD的第一个参数为空格则同space函数）

S:select 'abcd'+space(10) value O:select RPAD('abcd',14) value from dual

32.删除空格

S:ltrim,rtrim

O:ltrim,rtrim,trim

33. 重复字符串

S:select REPLICATE('abcd',2) value

O:没发现

34.发音相似性比较(这两个单词返回值一样，发音相同)

S:Select SOUNDEX ('Smith'), SOUNDEX ('Smythe')

O:Select SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual

SQLServer中用Select DIFFERENCE('Smithers', 'Smythers') 比较soundex的差

返回0-4，4为同音，1最高

--------------日期函数

35.系统时间

S:select getdate() value

O:select sysdate value from dual

36.前后几日

直接与整数相加减

37.求日期

S:select convert(char(10),getdate(),20) value

O:select trunc(sysdate) value from dual

select to_char(sysdate,'yyyy-mm-dd') value from dual 38.求时间

S:select convert(char(8),getdate(),108) value

O:select to_char(sysdate,'hh24:mm:ss') value from dual

39.取日期时间的其他部分

S:DATEPART 和 DATENAME 函数（第一个参数决定）

O:to_char函数第二个参数决定

参数---------------------------------下表需要补充

year yy, yyyy

quarter qq, q (季度)

month mm, m (m O无效)

dayofyear dy, y (O表星期)

day dd, d (d O无效)

week wk, ww (wk O无效)

weekday dw (O不清楚)

Hour hh,hh12,hh24 (hh12,hh24 S无效)

minute mi, n (n O无效)

second ss, s (s O无效)

millisecond ms (O无效)

----------------------------------------------

40.当月最后一天

S:不知道

O:select LAST_DAY(sysdate) value from dual

41.本星期的某一天（比如星期日）

S:不知道

O:Select Next_day(sysdate,7) vaule FROM DUAL;

42.字符串转时间

S:可以直接转或者select cast('2004-09-08'as datetime) value

O:Select To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;

43.求两日期某一部分的差（比如秒）

S:select datediff(ss,getdate(),getdate()+12.3) value

O:直接用两个日期相减（比如d1-d2=12.3）

Select (d1-d2)*24*60*60 vaule FROM DUAL;

44.根据差值求新的日期（比如分钟）

S:select dateadd(mi,8,getdate()) value

O:Select sysdate+8/60/24 vaule FROM DUAL;

45.求不同时区时间

S:不知道

O:Select New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;

-----时区参数,北京在东8区应该是Ydt-------

AST ADT 大西洋标准时间

BST BDT 白令海标准时间

CST CDT 中部标准时间

EST EDT 东部标准时间

GMT 格林尼治标准时间

HST HDT 阿拉斯加—夏威夷标准时间

MST MDT 山区标准时间

NST 纽芬兰标准时间

PST PDT 太平洋标准时间

YST YDT YUKON标准时间

配置 SQL Server 数据库

EXEC sp_dboption 'pubs', 'read only', 'True'

此选项把数据库 “pubs” 设置为只读。

EXEC sp_dboption 'pubs' autoshrink true

此选项把符合条件的“pubs” 数据库文件设置为自动周期性收缩。

EXEC sp_dboption 'pubs' single user

此命令每次只允许一个用户访问数据库。

DBCC SHRINKDATABASE(PUBS, 10)

会减小“pubs”数据库中文件的大小，并允许有 10% 的可用空间。

使用 T-SQL 创建表

create table MyTable

( stdID int,

stdName varchar(50)

)

自定义数据类型

EXEC sp_addtype City, 'nvarchar(15)', NULL

EXEC sp_addtype PostCode, 'nvarchar(6)', NULL

EXEC sp_addtype NCode, 'nvarchar(18)', NULL

删除自定义数据类型

EXEC sp_droptype city

使用 T-SQL 创建表

CREATE TABLE Categories

( CategoryID int IDENTITY(1,1),

CategoryName nvarchar(15) NOT NULL,

Description ntext NULL,

Picture image NULL)

实体完整性实现

l 主键约束

CREATE TABLE jobs(

Job_id smallint PRIMARY KEY,

job_desc varchar(50) NOT NULL)

l 唯一约束

CREATE TABLE jobs(

Job_id smallint UNIQUE)

l 标识列

CREATE TABLE jobs(

Job_id smallint IDENTIRY（2,1） PRIMARY KEY)

l Uniqueidentifier数据类型和NEWID 函数

CREATE TABLE Customer

(CustID uniqueidentifier NOT NULL DEFAULT NEWID(),

CustName char(30) NOT NULL)

INSERT Customer VALUES (NEWID(),'ASB')

l 引用完整性实现 ALTER TABLE

ADD CONSTRAINT FK_Orders_Customers

FOREIGN KEY (CustomerID)

REFERENCES ers(CustomerID)

l 更新表结构

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL

ALTER TABLE jobs ADD HIRE_DATE DATETIME

ALTER TABLE Doc_ED DROP COLUMN Column_B

ALTER TABLE Doc_ED WITH NOCHECK ADD CONSTRAINT Exd_Check CHECK (Column_a > 1)

l 在已有字段中增加标识约束时，必须先删除该字段，再新建字段。

alter table mytable drop column userid

alter table mytable add userid int identity(1,1)

l 删除表

DROP TABLE Airlines_Master

l DEFAULT约束

USE Northwind

CREATE TABLE ABC (ASD int Default 8,

ASDE varchar(20) Default 'UNKNOWN')

USE Northwind

ALTER TABLE ers ADD CONSTRAINT DF_contactname DEFAULT

'UNKNOWN' FOR ContactName

l CHECK约束

CREATE TABLE ABCD

(ASD int CHECK(ASD <100) ,

ASDE varchar(80) Default 'UNKNOWN')

USE Northwind

ALTER TABLE Employees ADD CONSTRAINT CK_birthdate

CHECK (BirthDate > '01-01-1900' AND BirthDate < ’01-01-2010’)

PRIMARY KEY约束

CREATE TABLE AWC

(ASD int CHECK(ASD <100) PRIMARY KEY,

ASDE varchar(80) Default 'UNKNOWN')

USE Northwind

ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY

(CustomerID)

l UNIQUE约束

CREATE TABLE AAC

(ASD int CHECK(ASD <100) PRIMARY KEY,

ASDE int UNIQUE)

USE Northwind

ALTER TABLE Suppliers ADD CONSTRAINT U_CompanyName UNIQUE (CompanyName)

l FOREIGN KEY约束

CREATE TABLE ACC

(ASD int CHECK(ASD <100) PRIMARY KEY,

ASDE int FOREIGN KEY REFERENCES AAC(ASD))

USE Northwind

ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT FK_Or_Cu

FOREIGN KEY (CustomerID)

REFERENCES Customers(CustomerID)

通配符

通配符解释例子

'_' 一个字符 Branch Like 'L__'

% 任意长度 Route_Code Like 'AMS-%'

[] 指定范围内 Airbusno Like 'AB0[1-5] '

[^] 不在括号中 Airbusno Like 'AB0[^1-5] '

向表中插入数据

语法：INSERT [INTO] 表名（字段列表）VALUES （值列表）

例：1. 插入指定值的一条记录

INSERT INTO MyTable (PriKey, Description) VALUES (123, 'A description of part 123.')

2. 从查询中获得数据（多条）

INSERT INTO MyTable (PriKey, Description)

SELECT ForeignKey, Description FROM SomeView

更新数据行

语法：UPDATE 表名 SET 字段名 = 新值，… WHERE 条件

例： UPDATE TITLES SET Price = Price + 0.25*Price Where Title_ID

= ‘TC777’

联接：根据表之间的逻辑关系搜索多表中的数据

1．内联接

SELECT … FROM 表1 INNER JOIN 表2 ON 表1.字段1=表2.字段1 WHERE 条件

例：UPDATE TITLES SET Price = Price + 10 From Titles INNER JOIN TitleAuthor

ON _IS = _ID WHERE = 'SuShi,AnyOne?'

2．外联接

左联接：左表与右表相联接的数据和左表的其他数据：

SELECT … FROM 表1 LEFT JOIN 表2 ON 表1.字段1=表2.字段1 WHERE

条件

例：UPDATE TITLES SET Price = Price + 10 From Titles LEFT OUTER JOIN

TitleAuthor

ON _IS = _ID WHERE = 'SuShi,AnyOne?'

右联接：右表与左表相联接的数据和右表的其他数据：

SELECT … FROM 表1 RIGHT JOIN 表2 ON 表1.字段1=表2.字段1 WHERE 条件

例：UPDATE TITLES SET Price = Price + 10 From Titles RIGHT OUTER JOIN TitleAuthor ON _IS = _ID WHERE = 'SuShi,AnyOne?'

全联接：右表与左表相联接的数据及右表和左表的其他数据：

SELECT … FROM 表1 FULL JOIN 表2 ON 表1.字段1=表2.字段1WHERE

条件

3．自联接：表与其自身联接

SELECT … FROM 表1 as A JOIN 表1 as B ON A.字段1=B.字段2 WHERE 条件

删除数据

DELETE FROM 表 WHERE 条件

TRUNCATE TABLE 表 --删除表中所有的记录，但不记日志

查询数据

1. 数据库和所有者限定

SELECT * FROM rs

2. 查询中使用常量和运算符

SELECT title_id +':'+ title + '->' + type As MyTitle FROM titles

3. 聚合函数

SELECT productid ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid

显示所有type的值，并按type分组求平均值

SELECT type, AVG(price) FROM titles WHERE royalty = 10 GROUP BY ALL

type

条件中带有聚合函数的，要用Having 子句

SELECT productid, SUM(quantity) AS total_quantity FROM orderhist

GROUP BY productid HAVING SUM(quantity)>=30

4. 模糊查询

WHERE companyname LIKE '%Restaurant%’

WHERE fax IS NULL

WHERE country IN ('Japan', 'Italy')

WHERE unitprice BETWEEN 10 AND 20

1. 说明：复制表(只复制结构,源表名：a 新表名：b)

SQL: select * into b from a where 1<>1

2.说明：拷贝表(拷贝数据,源表名：a 目标表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;

3.说明：显示文章、提交人和最后回复时间

SQL: select ,me,e from table a,(select max(adddate) adddate from table where =) b

4.说明：外连接查询(表名1：a 表名2：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a

= b.c 5.说明：日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

6.说明：两张关联表，删除主表中已经在副表中没有的信息

SQL:

delete from info where not exists ( select * from infobz where = )

说明：--

SQL:

SELECT , , _DATE, _UPD_DATE

FROM TABLE1,

(SELECT , _DATE, _DATE PREV_UPD_DATE

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,

(SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,

WHERE = （+）

AND D_QTY + NVL(_ONHAND,0) <> _ONHAND ) B

WHERE =

说明：--

SQL:

select * from studentinfo where not exists(select * from student where =) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩

7.说明：

从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）

SQL:

SELECT r, , ee, TO_CHAR(date, 'yyyy') AS telyear,

SUM(decode(TO_CHAR(date, 'mm'), '01', tion)) AS JAN,

SUM(decode(TO_CHAR(date, 'mm'), '02', tion)) AS FRI,

SUM(decode(TO_CHAR(date, 'mm'), '03', tion)) AS MAR,

SUM(decode(TO_CHAR(date, 'mm'), '04', tion)) AS APR,

SUM(decode(TO_CHAR(date, 'mm'), '05', tion)) AS MAY,

SUM(decode(TO_CHAR(date, 'mm'), '06', tion)) AS JUE,

SUM(decode(TO_CHAR(date, 'mm'), '07', tion)) AS JUL,

SUM(decode(TO_CHAR(date, 'mm'), '08', tion)) AS AGU,

SUM(decode(TO_CHAR(date, 'mm'), '09', tion)) AS SEP,

SUM(decode(TO_CHAR(date, 'mm'), '10', tion)) AS OCT,

SUM(decode(TO_CHAR(date, 'mm'), '11', tion)) AS NOV,

SUM(decode(TO_CHAR(date, 'mm'), '12', tion)) AS DEC

FROM (SELECT r, , ee, date, tion

FROM TELFEESTAND a, TELFEE b

WHERE = ) a

GROUP BY r, , ee, TO_CHAR(date, 'yyyy')

8.说明：四表联查问题：

SQL: 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 .....

9.说明：得到表中最小的未使用的ID号

SQL:

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE ID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID

FROM Handle

WHERE NOT HandleID IN (SELECT ID - 1 FROM Handle a)

语句技巧

9.1、一个SQL语句的问题:行列转换

select * from v_temp

上面的视图结果如下:

user_name role_name

-------------------------

系统管理员管理员

feng 管理员 feng 一般用户

test 一般用户

想把结果变成这样:

user_name role_name

---------------------------

系统管理员管理员

feng 管理员,一般用户

test 一般用户

===================

create table a_test(name varchar(20),role2 varchar(20))

insert into a_test values('李','管理員')

insert into a_test values('張','管理員')

insert into a_test values('張','一般用戶')

insert into a_test values('常','一般用戶')

create function join_str(@content varchar(100))

returns varchar(2000)

begin

declare @str varchar(2000)

set @str=''

select @str=@str+','+rtrim(role2) from a_test where [name]=@content

select @str=right(@str,len(@str)-1)

return @str

end

--调用：

select [name],_str([name]) role2 from a_test group by [name]

--select distinct name,_test(name) from a_test

9.2、求助！快速比较结构相同的两表

结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？

============================

给你一个测试方法，从northwind中的orders表取数据。

select * into n1 from orders

select * into n2 from orders

select * from n1

select * from n2

--添加主键，然后修改n1中若干字段的若干条

alter table n1 add constraint pk_n1_id primary key (OrderID)

alter table n2 add constraint pk_n2_id primary key (OrderID)

select OrderID from (select * from n1

union

select * from n2) a group by OrderID having count(*) > 1

应该可以，而且将不同的记录的ID显示出来。

下面的适用于双方记录一样的情况，

select * from n1 where orderid in

(

select OrderID from (select * from n1

union

select * from n2) a group by OrderID having count(*) > 1

) 至于双方互不存在的记录是比较好处理的

--删除n1,n2中若干条记录

delete from n1 where orderID in ('10728','10730')

delete from n2 where orderID in ('11000','11001')

--*************************************************************

-- 双方都有该记录却不完全相同

select * from n1 where orderid in

(

select OrderID from (select * from n1

union

select * from n2) a group by OrderID having count(*) > 1

)

union

--n2中存在但在n1中不存的在10728,10730

select * from n1 where OrderID not in (select OrderID from n2)

union

--n1中存在但在n2中不存的在11000,11001

select * from n2 where OrderID not in (select OrderID from n1)

9.3、四种方法取表里n到m条纪录：

select top m * into 临时表(或表变量) from tablename order by columnname

-- 将top m笔插入

set rowcount n

select * from 表变量 order by columnname desc

select top n * from

(select top m * from tablename order by columnname) a

order by columnname desc

3.如果tablename里没有其他identity列，那么：

select identity(int) id0,* into #temp from tablename

取n到m条的语句为：

select * from #temp where id0 >=n and id0 <= m

如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：

exec sp_dboption 你的DB名字,'select into/bulkcopy',true

4.如果表里有identity属性，那么简单：

select * from tablename where identitycol between n and m

5.如何删除一个表中重复的记录？

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))

--f_key表示是分組字段﹐即主鍵字段

begin

declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer

select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'

exec(@sql)

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name) and

name=@f_key

if @type=56

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id

if @type=167

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''

exec(@sql)

fetch cur_rows into @id,@max

end

close cur_rows

deallocate cur_rows

set rowcount 0

end

select * from systypes

select * from syscolumns where id = object_id('a_dist')

9.4.查询数据的最大排序问题（只能用一条语句写）

CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))

insert into hard values ('A','1',3)

insert into hard values ('A','2',4)

insert into hard values ('A','4',2)

insert into hard values ('A','6',9)

insert into hard values ('B','1',4)

insert into hard values ('B','2',5)

insert into hard values ('B','3',6)

insert into hard values ('C','3',4)

insert into hard values ('C','6',7)

insert into hard values ('C','2',3)

要求查询出来的结果如下：

qu co je

----------- ----------- -----

A 6 9

A 2 4

B 3 6

B 2 5

C 6 7

C 3 4

就是要按qu分组，每组中取je最大的前2位！！

而且只能用一句sql语句

select * from hard a where je in (select top 2 je from hard b where = order by je)

9.5.求删除重复记录的sql语句？

怎样把具有相同字段的纪录删除，只留下一条。

例如，表test里有id,name字段

如果有name相同的记录只留下一条，其余的删除。

name的内容不定，相同的记录数不定。

有没有这样的sql语句？

==============================

A:一个完整的解决方案：

将重复的记录记入temp1表:

select [标志字段id],count(*) into temp1 from [表名]

group by [标志字段id]

having count(*)>1

2、将不重复的记录记入temp1表:

insert temp1

select [标志字段id],count(*) from [表名]

group by [标志字段id]

having count(*)=1

3、作一个包含所有不重复记录的表：

select * into temp2 from [表名]

where 标志字段id in(select 标志字段id from temp1)

4、删除重复表:

delete [表名] 5、恢复表：

insert [表名]

select * from temp2

6、删除临时表:

drop table temp1

drop table temp2

================================

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))

--f_key表示是分組字段﹐即主鍵字段

begin

declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer

select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'

exec(@sql)

open cur_rows fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name) and

name=@f_key

if @type=56

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id

if @type=167

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''

exec(@sql)

fetch cur_rows into @id,@max

end

close cur_rows

deallocate cur_rows

set rowcount 0

end

select * from systypes

select * from syscolumns where id = object_id('a_dist')

10.1. 行列转换--普通

假设有张学生成绩表(CJ)如下

Name Subject Result

张三语文 80

张三数学 90

张三物理 85 李四语文 85

李四数学 92

李四物理 82

想变成

姓名语文数学物理

张三 80 90 85

李四 85 92 82

declare @sql varchar(4000)

set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result

end) ['+Subject+']'

from (select distinct Subject from CJ) as a

select @sql = @sql+' from test group by name'

exec(@sql)

10.2. 行列转换--合并

有表A,

id pid

1 1

1 2

1 3

2 1

2 2

3 1

如何化成表B:

id pid

1 1,2,3 2 1,2

3 1

创建一个合并的函数

create function fmerg(@id int)

returns varchar(8000)

begin

declare @str varchar(8000)

set @str=''

select @str=@str+','+cast(pid as varchar) from 表A where id=@id

set @str=right(@str,len(@str)-1)

return(@str)

End

--调用自定义函数得到结果

select distinct id,(id) from 表A

10.3. 如何取得一个数据表的所有列名

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。

SQL语句如下：

declare @objid int,@objname char(40)

set @objname = 'tablename'

select @objid = id from sysobjects where id = object_id(@objname)

select 'Column_name' = name from syscolumns where id = @objid order by colid

是不是太简单了？呵呵不过经常用阿.

10.4. 通过SQL语句来更改用户的密码

修改别人的,需要sysadmin role

EXEC sp_password NULL, 'newpassword', 'User'

如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa

10.5. 怎么判断出一个表的哪些字段不允许为空？

select COLUMN_NAME from INFORMATION_S where IS_NULLABLE='NO' and TABLE_NAME=tablename

10.6. 如何在数据库里找到含有相同字段的表？

a. 查已知列名的情况

SELECT as TableName, as columnname

From syscolumns a INNER JOIN sysobjects b

ON =

AND ='U'

AND ='你的字段名字'

b. 未知列名查所有在不同表出现过的列名

Select As tablename, As columnname

From syscolumns s1, sysobjects o

Where =

And = 'U'

And Exists (

Select 1 From syscolumns s2

Where = And <>

)

10.7. 查询第xxx行数据

假设id是主键：

select *

from (select top xxx * from yourtable) aa

where not exists(select 1 from (select top xxx-1 * from yourtable) bb where =)

如果使用游标也是可以的

fetch absolute [number] from [cursor_name]

行数为绝对行数

10.8. SQL Server日期计算

a. 一个月的第一天

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

b. 本周的星期一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

c. 一年的第一天

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

d. 季度的第一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

e. 上个月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

f. 去年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

g. 本月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) h. 本月的第一个星期一

select DATEADD(wk, DATEDIFF(wk,0,

dateadd(dd,6-datepart(day,getdate()),getdate())

), 0)

i. 本年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

11.1.获取表结构[把 'sysobjects' 替换成 'tablename' 即可]

SELECT CASE IsNull(, '')

When '' Then ''

Else '*'

End as IsPK,

Object_Name() as t_name,

as c_name,

IsNull(SubString(, 1, 254), '') as pbc_init,

as F_DataType,

CASE IsNull(TYPEPROPERTY(, 'Scale'), '')

WHEN '' Then Cast( as varchar)

ELSE Cast( as varchar) + ',' + Cast( as varchar)

END as F_Scale,

able as F_isNullAble

FROM Syscolumns as A

JOIN Systypes as T

ON ( = ype AND = Object_id('sysobjects') )

LEFT JOIN ( SysIndexes as I

JOIN Syscolumns as A1

ON ( = and = object_id('sysobjects') and ( & 0x800) = 0x800 AND <= ) )

ON ( = AND = index_col('sysobjects', , ) ) LEFT JOIN SysComments as M

ON ( = lt and ObjectProperty(lt, 'IsConstraint') = 1 )

ORDER BY ASC

11.2..提取数据库内所有表的字段详细说明的SQL语句

SELECT

(case when er=1 then else '' end) N'表名',

er N'字段序号',

N'字段名',

(case when COLUMNPROPERTY( ,,'IsIdentity')=1 then '√'else ''

end) N'标识',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

(SELECT name

FROM sysindexes

WHERE (id = ) AND (indid in

(SELECT indid

FROM sysindexkeys

WHERE (id = ) AND (colid in

(SELECT colid

FROM syscolumns

WHERE (id = ) AND (name = ))))))) AND

(xtype = 'PK'))>0 then '√' else '' end) N'主键',

N'类型',

N'占用字节数',

COLUMNPROPERTY(,,'PRECISION') as N'长度', isnull(COLUMNPROPERTY(,,'Scale'),0) as N'小数位数',

(case when able=1 then '√'else '' end) N'允许空',

isnull(,'') N'默认值',

isnull(g.[value],'') AS N'字段说明'

FROM syscolumns a

left join systypes b

on =ype

inner join sysobjects d

on = and ='U' and <>'dtproperties'

left join syscomments e

on lt=

left join sysproperties g

on = AND = d

order by object_name(),er

11.3.快速获取表test的记录总数[对大容量表非常有效]

快速获取表test的记录总数:

select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)

Sql Server实用操作小技巧集合

包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等

1.挂起操作

在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作，要求重启，这里往往重启无用，解决办法：

到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager

删除PendingFileRenameOperations

2.收缩数据库

--重建索引

DBCC REINDEX

DBCC INDEXDEFRAG

--收缩数据和日志

DBCC SHRINKDB

DBCC SHRINKFILE

3.压缩数据库

dbcc shrinkdatabase(dbname)

4.转移数据库给新用户以已存在用户权限

exec sp_change_users_login 'update_one','newname','oldname'

5.检查备份集

RESTORE VERIFYONLY from disk='E:'

6.修复数据库

ALTER DATABASE [dvbbs] SET SINGLE_USER

DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK

ALTER DATABASE [dvbbs] SET MULTI_USER

--CHECKDB 有3个参数:

--REPAIR_ALLOW_DATA_LOSS

-- 执行由 REPAIR_REBUILD 完成的所有修复，包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，以及删除已损坏的文本对象。这些修复可能会导致一些数据丢失。修复操作可以在用户事务下完成以允许用户回滚所做的更改。如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。修复完成后，备份数据库。

--REPAIR_FAST 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。这些修复可以很快完成，并且不会有丢失数据的危险。

--REPAIR_REBUILD 执行由 REPAIR_FAST 完成的所有修复，包括需要较长时间的修复（如重建索引）。执行这些修复时不会有丢失数据的危险。

--DBCC CHECKDB('dvbbs') with NO_INFOMSGS,PHYSICAL_ONLY

SERVER日志清除的两种方法

在使用过程中大家经常碰到数据库日志非常大的情况，在这里介绍了两种处理方法……

方法一

一般情况下，SQL数据库的收缩并不能很大程度上减小数据库大小，其主要作用是收缩日志大小，应当定期进行此操作以免数据库日志过大

1、设置数据库模式为简单模式：打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称（如论坛数据库Forum）-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”，然后按确定保存

2、在当前数据库上点右键，看所有任务中的收缩数据库，一般里面的默认设置不用调整，直接点确定

3、收缩数据库完成后，建议将您的数据库属性重新设置为标准模式，操作方法同第一点，因为日志在一些异常情况下往往是恢复数据库的重要依据

方法二

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

USE databasename -- 要操作的数据库名

SELECT @LogicalFileName = 'databasename_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.删除数据库中重复数据的几个方法

数据库的使用过程中由于程序方面的问题有时候会碰到重复数据，重复数据导致了数据库部分设置不能正确设置……

方法一

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

方法二

有两个意义上的重复记录:

一是完全重复的记录，也即所有字段均重复的记录，

二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。

A、对于第一种重复，比较容易解决，使用

select distinct * from tableName

就可以得到无重复记录的结果集。

如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

B、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下

假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集

select identity(int,1,1) as autoID, * into #Tmp from tableName

select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID

select * from #Tmp where autoID in(select autoID from #tmp2)

最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）

9.更改数据库中表的所属用户的两个方法

大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了，原因是建表的时候采用了当时的数据库用户……

--更改某个表

exec sp_changeobjectowner 'tablename','dbo'

--存储更改全部表

CREATE PROCEDURE _ChangeObjectOwnerBatch

@OldOwner as NVARCHAR(128),

@NewOwner as NVARCHAR(128)

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

SERVER中直接循环写入数据

declare @i int

set @i=1

while @i<30

begin

insert into test (userid) values(@i)

set @i=@i+1 end

11.无数据库日志文件恢复数据库方法两则

数据库日志文件的误删或别的原因引起数据库日志的损坏

方法一

1.新建一个同名的数据库

2.再停掉sql server(注意不要分离数据库)

3.用原数据库的数据文件覆盖掉这个新建的数据库

4.再重启sql server

5.此时打开企业管理器时会出现置疑，先不管，执行下面的语句（注意修改其中的数据库名)

6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用

数据库的脚本创建一个新的数据库,并将数据导进去就行了.

USE MASTER

SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE

GO UPDATE SYSDATABASES SET STATUS =32768 WHERE NAME='置疑的数据库名'

sp_dboption '置疑的数据库名', 'single user', 'true'

DBCC CHECKDB('置疑的数据库名')

update sysdatabases set status =28 where name='置疑的数据库名'

sp_configure 'allow updates', 0 reconfigure with override

sp_dboption '置疑的数据库名', 'single user', 'false'

方法二

设置数据库为紧急模式

停掉SQL Server服务；

把应用数据库的数据文件XXX_移走；

重新建立一个同名的数据库XXX；

停掉SQL服务；

把原来的数据文件再覆盖回来；

运行以下语句，把该数据库设置为紧急模式；

运行 “Use Master

sp_configure 'allow updates', 1

reconfigure with override

Go”

执行结果：

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

已将配置选项 'allow updates' 从 0 改为 1。请运行 RECONFIGURE 语句以安装。

接着运行“update sysdatabases set status = 32768 where name = 'XXX'”

执行结果：

（所影响的行数为 1 行）

重启SQL Server服务；

运行以下语句，把应用数据库设置为Single User模式；

运行“sp_dboption 'XXX', 'single user', 'true'”

执行结果：命令已成功完成。

做DBCC CHECKDB；

运行“DBCC CHECKDB('XXX')”

执行结果：

'XXX' 的 DBCC 结果。

'sysobjects' 的 DBCC 结果。

对象 'sysobjects' 有 273 行，这些行位于 5 页中。

'sysindexes' 的 DBCC 结果。

对象 'sysindexes' 有 202 行，这些行位于 7 页中。

'syscolumns' 的 DBCC 结果。

………

运行以下语句把系统表的修改选项关掉；

运行“sp_resetstatus "XXX"

go sp_configure 'allow updates', 0

reconfigure with override

Go”

执行结果：

在 sysdatabases 中更新数据库 'XXX' 的条目之前，模式 = 0，状态 = 28（状态 suspect_bit = 0），

没有更新 sysdatabases 中的任何行，因为已正确地重置了模式和状态。没有错误，未进行任何更改。

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

已将配置选项 'allow updates' 从 1 改为 0。请运行 RECONFIGURE 语句以安装。

重新建立另外一个数据库；

DTS导出向导

运行DTS导出向导；

复制源选择EmergencyMode的数据库XXX，导入到；

选择“在SQL Server数据库之间复制对象和数据”，试了多次，好像不行，只是复制过来

发布者：admin，转转请注明出处：http://www.yc00.com/news/1689246900a225639.html

sql数据库命令大全

发表回复

评论列表（0条）

联系我们

400-800-8888

sql数据库命令大全

相关推荐

发表回复

评论列表（0条）

联系我们

400-800-8888