数据库mongodb和sql语法集对比

mysql和mongodb的sql语法对比

逻辑关系对比：
Mongodb：
Db(数据库)
Collection(集合)
Dowment(文档)
Field(列)
Mysql：
Database(数据库)
Table(表)
Row(行)
Col(字段)
下面是mongodb和mysql的语法对比：
Connecting, Creating and using a DB, Create a Table / Collection

 $link = new Mongo();     $link = mysql_connect($host, $user, $pass, 1) or die("Could not connect to: {$host}.");
 $db = $link->testdb;     $sql = "CREATE DATABASE `$db`"; mysql_select_db($db`,$link);
 $col = $db->user;        $sql = "CREATE TABLE 'user'...; mysql_query($sql,$link);
 $col->drop();            mysql_query(DROP TABLE `$db`.`$tbl`, $link);
 $db->drop();             mysql_query(DROP DATABASE `$db`, $link);
 $link->close();          mysql_close($link);

Insert Data

 $doc= array('login' => 'jsmith', 'password' => ' 5f4dcc3b5aa765d61d8327deb882cf99', 'email' => 'jsmith@example.com');
 mysql_query("INSERT INTO `$tbl` SET `login`='jsmith',`password`='5f4dcc3b5aa765d61d8327deb882cf99',`email`='jsmith@example.com'");
 $id = $doc['_id'];     $id = mysql_insert_id($link);

Updating Data

 $col->update(array('_id' => $id), array('$set' => array('password' => 'b497dd1a701a33026f7211533620780d')));
 $qry = mysql_query("UPDATE `$tbl` SET `password` = 'b497dd1a701a33026f7211533620780d' WHERE `id` = {$id}", $link);

Indexing data

 $col->ensureIndex(array("login" => 1), array("unique" => true, "dropDups" => true));
 $qry = mysql_query("ALTER TABLE `$db`.`$tbl` ADD UNIQUE `login` (`login`)", $link);

Querying Data

 $res = $col->find();      SELECT * FROM `$tbl`
 $doc = $col->findone(array('_id' => $id));     SELECT * FROM `$tbl` WHERE `id` = {$id}
 $res = $col->find()->limit(1);      SELECT * FROM `$tbl` LIMIT 1
 $res = $col->find()->skip(1)->limit(1);      SELECT * FROM `$tbl` OFFSET 1 LIMIT 1;
 $res = $col->find()->sort(array('login' => -1));      SELECT * FROM `$tbl` ORDERBY `login` DESC //-1=DESC,1=ASC
 $res = $col->find(array('age' => array('$lt' => 30)));      SELECT * FROM `$tbl` WHERE `age` < 30 //$lt:<,$gt:>,$gte:>=, $lte:< =, $ne:!= $doc = $col->find(array(’a'=>’hello’,'b’=>1)); SELECT * FROM `$tbl` WHERE `a` = ‘hello’ AND `b`=1
 $doc = $col->find(array(’a'=>’hello’,'b’=>array(’$gt’=>1)));      SELECT * FROM `$tbl` WHERE `a` = ‘hello’ AND `b`>=1
 $res = $col->find(array('age' => array('$gte' => 20, '$lte' => 50)));      SELECT * FROM `$tbl` WHERE `age` >= 20 AND `age` < = 50 $col->remove(array('age'=>24),array('justOne'=>true,'safe'=>true)); DELETE FROM `$tbl` WHERE `login` = 'psmith'
 MongoCollection::insert(array $a,array $options) //$a要插入的数组, $options选项:safe是否返回结果信息,fsync是否直接插入到物理硬盘
 db.linlin.find({id:10})    返回linlin数据集ID=10的数据集
 db.linlin.find({id:10}).count()    返回linlin数据集ID=10的数据总数
 db.linlin.find({id:10}).limit(2)     返回linlin数据集ID=10的数据集从第二条开始的数据集
 db.linlin.find({id:10}).skip(8)      返回linlin数据集ID=10的数据集从0到第八条的数据集
 db.linlin.find({id:10}).limit(2).skip(8)    返回linlin数据集ID=1=的数据集从第二条到第八条的数据
 db.linlin.find({id:10}).sort()     返回linlin数据集ID=10的排序数据集
 db.linlin.findOne([query])    返回符合条件的一条数据
 db.linlin.getDB()    返回此数据集所属的数据库名称
 db.linlin.getIndexes()    返回些数据集的索引信息
 db.linlin.group({key:…,initial:…,reduce:…[,cond:...]})
 db.linlin.mapReduce(mayFunction,reduceFunction,)
 db.linlin.remove(query)    在数据集中删除一条数据
 db.linlin.renameCollection(newName)    重命名些数据集名称
 db.linlin.save(obj)    往数据集中插入一条数据
 db.linlin.stats()      返回此数据集的状态
 db.linlin.storageSize()   返回此数据集的存储大小
 db.linlin.totalIndexSize()    返回此数据集的索引文件大小
 db.linlin.totalSize()    返回些数据集的总大小
 db.linlin.update(query,object[,upsert_bool])    在此数据集中更新一条数据
 db.linlin.validate()    验证此数据集