sakila

标题：使用MYSQL简单处理sakila数据库

作业目的：发现最具潜力的客户，并发送感谢邮件。

客户表

customer_id: A surrogate primary key used to uniquely identify each customer in the table.

store_id: A foreign key identifying the customer's “home store.” Customers are not limited to renting only from this store, but this is the store they generally shop at.

first_name: The customer's first name.

last_name: The customer's last name.

email: The customer's email address.

address_id: A foreign key identifying the customer's address in the address table.

active: Indicates whether the customer is an active customer. Setting this to FALSE serves as an alternative to deleting a customer outright. Most queries should have a WHERE active = TRUE clause.

create_date: The date the customer was added to the system. This date is automatically set using a trigger during an INSERT.

last_update: The time that the row was created or most recently updated.

查询语句

SELECT customer_id,store_id,first_name,last_name,email,address_id,active,create_date,last_update
FROM
    `sakila`.`customer`;

客户表

付款表：

payment_id: A surrogate primary key used to uniquely identify each payment.

customer_id: The customer whose balance the payment is being applied to. This is a foreign key reference to the customer table.

staff_id: The staff member who processed the payment. This is a foreign key reference to the staff table.

rental_id: The rental that the payment is being applied to. This is optional because some payments are for outstanding fees and may not be directly related to a rental.

amount: The amount of the payment.

payment_date: The date the payment was processed.

last_update: The time that the row was created or most recently updated.

查询语句

SELECT 
    payment_id,customer_id,staff_id,rental_id,amount,payment_date,last_update
FROM
    `sakila`.`payment`;

付款表

统计rental_id项总和最高的十位客户返回email。

SELECT 
   payment.customer_id,sum(rental_id),email
FROM
    `sakila`.`payment`,`sakila`.`customer`
    where payment.customer_id=customer.customer_id
    group by payment.customer_id
   order by sum(rental_id) DESC limit 10
 ;

上图所示为最具潜力的十位客户，以及他们的邮箱。最后发送感谢邮件即可。