You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
119 lines
2.4 KiB
119 lines
2.4 KiB
-- 比较条件
|
|
|
|
-- 查询所有活跃状态的学生
|
|
SELECT *
|
|
FROM student
|
|
WHERE account_id IN (
|
|
SELECT account_id
|
|
FROM account
|
|
WHERE status = '活跃'
|
|
);
|
|
|
|
-- 查询所有非管理员账户
|
|
SELECT *
|
|
FROM account
|
|
WHERE type <> '管理员';
|
|
|
|
-- 集合比较
|
|
|
|
-- 查询在多个场地有过预约的学生
|
|
SELECT s.student_id, s.name
|
|
FROM student s
|
|
JOIN appointment a ON s.student_id = a.student_id
|
|
GROUP BY s.student_id, s.name
|
|
HAVING COUNT(DISTINCT a.venue_id) > 1;
|
|
|
|
-- 查询同时是教练和学生的人员
|
|
SELECT p.info_id, p.phone, p.email
|
|
FROM personal_info p
|
|
WHERE p.info_id IN (SELECT student_id FROM student)
|
|
AND p.info_id IN (SELECT personal_info_id FROM coach);
|
|
|
|
-- 范围比较
|
|
|
|
-- 查询2023年10月1日到2023年10月31日期间的反馈
|
|
SELECT *
|
|
FROM feedback
|
|
WHERE date BETWEEN '2023-10-01' AND '2023-10-31';
|
|
|
|
-- 查询容量大于50的场地
|
|
SELECT *
|
|
FROM venue
|
|
WHERE capacity > 50;
|
|
|
|
-- 字符串相似比较
|
|
|
|
-- 查询名字以“张”开头的学生
|
|
SELECT *
|
|
FROM student
|
|
WHERE name LIKE '张%';
|
|
|
|
-- 查询电子邮件包含“example.com”的个人信息
|
|
SELECT *
|
|
FROM personal_info
|
|
WHERE email LIKE '%example.com%';
|
|
|
|
-- 多表连接
|
|
|
|
-- 查询每个学生的最新反馈
|
|
SELECT f.feedback_id, f.content, f.date, s.student_id, s.name
|
|
FROM feedback f
|
|
JOIN training_record tr ON f.feedback_id = tr.feedback_id
|
|
JOIN student s ON tr.student_id = s.student_id
|
|
WHERE (s.student_id, f.date) IN (
|
|
SELECT student_id, MAX(date)
|
|
FROM feedback f2
|
|
JOIN training_record tr2 ON f2.feedback_id = tr2.feedback_id
|
|
GROUP BY student_id
|
|
);
|
|
|
|
-- 查询每个教练负责的所有学生
|
|
SELECT c.coach_id, c.name AS coach_name, s.student_id, s.name AS student_name
|
|
FROM coach c
|
|
JOIN appointment a ON c.coach_id = a.coach_id
|
|
JOIN student s ON a.student_id = s.student_id
|
|
ORDER BY c.coach_id, s.student_id;
|
|
|
|
-- 嵌套查询
|
|
|
|
-- 查询有超过一次预约的学生
|
|
SELECT student_id, name
|
|
FROM student
|
|
WHERE student_id IN (
|
|
SELECT student_id
|
|
FROM appointment
|
|
GROUP BY student_id
|
|
HAVING COUNT(*) > 1
|
|
);
|
|
|
|
-- 查询没有任何反馈的预约
|
|
SELECT *
|
|
FROM appointment
|
|
WHERE appointment_id NOT IN (
|
|
SELECT appointment_id
|
|
FROM feedback
|
|
);
|
|
|
|
-- EXISTS 查询
|
|
|
|
-- 查询有预约记录的学生
|
|
SELECT *
|
|
FROM student s
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM appointment a
|
|
WHERE a.student_id = s.student_id
|
|
);
|
|
|
|
-- 查询有训练记录的教练
|
|
SELECT *
|
|
FROM coach c
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM training_record tr
|
|
WHERE tr.coach_id = c.coach_id
|
|
);
|
|
|
|
|
|
|