MySQL

MySQL

Yiuhang Chan
  2019-03-15 11:02:13 2019-03-15 11:02:13 创建   2024-02-28 18:49:38 2024-02-28 18:49:38 更新      12.6k 字  50 分钟  

数据库

数据库（Database），简称DB，本质上是由一系列文件构成的集合，构建成一个用于存储数据的仓库。这种数据存储系统是建立在文件系统之上的，设计用来以特定格式组织、存储和管理数据。数据库使用户能够对数据执行一系列操作，如增加（Create）、删除（Delete）、修改（Update）和查询（Retrieve）等，从而有效地管理和操纵数据。可以将数据库比喻为哆啦A梦的百宝袋，其中存储的数据就像袋中的各种道具，可以根据需要被取出或存入。

关系型数据库

关系型数据库基于关系模型，即数据以二维表格的形式进行组织。它由一系列具有固定结构（列）和可变数据项（行）的表格（数据表）及这些表格之间的关系所组成。核心元素包括：

  • 数据行：代表一条记录，类似于Python中的一个对象。
  • 数据列：代表一个字段，相当于对象的属性。
  • 数据表：由数据行（记录）的集合构成，每个数据表存储一类相似的数据。
  • 数据库：由一个或多个相关数据表的集合构成，形成了数据的整体结构。

常见的关系型数据库系统包括Oracle、DB2、Microsoft SQL Server、Microsoft Access、和MySQL等。

非关系型数据库

非关系型数据库，也称为NoSQL（Not Only SQL）数据库，是指不仅仅支持SQL的分布式数据存储系统。它不依赖固定的表结构，允许存储无固定格式的数据，因此提供了更为灵活的数据模型，并能够处理大规模分布式数据的存储。非关系型数据库按数据模型可分为：

  • 键值存储数据库：以键值对的方式存储数据，适用于快速查询和存储简单数据模型的场景。
  • 文档型数据库：存储文档格式数据（如JSON、XML），适用于存储结构化或半结构化的数据，便于开发者直接在应用程序中使用。
  • 列存储数据库：将数据以列簇方式存储在硬盘上，适用于分析大规模数据集，提高读写性能。
  • 图数据库：使用图结构存储实体及其关系，适合处理复杂的网络结构数据。

常见的非关系型数据库系统包括NoSql、Cloudant、MongoDb、Redis、HBase等。

关系型数据库和非关系型数据库各有优势和适用场景。关系型数据库适合需要严格数据完整性和复杂查询的应用场景，而非关系型数据库则更适合于需要灵活数据模型、快速迭代和能够水平扩展处理大量数据的场景。在现代的数据架构设计中，根据应用需求选择合适的数据库类型是至关重要的。

什么是MySQL

数据库技术是计算机科学领域中发展最迅速且应用最为广泛的技术之一。它在现代信息社会扮演着核心角色，其应用范围覆盖了几乎所有行业和领域。个人身份信息、在线购物的登录、支付和购物车信息、旅行的订票和酒店预订信息、天气和交通信息，以及QQ、微信等通讯服务的数据，都是通过数据库进行记录、查询、修改和管理的。在大数据时代，数据库技术和大数据分析的结合更是释放了巨大的潜力，使得我们能够从庞杂的数据中提炼出难以直接观察到的信息规律和价值，进一步促进了决策制定和创新。

数据库技术不仅快速发展、应用范围广泛，而且具有高度的信息价值挖掘能力。全球许多最大及发展最快的组织，如Facebook、Twitter、Booking.com、Verizon等，都依赖于MySQL这类数据库管理系统来支撑他们的高容量网站、关键业务系统和软件产品。MySQL数据库，从其诞生之初的完全开源，发展至今已经衍生出了免费的社区版以及收费的标准版和企业版，满足了不同用户和组织的需求。

随着信息技术的不断进步和大数据技术的应用，数据库技术的发展未来仍然充满广阔的前景。它不仅是现代信息领域从业人员的必备技能，也是推动社会信息化进程、实现数据驱动决策的重要基础。因此，深入理解数据库技术的原理、掌握其应用和管理的技巧，对于追求事业发展和技术创新的专业人士来说至关重要。

MySQL数据库的结构可以被分解为几个关键的组件，每个组件都扮演着特定的角色，确保数据库能够高效且安全地存储和处理数据。

形式结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
graph LR
    Client[客户端] -->|发出请求| LoadBalancer[负载均衡]
    LoadBalancer -->|分发请求| Server1[服务器1]
    LoadBalancer -->|分发请求| Server2[服务器2]
    LoadBalancer -->|分发请求| Server3[服务器3]

    Server1 -->|读写请求| MySQLMaster[MySQL 主服务器]
    Server2 -->|读写请求| MySQLMaster
    Server3 -->|只读请求| MySQLSlave[MySQL 从服务器]

    MySQLMaster -->|数据复制| MySQLSlave

    classDef server fill:#f96,stroke:#333,stroke-width:2px;
    classDef database fill:#9f6,stroke:#333,stroke-width:2px;

    class LoadBalancer,Server1,Server2,Server3 server;
    class MySQLMaster,MySQLSlave database;

客户端发送请求到负载均衡器，负载均衡器将请求分发到多个服务器。服务器对 MySQL 主服务器进行读写操作，而对从服务器进行只读操作。MySQL 主服务器将数据复制到从服务器，以保持数据的一致性。

运行逻辑

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
graph LR
    Client -->|发送查询| LoadBalancer
    LoadBalancer -->|读请求| Slave1[MySQL 从服务器1]
    LoadBalancer -->|读请求| Slave2[MySQL 从服务器2]
    LoadBalancer -->|写请求| Master[MySQL 主服务器]

    Master -->|复制日志| Slave1
    Master -->|复制日志| Slave2

    classDef read fill:#9f6,stroke:#333,stroke-width:2px;
    classDef write fill:#f96,stroke:#333,stroke-width:2px;
    classDef database fill:#ff6,stroke:#333,stroke-width:2px;

    class LoadBalancer read;
    class Master write;
    class Slave1,Slave2 database;

  • 客户端发送查询到负载均衡器。
  • 负载均衡器根据查询类型将读请求分发到从服务器，将写请求发送到主服务器。
  • 主服务器处理写请求，并将更改通过复制日志发送到从服务器，确保数据的一致性。

语句类型

