MySQL —— MySQL基础概念与常用功能介绍

基本概念

数据库（DataBase，DB）

• 是指长期保存在计算机的存储设备上，按照定规则组织起来，可以被各种用户或应用共享的数据集合。

数据库管理系统（DataBase Management System，DBMS）

• 是指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。当前比较流行和常用的数据库管理系统有 Oracle、MySQL、SQL Server 和 DB2 等。

数据库系统（DataBase System，DBS）

• 是指在计算机系统中引入数据库后的系统，通常由计算机硬件、软件、数据库管理系统和数据管理员组成。

结构化查询语言（Structure Query Language、SQL）

SQL 为关系型数据库语言的国际标准。

数据库管理系统提供许多功能，可以通过 SQL（结构化查询语言）来定义和操作数据，维护数据的完整性和安全性，以及进行各种数据库的管理等。那么数据库管理系统所支持的 SQL 提供哪些功能呢?

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

   数据库管理系统提供了数据定义语言定义数据库涉及各种对象，定义数据的完整性约束、保密限制等约束。

   CREATE TABLE users (
       id INT PRIMARY KEY,
       name VARCHAR(100),
       email VARCHAR(100)
   );
   

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

   数据库管理系统提供了数据操作语言实现对数据的操作。基本的数据操作有两类：检索（查询）和更新（插入、删除和更新)。

   SELECT * FROM users WHERE age > 18;
   INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);
   UPDATE users SET age = 26 WHERE name = 'Alice';
   DELETE FROM users WHERE name = 'Alice';
   

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

   数据库管理系统提供了数据控制语言实现对数据库的控制，包含数据完整性控制、数据安全性控制和数据库的恢复等。

   -- 授予用户对数据库对象的权限
   GRANT SELECT, INSERT ON users TO 'username'@'localhost';
   -- 撤销用户对数据库对象的权限
   REVOKE INSERT ON users FROM 'username'@'localhost';
   

MySQL

• 是一款免费开源、小型、关系型数据库管理系统。

数据类型

数据类型分类

MySQL 支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。

1. 数值数据类型：
   • 整数类型 TINYINT（1字节）、SMALLINT（2字节）、MEDIUMINT（3字节）、INT（4字节）、BIGINT（8字节）；
   • 浮点小数数据类型 FLOAT（4字节）、 DOUBLE（8字节）；
   • 定点小数类型 DECIMAL(M,D)：存储精确的定点数，M 为总位数，D 为小数位数；
   • 位类型（BIT）（1~8字节），M 是位数，范围从 1 到 64。
2. 日期/时间类型：
   • YEAR（1字节）：表示年份；
   • TIME（3字节）：表示时间，时分秒，格式为 ‘HH:MM:SS’；
   • DATE（4字节）：表示日期，年月日，格式为 ‘YYYY-MM-DD’；
   • DATETIME（8字节）：表示日期和时间，年月日时分秒，格式为 ‘YYYY-MM-DD HH:MM:SS’；
   • TIMESTAMP（4字节）表示时间戳，需要经常插入或者更新日期为当前系统时刻）；
3. 字符串类型：包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。字符串类型又分为文本字符串和二进制字符串。

CHAR 与 VARCHAR 之间的特点与选择

1. CHAR 是固定长度字符串，最大长度为 255 字节，存储时，如果字符串长度小于 M，会用空格填充；VARCHAR 是可变长度字符串，最大长度为 65,535 字节（包括额外的字节用于存储长度信息），不会填充空格，实际存储的字节数取决于字符串的实际长度；
2. CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格；
3. CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间，适合存储长度相对一致的字符串，例如国家代码、邮政编码等；VARCHAR 类型在存储时可能会更节省空间，适合存储长度变化较大的字符串，在处理速度上，VARCHAR 可能稍慢于 CHAR，但差别通常不显著，尤其是在现代数据库中；

存储引擎对于选择 CHAR 和 VARCHAR 的影响：

• 对于 MyISAM 存储引擎：最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间；如果数据长度相对固定且查询性能优先，使用 CHAR 类型是一个好的选择；
• 对于 InnoDB 存储引擎：使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 IO 和数据存储总量比较好；优先选择 VARCHAR 类型，尤其是在数据长度波动较大时；

约束

主键约束（PRIMARY KEY）

主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够惟一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。

1、单字段主键：主键由一个字段组成。

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

   -- 字段名 数据类型 PRIMARY KEY [默认值]
   CREATE TABLE users (
       id INT PRIMARY KEY,
       name VARCHAR(100),
       email VARCHAR(100)
   );
   

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

   -- [CONSTRAINT <约束名>] PRIMARY KEY [字段名]
   CREATE TABLE users (
       id INT,
       name VARCHAR(100),
       email VARCHAR(100),
       CONSTRAINT pk_users_id PRIMARY KEY (id)
   );
   

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

