Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1140165
  • 博文数量: 196
  • 博客积分: 4141
  • 博客等级: 中将
  • 技术积分: 2253
  • 用 户 组: 普通用户
  • 注册时间: 2009-03-21 20:04
文章存档

2019年(31)

2016年(1)

2014年(16)

2011年(8)

2010年(25)

2009年(115)

分类: NOSQL

2016-05-09 15:27:15

SQL文法

SQL语句

InsertStatement = INSERT INTO Tablename (ColumnNameList)* VALUES ValueList //不实现 


SelectStatement = SELECT SelectFieldList FROM Tablename (WhereExpression)* (OrderExpression)*  (LimiteExpression)*;


UPDATE Tablename SET UpdateFieldList WhereExpression


DELETE FROM Tablename (WhereExpression)*

表达式

SelectFieldList      = WildChar | SelectExpressionList

SelectExpressionList = SelectExpression(, SelectExpression)*

SelectExpression     = FunctionExpression | ColumnName


UpdateFieldList      = UpdateField(, UpdateField)*

UpdateField          = ColumnName = Value


WhereExpression      =  WHERE WhereConditionList

WhereConditionList   =  WhereCondition ((or | and) WhereCondition)*

WhereCondition       =  ColumnName IS (NOT)* NULL | ColumnName CompareOp Value | ColumnName LIKE String | ColumnName IN (ValueList) | NOT WhereCondition | ColumnName BETWEEN BetweenValue AND BetweenValue |WhereCondition | ‘(’ WhereCondition ‘)’


OrderExpression      =  ORDER BY OrderConditionList

OrderConditionList   =  OrderCondition (, OrderCondition)*

OrderCondition       =  ColumnName (ASC | DESC) *


LimiteExpression     =  LIMIT Digit, Digit


FunctionExpression   = FunctionNames((FunctionParamList)?)


BetweenValue         = String | Number | Double


ColumnNameList       = ColumnName(, ColumnName)*

ValueList            = Value(,value)*

FunctionParamList    = FunctionParam(, FunctionParam)*


Tablename            = Identifier

ColumnName           = Identifier

Value                = String | Number | Double | Bool

FunctionParam        = String | Number | Double | Bool

运算符

CompareOp     = ‘=’| ‘!=’| ‘>’| ‘<’| ‘>=’| ‘<=’

CompareOp Priority :

l  ()

l  Not

l  And

l  or

数据类型

Number         = (‘+’| ‘-’)* Digit+

Double          =('+' | '-')? Digit+ ('.' Digit+)? ( ('E' | 'e') Number)?

Bool            = BoolConstant

String          = ''A sequence of Unicode characters not including double quote or newline "


BoolConstant    = true | false

词法

Identifier     = ( Letter | '_' ) ( Letter | Digit | '_' )*


Symbol         = ‘(‘ | ‘)’ | ‘,’ | ‘+’ | ‘-’ | ‘.’ | ‘=’ | ‘!=’ | ‘>’ | ‘<’ | ‘>=’ | ‘<=’


Keywords       = ‘and’ |‘as’ | ‘asc’ | ‘between’ | ‘by’ | ‘delete’ | ‘desc’ | ‘false’ | ‘from’ |‘group’ | ‘having’ |‘insert’ | ‘into’ | ‘in’ |‘is’ | ‘like’ |‘limit’ | ‘null’ | ‘not’ | ‘or’ | ‘order’ | ‘select’ | ‘set’ | ‘update’ | ‘true’ | ‘values’ |‘where’

FunctionNames  = ‘count’

WildChar       = ‘*’

Digit          = [0-9]

Letter         = [A-Za-z]

注意事项

1. Sql的关键字是大小写不敏感,其他的是大消息敏感

2. 函数名大小写不敏感


MongoDB功能

查询

1. 查询条件,并列条件,and

2. 返回指定的键

3. 查询条件:$lt、$lte、$gt、$gte、$ne

4. 或查询:

l  $in、$nin

l  $or

5. $not

6. $and

备注:

l  一个键只能对应一个修改器

l  查询子句的范围,不能够用并列字句来代替?为啥呢

7. 判断键是否存在,或者键是否为空

8. 匹配正则表达式

9. 查询数组

a)       $all

b)       数组精确匹配

c)       数组下标索引

d)       $size               // 数组长度,但是不能够跟查询字句结合,比如说($lt)

e)       $slice              // 返回数组的子集合

10. 内嵌式文档匹配

a)       精确匹配

b)       模糊匹配:$elemMatch

11. 游标功能

a)       Limit

b)       Skip

c)       Sort

d)       Count

e)       Distinct

插入


修改

1. 全文档修改

2. 部分修改

a)       增加一个键

b)       删除一个键

c)       增加一个数组

d)       $set

e)       $unset

f)        $inc       // 只能用于数字,不能用非数字

g)       $push      // 用于数组操作

h)       $addToSet  // 用于数组操作

i)        $addToSet 和 $each  // 一次添加多个数组元素

j)        $pop(键,索引)      // 从数组头或者数组尾删除数据

