sql總結(jié)

123456789101112131415161718192021222324252627282930313233343536

altertable表名[add子句]

[drop子句]

創(chuàng)建表：

createtable表名（

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

......

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

如：

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

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

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

簡單定義表：

createtableCIA(NAMEvarchar(50)primarykey,)

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

修改表：

增加新列刪除列

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

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

3738

索引的定義

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

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

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

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…

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

8485

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

3.6SQLAlias（別名）3.7SQLJOIN

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

語法

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

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

實例

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

（JOIN）實例

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（等價于1和0）注釋：Yes/No字段中不允許Null值1比特OleObjectHyperlinkLookupWizard可以存儲圖片、音頻、視頻或其他BLOBs(BinaryLargeOBjects)包含指向其他文件的鏈接，包括網(wǎng)頁。允許你創(chuàng)建一個可從下列列表中進行選擇的選項列表。最多1GB4字節(jié)MySQL數(shù)據(jù)類型

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

tinyintsmallintintbigint

描述

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

允許從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ù)指示可以存儲的最大位數(shù)（小數(shù)點左側(cè)和右側(cè)）。p必須是1

到38之間的值。默認是18。

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

值。默認是0。

5-17字節(jié)

numeric(p,s)

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

字。

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

到38之間的值。默認是18。

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

值。默認是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的浮動精度數(shù)字數(shù)據(jù)。參數(shù)n指示該字段保存4字節(jié)還是8字節(jié)。float(24)保存4字節(jié)，而float(53)保存8字節(jié)。n的默認值是53。4或8字節(jié)realDate類型：數(shù)據(jù)類型

datetime

描述

從-3.40E+38到3.40E+38的浮動精度數(shù)字數(shù)據(jù)。4字節(jié)存儲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分鐘。僅存儲日期。從0001年1月1日到9999年12月31日。

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

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

量。

4bytes3bytes3-5bytes8-10bytes

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

FIRST(column)LAST(column)MAX(column)MIN(column)返回在指定的域中第一個記錄的值返回在指定的域中最后一個記錄的值返回某列的最高值返回某列的最低值返回某列的總和STDEV(column)STDEVP(column)SUM(column)VAR(column)VARP(column)在SQLServer中的合計函數(shù)函數(shù)AVG(column)描述返回某列的行數(shù)返回某列的行數(shù)（不包括NULL值）返回被選行數(shù)返回相異結(jié)果的數(shù)目返回在指定的域中第一個記錄的值（SQLServer201*不支持）返回在指定的域中最后一個記錄的值（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ù)的操作面向某個單一的值，并返回基于輸入值的一個單一的值。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)描述將某個域轉(zhuǎn)換為大寫將某個域轉(zhuǎn)換為小寫從某個文本域提取字符返回某個文本域的長度返回在某個文本域中指定字符的數(shù)值位置返回某個被請求的文本域的左側(cè)部分返回某個被請求的文本域的右側(cè)部分對某個數(shù)值域進行指定小數(shù)位數(shù)的四舍五入返回除法操作的余數(shù)返回當前的系統(tǒng)日期改變某個域的顯示方式用于執(zhí)行日期計算CURDATE()CURTIME()DATE()返回當前的日期返回當前的時間提取日期或日期/時間表達式的日期部分返回日期/時間按的單獨部分給日期添加指定的時間間隔從日期減去指定的時間間隔返回兩個日期之間的天數(shù)用不同的格式顯示日期/時間EXTRACT()DATE_ADD()DATE_SUB()DATEDIFF()DATE_FORMAT()SQLServerDate函數(shù)

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

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

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

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ù)庫中存儲日期或日期/時間值：

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)作。

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

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

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