sql總結(jié)

123456789101112131415161718192021222324252627282930313233343536

altertable表名[add子句]

[drop子句]

創(chuàng)建表：

createtable表名（

列名數(shù)據(jù)類型[default缺省值][notnull][，列名數(shù)據(jù)類型[default缺省值][notnull]]

......

[，primarykey（列名[，列名]…）]主鍵（多個(gè)值作為聯(lián)合主鍵）[，foreignkey（列名[，列名]…）外鍵references表名（列名[，列名]…）]外間關(guān)聯(lián)表[，unique（列名[，列名]…）]不允許重復(fù)列[，check（條件）]）約束條件

如：

createdomainperson_namechar(20)（定義域）createtablePROF(PNOchar(10),--職工編號(hào)

PNAMEperson_namenotnull,--職工姓名SALint,--工資AGEint,--年齡DNOchar(10),--主任職工編號(hào)primarykey(PNO),--主鍵

foreignkey(DNO)referencesDEPT(DNO),外鍵關(guān)聯(lián)

簡(jiǎn)單定義表：

createtableCIA(NAMEvarchar(50)primarykey,)

REGIONvarchar(50)notnulldefault‘regionnotformalizedyet",AREAintnull,POPULATIONlongnull,GDPlongnotnull

修改表：

增加新列刪除列

[modify子句]修改列定義Add列不為空是需加默認(rèn)值（缺省值）

修改列定義時(shí)先建一修改后的新列，復(fù)制要修改的原列，刪除修改的原列

3738

索引的定義

create[unique|distinct][cluster]index索引名3940414243444546474849505152

on表名(列名[asc|desc][,列名[asc|desc]]…)

unique（distinct）：唯一性索引，不允許表中不同的行在索引列上取相同值。若已有相同值存在，則系統(tǒng)給出相關(guān)信息，不建此索引。系統(tǒng)并拒絕違背唯一性的插入、更新。cluster：聚簇索引，表中元組按索引項(xiàng)的值排序并物理地聚集在一起。一個(gè)基本表上只能建一個(gè)聚集索引。

asc|desc：索引表中索引值的排序次序，缺省為asc。

示例：createclusterindexg-indexonCIA（REGION）

刪除索引：

dropindex索引名

sql查詢

1、sum總和avg平均distinct/unique去重asc升序|desc降序

2、selectnamecasewhenage>60then1whenage737475767778798081whereCNO=003）

聚集函數(shù)

平均值：avg最小值：min最大值：max

總和：sum記數(shù)：count

聚集函數(shù)后跟groupby…h(huán)aving…

(單個(gè)字段查詢不需要：SELECTMAX(SALARY)FROMEMPLOYEES)8283

8485

擴(kuò)展閱讀：SQL總結(jié)

3.6SQLAlias（別名）3.7SQLJOIN

用于根據(jù)兩個(gè)或多個(gè)表中的列之間的關(guān)系，從這些表中查詢數(shù)據(jù)。

語(yǔ)法

INNERJOIN：如果表中有至少一個(gè)匹配，則返回行

JOIN:如果表中有至少一個(gè)匹配，則返回行（與INNERJOIN相同）LEFTJOIN:即使右表中沒(méi)有匹配，也從左表返回所有的行RIGHTJOIN:即使左表中沒(méi)有匹配，也從右表返回所有的行FULLJOIN:只要其中一個(gè)表中存在匹配，就返回行

實(shí)例

"Persons"表：Id_P123"Orders"表：Id_O12345OrderNo7789544678224562456234764Id_P331165LastNameAdamsBushCarterFirstNameJohnGeorgeThomasAddressOxfordStreetFifthAvenueChanganStreetCityLondonNewYorkBeijing

（JOIN）實(shí)例

SELECTPersons.LastName,Persons.FirstName,Orders.OrderNo":[{"c":{"ix":0,"iy":0,"iw":663,"ih":37},"p":{"h":37,"w":663,"x":115,"y":108,"z":1},"ps":{"_drop":1},"s":null,"t":"pic"},{"c":{"ix":0,"iy":42,"iw":893,"ih":3},"p":{"h":3,"opacity":1,"w":892.979,"x":0,"y":107,"z":2},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_2.png"},"t":"pic"},{"c":{"ix":0,"iy":50,"iw":2,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":1.079,"x":119.916,"x0":119.916,"x1":119.916,"x2":120.996,"x3":120.996,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":3},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_3.png"},"t":"pic"},{"c":{"ix":0,"iy":57,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":120.996,"x0":120.996,"x1":120.996,"x2":123.156,"x3":123.156,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":4},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_4.png"},"t":"pic"},{"c":{"ix":0,"iy":64,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":123.156,"x0":123.156,"x1":123.156,"x2":125.316,"x3":125.316,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":5},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_5.png"},"t":"pic"},{"c":{"ix":0,"iy":71,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":125.316,"x0":125.316,"x1":125.316,"x2":127.476,"x3":127.476,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":6},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_6.png"},"t":"pic"},{"c":{"ix":0,"iy":78,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.16,"x":127.476,"x0":127.476,"x1":127.476,"x2":129.636,"x3":129.636,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":7},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_7.png"},"t":"pic"},{"c":{"ix":0,"iy":85,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":129.636,"x0":129.636,"x1":129.636,"x2":131.796,"x3":131.796,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":8},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_8.png"},"t":"pic"},{"c":{"ix":0,"iy":92,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":131.796,"x0":131.796,"x1":131.796,"x2":133.956,"x3":133.956,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":9},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_9.png"},"t":"pic"},{"c":{"ix":0,"iy":99,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":133.956,"x0":133.956,"x1":133.956,"x2":136.116,"x3":136.116,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":10},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_10.png"},"t":"pic"},{"c":{"ix":0,"iy":106,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":136.116,"x0":136.116,"x1":136.116,"x2":138.276,"x3":138.276,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":11},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_11.png"},"t":"pic"},{"c":{"ix":0,"iy":113,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":138.276,"x0":138.276,"x1":138.276,"x2":140.436,"x3":140.436,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":12},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_12.png"},"t":"pic"},{"c":{"ix":0,"iy":120,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":140.435,"x0":140.435,"x1":140.435,"x2":142.595,"x3":142.595,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":13},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_13.png"},"t":"pic"},{"c":{"ix":0,"iy":127,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":142.596,"x0":142.596,"x1":142.596,"x2":144.756,"x3":144.756,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":14},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_14.png"},"t":"pic"},{"c":{"ix":0,"iy":134,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":144.756,"x0":144.756,"x1":144.756,"x2":146.916,"x3":146.916,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":15},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_15.png"},"t":"pic"},{"c":{"ix":0,"iy":141,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":146.916,"x0":146.916,"x1":146.916,"x2":149.076,"x3":149.076,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":16},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_16.png"},"t":"pic"},{"c":{"ix":0,"iy":148,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":149.076,"x0":149.076,"x1":149.076,"x2":151.236,"x3":151.236,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":17},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_17.png"},"t":"pic"},{"c":{"ix":0,"iy":155,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":151.23,"x0":151.23,"x1":151.23,"x2":153.39,"x3":153.39,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":18},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_18.png"},"t":"pic"},{"c":{"ix":0,"iy":162,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":153.39,"x0":153.39,"x1":153.39,"x2":155.55,"x3":155.55,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":19},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_19.png"},"t":"pic"},{"c":{"ix":0,"iy":169,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":155.55,"x0":155.55,"x1":155.55,"x2":157.71,"x3":157.71,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":20},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_20.png"},"t":"pic"},{"c":{"ix":0,"iy":176,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":157.71,"x0":157.71,"x1":157.71,"x2":159.87,"x3":159.87,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":21},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_21.png"},"t":"pic"},{"c":{"ix":0,"iy":183,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":159.87,"x0":159.87,"x1":159.87,"x2":162.03,"x3":162.03,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":22},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_22.png"},"t":"pic"},{"c":{"ix":0,"iy":190,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":162.03,"x0":162.03,"x1":162.03,"x2":164.19,"x3":164.19,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":23},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_23.png"},"t":"pic"},{"c":{"ix":0,"iy":197,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":164.19,"x0":164.19,"x1":164.19,"x2":166.35,"x3":166.35,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":24},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_12_24.png"},"t":"pic"},{"c":{"ix":0,"iy":204,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":166.35,"x0":166.35,"x1":166.35,"x2":168.51,"x3":168.51,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":25},"ps":null,"s":{"pic_file":"/home/iknow/con":[{"c":{"ix":0,"iy":0,"iw":663,"ih":37},"p":{"h":37,"w":663,"x":115,"y":108,"z":1},"ps":{"_drop":1},"s":null,"t":"pic"},{"c":{"ix":0,"iy":42,"iw":893,"ih":3},"p":{"h":3,"opacity":1,"w":892.979,"x":0,"y":107,"z":2},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_2.png"},"t":"pic"},{"c":{"ix":0,"iy":50,"iw":2,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":1.079,"x":119.916,"x0":119.916,"x1":119.916,"x2":120.996,"x3":120.996,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":3},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_3.png"},"t":"pic"},{"c":{"ix":0,"iy":57,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":120.996,"x0":120.996,"x1":120.996,"x2":123.156,"x3":123.156,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":4},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_4.png"},"t":"pic"},{"c":{"ix":0,"iy":64,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":123.156,"x0":123.156,"x1":123.156,"x2":125.316,"x3":125.316,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":5},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_5.png"},"t":"pic"},{"c":{"ix":0,"iy":71,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":125.316,"x0":125.316,"x1":125.316,"x2":127.476,"x3":127.476,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":6},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_6.png"},"t":"pic"},{"c":{"ix":0,"iy":78,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.16,"x":127.476,"x0":127.476,"x1":127.476,"x2":129.636,"x3":129.636,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":7},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_7.png"},"t":"pic"},{"c":{"ix":0,"iy":85,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":129.636,"x0":129.636,"x1":129.636,"x2":131.796,"x3":131.796,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":8},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_8.png"},"t":"pic"},{"c":{"ix":0,"iy":92,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":131.796,"x0":131.796,"x1":131.796,"x2":133.956,"x3":133.956,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":9},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_9.png"},"t":"pic"},{"c":{"ix":0,"iy":99,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":133.956,"x0":133.956,"x1":133.956,"x2":136.116,"x3":136.116,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":10},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_10.png"},"t":"pic"},{"c":{"ix":0,"iy":106,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":136.116,"x0":136.116,"x1":136.116,"x2":138.276,"x3":138.276,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":11},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_11.png"},"t":"pic"},{"c":{"ix":0,"iy":113,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":138.276,"x0":138.276,"x1":138.276,"x2":140.436,"x3":140.436,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":12},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_12.png"},"t":"pic"},{"c":{"ix":0,"iy":120,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":140.435,"x0":140.435,"x1":140.435,"x2":142.595,"x3":142.595,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":13},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_13.png"},"t":"pic"},{"c":{"ix":0,"iy":127,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":142.596,"x0":142.596,"x1":142.596,"x2":144.756,"x3":144.756,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":14},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_14.png"},"t":"pic"},{"c":{"ix":0,"iy":134,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":144.756,"x0":144.756,"x1":144.756,"x2":146.916,"x3":146.916,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":15},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_15.png"},"t":"pic"},{"c":{"ix":0,"iy":141,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":146.916,"x0":146.916,"x1":146.916,"x2":149.076,"x3":149.076,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":16},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_16.png"},"t":"pic"},{"c":{"ix":0,"iy":148,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":149.076,"x0":149.076,"x1":149.076,"x2":151.236,"x3":151.236,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":17},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_17.png"},"t":"pic"},{"c":{"ix":0,"iy":155,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":151.23,"x0":151.23,"x1":151.23,"x2":153.39,"x3":153.39,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":18},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_18.png"},"t":"pic"},{"c":{"ix":0,"iy":162,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":153.39,"x0":153.39,"x1":153.39,"x2":155.55,"x3":155.55,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":19},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_19.png"},"t":"pic"},{"c":{"ix":0,"iy":169,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":155.55,"x0":155.55,"x1":155.55,"x2":157.71,"x3":157.71,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":20},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_20.png"},"t":"pic"},{"c":{"ix":0,"iy":176,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":157.71,"x0":157.71,"x1":157.71,"x2":159.87,"x3":159.87,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":21},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_21.png"},"t":"pic"},{"c":{"ix":0,"iy":183,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":159.87,"x0":159.87,"x1":159.87,"x2":162.03,"x3":162.03,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":22},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_22.png"},"t":"pic"},{"c":{"ix":0,"iy":190,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":162.03,"x0":162.03,"x1":162.03,"x2":164.19,"x3":164.19,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":23},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_23.png"},"t":"pic"},{"c":{"ix":0,"iy":197,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":164.19,"x0":164.19,"x1":164.19,"x2":166.35,"x3":166.35,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":24},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_24.png"},"t":"pic"},{"c":{"ix":0,"iy":204,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":166.35,"x0":166.35,"x1":166.35,"x2":168.51,"x3":168.51,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":25},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_25.png"},"t":"pic"},{"c":{"ix":0,"iy":211,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":168.51,"x0":168.51,"x1":168.51,"x2":170.67,"x3":170.67,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":26},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_26.png"},"t":"pic"},{"c":{"ix":0,"iy":218,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":170.67,"x0":170.67,"x1":170.67,"x2":172.83,"x3":172.83,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":27},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_27.png"},"t":"pic"},{"c":{"ix":0,"iy":225,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":172.829,"x0":172.829,"x1":172.829,"x2":174.989,"x3":174.989,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":28},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_28.png"},"t":"pic"},{"c":{"ix":0,"iy":232,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":174.99,"x0":174.99,"x1":174.99,"x2":177.15,"x3":177.15,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":29},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_29.png"},"t":"pic"},{"c":{"ix":0,"iy":239,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":177.149,"x0":177.149,"x1":177.149,"x2":179.309,"x3":179.309,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":30},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_30.png"},"t":"pic"},{"c":{"ix":0,"iy":246,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":179.31,"x0":179.31,"x1":179.31,"x2":181.47,"x3":181.47,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":31},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//95671653//95671653_17_31.png"},"t":"pic"},{"c":{"ix":0,"iy":253,"iw":4,"ih":2},"p":{"h":2,"opacity":1,"rotate":0,"w":2.159,"x":181.47,"x0":181.47,"x1":181.47,"x2":183.63,"x3":183.63,"y":108.179,"y0":109.259,"y1":108.179,"y2":108.179,"y3":109.259,"z":3Yes/No邏輯字段，可以顯示為Yes/No、True/False或On/Off。在代碼中，使用常量True和False（等價(jià)于1和0）注釋：Yes/No字段中不允許Null值1比特OleObjectHyperlinkLookupWizard可以存儲(chǔ)圖片、音頻、視頻或其他BLOBs(BinaryLargeOBjects)包含指向其他文件的鏈接，包括網(wǎng)頁(yè)。允許你創(chuàng)建一個(gè)可從下列列表中進(jìn)行選擇的選項(xiàng)列表。最多1GB4字節(jié)MySQL數(shù)據(jù)類型

在MySQL中，有三種主要的類型：文本、數(shù)字和日期/時(shí)間類型。Text類型：數(shù)據(jù)類型CHAR(size)描述保存固定長(zhǎng)度的字符串（可包含字母、數(shù)字以及特殊字符）。在括號(hào)中指定字符串的長(zhǎng)度。最多255個(gè)字符。VARCHAR(size)保存可變長(zhǎng)度的字符串（可包含字母、數(shù)字以及特殊字符）。在括號(hào)中指定字符串的最大長(zhǎng)度。最多255個(gè)字符。注釋：如果值的長(zhǎng)度大于255，則被轉(zhuǎn)換為TEXT類型。TINYTEXTTEXTBLOBMEDIUMTEXTMEDIUMBLOBLONGTEXTLONGBLOBENUM(x,y,z,etc.)存放最大長(zhǎng)度為255個(gè)字符的字符串。存放最大長(zhǎng)度為65,535個(gè)字符的字符串。用于BLOBs(BinaryLargeOBjects)。存放最多65,535字節(jié)的數(shù)據(jù)。存放最大長(zhǎng)度為16,777,215個(gè)字符的字符串。用于BLOBs(BinaryLargeOBjects)。存放最多16,777,215字節(jié)的數(shù)據(jù)。存放最大長(zhǎng)度為4,294,967,295個(gè)字符的字符串。用于BLOBs(BinaryLargeOBjects)。存放最多4,294,967,295字節(jié)的數(shù)據(jù)。允許你輸入可能值的列表�？梢栽贓NUM列表中列出最大65535個(gè)值。如果列表中不存在插入的值，則插入空值。注釋：這些值是按照你輸入的順序存儲(chǔ)的�？梢园凑沾烁袷捷斎肟赡艿闹担篍NUM("X","Y","Z")SET與ENUM類似，SET最多只能包含64個(gè)列表項(xiàng)，不過(guò)SET可存儲(chǔ)一個(gè)以上的值。Number類型：數(shù)據(jù)類型TINYINT(size)SMALLINT(size)MEDIUMINT(size)INT(size)描述-128到127常規(guī)。0到255無(wú)符號(hào)*。在括號(hào)中規(guī)定最大位數(shù)。-32768到32767常規(guī)。0到65535無(wú)符號(hào)*。在括號(hào)中規(guī)定最大位數(shù)。-8388608到8388607普通。0to16777215無(wú)符號(hào)*。在括號(hào)中規(guī)定最大位數(shù)。-2147483648到2147483647常規(guī)。0到4294967295無(wú)符號(hào)*。在括號(hào)中規(guī)定最大位數(shù)。BIGINT(size)-9223372036854775808到9223372036854775807常規(guī)。0到18446744073709551615無(wú)符號(hào)*。在括號(hào)中規(guī)定最大位數(shù)。FLOAT(size,d)帶有浮動(dòng)小數(shù)點(diǎn)的小數(shù)字。在括號(hào)中規(guī)定最大位數(shù)。在d參數(shù)中規(guī)定小數(shù)點(diǎn)右側(cè)的最大位數(shù)。DOUBLE(size,d)帶有浮動(dòng)小數(shù)點(diǎn)的大數(shù)字。在括號(hào)中規(guī)定最大位數(shù)。在d參數(shù)中規(guī)定小數(shù)點(diǎn)右側(cè)的最大位數(shù)。DECIMAL(size,d)作為字符串存儲(chǔ)的DOUBLE類型，允許固定的小數(shù)點(diǎn)。*這些整數(shù)類型擁有額外的選項(xiàng)UNSIGNED。通常，整數(shù)可以是負(fù)數(shù)或正數(shù)。如果添加UNSIGNED屬性，那么范圍將從0開(kāi)始，而不是某個(gè)負(fù)數(shù)。Date類型：數(shù)據(jù)類型DATE()描述日期。格式：YYYY-MM-DD注釋：支持的范圍是從"1000-01-01"到"9999-12-31"DATETIME()*日期和時(shí)間的組合。格式：YYYY-MM-DDHH:MM:SS注釋：支持的范圍是從"1000-01-0100:00:00"到"9999-12-3123:59:59"TIMESTAMP()*時(shí)間戳。TIMESTAMP值使用Unix紀(jì)元("1970-01-0100:00:00"UTC)至今的描述來(lái)存儲(chǔ)。格式：YYYY-MM-DDHH:MM:SS注釋：支持的范圍是從"1970-01-0100:00:01"UTC到"2038-01-0903:14:07"UTCTIME()YEAR()時(shí)間。格式：HH:MM:SS注釋：支持的范圍是從"-838:59:59"到"838:59:59"2位或4位格式的年。注釋：4位格式所允許的值：1901到2155。2位格式所允許的值：70到69，表示從1970到2069。*即便DATETIME和TIMESTAMP返回相同的格式，它們的工作方式很不同。在INSERT或UPDATE查詢中，TIMESTAMP自動(dòng)把自身設(shè)置為當(dāng)前的日期和時(shí)間。TIMESTAMP也接受不同的格式，比如YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD。

SQLServer數(shù)據(jù)類型

Character字符串：數(shù)據(jù)類型char(n)varchar(n)varchar(max)textUnicode字符串：數(shù)據(jù)類型nchar(n)nvarchar(n)nvarchar(max)ntextBinary類型：數(shù)據(jù)類型bit描述允許0、1或NULL存儲(chǔ)描述固定長(zhǎng)度的Unicode數(shù)據(jù)。最多4,000個(gè)字符�？勺冮L(zhǎng)度的Unicode數(shù)據(jù)。最多4,000個(gè)字符。可變長(zhǎng)度的Unicode數(shù)據(jù)。最多536,870,912個(gè)字符�？勺冮L(zhǎng)度的Unicode數(shù)據(jù)。最多2GB字符數(shù)據(jù)。存儲(chǔ)描述固定長(zhǎng)度的字符串。最多8,000個(gè)字符�？勺冮L(zhǎng)度的字符串。最多8,000個(gè)字符�？勺冮L(zhǎng)度的字符串。最多1,073,741,824個(gè)字符�？勺冮L(zhǎng)度的字符串。最多2GB字符數(shù)據(jù)。存儲(chǔ)nbinary(n)varbinary(n)varbinary(max)imageNumber類型：數(shù)據(jù)類型

tinyintsmallintintbigint

描述

固定長(zhǎng)度的二進(jìn)制數(shù)據(jù)。最多8,000字節(jié)。可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)。最多8,000字節(jié)�？勺冮L(zhǎng)度的二進(jìn)制數(shù)據(jù)。最多2GB字節(jié)。可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)。最多2GB。存儲(chǔ)

