板凳-------Mysql cookbook学习 （八）

MySQL ：： MySQL 8.0 参考手册 ：： 30.4.5.1 extract_schema_from_file_name（） 函数

第3章：从表中查询数据
3.0 引言

mysql> use cookbook
Database changed
mysql> select version(), database();
+-----------+------------+
| version() | database() |
+-----------+------------+
| 8.0.40    | cookbook   |
+-----------+------------+
1 row in set (0.00 sec)

mysql> select * from mail;
+---------------------+---------+---------+---------+---------+---------+
| t                   | srcuser | srchost | dstuser | dsthost | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2006-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2006-05-12 15:02:49 | phil    | mars    | phil    | saturn  |    1048 |
| 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |     271 |
| 2006-05-14 09:31:37 | gene    | venus   | barb    | mars    |    2291 |
| 2006-05-14 11:52:17 | phil    | mars    | tricia  | saturn  |    5781 |
| 2006-05-14 14:42:21 | barb    | venus   | barb    | venus   |   98151 |
| 2006-05-14 17:03:01 | tricia  | saturn  | phil    | venus   | 2394482 |
| 2006-05-15 07:17:48 | gene    | mars    | gene    | saturn  |    3824 |
| 2006-05-15 08:50:57 | phil    | venus   | phil    | venus   |     978 |
| 2006-05-15 10:25:52 | gene    | mars    | tricia  | saturn  |  998532 |
| 2006-05-15 17:35:31 | gene    | saturn  | gene    | mars    |    3856 |
| 2006-05-16 09:00:28 | gene    | venus   | barb    | mars    |     613 |
| 2006-05-16 23:04:19 | phil    | venus   | barb    | venus   |   10294 |
| 2006-05-17 12:49:23 | phil    | mars    | tricia  | saturn  |     873 |
| 2006-05-19 22:21:51 | gene    | saturn  | gene    | venus   |   23992 |
+---------------------+---------+---------+---------+---------+---------+

16 rows in set (0.04 sec)

3.1 指定查询列/从指定列中查询
显示查询列， 可以按用户指定的任意顺序显示

mysql> select t, srchost, srcuser, dsthost, dstuser, size from mail;
+---------------------+---------+---------+---------+---------+---------+
| t                   | srchost | srcuser | dsthost | dstuser | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | saturn  | barb    | mars    | tricia  |   58274 |
| 2006-05-12 12:48:13 | mars    | tricia  | venus   | gene    |  194925 |
| 2006-05-12 15:02:49 | mars    | phil    | saturn  | phil    |    1048 |
| 2006-05-13 13:59:18 | saturn  | barb    | venus   | tricia  |     271 |
| 2006-05-14 09:31:37 | venus   | gene    | mars    | barb    |    2291 |
| 2006-05-14 11:52:17 | mars    | phil    | saturn  | tricia  |    5781 |
| 2006-05-14 14:42:21 | venus   | barb    | venus   | barb    |   98151 |
| 2006-05-14 17:03:01 | saturn  | tricia  | venus   | phil    | 2394482 |
| 2006-05-15 07:17:48 | mars    | gene    | saturn  | gene    |    3824 |
| 2006-05-15 08:50:57 | venus   | phil    | venus   | phil    |     978 |
| 2006-05-15 10:25:52 | mars    | gene    | saturn  | tricia  |  998532 |
| 2006-05-15 17:35:31 | saturn  | gene    | mars    | gene    |    3856 |
| 2006-05-16 09:00:28 | venus   | gene    | mars    | barb    |     613 |
| 2006-05-16 23:04:19 | venus   | phil    | venus   | barb    |   10294 |
| 2006-05-17 12:49:23 | mars    | phil    | saturn  | tricia  |     873 |
| 2006-05-19 22:21:51 | saturn  | gene    | venus   | gene    |   23992 |
+---------------------+---------+---------+---------+---------+---------+

16 rows in set (0.00 sec)

3.2 指定查询行
为了更精确的查询， 通过使用where 语句， 给select 查询加上一个或多个条件限制。

mysql> select t, srcuser, srchost, size from mail where srchost = 'venus';
+---------------------+---------+---------+-------+
| t                   | srcuser | srchost | size  |
+---------------------+---------+---------+-------+
| 2006-05-14 09:31:37 | gene    | venus   |  2291 |
| 2006-05-14 14:42:21 | barb    | venus   | 98151 |
| 2006-05-15 08:50:57 | phil    | venus   |   978 |
| 2006-05-16 09:00:28 | gene    | venus   |   613 |
| 2006-05-16 23:04:19 | phil    | venus   | 10294 |
+---------------------+---------+---------+-------+
5 rows in set (0.00 sec)

