❌

阅读视图

发现新文章，点击刷新页面。

office系的SQL为啥不能文本拼接？！

✇我的天
xrspook

当年今日

花了几乎一天的时间去研究什么把Access VBA里的自定义函数移植到Excel的VBA里面。大家都是VBA，大家都是 office家庭的，听上去好像没什么难度，但实际上前人已经碰壁阵亡，确定这是不可能的，我只是在做垂死的挣扎。经过这么多年office的发展，在数据格结构上，会不会只有那么一点改进呢？毕竟即便是在Excel里，如果我用的是VBA+ADO+SQL，实际上我是把数据以数据库形态进行SQL的加工。于是我就想，万一他们的数据格式是一样的，万一Excel已经进化了那么一点点呢。但现实告诉我，虽然都是VBA，虽然都是自定义函数，但是因为他们操作的是SQL，所以出来的效果完全不一样。

SQL的语法结构非常类似，无论你用的是什么类型的数据库，但在一些细节上，大家的处理是有区别的，我觉得Excel里面和Access里SQL最大区别在于因为我在Excel里面SQL用的是ADO的方式，所以这就意味着虽然我写的是SQL的语法，但实际上那是以字符串的名义存在的东西。在Excel VBA的数据格式里，我写的结构化语言全部都是字符串，但是在Access里，在SQL的查询界面里，那个东西不是字符串。我没有认真看某些单词有没有高亮，因为那是特殊字段又或者是保留字段。当我直接把Access VBA里的那个自定义模块挪到Excel VBA里，发现打开记录集的方式根本不一样，语法不一样。因为在Access里本来就是一个数据库，但在Excel VBA的ADO里是通过一些特殊的语句打开那个记录集的。

回到一开始，为什么我得这么折腾呢？因为一直以来我都发现，从来没有一个人能在Excel VBA+ADO+SQL的模式之下在分组聚合的时候把文本以某些字符去重连接成字符串。要实现这个功能，只能最后把结果输出，然后在VBA里通过字典的处理，再把那些合并好的东西与其它东西结合在一起形成一个新的数组，最后往单元格里面输出，而不能像其它SQL查询结果那样直接就在单元格里全部输出。先输出到字典，然后再用字典合数组合并的难易程度跟那个数据最终的查询结果复杂程度有关。在高端的数据库里，文本聚合连接有直接的函数可以做到，比如在MySQL里面直接group_concat就可以做到，在其它专业数据库里，那个函数的名字各有不同，但都能实现同一个效果，就是把字符聚合拼接。在Power Query里，他们没办法在窗口界面让你实现这个，但可以在高级编辑器里面通过text.combine的方式实现这种功能。在Power Pivot里，concatenatex也能实现这种文本的拼接。让人觉得非常无语的是，都到了Microsoft 365时代，Access这个东西依然是office大家族的一部分，但这种肯定有需求的东西居然没有一个官方函数实现，但你又可以通过在模块里用自定义函数的方式达成。Excel的VBA里不能秒生成这种东西，但在函数层面textjoin+unique+filter可以。为什么就不能在Excel VBA支持的SQL里面出现这个文本拼接的官方函数呢？如果他们真觉得没有必要的话，为什么Power Bi的软件就可以实现呢？我不知道Power Bi软件是一开始就能实现，还是后面慢慢进化出来实现的，反正我第1次看到Power Bi相关软件的时候，他们已经能实现了。

一整天的挣扎下来好像没什么进展，但我在这些问题上又仔细思考了一番。

MySQL 实践

✇anran758's blog
anran758

由于自考的实践考核要求有需要用到 mysql 进行考核，故记录一下在 mac 环境下试手的笔记。

初始环境

首先在 mysql 官网中下载你想要的版本。可以直接下载 dmg 安装包，按照安装指示一步一步安装，并设置 mysql 的密码。

下载完毕后，一般情况下直接通过命令行使用 mysql 命令会找不到对应的命令：

1
2
➜  ~ mysql -v
zsh: command not found: mysql

因此需要对当前的命令行工具配置对应的环境变量，比如笔者使用的是 zsh，则打开 ~/.zshrc 文件添加以下配置:

1
export PATH=${PATH}:/usr/local/mysql/bin/

