1
-- 添加自增主键 id
2
ALTER TABLE brookes.customers_copy
3
ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT FIRST;
4

5
-- 修改 first_name 字段（长度、非空约束）
6
ALTER TABLE brookes.customers_copy
7
MODIFY COLUMN First_Name VARCHAR(50) NOT NULL;
8

9
-- 修改 last_name 字段（长度、非空约束）
10
ALTER TABLE brookes.customers_copy
11
MODIFY COLUMN Last_Name VARCHAR(50) NOT NULL;
12

13
-- 修改 full_name 字段长度（保持可为空）
14
ALTER TABLE brookes.customers_copy
15
MODIFY COLUMN Full_Name VARCHAR(100);
16

17
-- 修改 cinema 字段（添加非空约束）
18
ALTER TABLE brookes.customers_copy
19
MODIFY COLUMN Cinema VARCHAR(100) NOT NULL;
20

21
-- 修改 film 字段（添加非空约束）
22
ALTER TABLE brookes.customers_copy
23
MODIFY COLUMN Film VARCHAR(100) NOT NULL;
24

25
-- 修改 tickets 字段（类型改为 INT、非空约束、检查约束）
26
ALTER TABLE brookes.customers_copy
27
MODIFY COLUMN Tickets INT NOT NULL CHECK (tickets >= 1);
28

29
-- 禁用安全更新模式
30
SET sql_safe_updates = 0;
31

32
-- 将字符串日期（DD/MM/YYYY）转换为 DATE 类型格式（YYYY-MM-DD）
33
UPDATE brookes.customers_copy
34
SET Showing_Date = STR_TO_DATE(Showing_Date, '%d/%m/%Y');
35

36
-- 修改 Showing_Date 字段（类型改为 DATE、非空约束、重命名）
37
ALTER TABLE brookes.customers_copy
38
CHANGE COLUMN Showing_Date show_date DATE NOT NULL;
39

40
-- 修改 Showing_Time 字段（类型改为 TIME、非空约束、重命名）
41
ALTER TABLE brookes.customers_copy
42
CHANGE COLUMN Showing_Time show_time TIME NOT NULL;

1
-- 检查无效的日期格式（转换后为 NULL 的记录）
2
SELECT Showing_Date
3
FROM customers
4
WHERE STR_TO_DATE(Showing_Date, '%d/%m/%Y') IS NULL;

1
ALTER table customers
2
MODIFY First_Name varchar(40),
3
MODIFY Last_Name varchar(40),
4
MODIFY Full_Name varchar(50),
5
MODIFY Cinema varchar(15),
6
MODIFY Film varchar(45),
7
MODIFY Tickets int,
8
MODIFY Showing_Date date,
9
MODIFY Showing_Time time

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;

1
select cinema_name , max(cinema_count) from
2
(select cinema as cinema_name, count(cinema) as cinema_count from customers
3
group by cinema order by cinema_count desc) as tab1;

1
select cin from
2
(select cinema_name as cin, max(cinema_count) from
3
(select cinema as cinema_name, count(cinema) as cinema_count from customers
4
group by cinema order by cinema_count desc)
5
as tab1)
6
as tab2;

1
select cinema_name from
2
(select cinema as cinema_name, count(cinema) as cinema_count from customers
3
group by cinema order by cinema_count desc limit 1)
4
as tab1;

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;

1
select F from
2
(select film_name as F, max(film_count) from
3
(select film as film_name, count(film) as film_count from customers group by film order
4
by film_count desc)
5
as tab1)
6
as tab2;

1
select film_name from
2
(select film as film_name, count(film) as film_count from customers group by film order
3
by film_count desc limit 1)
4
as tab1;

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;

1
select d from
2
(select sh_date as d, max(sh_date_count) from
3
(select showing_date as sh_date, count(showing_date) as sh_date_count from
4
customers group by showing_date order by sh_date_count desc)
5
as tab1) as tab2;

1
select d from
2
(select sh_date as d, max(tickets_count) from
3
(select showing_date as sh_date, sum(tickets) as tickets_count from customers group by
4
showing_date order by tickets_count desc)
5
as tab1) as tab2;

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;

1
select dayname(d) from
2
(select sh_date as d, max(tickets_count) from
3
(select showing_date as sh_date, sum(tickets) as tickets_count from customers group
4
by showing_date order by tickets_count desc)
5
as tab1) as tab2

1
SELECT count(DISTINCT Cinema) from brookes.customers_copy;

1
select count(distinct cinema) from customers;

1
SELECT sum(Tickets) FROM brookes.customers_copy WHERE Full_name="Ali, Jonathan";

1
select sum(tickets) from customers where full_name='Ali, Jonathan';

1
select sum(tickets) from customers where first_name='Jonathan' and last_name='Ali';

1
select sum(tickets) from customers where first_name='Jonathan' and last_name='Ali'
2
or first_name='ALi' and last_name='Jonathan' ;