sqlserverデータベース操作大全-常用文/テクニック集錦/経典文
本文は累計整理で、ちょっと散らかっているので、間に合って見ましょう.
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
☆
☆
☆ sql
☆
☆
☆ 2012 -8
☆
☆ ：
☆
☆ ： ， , ！
☆
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
MS SQL ，
(-) ：1. 2.
and or not
where
not and or
coalesce
coalesce(i.ProductID,d.ProductID)
@@rowcount
SQL
select * from where like '%[ - ]%'
select * from where like '%[a-z]%'
-- ，
select --
insert --
delete --
update --
--
create table --
drop table --
alter table --
create view --
drop view --
create index --
drop index --
create proceduer --
drop proceduer --
create trigger --
drop trigger --
create schema --
drop schema --
create domain --
alter domain --
drop domain --
--
grant --
deny --
revoke --
--
commit --
rollback --
set transaction --
-- SQL
declare --
explan --
open --
fetch --
close --
prepare -- SQL
execute -- SQL
describe --
------------------SQL -----------------
：
insert into ( 1， 2...) values ( 1， 2...)
：
insert into select * from
：insert into ( 1， 2...) select ( 1， 2...) from
insert select ：
SELECT
INSERT INTO SELECT
INSERT INTO SELECT
insert select ：
select * into from
----------SQL truncate table delete drop ----------
truncate table Where Delete ,
truncate table Delete ， 。
Delete ， 。
truncate table ， 。
truncate table ， 、 、 。
。 ， Delete。
FOREIGN KEY ， truncate table， Where Delete 。
truncate table ， 。
truncate table 。
truncate,delete,drop ：
: delete where delete
:truncate where delete, drop
:
1.truncate delete ( )
drop (constrain), (trigger), (index);
/ , invalid .
2.delete dml, rollback segement , ;
trigger, .
truncate,drop ddl, , rollback segment , . trigger.
3.delete extent, (high w2atermark)
drop
truncate minextents extent, reuse storage;
truncate ( ).
4. , : drop> truncate > delete
5. : drop truncate, .
：
delete, where . .
, drop
. , truncate .
, trigger, delete.
, truncate reuse stroage, /
--------------------------- --------------------------
1.UNION
SELECT NAME FROM SOFTBALL
UNION
SELECT NAME FROM FOOTBALL
distinct ( )
select distinct * from
with ties ( )
select Top 10 with ties * from
2.UNION ALL
SELECT NAME FROM SOFTBALL
UNION ALL
SELECT NAME FROM FOOTBALL
3.INTERSECT
SELECT * FROM FOOTBALL
INTERSECT
SELECT * FROM SOFTBALL
4.MINUS ( )
SELECT * FROM FOOTBALL
MINUS
SELECT * FROM SOFTBALL
5.in( )
SELECT * FROM FRIENDS WHERE STATE IN('CA','CO','LA')
6.BEWTEEN ( )
SELECT * FROM PRICE WHERE WHOLESALE BETWEEN 0.25 AND 0.75
7. (||)
SELECT (NAMEa || NAMEb) as FROM FRIENDS
SELECT (NAMEa || ',' || NAMEb) as FROM FRIENDS --
8.STARTING WITH ( like , sql )
SELECT 1， 2... FROM WHERE STARTING WITH('Ca')
ORDER BY ( )
：SELECT * FROM ORDER BY
：SELECT * FROM ORDER BY DESC
： ， ORDER
BY 1
GROUP BY ( )
SELECT FROM GROUP BY
HAVING ( )
SELECT 1，AVG( 2) FROM GROUP BY HAVING AVG( 3)>66
---
WHERE
：SELECT O.NAME,O.PARTNUM,P.PARTNUM FROM ORDERS as O,PART as P WHERE O.PARTNUM > P.PARTNUM
---
WHERE F.PARTNUM = S.PARTNUM AND F.DESCRIPTION <> S.DESCRIPTION
------------------------ --------------------------
VARIANCE ( )
：SELECT VARIANCE( ) AS FROM -- int double
STDDEV ( )
：SELECT STDDEV( ) AS FROM -- int double
ADD_MONTHS ( )
：SELECT ADD_MONTHS(ENDDATE,1) AS FROM --ENDDATE datetime
ADD_MONTHS(ENDDATE,1) where
LAST_DAY ( )
：SELECT LAST_DAY(ENDDATE) AS FROM
DISTINCT ( , )
：SELECT DISTINCT FROM
MONTHS_BETWEEN ( )
1：SELECT MONTHS_BETWEEN(ENDDATE,STARTDATE) AS FROM
2：SELECT * FROM WHERE MONTHS_BETWEEN(DATETIME1,DATETIME2)>0
SYSDATE ( )
：SELECT DISTINCT SYSDATE FROM
---------------------- --------------------------
ABS()
CEIL()
FLOOR()
MOD(A,B) A B
SIGN() -1 , 1 ,
SQRT() ,
----------------------- ---------------------------
CHR() ASCLL ,
：SELECT CHR( ) FROM
CONCAT() || ，
：SELECT CONCAT( 1, 2) FROM
INITCAP() ,
：SELECT INITCAP( ) as FROM
LOWER()
UPPER()
LENGTH()
---------------------- ----------------------
TO_CHAR()
TO_NUMBER()
---
GREATEST() , ；
：SELECT GREATEST(‘ALPHA’,’BRAVO’,’FOXTROT’,’DELTA’,’FP’) FROM
LEAST() ！
：SELECT DISTINCT LEAST(34,567,3,45,1090) FROM
USER()
：SELECT DISTINCT USER FROM
------------------- ---------------
EXISTS
，EXIST true。 ，EXIST false。
：
SELECT NAME FROM ORDERS
WHERE EXISTS(SELECT * FROM ORDERS WHERE NAME ='MOSTLY HARMLESS')
ANY/SOME ANY SOME
ANY , TRUE
：
IN ,IN 。
ANY SOME 。
：
SELECT NAME FROM ORDERS WHERE NAME > ANY
(SELECT NAME FROM ORDERS WHERE NAME ='JACKS BIKE')
ALL TRUE，ALL 。
：
SELECT NAME FROM ORDERS WHERE NAME <> ALL
(SELECT NAME FROM ORDERS WHERE NAME ='JACKS BIKE')
------------------- --------------------
“@” ， DECLARE 。
：DECLARE @
SELECT SET
： SELECT @ =
SET @ =
， 。
。
， “@@” 。
、
---------------------- - - ------------------
， 。
4 ： （Atomicity）、 （Consistency）、 （Isolation） （Durability）， ACID 。
： ， ， 。
： ， 。
： ， ， 。
， 。
。
： ， ， ， ， 。
a、
T-SQL （begin transaction） （commit transaction） （rollback transaction）
b、
， T-SQL 。
， ， ， 。
：set implicit_transaction on;
c、
，SQL Server 。 ， begin transaction ，
T-SQL 。
， set implicit_transaction on ， ， ， 。
： begin transaction
： commit transaction
： rollback transaction
：create view as <select >
：drop view
select ：
1.order by ， select top
2.into
3.
：create unique 【clustered | nonclustered】 index on ( ) 【with fillfactor=x】
unique ，
clustered , nonclustered ，
fillfactor ， ， 0-100 ，
：drop index .
( )。
(order by)
------------------------- ----------------------
SQL ：
DDL 、 ， ， ， — (CREATE，ALTER，DROP，DECLARE)
DML ， ， ， — (SELECT，DELETE，UPDATE，INSERT)
DCL ， ， ， — (GRANT，REVOKE，COMMIT，ROLLBACK)
, ：
1、 ：
CREATE DATABASE db1(db1 ， )
on primary -- primary ，
(
--
name='MySchool_data', --
filename='D:\project\MySchool_data.mdf', --
size=5MB, --
maxsize=100MB, --
filegrowth=15% --
)
log on
(
-- ，
name='MySchool_log',
filename='D:\project\MySchool_data.ldf',
size=2MB,
filegrowth=1MB
)
2、 ：
drop database db1(db1 ， )
3、 ： sql server
--- device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
---
BACKUP DATABASE pubs TO testBack
4、 ：
create table tb1
(
Id int not null primary key, --
one int identity(1,1), --
name varchar not null, --
phone nvarchar(100), --
...
)
：
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 tb1
use MySchool -- MySchool
if exists(select * from MySchool where name='Student') --exist ，
drop table Student
6、 ：
：Alter table add /
：
alter table
add constraint
-- ( StudentNo )
alter table Student
add constraint PK_stuNo primary key (StudentNo)
-- ( )
alter table Student
add constraint UQ_stuID unique ( )
-- ( ， “ ”)
alter table Student
add constraint DF_stuAddress default (' ') for Address
-- ( 1980 1 1 )
alter table Student
add constraint CK_stuBornDate check (BornDate >= '1980-01-01')
-- ( Student Result ， StudentNo)
alter table Result
add constraint FK_stuNo
foreign key (stuNo) references Student(stuNo)
：
， ， （ ）， ：
ALTER TABLE DROP CONSTRAINT
GO
ALTER TABLE DROP COLUMN
GO
：
alter table Student
drop constraint
：
alter table Student
add constraint PK_stuNo
7、 ：
：Alter table tabname add primary key(ID)( ，ID ， ）
：
：Alter table tabname drop primary key(ID)（ ）
8、 ：
：create [unique] index idxname on tabname(col….)
：drop index idxname
： ， 。
9、 ：
：create view viewname as select statement
alter view yourviewname as...
drop view yourviewname as...
alter view yourviewname with encryption as...
10、 ： sql
：select * from table1 where Id=1(Id=1 ， ）
：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：
： ， 。
， sql
1、 ： ( , ：a ：b) (Access )
：select * into b from a where 1<>1 （ SQlServer）
：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
( Randomize ， SQL )
， ， 。
ASP“ ” 。 ：
Randomize
RNumber = Int(Rnd*499) +1
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... ...
end if
objRec.MoveNext
Wend
。 ， 1 500 ( 500 )。
， ID 、 RNumber。 THEN 。
RNumber 495， 。
500 ， ，
。 ?
SQL， recordset， ：
Randomize
RNumber = Int(Rnd*499) + 1
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")
RNumber ID， 。 ， “ ” 。
Recordset ， 。
Random ， 。
Random SQL 。
recordset ， ， ：
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
10 ( 10 )， BETWEEN 。
， SELECT ( ID )：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"
： 9 。
，
Access ：SELECT top 10 * From ORDER BY Rnd(id)
Sql server:select top n * from order by newid()
mysql select * From Order By rand() Limit n
Access ( ,Access , Access SQL , , )
select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...
SQL ...
：
SQL ：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
Access ：SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename;
Conn.Execute
Execute
SQL 。 SQL ， ：
1. SQL ， 。 ：
Set = .Execute("SQL ")
Execute ， ， ， Set ， ， 。
2. SQL ， 。 ：
.Execute "SQL " [, RecordAffected][, Option]
·RecordAffected ， ，SQL ， 。 ， SQL 。
·Option ， adCMDText， ADO， Execute 。 ， 。
·BeginTrans、RollbackTrans、CommitTrans
。BeginTrans ;RollbackTrans ;CommitTrans ， 。
， ， ; ， ， 。
BeginTrans CommitTrans ， ， 。
， Error ， Error 0， ， 。
Error Error ， 。
SQL
DELETE
DELETE ： ， FROM ， WHERE ， DELETE 。
：DELETE [table.*] FROM table WHERE criteria
：DELETE * FROM table WHERE criteria=' '
：table 。
criteria ， 。
Execute DROP 。 ， ， 。
DELETE， ; ， 。
UPDATE
UPDATE， !!!!!!!!!!!
ORACLE
A ( ID ,FIRSTNAME,LASTNAME )
B( ID,LASTNAME)
A ID,FIRSTNAME
B ID,LASTNAME
B LASTNAME A LASTNAME 。 ID 。
update a set a.lastname=(select b.lastname from b where a.id=b.id)
sql
1.
select @@version
2.
exec master..xp_msver
3.
sp_configure
4.
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
print 'Server Name...............：' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................：' + convert(varchar(30),@@SERVICENAME)
5.
sp_helpdb
SQL
sp_renamedb 'old_dbname', 'new_dbname'
6.
sp_helplogins
sp_helpsrvrolemember
, fix_orphan_user LoneUser
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
： 。
add_login_to_aserver
,
sp_helprotect
7.
sp_helplinkedsrvlogin
sp_helpremotelogin
8.
sp_spaceused @objname
sp_toptables N( 50)
sp_helpindex @objname
SP_NChelpindex
SP_NChelpindex @objname
clustered ， 。
DML clustered ，fillfactor 。
sp_helpconstraint @objname
9.
use @database_name
sp_stored_procedures
sp_helptext '@procedure_name'
@str
select distinct object_name(id) from syscomments where text like '%@str%'
AS WITH ENCRYPTION
sp_decrypt
10.
sp_who
SQL Server
sp_who 'active'
SQL Server
sp_lock
1--50 SQL Server , 50 .
spid ,dbid ,objid
SQL
dbcc inputbuffer ()
sp_who3 SQL
sp_who3
sp_who_lock
sp_who_lock
11.
dbcc sqlperf(logspace)
， ， @database_name_log M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12. SQL Server SQL ：
set statistics time {on | off}
set statistics io {on | off}
-> -> (D)-Ctrl-L
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13. ，NT 3624 ，
，
alter database [@error_database_name] set single_user
dbcc checktable('@error_table_name',repair_allow_data_loss)
dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 3 ：
repair_allow_data_loss 、 ， ， 。
。
， ， 。
， 。
， 。
repai*_**st 、 ， 。
， 。
repair_rebuild repai*_**st ， （ ）。
。