MySQL 基本语法讲解及示例（下）

🕗 发布于 2024-07-06 18:34 mysql 数据库

第六节：如何检索资料

在本节中，我们将介绍如何使用SQL语句检索数据库中的资料，具体包括选择特定列、排序、条件过滤以及组合排序等操作。我们以一个名为student的表格为例，演示不同的检索方法。

初始表格 `student`

student_id	name	major	score
1	小白	英语	50
2	小黄	生物	90
3	小绿	历史	70
4	小蓝	英语	80
5	小黑	化学	20

检索目标表格

1. 选取目标列 `name`, `major`

SELECT `name`, `major` FROM `student`;

结果：

name	major
小白	英语
小黄	生物
小绿	历史
小蓝	英语
小黑	化学

2. 依照特定列 `score`，对目标列进行排序（顺序）

SELECT `name`, `major`, `score` FROM `student` ORDER BY `score`;

结果：

name	major	score
小黑	化学	20
小白	英语	50
小绿	历史	70
小蓝	英语	80
小黄	生物	90

3. 依照特定列 `score`，对目标列进行排序（倒序）

SELECT `name`, `major`, `score` FROM `student` ORDER BY `score` DESC;

结果：

name	major	score
小黄	生物	90
小蓝	英语	80
小绿	历史	70
小白	英语	50
小黑	化学	20

4. 组合排序，依照特定列 `score`, `student_id` 先后对目标列进行排序

SELECT * FROM `student` ORDER BY `score`, `student_id`;

结果：

student_id	name	major	score
5	小黑	化学	20
1	小白	英语	50
3	小绿	历史	70
4	小蓝	英语	80
2	小黄	生物	90

5. `WHERE` 多条件限定情况

SELECT * FROM `student` 
WHERE `major` IN('生物', '历史', '英语');

或者

SELECT * FROM `student` 
WHERE `major` = '生物' OR `major` = '历史' OR `major` = '英语';

结果：

student_id	name	major	score
1	小白	英语	50
2	小黄	生物	90
3	小绿	历史	70
4	小蓝	英语	80

第七节：案例：创建公司数据库

本案例展示了如何创建一个包含员工、部门、客户以及员工与客户关系的公司数据库。

创建数据表

1. Employee 表

