对数据库进行查询和修改操作的语言叫做 SQL（Structured Query Language，结构化查询语言）。SQL 语言是目前广泛使用的关系数据库标准语言，是各种数据库交互方式的基础。绝大多数 DBMS(Datebase Management System) 都支持 SQL 语言，因此，学习 SQL 使你几乎能与所有数据库打交道。同时，许多 DBMS 厂商通过增加语句和指令对 SQL 进行了扩展，虽然各厂商之间的扩展并不兼容，但它们都支持标准 SQL（ANCI SQL），所以无需担心 SQL 不适用于某个 DBMS。

SQL组成

SQL 包含以下 4 部分：

  1. 数据定义语言（Data Definition Language，DDL）

    用来创建或删除数据库以及表等对象，针对的是表的结构(列) 。主要包含以下几种命令：

    • DROP ：删除数据库和表等对象
    • CREATE ：创建数据库和表等对象
    • ALTER ：修改数据库和表等对象的结构

  2. 数据操作语言（Data Manipulation Language，DML）

    用来变更表中的记录，针对的是表的内容(行) 。主要包含以下几种命令：

    • SELECT ：查询表中的数据
    • INSERT ：向表中插入新数据
    • UPDATE ：更新表中的数据
    • DELETE ：删除表中的数据

  3. 数据查询语言（Data Query Language，DQL）

    用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。

  4. 数据控制语言（Data Control Language，DCL）

    用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：

    • GRANT ：赋予用户操作权限
    • REVOKE ：取消用户的操作权限
    • COMMIT ：确认对数据库中的数据进行的变更
    • ROLLBACK ：取消对数据库中的数据进行的变更

SQL基本书写规则

  • SQL 语句要以分号 ; 结尾 。一条 SQL 语句代表着数据库的一个操作。

  • 单词需要用半角空格或者换行来分隔

  • SQL 语句大小写规则：

    • SQL 关键字和函数名：一律不区分大小写。
    • 数据库名、表名和视图名：MySQL 用服务器主机的底层文件系统所包含的目录和文件来表示数据库和表 。因此，数据库名和表名的默认大小写取决于服务器主机的操作系统在命名方面的规定。比如 Windows 系统的文件名不区分大小写，所以运行在 Windows 系统上面的 MySQL 服务器也不用区分数据库名和表名的大小写。Linux 系统的文件名区分大小写，所以运行在 Linux 系统上的 MySQL 服务器需要区分数据库名和表名的大小写。
    • 存储程序的名字：存储函数、存储过程和事件的名字都不区分大小写。触发器的名字要区分大小写，这一点与标准 SQL 的行为有所不同。
    • 列名和索引名：一律不区分大小写。
    • 别名：表的别名在 Linux 中区分大小写，在 Windows 中不区分；列名与列的别名一律忽略大小写；

    需要注意的是，插入到表中的数据是否区分大小写由创建数据库时指定的 字符集校对规则( COLLATE ) 决定，默认使用 utf8_general_ci (不区分大小写)，而 utf8_bin 则区分大小写。后面会详细阐述相关内容。

    在 Linux 服务器下创建数据库和表时，考虑到它们以后是否会迁移到 Windows 服务器上，应该认真对待大小写的问题。

数据库基本操作

创建数据库

使用 CREATE DATABASE 语句创建数据库，语法格式如下：

1
2
3
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

[ ]中的内容是可选的。语法说明如下：

  • <数据库名> ：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
  • IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
  • [DEFAULT] CHARACTER SET ：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集，即 utf-8 。
  • [DEFAULT] COLLATE ：指定字符集的默认校对规则，即 utf8_general_ci 。
  • 注意，不同于查看数据库的操作，DATABASE 后面没有 S ，数据库名不用加 '' 。

字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。

示例

直接创建数据库：

1
2
mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.12 sec);

若再次输入CREATE DATABASE test_db; 语句，则系统会给出错误提示信息，如下所示：

1
2
mysql> CREATE DATABASE test_db;
ERROR 1007 (HY000): Can't create database 'test_db'; database exists

MySQL 不允许在同一系统下创建两个相同名称的数据库。可以加上 IF NOT EXISTS 从句，就可以避免类似错误，如下所示：

1
2
mysql> CREATE DATABASE IF NOT EXISTS test_db;
Query OK, 1 row affected (0.12 sec)

指定字符集和校对规则：

使用 MySQL 命令行工具创建一个测试数据库，命名为 test_db_char，指定其默认字符集为 utf8，默认校对规则为 utf8_chinese_ci（简体中文，不区分大小写），输入的 SQL 语句与执行结果如下所示：

1
2
3
4
mysql> CREATE DATABASE IF NOT EXISTS test_db_char
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_chinese_ci;
Query OK, 1 row affected (0.03 sec)

仅在最后一行末尾打上分号即可 。DEFAULT 可写可不写。
这时，可以使用 SHOW CREATE DATABASE 查看 test_db_char 数据库的定义声明，发现该数据库的指定字符集为 utf8 ，运行结果如下所示：

