sql查询查看人员表中的最高积分和最低积分。在人员表中添加年龄字段并为其添加相应的值。统计人员表中人的平均年龄。统计人员表中未婚、男的人数。统计人员表中各类文化程度的人员数显示文化程度、人数。统计住宿表中每个宾馆的平均入住天数。查询住宿表中住宿次数最多的人显示身份证号和住宿次数。查询2019年11、12两个月的人员住宿超过3次含3次的信息显示身份证号、住宿次数。查询同一天登记住宿2次含2次以上的人

1. 最高积分和最低积分查询：

SELECT MAX(score) AS highest_score, MIN(score) AS lowest_score FROM personnel;

2. 添加年龄字段并赋值：

ALTER TABLE personnel ADD COLUMN age INT; UPDATE personnel SET age = 25 WHERE id = 1; -- 例如，将id为1的人员年龄设置为25岁

3. 统计平均年龄：

SELECT AVG(age) AS average_age FROM personnel;

4. 统计未婚、男性人数：

SELECT COUNT(*) AS unmarried_male_count FROM personnel WHERE marital_status = '未婚' AND gender = '男';

5. 统计各类文化程度的人员数：

SELECT education, COUNT(*) AS count FROM personnel GROUP BY education;

6. 统计每个宾馆的平均入住天数：

SELECT hotel_name, AVG(stay_days) AS average_stay_days FROM accommodation GROUP BY hotel_name;

7. 查询住宿次数最多的人：

SELECT id_card_number, COUNT(*) AS stay_count FROM accommodation GROUP BY id_card_number ORDER BY stay_count DESC LIMIT 1;

8. 查询2019年11、12两个月的人员住宿超过3次的信息：

SELECT id_card_number, COUNT(*) AS stay_count FROM accommodation WHERE check_in_time BETWEEN '2019-11-01' AND '2019-12-31' GROUP BY id_card_number HAVING stay_count >= 3;

9. 查询同一天登记住宿2次以上的人：

SELECT id_card_number, COUNT() AS stay_count FROM accommodation GROUP BY id_card_number, check_in_time HAVING COUNT() >= 2