CREATE TABLE `employee`(
    `emp_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `birth_date` DATE,
    `sex` VARCHAR(1),
    `salary` INT,
    `branch_id` INT,
    `sup_id` INT
);

2. Branch 表

CREATE TABLE `branch`(
    `branch_id` INT PRIMARY KEY,
    `branch_name` VARCHAR(20),
    `manager_id` INT,
    FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL
);

外键 (manager_id) 对应 employee(emp_id

3. Customer 表

CREATE TABLE `customer`(
    `cust_id` INT PRIMARY KEY,
    `name` VARCHAR(50),
    `address` VARCHAR(100),
    `phone` VARCHAR(15)
);

4. Works_With 表

CREATE TABLE `works_with`(
    `emp_id` INT,
    `cust_id` INT,
    `total_sales` DECIMAL(10, 2),
    PRIMARY KEY (`emp_id`, `cust_id`),
    FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,
    FOREIGN KEY (`cust_id`) REFERENCES `customer`(`cust_id`) ON DELETE CASCADE
);

插入数据

让我们插入一些数据以便更好地演示如何进行查询。

插入 Employee 表数据

INSERT INTO `employee` (`emp_id`, `name`, `birth_date`, `sex`, `salary`, `branch_id`, `sup_id`)
VALUES 
(1, 'Alice', '1985-01-15', 'F', 50000, 1, NULL),
(2, 'Bob', '1975-03-22', 'M', 60000, 1, 1),
(3, 'Charlie', '1990-07-18', 'M', 55000, 2, NULL);

插入 Branch 表数据

INSERT INTO `branch` (`branch_id`, `branch_name`, `manager_id`)
VALUES 
(1, 'HQ', 1),
(2, 'Branch 1', 3);

插入 Customer 表数据

INSERT INTO `customer` (`cust_id`, `name`, `address`, `phone`)
VALUES 
(1, 'Customer A', '123 Main St', '555-1234'),
(2, 'Customer B', '456 Elm St', '555-5678');

插入 Works_With 表数据

INSERT INTO `works_with` (`emp_id`, `cust_id`, `total_sales`)
VALUES 
(1, 1, 2000.00),
(2, 1, 1500.00),
(2, 2, 3000.00);

复杂查询示例

0. 图片案例查询

获取公司数据

基本语法案例：

-- 1．取得所有员工资料
SELECT * FROM `employee`;

-- 2．取得所有客户资料
SELECT * FROM `client`;

-- 3．按薪水低到高取得员工资料
SELECT * FROM `employee` ORDER BY `salary`;

-- 4．取得薪水前3高的员工
SELECT * FROM `employee` ORDER BY `salary` DESC LIMIT 3;

-- 5．取得所有员工的名子
SELECT `name` FROM `employee`;

-- 6．取得所有员工的性别，筛除重复值
SELECT DISTINCT `sex` FROM `employee`;

聚合函数

基本语法案例：

-- aggregate functions聚合函数

-- 1．取得员工人数
SELECT COUNT(`emp_id`) FROM `employee`;

-- 2．取得所有出生於 1970-01-01之後的女性员工人数
SELECT COUNT(`emp_id`) FROM `employee` WHERE `birth_data` > '1970-01-01' AND `sex` = 'F';

-- 3．取得所有员工的平均薪水 
SELECT AVG(`salary`) FROM `employee`;

-- 4．取得所有员工薪水的总和
SELECT SUM(`salary`) FROM `employee`;

-- 5．取得的最高薪水
SELECT MAX(`salary`) FROM `employee`;

-- 6．取得的最低薪水
SELECT MIN(`salary`) FROM `employee`;

wildcards 万用字符

基本语法案例：

-- wildcards万用字符% 代表多个字元，_代表一固字元
-- 1．取得电话号码尾数是335的客户
SELECT * FROM `client` WHERE `phone` LIKE '%335';

-- 2．取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';

-- 3．取得生日在12月的员工
SELECT * FROM `employee` WHERE `birth_data` LIKE '____-12%';

union 联合

基本语法案例：

-- 1. 员工名子 union 客户名子
SELECT `name` FROM `employee` 
UNION
SELECT `client_name` FROM `client`;

-- 2. 员工id + 员工名子 union 客户id + 客户名子
SELECT `emp_id`, `name` FROM `employee`
UNION
SELECT `client_id`, `client_name` FROM `client`;

-- 3．员工薪水 union 销售金额
SELECT `salary` AS `total_money` FROM `employee`
UNION
SELECT `total_sales` FROM `works_with`;

合并的字符类型要求一致

join 连接

-- join 连接

-- 取得所有部门经理的名子
SELECT `emp_id`, `name` FROM `employee` JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;

-- LEFT JOIN 左连接 空缺为NULL
-- RIGHT JOIN 右连接 空缺为NULL

JOIN连接时通过.来标定属性的表归属

subquery 子查询

-- subquery子查韵
-- 1．找出研发部门经理名子
SELECT `name` 
FROM `employee` 
WHERE `emp_id` = (
SELECT `manager_id` 
FROM `branch` 
    WHERE `branch_name` = '研发'
);

-- 2．找出对单一位客户销售金额超过50000的员工名子
SELECT `name` 
FROM `employee` 
WHERE `emp_id` IN (
SELECT `emp_id` 
FROM `works_with` 
    WHERE `total_sales` > 50000
);

on delete

Branch

CREATE TABLE `branch`(
`branch_id` INT PRIMARY KEY,
`branch_name` VARCHAR(20),
`manager_id` INT,
        FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL
);

表示：在删除表单时employee(emp_id)若缺失， (manager_id) 赋值为NULL
注意删除时，主键不能为NULL

Works_with

CREATE TABLE `works_with`(
`emp_id` INT,
`client_id` INT,
    `total_sales` INT,
PRIMARY KEY(`emp_id`, `client_id`),
FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,
    FOREIGN KEY (`client_id`) REFERENCES `client` (`client_id`) ON DELETE CASCADE
);

表示：在删除表单时employee(emp_id)若无对应，对应行直接删除

1. 查询每个员工的总销售额

SELECT 
    e.`name` AS `employee_name`,
    SUM(w.`total_sales`) AS `total_sales`
FROM 
    `employee` e
JOIN 
    `works_with` w ON e.`emp_id` = w.`emp_id`
GROUP BY 
    e.`name`;

结果：

employee_name	total_sales
Alice	2000.00
Bob	4500.00

2. 查询每个部门的经理姓名

SELECT 
    b.`branch_name`,
    e.`name` AS `manager_name`
FROM 
    `branch` b
JOIN 
    `employee` e ON b.`manager_id` = e.`emp_id`;

结果：

branch_name	manager_name
HQ	Alice
Branch 1	Charlie

3. 查询特定员工负责的客户信息

假设我们想查询Bob（emp_id = 2）负责的客户信息：

SELECT 
    c.`name` AS `customer_name`,
    c.`address`,
    c.`phone`,
    w.`total_sales`
FROM 
    `customer` c
JOIN 
    `works_with` w ON c.`cust_id` = w.`cust_id`
WHERE 
    w.`emp_id` = 2;

结果：

customer_name	address	phone	total_sales
Customer A	123 Main St	555-1234	1500.00
Customer B	456 Elm St	555-5678	3000.00

如何使用Python与MySQL进行集成，并展示一些更复杂的查询和数据处理操作。
以公司数据库为例，如何编写Python代码来查询数据、进行数据分析，并将结果可视化。

使用Python与MySQL进行集成的详细示例

1. 安装 `mysql-connector-python` 库

首先，确保你安装了 mysql-connector-python 库。可以使用以下命令进行安装：

2. 连接MySQL数据库并执行查询

以下是一个详细的Python示例，展示如何连接到MySQL数据库，执行查询并处理结果：

import mysql.connector

def connect_to_database():
    # 建立数据库连接
    conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='your_password',
        database='your_database'
    )
    return conn

def fetch_employee_sales():
    # 连接数据库
    conn = connect_to_database()
    cursor = conn.cursor()

    # 查询每个员工的总销售额
    query = """
    SELECT 
        e.name AS employee_name,
        SUM(w.total_sales) AS total_sales
    FROM 
        employee e
    JOIN 
        works_with w ON e.emp_id = w.emp_id
    GROUP BY 
        e.name;
    """
    cursor.execute(query)

    # 获取查询结果
    results = cursor.fetchall()
    
    # 处理查询结果
    for row in results:
        print(f"Employee: {row[0]}, Total Sales: {row[1]}")

    # 关闭游标和连接
    cursor.close()
    conn.close()

if __name__ == "__main__":
    fetch_employee_sales()

3. 数据分析和可视化

使用Python中的Pandas库进行数据分析，并使用Matplotlib或Seaborn进行可视化。

首先，确保安装Pandas和Matplotlib：

pip install pandas matplotlib

然后，我们可以扩展上述示例，使用Pandas读取查询结果并进行可视化：

import mysql.connector
import pandas as pd
import matplotlib.pyplot as plt

def connect_to_database():
    # 建立数据库连接
    conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='your_password',
        database='your_database'
    )
    return conn

def fetch_employee_sales():
    # 连接数据库
    conn = connect_to_database()
    cursor = conn.cursor()

    # 查询每个员工的总销售额
    query = """
    SELECT 
        e.name AS employee_name,
        SUM(w.total_sales) AS total_sales
    FROM 
        employee e
    JOIN 
        works_with w ON e.emp_id = w.emp_id
    GROUP BY 
        e.name;
    """
    cursor.execute(query)

    # 获取查询结果并转换为Pandas DataFrame
    results = cursor.fetchall()
    df = pd.DataFrame(results, columns=['Employee', 'Total Sales'])

    # 关闭游标和连接
    cursor.close()
    conn.close()
    
    return df

def visualize_sales(df):
    # 使用Matplotlib进行可视化
    plt.figure(figsize=(10, 6))
    plt.bar(df['Employee'], df['Total Sales'], color='skyblue')
    plt.xlabel('Employee')
    plt.ylabel('Total Sales')
    plt.title('Employee Total Sales')
    plt.show()

if __name__ == "__main__":
    df = fetch_employee_sales()
    visualize_sales(df)

高级数据处理和可视化

1. 查询每个部门的总销售额以及部门经理的姓名

如何在Python中执行此查询，并将结果进行可视化。

def fetch_branch_sales():
    # 连接数据库
    conn = connect_to_database()
    cursor = conn.cursor()

    # 查询每个部门的总销售额以及部门经理的姓名
    query = """
    SELECT 
        b.branch_name,
        SUM(w.total_sales) AS total_sales,
        e.name AS manager_name
    FROM 
        branch b
    JOIN 
        employee e ON b.manager_id = e.emp_id
    JOIN 
        employee emp ON b.branch_id = emp.branch_id
    JOIN 
        works_with w ON emp.emp_id = w.emp_id
    GROUP BY 
        b.branch_name, e.name;
    """
    cursor.execute(query)

    # 获取查询结果并转换为Pandas DataFrame
    results = cursor.fetchall()
    df = pd.DataFrame(results, columns=['Branch', 'Total Sales', 'Manager'])

    # 关闭游标和连接
    cursor.close()
    conn.close()
    
    return df

def visualize_branch_sales(df):
    # 使用Matplotlib进行可视化
    plt.figure(figsize=(12, 6))
    branches = df['Branch']
    sales = df['Total Sales']
    managers = df['Manager']
    
    bars = plt.bar(branches, sales, color='lightgreen')

    # 在图表上添加部门经理的姓名
    for bar, manager in zip(bars, managers):
        yval = bar.get_height()
        plt.text(bar.get_x() + bar.get_width()/2, yval + 1000, manager, ha='center', va='bottom')

    plt.xlabel('Branch')
    plt.ylabel('Total Sales')
    plt.title('Total Sales by Branch and Manager')
    plt.show()

if __name__ == "__main__":
    df = fetch_branch_sales()
    visualize_branch_sales(df)

2. 创建销售记录表并插入数据

CREATE TABLE `sales_record`(
    `record_id` INT PRIMARY KEY,
    `emp_id` INT,
    `sale_date` DATE,
    `amount` DECIMAL(10, 2),
    FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE
);

INSERT INTO `sales_record` (`record_id`, `emp_id`, `sale_date`, `amount`)
VALUES 
(1, 1, '2023-01-15', 2000.00),
(2, 2, '2023-02-20', 1500.00),
(3, 2, '2023-03-10', 3000.00),
(4, 1, '2023-03-25', 2500.00);

如果这对您有所帮助，希望点赞支持一下作者！ 😊

[ 详细全文-点击查看](

)

file

原文地址：https://blog.csdn.net/ThsPool/article/details/140209455

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Spring源码十一：事件驱动
下一篇：Codeforces Round 903 (Div. 3)A~F

[Azure PL300 Certified] 数据分析概论与定义
数据分析是标识、清除、转换和建模数据的过程，用于发现有意义和有用的信息。然后通过报表将数据制作成故事进行分析，支持关键的决策过程。
阅读更多2024-11-18
什么是微服务?
微服务是一种经过良好架构设计的分布式架构方案
阅读更多2024-11-18
leetcode-8-字符串转整数
题解:代码:
阅读更多2024-11-18
docker更新镜像源
1. 阿里云镜像加速器：https://cr.console.aliyun.com/cn-hangzhou/instances/mirrors2. 腾讯云镜像加速器：https://cloud.ten
阅读更多2024-11-18
21.3D surface
【代码】21.3D surface。
阅读更多2024-11-18
2. Django中的URL调度器 (自定义路径转换器)
路径转换器是一种 URL 模式的匹配工具，它不仅能够验证路径段的格式，还可以将匹配的路径段传递给视图函数。: 匹配一个整数。: 匹配任意非空字符串，不包括斜杠 /。: 匹配字母、数字、下划线或连字符。
阅读更多2024-11-18
【c++入门】打开新世界大门之初遇c++
在学习了c语言，初阶数据结构后，我们正式走进c++世界大门目录前言一、认识c++二、缺省参数三、函数重载四、引用4.1什么是引用？4.2 使用场景4.2.1 做参数4.2.2做函数返回值4.3引用和指
阅读更多2024-11-18
从零开始使用GOT-OCR2.0——多模态通用型OCR（非常具有潜力的开源OCR项目）：项目环境安装配置 + 测试使用
本文是多模态通用型OCR模型的环境安装和测试部分
阅读更多2024-11-18
WPF如何全局应用黑白主题效果
灰白色很多时候用于纪念，哀悼等。那么使用WPF如何来做到这种效果呢？要实现的这种效果，我们会发现，它其实不仅仅是要针对图片，而是要针对整个窗口来实现灰白色。如果只是针对图片的话，我可以可以对图片进行灰
阅读更多2024-11-18
【GNU】gcc -O编译选项 -Og -O0 -O1 -O2 -O3 -Os
GCC 提供的-O系列选项用于优化代码。这些选项可以控制编译器对代码进行优化的程度和类型，从而提高代码的性能、减小代码体积或优化其他特性。
阅读更多2024-11-18

MySQL 基本语法讲解及示例（下）

第六节：如何检索资料

初始表格 student

检索目标表格

1. 选取目标列 name, major

2. 依照特定列 score，对目标列进行排序（顺序）

3. 依照特定列 score，对目标列进行排序（倒序）

4. 组合排序，依照特定列 score, student_id 先后对目标列进行排序

5. WHERE 多条件限定情况

第七节：案例：创建公司数据库

创建数据表

1. Employee 表

2. Branch 表

3. Customer 表

4. Works_With 表

插入数据

插入 Employee 表数据

插入 Branch 表数据

插入 Customer 表数据

插入 Works_With 表数据

复杂查询示例

0. 图片案例查询

获取公司数据

聚合函数

wildcards 万用字符

union 联合

join 连接

subquery 子查询

on delete

1. 查询每个员工的总销售额

2. 查询每个部门的经理姓名

3. 查询特定员工负责的客户信息

使用Python与MySQL进行集成的详细示例

1. 安装 mysql-connector-python 库

2. 连接MySQL数据库并执行查询

3. 数据分析和可视化

高级数据处理和可视化

1. 查询每个部门的总销售额以及部门经理的姓名

2. 创建销售记录表并插入数据

相关文章

初始表格 `student`

1. 选取目标列 `name`, `major`

2. 依照特定列 `score`，对目标列进行排序（顺序）

3. 依照特定列 `score`，对目标列进行排序（倒序）

4. 组合排序，依照特定列 `score`, `student_id` 先后对目标列进行排序

5. `WHERE` 多条件限定情况

1. 安装 `mysql-connector-python` 库