SQLÓïÑÔ»ù´¡ Êý¾Ý¶¨Òå
1¡¢¶¨Òå»ù±¾±í
SQLÓïÑÔʹÓö¯´ÊCREATE¶¨Òå»ù±¾±í£¬Æä¾ßÌåÓï·¨¸ñʽÈçÏ£º
CREATE TABLE <±íÃû>
(<ÁÐÃû><Êý¾ÝÀàÐÍ>[Áм¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ]...[£¬<ÁÐÃû><Êý¾ÝÀàÐÍ>[Áм¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ]][,<±í¼¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ>])£»
ÀýÈ磺½¨Á¢Ò»¸öѧÉú±íStudent£¬ËüÓÉѧºÅSno£¬ÐÕÃûSname£¬ÐÔ±ðSsex£¬ÄêÁäSage£¬ËùÔÚϵSdeptÕâ5¸öÊôÐÔ×é³É¡£ÆäÖÐѧºÅ²»ÄÜΪ¿Õ£¬ÖµÊÇΨһµÄ£¬²¢ÇÒÐÕÃûȡֵҲΨһ¡£SQLÃüÁîÈçÏ£º
CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage INT,
Sdept CHAR(15));
¶¨Òå±íµÄ¸÷¸öÊôÐÔʱÐèÒªÖ¸Ã÷ÆäÊý¾ÝÀàÐͼ°³¤¶È¡£
SMALLINT °ë×Ö³¤¶þ½øÖÆÕûÊý¡£
INTEGER»òINT È«×Ö³¤¶þ½øÖÆÕûÊý¡£
DECIMAL(p[,q])»òDEC(p[,q]) ѹËõÊ®½øÖÆÊý£¬¹²P룬ÆäÖÐСÊýλÓÐqλ¡£0<=q<=p<=15,q=0ʱ¿ÉÒÔÊ¡ÂÔ²»Ð´¡£
FLOAT Ë«×Ö³¤¸¡µãÊý¡£
CHARTER(n)»òCHAR(n) ³¤¶ÈΪnµÄ¶¨³¤×Ö·û´®¡£
VARCHAR(n) ×î´ó³¤¶ÈΪnµÄ±ä³¤×Ö·û´®¡£
GRAPHIC(n) ³¤¶ÈΪnµÄ¶¨³¤Í¼ÐÎ×Ö·û´®¡£
VARGRAPHIC(n) ×î´ó³¤¶ÈΪnµÄ±ä³¤Í¼ÐÎ×Ö·û´®¡£
DATE ÈÕÆÚÐÍ£¬¸ñʽΪYYYY-MM-DD¡£
TIME ʱ¼äÐÍ£¬¸ñʽΪHH.MM.SS¡£
TIMESTAMP ÈÕÆÚ¼Óʱ¼ä¡£
2¡¢Ð޸Ļù±¾±í
°üÀ¨Ôö¼ÓÐÂÁС¢É¾³ýÔ¼Êø¡¢ÐÞ¸ÄÊý¾ÝÀàÐÍ¡£Ð޸Ļù±¾±íµÄÃüÁî¸ñʽÈçÏ£º
ALTER TABLE <±íÃû>
[ADD<ÐÂÁÐÃû><Êý¾ÝÀàÐÍ>[ÍêÕûÐÔÔ¼Êø]] //Ôö¼ÓÐÂÁÐ
[DROP<ÍêÕûÐÔÔ¼ÊøÃû>] //ɾ³ýÔ¼Êø
[MODIFY<ÁÐÃû><Êý¾ÝÀàÐÍ>]£» //ÐÞ¸ÄÊý¾ÝÀàÐÍ
ÀýÈ磺ÏòStudent±íÔö¼Ó“Èëѧʱ¼ä”ÁÐScome£¬ÆäÊý¾ÝÀàÐÍΪÈÕÆÚÐÍ¡£SQLÃüÁîÈçÏ£º
ALTER TABLE ADD Scome Scome Date£»
3¡¢É¾³ý»ù±¾±í
DROP TABLE <±íÃû>
ÀýÈ磺Ҫɾ³ýÃûΪStudentµÄ±í£¬SQLÃüÁîÈçÏ£º
DROP TABLE Student£»
×¢Ò⣺»ù±¾±íÒ»µ©±»É¾³
Ïà¹ØÎĵµ£º
delete from
uservalid
where(lastlogin<'2007-1-1
0:0:00')
Çå³ýÔÚuservalid±íÖÐ×îºóµÇ½ʱ¼äÔÚ2007Äê1ÔÂ1ÈÕÁãʱÁã·Ö֮ǰµÄÊý¾Ý
²éѯijһ·¶Î§ÄÚµÄÊý¾ÝÔòselete * from uservalid
where(wealth in(500,1000))
ÔÚ²éѯ·ÖÎöÆ÷ÀïÃæselete from where
inÕâЩ¹Ø¼ü×Ö¶¼×Ô¶¯´óдÁË
......
¶¨Ò壺 ºÎΪ´¥·¢Æ÷£¿ÔÚSQL ServerÀïÃæÒ²¾ÍÊǶÔijһ¸ö±íµÄÒ»¶¨µÄ²Ù×÷£¬´¥·¢Ä³ÖÖÌõ¼þ£¬´Ó¶øÖ´ÐеÄÒ»¶Î³ÌÐò¡£´¥·¢Æ÷ÊÇÒ»¸öÌØÊâµÄ´æ´¢¹ý³Ì¡£
³£¼ûµÄ´¥·¢Æ÷ÓÐÈýÖÖ£º·Ö±ðÓ¦ÓÃÓÚInsert , Update , Delete ʼþ¡£
ÎÒΪʲôҪʹÓô¥·¢Æ÷£¿±ÈÈ磬ÕâôÁ½¸ö±í£º
& ......
ÔÚÕâÀï,ÎÒËùÖ¸µÄ°æ±¾ÊÇÖ¸µÄ2000,2005,2008ÕâÑùµÄ°æ±¾Çø±ð,¶ø²»ÊÇÆóÒµ°æ,¿ª·¢°æÕâÑùµÄ°æ±¾Çø±ð.´Ó¹Ù·½µÄ˵·¨À´¿´,SQL ServerÊÇÔÊÐíÊý¾Ý¿â´ÓµÍ°æ±¾Ïò¸ß°æ±¾»Ö¸´,µ«²»Ö§³Ö¸ß°æ±¾ÏòµÍ°æ±¾µÄ»Ö¸´,Èç¹ûÎÒÃÇÓø߰汾µÄÊý¾Ý¿âÔڵͰ汾µÄÊý¾Ý¿âÒýÇæÏ»ָ´,»á³öÏÖʲô´íÎóÃèÊöÄØ?
......
--²âÊÔÊý¾Ý
if OBJECT_ID('tb') is not null
drop table tb
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'ɽ¶«Ê¡'
UNION ALL SELECT '002','001','ÑĮ̀ÊÐ'
UNION ALL SELECT '004','002','ÕÐÔ¶ÊÐ'
UNION ALL SELECT '003','001','ÇൺÊÐ'
UNION ALL SELECT '00 ......