若使用 bash 的用户同理，直接在 ~/.bashrc 添加相同代码。添加完毕后通过 source 命令重新加载对应的环境变量: source ~/.zshrc

接着就可以在命令行直接使用 mysql 了。输入 mysql -u root -p 登录 mysql，密码是在安装阶段时设置的密码。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
➜  ~ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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>

数据库操作

DATABASE 可以不区分大小写，但只能要么全小写，要么全大写。一般会将这些参数用大写写出。

创建数据库

1
2
3
-- 还可以通过 DEFAULT CHARACTER SET 选项设置默认的编码集
mysql> CREATE DATABASE DANNY_DATABASE;
Query OK, 1 row affected (0.01 sec)

查看现有的数据库

1
2
3
4
5
6
7
8
9
10
11
mysql> SHOW DATABASES;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema         |
| DANNY_DATABASE             |
| mysql                      |
| performance_schema         |
| sys                        |
+----------------------------+
6 rows in set (0.00 sec)

切换到指定数据库

1
mysql> USE DANNY_DATABASE

数据库的查看与删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 创建数据库: 准备稍后移除的数据库
mysql> CREATE DATABASE DANNY_DATABASE_WAIT_DELETE;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema         |
| DANNY_DATABASE             |
| DANNY_DATABASE_WAIT_DELETE |
| mysql                      |
| performance_schema         |
| sys                        |
+----------------------------+
6 rows in set (0.00 sec)

-- 删除数据库
mysql> DROP DATABASE DANNY_DATABASE_WAIT_DELETE;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DANNY_DATABASE     |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

查看当前使用的数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 未选择的情况下
mysql> SELECT DATABASE();
+----------------+
| DATABASE()     |
+----------------+
| null           |
+----------------+
1 row in set (0.00 sec)

-- 切换指定数据库
use DANNY_DATABASE;

mysql> SELECT DATABASE();
+----------------+
| DATABASE()     |
+----------------+
| danny_database |
+----------------+
1 row in set (0.00 sec)

数据表操作

创建数据表

1
2
3
4
5
6
7
8
9
10
-- 创建名为 customers 的数据表
mysql> CREATE TABLE IF NOT EXISTS customers(
    ->   cust_id INT NOT NULL AUTO_INCREMENT,
    ->   cust_name CHAR(50) NOT NULL,
    ->   cust_sex CHAR(1) NOT NULL DEFAULT 0,
    ->   cust_address CHAR(50) NULL,
    ->   cust_contact CHAR(50) NULL,
    ->   PRIMARY KEY(cust_id)
    -> );
Query OK, 0 rows affected (0.11 sec)

其中 IF NOT EXISTS 参数是可选的，它的意思为若 customers 表不存在则创建它。

查看数据表与表列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 查看当前用户在当前数据库中可以访问的数据表
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_danny_database |
+--------------------------+
| customers                |
+--------------------------+
1 rows in set (0.00 sec)

-- 查看指定数据表中列的信息
-- DESC customers;  等价于如下命令
mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+-----------+----------------+
| Field        | Type        | Null | Key | Default   | Extra          |
+--------------+-------------+------+-----+-----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL      | auto_increment |
| cust_name    | char(50)    | NO   |     | NULL      |                |
| cust_sex     | char(1)     | NO   |     | 0         |                |
| cust_address | char(50)    | YES  |     | NULL      |                |
| cust_contact | char(50)    | YES  |     | NULL      |                |
+--------------+-------------+------+-----+-----------+----------------+
5 rows in set (0.00 sec)

删除数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 添加一个数据表用于演示删除
mysql> CREATE TABLE IF NOT EXISTS customers_1(
    ->   cust_id INT NOT NULL AUTO_INCREMENT,
    ->   cust_name CHAR(50) NOT NULL,
    ->   cust_sex CHAR(1) NOT NULL DEFAULT 0,
    ->   cust_address CHAR(50) NULL,
    ->   cust_contact CHAR(50) NULL,
    ->   PRIMARY KEY(cust_id)
    -> );
Query OK, 0 rows affected (0.11 sec)

-- 查看当前的数据表
mysql> SHOW tables;
+--------------------------+
| Tables_in_danny_database |
+--------------------------+
| customers                |
| customers_1              |
+--------------------------+
2 rows in set (0.00 sec)

