SQL數(shù)據(jù)庫字典

1、*Transact_SQL* -Óï ¾ä ¹¦ ÄÜ -Êý¾Ý²Ù×÷ SELECT -´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ INSERT -Ï

2、42;Êý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐÐ DELETE -´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐÐ UPDATE -¸üÐÂ

3、02;ý¾Ý¿â±íÖеÄÊý¾Ý -Êý¾Ý¶¨Òå CREATE TABLE -´´½¨Ò»¸öÊý¾Ý¿â±í DROP TABLE -´ÓÊý¾

4、21;¿âÖÐɾ³ý±í ALTER TABLE -ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹ CREATE VIEW -´´½¨Ò»¸öÊÓͼ DROP VIEW -´ÓÊý¾

5、;Ý¿âÖÐɾ³ýÊÓͼ CREATE INDEX -ΪÊý¾Ý¿â±í´´½¨Ò»¸öË÷Òý DROP INDEX -´ÓÊý¾Ý¿âÖÐ&

6、#201;¾³ýË÷Òý CREATE PROCEDURE -´´½¨Ò»¸ö´æ´¢¹ý³Ì DROP PROCEDURE -´ÓÊý¾Ý¿âÖÐɾ³ý´æ´¢¹ý&

7、#179;Ì CREATE TRIGGER -´´½¨Ò»¸ö´¥·¢Æ÷ DROP TRIGGER -´ÓÊý¾Ý¿âÖÐɾ³ý´¥·¢Æ÷ CREATE SCHEMA -ÏòÊý¾Ý&

8、#191;âÌí¼ÓÒ»¸öÐÂģʽ DROP SCHEMA -´ÓÊý¾Ý¿âÖÐɾ³ýÒ»¸öģʽ CREATE DOMAIN -´´½¨Ò»¸&

9、#246;Êý¾ÝÖµÓò ALTER DOMAIN -¸Ä±äÓò¶¨Òå DROP DOMAIN -´ÓÊý¾Ý¿âÖÐɾ³ýÒ»¸öÓò -Êý¾Ý¿Ø

10、ÖÆ GRANT -ÊÚÓèÓû§·ÃÎÊȨÏÞ DENY -¾Ü¾øÓû§·ÃÎÊ REVOKE -½â³ýÓû§·ÃÎÊȨÏÞ

11、 -ÊÂÎñ¿ØÖÆ COMMIT -½áÊøµ±Ç°ÊÂÎñ ROLLBACK -ÖÐÖ¹µ±Ç°ÊÂÎñ SET TRANSACTION -¶¨Ò嵱ǰÊÂÎ&#

12、241;Êý¾Ý·ÃÎÊÌØÕ÷ -³ÌÐò»¯SQL DECLARE -Ϊ²éѯÉ趨Óαê EXPLAN -Ϊ²éѯÃèÊöÊý¾&#

13、221;·ÃÎʼƻ® OPEN -¼ìË÷²éѯ½á¹û´ò¿ªÒ»¸öÓαê FETCH -¼ìË÷Ò»Ðвéѯ½á¹

14、;û CLOSE -¹Ø±ÕÓαê PREPARE -Ϊ¶¯Ì¬Ö´ÐÐ×¼±¸SQL Óï¾ä EXECUTE -¶¯Ì¬µØÖ´ÐÐSQL Óï¾ä DESCRIBE -Ã

15、èÊö×¼±¸ºÃµÄ²éѯ -¾Ö²¿±äÁ¿ declare id char(10) -set id = '10010001' select id = '10010001' -È«¾Ö±äÁ¿ -±ØÐë

16、0;Ô¿ªÍ· -IF ELSE declare x int y int z int select x = 1 y = 2 z=3 if x > y print 'x > y' -´òÓ¡×Ö·û´®'x > y' else if y > z print 'y > z' else print 'z > y' -CASE use pangu upda

17、te employee set e_wage = case when job_level = ¡¯1¡¯ then e_wage*1.08 when job_level = ¡¯2¡¯ then e_wage*1.07 when job_level = ¡¯3¡¯ then e_wage*1.06 else e_wage*1.05 end -WHILE CONTINUE BREAK declare x int y int c int select x = 1 y=1 whil

