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

1.22 使长输出行更具可读性

mysql> show full columns from limbs;
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| thing | varchar(20) | utf8mb4_0900_ai_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| legs  | int         | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| arms  | int         | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.11 sec)mysql> show full columns from limbs\g
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| thing | varchar(20) | utf8mb4_0900_ai_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| legs  | int         | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| arms  | int         | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.01 sec)

1.23 控制mysql的繁冗级别

D:\software\MySql\bin>echo "select now()" | mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"select now()"' at line 1

这个错误是因为在 Windows 的 echo 命令中，双引号被直接传递给了 MySQL，导致语法错误

D:\software\MySql\bin>echo select now() | mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
now()
2025-05-18 09:54:49D:\software\MySql\bin>mysql -u root -proot -e "select now()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now()               |
+---------------------+
| 2025-05-18 09:56:46 |
+---------------------+-v, --verbose       Write more. (-v -v -v gives the table output format).
D:\software\MySql\bin>echo select now() | mysql -u root -proot -v
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------now()
2025-05-18 09:59:14D:\software\MySql\bin>echo select now() | mysql -u root -proot -vv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------now()
2025-05-18 09:59:27
1 row in setByeD:\software\MySql\bin>echo select now() | mysql -u root -proot -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select now()
--------------+---------------------+
| now()               |
+---------------------+
| 2025-05-18 09:59:36 |
+---------------------+
1 row in set (0.00 sec)Bye

-s, --silent        Be more silent. Print results with a tab as separator,each row on new line.
D:\software\MySql\bin>echo select now() | mysql -u root -proot -s
mysql: [Warning] Using a password on the command line interface can be insecure.
2025-05-18 10:05:11D:\software\MySql\bin>echo select now() | mysql -u root -proot -sss
mysql: [Warning] Using a password on the command line interface can be insecure.
2025-05-18 10:05:18

1.24 记录交互式的mysql会话

  --tee=name          Append everything into outfile. See interactive help (\h)also. Does not work in batch mode. Disable with--disable-tee. This option is disabled by default.

————————————————
–tee=D:\sql\Mysql_learning\tmp.out
作用：开启 日志记录功能，将客户端的输入（SQL 语句）和输出（查询结果、错误信息等）实时写入指定文件 tmp.out。
细节：
文件路径：需确保路径存在且当前用户有写入权限。
日志内容：包括连接成功后的所有操作记录，常用于调试、审计或学习复盘（如本例中用于记录 MySQL 学习过程）。
关闭日志：可执行 notee 命令停止记录，或退出客户端自动终止。 \T或 \t 来开启或关闭 tee的输出。
开启日志记录：将后续所有操作及结果保存到 D:\sql\Mysql_learning\tmp.out 文件中。
进入交互模式：等待用户输入 SQL 语句，执行后显示结果并记录到日志文件。
典型使用场景
学习与调试：记录 SQL 练习过程，方便复盘和排查错误。
审计与追溯：在生产环境中记录关键操作，用于合规性检查或问题追溯（需注意权限和安全性）。
批量操作记录：配合脚本执行批量 SQL 时，保存执行过程和结果。

D:\software\MySql\bin>mysql -u root -proot --tee=D:\sql\Mysql_learning\tmp.out cookbook
mysql: [Warning] Using a password on the command line interface can be insecure.
Logging to file 'D:\sql\Mysql_learning\tmp.out'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 74
Server version: 8.0.40 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>mysql> \T D:\sql\Mysql_learning\tmp.out
Logging to file 'D:\sql\Mysql_learning\tmp.out'
mysql> \t D:\sql\Mysql_learning\tmp.out

Outfile disabled.

mysql  Ver 8.0.40 for Win64 on x86_64 (MySQL Community Server - GPL)Connection id:          74
Current database:       cookbook
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter:        ;
Server version:         8.0.40 MySQL Community Server - GPL
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 8 days 13 hours 28 min 20 sec

Threads: 3 Questions: 17190 Slow queries: 0 Opens: 1319 Flush tables: 4 Open tables: 37 Queries per second avg: 0.023

ERROR:
Unknown command ‘\M’.
Outfile disabled.
-> \c
1.25 以之前执行的语句创建mysql脚本
通过在mysql中使用–tee命令选项或\T命令可以记录mysql 会话的所有或部分。
开启日志：\T /path/to/new_logfile.log
关闭日志：\t（小写 t）

