Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

sqlÃæÊÔÌâÄ¿

Sql³£ÓÃÓï·¨ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
 
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK) Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
 
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
 
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_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(1,2)
ɾ³ý£ºdelete from table1 where
·¶Î§¸üУºupdate table1 set field1=1 where
·¶Î§²éÕÒ£ºselect * from table1 where field1 like ’%1%’ ---likeµÄÓï·¨ºÜ¾«Ã²é×ÊÁÏ!
ÅÅÐò£ºselect * from table1 order by field1,field2 [desc]
×ÜÊý£ºselect count * as totalcount from table1
ÇóºÍ£ºselect sum(field1) as sum from table1
ƽ¾ù£ºselect avg(field1) as avg from table1
×î´ó£ºselect max(field1) as max from table1


Ïà¹ØÎĵµ£º

¹ØÓÚSQLÓï¾äCountµÄÒ»µãϸ½Ú

countÓï¾äÖ§³Ö*¡¢ÁÐÃû¡¢³£Á¿¡¢±äÁ¿,²¢ÇÒ¿ÉÒÔÓÃdistinct¹Ø¼ü×ÖÐÞÊΣ¬ ²¢ÇÒcount(ÁÐÃû)²»»áÀÛ¼ÆnullµÄ¼Ç¼¡£ÏÂÃæËæ±ãÓÃһЩÀý×Óʾ·¶Ò»ÏÂcountµÄ¹æÔò£º±ÈÈç¶ÔÈçϱí×öͳ¼Æ£¬ËùÓÐÁÐÕâÀï¶¼ÓÃsql_variantÀàÐÍÀ´±íʾ¡£
if (object_id ('t_test' )> 0 )
    drop table t_test
go
create table t_test (a ......

SQLÄ£ºý²éѯÓï¾äºÍEscapeתÒå×Ö·û

SQLÄ£ºý²éѯÓï¾äºÍEscapeתÒå×Ö·û(2008-09-22 14:26:01)
±êÇ©£ºfrom ×ªÒå×Ö·û sql µ¥ÒýºÅ Í¨Åä·û escape it  
ͨÅä·ûÃèÊöʾÀý%°üº¬Áã¸ö»ò¸ü¶à×Ö·ûµÄÈÎÒâ×Ö·û´®¡£WHERE title LIKE '%computer%' ½«²éÕÒ´¦ÓÚÊéÃûÈÎÒâλÖõİüº¬µ¥´Ê computer µ ......

SQLµÄÈýÖÖ·ÖÒ³·½·¨

±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º 
SELECT TOP Ò³¼Ç¼ÊýÁ¿ *
from ±íÃû
WHERE (ID NOT IN
  (SELECT TOP (ÿҳÐÐÊý*(Ò³Êý-1)) ID
  from ±íÃû
  ORDER BY ID))
  ORDER BY ID
//×Ô¼º»¹¿ÉÒÔ¼ÓÉÏһЩ²éѯ ......

My PL/SQL practice 8/2/10

SQLPlus  :http://www.orafaq.com/wiki/SQL*Plus_FAQ
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm
1. Transfer values from a sql scripts:
   CNT=`sqlplus -s username/password1@dbname @getUVQuery_NULLCNT`;
 
   Note : Remeber to use o ......

ÔÙ̸ Sql Á½ÐбäÒ»ÐРʵÀý

ÒªÇó£º °´ lct1£¬ lct2  ÅÅÐòºóµÄǰÁ½Ìõ¼Í¼£¬ÏÔʾΪÏÂÁнá¹û£º
item_cd1   item_cd2            lct
01             a         ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