-- 删除指定数据表
mysql> DROP TABLES customers_1;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW tables;
+--------------------------+
| Tables_in_danny_database |
+--------------------------+
| customers                |
+--------------------------+
1 row in set (0.00 sec)

数据表添加新列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 插入新列
mysql> alter TABLE customers
    -> ADD COLUMN cust_city char(10) NOT NULL DEFAULT 'guangzhou' AFTER cust_sex;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 确认表列状态
mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+-----------+----------------+
| Field        | Type        | Null | Key | Default   | Extra          |
+--------------+-------------+------+-----+-----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL      | auto_increment |
| cust_name    | char(50)    | NO   |     | NULL      |                |
| cust_sex     | char(1)     | NO   |     | 0         |                |
| cust_city    | char(10)    | NO   |     | guangzhou |                |
| cust_address | char(50)    | YES  |     | NULL      |                |
| cust_contact | char(50)    | YES  |     | NULL      |                |
+--------------+-------------+------+-----+-----------+----------------+
6 rows in set (0.00 sec)

数据表修改表列

修改整列: 将列名 cust_sex 修改 sex，并修改默认值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> alter TABLE customers
    -> CHANGE COLUMN cust_sex sex char(1) NULL DEFAULT 'M';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+-----------+----------------+
| Field        | Type        | Null | Key | Default   | Extra          |
+--------------+-------------+------+-----+-----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL      | auto_increment |
| cust_name    | char(50)    | YES  |     | NULL      |                |
| sex          | char(1)     | YES  |     | M         |                |
| cust_city    | char(10)    | NO   |     | guangzhou |                |
| cust_address | char(50)    | YES  |     | NULL      |                |
| cust_contact | char(50)    | YES  |     | NULL      |                |
+--------------+-------------+------+-----+-----------+----------------+
6 rows in set (0.00 sec)

仅修改列的类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> ALTER TABLE customers
    -> MODIFY COLUMN cust_address varchar(50);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show COLUMNS from customers;
+--------------+-------------+------+-----+-----------+----------------+
| Field        | Type        | Null | Key | Default   | Extra          |
+--------------+-------------+------+-----+-----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL      | auto_increment |
| cust_name    | char(50)    | YES  |     | NULL      |                |
| sex          | char(1)     | YES  |     | M         |                |
| cust_city    | char(10)    | NO   |     | guangzhou |                |
| cust_address | varchar(50) | YES  |     | NULL      |                |
| cust_contact | char(50)    | YES  |     | NULL      |                |
+--------------+-------------+------+-----+-----------+----------------+
6 rows in set (0.00 sec)

修改指定列的指定字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> ALTER TABLE customers
    -> ALTER COLUMN cust_city SET DEFAULT 'shenzhen';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+----------+----------------+
| Field        | Type        | Null | Key | Default  | Extra          |
+--------------+-------------+------+-----+----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL     | auto_increment |
| cust_name    | char(50)    | YES  |     | NULL     |                |
| sex          | char(1)     | YES  |     | M        |                |
| cust_city    | char(10)    | NO   |     | shenzhen |                |
| cust_address | varchar(50) | YES  |     | NULL     |                |
| cust_contact | char(50)    | YES  |     | NULL     |                |
+--------------+-------------+------+-----+----------+----------------+
6 rows in set (0.00 sec)

移除数据表列： 移除 cust_contact 数据表项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> ALTER TABLE danny_database.customers
    -> DROP COLUMN cust_contact;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS from customers;
+--------------+-------------+------+-----+----------+----------------+
| Field        | Type        | Null | Key | Default  | Extra          |
+--------------+-------------+------+-----+----------+----------------+
| cust_id      | int(11)     | NO   | PRI | NULL     | auto_increment |
| cust_name    | char(50)    | YES  |     | NULL     |                |
| sex          | char(1)     | YES  |     | M        |                |
| cust_city    | char(10)    | NO   |     | shenzhen |                |
| cust_address | varchar(50) | YES  |     | NULL     |                |
+--------------+-------------+------+-----+----------+----------------+
5 rows in set (0.00 sec)

数据项操作

添加数据

默认情况下在命令行中 mysql 是不能直接插入中文的，这个跟字符集有关。可输入下面命令修改数据库或表的字符集:

1
2
3
4
5
6

-- 设置名为 danny_database 的数据库字符集
ALTER DATABASE danny_database character SET utf8;