mysql> \T C:\mysql\part1.log      -- 开始记录到 part1.log
mysql> SELECT * FROM users;       -- 查询会被记录
mysql> \T C:\mysql\part2.log      -- 切换到新日志文件
mysql> INSERT INTO orders ...     -- 此操作记录到 part2.log
mysql> \t                         -- 停止记录

两者的核心区别
特性 --tee 选项 \T 命令
生效时机 客户端启动时一次性设置 会话中随时启用 / 禁用
日志文件切换 无法中途更换文件 可随时切换到新文件
适用场景 完整记录整个会话 分段记录或选择性记录
命令形式 命令行参数（非 SQL 语法） SQL 会话中的命令（以 \ 开头）

\o 命令 -o, --one-database  Ignore statements except those that occur while thedefault database is the one named at the command line.
将后续输出仅写入文件，不显示在终端（与 --tee/  \T 的区别在于不显示到屏幕）。
sql
mysql> \o C:\mysql\silent_output.log
mysql> SELECT * FROM large_table;  -- 结果只写入文件，不显示
system 命令
在不退出 MySQL 会话的情况下执行系统命令（如查看日志文件大小）。
sql
mysql> system ls -lh /var/log/mysql/

1.26 在sql语句中使用用户自定义的变量管理customers表。

1. 创建customers表
   首先，你需要一个customers表。以下是一个创建表的示例SQL语句：

mysql> CREATE TABLE IF NOT EXISTS customers (->     cust_id VARCHAR(255) NOT NULL,->     first_name VARCHAR(100) DEFAULT NULL,->     last_name VARCHAR(100) DEFAULT NULL,->     email VARCHAR(255) UNIQUE DEFAULT NULL,->     phone_number VARCHAR(20) DEFAULT NULL,->     address_line_1 VARCHAR(255) DEFAULT NULL,->     address_line_2 VARCHAR(255) DEFAULT NULL,->     city VARCHAR(100) DEFAULT NULL,->     state_province VARCHAR(100) DEFAULT NULL,->     postal_code VARCHAR(20) DEFAULT NULL,->     country VARCHAR(100) DEFAULT NULL,->     created_at DATETIME DEFAULT CURRENT_TIMESTAMP,->     updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,->     PRIMARY KEY (cust_id)-> );
Query OK, 0 rows affected (0.11 sec)mysql> -- 向 Customers 表中插入示例数据
mysql> INSERT INTO `Customers` (`cust_id`, `first_name`, `last_name`, `email`, `phone_number`, `address_line_1`, `city`, `state_province`, `postal_code`, `country`) VALUES-> ('CUST001', 'John', 'Doe', 'john.doe@example.com', '+1-800-123-4567', '123 Main St.', 'Springfield', 'IL', '62704', 'USA'),-> ('CUST002', 'Jane', 'Smith', 'jane.smith@example.com', '+1-800-987-6543', '456 Elm St.', 'Shelbyville', 'KY', '40065', 'USA'),-> ('CUST003', 'Alice', 'Johnson', 'alice.johnson@example.com', '+1-800-555-1212', '789 Oak Ave.', 'Capital City', 'TX', '76101', 'USA');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

代替

CREATE TABLE customers (customer_id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50) NOT NULL,last_name VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL,phone VARCHAR(20),address VARCHAR(255),city VARCHAR(50),state VARCHAR(50),zip_code VARCHAR(10),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 插入数据到customers表
   向表中插入数据可以使用以下SQL命令：

INSERT INTO customers (first_name, last_name, email, phone, address, city, state, zip_code) 
VALUES ('John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St', 'Anytown', 'CA', '12345');

3. 查询customers表中的数据
   查询所有客户：

SELECT * FROM customers;
查询特定条件的客户，例如按城市查询：

SELECT * FROM customers WHERE city = ‘Anytown’;
4. 更新customers表中的数据
更新特定客户的邮箱：

UPDATE customers SET email = ‘new.email@example.com’ WHERE customer_id = 1;
5. 删除customers表中的数据
删除特定客户：

DELETE FROM customers WHERE customer_id = 1;

mysql> select @max_limbs := MAX(arms+legs) from limbs;
+------------------------------+
| @max_limbs := MAX(arms+legs) |
+------------------------------+
|                          100 |
+------------------------------+
1 row in set, 1 warning (0.01 sec)mysql> SELECT LAST_INSERT_ID() AS new_customer_id;
+-----------------+
| new_customer_id |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)mysql> SELECT MAX(customer_id) FROM customers;
+------------------+
| MAX(customer_id) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)mysql> SELECT customer_id FROM customers-> WHERE first_name = 'John' AND last_name = 'Doe';
+-------------+
| customer_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)mysql> SELECT @john_id := customer_id FROM customers-> WHERE first_name = 'John' AND last_name = 'Doe';
+-------------------------+
| @john_id := customer_id |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