MySQL作为一款流行的关系型数据库管理系统，提供了一套丰富的语句类型来管理和操作数据库。这些语句类型可以分为四大类：数据定义语言（DDL）、数据操纵语言（DML）、数据查询语言（DQL）和数据控制语言（DCL），每种语言类型都承担着不同的职责和功能。

  1. 数据定义语言（DDL）：DDL语句用于定义和修改数据库的结构。这包括创建、修改、删除数据库和其中的对象，如表、视图、索引、触发器、存储过程等。DDL操作通常是自动提交的，这意味着一旦执行，就不能回滚。主要的DDL语句包括：
    • CREATE：用于创建新的数据库、表、索引等对象。
    • ALTER：用于修改现有数据库对象的结构。
    • DROP：用于删除数据库对象。
    • TRUNCATE：用于删除表中的所有记录，但不删除表本身，这通常比删除每一行要快得多。
  2. 数据操纵语言（DML）：DML语句用于数据库记录的添加、删除、更新，并检查数据的完整性。它们直接影响到数据库中的数据，但不改变数据库的结构。DML主要的语句包括：
    • INSERT：用于向表中添加新的记录。
    • UPDATE：用于修改表中的现有记录。
    • DELETE：用于从表中删除记录。
    • SELECT：虽然通常被分类为DQL，但有时也被视为DML的一部分，用于检索数据。
  3. 数据查询语言（DQL）：DQL主要是指SELECT语句，用于查询数据库中表的记录。通过SELECT语句，可以读取一个或多个表中的数据，支持复杂的查询，包括联结、分组和排序等操作。SELECT语句的灵活性和强大功能是数据库交互中最常用的部分之一。
  4. 数据控制语言（DCL）：DCL语句用于定义或改变数据库的访问权限和安全级别。DCL提供了管理数据库安全性的机制，包括授权用户访问和操作数据库的权限。主要的DCL语句包括：
    • GRANT：用于给用户分配权限。
    • REVOKE：用于撤销用户的权限。

数据类型

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 Bytes (-128，127) (0，255) 小整数值
SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss 混合日期和时间值
TIMESTAMP 4 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYY-MM-DD hh:mm:ss 混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

枚举与集合类型（Enumeration and Set Types）

  • ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合。
  • SET: 集合类型，用于存储多个值，可以选择多个预定义的集合。

空间数据类型（Spatial Data Types）

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据（地理信息、几何图形等）。

约束类型

MySQL数据库通过使用不同类型的约束来保证数据的完整性、准确性以及关系的一致性。约束是施加在表列上的规则，用于限制存入表中的数据类型。以下是MySQL中常见的约束类型，它们各自承担着不同的职责和作用：

  1. 主键约束（Primary Key）：主键约束确保表中的每一行都有一个唯一标识。因此，被标记为主键的列不允许有重复值，且不能为NULL。一个表中只能有一个主键，该主键可以包含一个或多个列（复合主键）。
  2. 自增约束（Auto Increment）：自增约束通常与主键一起使用，用于在新记录插入表时自动生成一个唯一的数字。这对于用户不需要手动输入且需保证唯一性的ID非常有用。自增字段通常是整数类型，每当向表中添加新行时，该字段的值会自动递增。
  3. 唯一约束（Unique）：唯一约束保证表中一列的所有值都是唯一的。这意味着在该列中，两行不能有相同的值。一个表可以有多个唯一约束，但唯一约束允许含有空值（NULL），并且每个唯一约束可以包含多个列。
  4. 非空约束（NOT NULL）：非空约束确保列中的值不能为NULL。这适用于那些必须有实际值的场合，确保数据的完整性。
  5. 默认约束（Default）：默认约束为列定义一个默认值。当插入记录时，如果没有为该列提供值，就会使用默认值。这对于定义某些列的典型值非常有用，例如，可以为布尔字段指定默认值false。
  6. 外键约束（Foreign Key）：外键约束用于建立两个表之间的关系，确保关系的一致性。外键在一个表中的字段（或多个字段）上定义，用于指向另一个表的主键。这保证了在子表中不能添加不存在于父表主键列中的值。如果尝试插入或更新不存在的值，或者删除在子表中仍被引用的父表中的记录，操作会失败并抛出异常。

案例

1
2
#查看所有的数据库 
SHOW DATABASES;
1
2
#删除数据库 
DROP DATABASE DEMO;
1
2
#创建数据库 
CREATE DATABASE DEMO;
1
2
#选择数据库 
USE DEMO;
1
2
3
4
5
6
#创建数据表
CREATE TABLE class(
    id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(30),
    class_id INT UNSIGNED DEFAULT 101
);
  1. id: 该字段为表的主键，数据类型为整数(INT)。使用了AUTO_INCREMENT属性，意味着每次向表中插入新行时，MySQL会自动为这个字段生成一个唯一的递增数字。这样做确保了表中每条记录的id都是唯一的，便于快速检索和引用。
  2. class_name: 此字段用于存储班级或课程的名称，数据类型为变长字符串(VARCHAR)，最大长度为30个字符。这允许存储各种班级名称，从而为用户提供灵活的命名选项。
  3. class_id: 该字段用于表示班级或课程的唯一标识符，数据类型为无符号整数(INT UNSIGNED)，默认值为101。使用无符号整数意味着这个字段只能存储正数或零，适用于标识符这样的数据。默认值101意味着，如果在插入记录时没有指定class_id的值，它将自动设为101。
1
2
#查看表结构
DESC class;
1
2
3
4
5
6
7
8
9
10
 #创建数据表
CREATE TABLE `学生表`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    stu_name VARCHAR(30),
    age INT UNSIGNED DEFAULT 18,
    `身高` DECIMAL(5,2),
    `性别` ENUM('男', '女', '保密'),
    class_id INT UNSIGNED DEFAULT 101,
    date_id DATE
);
  1. id: 这是表的主键字段，数据类型为整数(INT)。该字段使用了AUTO_INCREMENT属性，意味着每当向表中插入新行时，MySQL会自动为这个字段生成一个唯一的递增数字，确保每条记录都能通过一个唯一标识符(id)来区分。这对于快速检索、更新或删除特定记录非常有用。
  2. stu_name: 存储学生名称的字段，数据类型为变长字符串(VARCHAR)，最大长度为30个字符。这意味着可以存储最多30个字符的学生名字。
  3. age: 存储学生年龄的字段，使用了无符号整数(INT UNSIGNED)类型，且默认值为18。无符号意味着这个字段只能存储正数或零，适用于年龄这种不会有负值的数据。
  4. 身高: 存储学生身高的字段，使用了十进制数(DECIMAL)类型，最大值为999.99（因为定义为(5,2)，意味着总共5位数字，其中2位为小数）。这种类型适用于需要精确表示的数值，如身高。
  5. 性别: 用于存储学生性别的字段，数据类型为枚举(ENUM)，只允许三个值：'男'、'女'或'保密'。枚举类型限制了字段可能的值，确保数据的准确性和一致性。
  6. class_id: 表示学生所在班级的ID，使用了无符号整数(INT UNSIGNED)类型，并且默认值为101。这个字段可能用于关联到另一张表（如class表），用于存储班级相关信息。
  7. date_id: 存储日期的字段，数据类型为DATE。这可能用于记录学生入学日期或其他重要日期。DATE类型的格式为YYYY-MM-DD。
1
2
3
#删除表
DROP TABLE class;
DROP TABLE `学生表`;

字段操作

添加新的字段

  • 基本添加: 使用ADD关键字向表中添加新字段。基本语法如下:

    1
    ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件];

  • 在开头添加字段: 如果需要将新字段添加到表的开始位置，使用FIRST关键字。语法如下:

    1
    ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

  • 在中间添加字段: 要在已存在的字段之后添加新字段，使用AFTER关键字。语法如下:

    1
    ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;