k)       $pull()              // 把所有匹配的东西删除掉

l)        findAndModify

删除





MongoDB SQL 文法

SQL语句

// 文档的插入以及全文档的更新,都是直接用函数,不用SQL语句


SelectStatement = SELECT SelectFieldList FROM tablename 

                     (WhereExpression)* 

                     (OrderExpression)*  

                     (SkipExpression)* 

                     (LimitExpression)*;


SelectAndUpdate = SELECTANDUPDATE SelectAndUpdateFieldList Update UpdateFieldList FROM tablename WhereExpression


UPDATE Tablename SET UpdateFieldList (WhereExpression)*


DELETE Tablename (WhereExpression)*

表达式

SelectFieldList      = WildChar | FunctionExpression | SelectExpressionList

SelectExpressionList = SelectExpression(, SelectExpression)*

SelectExpression     = ColumnName

SelectionFunction    = FunctionExpression


SelectAndUpdateFieldList  =  WildChar | SelectExpressionList


UpdateFieldList      = UpdateField(, UpdateField)*

UpdateField          = Columnname = value || FunctionExpression


WhereExpression      =  WHERE WhereConditionList

WhereConditionList   =  AndExpression (or WhereConditionList)*

AndExpression        = WhereConditition and WhereCondition

WhereCondition       =  ColumnName is (not)* NULL | 

                        ColumnName CompareOp Value |

                        ColumnName IN (NOT)* ‘[‘ValueList’]’| 

                        NOT ColumnName CompareOp Value | 

                        ‘(‘WhereConditionList’)’|

                        FunctionExpression


OrderExpression      =  ORDER BY OrderConditionList

OrderConditionList   =  OrderCondition (, OrderCondition)*

OrderCondition       =  ColumnName (ASC | DESC) *


LimitExpression      =  LIMIT Digit

SkipExpression       =  SKIP Digit


FunctionExpression   = $FunctionName((FunctionParamList)?)

FunctionParamList    = FunctionParam(, FunctionParam)*

FunctionParam        = String | Number | Double | Bool


ColumnNameList       = ColumnName(, ColumnName)*

ValueList            = Value(,value)*


FunctionName         = Identifier

Tablename            = Identifier

ColumnName           = Identifier

Value                = String | Number | Double | Bool


Keywords       = ‘and’ |‘as’ | ‘asc’ | ‘between’ | ‘by’ | ‘delete’ | ‘desc’ | ‘false’ | ‘from’ |‘group’ | ‘having’ |‘in’ |‘insert’ | ‘into’ | ‘is’ |‘limit’ | ‘not’ |‘null’ |  ‘or’ | ‘order’ | ‘select’ | ‘SelectAndUpdate’ | ‘set’ | ‘skip’|  ‘true’ | ‘update’ |‘values’ |‘where’

FunctionNames  = SelectionFunction | WhereFunction | UpdateFunction

WildChar       = ‘*’

函数

// selectfield

Count      =  count()

Distinct   =  distinct(ColumnName)


//where

Pattern      = pattern(Column, String(, Bool)*)

Arraycontain = arraycontain(Columnname, ValueList)

Arraymatch   = arraymatch(Columnname, ValueList)

Arraysize    = arraysize(Columnname, size)

Arrayrange   = arrayslice(Columname, NUMBER(, NUMBER)*)  // 暂不支持

WhereDoc     = doc(ColumnName, Type, ...)

  • λ TypeVague :ValueList
  • λ TypeExact :ValueList


// update

assgin        = assign(ColumnName, value)

set           =  set(ColumnName, Value)

unset         =  unset(ColumnNameList)

inc           =  inc(ColumnName, Number)

push          =  push(ColumnName, Value)

addToSet      =  addToSet(ColumnName, ValueList)

pop           =  pop(ColumnName, Number)

pull          =  pull(ColumnName, Value)

运算符

CompareOp     = ‘=’| ‘<>’| ‘>’| ‘<’| ‘>=’| ‘<=’

CompareOp Priority : 

  • λ () 
  • λ Not
  • λ And、or

数据类型

Number         = (‘+’| ‘-’)* Digit+

Double          =('+' | '-')? Digit+ ('.' Digit+)? ( ('E' | 'e') Number)?

Bool            = BoolConstant

String          = StringLexical

NULL 

BoolConstant    = true | false

词法

Identifier     = ( Letter | '_' ) ( Letter | Digit | '_' )*


Symbol         = ‘(‘ | ‘)’ | ‘,’ | ‘+’ | ‘-’ | ‘.’ | ‘=’ | ‘<>’ | ‘>’ | ‘<’ | ‘>=’ | ‘<=’| WildChar | ‘$’| ‘[’| ’]’


StringLexical  = "A sequence of Unicode characters not including double quote or newline "


WhiteSpace     = ‘ ‘


Digit          = [0-9]

Letter         = [A-Za-z]

注意事项

  1. 1. Sql的关键字是大小写不敏感,
  2. 2. 函数名大小写不敏感
  3. 3. 支持转义符号
阅读(1849) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~