完整的示例：

mysql> -- 插入新客户
mysql> INSERT INTO customers (first_name, last_name, email)-> VALUES ('Jane', 'Smith', 'jane.smith@example.com');
Query OK, 1 row affected (0.01 sec)mysql>
mysql> -- 获取新插入的ID
mysql> SET @new_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> -- 使用变量查询
mysql> SELECT * FROM customers WHERE customer_id = @new_id;
+-------------+------------+-----------+------------------------+-------+---------+------+-------+----------+---------------------+
| customer_id | first_name | last_name | email                  | phone | address | city | state | zip_code | created_at          |
+-------------+------------+-----------+------------------------+-------+---------+------+-------+----------+---------------------+
|           2 | Jane       | Smith     | jane.smith@example.com | NULL  | NULL    | NULL | NULL  | NULL     | 2025-05-18 11:26:49 |
+-------------+------------+-----------+------------------------+-------+---------+------+-------+----------+---------------------+
1 row in set (0.00 sec)
Windows10系统安装perl命令
https://blog.csdn.net/m0_37542524/article/details/130095555
mysql> select @name := thing from limbs where legs = 0;
+----------------+
| @name := thing |
+----------------+
| squid          |
| octopus        |
| fish           |
| phonograph     |
| fish           |
| phonograph     |
| squid          |
+----------------+
7 rows in set, 1 warning (0.00 sec)mysql> select @name;
+-------+
| @name |
+-------+
| squid |
+-------+
1 row in set (0.00 sec)mysql> select @name2 := thing from limbs where legs < 0;
Empty set, 1 warning (0.00 sec)mysql> select @name2;
+--------+
| @name2 |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)mysql> set @sum = 4 + 7;
Query OK, 0 rows affected (0.00 sec)mysql> select @sum;
+------+
| @sum |
+------+
|   11 |
+------+
1 row in set (0.00 sec)mysql> set @max_limbs = (select max(arms + legs) from limbs);
Query OK, 0 rows affected (0.00 sec)mysql> select @max_limbs;
+------------+
| @max_limbs |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)mysql> set @x = 1, @x = 2; select @x, @x;
Query OK, 0 rows affected (0.00 sec)+------+------+
| @x   | @x   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

1.27 为查询输出行计数

mysql -u root -proot --skip-column-names -e "select thing, arms from limbs cookbook" | cat -n
其中的 cat -n 是 Linux/macOS 系统用于显示文件内容并添加行号的命令，Windows 系统不支持。
使用 Windows 等效命令 more +n
mysql -u root -proot --skip-column-names -e "select thing, arms from limbs" cookbook | more +n
more +n：显示内容并添加行号（+n 表示从第 n 行开始显示，省略则默认从第 1 行开始）。

– 假设limbs是表名，cookbook是数据库名，应在连接时指定数据库：

D:\software\MySql\bin>mysql -u root -proot --skip-column-names -e "select thing, arms from limbs" cookbook | more +1
mysql: [Warning] Using a password on the command line interface can be insecure.
insect  0
squid   10
octopus 8
fish    0
centipede       0
table   0
armchair        2
phonograph      1
tripod  0
Peg Leg Pete    2
space alien     NULL
armchair        2
centipede       0
fish    0
human   2
insect  0
Peg Leg Pete    2
phonograph      1
space alien     NULL
squid   10
table   0
tripod  0-D, --database=name Database to use.--default-character-set=nameSet the default character set.--delimiter=name    Delimiter to be used.--enable-cleartext-pluginEnable/disable the clear text authentication plugin.
————————————————
bash
mysql -u root -proot --skip-column-names -D cookbook -e "select thing, arms from limbs"
D:\software\MySql\bin>mysql -u root -proot --skip-column-names -D cookbook -e "select thing, arms from limbs"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+------+
|        human | 2    |
|       insect | 0    |
|        squid | 10   |
|      octopus | 8    |
|         fish | 0    |
|    centipede | 0    |
|        table | 0    |
|     armchair | 2    |
|   phonograph | 1    |
|       tripod | 0    |
| Peg Leg Pete | 2    |
|  space alien | NULL |
|     armchair | 2    |
|    centipede | 0    |
|         fish | 0    |
|        human | 2    |
|       insect | 0    |
| Peg Leg Pete | 2    |
|   phonograph | 1    |
|  space alien | NULL |
|        squid | 10   |
|        table | 0    |
|       tripod | 0    |
+--------------+------+