修改字段属性/数值类型

  • 修改字段类型: 使用MODIFY关键字修改字段的数据类型或约束。语法如下:
    1
    ALTER TABLE <表名> MODIFY <字段名> <新数据类型> [新约束条件];

修改字段名称

  • 修改字段名称和类型: 使用CHANGE关键字同时更改字段的名称和数据类型。语法如下:
    1
    ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型> [新约束条件];

删除字段

  • 删除字段: 使用DROP关键字从表中删除字段。语法如下:
    1
    ALTER TABLE <表名> DROP <字段名>;

数据操作

添加表数据

  • 基本插入: 使用INSERT INTO关键字向表中添加数据。基本语法如下:

    1
    INSERT INTO 表名 VALUES (值1, 值2, 值3...);

  • 批量插入示例: 向students表中插入多条记录。示例如下:

    1
    2
    3
    4
    INSERT INTO students VALUES
    (1, '张三', 18, 1.82, '男', 001, '2019-09-01'),
    ...
    (17, '熊二', 21, 1.90, '男', 002, '2020-09-01');

修改数据

  • 基本更新: 使用UPDATE关键字更新表中的数据。基本语法如下:
    1
    UPDATE 数据表名 SET 字段1=新值, 字段2=新值 [WHERE 条件];

删除数据

  • 物理删除: 使用DELETE FROM关键字从表中删除数据。基本语法如下:

    1
    DELETE FROM 表名 [WHERE 条件];

  • 逻辑删除: 通过添加一个字段并改变其值来隐藏数据，而不是物理删除。这样，数据仍然存在于表中，但在查询时不会显示。示例如下:

    1
    ALTER TABLE 表名 ADD 字段 INT DEFAULT 0;

案例

  1. 将id为3的年龄修改为22并且性别为女:

    1
    UPDATE students SET age=22, 性别='女' WHERE id=3;

  2. 将张三的名字改成怪兽:

    1
    UPDATE students SET stu_name='怪兽' WHERE stu_name='张三';

  3. 将女生的名字都修改为rose:

    1
    UPDATE students SET stu_name='rose' WHERE 性别='女';

  4. 男生的名字修改为jake:

    1
    UPDATE students SET stu_name='jake' WHERE 性别='男';

基础查询

查询指定字段

  • 查询所有字段: 使用SELECT * FROM 表名;。*代表所有字段，可以根据需要替换为特定的字段名。

查询指定字段并重命名 (AS)

  • 字段重命名: 使用AS关键字给查询结果中的列赋予一个新的名称。语法如下:
    1
    SELECT 列名1 AS '新列名1', 列名2 AS '新列名2' FROM 表名;

查询指定字段并去重 (DISTINCT)

  • 去重查询: 使用DISTINCT关键字去除查询结果中的重复项。语法如下:

    1
    SELECT DISTINCT 列名 FROM 表名;

行条件查询 (WHERE)

  • 条件查询: 使用WHERE子句过滤符合特定条件的记录。语法如下:
    1
    SELECT * FROM 表名 WHERE 条件;

运算符

常见的SQL运算符

  1. 算术运算符:

    • + (加): 对两个数值进行加法运算。
    • - (减): 对两个数值进行减法运算或表示一个负数。
    • * (乘): 对两个数值进行乘法运算。
    • / (除): 对两个数值进行除法运算。
    • % (模): 返回两个数相除后的余数。

  2. 比较运算符:

    • = (等于): 检查两个表达式的值是否相等。
    • != 或 <> (不等于): 检查两个表达式的值是否不相等。
    • > (大于): 检查左边表达式的值是否大于右边的值。
    • < (小于): 检查左边表达式的值是否小于右边的值。
    • >= (大于等于): 检查左边表达式的值是否大于或等于右边的值。
    • <= (小于等于): 检查左边表达式的值是否小于或等于右边的值。

  3. 逻辑运算符:

    • AND: 如果所有给定的条件都为真，则返回真。
    • OR: 如果任一给定的条件为真，则返回真。
    • NOT: 如果给定的条件为假，则返回真。

  4. 位运算符:

    • &: 按位与操作。
    • |: 按位或操作。
    • ^: 按位异或操作。
    • ~: 按位非操作。

  5. 赋值运算符:

    • =: 将表达式的值赋给变量或表的列。

使用场景示例

  • 算术运算符:

    1
    SELECT salary, salary * 0.10 AS bonus FROM employees;

  • 比较运算符:

    1
    SELECT name, age FROM students WHERE age >= 18;

  • 逻辑运算符:

    1
    SELECT * FROM orders WHERE amount > 100 AND status = 'Shipped';

  • 位运算符:

    1
    SELECT flags, flags | 1 FROM permissions;

注意

  1. 优先级: SQL运算符有不同的优先级，比如算术运算符的优先级高于比较运算符，比较运算符的优先级又高于逻辑运算符。使用括号()可以改变执行顺序。

  2. 类型兼容性: 在进行运算时，确保参与运算的数据类型兼容，否则可能会导致错误或不预期的结果。

  3. 性能考虑: 在使用逻辑运算符时，尤其是在大型数据集上，应注意其对查询性能的影响。合理使用索引和优化查询条件可以显著提高性能。

  4. NULL值的处理: SQL中的NULL表示一个未知值。在使用比较运算符时，任何包含NULL的比较操作都会返回NULL。使用IS NULL或IS NOT NULL来检查NULL值。

模糊查询 (LIKE)

  • 模糊匹配: 使用LIKE关键字进行模糊查询，%表示任意多个字符，_表示单个任意字符。例如:
    1
    2
    3
    SELECT 列名 FROM 表名 WHERE 列名 LIKE 'a%';  -- 以a开头的
    SELECT 列名 FROM 表名 WHERE 列名 LIKE '%a%';  -- 包含a的
    SELECT 列名 FROM 表名 WHERE 列名 LIKE '_a';   -- 以任意字符开头，后接a的

非连续查询 (IN)

  • 指定范围查询: 使用IN关键字查询列值在指定列表中的记录。语法如下:
    1
    SELECT * FROM 表名 WHERE 列名 IN (值1, 值2, 值3);

连续查询 (BETWEEN…AND…)

  • 范围查询: 使用BETWEEN...AND...查询列值在某个连续范围内的记录。语法如下:
    1
    SELECT * FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2;

空判断 (IS NULL, IS NOT NULL)

  • 空值查询: 使用IS NULL或IS NOT NULL检查列值是否为空。语法如下:
    1
    2
    SELECT * FROM 表名 WHERE 列名 IS NULL;
    SELECT * FROM 表名 WHERE 列名 IS NOT NULL;

案例

查询出所有女生:

1
SELECT * FROM 表名 WHERE 性别 = '女';

查询出年龄为18和20和22的学生:

1
SELECT * FROM 表名 WHERE 年龄 IN (18, 20, 22);

查询年龄在20到24的学生:

