Anka

Announcement

Welcome to my blog!

标签

650 字

3 分钟

Databases Week7 Seminar

2025-11-13

Databases

SQL

Modelling and Loading#

Exercise 1 – Considering the `CustomerDB.csv` file from the last lecture.#

a. Optimize the storage of the database by refining the structure of brookes.customers in terms of field data types.

1
CREATE TABLE brookes.customers2 (
2
    id INT PRIMARY KEY AUTO_INCREMENT,
3
    first_name VARCHAR(50) NOT NULL,
4
    last_name VARCHAR(50) NOT NULL,
5
    full_name VARCHAR(100),
6
    cinema VARCHAR(100) NOT NULL,
7
    film VARCHAR(100) NOT NULL,
8
    tickets INT NOT NULL CHECK (tickets >= 1),
9
    show_date DATE NOT NULL,
10
    show_time TIME NOT NULL
11
);

1
LOAD DATA local INFILE '/Users/anka/Documents/成都理工大学/2025-2026/Databases/Week7/customerdb_1.csv'
2
INTO TABLE brookes.customers2
3
CHARACTER SET latin1
4
FIELDS TERMINATED BY ','              -- 字段分隔符
5
OPTIONALLY ENCLOSED BY '"'            -- 字段可能被引号包围
6
LINES TERMINATED BY '\r\n'            -- 每行结束符
7
IGNORE 1 LINES                        -- 跳过表头
8
(first_name, last_name, full_name, cinema, film, tickets, @date_str, @time_str)
9
SET
10
  show_date = STR_TO_DATE(@date_str, '%d/%m/%Y'),  -- 转换日期格式
11
  show_time = TRIM(@time_str);

NOTE
典型报错： ⚠️ 4096 Delimiter '\r' in position 8 in datetime value '20:00:00 ' at row 1 is superfluous and is deprecated. Please remove.
说明在导入的 CSV 文件中，时间字段（20:00:00）后面有一个多余的 回车符 \r 或空格，MySQL 在解析时认为这是多余的分隔符。

原因分析：

原因	说明
① Windows 生成的 CSV 文件	Windows 使用 `\r\n` 作为换行符，而不是 Linux 的 `\n`。MySQL 会把 `\r` 误认为字段内容的一部分。
② CSV 文件的最后一列（`Time`）后面有空格	例如 `"20:00:00 "`，MySQL 会读取到空格或 `\r`。

解决方法：
修改 LOAD DATA 命令
显式指定行结束符为 '\r\n'，并使用 TRIM() 去除多余空格。
🔹 TRIM(@time_str)：自动去除末尾空格或 \r
🔹 LINES TERMINATED BY '\r\n'：兼容 Windows 格式的 CSV 文件

b. Express the answer to the following questions using the proper SQL queries.

How many customers?

1
SELECT
2
  count(*)
3
FROM
4
  brookes.customers2;

How many tickets are sold?

1
SELECT
2
  sum(Tickets)
3
FROM
4
  brookes.customers2;

Most popular cinema?

Show cinema name and count (The cinema that appears most frequently in the database):

1
SELECT
2
  Cinema, COUNT(*) as Count
3
FROM
4
  brookes.customers2
5
GROUP BY
6
  Cinema
7
order by
8
  count(Cinema) desc
9
limit 1;

Only show cinema name:

1
SELECT
2
  Cinema,
3
FROM
4
  brookes.customers2
5
GROUP BY
6
  Cinema
7
order by
8
  count(Cinema) desc
9
limit 1;

⭐The cinema with the most tickets sold:

1
SELECT
2
    cinema,
3
    SUM(tickets) AS total_tickets
4
FROM
5
    brookes.customers2
6
GROUP BY
7
    cinema
8
ORDER BY
9
    total_tickets DESC
10
LIMIT 1;

Most popular film?

The movie that appears most frequently in the database:

1
SELECT
2
  Film, COUNT(*) as Count
3
FROM
4
  brookes.customers2
5
GROUP BY
6
  Film
7
order by
8
  count(Film) desc
9
limit 1;

⭐The movie with the highest number of ticket sales:

1
SELECT
2
    Film,
3
    SUM(tickets) AS total_tickets
4
FROM
5
    brookes.customers2
6
GROUP BY
7
    Film
8
ORDER BY
9
    total_tickets DESC
10
LIMIT 1;

Most popular date?

1
SELECT
2
  show_date, COUNT(*) as Count
3
FROM
4
  brookes.customers2
5
GROUP BY
6
  show_date
7
order by
8
  count(show_date) desc
9
limit 1;

Most popular day of the week?

1
SELECT
2
    DAYNAME(show_date) AS weekday,
3
    SUM(tickets) AS total_tickets
4
FROM
5
    brookes.customers2
6
GROUP BY
7
    weekday
8
ORDER BY
9
    total_tickets DESC;

Exercise 2 – Create another two tables depending on the Films and Geo CSV files.#

films

1
CREATE TABLE brookes.films (
2
    id INT PRIMARY KEY AUTO_INCREMENT,
3
    movie VARCHAR(100) NOT NULL,
4
    certificate VARCHAR(10),
5
    duration INT NOT NULL
6
);

1
LOAD DATA local INFILE '/Users/anka/Documents/成都理工大学/2025-2026/Databases/Week7/films_1.csv'
2
INTO TABLE brookes.films
3
CHARACTER SET latin1
4
FIELDS TERMINATED BY ','
5
ENCLOSED BY '"'
6
LINES TERMINATED BY '\n'
7
IGNORE 1 LINES
8
(movie, certificate, duration);

geo

1
CREATE TABLE brookes.geo (
2
    id INT AUTO_INCREMENT PRIMARY KEY,
3
    city VARCHAR(100) NOT NULL,
4
    region VARCHAR(100),
5
    country VARCHAR(100) NOT NULL
6
);

1
LOAD DATA local INFILE '/Users/anka/Documents/成都理工大学/2025-2026/Databases/Week7/geo_1.csv'
2
INTO TABLE brookes.geo
3
CHARACTER SET latin1
4
FIELDS TERMINATED BY ','
5
ENCLOSED BY '"'
6
LINES TERMINATED BY '\n'
7
IGNORE 1 LINES
8
(city, region, country);

Databases Week7 Seminar

https://mizuki.anka2.top/posts/l5-db-week7-seminar/

作者

🐦‍🔥不死鸟Anka

发布于

2025-11-13

许可协议

MIT

部分信息可能已经过时

Databases Week8 Lecture

Software Development with C and Cpp Week7 Seminar

Hi~ I'm Anka!

Modelling and Loading#

Exercise 1 – Considering the CustomerDB.csv file from the last lecture.#

Exercise 2 – Create another two tables depending on the Films and Geo CSV files.#

Exercise 1 – Considering the `CustomerDB.csv` file from the last lecture.#