MongoDB與Mysql常用指令解釋

原文

本文旨在介紹MongoDB，Mysql的常用指令：将MongoDB 和傳統的關系型資料庫的常用指令對照起來學習，更加便于記憶和了解。

MongoDB是由資料庫（database/repository）、集合（collection）、文檔對象（document）三個層次組成。MongoDB中集合對應關系型資料庫裡的表，但是集合中沒有列、行和關系的概念，這展現了模式自由的特點。

傳統的關系資料庫一般由資料庫（database）、表（table）、記錄（record）三個層次概念組成， 

常用指令介紹：

MySQL	MongoDB	說明
mysqld	mongod	伺服器守護程序
mysql	mongo	用戶端工具
mysqldump	mongodump	邏輯備份工具
	mongorestore	邏輯恢複工具
db.repairDatabase()	修複資料庫
	mongoexport	資料導出工具
source	mongoimport	資料導入工具
grant * privileges on *.* to …	Db.addUser() Db.auth()	建立使用者并權限
show databases	show dbs	顯示庫清單
Show tables	Show collections	顯示表清單
Show slave status	Rs.status	查詢主從狀态
Create table users(a int, b int)	db.createCollection("mycoll", {capped:true, size:100000}) 另：可隐式建立表。	建立表
Create INDEX idxname ON users(name)	db.users.ensureIndex({name:1})	建立索引
Create INDEX idxname ON users(name,ts DESC)	db.users.ensureIndex({name:1,ts:-1})
Insert into users values(1, 1)	db.users.insert({a:1, b:1})	插入記錄
Select a, b from users	db.users.find({},{a:1, b:1})	查詢表
Select * from users	db.users.find()
Select * from users where age=33	db.users.find({age:33})	條件查詢
Select a, b from users where age=33	db.users.find({age:33},{a:1, b:1})
select * from users where age<33	db.users.find({'age':{$lt:33}})
select * from users where age>33 and age<=40	db.users.find({'age':{$gt:33,$lte:40}})

select * from users where a=1 and b='q'	db.users.find({a:1,b:'q'})
select * from users where a=1 or b=2	db.users.find( { $or : [ { a : 1 } , { b : 2 } ] } )
select * from users limit 1	db.users.findOne()
select * from users where name like "%Joe%"	db.users.find({name:/Joe/})	模糊查詢
select * from users where name like "Joe%"	db.users.find({name:/^Joe/})
select count(1) from users	Db.users.count()	擷取表記錄數
select count(1) from users where age>30	db.users.find({age: {'$gt': 30}}).count()
select DISTINCT last_name from users	db.users.distinct('last_name')	去掉重複值
select * from users ORDER BY name	db.users.find().sort({name:-1})	排序
select * from users ORDER BY name DESC
EXPLAIN select * from users where z=3	db.users.find({z:3}).explain()	擷取存儲路徑
update users set a=1 where b='q'	db.users.update({b:'q'}, {$set:{a:1}}, false, true)	更新記錄
update users set a=a+2 where b='q'	db.users.update({b:'q'}, {$inc:{a:2}}, false, true)
delete from users where z="abc"	db.users.remove({z:'abc'})	删除記錄
db. users.remove()	删除所有的記錄
drop database IF EXISTS test;	use test db.dropDatabase()	删除資料庫
drop table IF EXISTS test;	db.mytable.drop()	删除表/collection
db.addUser(‘test’, ’test’)	添加使用者 readOnly-->false
db.addUser(‘test’, ’test’, true)	readOnly-->true
db.addUser("test","test222")	更改密碼
db.system.users.remove({user:"test"}) 或者db.removeUser('test')	删除使用者

use admin	超級使用者
db.auth(‘test’, ‘test’)	使用者授權
db.system.users.find()	檢視使用者清單
show users	檢視所有使用者
db.printCollectionStats()	檢視各collection的狀态
db.printReplicationInfo()	檢視主從複制狀态
show profile	檢視profiling
db.copyDatabase('mail_addr','mail_addr_tmp')	拷貝資料庫
db.users.dataSize()	檢視collection資料的大小
db. users.totalIndexSize()	查詢索引的大小

 mongodb文法

MongoDB的好處挺多的，比如多列索引，查詢時可以用一些統計函數，支援多條件查詢，但是目前多表查詢是不支援的，可以想辦法通過資料備援來解決多表查詢的問題。

MongoDB對資料的操作很豐富，下面做一些舉例說明，内容大部分來自官方文檔，另外有部分為自己了解。

查詢colls所有資料

db.colls.find() //select * from colls

通過指定條件查詢

db.colls.find({‘last_name’: ‘Smith’});//select * from colls where last_name=’Smith’

指定多條件查詢

db.colls.find( { x : 3, y : “foo” } );//select * from colls where x=3 and y=’foo’

指定條件範圍查詢

db.colls.find({j: {$ne: 3}, k: {$gt: 10} });//select * from colls where j!=3 and k>10

查詢不包括某内容

db.colls.find({}, {a:0});//查詢除a為0外的所有資料