-- 设置名为 customers 的数据库表字符集 (Tip: 若数据库已经被设置为 utf8, 则无需再设置表的字符集)
ALTER TABLE customers convert to character SET utf8;

为数据表插入数据，显式设置字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address)
    -> VALUES(901, '张三', DEFAULT, '广州市');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address) 
    -> VALUES(0, '李四', DEFAULT, '广州市');
Query OK, 1 row affected (0.01 sec)

mysql> select * from customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|     901 | 张三      | M    | shenzhen  | 广州市         |
|     902 | 李四      | M    | shenzhen  | 广州市         |
+---------+-----------+------+-----------+--------------+
2 rows in set (0.00 sec)

由于 cust_id 是自增的，因此可以将此字段的值设置为 0 或 NULL 会自动自增。上例 “李四” 的 cust_id 在创建后就被自增为 902。

还可以通过 SET 语句设置部分值：

1
2
mysql> INSERT INTO danny_database.customers SET cust_name='王五', cust_address='武汉市', sex=DEFAULT;
Query OK, 1 row affected (0.00 sec)

查询数据

可通过 SELECT 语句查询数据：

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|     901 | 张三      | M    | shenzhen  | 广州市         |
|     902 | 李四      | M    | shenzhen  | 广州市         |
|     903 | 王五      | M    | shenzhen  | 武汉市         |
+---------+-----------+------+-----------+--------------+
3 rows in set (0.00 sec)

仅展示指定字段：

1
2
3
4
5
6
7
8
+---------+-----------+------+
| cust_id | cust_name | sex  |
+---------+-----------+------+
|     901 | 张三      | M    |
|     902 | 李四      | M    |
|     903 | 王五      | M    |
+---------+-----------+------+
3 rows in set (0.00 sec)

通过 WHERE 子句设置查询条件，筛选出符合查询条件的数据：

1
2
3
4
5
6
7
8
9
mysql> SELECT cust_id,cust_name,cust_address FROM customers
    -> WHERE cust_address="广州市";
+---------+-----------+--------------+
| cust_id | cust_name | cust_address |
+---------+-----------+--------------+
|     901 | 张三      | 广州市         |
|     902 | 李四      | 广州市         |
+---------+-----------+--------------+
2 rows in set (0.00 sec)

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 添加几项测试数据
mysql> INSERT INTO danny_database.customers(cust_id, cust_name, sex, cust_address) 
    -> VALUES(1, 'test1', DEFAULT, '深圳市');
Query OK, 1 row affected (0.02 sec)

mysql> select * from customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|       1 | test1    | M    | shenzhen  | 深圳市         |
|     901 | 张三      | M    | shenzhen  | 广州市         |
|     902 | 李四      | M    | shenzhen  | 广州市         |
|     903 | 王五      | M    | shenzhen  | 武汉市         |
+---------+-----------+------+-----------+--------------+
4 rows in set (0.00 sec)

-- 删除表数据
mysql> DELETE FROM customers
    -> WHERE cust_id=1;
Query OK, 1 row affected (0.02 sec)


mysql> select * from customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|     901 | 张三      | M    | shenzhen  | 广州市         |
|     902 | 李四      | M    | shenzhen  | 广州市         |
|     903 | 王五      | M    | shenzhen  | 武汉市         |
+---------+-----------+------+-----------+--------------+

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 更新数据
mysql> UPDATE customers SET cust_address="深圳市" WHERE cust_name="李四";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM customers;
+---------+-----------+------+-----------+--------------+
| cust_id | cust_name | sex  | cust_city | cust_address |
+---------+-----------+------+-----------+--------------+
|     901 | 张三      | M    | shenzhen  | 广州市       |
|     902 | 李四      | M    | shenzhen  | 深圳市       |
|     903 | 王五      | M    | shenzhen  | 武汉市       |
+---------+-----------+------+-----------+--------------+
3 rows in set (0.00 sec)

实践

以一个 eShop 的需求为例做个简单的测试吧。

创建 eshop 数据库

在 MySQL 中创建一个名为 eshop 的数据库，选择字符集为 utf8mb4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> CREATE DATABASE IF NOT EXISTS eshop DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DANNY_DATABASE     |
| eshop              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

-- 切换数据库
mysql> use eshop;
Database changed