允許從0到255的所有數(shù)字。允許從-32,768到32,767的所有數(shù)字。

1字節(jié)2字節(jié)4字節(jié)8字節(jié)

允許從-2,147,483,648到2,147,483,647的所有數(shù)字。

允許介于-9,223,372,036,854,775,808和9,223,372,036,854,775,807之間的所有數(shù)字。

decimal(p,s)

固定精度和比例的數(shù)字。允許從-10^38+1到10^38-1之間的數(shù)

字。

p參數(shù)指示可以存儲(chǔ)的最大位數(shù)（小數(shù)點(diǎn)左側(cè)和右側(cè)）。p必須是1

到38之間的值。默認(rèn)是18。

s參數(shù)指示小數(shù)點(diǎn)右側(cè)存儲(chǔ)的最大位數(shù)。s必須是0到p之間的

值。默認(rèn)是0。

5-17字節(jié)

numeric(p,s)

固定精度和比例的數(shù)字。允許從-10^38+1到10^38-1之間的數(shù)

字。

p參數(shù)指示可以存儲(chǔ)的最大位數(shù)（小數(shù)點(diǎn)左側(cè)和右側(cè)）。p必須是1

到38之間的值。默認(rèn)是18。

s參數(shù)指示小數(shù)點(diǎn)右側(cè)存儲(chǔ)的最大位數(shù)。s必須是0到p之間的

