【从入门到精通系列】-- MySQL（持续更新中……）

系列文章目录

---

前言

一、初识MySQL

1.1数据库基础

数据库由一批数据构成的有序的集合；

1.1.1 什么是数据库

数据库的发展大致分为：

• 人工管理阶段
• 文件系统阶段
• 数据库系统阶段
• 高级数据库阶段

其种类可分为：

• 层次式数据库
• 网络式数据库
• 关系式数据库

数据库的定义很多，其中一个普遍的定义：数据库（DataBase ，DB）是一个长期存储在计算机内的，有组织的，有共享的，统一管理的数据集合。

数据库的特点：

• 数据共享
• 减少数据冗余
• 采用特定的数据类型
• 具有较高的独立性
• 具有统一的数据控制功能

1.1.2 表

在关系数据库中，数据库表是一系列二维数组的集合，用来存储数据和操作数据的逻辑结构。它由纵向的列和横向的行组成，行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性，都有相应的描述信息，如数据类型，数据宽度等；

1.1.3 数据类型

数据类型决定了数据在计算机中的存储格式，代表不同的信息类型。常用的数据类型有：

• 整数数据类型
• 浮点数数据类型
• 精确小数类型
• 二进制数据类型
• 日期/时间数据类型
• 字符串数据类型

1.1.4 主键

主键（Primary Key）又称主码，用于唯一地标识表中的每一条记录。可以定义表中的一列或多列为主键，主键列上不能有两行相同的值，也不能为空值。

1.2 数据库技术构成

数据库系统由硬件部分和软件部分共同构成，硬件主要用于存储数据库中的数据，包括计算机、存储设备等。软件部分则主要包括DBMS,支持DBMS运行的操作系统，以及支持多种语言进行应用开发的访问技术等。

1.2.1 数据库系统

数据库系统主要有2个部分组成：

• 数据库：用于存储数据的地方
• 数据库管理系统：用于管理数据库的软件
• 数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库的软件补充；

数据库提供了一个存储空间用以存储各种数据，可以将数据库视为一个存储数据的容器。一个数据库可能包含许多文件，一个数据库系统通常包括许多输数据库。

数据库管理系统（DataBase Management System,DBMS）是用户创建、管理和维护数据库时所使用的软件，位于用户和操作系统之间，对数据库进行统一管理。

虽然有了DBMS，但在很多情况下，DBMS无法满足对数据管理的要求，数据库应用程序（DataBase Application）的使用可以满足数据管理的更高要求；

1.2.2 SQL语言

对数据库进行查询和修改操作的语言叫做SQL。SQL的含义是结构化查询语言（Structured Query Language）.SQL有许多不同的类型，有三个标准：

ANSI（美国国家标准机构）SQL，对ANSI SQL修改后在1992年采纳的标准，称为SQL-92或SQL2.最近的SQL-99标准，从SQL2扩充而来并增加了对象关系特征和许多新功能。其次，各大数据库厂商提供不同版本的SQL，这些版本的SQL不但能包括原始的ANSI标准，而且在很大程度上支持SQL-92标准；

SQL包含以下4个部分：

1. 数据定义语言（DDL）：DROP、CREATE、ALTER等语句；
2. 数据操作语言（DML）：INSERT、UPDATE、DELETE语句；
3. 数据查询语言（DQL）：SELECT语句
4. 数控制语言（DCL）：GRANT、REVOKE、COMMIT、ROLLBACK等语句

下面是一条SQL语句的例子，该语句声明创建一个名叫students的表：

首先启动数据库：

mysql -uroot -p

CREATE TABLE students
(
student_id INT UNSIGNED,
name VARCHAR(30),
sex CHAR(1),
birth DATE,
PRIMARY KEY (student_id)
 );

出现：

ERROR 1046 (3D000): No database selected

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.41 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE students
    -> (
    -> student_id INT UNSIGNED,
    -> name VARCHAR(30),
    -> sex CHAR(1),
    -> birth DATE,
    -> PRIMARY KEY (student_id)
    ->  );