1
SELECT * FROM 表名 WHERE 年龄 BETWEEN 20 AND 24;

查询姓王的学生:

1
SELECT * FROM 表名 WHERE 名字 LIKE '王%';

查询出所有男生的信息:

1
SELECT * FROM 表名 WHERE 性别 = '男';

查询名字带有”王”字的学生:

1
SELECT * FROM 表名 WHERE 名字 LIKE '%王%';

字符函数

字符函数在SQL中用于对字符串类型的数据进行操作，包括获取长度、拼接、拆分、大小写转换、搜索、裁剪和替换等。以下是一些常用的MySQL字符函数及其应用实例。

1. 获取字符串长度: LENGTH()

  • 作用: 返回字符串的字节数。
  • 示例:
    1
    SELECT LENGTH('linli');

2. 拼接字符串: CONCAT()

  • 作用: 将多个字符串值连接成一个字符串。
  • 示例:
    1
    SELECT CONCAT(字段1, ' ', 字段2) FROM 表名;

3. 拆分字符串: SUBSTRING_INDEX()

  • 作用: 根据指定的分隔符拆分字符串，并返回请求的部分。
  • 示例:
    1
    2
    3
    4
    SELECT 
      SUBSTRING_INDEX(字段, '分隔符', 1) AS 新字段名1,
      SUBSTRING_INDEX(字段, '分隔符', -1) AS 新字段名2
    FROM 表名;

4. 字符串转大写: UPPER()

  • 作用: 将字符串转换为大写字母。
  • 示例:
    1
    2
    3
    SELECT UPPER('linli');
    SELECT UPPER(字段) FROM 表名;
    UPDATE 表名 SET 字段 = UPPER(字段);

5. 字符串转小写: LOWER()

  • 作用: 将字符串转换为小写字母。
  • 示例:
    1
    2
    3
    SELECT LOWER('linli');
    SELECT LOWER(字段) FROM 表名;
    UPDATE 表名 SET 字段 = LOWER(字段);

6. 字符串中字符首次出现的位置: INSTR()

  • 作用: 返回子字符串在字符串中第一次出现的位置。
  • 示例:
    1
    SELECT INSTR('abcdf', 'c');

7. 去除字符串两端的空白: TRIM()

  • 作用: 去除字符串两端的空格或指定的字符。
  • 示例:
    1
    2
    SELECT TRIM(' abc ');
    SELECT TRIM('a' FROM 'aaaaaaaa林里aaaa');

8. 字符串左/右填充: LPAD()/RPAD()

  • 作用: 使用指定的字符串对原字符串进行左填充或右填充到指定长度。
  • 示例:
    1
    2
    SELECT LPAD('林里', 10, 'a');
    SELECT RPAD('林里', 10, 'a');

9. 替换字符串中的所有指定子串: REPLACE()

  • 作用: 在字符串中替换所有出现的指定子串。
  • 示例:
    1
    2
    SELECT REPLACE('周芷若张无忌爱上周芷若', '周芷若', '赵敏');
    UPDATE 表名 SET 字段 = REPLACE(字段, '可替换数值', '待替换数值') WHERE id = 2;

数学函数

MySQL提供了多种数学函数，用于处理数值数据。这些函数可以执行从基本算术到复杂数学计算的各种操作。

1. 四舍五入: ROUND()

  • 作用: 对数值进行四舍五入到指定的小数位数。
  • 示例:
    1
    SELECT ROUND(数值/字段, x) FROM 表名; -- x为要保留的小数位数，当x为0时保留整数位

2. 向上取整: CEIL()

  • 作用: 向上取整，即取大于等于该数的最小整数。
  • 示例:
    1
    2
    SELECT CEIL(1.02);
    SELECT CEIL(-1.59);

3. 向下取整: FLOOR()

  • 作用: 向下取整，即取小于等于该数的最大整数。
  • 示例:
    1
    2
    SELECT FLOOR(1.02);
    SELECT FLOOR(-1.02);

4. 截断: TRUNCATE()

  • 作用: 将数值截断到指定的小数位数，不进行四舍五入。
  • 示例:
    1
    2
    SELECT TRUNCATE(1.699999, 2);
    SELECT TRUNCATE(69999.999999, 5);

5. 取余: MOD()

  • 作用: 返回两个数相除的余数。
  • 示例:
    1
    SELECT MOD(10, 3);

6. 生成随机数: RAND()

  • 作用: 生成一个0到1之间的随机浮点数。
  • 示例:
    1
    SELECT RAND();

案例

生成随机整数:

  • 生成1到10之间的随机整数:
    1
    SELECT FLOOR(RAND() * 10) + 1;
  • 生成-10到10之间的随机整数:
    1
    SELECT FLOOR(RAND() * 21) - 10;

日期函数

MySQL的日期函数允许对日期和时间值进行操作，包括提取日期部分、转换日期格式等。

1. 获取当前日期和时间: NOW()

  • 作用: 返回当前的日期和时间。
  • 示例:
    1
    SELECT NOW();

2. 获取当前日期: CURDATE()

  • 作用: 返回当前的日期。
  • 示例:
    1
    SELECT CURDATE();

3. 获取当前时间: CURTIME()

  • 作用: 返回当前的时间。
  • 示例:
    1
    SELECT CURTIME();

4. 提取日期部分

  • 获取年份: YEAR()
  • 获取月份: MONTH()
  • 获取日: DAY()
  • 获取星期几: DAYNAME()
  • 示例:
    1
    SELECT YEAR(字段), MONTH(字段), DAY(字段), DAYNAME(字段) FROM 表名;

5. 日期格式转换: STR_TO_DATE()

  • 作用: 将字符串转换为日期格式。

  • 示例:

    1
    2
    SELECT STR_TO_DATE('1998-3-2', '%Y-%c-%d');
    SELECT * FROM 表名 WHERE 日期列 = STR_TO_DATE('4-3-1992', '%m-%d-%Y');

6. 日期差异函数: DATEDIFF()

DATEDIFF()函数在MySQL中用来计算两个日期之间的天数差异。

示例:

  • 计算两个指定日期之间的天数差:

    1
    SELECT DATEDIFF('2022-12-31', '2022-12-01');

  • 计算某个列中的日期与另一个日期之间的天数差，并为结果指定一个新列名:

    1
    SELECT id, DATEDIFF(日期列1, 日期列2) AS 新列名 FROM 表名;

  • 计算与当前系统日期的天数差:

    1
    SELECT DATEDIFF('2022-12-31', CURDATE());

聚合函数

聚合函数用于执行对一组值的计算，并返回单个值。在MySQL中，常用的聚合函数包括COUNT(), MAX(), MIN(), SUM(), 和AVG()。