创建数据表及相关记录

相关表信息如下

表名：用户(t_user)

字段名类型大小
用户ID (id)自增类型
姓名 (user_name)文本50，非空
联系电话 (phone_no)文本20，非空

表名：商品（product）

字段名类型大小
商品ID（id）自增类型
商品名称(product_name)文本50，非空
价格(price)数值类型（整数位9位，小数位2位），非空

表名：购物车 (shopping_cart)

字段名类型大小
用户id（user_id）整数非空，主键，参考用户表主键
商品id(product_id)整数非空，主键，参考商品表主键
商品数量(quantity)整数非空
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 用户表
mysql> CREATE TABLE IF NOT EXISTS t_user(
    ->   `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `user_name` CHAR(50) NOT NULL,
    ->   `phone_no` CHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.06 sec)

-- 商品表
mysql> CREATE TABLE IF NOT EXISTS product(
    ->   `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `product_name` CHAR(50) NOT NULL,
    ->   `price` DOUBLE(9, 2)
    -> );
Query OK, 0 rows affected (0.06 sec)

-- 购物车
mysql> CREATE TABLE IF NOT EXISTS shopping_cart(
    ->   `user_id` INT NOT NULL,
    ->   `product_id` INT NOT NULL,
    ->   `quantity` INT NOT NULL,
    ->   PRIMARY KEY(`user_id`, `product_id`)
    -> );
Query OK, 0 rows affected (0.05 sec)

-- 查看数据表
mysql> show tables;
+-----------------+
| Tables_in_eshop |
+-----------------+
| product         |
| shopping_cart   |
| t_user          |
+-----------------+
3 rows in set (0.00 sec)

录入用户数据

用户信息

1
2
3
4
1;张三; 13333333333;
2;李四; 13666666666
3;王五; 13888888888
4;赵六; 13999999999

商品信息

1
2
3
1; C++程序设计教程; 45.5
2; 数据结构; 33.7
3; 操作系统; 51

购物车

1
2
3
4
1; 1; 5
1; 2; 3
2; 3; 6
2; 4; 8

录入数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- 插入用户表数据
mysql> INSERT INTO t_user
    ->   (id, user_name, phone_no)
    -> VALUES
    ->   (1, '张三', '13333333333'),
    ->   (2, '李四', '13666666666'),
    ->   (3, '王五', '13888888888'),
    ->   (4, '赵六', '13999999999');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_user;
+----+-----------+-------------+
| id | user_name | phone_no    |
+----+-----------+-------------+
|  1 | 张三      | 13333333333 |
|  2 | 李四      | 13666666666 |
|  3 | 王五      | 13888888888 |
|  4 | 赵六      | 13999999999 |
+----+-----------+-------------+
4 rows in set (0.00 sec)

-- 插入「商品信息」
mysql> INSERT INTO product
    ->   (id, product_name, price)
    -> VALUES
    ->   (1, 'C++程序设计教程', 45.5),
    ->   (2, '数据结构', 33.7),
    ->   (3, '操作系统', 51);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM product;
+----+-----------------------+-------+
| id | product_name          | price |
+----+-----------------------+-------+
|  1 | C++程序设计教程       | 45.50 |
|  2 | 数据结构              | 33.70 |
|  3 | 操作系统              | 51.00 |
+----+-----------------------+-------+
3 rows in set (0.00 sec)

-- 插入购物车
mysql> INSERT INTO shopping_cart
    ->   (user_id, product_id, quantity)
    -> VALUES
    ->   (1, 1, 5),
    ->   (1, 2, 3),
    ->   (2, 3, 6),
    ->   (2, 4, 8);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM shopping_cart;
+---------+------------+----------+
| user_id | product_id | quantity |
+---------+------------+----------+
|       1 |          1 |        5 |
|       1 |          2 |        3 |
|       2 |          3 |        6 |
|       2 |          4 |        8 |
+---------+------------+----------+
4 rows in set (0.00 sec)

数据的查询与更新

使用 SQL 语句列出「张三」购买商品清单信息，以购买数量升序排列：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT u.user_name, p.product_name, u.phone_no, p.price, s.quantity FROM t_user u, product p, shopping_cart s
    -> WHERE u.user_name="张三" AND u.id = s.user_id  AND p.id = s.product_id 
    -> ORDER BY quantity asc
    -> LIMIT 100;