• 语法规则如下:

  -- PRIMARY KEY [字段1,字段2,. . .，字段n]
  CREATE TABLE order_items (
      order_id INT,
      product_id INT,
      quantity INT,
      PRIMARY KEY (order_id, product_id)
  );
  

外键约束（FOREIGN KEY）

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

外键：首先它是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。例如，部门表 tb_dept 的主键是id，在员工表 tb_emp 中有一个键 deptld 与这个 id 关联。

• 主表（父表）：对于两个具有关联关系的表而言，包含主键的表，被其他表引用；
• 从表（子表）：对于两个具有关联关系的表而言，包含外键的表，引用主表中的主键；

创建外键的语法规则如下:

-- [CONSTRAINT <外键名>] FOREIGN KEY (字段名1 [,字段名2,…]) REFERENCES <主表名> (主键列1 [,主键列2,…]) 
-- 如：部门表 tb_dept 和一个员工表 tb_emp，希望在员工表中添加一个外键以引用部门表的主键 id。
CREATE TABLE tb_dept (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE tb_emp (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    deptId INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept(id)
);

“外键名” 为定义的外键约束的名称，一个表中不能有相同名称的外键；“字段名” 表示子表需要添加外键约束的字段列；“主表名” 即被子表外键所依赖的表的名称；“主键列” 表示主表中定义的主键列，或者列组合。

使用非空约束（not null）

非空约束（Not Null Constraint）指字段的值不能为空（NULL）。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。非空约束的语法规则如下：

-- 字段名 数据类型 not null
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,  -- 用户名不能为空
    email VARCHAR(100) NOT NULL,     -- 电子邮件不能为空
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

需要在已存在的表中添加非空约束，可以使用 ALTER TABLE 语句。例如：

ALTER TABLE users
MODIFY COLUMN email VARCHAR(100) NOT NULL;

使用唯一性约束（UNIQUE）

唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。唯一性约束的语法规则如下：

1. 在定义完列之后直接指定唯一约束，语法规则如下：

   -- 字段名 数据类型 UNIQUE
   CREATE TABLE users (
       id INT PRIMARY KEY,
       username VARCHAR(50) UNIQUE,  -- 用户名必须唯一
       email VARCHAR(100) UNIQUE,     -- 电子邮件必须唯一
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

2. 在定义完所有列之后指定唯一约束，语法规则如下：

   -- [CONSTRAINT <约束名>] UNIQUE(<字段名>)
   ALTER TABLE users
   ADD CONSTRAINT uq_email UNIQUE (email);
   

UNIQUE 和 PRIMARY KEY 的区别

1. 一个表中可以有多个字段声明为 UNIQUE，但只能有一个 PRIMARY KEY 声明；

2. 声明为 PRIMARY KEY 的列不允许有空值，但是声明为 UNIQUE 的字段允许空值（NULL）的存在。

使用默认约束（DEFAULT）

默认约束（Default Constraint）指定某列的默认值，当插入一条新记录时，如果该列没有显式提供值，数据库系统将自动使用默认值。如男性同学较多，性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。默认约束的语法规则如下:

-- 字段名 数据类型 DEFAULT 默认值
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(10) DEFAULT '男',  -- 默认值为 '男'
    age INT DEFAULT 18,                -- 默认值为 18
    enrolled_date DATE DEFAULT CURRENT_DATE
);

需要在已存在的表中添加默认约束，可以使用 ALTER TABLE 语句。例如：

ALTER TABLE students
ALTER COLUMN gender SET DEFAULT '男';

设置表的属性值自动增加（AUTO_INCREMENT）

AUTO_INCREMENT 关键字是一种用于自动生成唯一整数值的特性，通常用于指定表的主键字段，以确保每条记录都有唯一的标识符。默认在 MySQL 中 AUTO_INCREMENT 的初始值值是1，每新增一条记录，字段值自动加 1。一个表只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须为主键的一部分。 AUTO_INCREMENT 约束的字段可以是任何整数类型（TINYINT、SMALLIN、INT、BIGINT等）。设置表的属性值自动增加的语法规则如下：

-- 字段名 数据类型 AUTO_INCREMENT
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 主键，自动增加
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

运算符

LIKE 运算符：LIKE 运算符是 SQL 中用于进行模式匹配的运算符，以便在查询中筛选符合特定模式的记录；LIKE 运算符与通配符结合使用，可以实现灵活的字符串匹配：

1. 百分号 (%)：匹配任何数目的字符，甚至包括零字符。例如，‘a%’ 匹配以字母 a 开头的任何字符串，包括 a、abc、a123 等；
2. 下划线 (_)：只能匹配一个字符。例如，‘a_’ 匹配以字母 a 开头且后面跟着一个字符的字符串，如 ab、a1、a_ 等。

查询

连接查询

• 连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在多个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询。

内连接查询 (inner join on)

内连接（INNER JOIN）返回两个表中匹配的记录，组合成新的记录。只有当两个表中存在匹配的记录时，结果集才会包含这些记录。

连接条件同时满足的数据行：

SELECT 列名1, 列名2, ...
FROM 表1
INNER JOIN 表2 ON 表1.字段名 = 表2.字段名;

SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

外连接查询 (left/right join on)

外连接查询将查询多个表中相关联的记录以及不匹配的记录。内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中的所有数据行。外连接分为左外连接或左连接、右外连接或右连接、全外连接：

• LEFT JOIN（左连接）：返回包括左表中的所有记录、右表中与连接条件匹配的记录。如果右表没有匹配，结果中的右表字段将为 NULL；

  SELECT 列名1, 列名2, ...
  FROM 表1
  LEFT JOIN 表2 ON 表1.字段名 = 表2.字段名;
  
  -- 返回所有用户，即使他们没有任何订单，未匹配的订单 ID 将显示为 NULL
  SELECT users.username, orders.order_id
  FROM users
  LEFT JOIN orders ON users.id = orders.user_id;
  

• RIGHT JOIN（右连接）：返回包括右表中的所有记录、左表中与连接条件匹配的记录。如果左表没有匹配，结果中的左表字段将为 NULL；

  SELECT 列名1, 列名2, ...
  FROM 表1
  RIGHT JOIN 表2 ON 表1.字段名 = 表2.字段名;
  
  -- 返回所有订单，即使某些订单没有对应的用户，未匹配的用户字段将显示为 NULL。
  SELECT users.username, orders.order_id
  FROM users
  RIGHT JOIN orders ON users.id = orders.user_id;
  

• FULL OUTER JOIN（全外连接）：返回两个表中所有的记录，匹配的记录会合并在一起，未匹配的记录会在另一侧显示为 NULL；

  SELECT 列名1, 列名2, ...
  FROM 表1
  FULL OUTER JOIN 表2 ON 表1.字段名 = 表2.字段名;
  
  SELECT employees.name, departments.dept_name
  FROM employees
  FULL OUTER JOIN departments ON employees.dept_id = departments.dept_id;
  

复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。可以使用 AND 或 OR 操作符来组合多个条件。

SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id
AND orders.status = 'completed';

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。通常用于在外层查询中提供额外的条件或数据。

• 单行子查询：单行子查询返回一个单一的值（单行单列）

  SELECT name
  FROM employees
  WHERE salary > (SELECT AVG(salary) FROM employees);
  

• 多行子查询：多行子查询返回多行数据，通常与 IN、ANY 或 ALL 运算符结合使用；

  SELECT name
  FROM employees
  WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'New York');
  