支援<, <=, >, >=查詢，需用符号替代分别為$lt，$lte，$gt，$gte

db.colls.find({ “field” : { $gt: value } } ); 

db.colls.find({ “field” : { $lt: value } } ); 

db.colls.find({ “field” : { $gte: value } } );

db.colls.find({ “field” : { $lte: value } } );

也可對某一字段做範圍查詢

db.colls.find({ “field” : { $gt: value1, $lt: value2 } } );

不等于查詢用字元$ne

db.colls.find( { x : { $ne : 3 } } );

in查詢用字元$in

db.colls.find( { “field” : { $in : array } } );

db.colls.find({j:{$in: [2,4,6]}});

not in查詢用字元$nin

db.colls.find({j:{$nin: [2,4,6]}});

取模查詢用字元$mod

db.colls.find( { a : { $mod : [ 10 , 1 ] } } )// where a % 10 == 1

$all查詢

db.colls.find( { a: { $all: [ 2, 3 ] } } );//指定a滿足數組中任意值時

$size查詢

db.colls.find( { a : { $size: 1 } } );//對對象的數量查詢，此查詢查詢a的子對象數目為1的記錄

$exists查詢

db.colls.find( { a : { $exists : true } } ); // 存在a對象的資料

db.colls.find( { a : { $exists : false } } ); // 不存在a對象的資料

$type查詢$type值為bsonhttp://bsonspec.org/數 據的類型值

db.colls.find( { a : { $type : 2 } } ); // 比對a為string類型資料

db.colls.find( { a : { $type : 16 } } ); // 比對a為int類型資料

使用正規表達式比對

db.colls.find( { name : /acme.*corp/i } );//類似于SQL中like

内嵌對象查詢

db.colls.find( { “author.name” : “joe” } );

1.3.3版本及更高版本包含$not查詢

db.colls.find( { name : { $not : /acme.*corp/i } } );

db.colls.find( { a : { $not : { $mod : [ 10 , 1 ] } } } );

sort()排序

db.colls.find().sort( { ts : -1 } );//1為升序2為降序

limit()對限制查詢資料傳回個數

db.colls.find().limit(10)

skip()跳過某些資料

db.colls.find().skip(10)

snapshot()快照保證沒有重複資料傳回或對象丢失

count()統計查詢對象個數

db.students.find({‘address.state’ : ‘CA’}).count();//效率較高

db.students.find({‘address.state’ : ‘CA’}).toArray().length;//效率很低

group()對查詢結果分組和SQL中group by函數類似

distinct()傳回不重複值

連接配接MYSQL

　　格式： mysql -h主機位址 -u使用者名 －p使用者密碼

　　1、例1：連接配接到本機上的MYSQL。

　　首先在打開DOS視窗，然後進入目錄 mysqlbin，再鍵入指令mysql -uroot -p，回車後提示你輸密碼，如果剛安裝好MYSQL，超級使用者root是沒有密碼的，故直接回車即可進入到MYSQL中了，MYSQL的提示符是：mysql>

　　2、例2：連接配接到遠端主機上的MYSQL。假設遠端主機的IP為：110.110.110.110，使用者名為root,密碼為abcd123。則鍵入以下指令：

　　mysql -h110.110.110.110 -uroot -pabcd123

　　（注:u與root可以不用加空格，其它也一樣）

　　3、退出MYSQL指令： exit （回車）

　　注意：想要成功連接配接到遠端主機，需要在遠端主機打開MySQL遠端通路權限

　　方法如下：

　　在遠端主機中以管理者身份進入

　　輸入如下指令

　　mysql>GRANT ALL PRIVILEGES ON *.* TO 'agui'@%'IDENTIFIED BY '123' WITH GRANT OPTION;

　　FLUSH PRIVILEGES;

　　//賦予任何主機通路資料的權限

　　mysql>FLUSH PRIVILEGES

　　//修改生效

　　agui為我們使用的使用者名

　　密碼為123

　　即：在遠端主機上作好設定，我們即可通過mysql -h110.110.110.110 -uagui -p123連接配接進遠端主機

修改和取消MySQL超級使用者root密碼

        （一）、密碼的修改:

使用mysqladmin指令 

1、例如你的 root使用者現在沒有密碼，你希望的密碼修改為abc，那麼指令是：mysqladmin -u root password abc 

2、如果你的root現在有密碼了，那麼修改密碼為abc的指令是：mysqladmin -u root -p password ‘newpassword'

注意，指令回車後會問你舊密碼，輸入舊密碼之後指令完成，密碼修改成功。 

          （二）、密碼的消除 

1、以root登入：mysql -u root -p 

2、mysql>use mysql; 

3、mysql>update user set password='' where user='root'; 

重新開機mysql服務就生效了。

修改Mysql中普通使用者的密碼：

       1.直接在資料庫中修改記錄

mysql> use mysql

mysql> update user set password = password(”new_password”) where user = “user_name”;

mysql> flush privileges;

其實這種方法就是更新一條資料庫記錄，與普通update語句不同的是，密碼加密存儲，需用password()函數來生成，另一個不同點是需要重新整理權限表。

       2.在資料庫中運作set password