+-----------+-----------------------+-------------+-------+----------+
| user_name | product_name          | phone_no    | price | quantity |
+-----------+-----------------------+-------------+-------+----------+
| 张三      | 数据结构              | 13333333333 | 33.70 |        3 |
| 张三      | C++程序设计教程       | 13333333333 | 45.50 |        5 |
+-----------+-----------------------+-------------+-------+----------+
2 rows in set (0.01 sec)

使用 SQL 语句选出李四购买商品的总价：

1
2
3
4
5
6
7
8
9
mysql> SELECT u.user_name, p.product_name, p.price, s.quantity, p.price*s.quantity AS total_price FROM t_user u, product p, shopping_cart s
    -> WHERE u.user_name="李四" AND u.id = s.user_id  AND p.id = s.product_id 
    -> LIMIT 100;
+-----------+--------------+-------+----------+-------------+
| user_name | product_name | price | quantity | total_price |
+-----------+--------------+-------+----------+-------------+
| 李四      | 操作系统     | 51.00 |        6 |      306.00 |
+-----------+--------------+-------+----------+-------------+
1 row in set (0.00 sec)

使用 SQL 语句列出购买数量排前两位的商品名称：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT p.product_name, p.price, s.quantity FROM product p, shopping_cart s
    -> WHERE p.id = s.product_id
    -> ORDER BY quantity desc
    -> LIMIT 2;
+-----------------------+-------+----------+
| product_name          | price | quantity |
+-----------------------+-------+----------+
| 操作系统              | 51.00 |        6 |
| C++程序设计教程       | 45.50 |        5 |
+-----------------------+-------+----------+
2 rows in set (0.00 sec)

忘记密码

若忘记数据库密码后可通过 mysqld_safe 来修改密码：

  1. 在系统偏好设置中关闭 mysql 服务

  2. 打开终端，输入命令:

    1
    2
    ➜  ~ cd /usr/local/mysql/bin
    ➜  ~ sudo su

  3. 命令行变成以 sh-3.2# 开头后继续输入命令:

    1
    2
    3
    4
    sh-3.2# ./mysqld_safe --skip-grant-tables &

    mysqld_safe Logging to '/usr/local/mysql/data/DannydeMBP.err'. 
    mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

  4. 新开个命令行窗口，进入 mysql:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    ➜  ~ /usr/local/mysql/bin/mysql

    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 30
    Server version: 5.7.31

    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

    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> use mysql

    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed

  5. 更新密码

    1
    2
    3
    4
    mysql> update user set authentication_string=password('admin') where Host='localhost' and User='root';

    Query OK, 1 row affected, 1 warning (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 1

  6. 输入 exit 命令退出 mysql，查出 mysqld_safe 进程号并杀掉:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> exit
    Bye

    ➜  ~ ps -ax | grep mysql
      8553 ttys004    0:00.03 /bin/sh ./mysqld_safe --skip-grant-tables
      8623 ttys004    0:00.92 /usr/local/mysql-5.7.31-macos10.14-x86_64/bin/mysqld --basedir=/usr/local/mysql-5.7.31-macos10.14-x86_64 --datadir=/usr/local/mysql-5.7.31-macos10.14-x86_64/data --plugin-dir=/usr/local/mysql-5.7.31-macos10.14-x86_64/lib/plugin --user=mysql --skip-grant-tables --log-error=host-3-187.can.danny1.network.err --pid-file=host-3-187.can.danny1.network.pid

    # 杀掉 mysql 的进程
    ➜  ~ kill -9 8553
    ➜  ~ kill -9 8623

  7. 此时返回系统偏好设置中看到 mysql 被关闭后就算正确退出了。接着继续输入 mysql -u root -p 命令连接数据库，再输入刚才修改的密码即可。

参考资料

MySQL 大批量插入，如何过滤掉重复数据

✇鸟不拉诗
鸟不拉诗

线上库有6个表存在重复数据，其中2个表比较大，一个96万+、一个30万+，因为之前处理过相同的问题，就直接拿来了上次的Python去重脚本，脚本很简单，就是连接数据库，查出来重复数据，循环删除。

emmmm，但是这个效率嘛，实在是太低了，1秒一条，重复数据大约

❌