18、e x < 3 begin print x -´òÓ¡±äÁ¿x µÄÖµ while y < 3 begin select c = 100*x + y print c -´òÓ¡±äÁ¿c µÄÖµ select y = y + 1 end select x = x + 1 select y = 1 end -WAITFOR -À&#

19、253; µÈ´ý1 Сʱ2 ·ÖÁã3 Ãëºó²ÅÖ´ÐÐSELECT Óï¾ä waitfor delay ¡¯01:02:03¡¯ select * from employee -Àý µÈµ½ÍíÉ

20、;Ï11 µãÁã8 ·Öºó²ÅÖ´ÐÐSELECT Óï¾ä waitfor time ¡¯23:08:00¡¯ select * from employee *SELECT* select *(ÁÐÃû) from table_name(±íÃû) where column_name

21、 operator value ex:(ËÞÖ÷) select * from stock_information where stockid = str(nid) stockname = 'str_name' stockname like '% find this %' stockname like 'a-zA-Z%' - (Ö¸¶¨ÖµµÄ·¶Î§) stockname like &#

22、39;F-M%' - (ÅųýÖ¸¶¨·¶Î§) - Ö»ÄÜÔÚʹÓÃlike¹Ø¼ü×ÖµÄwhere×Ó¾äÖÐʹÓÃͨÅä·&

23、#251;) or stockpath = 'stock_path' or stocknumber < 1000 and stockindex = 24 not stocksex = 'man' stocknumber between 20 and 100 stocknumber in(10,20,30) order by stockid desc(asc) - ÅÅÐò£¬desc-½µÐò£¬asc-ÉýÐ

24、;ò order by 1,2 - byÁкŠstockname = (select stockname from stock_information where stockid = 4) - ×Ó²éѯ - ³ý·ÇÄÜÈ·±£ÄÚ²ãselectÖ»·µ»ØÒ»

25、;¸öÐеÄÖµ£¬ - ·ñÔòÓ¦ÔÚÍâ²ãwhere×Ó¾äÖÐÓÃÒ»¸öinÏÞ¶¨·û select distinct column_name form table_name - di

26、stinctÖ¸¶¨¼ìË÷¶ÀÓеÄÁÐÖµ£¬²»Öظ´ select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name select stockname , "stocknumber" = count(*)

27、 from table_name group by stockname - group by ½«±í°´ÐзÖ×é,Ö¸¶¨ÁÐÖÐÓÐÏàͬµÄÖµ having count(*) = 2 - havingÑ¡¶¨Ö¸¶&

28、#168;µÄ×é select * from table1, table2 where table1.id *= table2.id - ×óÍⲿÁ¬½Ó£¬table1ÖÐÓеĶøtable2ÖÐûÓеÃÒÔnull±í&#

29、202;¾ table1.id =* table2.id - ÓÒÍⲿÁ¬½Ó select stockname from table1 union all - unionºÏ²¢²éѯ½á¹û¼¯£¬all-±£ÁôÖظ´Ð

30、08; select stockname from table2 *insert* insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx") value (select Stockname , Stocknumber from Stock_table2)-valueΪselectÓï¾ä *update* update table_name set Stockname = "xxx" wher

31、e Stockid = 3 Stockname = default Stockname = null Stocknumber = Stockname + 4 *delete* delete from table_name where Stockid = 3 truncate table_name - ɾ³ý±íÖÐËùÓÐÐУ¬ÈÔ±£³Ö±íµ

32、;ÄÍêÕûÐÔ drop table table_name - Íêȫɾ³ý±í *alter table* - ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹ alter table database.owner.table_name add column_name char