示例:

  • 总数 (COUNT()): 计算表中符合条件的行数。

    1
    2
    SELECT COUNT(*) FROM 表名;
    SELECT COUNT(字段) FROM 表名 WHERE 条件;

  • 最大值 (MAX()): 查找字段的最大值。

    1
    SELECT MAX(字段) FROM 表名;

  • 最小值 (MIN()): 查找字段的最小值。

    1
    SELECT MIN(字段) FROM 表名;

  • 求和 (SUM()): 计算字段值的总和。

    1
    SELECT SUM(字段) FROM 表名;

  • 平均值 (AVG()): 计算字段值的平均值。

    1
    SELECT AVG(字段) FROM 表名;

案例

  • 查询女生的人数:

    1
    SELECT COUNT(*) FROM 表名 WHERE 性别 = '女';

  • 查询年龄为18岁的人数:

    1
    SELECT COUNT(*) FROM 表名 WHERE 年龄 = 18;

  • 查询年纪最大的男生:

    1
    SELECT MAX(年龄) FROM 表名 WHERE 性别 = '男';

  • 查询女生的最大id:

    1
    SELECT MAX(id) FROM 表名 WHERE 性别 = '女';

  • 查询年纪最小的女生:

    1
    SELECT MIN(年龄) FROM 表名 WHERE 性别 = '女';

  • 查询男生的最小id:

    1
    SELECT MIN(id) FROM 表名 WHERE 性别 = '男';

  • 查询女生的年龄和:

    1
    SELECT SUM(年龄) FROM 表名 WHERE 性别 = '女';

  • 查询男生的id和:

    1
    SELECT SUM(id) FROM 表名 WHERE 性别 = '男';

  • 查询男生的平均年龄，保留两位小数:

    1
    SELECT ROUND(AVG(年龄), 2) FROM 表名 WHERE 性别 = '男';

排序和分组

排序: ORDER BY

ORDER BY子句用于根据一个或多个列的数值大小或字母顺序对查询结果进行排序，默认为升序排序。

  • 基本排序:

    1
    SELECT 字段1, 字段2 FROM 表名 ORDER BY 字段2;

  • 升序排序 (ASC):

    1
    SELECT 字段 FROM 表名 ORDER BY 字段 ASC;

  • 降序排序 (DESC):

    1
    SELECT 字段 FROM 表名 ORDER BY 字段 DESC;

  • 显示指定行数 (LIMIT):

    1
    SELECT 字段 FROM 表名 LIMIT 1, 4; -- 显示从第二行开始的四行数据

分组: GROUP BY

GROUP BY子句按照对应的字段进行分组，查询结果会根据指定的列进行分组，相同值的行将被合并到同一个分组中。

  • 基本分组:

    1
    SELECT 字段1, 字段2 FROM 表名 GROUP BY 字段2;

  • 分组并计数:

    1
    SELECT COUNT(*), 字段1, 字段2 FROM 表名 GROUP BY 字段2;

  • 分组并使用聚合函数:

    1
    SELECT COUNT(*), GROUP_CONCAT(字段1), 字段2 FROM 表名 GROUP BY 字段2;

  • 分组汇总 (WITH ROLLUP):

    1
    SELECT 字段 FROM 表名 GROUP BY 字段 WITH ROLLUP;

条件筛选: HAVING和WHERE

  • WHERE: 在分组前对数据进行过滤。
  • HAVING: 在分组后对分组的结果进行条件筛选。

案例

  • 如果男生或者女生的平均年龄超过18岁就输出他们的平均年龄和性别:

    1
    SELECT 性别, AVG(年龄) AS 平均年龄 FROM 表名 GROUP BY 性别 HAVING AVG(年龄) > 18;

  • 查询年龄在18-26岁之间的男生，年龄从小到大排序:

    1
    SELECT * FROM 表名 WHERE 性别 = '男' AND 年龄 BETWEEN 18 AND 26 ORDER BY 年龄 ASC;

  • 查询年龄从18到20的女生，id从低到高排序:

    1
    SELECT * FROM 表名 WHERE 性别 = '女' AND 年龄 BETWEEN 18 AND 20 ORDER BY id ASC;

子查询

子查询是嵌套在另一个查询中的查询，用于提供外部查询使用的条件或数据。

  • 示例:
    1
    SELECT stuname, height FROM students WHERE height = (SELECT MAX(height) FROM students WHERE gender = 1);

在这个示例中，(SELECT MAX(height) FROM students WHERE gender = 1)是一个子查询，它先找出所有男生中身高最高的值，然后外部查询使用这个结果来找出具有该身高的学生信息。

表连接

在数据库中，表连接是用来组合来自两个或多个表的行的过程。根据连接的类型，可以选择不同的方法来组合这些行。

内连接 (INNER JOIN)

  • 定义: 内连接返回两个表中满足连接条件的记录。只有在两个表中都有匹配时，记录才会被包含在结果集中。
  • 示例:
    1
    2
    3
    SELECT 表1.字段1, 表1.字段2, 表2.字段1 
    FROM 表1 
    INNER JOIN 表2 ON 表1.字段1 = 表2.字段2;

外连接

外连接返回一个表中的所有记录和另一个表中的匹配记录。如果某侧表中的行在另一侧表中没有匹配，结果集中这些行的缺失侧将包含NULL。

  • 左外连接 (LEFT JOIN):

    • 定义: 返回左表的所有记录和右表中符合连接条件的记录。
    • 示例:
      1
      2
      3
      SELECT 表1.字段1, 表1.字段2, 表2.字段1 
      FROM 表1 
      LEFT JOIN 表2 ON 表1.字段1 = 表2.字段2;

  • 右外连接 (RIGHT JOIN):

    • 定义: 返回右表的所有记录和左表中符合连接条件的记录。
    • 示例:
      1
      2
      3
      SELECT 表1.字段1, 表1.字段2, 表2.字段1 
      FROM 表1 
      RIGHT JOIN 表2 ON 表1.字段1 = 表2.字段2;

表连接的区别

  • 内连接 (INNER JOIN) 只返回两个表中关联字段相匹配的行。
  • 外连接 (LEFT JOIN, RIGHT JOIN) 除了返回匹配的行外，还会返回左表或右表中未匹配的行，并用NULL填充未匹配的另一侧表的列。

案例

假设有一个students表和class表，students表中有学生的信息，包括他们报名的班级，而class表包含班级信息。

统计两个班各有多少人报名

1
2
3
4
SELECT class.class_name, COUNT(students.id) AS enrolment_count
FROM class
LEFT JOIN students ON class.id = students.class_id
GROUP BY class.class_name;

统计出他们的在校时间

这个查询需要有学生的入学时间和当前日期来计算在校时间，假设students表中有一个admission_date字段：

1
2
3
SELECT class.class_name, students.id, DATEDIFF(CURDATE(), students.admission_date) AS days_in_school
FROM class
JOIN students ON class.id = students.class_id;

查询出报名了大数据分析基础班的女生人数

假设students表中有gender字段表示性别：

1
2
3
4
SELECT COUNT(*) AS female_students_count
FROM students
JOIN class ON students.class_id = class.id
WHERE class.class_name = '大数据分析基础班' AND students.gender = '女';

查询报名了大数据分析全栈在18岁到20岁之间有多少人

假设students表中有age字段表示年龄：