Query OK, 0 rows affected (1.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stu            |
| students       |
+----------------+
2 rows in set (0.04 sec)

该表中包含4个字段，分别为student_id、name、sex、birth，其中student_id定义为主键；
现在只是定义了一张表格，没有任何数据，接下来插入数据：

mysql> INSERT INTO students (student_id, name, sex, birth)
    -> VALUES (41048101, 'Lucy Green', '1', '1997-11-01')
    -> ;
Query OK, 1 row affected (0.26 sec)

执行完该语句后，students表中会增加一行记录，该记录中字段student_id的值为41048101，……；

再使用SELECT查询语句获取刚才插入的数据：

mysql> SELECT name FROM students WHERE student_id = 41048101;
+------------+
| name       |
+------------+
| Lucy Green |
+------------+
1 row in set (0.02 sec)

1.2.3 数据库访问接口

不同的程序语言会有各种不同的数据库访问接口，程序语言通过这些接口，执行SQL语句，进行数据库管理，主要的数据库访问接口有：

1. ODBC
   Open Database Connectivity(ODBC, 开放数据库互连)，技术为访问不同的SQL数据库提供了一个共同的接口。一个应用程序可以通过共同的一组代码访问不同的SQL数据库管理系统（DBMS），一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS，所有的数据库操作由对应的DBMS的ODBC驱动程序完成。也就是说，不论是Access、MySQL、还是Oracle，均可以用ODBC API进行访问。
2. JDBC
   Java Data Base Connectivity(JDBC, Java数据库连接)用于Java应用程序连接数据库的标准方法，是一种用于执行SQL语句的Java API。
3. ADO.NET
   ADO.NET是微软在.NET框架下开发设计的一组用于和数据源进行交互的面向对象类库。
4. PDO
   PDO（PHP Data Object）为PHP访问数据库定义了一个轻量级、一致性的接口，它提供了一个数据访问抽象层。

针对不同的程序语言，MySQL提供了不同数据库的访问连接驱动；

1.3 什么是MySQL

MySQL是一个小型关系型数据库管理系统，与其它大型数据库管理系统（Oracle、DB2、SQL Server）相比，MySQL规模小、功能有限，但是它体积小】速度快、成本低；

1.3.1 客户机-服务器软件

1.3.2 MySQL 版本

1.3.3 MySQL 的优势

1.3.4 MySQL5.7 的新功能

1. 支持JSON
2. 性能和可扩展性
3. 改进复制以提高可用性的性能
4. 性能模式提供更好的视角
5. 安全
6. 优化
7. GIS

1.4 MySQL 工具

1.4.1 MySQL 命令行实用程序

1.4.2 MySQL Workbench

二、数据库技术构成

2.1 MySQL 安装

2.1.1 安装 MySQL

安装指南

2.1.2 命令

net start MySQL
net stop MySQL
mysql -h hostname -u username -p
mysql -h localhose -u root -p

2.3 MySQL 常用图形管理工具

2.3.1 图形管理工具

• MySQL Workbench
• phpMyAdmin
• Navicat
• MySQLDumper
• SQLyog

2.4 MySQL 常用图形管理工具

2.4.1 Linux下的版本

三、数据库的基本操作

3.1 创建数据库

查看：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.39 sec)

创建：

mysql> create database database_name;
Query OK, 1 row affected (0.20 sec)

例3.1

mysql> create database test_db;
Query OK, 1 row affected (0.06 sec)

mysql> show create database test_db \G
*************************** 1. row ***************************
       Database: test_db
Create Database: CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

再次查看：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| database_name      |
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_db            |
+--------------------+
8 rows in set (0.00 sec)

3.2 删除数据库

DROP DATABASE database_name

例3.2：

DROP DATABASE test_db;

使用DROP DATABASE命令时要非常谨慎，在执行该命令时，MySQL不会给出任何提示；

