1.创建数据库
CREATE {DATABASE | SCHEMA} 数据库名称;
注：{}代表必选，[]代表可选,|代表2选1；
实例：
mysql> CREATE DATABASE daqing1;
Query OK, 1 row affected (0.21 sec)
在C:\ProgramData\MySQL\MySQL Server 5.7\Data的目录下可找到daqing1的文件夹。
1.1不能重复创建同名数据库，如：
mysql> CREATE SCHEMA daqing1;
ERROR 1007 (HY000): Can't create database 'daqing1'; database exists
1.2查看上一步操作产生的警告信息
SHOW WARNINGS;
实例：
mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------+
| Error | 1007 | Can't create database 'daqing1'; database exists |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)
1.3创建数据库时指定编码方式
CREATE DATABASE [IF NOT EXITS] db_name [DEFAULT] CHARACTER SET [=] '{utf8 | gbk | gb2312 | ...}'
实例：
mysql> CREATE DATABASE IF NOT EXISTS daqing2 CHARACTER SET = 'gb2312';
Query OK, 1 row affected (0.07 sec)
2.显示数据库
SHOW {DATABASES | SCHEMAS};
实例：
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| daqing1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.11 sec)
2.1查看数据库详细信息
SHOW CREATE {DATABASE | SCHEMA} db_name;
实例1：
mysql> SHOW CREATE DATABASE daqing1;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| daqing1 | CREATE DATABASE `daqing1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
实例2：
mysql> SHOW CREATE DATABASE daqing2;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| daqing2 | CREATE DATABASE `daqing2` /*!40100 DEFAULT CHARACTER SET gb2312 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
3.修改指定数据库的编码方式
ALTER {DATABASE | SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name;
实例：
mysql> ALTER DATABASE daqing2 CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE daqing2;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| daqing2 | CREATE DATABASE `daqing2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
4.打开指定数据库
USE db_name;
实例：
mysql> USE daqing1;
Database changed
4.1得到当前打开数据库名称
SELECT {DATABASE() | SCHEMA()};
实例：
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| daqing1 |
+------------+
1 row in set (0.00 sec)
5.删除指定数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
实例：
mysql> DROP DATABASE IF EXISTS daqing2;
Query OK, 0 rows affected (0.31 sec)
6.帮助的三种方式
6.1 help
实例：
mysql> help tinyint
Name: 'TINYINT'
Description:
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned
range is 0 to 255.
URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
6.2 ？
实例：
mysql> ? INT
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.
URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
6.3 \h
实例：
mysql> \h SMALLINT
Name: 'SMALLINT'
Description:
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned
range is 0 to 65535.
URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
7.存储引擎
7.1显示已有引擎
SHOW ENGINES;
mysql> SHOW ENGINES;
+--------------------+---------+-----------------------------------------------------------
-----+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+-----------------------------------------------------------
-----+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys
| YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables
| NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappe
ars) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
+--------------------+---------+-----------------------------------------------------------
-----+--------------+------+------------+
9 rows in set (0.13 sec)
另一种显示格式，查询结果按列打印
SHOW ENGINES\G;
mysql> SHOW ENGINES\G;
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
7.2查看默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%';
mysql> SHOW VARIABLES LIKE '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)
7.3显示引擎支持的变量名
mysql> SHOW VARIABLES LIKE 'have%';
mysql> SHOW VARIABLES LIKE 'have%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| have_compress | YES |
| have_crypt | NO |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | DISABLED |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_statement_timeout | YES |
| have_symlink | YES |
+------------------------+----------+
11 rows in set, 1 warning (0.00 sec)
8.迁移数据库
mysqldump -uroot -p 数据库名 > 保存文件名.sql
将数据库在当前路径下导出为保存文件名.sql文件。