值。默認(rèn)是0。

5-17字節(jié)

smallmoneymoney

介于-214,748.3648和214,748.3647之間的貨幣數(shù)據(jù)。介于-922,337,203,685,477.5808和922,337,203,685,477.5807

之間的貨幣數(shù)據(jù)。

4字節(jié)8字節(jié)float(n)從-1.79E+308到1.79E+308的浮動(dòng)精度數(shù)字?jǐn)?shù)據(jù)。參數(shù)n指示該字段保存4字節(jié)還是8字節(jié)。float(24)保存4字節(jié)，而float(53)保存8字節(jié)。n的默認(rèn)值是53。4或8字節(jié)realDate類型：數(shù)據(jù)類型

datetime

描述

從-3.40E+38到3.40E+38的浮動(dòng)精度數(shù)字?jǐn)?shù)據(jù)。4字節(jié)存儲(chǔ)8bytes

從1753年1月1日到9999年12月31日，精度為3.33毫

秒。

datetime2從1753年1月1日到9999年12月31日，精度為100納

秒。

6-8bytes

smalldatetime

datetimedatetimeoffsettimestamp

從1900年1月1日到2079年6月6日，精度為1分鐘。僅存儲(chǔ)日期。從0001年1月1日到9999年12月31日。

僅存儲(chǔ)時(shí)間。精度為100納秒。與datetime2相同，外加時(shí)區(qū)偏移。

