小明的程式記事本: SQL

顯示具有 SQL 標籤的文章。顯示所有文章

顯示具有 SQL 標籤的文章。顯示所有文章

DDL、DML、DCL、DQL定義

2014年9月15日星期一

DDL、DML、DCL、DQL

資料定義語言：
DDL(Data Definition Language)

用來定義資料庫、資料表、檢視表、索引、預存程序、觸發程序、函數等資料庫物件。
可以用來建立、更新、刪除 table,schema,domain,index,view

常見的指令有：
CREATE 建立資料庫的物件
ALTER 變更資料庫的物件
DROP 刪除資料庫的物件

資料操作語言：
DML(Data Manipulation Language)

用來處理資料表裡的資料。

常見的指令有：
INSERT 新增資料到資料表中
UPDATE 更改資料表中的資料
DELETE 刪除資料表中的資料

資料控制語言：
DCL(Data Control Language)

用來控制資料表、檢視表之存取權限，提供資料庫的安全性。

常見的指令有：
GRANT 賦予使用者使用權限
REVOKE 取消使用者的使用權限
COMMIT 完成交易作業
ROLLBACK 交易作業異常，將已變動的資料回復到交易開始的狀態

資料查詢語言：
DQL(Data Query Language)

負責進行資料查詢，不會對資料本身進行修改的語句

用來查詢資料表裡的資料。

指令只有一個：
SELECT 選取資料庫中的資料

各類輔助指令：SELECT,FROM,WHERE,GROUP BY,ORDER BY

用來定義資料庫、資料表、檢視表、索引、預存程序、觸發程序、函數等資料庫物件。常見的指令有：
CREATE 建立資料庫的物件
ALTER 變更資料庫的物件
DROP 刪除資料庫的物件

SQL BULK INSERT

2014年8月5日星期二

情境：來源資料為txt檔，要利用這些資料做處理，例(select,update)

由於sql用 in有1000筆的限制，這時就可以用bulk insert 方法來處理。

取到之後就可以做應用啦。

請注意：檔案路徑是sql那台機器的路徑喔，檔案格式也一定要是Unicode，才不會出現亂碼喔。

create table #temp1
(col1 nvarchar(max))

bulk insert #temp1
from 'd:\temp\test.txt'
with (fieldterminator=',',rowterminator='\n')
go 

select * from #temp1
drop table #temp1

參考：http://beyondrelational.com/modules/2/blogs/70/posts/10891/bulk-insert-to-table-with-specific-columns.aspx

MSDN:http://msdn.microsoft.com/zh-tw/library/ms175915.aspx

SSIS資料型態異常處理

2013年12月9日星期一

最近遇到的問題是

tableA丟資料到tableB發生小數點異常0.12345—>0.12

例：

Table	欄位	型態	值
tableA	grossProfit	decimal(32,5)	0.12345
tableB	grossProfit	decimal(32,5)	0.12

原來是用OLEDB來源元件抓取資料時，該元件自動將tableA.grossProfit欄位判斷為”有效位數32，小數位數2”

才會有這種自動截斷的情況。

解決方法：

先在元件上按右鍵—>顯示進階編輯器—>輸入與輸出屬性頁籤—>OLEDB來源輸出—>輸出資料行—>選取該欄位

並修改1.Precision(有效位數)2.Scal(小數位數)，改成32,5節可。

如何新增資料表中有Identity欄位

2013年10月7日星期一

在補資料時當遇到Identity欄位時，會發生以下錯誤：

訊息 544，層級 16，狀態 1，行 1
當 IDENTITY_INSERT 設為 OFF 時，無法將外顯值插入資料表 'bt_test' 的識別欄位中。

用以下方法則可解決

Create Procedure spx_identityinsert
(
@dbName VARCHAR(100),
@schemaName varchar(100),
@tableName VARCHAR(100),
@insertSql Varchar(MAX)
)
AS
BEGIN
 
 Declare @sql varchar(MAX)
 if(OBJECTPROPERTY(OBJECT_ID(@dbName +'.'+@schemaName +'.'+@tableName ,'U'),'TableHasIdentity') > 0)
 Begin
  Set @sql = 'SET IDENTITY_INSERT ' + @dbName+'.'+@schemaName+'.'+@tableName+ ' ON;'
   Set @sql = @s + @insertSql
   Set @sql = @s + 'SET IDENTITY_INSERT ' + @dbName+'.'+@schemaName+'.'+@tableName+ ' OFF;'
   Execute(@sql)
 End
  
END
Go;
 
/*執行範例：*/
Exec usp_identityinsert 'MyTestDB','dbo','bt_test','Insert into tb_test (id,name) values (9,''neil'')' /*塞字串要跳脫字元*/

SQL 條件式

2013年7月28日星期日