1
2
3
4
5
6
7
mysql> SHOW CREATE DATABASE test_db_char;
+--------------+-----------------------------------------------------+
| Database     | Create Database                                     |
+--------------+-----------------------------------------------------+
| test_db_char | CREATE DATABASE `test_db_char` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+-----------------------------------------------------+
1 row in set (0.00 sec)

查看数据库

在 MySQL 数据库中存在系统数据库和自定义数据库，系统数据库是在安装 MySQL 后系统自带的数据库，自定义数据库是由用户定义创建的数据库。在 MySQL 中，可使用 SHOW DATABASES 语句来查看或显示当前用户权限范围以内的数据库。查看数据库的语法格式为：

1
SHOW DATABASES LIKE '数据库名';
  • LIKE 从句是可选项，用于匹配指定的数据库名称；LIKE 从句可以部分匹配，也可以完全匹配。
    • 查看名字为 stuInfo 的数据库 ：SHOW DATABASES LIKE 'stuInfo';
    • 查看名字中包含 stuInfo 的数据库：SHOW DATABASES LIKE '%stuInfo%';
    • 查看名字以 stuInfo 开头的数据库：SHOW DATABASES LIKE '%stuInfo';
    • 查看名字以 stuInfo 结尾的数据库：SHOW DATABASES LIKE 'stuInfo%';
  • 注意是 DATABASES ，可别忘了后面的 S 。
  • 可用单引号也可以双引号。

系统数据库：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 row in set (0.22 sec)

还未创建自定义数据库时，SHOW DATABASES; 列出的数据库即为系统数据库：

  • information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
  • mysql：MySQL 的核心数据库，类似于 SQL Server 中的 master 表，主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
  • performance_schema：主要用于收集数据库服务器性能参数。
  • sakila：MySQL 提供的样例数据库，该数据库共有 16 张表，这些数据表都是比较常见的，在设计数据库时，可以参照这些样例数据表来快速完成所需的数据表。
  • sys：MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题。
  • world：world 数据库是 MySQL 自动创建的数据库，该数据库中只包括 3 张数据表，分别保存城市，国家和国家使用的语言等内容。

查看数据库的定义声明：

使用 SHOW CREATE DATABASE 查看 test_db_char 数据库的定义声明，发现该数据库的指定字符集为 utf8 ，运行结果如下所示：

1
2
3
4
5
6
7
mysql> SHOW CREATE DATABASE test_db_char;
+--------------+-----------------------------------------------------+
| Database     | Create Database                                     |
+--------------+-----------------------------------------------------+
| test_db_char | CREATE DATABASE `test_db_char` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+-----------------------------------------------------+
1 row in set (0.00 sec)

说明：第五行第二列中test_db_char 两旁的反引号 ` 是为了在创建数据库时规避关键字冲突，比如我脑壳烧坏了，想创建一个名为 CREATE 的数据库，就必须在创建时输入反引号：

1
2
mysql> CREATE DATABASE `CREATE`;
Query OK, 1 row affected (0.00 sec)

查看当前所在数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT DATABASE();
############################
mysql> show tables;
+-------------------+
| Tables_in_test    |#数据库名
+-------------------+
| push_test         |
| ship_order_detail |
+-------------------+
2 rows in set (0.00 sec)
############################
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.19, for Win64 (x86_64)

Connection id:          3
Current database:       no1sc  #当前数据库
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.19 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 1 day 4 hours 20 min 58 sec

修改数据库

在 MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改，数据库的这些特性都储存在 db.opt 文件中。修改数据库的语法格式为：

1
2
3
ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

语法说明如下：

  • ALTER DATABASE 用于更改数据库的全局特性。
  • 使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
  • 数据库名称可以忽略，此时语句对应于默认数据库。

示例： 

1
2
3
4
5
6
7
8
9
10
mysql> ALTER DATABASE test_db
    -> DEFAULT CHARACTER SET gb2312
    -> DEFAULT COLLATE gb2312_chinese_ci;
mysql> SHOW CREATE DATABASE test_db;
+----------+--------------------------------------------------------+
| Database | ALTER Database                                        |
+----------+--------------------------------------------------------+
| test_db  | ALTER DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET gb2312 */|
+----------+--------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

Mysql 数据库中储存着一个或多个数据表，而数据表的本质仍是文件，这种文件有特殊的结构，非常方便数据的操作；数据库则体现为一个目录，如下图：

stuinfo数据库 table_1数据表

删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一同被删除。命令如下：

1
DROP DATABASE [ IF EXISTS ] <数据库名>
  • IF EXISTS：用于防止当数据库不存在时发生错误。
  • DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

示例： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#删除stuInfo数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| stuinfo            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> DROP DATABASE stuInfo;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

另外，使用 MySQL 命令删除数据库后不可恢复，需谨慎操作；若直接删除数据库目录(将数据库目录放入回收站)，似乎也能够完成删除操作，但不建议如此，因为其他文件中相关的配置可能没有即时更改(个人猜测)。

选择数据库

当用 CREATE DATABASE 语句创建数据库之后，该数据库不会自动成为当前数据库，需要用 USE 来指定当前数据库。其语法格式为：

1
USE <数据库名>

无需加 DATABASE ! 该语句可以通知 MySQL 把 <数据库名> 所指示的数据库作为当前默认数据库。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| stuinfo            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use stuinfo;
Database changed