sql总结

网站：公文素材库　|　时间：2019-05-29 22:28:59　|　移动端：sql总结

sql总结

123456789101112131415161718192021222324252627282930313233343536

altertable表名[add子句]

[drop子句]

创建表：

createtable表名（

列名数据类型[default缺省值][notnull][，列名数据类型[default缺省值][notnull]]

......

[，primarykey（列名[，列名]…）]主键（多个值作为联合主键）[，foreignkey（列名[，列名]…）外键references表名（列名[，列名]…）]外间关联表[，unique（列名[，列名]…）]不允许重复列[，check（条件）]）约束条件

如：

createdomainperson_namechar(20)（定义域）createtablePROF(PNOchar(10),--职工编号

PNAMEperson_namenotnull,--职工姓名SALint,--工资AGEint,--年龄DNOchar(10),--主任职工编号primarykey(PNO),--主键

foreignkey(DNO)referencesDEPT(DNO),外键关联

简单定义表：

createtableCIA(NAMEvarchar(50)primarykey,)

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

修改表：

增加新列删除列

[modify子句]修改列定义Add列不为空是需加默认值（缺省值）

修改列定义时先建一修改后的新列，复制要修改的原列，删除修改的原列

3738

索引的定义

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

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

unique（distinct）：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新。cluster：聚簇索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引。

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

示例：createclusterindexg-indexonCIA（REGION）

删除索引：

dropindex索引名

sql查询

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

2、selectnamecasewhenage>60then1whenage737475767778798081whereCNO=003）

聚集函数

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

总和：sum记数：count

聚集函数后跟groupby…having…

(单个字段查询不需要：SELECTMAX(SALARY)FROMEMPLOYEES)8283

8485

扩展阅读：SQL总结

3.6SQLAlias（别名）3.7SQLJOIN

用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

语法

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)包含指向其他文件的链接，包括网页。允许你创建一个可从下列列表中进行选择的选项列表。最多1GB4字节MySQL数据类型

在MySQL中，有三种主要的类型：文本、数字和日期/时间类型。Text类型：数据类型CHAR(size)描述保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多255个字符。VARCHAR(size)保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多255个字符。注释：如果值的长度大于255，则被转换为TEXT类型。TINYTEXTTEXTBLOBMEDIUMTEXTMEDIUMBLOBLONGTEXTLONGBLOBENUM(x,y,z,etc.)存放最大长度为255个字符的字符串。存放最大长度为65,535个字符的字符串。用于BLOBs(BinaryLargeOBjects)。存放最多65,535字节的数据。存放最大长度为16,777,215个字符的字符串。用于BLOBs(BinaryLargeOBjects)。存放最多16,777,215字节的数据。存放最大长度为4,294,967,295个字符的字符串。用于BLOBs(BinaryLargeOBjects)。存放最多4,294,967,295字节的数据。允许你输入可能值的列表。可以在ENUM列表中列出最大65535个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值：ENUM("X","Y","Z")SET与ENUM类似，SET最多只能包含64个列表项，不过SET可存储一个以上的值。Number类型：数据类型TINYINT(size)SMALLINT(size)MEDIUMINT(size)INT(size)描述-128到127常规。0到255无符号*。在括号中规定最大位数。-32768到32767常规。0到65535无符号*。在括号中规定最大位数。-8388608到8388607普通。0to16777215无符号*。在括号中规定最大位数。-2147483648到2147483647常规。0到4294967295无符号*。在括号中规定最大位数。BIGINT(size)-9223372036854775808到9223372036854775807常规。0到18446744073709551615无符号*。在括号中规定最大位数。FLOAT(size,d)带有浮动小数点的小数字。在括号中规定最大位数。在d参数中规定小数点右侧的最大位数。DOUBLE(size,d)带有浮动小数点的大数字。在括号中规定最大位数。在d参数中规定小数点右侧的最大位数。DECIMAL(size,d)作为字符串存储的DOUBLE类型，允许固定的小数点。*这些整数类型拥有额外的选项UNSIGNED。通常，整数可以是负数或正数。如果添加UNSIGNED属性，那么范围将从0开始，而不是某个负数。Date类型：数据类型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自动把自身设置为当前的日期和时间。TIMESTAMP也接受不同的格式，比如YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD。

SQLServer数据类型

Character字符串：数据类型char(n)varchar(n)varchar(max)textUnicode字符串：数据类型nchar(n)nvarchar(n)nvarchar(max)ntextBinary类型：数据类型bit描述允许0、1或NULL存储描述固定长度的Unicode数据。最多4,000个字符。可变长度的Unicode数据。最多4,000个字符。可变长度的Unicode数据。最多536,870,912个字符。可变长度的Unicode数据。最多2GB字符数据。存储描述固定长度的字符串。最多8,000个字符。可变长度的字符串。最多8,000个字符。可变长度的字符串。最多1,073,741,824个字符。可变长度的字符串。最多2GB字符数据。存储nbinary(n)varbinary(n)varbinary(max)imageNumber类型：数据类型

