麻烦老师指正

CREATE TABLE IF NOT EXISTS hobby(
	id INT PRIMARY KEY AUTO_INCREMENT,
	hobby VARCHAR(30),
)

INSERT into hobby(hobby) VALUES ('健身');
INSERT into hobby(hobby) VALUES ('骑行');
INSERT into hobby(hobby) VALUES ('羽毛球');
INSERT into hobby(hobby) VALUES ('篮球');
INSERT into hobby(hobby) VALUES ('足球');
INSERT into hobby(hobby) VALUES ('游泳');
INSERT into hobby(hobby) VALUES ('排球');
INSERT into hobby(hobby) VALUES ('乒乓球');
INSERT into hobby(hobby) VALUES ('长跑');
INSERT into hobby(hobby) VALUES ('举重');

ALTER TABLE player add hobby_id INT;
alter table player add constraint hobby_id foreign key (hobby_id) references hobby(id) on update CASCADE;

UPDATE player SET hobby_id = 1 WHERE id<3;
UPDATE player SET hobby_id = 3 WHERE id>=3 && id<=6;
UPDATE player SET hobby_id = 2 WHERE id>6 && id <=9;
UPDATE player SET hobby_id = 4 WHERE id>9;


-- 有多少球员选择了
SELECT count(name) FROM player INNER JOIN hobby on player.hobby_id = hobby.id;
-- 并选择了什么运动，有那些喜好被球员选择了
SELECT hobby from player inner join hobby on player.hobby_id = hobby.id ;
-- 并被哪些球员选择了
SELECT name FROM player INNER JOIN hobby on player.hobby_id = hobby.id;
-- 那些球员没有选择喜好
SELECT * FROM player left JOIN hobby on player.hobby_id = hobby.id WHERE hobby_id is null;
-- 哪些喜好没有被球员选择
SELECT id from hobby WHERE id not in (SELECT distinct hobby_id FROM player WHERE hobby_id is not null);
-- 具体球员选择的喜好
SELECT * FROM player inner JOIN hobby on player.hobby_id = hobby.id;