mysql> select t, srcuser, srchost from mail where srchost like 's%';
+---------------------+---------+---------+
| t                   | srcuser | srchost |
+---------------------+---------+---------+
| 2006-05-11 10:15:08 | barb    | saturn  |
| 2006-05-13 13:59:18 | barb    | saturn  |
| 2006-05-14 17:03:01 | tricia  | saturn  |
| 2006-05-15 17:35:31 | gene    | saturn  |
| 2006-05-19 22:21:51 | gene    | saturn  |
+---------------------+---------+---------+

5 rows in set (0.00 sec)

mysql> select * from mail where srcuser = 'barb' and dstuser = 'tricia';
+---------------------+---------+---------+---------+---------+-------+
| t                   | srcuser | srchost | dstuser | dsthost | size  |
+---------------------+---------+---------+---------+---------+-------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    | 58274 |
| 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |   271 |
+---------------------+---------+---------+---------+---------+-------+

3.3 格式化显示查询结果
使用列别名来作为所选择的名称

mysql> select t, srcuser,  size from mail;
+---------------------+---------+---------+
| t                   | srcuser | size    |
+---------------------+---------+---------+
| 2006-05-11 10:15:08 | barb    |   58274 |
| 2006-05-12 12:48:13 | tricia  |  194925 |
| 2006-05-12 15:02:49 | phil    |    1048 |
| 2006-05-13 13:59:18 | barb    |     271 |
| 2006-05-14 09:31:37 | gene    |    2291 |
| 2006-05-14 11:52:17 | phil    |    5781 |
| 2006-05-14 14:42:21 | barb    |   98151 |
| 2006-05-14 17:03:01 | tricia  | 2394482 |
| 2006-05-15 07:17:48 | gene    |    3824 |
| 2006-05-15 08:50:57 | phil    |     978 |
| 2006-05-15 10:25:52 | gene    |  998532 |
| 2006-05-15 17:35:31 | gene    |    3856 |
| 2006-05-16 09:00:28 | gene    |     613 |
| 2006-05-16 23:04:19 | phil    |   10294 |
| 2006-05-17 12:49:23 | phil    |     873 |
| 2006-05-19 22:21:51 | gene    |   23992 |
+---------------------+---------+---------+

16 rows in set (0.00 sec)

使用一个运算表达式在查询结果中插入一列， 表达式作为查询结果的默认列名
这样可能导致查询结果中有的列名长且含义不明确。

mysql> select
    -> concat (monthname(t), '', dayofmonth(t), ',', year(t)),
    -> srcuser,size from mail;
+--------------------------------------------------------+---------+---------+
| concat (monthname(t), '', dayofmonth(t), ',', year(t)) | srcuser | size    |
+--------------------------------------------------------+---------+---------+
| May11,2006                                             | barb    |   58274 |
| May12,2006                                             | tricia  |  194925 |
| May12,2006                                             | phil    |    1048 |
| May13,2006                                             | barb    |     271 |
| May14,2006                                             | gene    |    2291 |
| May14,2006                                             | phil    |    5781 |
| May14,2006                                             | barb    |   98151 |
| May14,2006                                             | tricia  | 2394482 |
| May15,2006                                             | gene    |    3824 |
| May15,2006                                             | phil    |     978 |
| May15,2006                                             | gene    |  998532 |
| May15,2006                                             | gene    |    3856 |
| May16,2006                                             | gene    |     613 |
| May16,2006                                             | phil    |   10294 |
| May17,2006                                             | phil    |     873 |
| May19,2006                                             | gene    |   23992 |
+--------------------------------------------------------+---------+---------+

16 rows in set (0.01 sec)

使用date_format 可以得到一样的查询结果。

mysql> select
    -> date_format(t, '%m %e, %y'),
    -> srcuser, size from mail;
+-----------------------------+---------+---------+
| date_format(t, '%m %e, %y') | srcuser | size    |
+-----------------------------+---------+---------+
| 05 11, 06                   | barb    |   58274 |
| 05 12, 06                   | tricia  |  194925 |
| 05 12, 06                   | phil    |    1048 |
| 05 13, 06                   | barb    |     271 |
| 05 14, 06                   | gene    |    2291 |
| 05 14, 06                   | phil    |    5781 |
| 05 14, 06                   | barb    |   98151 |
| 05 14, 06                   | tricia  | 2394482 |
| 05 15, 06                   | gene    |    3824 |
| 05 15, 06                   | phil    |     978 |
| 05 15, 06                   | gene    |  998532 |
| 05 15, 06                   | gene    |    3856 |
| 05 16, 06                   | gene    |     613 |
| 05 16, 06                   | phil    |   10294 |
| 05 17, 06                   | phil    |     873 |
| 05 19, 06                   | gene    |   23992 |
+-----------------------------+---------+---------+