1
2
3
4
SELECT COUNT(*) AS students_count
FROM students
JOIN class ON students.class_id = class.id
WHERE class.class_name = '大数据分析全栈' AND students.age BETWEEN 18 AND 20;

多表连接与高级数据库操作

1. 多表连接

在数据库设计中，多表连接是一种常用的数据查询方式，允许我们从不同的表中提取并合并信息。通过JOIN语句，我们可以实现内连接、左连接、右连接等，以满足不同的数据合并需求。

2. 临时表

  • 定义: MySQL中的临时表用于存储会话或事务中的临时数据。这些表仅在创建它们的数据库会话中可见，并在会话结束时自动删除。
  • 应用场景: 临时表适用于存储查询过程中的临时结果，这对于复杂的数据处理操作特别有用。
  • 创建临时表:
    1
    2
    3
    4
    5
    CREATE TEMPORARY TABLE temp_table_name (
      column1 datatype,
      column2 datatype,
      ...
    );
  • 使用示例:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- 创建临时表
    CREATE TEMPORARY TABLE temp_orders AS
    SELECT * FROM orders WHERE order_date >= '2023-01-01';

    -- 查询临时表
    SELECT * FROM temp_orders;

    -- 插入数据到临时表
    INSERT INTO temp_orders (order_id, customer_id, order_date)
    VALUES (1001, 1, '2023-01-05');

3. 流程控制函数

流程控制函数在SQL查询中用于根据条件返回不同的结果，这提高了SQL语句的灵活性和表达能力。

  • IF函数:

    • 语法: IF(condition, true_value, false_value)
    • 示例: SELECT IF(score >= 60, 'Passed', 'Failed') AS result FROM grades;

  • CASE表达式:

    • 语法:
      1
      2
      3
      4
      5
      6
      7
      SELECT column_name,
             CASE 
                 WHEN condition1 THEN 'result1'
                 WHEN condition2 THEN 'result2'
                 ELSE 'resultN'
             END AS alias_name
      FROM table_name;
    • 示例:
      1
      2
      3
      4
      5
      6
      7
      8
      SELECT name, 
             CASE 
                 WHEN score >= 90 THEN 'Excellent'
                 WHEN score >= 80 THEN 'Good'
                 WHEN score >= 60 THEN 'Pass'
                 ELSE 'Fail'
             END AS result
      FROM scores;

4. MySQL正则表达式

MySQL正则表达式提供了一种强大的工具来进行复杂的模式匹配和数据检索。通过使用正则表达式，你可以在SQL查询中执行高级文本匹配，从而筛选出满足特定模式的数据行。这在处理文本数据时尤其有用，比如搜索日志、验证数据格式或提取特定格式的信息等场景。

基本使用

在MySQL中，REGEXP或RLIKE运算符用于正则表达式匹配，检查左侧的字符串是否匹配右侧的正则表达式模式。

  • 语法:
    1
    SELECT column FROM table WHERE column REGEXP pattern;

常用模式

  • ^: 匹配输入字符串的开始位置。
  • $: 匹配输入字符串的结束位置。
  • .: 匹配除“\n”之外的任何单个字符。
  • *: 匹配前面的字符零次或多次。
  • +: 匹配前面的字符一次或多次。
  • ?: 匹配前面的字符零次或一次。
  • [...]: 匹配方括号内的任意字符。
  • |: 选择，匹配|前后的任一模式。
  • (..): 分组标记，将()内的作为一个整体进行处理。

示例

  • 匹配以特定字符开始的字符串:

    1
    SELECT * FROM table WHERE column REGEXP '^abc';

  • 匹配包含特定字符的字符串:

    1
    SELECT * FROM table WHERE column REGEXP 'abc';

  • 匹配以特定字符结束的字符串:

    1
    SELECT * FROM table WHERE column REGEXP 'abc$';

  • 匹配任意位置包含数字的字符串:

    1
    SELECT * FROM table WHERE column REGEXP '[0-9]';

  • 匹配包含两个连续相同字符的字符串:

    1
    SELECT * FROM table WHERE column REGEXP '(.)\\1';

高级应用

  • 分组和选择:
    使用分组()和选择|可以构造更复杂的匹配模式。例如，匹配包含abc或def的字符串：

    1
    SELECT * FROM table WHERE column REGEXP 'abc|def';

  • 字符类:
    使用字符类[...]可以匹配指定范围内的字符。例如，匹配任何小写字母：

    1
    SELECT * FROM table WHERE column REGEXP '[a-z]';

MySQL的正则表达式功能提供了灵活而强大的数据匹配能力，使得对于复杂文本数据的查询和分析变得更加高效和精确。通过合理利用正则表达式，可以极大地提升数据处理的能力和效率。

5. UNION操作

  • 定义: UNION操作符用于合并两个或多个SELECT语句的结果集，并默认去除重复行。

  • 语法:

    1
    2
    3
    SELECT column_name(s) FROM table1
    UNION
    SELECT column_name(s) FROM table2;

  • 示例:

    1
    2
    3
    SELECT student_name FROM students
    UNION
    SELECT teacher_name FROM teachers;

  • UNION ALL: 如果希望保留重复行，可以使用UNION ALL。

    1
    2
    3
    SELECT column_name FROM table1
    UNION ALL
    SELECT column_name FROM table2;

这些高级功能和操作提供了强大的工具，用于管理和查询数据库中的数据，允许进行复杂的数据分析和处理。通过灵活使用这些工具，可以有效地解决多种数据处理的需求。

案例

围绕一个学校的数据库模型，涉及学生(Student)、课程(Course)、教师(Teacher)和成绩(Scores)四个主要表。

数据库表结构

  1. 学生表(Student)

    • S (VARCHAR(10)): 学生编号
    • Sname (VARCHAR(10)): 学生姓名
    • Sage (DATETIME): 学生出生日期
    • Ssex (VARCHAR(10)): 学生性别

  2. 课程表(Course)

    • C (VARCHAR(10)): 课程编号
    • Cname (VARCHAR(10)): 课程名称
    • T (VARCHAR(10)): 教师编号

  3. 教师表(Teacher)

    • T (VARCHAR(10)): 教师编号
    • Tname (VARCHAR(10)): 教师姓名

  4. 成绩表(SC)

    • S (VARCHAR(10)): 学生编号
    • C (VARCHAR(10)): 课程编号
    • score (DECIMAL(18,1)): 成绩