存儲(chǔ)唯一的數(shù)字，每當(dāng)創(chuàng)建或修改某行時(shí)，該數(shù)字會(huì)更新。timestamp基于內(nèi)部時(shí)鐘，不對(duì)應(yīng)真實(shí)時(shí)間。每個(gè)表只能有一個(gè)timestamp變

量。

4bytes3bytes3-5bytes8-10bytes

其他數(shù)據(jù)類型：數(shù)據(jù)類型sql_variantuniqueidentifierxmlcursortable描述存儲(chǔ)最多8,000字節(jié)不同數(shù)據(jù)類型的數(shù)據(jù)，除了text、ntext以及timestamp。存儲(chǔ)全局標(biāo)識(shí)符(GUID)。存儲(chǔ)XML格式化數(shù)據(jù)。最多2GB。存儲(chǔ)對(duì)用于數(shù)據(jù)庫(kù)操作的指針的引用。存儲(chǔ)結(jié)果集，供稍后處理。

FIRST(column)LAST(column)MAX(column)MIN(column)返回在指定的域中第一個(gè)記錄的值返回在指定的域中最后一個(gè)記錄的值返回某列的最高值返回某列的最低值返回某列的總和STDEV(column)STDEVP(column)SUM(column)VAR(column)VARP(column)在SQLServer中的合計(jì)函數(shù)函數(shù)AVG(column)描述返回某列的行數(shù)返回某列的行數(shù)（不包括NULL值）返回被選行數(shù)返回相異結(jié)果的數(shù)目返回在指定的域中第一個(gè)記錄的值（SQLServer201*不支持）返回在指定的域中最后一個(gè)記錄的值（SQLServer201*不支持）返回某列的最高值返回某列的最低值BINARY_CHECKSUMCHECKSUMCHECKSUM_AGGCOUNT(column)COUNT(*)COUNT(DISTINCTcolumn)FIRST(column)LAST(column)MAX(column)MIN(column)STDEV(column)STDEVP(column)SUM(column)返回某列的總和VAR(column)VARP(column)Scalar函數(shù)

