CREATE DATABASE test;
use test;
CREATE TABLE hotels (
hotel_id INT ,
hotel_name VARCHAR(100),
city VARCHAR(50),
hotel_address VARCHAR(200),
star_level INT ,
rating DECIMAL(3,1),
min_price DECIMAL(10,2),
max_price DECIMAL(10,2)
);
create table customers(
customer_id INT,
customer_name VARCHAR(50) ,
gender CHAR(1),
phone VARCHAR(20),
id_card VARCHAR(18),
birth_date DATE
);
create table check_ins(
check_in_id INT ,
hotel_id INT,
customer_id INT ,
check_in_date DATE,
check_out_date DATE,
daily_price DECIMAL(10,2),
discount DECIMAL(3,2),
actual_amount DECIMAL(10,2)
);
INSERT INTO hotels VALUES
(101, '南京古南都饭店', '南京', '鼓楼区广州路208号', 5, 4.5, 600.00, 1200.00),
(102, '桔子酒店(北京中关村店)', '北京', '海淀区中关村南大街5号', 3, 4.2, 300.00, 500.00),
(103, '如家(南京新街口店)', '南京', '秦淮区中山南路100号', 3, 4.0, 280.00, 450.00),
(104, '北京四季酒店', '北京', '朝阳区亮马桥路48号', 5, 4.7, 1200.00, 3000.00),
(105, '桔子水晶(上海外滩店)', '上海', '黄浦区中山东一路33号', 4, 4.3, 500.00, 900.00);
INSERT INTO customers VALUES
(201, '潘叶', 'F', '13812345678', '320102199003071234', '1990-03-07'),
(202, '卢城刚', 'M', '13987654321', '320103198511152345', '1985-11-15'),
(203, '王小明', 'M', '13711112222', '110105199510203456', '1995-10-20'),
(204, '刘芳', 'F', '13655556666', '310104200801014567', '2008-01-01'),
(205, '王磊', 'M', '13599998888', '320104199208156789', '1992-08-15');
INSERT INTO check_ins VALUES
(301, 101, 201, '2017-06-10', '2017-06-15', 800.00, 0.90, 3600.00),
(302, 102, 202, '2017-06-20', '2017-06-22', 400.00, 0.85, 680.00),
(303, 103, 201, '2018-03-01', '2018-03-05', 350.00, 0.95, 1330.00),
(304, 104, 203, '2017-09-15', '2017-09-18', 1500.00, 0.88, 3960.00),
(305, 105, 204, '2017-11-10', '2017-11-12', 600.00, 0.92, 1104.00);
-- SELECT * FROM hotels;
-- SELECT * FROM customers;
-- SELECT * FROM check_ins;
-- select * from hotels
-- where star_level in (3,4) and rating =
-- (SELECT max(rating) FROM hotels
-- where star_level in (3,4));
-- select count(*) as '北京三星酒店数量' from hotels
-- where city='北京'and star_level=3;
-- select left(customer_name,1) as 姓氏,count(*) as 人数 from customers
-- where customer_name like '王%' or customer_name like '刘%'
-- group by 姓氏
-- order by 人数 desc;
-- select
-- case when exists(
-- select * from customers
-- where timestampdiff (year,birth_date,current_date) < 18
-- ) then '有未满18岁的客户'
-- else'没有未满18岁的客户'
-- end as 检查结果;
-- select * from hotels
-- where star_level = 5 order by max_price desc limit 1;
-- -- 方法1：使用JOIN连接
-- SELECT DISTINCT *
-- FROM customers c
-- left JOIN check_ins ci ON c.customer_id = ci.customer_id
-- WHERE ci.check_in_date BETWEEN '2017-06-01' and '2017-06-30';
-- select * from customers where customer_id in (
-- SELECT customer_id from check_ins where check_in_date between '2017-06-01' and '2017-06-30');
-- -- 方法2：使用子查询
-- SELECT *
-- FROM customers
-- WHERE customer_id IN (
-- SELECT DISTINCT customer_id
-- FROM check_ins
-- WHERE check_in_date BETWEEN '2017-06-01' AND '2017-06-30'
-- );
-- -- 方法3：使用EXISTS
-- SELECT *
-- FROM customers c
-- WHERE EXISTS (
-- SELECT 1
-- FROM check_ins ci
-- WHERE ci.customer_id = c.customer_id
-- AND ci.check_in_date BETWEEN '2017-06-01' AND '2017-06-30'
-- );
-- update hotels
-- set min_price=280*0.95 where hotel_name='如家（南京新街口店）';
-- select * from hotels;
-- select
-- case
-- when month(birth_date) in (3,4,5) then '春季'
-- when month(birth_date) in (9,10,11) then '秋季'
-- end as 季节,
-- count(*) as 人数
-- from customers
-- WHERE
-- MONTH(birth_date) IN (3, 4, 5, 9, 10, 11)
-- GROUP BY
-- CASE
-- WHEN MONTH(birth_date) IN (3, 4, 5) THEN '春季'
-- WHEN MONTH(birth_date) IN (9, 10, 11) THEN '秋季'
-- END;
-- select
-- h.hotel_name,
-- h.star_level,
-- ch.check_in_date,
-- ch.check_out_date
-- from
-- check_ins as ch
-- left join hotels as h on h.hotel_id = ch.hotel_id
-- left join customers as cu on cu.customer_id = ch.customer_id
-- where
-- cu.customer_name='潘叶' and year(ch.check_in_date)=2017 ;
-- 更新如家(南京新街口店)在2018年3月2日的折扣
-- UPDATE check_ins ci
-- JOIN hotels h ON ci.hotel_id = h.hotel_id
-- SET ci.discount = ci.discount * 0.95 -- 原折扣基础上再打九五折
-- WHERE h.hotel_name = '如家(南京新街口店)'
-- AND ci.check_in_date = '2018-03-02';
-- select * from check_ins;
-- update check_ins as ch
-- join hotels as h on h.hotel_id = ch.check_in_date
-- set ch.discount = ch.discount*0.95
-- where h.hotel_name like '如家%' and ch.check_in_date='2018-03-02';
-- select * from check_ins;
-- select star_level as 星级,count(*) as 数量 from hotels
-- group by star_level
-- order by 数量
-- ;
-- 方法3：分别查询后合并
-- SELECT CONCAT(star_level, '星级') AS 酒店类型, COUNT(*) AS 酒店数量
-- FROM hotels
-- GROUP BY star_level
-- UNION ALL
-- SELECT '总计', COUNT(*)
-- FROM hotels
-- ORDER BY
-- CASE WHEN 酒店类型 = '总计' THEN 999
-- ELSE CAST(SUBSTRING(酒店类型, 1, 1) AS SIGNED)
-- END;
-- select h.city as 城市名称,round(sum(ci.daily_price*DATEDIFF(ci.check_out_date,ci.check_in_date)*ci.discount),2) as 总收入
-- from hotels as h
-- join check_ins as ci on h.hotel_id = ci.hotel_id
-- where h.city in ('北京','南京') and year(check_in_date) = 2017 and year(check_out_date)=2017
-- group by 城市名称
-- order by 总收入
-- ;
-- select cu.customer_name as 姓名,count( ci.hotel_id) as 酒店数
-- from check_ins as ci
-- left join customers as cu on cu.customer_id = ci.customer_id
-- group by 姓名
-- ORDER BY
-- CASE WHEN COUNT(DISTINCT ci.hotel_id) IS NULL THEN 1 ELSE 0 END,
-- 酒店数 DESC;
-- select h.hotel_name as 酒店名称 from hotels as h
-- join check_ins as ci on h.hotel_id = ci.hotel_id
-- join customers as cu on cu.customer_id = ci.customer_id
-- where cu.customer_name='潘叶'
-- and h.hotel_id not in (
-- select h1.hotel_id from hotels as h1
-- join check_ins as ci1 on h1.hotel_id = ci1.hotel_id
-- join customers as cu1 on cu1.customer_id = ci1.customer_id
-- where cu1.customer_name='卢城刚');
SELECT DISTINCT
h.hotel_name AS 酒店名称
FROM
hotels h
WHERE
h.hotel_id IN (
SELECT ci.hotel_id
FROM check_ins ci
GROUP BY ci.hotel_id, ci.customer_id
HAVING COUNT(*) > 1 -- 同一客户多次入住同一酒店
);
select