查询需求及SQL实现

  1. 查询”01”课程比”02”课程成绩高的学生的信息及课程分数

    思路：使用自连接查询SC表，比较同一学生的两门课成绩。

    1
    2
    3
    4
    5
    SELECT s1.S, Sname, s1.score AS '01_Score', s2.score AS '02_Score'
    FROM SC s1
    JOIN SC s2 ON s1.S = s2.S AND s1.C = '01' AND s2.C = '02'
    JOIN Student ON Student.S = s1.S
    WHERE s1.score > s2.score;

  2. 查询同时选修01和02课程的情况

    思路：查找同时在SC表中存在课程编号为”01”和”02”记录的学生。

    1
    2
    3
    4
    SELECT DISTINCT s.S, Sname
    FROM Student s
    JOIN SC sc1 ON s.S = sc1.S AND sc1.C = '01'
    JOIN SC sc2 ON s.S = sc2.S AND sc2.C = '02';

  3. 查询存在”01”课程但可能不存在”02”课程的情况

    思路：使用左连接确保”01”课程存在，而”02”课程可能不存在。

    1
    2
    3
    4
    5
    SELECT DISTINCT s.S, Sname
    FROM Student s
    JOIN SC sc1 ON s.S = sc1.S AND sc1.C = '01'
    LEFT JOIN SC sc2 ON s.S = sc2.S AND sc2.C = '02'
    WHERE sc2.S IS NULL;

  4. 查询不存在”01”课程但存在”02”课程的情况

    思路：反转第3题的逻辑，使用左连接确保”02”课程存在，而”01”课程不存在。

    1
    2
    3
    4
    5
    SELECT DISTINCT s.S, Sname
    FROM Student s
    JOIN SC sc2 ON s.S = sc2.S AND sc2.C = '02'
    LEFT JOIN SC sc1 ON s.S = sc1.S AND sc1.C = '01'
    WHERE sc1.S IS NULL;

  5. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

    思路：对SC表进行分组聚合查询，计算每个学生的平均成绩。

    1
    2
    3
    4
    5
    SELECT s.S, Sname, AVG(score) AS Avg_Score
    FROM Student s
    JOIN SC ON s.S = SC.S
    GROUP BY s.S, Sname
    HAVING AVG(score) >= 60;

  6. 查询SC表存在成绩的学生信息

    思路：查询SC表中有成绩记录的学生信息。

    1
    2
    3
    SELECT DISTINCT Student.S, Sname
    FROM Student
    JOIN SC ON Student.S = SC.S;

  7. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

    思路：对SC表进行分组聚合查询，同时计算选课总数和成绩总和。

    1
    2
    3
    4
    SELECT s.S, Sname, COUNT(SC.C) AS Course_Count, SUM(SC.score) AS Total_Score
    FROM Student s
    LEFT JOIN SC ON s.S = SC.S
    GROUP BY s.S, Sname;

  8. 查没有成绩的学生信息

    思路：查询Student表中不存在于SC表中的学生信息。

    1
    2
    3
    SELECT S, Sname
    FROM Student
    WHERE S NOT IN (SELECT DISTINCT S FROM SC);

  9. 查询「李」姓老师的数量

    思路：根据教师姓名筛选并计数。

    1
    2
    3
    SELECT COUNT(*) AS Li_Teachers
    FROM Teacher
    WHERE Tname LIKE '李%';

  10. 查询学过「张三」老师授课的同学的信息

    思路：先找到「张三」老师的课程，再查询选修这些课程的学生信息。

    1
    2
    3
    4
    5
    6
    SELECT DISTINCT Student.S, Sname
    FROM Student
    JOIN SC ON Student.S = SC.S
    JOIN Course ON SC.C = Course.C
    JOIN Teacher ON Course.T = Teacher.T
    WHERE Teacher.Tname = '张三';

  11. 查询没有学全所有课程的同学的信息

    思路：需要比较学生选修的课程数量和课程表中课程总数。

    1
    2
    3
    4
    5
    SELECT s.S, Sname
    FROM Student s
    JOIN SC ON s.S = SC.S
    GROUP BY s.S, Sname
    HAVING COUNT(DISTINCT SC.C) < (SELECT COUNT(*) FROM Course);

  12. 查询至少有一门课与学号为”01”的同学所学相同的同学的信息

    思路：首先找出学号为”01”的同学所学的课程，然后查找至少选修了这些课程之一的其他同学。

    1
    2
    3
    4
    SELECT DISTINCT Student.S, Sname
    FROM Student
    JOIN SC ON Student.S = SC.S
    WHERE SC.C IN (SELECT SC.C FROM SC WHERE S = '01') AND Student.S != '01';

项目

本项目通过分析广州的wassup网店在12月份的用户交易情况和产品情况，目的是为了提供数据支持，帮助店主更好地经营小店。通过对产品情况表product_tb和销售数据表sales_tb的查询分析，以下是从五个关键方面的详细分析和查询实现。

产品情况表 (product_tb)

  • 表结构：
    • item_id (VARCHAR(30)): 商品的具体货号。
    • style_id (VARCHAR(30)): 商品的款号。
    • tag_price (INT): 商品的标签价格。
    • inventory (INT): 商品的库存量。
  • 示例数据：
    • 商品范围从A001到C002，覆盖三个不同的款式（A、B、C）。
    • 每个款式下有不同的货号，价格和库存量各不相同。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE product_tb(item_id VARCHAR(30),
                        style_id VARCHAR(30),
                        tag_price INT,
                        inventory INT);
                        
INSERT INTO product_tb VALUES
('A001','A',100,20),
('A002','A',120,30),
('A003','A',200,15),
('B001','B',130,18),
('B002','B',150,22),
('B003','B',125,10),
('B004','B',155,12),
('C001','C',260,25),
('C002','C',280,18);

销售数据表 (sales_tb)

  • 表结构：
    • sales_date (DATE): 销售日期。
    • user_id (INT): 用户编号。
    • item_id (VARCHAR(30)): 销售的商品货号。
    • sales_num (INT): 销售数量。
    • sales_price (INT): 销售结算金额。
  • 示例数据：
    • 销售记录从2023-12-01到2023-12-06，涵盖了多个用户对不同货号商品的购买行为。
    • 销售数据包含销售数量和销售结算金额，可以通过这些数据分析商品的销售情况和用户购买偏好。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE sales_tb(sales_date DATE,
                      user_id INT,
                      item_id VARCHAR(30),
                      sales_num INT,
                      sales_price INT);
INSERT INTO sales_tb VALUES
('2023-12-01','1','A001','1',90),
('2023-12-01','2','A002','2',220),
('2023-12-01','2','B001','1',120),
('2023-12-02','3','C001','2',500),
('2023-12-02','4','B001','1',120),
('2023-12-03','5','C001','1',240),
('2023-12-03','6','C002','1',270),
('2023-12-04','7','A003','1',180),
('2023-12-04','8','B002','1',140),
('2023-12-04','9','B001','1',125),
('2023-12-04','10','B003','1',120),
('2023-12-05','10','B004','1',150),
('2023-12-05','10','A003','1',180),
('2023-12-06','11','B003','1',120),
('2023-12-06','10','B004','1',150);

问题1: 查询每款的SPU（货号）数量及排序

  • 目的：了解店铺的产品种类和每个款式下具体货号的数量。
  • SQL 查询：
    1
    2
    3
    4
    SELECT style_id, COUNT(item_id) AS SPU 
    FROM product_tb 
    GROUP BY style_id 
    ORDER BY COUNT(item_id) DESC;