mysql> set password for user_name = password(”new_password”);

同第一種方法，也要重新整理權限表

       3.直接在shell環境運作mysqladmin

> mysqladmin -u user_name -p password “new_password”

> mysqladmin flush-privileges

這個方法我試了幾次，每次都能将密碼記錄修改掉，但是每次修改後都無法登入，即使重新開機資料庫也無濟于事。是以建議不要采用本方法修改使用者密碼，尤其是root密碼。

       4.grant all privileges on db.table to user_name@localhost identified by “your_pwd”;

使用者名密碼的生效不必用flush privileges重新整理

注：

db.table: db表示授權哪個庫，table是相應庫裡的表。可以用*.*表示所有庫所有表。注意，如果想表示某個庫的所有表，必須用db_name.*，後面的”.*”不可省略，否則權限将無法賦予。

user_name@localhost: user_name表示使用者名，localhost表示該使用者隻能在本地通路該庫，可以用%表示從任何地方通路該庫，也可以用111.11.22.33來表示位址

your_pwd: 給使用者設定的密碼

Mysql建立普通使用者

1.使用

insert into mysql.user(Host,User,Password) values("localhost","ea",password("ea"));

時有可能遇到：Field 'ssl_cipher' doesn't have a default value的錯誤。

GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

“username”替換為将要授權的使用者名，比如clientusr；

“password”替換為clientusr設定的密碼；

locaohost可以改為%，友善你從别的IP登入。

如：GRANT USAGE ON *.* TO 'ea'@'localhost' IDENTIFIED BY 'ea' WITH GRANT OPTION;

然後對你建的使用者進行授權

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON tablename.*  TO 'username'@'localhost' IDENTIFIED BY 'password';

本語句中的權限根據實際需要确定：

"tablename"替換為授權通路的資料表table名

"username"是步驟2授權使用者名

"password"是步驟2授權使用者的設定密碼

這樣就為該使用者授予了對某資料表的SELECT, INSERT, UPDATE, DELETE, CAREATE, DROP權限。

如： GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON easy_activity.*  TO 'ea'@'localhost' IDENTIFIED BY 'ea';

生效授權：一句話即可：FLUSH PRIVILEGES;

用MySQL建立資料庫和資料庫表

1、使用SHOW語句找出在伺服器上目前存在什麼資料庫：

mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+ 3 rows in set (0.00 sec)

2、建立一個資料庫abccs 

mysql> CREATE DATABASE abccs; 

注意不同作業系統對大小寫的敏感。 

3、選擇你所建立的資料庫 

mysql> USE abccs 

Database changed 

此時你已經進入你剛才所建立的資料庫abccs. 

4、 建立一個資料庫表 

首先看現在你的資料庫中存在什麼表： 

mysql> SHOW TABLES; 

Empty set (0.00 sec) 

說明剛才建立的資料庫中還沒有資料庫表。下面來建立一個資料庫表mytable:   我們要建立一個你公司員工的生日表，表的内容包含員工姓名、性别、出生日期、出生城市。 

mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), -> birth DATE, birthaddr VARCHAR(20)); Query OK, 0 rows affected (0.00 sec)

由 于name、birthadd的列值是變化的，是以選擇VARCHAR，其長度不一定是20。可以選擇從1到255的任何長度，如果以後需要改變它的字 長，可以使用ALTER TABLE語句。）;性别隻需一個字元就可以表示："m"或"f"，是以選用CHAR(1);birth列則使用DATE資料類型。 

建立了一個表後，我們可以看看剛才做的結果，用SHOW TABLES顯示資料庫中有哪些表： 

mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | mytables | +---------------------+

5、顯示表的結構： 

mysql> DESCRIBE mytable; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | deathaddr | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

6、 往表中加入記錄 

我們先用SELECT指令來檢視表中的資料： 

mysql> select * from mytable; 

Empty set (0.00 sec)

這說明剛才建立的表還沒有記錄。 加入一條新記錄： 

mysql> insert into mytable -> values (′abccs′,′f′,′1977-07-07′,′china′); Query OK, 1 row affected (0.05 sec)

再用上面的SELECT指令看看發生了什麼變化。我們可以按此方法一條一條地将所有員工的記錄加入到表中。 

7、用文本方式将資料裝入一個資料庫表 

如果一條一條地輸入，很麻煩。我們可以用文本檔案的方式将所有記錄加入你的資料庫表中。建立一個文本檔案“mysql.txt”，每行包含一個記錄，用定位符(tab)把值分開，并且以在CREATE TABLE語句中列出的列次序給出，例如： 

abccs f 1977-07-07 china 　 mary f 1978-12-12 usa tom m 1970-09-02 usa

使用下面指令将文本檔案“mytable.txt”裝載到mytable表中:mysql> LOAD DATA LOCAL INFILE "mytable.txt" INTO TABLE pet; 

再使用如下指令看看是否已将資料輸入到資料庫表中：mysql> select * from mytable;