• 相关子查询：相关子查询依赖于外层查询的值。每次外层查询执行时，相关子查询都会被重新执行；

  SELECT e1.name
  FROM employees e1
  WHERE e1.salary > (SELECT AVG(e2.salary) 
                      FROM employees e2 
                      WHERE e1.dept_id = e2.dept_id);
  

• 使用子查询作为新表：可以使用子查询作为新表（通常称为派生表或临时表）来简化数据处理和查询逻辑。这种方法允许将子查询的结果作为一个表，供外层查询使用。

  SELECT 列名1, 列名2, ...
  FROM (SELECT 列名1, 列名2, ...
        FROM 表名
        WHERE 条件) AS 子查询别名
  WHERE 其他条件;
  
  SELECT d.dept_name, avg_data.avg_salary
  FROM (SELECT dept_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY dept_id) AS avg_data
  JOIN departments d ON avg_data.dept_id = d.dept_id
  WHERE avg_data.avg_salary > 50000;
  

合并查询结果 (union)

利用 UNION 关键字，可以给出多条 SELECT 语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字分隔。UNION 不使用关键字 ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字 ALL 的作用是不删除重复行也不对结果进行自动排序。

返回所有记录，去重，不会有重复的记录；

SELECT name, email FROM employees
UNION
SELECT name, email FROM contractors;

返回所有记录，包括所有重复记录；

SELECT name, email FROM employees
UNION ALL
SELECT name, email FROM contractors;