只需查看结果的行号，可在 SQL 中直接实现：

mysql> SELECT @row:=@row+1 AS row_num, thing, arms-> FROM limbs, (SELECT @row:=0) AS t;
+---------+--------------+------+
| row_num | thing        | arms |
+---------+--------------+------+
|       1 | human        |    2 |
|       2 | insect       |    0 |
|       3 | squid        |   10 |
|       4 | octopus      |    8 |
|       5 | fish         |    0 |
|       6 | centipede    |    0 |
|       7 | table        |    0 |
|       8 | armchair     |    2 |
|       9 | phonograph   |    1 |
|      10 | tripod       |    0 |
|      11 | Peg Leg Pete |    2 |
|      12 | space alien  | NULL |
|      13 | armchair     |    2 |
|      14 | centipede    |    0 |
|      15 | fish         |    0 |
|      16 | human        |    2 |
|      17 | insect       |    0 |
|      18 | Peg Leg Pete |    2 |
|      19 | phonograph   |    1 |
|      20 | space alien  | NULL |
|      21 | squid        |   10 |
|      22 | table        |    0 |
|      23 | tripod       |    0 |
+---------+--------------+------+
23 rows in set, 2 warnings (0.01 sec)

使用用户变量，表达式对于查询的每一行都进行计算。

mysql> set @n = 0;
Query OK, 0 rows affected (0.00 sec)mysql> select @n := @n+1 as rownum, thing, arms, legs from limbs;
+--------+--------------+------+------+
| rownum | thing        | arms | legs |
+--------+--------------+------+------+
|      1 | human        |    2 |    2 |
|      2 | insect       |    0 |    6 |
|      3 | squid        |   10 |    0 |
|      4 | octopus      |    8 |    0 |
|      5 | fish         |    0 |    0 |
|      6 | centipede    |    0 |  100 |
|      7 | table        |    0 |    4 |
|      8 | armchair     |    2 |    4 |
|      9 | phonograph   |    1 |    0 |
|     10 | tripod       |    0 |    3 |
|     11 | Peg Leg Pete |    2 |    1 |
|     12 | space alien  | NULL | NULL |
|     13 | armchair     |    2 |    4 |
|     14 | centipede    |    0 |   99 |
|     15 | fish         |    0 |    0 |
|     16 | human        |    2 |    2 |
|     17 | insect       |    0 |    6 |
|     18 | Peg Leg Pete |    2 |    1 |
|     19 | phonograph   |    1 |    0 |
|     20 | space alien  | NULL | NULL |
|     21 | squid        |   10 |    0 |
|     22 | table        |    0 |    4 |
|     23 | tripod       |    0 |    3 |
+--------+--------------+------+------+
23 rows in set, 1 warning (0.00 sec)1.28 将mysql用作计算器
mysql> select(17 + 23) / sqrt(64);
+----------------------+
| (17 + 23) / sqrt(64) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)mysql> select 'ABC'= 'abc';
+--------------+
| 'ABC'= 'abc' |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)mysql> set @daily_room_charge = 100.00;
Query OK, 0 rows affected (0.00 sec)mysql> set @num_of_nights = 3;
Query OK, 0 rows affected (0.00 sec)mysql> set @tax_precent = 8;
Query OK, 0 rows affected (0.00 sec)mysql> set @total_room_charge = @daily_room_charge * @num_of_nights;
Query OK, 0 rows affected (0.00 sec)mysql> set @tax = (@total_room_charge * @tax_precent)/100;
Query OK, 0 rows affected (0.00 sec)mysql> set @total = @total_room_charge + @tax;
Query OK, 0 rows affected (0.00 sec)mysql> select @total;
+---------------+
| @total        |
+---------------+
| 324.000000000 |
+---------------+
1 row in set (0.00 sec)

1.29 在shell脚本中使用mysql
Windows 系统不支持 Linux 命令（如grep）导致的。
D:\software\MySql\bin>mysql -u root -proot -e status | grep “^uptime”
‘grep’ 不是内部或外部命令，也不是可运行的程序
或批处理文件。

D:\software\MySql\bin>mysql -u root -proot -e "status" | findstr /B "Uptime"
mysql: [Warning] Using a password on the command line interface can be insecure.
Uptime:                 8 days 18 hours 37 min 16 secD:\software\MySql\bin>mysql -u root -proot -e "SHOW GLOBAL STATUS LIKE 'Uptime';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 758359 |
+---------------+--------+

