¾µäSQLÓï¾ä´óÈ«
Ò»¡¢»ù´¡
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ÀàÐ͵ij¤¶È¡£
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
×¢£ºË÷ÒýÊDz»¿É¸ü¸ÄµÄ£¬Ïë¸ü¸Ä±ØÐëɾ³ýÖØÐ½¨¡£
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£©£¬²»Ïû³ýÖØ¸´ÐС£Á½ÖÖÇé¿öÏ£¬ÅÉÉú±íµÄÿһÐв»ÊÇÀ´×Ô TABLE
Ïà¹ØÎĵµ£º
-----------------------------------------------------
--×÷ÓãºÓÃÓÚsql server 2005 µ¼³öÓû§±íÊý¾Ý×Öµä
--1 ĬÈϵ¼³öµ±Ç°Êý¾Ý¿âÖÐËùÓÐÓû§±í(xtype = 'u')µÄÊý¾Ý×ֵ䣻
--2 ¿ÉÒÔÔÚxtype = 'u'Ìæ»»ÎªÈçÏÂËùʾÓï¾äxtype = 'u' and name in ('son','Person') µ¼³ö×Ô¶¨ÒåµÄ±íµÄÊý¾Ý×ֵ䣻
--3 ¿ÉÒÔÀûÓü¯³É·þÎñSSISµ¼³ ......
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_convert]') and xtype in (N'FN', N'IF', N'TF'))&n ......
·Ö²¼Ê½²éѯ
OPENROWSET
´ÓExcelÈ¡Êý¾Ý
SELECT * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\1.xls',[Sheet1$])
´ÓOracleÈ¡Êý¾Ý
SELECT * from OPENROWSET('MSDAORA.1','NetServiceName ......
ת×Ô£ºhttp://hong9270503.blog.163.com/blog/static/127292320091611319516/
ͨ¹ýSQL*PLUSÎÒÃÇ¿ÉÒÔ¹¹½¨ÓѺõÄÊä³ö£¬Âú×ã¶àÑù»¯Óû§ÐèÇó¡£
¡¡¡¡
±¾Àýͨ¹ý¼òµ¥Ê¾Àý£¬½éÉÜͨ¹ýsql*plusÊä³öxls£¬htmlÁ½ÖÖ¸ñʽÎļþ.
¡¡¡¡ Ê×ÏÈ´´½¨Á½¸ö½Å±¾:
¡¡¡¡
1.main.sql
¡¡¡¡ ÓÃÒÔÉèÖû·¾³£¬µ÷ÓþßÌ幦Äܽű¾
¡¡¡¡ 2.¹¦Äܽű¾-ge ......
¡¾SQL¡¿ÐÐÁÐת»»
http://space.itpub.net/519536/viewspace-609167
Ò»¡¢ÁбäÐÐ
1.´´½¨²âÊÔ±ítest£¬²¢³õʼ»¯ÊµÑéÊý¾Ý
sec@ora10g> create table test (name_id varchar2(10), name varchar2(10));
sec@ora10g> insert into test values ('01','Andy1');
sec@ora10g> insert into test values ('01','Andy2'); ......