16 rows in set (0.00 sec)

别名可以是任意的一个单词，也可以是词组，但同时也要遵守一定的定义规则。

mysql> select
    -> date_format(t, '%m %e, %y') as 'date of message',
    -> srcuser as 'message sender', size as 'number of bytes' from mail;
+-----------------+----------------+-----------------+
| date of message | message sender | number of bytes |
+-----------------+----------------+-----------------+
| 05 11, 06       | barb           |           58274 |
| 05 12, 06       | tricia         |          194925 |
| 05 12, 06       | phil           |            1048 |
| 05 13, 06       | barb           |             271 |
| 05 14, 06       | gene           |            2291 |
| 05 14, 06       | phil           |            5781 |
| 05 14, 06       | barb           |           98151 |
| 05 14, 06       | tricia         |         2394482 |
| 05 15, 06       | gene           |            3824 |
| 05 15, 06       | phil           |             978 |
| 05 15, 06       | gene           |          998532 |
| 05 15, 06       | gene           |            3856 |
| 05 16, 06       | gene           |             613 |
| 05 16, 06       | phil           |           10294 |
| 05 17, 06       | phil           |             873 |
| 05 19, 06       | gene           |           23992 |
+-----------------+----------------+-----------------+

16 rows in set (0.00 sec)

mysql> select '1+1+1' as 'The expression', 1+1+1 as 'The result';
+----------------+------------+
| The expression | The result |
+----------------+------------+
| 1+1+1          |          3 |
+----------------+------------+
1 row in set (0.00 sec)

mysql> select 1 as interger;
+----------+
| interger |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select 1 as 'integer';
+---------+
| integer |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

3.4 使用列别名来简化程序
通过使用别名的方法， 给所有使用的查询结果列一个简单的列名，方便引用。
主要修正点：

1. 统一使用 mysql.connector：删除了不必要的 MySQLdb 导入，统一使用 mysql.connector
2. 正确导入模块：添加了 import mysql.connector 和 from mysql.connector import Error
3. 修复配置处理：
   o 使用单独的 db_config 变量存储配置
   o 修正了配置文件的读取方式
4. 完善异常处理：
   o 初始化 connection = None
   o 在 finally 块中添加了 if connection 检查
5. 函数调用位置：将函数调用放在最后

import os
import configparser
import mysql.connector
from mysql.connector import Error

def query_mail_data():
    # --- 配置部分 ---
    # 方法1：直接硬编码配置（适合测试）
    config = {
        'host': 'localhost',
        'user': 'your_username',
        'password': 'your_password',
        'database': 'cookbook'
    }

    # 方法2：从配置文件读取（推荐）
    config_path = 'D:/sql/Mysql_learning/config.ini'
    if os.path.exists(config_path):
        config_parser = configparser.ConfigParser()
        config_parser.read(config_path)
        db_config = {
            'host': config_parser.get('database', 'host', fallback='localhost'),
            'user': config_parser.get('database', 'user'),
            'password': config_parser.get('database', 'password'),
            'database': config_parser.get('database', 'database', fallback='cookbook')
        }
    else:
        print(f"警告：配置文件 {config_path} 不存在，使用默认配置")
        db_config = config  # 回退到硬编码配置

    # --- 数据库连接部分 ---
    connection = None
    try:
        # 建立数据库连接
        connection = mysql.connector.connect(
            host=db_config['host'],
            database=db_config['database'],
            user=db_config['user'],
            password=db_config['password']
        )
        
        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)  # 使用字典游标方便按列名访问
            
            # 执行查询
            query = """
            SELECT 
                DATE_FORMAT(t, '%m %e, %y') AS 'date of message',
                srcuser AS 'message sender', 
                size AS 'number of bytes' 
            FROM mail
            """
            cursor.execute(query)
            
            # 获取所有结果
            records = cursor.fetchall()
            
            # 打印结果
            print("\n邮件信息:")
            print("-" * 50)
            for row in records:
                print(f"日期: {row['date of message']}")
                print(f"发件人: {row['message sender']}")
                print(f"字节数: {row['number of bytes']}")
                print("-" * 50)
                
    except Error as e:
        print(f"数据库错误: {e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL连接已关闭")

# 运行查询
query_mail_data()