33、(2) null . sp_help table_name - ÏÔʾ±íÒÑÓÐÌØÕ÷ create table table_name (name char(20), age smallint, lname varchar(30) insert into table_name select . - ʵÏÖɾ³ýÁеķ

34、;½·¨£¨´´½¨ÐÂ±í£© alter table table_name drop constraint Stockname_default - ɾ³ýStocknameµÄdefaultÔ¼Êø *function(/*³£Óú¯Êý*/)* -Í

35、³¼Æº¯Êý- AVG -Çóƽ¾ùÖµ COUNT -ͳ¼ÆÊýÄ¿ MAX -Çó×î´óÖµ MIN -Çó×îСֵ SUM -ÇóºÍ -AVG use

36、 pangu select avg(e_wage) as dept_avgWage from employee group by dept_id -MAX -Çó¹¤×Ê×î¸ßµÄÔ±¹¤ÐÕÃû use pangu select e_name from employee where e_wage = (select max(e_wage) from employee) -STDEV() -ST

37、DEV()º¯Êý·µ»Ø±í´ïʽÖÐËùÓÐÊý¾ÝµÄ±ê×¼²î -STDEVP() -STDEVP()º¯Êý·µ»Ø×ÜÌå±ê

38、;×¼²î -VAR() -VAR()º¯Êý·µ»Ø±í´ïʽÖÐËùÓÐÖµµÄͳ¼Æ±äÒìÊý -VARP() -VARP()º¯Êý·µ»

39、;Ø×ÜÌå±äÒìÊý -ËãÊõº¯Êý- /*Èý½Çº¯Êý*/ SIN(float_expression) -·µ»ØÒÔ»¡¶È±íʾµÄ½

40、99;µÄÕýÏÒ COS(float_expression) -·µ»ØÒÔ»¡¶È±íʾµÄ½ÇµÄÓàÏÒ TAN(float_expression) -·µ»ØÒÔ»¡¶È±

41、37;ʾµÄ½ÇµÄÕýÇÐ COT(float_expression) -·µ»ØÒÔ»¡¶È±íʾµÄ½ÇµÄÓàÇÐ /*·´Èý½Çº¯Ê

42、ý*/ ASIN(float_expression) -·µ»ØÕýÏÒÊÇFLOAT ÖµµÄÒÔ»¡¶È±íʾµÄ½Ç ACOS(float_expression) -·µ»ØÓàÏÒÊÇFLOAT &#

43、214;µµÄÒÔ»¡¶È±íʾµÄ½Ç ATAN(float_expression) -·µ»ØÕýÇÐÊÇFLOAT ÖµµÄÒÔ»¡¶È±íʾµÄ½&

44、#199; ATAN2(float_expression1,float_expression2) -·µ»ØÕýÇÐÊÇfloat_expression1 /float_expres-sion2µÄÒÔ»¡¶È±íʾµÄ½Ç DEGREES(numeric_expression) -°Ñ»¡

45、82;Èת»»Îª½Ç¶È·µ»ØÓë±í´ïʽÏàͬµÄÊý¾ÝÀàÐÍ¿ÉΪ -INTEGER/MONEY/REAL/FLOAT ÀàÐÍ

46、 RADIANS(numeric_expression) -°Ñ½Ç¶Èת»»Îª»¡¶È·µ»ØÓë±í´ïʽÏàͬµÄÊý¾ÝÀàÐÍ¿ÉÎ

47、ª -INTEGER/MONEY/REAL/FLOAT ÀàÐÍ EXP(float_expression) -·µ»Ø±í´ïʽµÄÖ¸ÊýÖµ LOG(float_expression) -·µ»Ø±í´ïʽµÄ×Ô

48、00;»¶ÔÊýÖµ LOG10(float_expression)-·µ»Ø±í´ïʽµÄÒÔ10 Ϊµ×µÄ¶ÔÊýÖµ SQRT(float_expression) -·µ»Ø±í´

49、9;ʽµÄƽ·½¸ù /*È¡½üËÆÖµº¯Êý*/ CEILING(numeric_expression) -·µ»Ø>=±í´ïʽµÄ×îСÕûÊý·

50、µ»ØµÄÊý¾ÝÀàÐÍÓë±í´ïʽÏàͬ¿ÉΪ -INTEGER/MONEY/REAL/FLOAT ÀàÐÍ FLOOR(numeric_expression) -·µ»Ø<=±í´

51、ïʽµÄ×îСÕûÊý·µ»ØµÄÊý¾ÝÀàÐÍÓë±í´ïʽÏàͬ¿ÉΪ -INTEGER/MONEY/REAL/FLOAT Àà

52、08;Í ROUND(numeric_expression) -·µ»ØÒÔinteger_expression Ϊ¾«¶ÈµÄËÄÉáÎåÈëÖµ·µ»ØµÄÊý¾Ý -ÀàÐÍÓë&#

53、177;í´ïʽÏàͬ¿ÉΪINTEGER/MONEY/REAL/FLOAT ÀàÐÍ ABS(numeric_expression) -·µ»Ø±í´ïʽµÄ¾ø¶ÔÖµ·µ»ØµÄ

54、;Êý¾ÝÀàÐÍÓë±í´ïʽÏàͬ¿ÉΪ -INTEGER/MONEY/REAL/FLOAT ÀàÐÍ SIGN(numeric_expression) -²âÊÔ²ÎÊýµÄÕý¸

55、;ººÅ·µ»Ø0 ÁãÖµ1 ÕýÊý»ò-1 ¸ºÊý·µ»ØµÄÊý¾ÝÀàÐÍ -Óë±í´ïʽÏàͬ¿&#

56、201;ΪINTEGER/MONEY/REAL/FLOAT ÀàÐÍ PI() -·µ»ØֵΪ¦Ð ¼´3.1415926535897936 RAND(integer_expression) -ÓÃÈÎÑ¡µÄinteger_expression×öÖÖ×ÓÖ&#

57、181;µÃ³ö0-1 ¼äµÄËæ»ú¸¡µãÊý -×Ö·û´®º¯Êý- ASCII() -º¯Êý·µ»Ø×Ö·û±í´ïʽ

58、15;î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ CHAR() -º¯ÊýÓÃÓÚ½«ASCII Âëת»»Îª×Ö·û -Èç¹ûûÓÐ

59、2;äÈë0 255 Ö®¼äµÄASCII ÂëÖµCHAR º¯Êý»á·µ»ØÒ»¸öNULL Öµ LOWER() -º¯Êý°Ñ×Ö·û´®È«²&#

60、191;ת»»ÎªÐ¡Ð´ UPPER() -º¯Êý°Ñ×Ö·û´®È«²¿×ª»»Îª´óд STR() -º¯Êý°ÑÊýÖµ&#

61、208;ÍÊý¾Ýת»»Îª×Ö·ûÐÍÊý¾Ý LTRIM() -º¯Êý°Ñ×Ö·û´®Í·²¿µÄ¿Õ¸ñÈ¥µô RTR

62、IM() -º¯Êý°Ñ×Ö·û´®Î²²¿µÄ¿Õ¸ñÈ¥µô LEFT(),RIGHT(),SUBSTRING() -º¯Êý·µ»Ø²¿·Ö×Ö·û´®

63、CHARINDEX(),PATINDEX() -º¯Êý·µ»Ø×Ö·û´®ÖÐij¸öÖ¸¶¨µÄ×Ó´®³öÏֵĿªÊ¼Î»Öà SOUNDEX() -

64、º¯Êý·µ»ØÒ»¸öËÄλ×Ö·ûÂë -SOUNDEXº¯Êý¿ÉÓÃÀ´²éÕÒÉùÒôÏàËƵÄ×Ö

65、83;û´®µ«SOUNDEXº¯Êý¶ÔÊý×ֺͺº×Ö¾ùÖ»·µ»Ø0 Öµ DIFFERENCE() -º¯Êý·µ»ØÓÉSOUNDEX º¯Ê&

66、#253;·µ»ØµÄÁ½¸ö×Ö·û±í´ïʽµÄÖµµÄ²îÒì -0 Á½¸öSOUNDEX º¯Êý·µ»ØÖµµÄµ&

67、#218;Ò»¸ö×Ö·û²»Í¬ -1 Á½¸öSOUNDEX º¯Êý·µ»ØÖµµÄµÚÒ»¸ö×Ö·ûÏàͬ -2 Á½¸öSOU

68、NDEX º¯Êý·µ»ØÖµµÄµÚÒ»¶þ¸ö×Ö·ûÏàͬ -3 Á½¸öSOUNDEX º¯Êý·µ»ØÖµµÄµÚÒ&

69、#187;¶þÈý¸ö×Ö·ûÏàͬ -4 Á½¸öSOUNDEX º¯Êý·µ»ØÖµÍêÈ«Ïàͬ QUOTENAME() -º¯Êý·µ»Ø±

70、;»Ìض¨×Ö·ûÀ¨ÆðÀ´µÄ×Ö·û´® /*select quotename('abc', '') quotename('abc') ÔËÐнá¹ûÈçÏ - abc abc*/ REPL

71、ICATE() -º¯Êý·µ»ØÒ»¸öÖظ´character_expression Ö¸¶¨´ÎÊýµÄ×Ö·û´® /*select replicate('abc', 3) replicate( 'abc', -2) Ô

72、3;Ðнá¹ûÈçÏ - - abcabcabc NULL*/ REVERSE() -º¯Êý½«Ö¸¶¨µÄ×Ö·û´®µÄ×Ö·ûÅÅÁÐ˳Ðòµß&

73、#181;¹ REPLACE() -º¯Êý·µ»Ø±»Ìæ»»ÁËÖ¸¶¨×Ó´®µÄ×Ö·û´® /*select replace('abc123g', '123', 'def') ÔËÐ

74、нá¹ûÈçÏ - - abcdefg*/ SPACE() -º¯Êý·µ»ØÒ»¸öÓÐÖ¸¶¨³¤¶ÈµÄ¿Õ°××Ö·û´® STUFF() -º&#

75、175;ÊýÓÃÁíÒ»×Ó´®Ìæ»»×Ö·û´®Ö¸¶¨Î»Öó¤¶ÈµÄ×Ó´® -Êý¾ÝÀàÐÍ×

76、ª»»º¯Êý- CAST() º¯ÊýÓï·¨ÈçÏ CAST() (<expression> AS <data_ type> length ) CONVERT() º¯ÊýÓï·¨ÈçÏ CONVERT() (<data_ type> lengt

77、h , <expression> , style) select cast(100+99 as char) convert(varchar(12), getdate() ÔËÐнá¹ûÈçÏ - - 199 Jan 15 2000 -ÈÕÆÚº¯Êý- DAY() -º¯Êý·µ»Ødate_expre

78、ssion ÖеÄÈÕÆÚÖµ MONTH() -º¯Êý·µ»Ødate_expression ÖеÄÔ·ÝÖµ YEAR() -º¯Êý·µ»Ødate_expression ÖеÄ

79、96;ê·ÝÖµ DATEADD(<datepart> ,<number> ,<date>) -º¯Êý·µ»ØÖ¸¶¨ÈÕÆÚdate ¼ÓÉÏÖ¸¶¨µÄ¶îÍâÈÕÆÚ

80、¼ä¸ônumber ²úÉúµÄÐÂÈÕÆÚ DATEDIFF(<datepart> ,<number> ,<date>) -º¯Êý·µ»ØÁ½¸öÖ¸¶¨ÈÕÆÚÔÚdatepa

81、rt ·½ÃæµÄ²»Í¬Ö®´¦ DATENAME(<datepart> , <date>) -º¯ÊýÒÔ×Ö·û´®µÄÐÎʽ·µ»ØÈÕÆÚµÄ&

82、#214;¸¶¨²¿·Ö DATEPART(<datepart> , <date>) -º¯ÊýÒÔÕûÊýÖµµÄÐÎʽ·µ»ØÈÕÆÚµÄÖ¸¶¨²¿·

83、;Ö GETDATE() -º¯ÊýÒÔDATETIME µÄȱʡ¸ñʽ·µ»Øϵͳµ±Ç°µÄÈÕÆÚºÍʱ¼ä -ϵͳº

84、¯Êý- APP_NAME() -º¯Êý·µ»Øµ±Ç°Ö´ÐеÄÓ¦ÓóÌÐòµÄÃû³Æ COALESCE() -º¯Êý·µ»ØÖÚ

85、82;à±í´ïʽÖеÚÒ»¸ö·ÇNULL ±í´ïʽµÄÖµ COL_LENGTH(<'table_name'>, <'column_name'>) -º¯Êý·µ»Ø±íÖÐÖ&