MongoDB SQL引擎

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. 支持转义符号