邮件信息:
--------------------------------------------------
日期: 05 11, 06
发件人: barb
字节数: 58274
--------------------------------------------------
日期: 05 12, 06
发件人: tricia
字节数: 194925
--------------------------------------------------
日期: 05 12, 06
发件人: phil
字节数: 1048
--------------------------------------------------
日期: 05 13, 06
发件人: barb
字节数: 271
--------------------------------------------------
日期: 05 14, 06
发件人: gene
字节数: 2291
--------------------------------------------------
日期: 05 14, 06
发件人: phil
字节数: 5781
--------------------------------------------------
日期: 05 14, 06
发件人: barb
字节数: 98151
--------------------------------------------------
日期: 05 14, 06
发件人: tricia
字节数: 2394482
--------------------------------------------------
日期: 05 15, 06
发件人: gene
字节数: 3824
--------------------------------------------------
日期: 05 15, 06
发件人: phil
字节数: 978
--------------------------------------------------
日期: 05 15, 06
发件人: gene
字节数: 998532
--------------------------------------------------
日期: 05 15, 06
发件人: gene
字节数: 3856
--------------------------------------------------
日期: 05 16, 06
发件人: gene
字节数: 613
--------------------------------------------------
日期: 05 16, 06
发件人: phil
字节数: 10294
--------------------------------------------------
日期: 05 17, 06
发件人: phil
字节数: 873
--------------------------------------------------
日期: 05 19, 06
发件人: gene
字节数: 23992
--------------------------------------------------
MySQL连接已关闭

如果要对这个数据进行更多分析，我们可以轻松修改查询，比如：

python
# 可以添加这样的分析查询
analysis_query = """
SELECT 
    srcuser AS '发件人',
    COUNT(*) AS '邮件数量',
    SUM(size) AS '总字节数',
    AVG(size) AS '平均大小'
FROM mail
GROUP BY srcuser
ORDER BY COUNT(*) DESC
"""

3.5 合并多列来构建复合值

mysql> SELECT
    -> DATE_FORMAT(t, '%m %e, %y') AS 'date_sent',
    -> CONCAT(srcuser, '@', srchost) AS 'sender',
    -> CONCAT(dstuser, '@', dsthost) AS 'recipient',
    -> size FROM mail;
+-----------+---------------+---------------+---------+
| date_sent | sender        | recipient     | size    |
+-----------+---------------+---------------+---------+
| 05 11, 06 | barb@saturn   | tricia@mars   |   58274 |
| 05 12, 06 | tricia@mars   | gene@venus    |  194925 |
| 05 12, 06 | phil@mars     | phil@saturn   |    1048 |
| 05 13, 06 | barb@saturn   | tricia@venus  |     271 |
| 05 14, 06 | gene@venus    | barb@mars     |    2291 |
| 05 14, 06 | phil@mars     | tricia@saturn |    5781 |
| 05 14, 06 | barb@venus    | barb@venus    |   98151 |
| 05 14, 06 | tricia@saturn | phil@venus    | 2394482 |
| 05 15, 06 | gene@mars     | gene@saturn   |    3824 |
| 05 15, 06 | phil@venus    | phil@venus    |     978 |
| 05 15, 06 | gene@mars     | tricia@saturn |  998532 |
| 05 15, 06 | gene@saturn   | gene@mars     |    3856 |
| 05 16, 06 | gene@venus    | barb@mars     |     613 |
| 05 16, 06 | phil@venus    | barb@venus    |   10294 |
| 05 17, 06 | phil@mars     | tricia@saturn |     873 |
| 05 19, 06 | gene@saturn   | gene@venus    |   23992 |
+-----------+---------------+---------------+---------+

16 rows in set (0.00 sec)

3.6 where表达式中的列别名

mysql> select t, srcuser, dstuser, size/1024 as kilobytes from mail where kilobytes > 500;
ERROR 1054 (42S22): Unknown column 'kilobytes' in 'where clause'

mysql> select t, srcuser, dstuser, size/1024 as kilobytes from mail where size/1024 > 500;
+---------------------+---------+---------+-----------+
| t                   | srcuser | dstuser | kilobytes |
+---------------------+---------+---------+-----------+
| 2006-05-14 17:03:01 | tricia  | phil    | 2338.3613 |
| 2006-05-15 10:25:52 | gene    | tricia  |  975.1289 |
+---------------------+---------+---------+-----------+

2 rows in set (0.01 sec)

3.7 调试比较表达式
将在where 语句中设定的查询条件和查询结果一起显示给用户