Scalar函數(shù)的操作面向某個(gè)單一的值，并返回基于輸入值的一個(gè)單一的值。MSAccess中的Scalar函數(shù)函數(shù)UCASE(c)LCASE(c)MID(c,start[,end])LEN(c)INSTR(c,char)LEFT(c,number_of_char)RIGHT(c,number_of_char)ROUND(c,decimals)MOD(x,y)NOW()FORMAT(c,format)DATEDIFF(d,date1,date2)描述將某個(gè)域轉(zhuǎn)換為大寫將某個(gè)域轉(zhuǎn)換為小寫從某個(gè)文本域提取字符返回某個(gè)文本域的長(zhǎng)度返回在某個(gè)文本域中指定字符的數(shù)值位置返回某個(gè)被請(qǐng)求的文本域的左側(cè)部分返回某個(gè)被請(qǐng)求的文本域的右側(cè)部分對(duì)某個(gè)數(shù)值域進(jìn)行指定小數(shù)位數(shù)的四舍五入返回除法操作的余數(shù)返回當(dāng)前的系統(tǒng)日期改變某個(gè)域的顯示方式用于執(zhí)行日期計(jì)算CURDATE()CURTIME()DATE()返回當(dāng)前的日期返回當(dāng)前的時(shí)間提取日期或日期/時(shí)間表達(dá)式的日期部分返回日期/時(shí)間按的單獨(dú)部分給日期添加指定的時(shí)間間隔從日期減去指定的時(shí)間間隔返回兩個(gè)日期之間的天數(shù)用不同的格式顯示日期/時(shí)間EXTRACT()DATE_ADD()DATE_SUB()DATEDIFF()DATE_FORMAT()SQLServerDate函數(shù)