在t-sql裡也是可以寫條件式，方法如下：

if not exists (select * from user where userid=’neil’)

begin

select '第一個判斷式’

end

else if not exists (select * from user where userid=’neil01’)

begin

select ‘第二個判斷式’

end

else

begin

select ‘第三個判斷式’

end

Cursor

2013年1月8日星期二

Cursor可以看成是一個存資料集的物件，並且可以將資料一筆一筆取出來處理。

http://www.dotblogs.com.tw/lastsecret/archive/2010/06/18/15954.aspx

各家SQL連線

2012年8月5日星期日

不同的資料庫得使用不同的連線字串來連接，以下網站可以參加各種資料庫連結字串。
http://www.connectionstrings.com/

資料庫連結四大步驟

2012年4月25日星期三

這四大步驟，任何跟資料庫連結的語法都適用喔

第一，連接資料庫（Connection）。
第二，執行SQL指令（又分成兩大類：取出資料、或是寫入資料）。
第三，自由發揮（通常這一段是畫面或流程的設計）。
第四，關閉資源（如：關閉資料庫的連接）。

參考連結http://www.dotblogs.com.tw/mis2000lab/archive/2008/08/15/4918.aspx

用sql語法檢視TABLE大小

2012年3月11日星期日

declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id

/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id

fetch next from c_tables
into @id
end

select TableName = (select left(name,60) from sysobjects where id = objid),
Rows = convert(char(11), rows),
ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables

如何新增Outlook 2010簽名檔

2011年10月5日星期三

相信很多使用者跟我一樣，從Office 2007換到2010後會覺得很多介面很不習慣

常常有些設定都找不到在哪裡，最近公司要換簽名檔，我就找了老半天@@

不過最後還是問同事才找到了，年紀大了怕忘記所以就來記錄一下囉。

Setp 1.

Step 2.

Step 3.

1.

刪除資料

2011年9月27日星期二

一般刪除Table都是用delete，但是流水號PK不會清掉
通常開發完要將資料庫更新至客戶時，可以先用以下語法將Table清空
truncate table xxxx

參考資料
http://technet.microsoft.com/zh-tw/library/ms177570.aspx

SQL2008建立Linked Server

2011年5月8日星期日

1.伺服器物件-->連結的伺服器-->新增連結的伺服器

2.輸入相關資訊

3.按下確定，建立完成

查詢語法如下：

select * from SQL2008.POSTDB2.dbo.xt_user

SQL2008:LinkServer名稱

POSTDB2:DB名稱

xt_user:table名稱

定序問題

2010年10月8日星期五

今天在新增table後在去查詢時發現錯誤，訊息為：
「無法解析 equal to 作業中 "Chinese_Taiwan_Stroke_CI_AS" 與"Chinese_Taiwan_Stroke_CI_AS_WS" 之間的定序衝突。」
語法為：
select * from a inner join b on a.pk=b.rpk

因為是剛新增的table裡面都還沒有資料，所以就把table drop 掉，在新建
這時候就加入指定定序的語法

CREATE TABLE [dbo].[WINDOW_DEP](
[UserID] [nvarchar](255) collate Chinese_Taiwan_Stroke_CI_AS NULL ,
[DepID] [nvarchar](255) collate Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]

若此Table已有資料的話，那就只能從query去下手了
例：
SELECT WINDOW_DEP.DEPID
FROM WINDOW_DEP
INNER JOIN AT_ORG_ORGUNIT ON AT_ORG_ORGUNIT.nodeID collate Chinese_PRC_Stroke_CI_AS = WINDOW_DEP.DepID
WHERE WINDOW_DEP.UserID = 'Admin'

可參考
http://renjin.blogspot.com/2008/02/collation-conflict.html

暫存table用法

2010年7月22日星期四

過去都是使用 insert #table方式來新增暫存的資料

現在可以用

WITH User (USERID, AGE) as
(
Select USERID, AGE
from dbo.User
)

Select *
From User

可參考

http://www.dotblogs.com.tw/dc690216/archive/2010/02/02/13440.aspx

如何在SQL2008將既有的table及資料產生srcipt語法匯出

2010年6月13日星期日

這是今天同事遇到的問題

他設計了一個新功能，有開了一個新的Table, 而裡面有一些預設資料
今天他要將這個功能更新至客戶那邊，除了提供程式外，也要提供相關的Create Table及insert 預設資料的語法

Step1

Step2

Step3選擇DB

Step4勾選後才會有資料的script

Step5

Step6

Step7

可參考:http://sharedderrick.blogspot.com/2009/03/insert-sql-server-management-studio.html

SQL取出序號

2010年6月1日星期二

之前在寫sql語法時，需要用到序號這種東西，今天同事分享了用法。

select row_number() over (order by pk ) as NO, * from tablename

訂閱：文章 (Atom)