mysql> select * from mail where srcuser < 'c' and size > 5000;
+---------------------+---------+---------+---------+---------+-------+
| t                   | srcuser | srchost | dstuser | dsthost | size  |
+---------------------+---------+---------+---------+---------+-------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    | 58274 |
| 2006-05-14 14:42:21 | barb    | venus   | barb    | venus   | 98151 |
+---------------------+---------+---------+---------+---------+-------+
2 rows in set (0.00 sec)

mysql> select srcuser, srcuser < 'c', size, size > 5000 from mail;
+---------+---------------+---------+-------------+
| srcuser | srcuser < 'c' | size    | size > 5000 |
+---------+---------------+---------+-------------+
| barb    |             1 |   58274 |           1 |
| tricia  |             0 |  194925 |           1 |
| phil    |             0 |    1048 |           0 |
| barb    |             1 |     271 |           0 |
| gene    |             0 |    2291 |           0 |
| phil    |             0 |    5781 |           1 |
| barb    |             1 |   98151 |           1 |
| tricia  |             0 | 2394482 |           1 |
| gene    |             0 |    3824 |           0 |
| phil    |             0 |     978 |           0 |
| gene    |             0 |  998532 |           1 |
| gene    |             0 |    3856 |           0 |
| gene    |             0 |     613 |           0 |
| phil    |             0 |   10294 |           1 |
| phil    |             0 |     873 |           0 |
| gene    |             0 |   23992 |           1 |
+---------+---------------+---------+-------------+

16 rows in set (0.00 sec)

3.8 使查询结果唯一化
在查询语句中加入distinct 关键字， 过滤冗余信息

mysql> select srcuser from mail;
+---------+
| srcuser |
+---------+
| barb    |
| tricia  |
| phil    |
| barb    |
| gene    |
| phil    |
| barb    |
| tricia  |
| gene    |
| phil    |
| gene    |
| gene    |
| gene    |
| phil    |
| phil    |
| gene    |
+---------+
16 rows in set (0.00 sec)

mysql> select distinct srcuser from mail;
+---------+
| srcuser |
+---------+
| barb    |
| tricia  |
| phil    |
| gene    |
+---------+
4 rows in set (0.01 sec)

mysql> select distinct year(t), month(t), dayofmonth(t) from mail;
+---------+----------+---------------+
| year(t) | month(t) | dayofmonth(t) |
+---------+----------+---------------+
|    2006 |        5 |            11 |
|    2006 |        5 |            12 |
|    2006 |        5 |            13 |
|    2006 |        5 |            14 |
|    2006 |        5 |            15 |
|    2006 |        5 |            16 |
|    2006 |        5 |            17 |
|    2006 |        5 |            19 |
+---------+----------+---------------+
8 rows in set (0.01 sec)

mysql> select count(distinct srcuser) from mail;
+-------------------------+
| count(distinct srcuser) |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.01 sec)

3.9 如何处理null 值

mysql> select * from taxpayer;
+---------+--------+
| name    | id     |
+---------+--------+
| bernina | 198-48 |
| bertha  | NULL   |
| ben     | NULL   |
| NULL    | 475-83 |
| baidu   | 111+55 |
+---------+--------+
5 rows in set (0.02 sec)

mysql> select * from taxpayer where id = NULL;
Empty set (0.00 sec)

mysql> select * from taxpayer where id != NULL;
Empty set (0.00 sec)

mysql> select * from taxpayer where id is NULL;
+--------+------+
| name   | id   |
+--------+------+
| bertha | NULL |
| ben    | NULL |
+--------+------+
2 rows in set (0.00 sec)

mysql> select * from taxpayer where id is not NULL;
+---------+--------+
| name    | id     |
+---------+--------+
| bernina | 198-48 |
| NULL    | 475-83 |
| baidu   | 111+55 |
+---------+--------+
3 rows in set (0.00 sec)

mysql> select NULL = NULL, NULL <=> NULL;
+-------------+---------------+
| NULL = NULL | NULL <=> NULL |
+-------------+---------------+
|        NULL |             1 |
+-------------+---------------+
1 row in set (0.00 sec)

mysql> select name, if(id is NULL, 'Unknown', id) as 'id' from taxpayer;
+---------+---------+
| name    | id      |
+---------+---------+
| bernina | 198-48  |
| bertha  | Unknown |
| ben     | Unknown |
| NULL    | 475-83  |
| baidu   | 111+55  |
+---------+---------+
5 rows in set (0.00 sec)

mysql> select name, IFNULL(id , 'Unknown') as 'id' from taxpayer;
+---------+---------+
| name    | id      |
+---------+---------+
| bernina | 198-48  |
| bertha  | Unknown |
| ben     | Unknown |
| NULL    | 475-83  |
| baidu   | 111+55  |
+---------+---------+
5 rows in set (0.00 sec)