tinyintsmallintintbigint

描述

固定长度的二进制数据。最多8,000字节。可变长度的二进制数据。最多8,000字节。可变长度的二进制数据。最多2GB字节。可变长度的二进制数据。最多2GB。存储

允许从0到255的所有数字。允许从-32,768到32,767的所有数字。

1字节2字节4字节8字节

允许从-2,147,483,648到2,147,483,647的所有数字。

允许介于-9,223,372,036,854,775,808和9,223,372,036,854,775,807之间的所有数字。

decimal(p,s)

固定精度和比例的数字。允许从-10^38+1到10^38-1之间的数

字。

p参数指示可以存储的最大位数（小数点左侧和右侧）。p必须是1

到38之间的值。默认是18。

s参数指示小数点右侧存储的最大位数。s必须是0到p之间的

值。默认是0。

5-17字节

numeric(p,s)

固定精度和比例的数字。允许从-10^38+1到10^38-1之间的数

字。

p参数指示可以存储的最大位数（小数点左侧和右侧）。p必须是1

到38之间的值。默认是18。

s参数指示小数点右侧存储的最大位数。s必须是0到p之间的

值。默认是0。

5-17字节

smallmoneymoney

介于-214,748.3648和214,748.3647之间的货币数据。介于-922,337,203,685,477.5808和922,337,203,685,477.5807

之间的货币数据。

4字节8字节float(n)从-1.79E+308到1.79E+308的浮动精度数字数据。参数n指示该字段保存4字节还是8字节。float(24)保存4字节，而float(53)保存8字节。n的默认值是53。4或8字节realDate类型：数据类型

datetime

描述

从-3.40E+38到3.40E+38的浮动精度数字数据。4字节存储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相同，外加时区偏移。

存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp基于内部时钟，不对应真实时间。每个表只能有一个timestamp变

量。

4bytes3bytes3-5bytes8-10bytes

其他数据类型：数据类型sql_variantuniqueidentifierxmlcursortable描述存储最多8,000字节不同数据类型的数据，除了text、ntext以及timestamp。存储全局标识符(GUID)。存储XML格式化数据。最多2GB。存储对用于数据库操作的指针的引用。存储结果集，供稍后处理。

FIRST(column)LAST(column)MAX(column)MIN(column)返回在指定的域中第一个记录的值返回在指定的域中最后一个记录的值返回某列的最高值返回某列的最低值返回某列的总和STDEV(column)STDEVP(column)SUM(column)VAR(column)VARP(column)在SQLServer中的合计函数函数AVG(column)描述返回某列的行数返回某列的行数（不包括NULL值）返回被选行数返回相异结果的数目返回在指定的域中第一个记录的值（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函数

Scalar函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。MSAccess中的Scalar函数函数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)描述将某个域转换为大写将某个域转换为小写从某个文本域提取字符返回某个文本域的长度返回在某个文本域中指定字符的数值位置返回某个被请求的文本域的左侧部分返回某个被请求的文本域的右侧部分对某个数值域进行指定小数位数的四舍五入返回除法操作的余数返回当前的系统日期改变某个域的显示方式用于执行日期计算CURDATE()CURTIME()DATE()返回当前的日期返回当前的时间提取日期或日期/时间表达式的日期部分返回日期/时间按的单独部分给日期添加指定的时间间隔从日期减去指定的时间间隔返回两个日期之间的天数用不同的格式显示日期/时间EXTRACT()DATE_ADD()DATE_SUB()DATEDIFF()DATE_FORMAT()SQLServerDate函数

下面的表格列出了SQLServer中最重要的内建日期函数：

函数GETDATE()描述返回当前日期和时间返回日期/时间的单独部分在日期中添加或减去指定的时间间隔返回两个日期之间的时间用不同的格式显示日期/时间DATEPART()DATEADD()DATEDIFF()CONVERT()SQLDate数据类型

MySQL使用下列数据类型在数据库中存储日期或日期/时间值：

DATE-格式YYYY-MM-DD

DATETIME-格式:YYYY-MM-DDHH:MM:SSTIMESTAMP-格式:YYYY-MM-DDHH:MM:SSYEAR-格式YYYY或YY

SQLServer使用下列数据类型在数据库中存储日期或日期/时间值：

DATE-格式YYYY-MM-DDDATETIME-格式:YYYY-MM-DDHH:MM:SSSMALLDATETIME-格式:YYYY-MM-DDHH:MM:SSTIMESTAMP-格式:唯一的数字

友情提示：本文中关于《sql总结》给出的范例仅供您参考拓展思维使用，sql总结：该篇文章建议您自主创作。

来源：网络整理免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。

《sql总结》由互联网用户整理提供,转载分享请保留原作者信息,谢谢!
链接地址：http://www.bsmz.net/gongwen/747407.html

推荐专题

相关文章

最新文章