通過(guò)cmd命令導(dǎo)入和導(dǎo)出sql文件（以管理員身份運(yùn)行cmd）
導(dǎo)出
mysqldump -u username -ppassword dbname > dbname.sql
1.cmd 進(jìn)入到mysql的安裝路徑下的bin 目錄（如：C:\Program Files\MySQL\MySQL Server 5.7\bin）
2.例：mysql賬號(hào)為 root顽素； 密碼為：123456 先较；要導(dǎo)出的數(shù)據(jù)庫(kù)名為nodejs_db；dbsqlfile.sql為導(dǎo)出的sql腳本名字
mysqldump -u root -p123456 nodejs_db > dbsqlfile.sql （不能帶 ；）
3.在當(dāng)前目錄下就會(huì)出現(xiàn)dbsqlfile.sql 文件
導(dǎo)入：（導(dǎo)入桌面上dbsqlfile.sql腳本數(shù)據(jù)）
source C:/Users/Administrator/Desktop/dbsqlfile.sql;
例：mysql賬號(hào)為 root 伸但；密碼為：123456场斑； 要導(dǎo)入的數(shù)據(jù)庫(kù)名為nodejs_db蹋岩；dbsqlfile.sql為導(dǎo)入的sql腳本名字
打開(kāi) MySQL 5.7 Command Line Client - Unicode
然后輸入mysql密碼
1.首先創(chuàng)建 數(shù)據(jù)庫(kù)：nodejs_db增蹭；
create database nodejs_db;
2.切換到當(dāng)前 數(shù)據(jù)庫(kù)：
use nodejs_db;
3.執(zhí)行腳本 （sql腳本放在桌面：C:/Users/Administrator/Desktop/dbsqlfile.sql;）
source C:/Users/Administrator/Desktop/dbsqlfile.sql;
4.數(shù)據(jù)庫(kù)的表及表內(nèi)的數(shù)據(jù)導(dǎo)入完成。
mysql命令行操作
-
打開(kāi) MySQL 5.7 Command Line Client - Unicode
-
展示所有數(shù)據(jù)庫(kù)：
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| flasker |
| mygame_db |
| mygamedb |
| mysql |
| performance_schema |
| python_study |
| sys |
| world |
| wyj_game_db |
+--------------------+
-
創(chuàng)建數(shù)據(jù)庫(kù)（create database <數(shù)據(jù)庫(kù)名>）：
CREATE DATABASE mytest;
mysql> CREATE DATABASE mytest;
Query OK, 1 row affected (0.00 sec)
此時(shí)數(shù)據(jù)庫(kù)存在該數(shù)據(jù)庫(kù)：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| flasker |
| mygame_db |
| mygamedb |
| mysql |
| mytest |
| performance_schema |
| python_study |
| sys |
| world |
| wyj_game_db |
+--------------------+
-
刪除數(shù)據(jù)庫(kù)(drop database <數(shù)據(jù)庫(kù)名>) :
DROP DATABASE myTest;
mysql> DROP DATABASE myTest;
Query OK, 0 rows affected (0.00 sec)
此時(shí)：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| flasker |
| mygame_db |
| mygamedb |
| mysql |
| performance_schema |
| python_study |
| sys |
| world |
| wyj_game_db |
+--------------------+
10 rows in set (0.00 sec)
-
創(chuàng)建數(shù)據(jù)庫(kù)：
CREATE DATABASE mytest;
mysql> CREATE DATABASE mytest;
Query OK, 1 row affected (0.00 sec)
-
刪除數(shù)據(jù)庫(kù)：drop database <數(shù)據(jù)庫(kù)名>
drop database mytest;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database mytest;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
6 rows in set (0.00 sec)
-
打開(kāi)某一數(shù)據(jù)庫(kù)：
use mytest;
mysql> use mytest;
Database changed
創(chuàng)建數(shù)據(jù)庫(kù)表：
create table <表名> ( <字段名1> <類(lèi)型1> [,..<字段名n> <類(lèi)型n>])
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(80) DEFAULT NULL,
email varchar(120) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
mysql> CREATE TABLE `user` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `username` varchar(80) DEFAULT NULL,
-> `email` varchar(120) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
-
刪除數(shù)據(jù)庫(kù)中的表：
DROP TABLE user;
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
mysql> use mytest;
Database changed
mysql> drop table user;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
-
展示數(shù)據(jù)庫(kù)中所有的表：
show tables;
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| user |
+------------------+
-
展示數(shù)據(jù)庫(kù)表結(jié)構(gòu)：
desc user;
mysql> desc user;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(80) | YES | | NULL | |
| email | varchar(120) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
-
插入一條數(shù)據(jù)：
insert into user(username,email) values('erming','erming@example.com');
（或者：insert into user values(3,'erming','erming@example.com');）
mysql> insert into user(username,email) values('erming','erming@example.com');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+----------+----------------------+
| id | username | email |
+----+----------+----------------------+
| 1 | admin | xiaoming@example.com |
| 16 | erming | erming@example.com |
+----+----------+----------------------+
-
刪除一條數(shù)據(jù)：
delete from user where username='xiaoming';
mysql> delete from user where username='xiaoming';
Query OK, 1 row affected (0.00 sec)
-
查找表中某一條數(shù)據(jù)：
select * from user where username='admin';
mysql> select * from user where username='admin';
+----+----------+----------------------+
| id | username | email |
+----+----------+----------------------+
| 1 | admin | xiaoming@example.com |
+----+----------+----------------------+
1 row in set (0.00 sec)
-
查找表中所有數(shù)據(jù)：
select * from user;
在命令窗口按照每條數(shù)據(jù)展示：
select * from user \G;
mysql> select * from user;
+----+----------+----------------------+
| id | username | email |
+----+----------+----------------------+
| 1 | admin | admin@example.com |
| 2 | xiaoming | xiaoming@example.com |
+----+----------+----------------------+
mysql> select * from user;
+----+----------+-------------------+
| id | username | email |
+----+----------+-------------------+
| 1 | admin | admin@example.com |
+----+----------+-------------------+
-
更新一條數(shù)據(jù)：
update user set email='xiaoming@example.com' where username='admin';
mysql> update user set email='xiaoming@example.com' where username='admin';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+----------+----------------------+
| id | username | email |
+----+----------+----------------------+
| 1 | admin | xiaoming@example.com |
+----+----------+----------------------+
1 row in set (0.00 sec)
```javascript
