平时偶尔会用到数据库的常用命令，来进行实验或者测试，在这里简单整理一下。
mongodb
常用命令
help命令查看帮助
> help
db.help() help on db methods
db.mycoll.help() help on collection methods
sh.help() sharding helpers
rs.help() replica set helpers
help admin administrative help
help connect connecting to a db help
help keys key shortcuts
help misc misc things to know
help mr mapreduce
show dbs show database names
show collections show collections in current database
show users show users in current database
show profile show most recent system.profile entries with time >= 1ms
show logs show the accessible logger names
show log [name] prints out the last segment of log in memory, 'global' is default
use <db_name> set current database
db.foo.find() list objects in collection foo
db.foo.find( { a : 1 } ) list objects in foo where a == 1
it result of the last line evaluated; use to further iterate
DBQuery.shellBatchSize = x set default number of items to display on shell
exit quit the mongo shell
切换/创建数据库
> use yourDB;
查看数据库/查看数据表
> show dbs;
local 0.078125GB
> use local;
switched to db local
> show tables;
startup_log
删除库
> db.dropDatabase();
复制数据库
> db.copyDatabase("local","test","127.0.0.1");
{ "ok" : 1 }
> show dbs;
test 0.078125GB
local 0.078125GB
查看当前使用的DBname
> db;
test
> db.getName()
test
查看当前库状态
> db.stats();
{
"db" : "test",
"collections" : 3,
"objects" : 5,
"avgObjSize" : 237.6,
"dataSize" : 1188,
"storageSize" : 10498048,
"numExtents" : 3,
"indexes" : 1,
"indexSize" : 8176,
"fileSize" : 67108864,
"nsSizeMB" : 16,
"dataFileVersion" : {
"major" : 4,
"minor" : 5
},
"ok" : 1
}
查看当前库链接地址
> db.getMongo();
connection to 127.0.0.1
插入数据(insert)
> db.dshowing.insert({_id:10,name:"dshowing",age:18});
查询文档
> db.dshowing.find()
{ "_id" : 10, "name" : "dshowing", "age" : 18 }
> db.dshowing.find().pretty()
{ "_id" : 10, "name" : "dshowing", "age" : 18 }
> db.dshowing.findOne()
{ "_id" : 10, "name" : "dshowing", "age" : 18 }
删除文档
> db.dshowing.remove()
> db.dshowing.findOne()
null
mysql
常用命令
显示数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dshowing |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
显示数据表
mysql> USE dshowing;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)
显示数据表结构
mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> DESCRIBE db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
建立数据库
mysql> CREATE DATABASE dshowing;
Query OK, 1 row affected (0.00 sec)
删除数据库
mysql> DROP DATABASE dshowing;
Query OK, 0 rows affected (0.00 sec)
建立数据表
mysql> CREATE TABLE test(name varchar(20),age int(2));
Query OK, 0 rows affected (0.03 sec)
删除数据表
mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)
向表中插入数据
mysql> INSERT INTO test VALUES ("dshowing",18);
Query OK, 1 row affected (0.02 sec)
查看表数据
mysql> SELECT * FROM test;
+----------+------+
| name | age |
+----------+------+
| dshowing | 18 |
+----------+------+
1 row in set (0.00 sec)
更新表数据
mysql> UPDATE test SET name="dshowing01",age=23;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM test;
+------------+------+
| name | age |
+------------+------+
| dshowing01 | 23 |
+------------+------+
1 row in set (0.00 sec)
清空表数据
mysql> DELETE FROM test;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test;
Empty set (0.00 sec)
显示当前数据库名
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
显示当前user
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
更改MySQL的root密码
SET PASSWORD
mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('dshowing');
Query OK, 0 rows affected, 1 warning (0.01 sec)
UPDATE
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('dshowing') WHERE user='root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
丢失root密码后:
通过跳过身份认证来进行密码修改。按照网上的教程使用mysqld_safe --skip-grant-tables总是出现问题，最后发现可以在配置文件里进行修改。(我的环境是Debian 15 + Mysql 5.7.18-1)
修改配置文件，在[mysqld]下添加skip-grant-tables
root@dshowing-PC:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf
.
..
...
....
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-grant-tables
skip-external-locking
....
...
..
.
重启mysql服务，进入MySQL，输入密码时直接回车进入
root@dshowing-PC:~# service mysql restart
root@dshowing-PC:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-1 (Debian)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
使用UPDATE语句修改密码
mysql> USE mysql;
Database changed
mysql> UPDATE user SET authentication_string=PASSWORD('dshowingggg') WHERE user='root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
注释掉skip-grant-tables配置行，重启服务并验证
