數(shù)據(jù)庫常用語句_百度文庫

1、一、基礎(chǔ)1、說明:創(chuàng)建數(shù)據(jù)庫create DATABASE database-name2、說明:刪除數(shù)據(jù)庫drop database dbname3、說明:備份 sql server- 創(chuàng)建 備份數(shù)據(jù)的 deviceUSE masterEXEC sp_addumpdevice disk, testBack, c:mssql7backupMyNwind_1.dat - 開始 備份BACKUP DATABASE pubs TO testBack4、說明:創(chuàng)建新表create table tabname(col1 type1 not null primary key,col2 type2 not n

2、ull,. 根據(jù)已有的表創(chuàng)建新表:A :create table tab_new like tab_old (使用舊表創(chuàng)建新表 B :create table tab_new as select col1,col2. from tab_old definition only5、說明:刪除新表drop table tabname6、說明:增加一個(gè)列Alter table tabname add column col type注:列增加后將不能刪除。 DB2中列加上后數(shù)據(jù)類型也不能改變,唯一能改變的是增 加 varchar 類型的長度。7、說明:添加主鍵: Alter table tabname

3、add primary key(col說明:刪除主鍵: Alter table tabname drop primary key(col8、說明:創(chuàng)建索引:create unique index idxname on tabname(col.刪除索引:drop index idxname注:索引是不可更改的,想更改必須刪除重新建。9、說明:創(chuàng)建視圖:create view viewname as select statement刪除視圖:drop view viewname10、說明:幾個(gè)簡單的基本的 sql 語句選擇:select * from table1 where 范圍插入:inser

4、t 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總數(shù):select count as totalcount from table1求和:select sum(fiel

5、d1 as sumvalue from table1平均:select avg(field1 as avgvalue from table1最大:select max(field1 as maxvalue from table1最小:select min(field1 as minvalue from table111、說明:幾個(gè)高級查詢運(yùn)算詞A : UNION 運(yùn)算符UNION 運(yùn)算符通過組合其他兩個(gè)結(jié)果表(例如 TABLE1 和 TABLE2并消去表中任何 重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 UNION 一起使用時(shí)(即 UNION ALL,不消 除重復(fù)行。兩種情況下,派生表的每一行不

6、是來自 TABLE1 就是來自 TABLE2。B : EXCEPT 運(yùn)算符EXCEPT 運(yùn)算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行 而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 EXCEPT 一起使用時(shí) (EXCEPT ALL,不消除重復(fù)行。C : INTERSECT 運(yùn)算符INTERSECT 運(yùn)算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派 生出一個(gè)結(jié)果表。當(dāng) ALL 隨 INTERSECT 一起使用時(shí) (INTERSECT ALL,不消除重復(fù) 行。注:使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。12、說明:使用外連接A 、 left

7、 outer join:左外連接(左連接:結(jié)果集幾包括連接表的匹配行,也包括左連接表的所有行。 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:右外連接 (右連接 :結(jié)果集既包括連接表的匹配連接行,也包括右連接表的所有行。 C :full outer join:全外連接:不僅包括符號連接表的匹配行,還包括兩個(gè)連接表中的所有記錄。 二、提升1、說明:復(fù)制表 (只復(fù)制結(jié)構(gòu) , 源表名:a 新表名:b (Access可用 法一:select * into

8、b from a where 11法二:select top 0 * into b from a2、說明:拷貝表 (拷貝數(shù)據(jù) , 源表名:a 目標(biāo)表名:b (Access可用 insert into b(a, b, c select d,e,f from b;3、說明:跨數(shù)據(jù)庫之間表的拷貝 (具體數(shù)據(jù)使用絕對路徑 (Access可用 insert into b(a, b, c select d,e,f from b in 具體數(shù)據(jù)庫 where 條件例子:.from b in &Server.MapPath(.&data.mdb & where.4、說明:子查詢 (表名 1:a 表名 2:bs

9、elect a,b,c from a where a IN (select d from b 或者 : select a,b,c from a where a IN (1,2,35、說明:顯示文章、提交人和最后回復(fù)時(shí)間select a.title,a.username,b.adddate from table a,(select max(adddate adddate from table where table.title=a.title b6、說明:外連接查詢 (表名 1:a 表名 2:bselect a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT

10、 JOIN b ON a.a = b.c7、說明:在線視圖查詢 (表名 1:a select * from (SELECT a,b,c FROM a T where t.a 1;8、說明:between 的用法 ,between 限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值 ,not between不包括select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 數(shù)值 1 and 數(shù)值 29、說明:in 的使用方法select * from table1 where

11、a not in (值 1, 值 2, 值 4, 值 610、說明:兩張關(guān)聯(lián)表,刪除主表中已經(jīng)在副表中沒有的信息delete from table1 where not exists ( select * from table2 wheretable1.field1=table2.field1 11、說明:四表聯(lián)查問題: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 * fr

12、om 日程安排 where datediff(minute,f開始時(shí)間 ,getdate(5 13、說明:一條 sql 語句搞定數(shù)據(jù)庫分頁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 值相同的數(shù)據(jù)中對應(yīng)的 a 最大的記錄的所有信息 (類似這樣 的用法可以用于論壇每月排行榜

13、, 每月熱銷產(chǎn)品分析 , 按科目成績排名 , 等等 .select a,b,c from tablename ta where a=(select max(a from tablename tb where tb.b=ta.b16、說明:包括所有在 TableA 中但不在 TableB和 TableC 中的行并消除所有重復(fù)行 而派生出一個(gè)結(jié)果表(select a from tableA except (select a from tableB except (select a from tableC 17、說明:隨機(jī)取出 10條數(shù)據(jù)select top 10 * from tablename

14、order by newid(18、說明:隨機(jī)選擇記錄select newid(19、說明:刪除重復(fù)記錄Delete from tablename where id not in (select max(id from tablename group by col1,col2,.20、說明:列出數(shù)據(jù)庫里所有的表名select name from sysobjects where type=U21、說明:列出表里的所有的select name from syscolumns where id=object_id(TableName22、說明:列示 type 、 vender 、 pcs 字段,以

15、 type 字段排列, case 可以方便地實(shí)現(xiàn)多 重選擇,類似 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顯示結(jié)果:type vender pcs電腦 A 1電腦 A 1光盤 B 2光盤 A 2手機(jī) B 3手機(jī) C 323、說明:初始化表 table1TRUNCATE

16、TABLE table124、說明:選擇從 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 語句組合時(shí)用的較多where 1=1 是表示選擇全部 where 1=2全部不選,如:if strWhere !=beginset strSQL = select count(* as Total from + tblName + where +strWhereendelsebeginset strSQL =

17、 select count(* as Total from + tblName + end我們可以直接寫成set strSQL = select count(* as Total from + tblName + where 1=1 安定 + strWhere2、收縮數(shù)據(jù)庫-重建索引DBCC REINDEXDBCC INDEXDEFRAG-收縮數(shù)據(jù)和日志DBCC SHRINKDBDBCC SHRINKFILE3、壓縮數(shù)據(jù)庫dbcc shrinkdatabase(dbname4、轉(zhuǎn)移數(shù)據(jù)庫給新用戶以已存在用戶權(quán)限exec sp_change_users_login update_one,newn

18、ame,oldname go5、檢查備份集RESTORE VERIFYONLY from disk=E:dvbbs.bak6、修復(fù)數(shù)據(jù)庫ALTER DATABASE dvbbs SET SINGLE_USERGODBCC CHECKDB(dvbbs,repair_allow_data_loss WITH TABLOCK GOALTER DATABASE dvbbs SET MULTI_USERGO7、日志清除SET NOCOUNT ONDECLARE LogicalFileName sysname,MaxMinutes INT,NewSize INTUSE tablename - 要操作的數(shù)據(jù)

19、庫名SELECT LogicalFileName = tablename_log, - 日志文件名 MaxMinutes = 10, - Limit on time allowed to wrap log. NewSize = 1 - 你想設(shè)定的日志文件的大小 (M - Setup / initializeDECLARE OriginalSize intSELECT OriginalSize = sizeFROM sysfilesWHERE name = LogicalFileNameSELECT Original Size of + db_name( + LOG is +CONVERT(VA

20、RCHAR(30,OriginalSize + 8K pages or +CONVERT(VARCHAR(30,(OriginalSize*8/1024 + MBFROM sysfilesWHERE name = LogicalFileNameCREATE TABLE DummyTrans(DummyColumn char (8000 not nullDECLARE Counter INT,StartTime DATETIME,TruncLog VARCHAR(255SELECT StartTime = GETDATE(,TruncLog = BACKUP LOG + db_name( + W

21、ITH TRUNCATE_ONLY DBCC SHRINKFILE (LogicalFileName, NewSizeEXEC (TruncLog- Wrap the log if necessary.WHILE MaxMinutes DATEDIFF (mi, StartTime, GETDATE( - time has not expiredAND OriginalSize = (SELECT size FROM sysfiles WHERE name =LogicalFileNameAND (OriginalSize * 8 /1024 NewSizeBEGIN - Outer loop

22、.SELECT Counter = 0WHILE (Counter OriginalSize / 16 AND (Counter 50000BEGIN - updateINSERT DummyTrans VALUES (Fill LogDELETE DummyTransSELECT Counter = Counter + 1ENDEXEC (TruncLogENDSELECT Final Size of + db_name( + LOG is +CONVERT(VARCHAR(30,size + 8K pages or + CONVERT(VARCHAR(30,(size*8/1024 + M

23、B FROM sysfilesWHERE name = LogicalFileNameDROP TABLE DummyTransSET NOCOUNT OFF8、說明:更改某個(gè)表exec sp_changeobjectowner tablename,dbo9、存儲更改全部表CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch OldOwner as NVARCHAR(128,NewOwner as NVARCHAR(128ASDECLARE Name as NVARCHAR(128DECLARE Owner as NVARCHAR(128DECLAR

24、E OwnerName as NVARCHAR(128 DECLARE curObject CURSOR FORselect Name = name,Owner = user_name(uidfrom sysobjectswhere user_name(uid=OldOwnerorder by nameOPEN curObjectFETCH NEXT FROM curObject INTO Name, Owner WHILE(FETCH_STATUS=0BEGINif Owner=OldOwnerbeginset OwnerName = OldOwner + . + rtrim(Nameexec sp_changeobjectowner OwnerName, NewOwner end- select name,NewOwner,O