1.使用 PowerShell（最简单）
按 Win + X 选择 PowerShell（管理员）。
PS D:\software\MySql\bin> .\mysql -u root -proot -Bse "SHOW GLOBAL STATUS LIKE 'Uptime';" | ForEach-Object { [regex]::Match($_, "\d+").Value }
mysql: [Warning] Using a password on the command line interface can be insecure.
758695
2.使用 CMD 纯命令（无需额外工具）
D:\software\MySql\bin>mysql -u root -proot -e "SHOW GLOBAL STATUS LIKE 'Uptime';" | findstr /C:"Uptime" | findstr /r "[0-9]"
mysql: [Warning] Using a password on the command line interface can be insecure.
Uptime  758881

3.将 MySQL 的 bin 目录添加到系统环境变量（推荐）
这样可以在任意目录直接使用mysql命令，无需每次指定路径：
右键点击 “此电脑” → 属性 → 高级系统设置 → 环境变量。
在系统变量中找到Path，点击编辑。
新建并添加 MySQL 的bin目录路径：D:\software\MySql\bin。
确认保存，重启 PowerShell。
（！未操作成功，以待日后来处理 20250518）

使用完整路径
使用 & 符号作为调用操作符，告诉 PowerShell 将后面的内容作为命令执行。

PS C:\windows\system32> &D:\software\MySql\bin\mysql -u root -proot -Bse "SHOW GLOBAL STATUS LIKE 'Uptime';" | ForEach-Object { [regex]::Match($_, "\d+").Value }
mysql: [Warning] Using a password on the command line interface can be insecure.
761228
PS C:\windows\system32> # 交互式输入密码（不显示在命令历史中）
PS C:\windows\system32> &D:\software\MySql\bin\mysql -u root -p -Bse "SHOW GLOBAL STATUS LIKE 'Uptime';" | ForEach-Object { [regex]::Match($_, "\d+").Value }
Enter password: ****
761426
# 查看 MySQL 版本
PS C:\windows\system32> &D:\software\MySql\bin\mysql -u root -p -Bse "SELECT VERSION();"
Enter password: ****
8.0.40
# 获取服务器负载信息
PS C:\windows\system32> &D:\software\MySql\bin\mysql -u root -p -Bse "SHOW GLOBAL STATUS LIKE 'Threads_running';"
Enter password: ****
Threads_running 2

mysql> select count(*) as 'new log entries:'-> from log_tbl-> where date_added = date_sub(curdate(), interval 1 day);
ERROR 1146 (42S02): Table 'cookbook.log_tbl' doesn't exist

这个错误表明您正在尝试查询一个不存在的表 log_tbl。让我们一步步解决这个问题：

解决方案

1. 首先确认数据库中存在的表
   sql
   SHOW TABLES;
2. 如果您确实需要 log_tbl 表，请先创建它

sql
CREATE TABLE IF NOT EXISTS log_tbl (id INT AUTO_INCREMENT PRIMARY KEY,log_message TEXT,date_added DATE,-- 其他您需要的字段INDEX (date_added)  -- 为日期字段添加索引提高查询性能
);

3. 如果您想查询其他日志表
   如果您实际想查询的是其他日志表（比如 app_log），可以修改查询：

sql
SELECT COUNT(*) AS 'new log entries:'
FROM app_log  -- 替换为实际的日志表名
WHERE date_added = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

4. 如果您想创建每日日志统计

sql
-- 创建日志统计表
CREATE TABLE IF NOT EXISTS daily_log_stats (stat_date DATE PRIMARY KEY,entry_count INT,last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

– 插入昨日日志统计

INSERT INTO daily_log_stats (stat_date, entry_count)
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS stat_date,COUNT(*) AS entry_count
FROM app_log  -- 使用您实际的日志表
WHERE date_added = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
ON DUPLICATE KEY UPDATE entry_count = VALUES(entry_count);

关键点说明
错误原因：ERROR 1146 表示表不存在

日期函数：

CURDATE() 获取当前日期

DATE_SUB() 用于日期计算

索引建议：日志表应该为日期字段添加索引

预防措施
在查询前总是检查表是否存在：

sql
SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'log_tbl';
使用事务确保数据一致性：sql
START TRANSACTION;
-- 您的查询语句
COMMIT;

D:\software\MySql\bin>mysql -u root -p  p72/951 Sunday, May 18, 2025