三、数据库操作

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文件。

三、数据库操作

推荐阅读更多精彩内容