下面的表格列出了SQLServer中最重要的內(nèi)建日期函數(shù)：

函數(shù)GETDATE()描述返回當(dāng)前日期和時(shí)間返回日期/時(shí)間的單獨(dú)部分在日期中添加或減去指定的時(shí)間間隔返回兩個(gè)日期之間的時(shí)間用不同的格式顯示日期/時(shí)間DATEPART()DATEADD()DATEDIFF()CONVERT()SQLDate數(shù)據(jù)類型

MySQL使用下列數(shù)據(jù)類型在數(shù)據(jù)庫(kù)中存儲(chǔ)日期或日期/時(shí)間值：

DATE-格式Y(jié)YYY-MM-DD

DATETIME-格式:YYYY-MM-DDHH:MM:SSTIMESTAMP-格式:YYYY-MM-DDHH:MM:SSYEAR-格式Y(jié)YYY或YY

SQLServer使用下列數(shù)據(jù)類型在數(shù)據(jù)庫(kù)中存儲(chǔ)日期或日期/時(shí)間值：

DATE-格式Y(jié)YYY-MM-DDDATETIME-格式:YYYY-MM-DDHH:MM:SSSMALLDATETIME-格式:YYYY-MM-DDHH:MM:SSTIMESTAMP-格式:唯一的數(shù)字

友情提示：本文中關(guān)于《sql總結(jié)》給出的范例僅供您參考拓展思維使用，sql總結(jié)：該篇文章建議您自主創(chuàng)作。

來(lái)源：網(wǎng)絡(luò)整理 免責(zé)聲明：本文僅限學(xué)習(xí)分享，如產(chǎn)生版權(quán)問(wèn)題，請(qǐng)聯(lián)系我們及時(shí)刪除。

《sql總結(jié)》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請(qǐng)保留原作者信息,謝謝!
鏈接地址：http://www.taixiivf.com/gongwen/747407.html

• 上一篇：SQL總結(jié)
• 下一篇：201*級(jí)新生軍訓(xùn)總結(jié)