3.3 数据库存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建，查询，更新和删除操作；不同的引擎提供不同的存储机制，索引技巧，锁定水平等

3.3.1 MySQL 存储引擎简介

MySQL5.7 支持的存储引擎有：

• InnoDB
• MyISAM
• Memory
• Merge
• Archive
• Federated
• CSV
• BLACKHOLE

mysql> SHOW ENGINES \G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.04 sec)

备注：Support：YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认存储引擎；

四、数据表的基本操作

在数据库中，数据表是数据库中最重要的、最基本的操作对象，是数据存储的基本单位。

4.1 创建数据表

创建数据表的过程是规定数据列的属性的过程。

4.1.1 创建数据表

数据表属于数据库，在创建数据表之前，应该使用：

use <数据库名>

指定在哪个数据库中进行，如果没有选择数据库，会出现：

No database selected

创建数据表的语句：

CREATE TABLE <表名>
(
字段名1, 数据类型 [列级别约束条件] [默认值],
字段名2, 数据类型 [列级别约束条件] [默认值],
……
[表级别约束条件]
);

注意：

• 要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
• 数据表中每一列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。

例4.1：创建如下工作表

字段名称	数据类型	备注
id	INT(11)	员工编号
name	VARCHAR(25)	员工名称
deptId	INT(11)	所在部门编号
salary	FLOAT	工资

步骤：

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

mysql> use test_db;
Database changed
mysql> CREATE TABLE tb_empl
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.49 sec)

执行后，便创建了一个名称为tb_empl的数据表，使用：

SHOW TABLES;

语句可查看数据表是否创建成功；

mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_empl           |
+-------------------+
1 row in set (0.06 sec)

4.1.2 使用主键约束

主键，又称主码，是表中一列或多列的组合。主键（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键可以唯一地标识表中的每一条记录，可以结合外键来定义不同数据表之间的关系，并且加快数据库查询的速度。

1、单字段主键
单字段主键由一个字段组成，SQL语句：

1. 在定义列的同时指定主键，语法规则如下：

字段名 数据类型 PRIMARY KEY [默认值]

例4.2 定义数据表tb_emp2,其主键为id：

mysql> CREATE TABLE tb_emp2
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.46 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp2           |
| tb_empl           |
+-------------------+
2 rows in set (0.05 sec)

2. 在定义完所有的列之后指定主键；

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

例4.3：定义数据表tb_emp3:

mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11) ,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.37 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp2           |
| tb_emp3           |
| tb_empl           |
+-------------------+
3 rows in set (0.00 sec)

2、多字段联合主键
主键由多个字段联合组成：

PRIMARY KEY [字段1, 字段2, ..., 字段n]

例4.4：定义数据表tb_emp4 , 假设表中间没有主键id，为了唯一确认一个员工，可以把name, deptId联合起来作为主键：

mysql> CREATE TABLE tb_emp4
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name, deptId)
    -> );
Query OK, 0 rows affected, 1 warning (0.67 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_empl           |
+-------------------+
4 rows in set (0.00 sec)

4.1.3 使用外键约束

外键用来在两个表的数据之间建立链接。它可以是一列或多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表外键可以为空值，若不为空值，则每个外键值必须等于另一个表中主键的某个值；

外键：首先它是一个字母，它可以不是本表的主键，但对应另外一个表的主键。外键的作用主要是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行；

创建外键的规则如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [, 字段名2, …]
REFERENCES <主表名> 主键列1 [, 主键列2, …]

“外键名”为定义的外键约束的名称。
例4.5：定义数据表tb_emp5，并在tb_emp5表上创建外键约束。
创建一个部门表tb_dept1,：

mysql> CREATE TABLE tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected, 1 warning (0.31 sec)

---

总结

分享：
写作，是心灵的事业，是没有上下班界限的，不是只有正襟危坐才算写作。写作是一件令人遗憾的事业一—越追求完美，所感知的遗漏和瑕疵似乎也越多；写作的时间越长，惶惑、焦虑及不确定性似乎也与日俱增。