问题2: 查询实际总销售额与客单价

  • 目的：分析用户的平均消费金额，以及总销售额。
  • SQL 查询：
    1
    2
    3
    4
    SELECT
      SUM(sales_price) AS `销售总额`,
      ROUND(SUM(sales_price)/COUNT(DISTINCT user_id), 2) AS `客单价`
    FROM sales_tb;

问题3: 查询折扣率

  • 目的：分析店铺的促销效果，了解整体和各款式、各货号的折扣情况。
  • 总的折扣率查询：
    1
    2
    3
    4
    5
    SELECT CONCAT(
      (1 - SUM(sales_price) / SUM(sales_tb.sales_num * product_tb.tag_price)) * 100, '%'
    ) AS `折扣率`
    FROM sales_tb
    JOIN product_tb ON sales_tb.item_id = product_tb.item_id;
  • 每个款号的折扣率查询：
    1
    2
    3
    4
    5
    6
    7
    SELECT product_tb.style_id, CONCAT(
      ((SUM(sales_tb.sales_num * product_tb.tag_price) - SUM(sales_tb.sales_price))
      / SUM(sales_tb.sales_num * product_tb.tag_price)) * 100, '%'
    ) AS `折扣率`
    FROM sales_tb
    JOIN product_tb ON sales_tb.item_id = product_tb.item_id
    GROUP BY product_tb.style_id;

问题4: 查询每款的动销率与售罄率

  • 目的：了解每款产品的销售情况，包括动销率和售罄率，以评估库存和销售效率。
  • SQL 查询：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT
       a.style_id,                                            
       ROUND(b.sum_num/(a.sum_inv-b.sum_num)*100, 2) AS 'pin_rate(%)',   
       ROUND(b.GMV/a.sum_pri*100, 2) AS 'sell-through_rate(%)'
    FROM
       (SELECT style_id, SUM(inventory) AS sum_inv, SUM(tag_price * inventory) AS sum_pri
        FROM product_tb
        GROUP BY style_id) AS a
    LEFT JOIN
       (SELECT LEFT(item_id, 1) AS style_id, SUM(sales_num) AS sum_num, SUM(sales_price) AS GMV
        FROM sales_tb
        GROUP BY style_id) AS b 
    ON a.style_id = b.style_id
    ORDER BY a.style_id;

问题5: 查询连续2天及以上在该店铺购物的用户及其对应的次数

  • 目的：分析用户消费频率与粘性，特别是连续购物行为的用户。
  • SQL 查询：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT
       user_id,
       COUNT(rn)
    FROM
      (SELECT
          DISTINCT user_id,
          sales_date,
          DENSE_RANK() OVER (PARTITION BY user_id ORDER BY sales_date) rn
       FROM sales_tb) AS a
    GROUP BY user_id, DATE_SUB(sales_date, INTERVAL rn DAY)
    HAVING COUNT(*) > 2;

以上分析从产品种类和数量、销售额和客单价、促销折扣率、产品动销率和售罄率、以及用户消费频率和粘性五个方面全面分析了wassup网店的12月份经营情况。这些数据分析可以为店铺

  • 标题: MySQL
  • 作者: Yiuhang Chan
  • 创建于 : 2019-03-15 11:02:13
  • 更新于 : 2024-02-28 18:49:38
  • 链接: https://www.yiuhangblog.com/2019/03/15/20190315认识mysql/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
评论
此页目录
MySQL
  1. 数据库
    1. 关系型数据库
    2. 非关系型数据库
  2. 什么是MySQL
    1. 形式结构
    2. 运行逻辑
  3. 语句类型
  4. 数据类型
    1. 数值类型
    2. 日期和时间类型
    3. 字符串类型
    4. 枚举与集合类型（Enumeration and Set Types）
    5. 空间数据类型（Spatial Data Types）
  5. 约束类型
  6. 案例
  7. 字段操作
    1. 添加新的字段
    2. 修改字段属性/数值类型
    3. 修改字段名称
    4. 删除字段
  8. 数据操作
    1. 添加表数据
    2. 修改数据
    3. 删除数据
  9. 案例
  10. 基础查询
    1. 查询指定字段
    2. 查询指定字段并重命名 (AS)
    3. 查询指定字段并去重 (DISTINCT)
    4. 行条件查询 (WHERE)
    5. 运算符
      1. 常见的SQL运算符
      2. 使用场景示例
    6. 模糊查询 (LIKE)
    7. 非连续查询 (IN)
    8. 连续查询 (BETWEEN…AND…)
    9. 空判断 (IS NULL, IS NOT NULL)
  11. 案例
  12. 字符函数
    1. 1. 获取字符串长度: LENGTH()
    2. 2. 拼接字符串: CONCAT()
    3. 3. 拆分字符串: SUBSTRING_INDEX()
    4. 4. 字符串转大写: UPPER()
    5. 5. 字符串转小写: LOWER()
    6. 6. 字符串中字符首次出现的位置: INSTR()
    7. 7. 去除字符串两端的空白: TRIM()
    8. 8. 字符串左/右填充: LPAD()/RPAD()
    9. 9. 替换字符串中的所有指定子串: REPLACE()
  13. 数学函数
    1. 1. 四舍五入: ROUND()
    2. 2. 向上取整: CEIL()
    3. 3. 向下取整: FLOOR()
    4. 4. 截断: TRUNCATE()
    5. 5. 取余: MOD()
    6. 6. 生成随机数: RAND()
  14. 案例
  15. 日期函数
    1. 1. 获取当前日期和时间: NOW()
    2. 2. 获取当前日期: CURDATE()
    3. 3. 获取当前时间: CURTIME()
    4. 4. 提取日期部分
    5. 5. 日期格式转换: STR_TO_DATE()
    6. 6. 日期差异函数: DATEDIFF()
  16. 聚合函数
  17. 案例
  18. 排序和分组
    1. 排序: ORDER BY
    2. 分组: GROUP BY
    3. 条件筛选: HAVING和WHERE
  19. 案例
  20. 子查询
  21. 表连接
    1. 内连接 (INNER JOIN)
    2. 外连接
  22. 表连接的区别
  23. 案例
    1. 统计两个班各有多少人报名
    2. 统计出他们的在校时间
    3. 查询出报名了大数据分析基础班的女生人数
    4. 查询报名了大数据分析全栈在18岁到20岁之间有多少人
  24. 多表连接与高级数据库操作
    1. 1. 多表连接
    2. 2. 临时表
    3. 3. 流程控制函数
    4. 4. MySQL正则表达式
      1. 基本使用
      2. 常用模式
      3. 示例
      4. 高级应用
    5. 5. UNION操作
  25. 案例
    1. 数据库表结构
    2. 查询需求及SQL实现
  26. 项目
    1. 产品情况表 (product_tb)
    2. 销售数据表 (sales_tb)
    3. 问题1: 查询每款的SPU（货号）数量及排序
    4. 问题2: 查询实际总销售额与客单价
    5. 问题3: 查询折扣率
    6. 问题4: 查询每款的动销率与售罄率
    7. 问题5: 查询连续2天及以上在该店铺购物的用户及其对应的次数