|
项目需求
全美职业篮球联赛(简称 NBA)，是被世界公认水平最高的篮球联赛，由 30 支球队组 成，每只球队由 16 名球员组成。NBA 联盟定期组织比赛，每一赛季产生一个总冠军。根据发展需要，NBA 需要一套球员管理系统。该系统包括球员信息管理、球员能力评 估、球队信息管理等模块，可以实现对球员信息和球队信息的录入、查询、删除、更新， 以及对球员能力的数据分析、汇总等。
项目分析
本次项目案例课需要我们完成该系统的数据库设计部分，该数据库由球员基本信息表、 球员能力评估表、球队基本信息表构成。根据业务需要对表添加约束。录入测试数据，并 利用 SQL 语句做业务测试。
知识点分析 在完成该项目时，我们使用到的知识点如下:
创建数据库。 创建数据表，添加各种约束。【外键约束体现参考完整性】【主键约束体现实体完整性】【not null 体现域完整性】 采用 INSERT 语句进行数据的录入。 采用 UPDATE 语句进行数据的更新。 采用 DELETE 语句进行数据的删除。 采用 SELECT 语句进行数据的查询。 聚合函数应用，分组操作，HAVING 子句。 应用联接查询。
实现步骤
1. 创建数据库 NBADB，设置数据文件和日志文件的保存路径。
2. 创建球员信息表(Players)、球员能力评估表(Abilities)、球队信息表(Clubs)。
create table clubs (
cid int auto_increment primary key,
cname varchar(50),
ctiy varchar(50)
);
create table players(
pid int auto_increment primary key,
pname varchar(50) not null,
birthday date,
height int,
weight int,
position varchar(10),
cid int,
foreign key(cid) references clubs(cid)
);
create table abilities(
pid int,
body numeric(4,1),
shoot numeric(4,1),
control numeric(4,1),
foreign key(pid) references players(pid)
);
3. 根据需求添加约束。【建表是已经完成】
4. 使用 INSERT 语句，录入如下数据:
insert into clubs(cname,ctiy) values
('湖人','洛杉矶'),('火箭','休斯顿'),('凯尔特人','波士顿'),('骑士','克利夫兰'),('马刺','圣安东尼奥'),('魔术','奥兰多');
insert into players (pname,birthday,height,weight,position,cid) values ('德怀恩-韦德','1982-1-17',193,96,null,1),
('勒布朗-詹姆斯','1984-12-30',203,113,'前锋',4),
('科比-布莱恩特','1978-8-23',198,99,'后卫',1),
('德克-诺维斯基','1978-6-19',213,111,null,2),
('克里斯-保罗','1985-5-6',182,79,'后卫',2),
('托尼-帕克','1982-5-17',187,83,'后卫',4),
('凯文-加内特','1981-7-14',212,113,null,3),
('保罗-皮尔斯','1977-10-13',200,106,'前锋',3),
('迈克尔-乔丹','1963-2-17',198,98,'前锋',3),
('德怀特-霍华德','1985-12-8',210,120,'中锋',1),
('姚明','1980-9-12',229,140,'中锋',2),
('沙奎尔-奥尼尔','1972-3-6',215,147,'中锋',4);
insert into abilities (pid,body,shoot,control) values (1,9.5,9.5,9.0),(2,10.0,9.5,9.0),(3,9.5,10.0,9.5),(4,7.5,9.5,7.5),(5,9.0,8.5,10.0),
(6,8.5,8.5,9.5),(7,8.5,8.0,8.0),(8,7.5,8.5,8.5),(9,10.0,10.0,10.0),(10,10.0,7.5,7.0),(11,9.0,9.0,7.5),(12,9.0,7.5,7.0);
5. 编写 SQL 语句，从 Players 表中查询体重大于等于 100kg 的球员名字、位置。效果
如图 1 所示:
select pname,position,weight from players where weight >= 100;
6. 编写 SQL 语句，从 Players 表和 Clubs 表用内连接的方式查询火箭队球员的名字、身高、体重、位置、球队名字。效果如图 2 所示:
select pname,height,weight,position,cname from players,clubs
where players.cid = clubs.cid and clubs.cname = '火箭'
7. 编写 SQL 语句，从 Players 表中查询位置为 NULL 的球员名字、身高、体重。注意
NULL 的查询方法。效果如图 3 所示:
select * from players where position is null;
8.编写 SQL 语句，从 Players 表和 Clubs 表中查询身高在 210cm 以上的球员名称以及
所属球队名称，并按照身高降序。效果如图 4 所示:
select pname,height,cname from players,clubs
where players.cid = clubs.cid and height >= 210
order by height desc
9.编写 SQL 语句，查询能力值(身体素质+投篮+控球)最高的前三名球员的基本信息。建议先对能力值降序排列，之后用 TOP 3 取 前三名球员。效果如图 5 所示:
select * from abilities,players where abilities.pid = players.pid
order by (body+shoot+control)
desc limit 0,3
10. 编写 SQL 语句，查询所有球队的球员信息，包括球队名称、球队所在地、球员名字、
身高、体重、年龄。建议使用左连接。效果如图 6 所示
select ctiy,cname,pname,height,weight,position from
clubs left join players on clubs.cid = players.cid
11.编写 SQL 语句，将位置为空的球员，位置修改为“自由人”。建议使用 UPDATE 语句。效果如图 7 所示:
update players set position = '自由人' where position is null;
12. 洛杉矶湖人队被华人收购，更名西安湖人队，编写 SQL 语句，将湖人队的城市修改
为西安。效果如图 8 所示:
update clubs set ctiy = '西安'
13. 编写 SQL 语句，统计身体素质、投篮、控球得分均大于等于 8.5 分的总人数。效果如图 9 所示
select count(*) from abilities where body >= 8.5 and shoot >= 8.5 and control >= 8.5;
14. 编写 SQL 语句，按照位置统计，每个位置的球员人数，并显示人数大于等于 3 人的 位置。建议使用分组查询，并利用 HAVING 子句过滤不满足条件的组。效果如图 10 所示:
select count(position) as total ,position from players group by position having total >=3;
15. 编写 SQL 语句，将马刺队和魔术队从 Clubs 表中删除。建议使用 DELETE 语句。效果如图 11 所示:
若想删除和更新主表中的字段 那么要对从表中的字段采取一些措施：
删除和更新有四种设置方式 （1）cascade：级联，当父表更新、删除，子表会同步更新和删除 （2）set null：置空，当父表更新、删除的时候，字表会把外键字段变为null，所以这个时候设计表的时候该字段要允许为null，否则会出错 （3）restrict：父表在删除和更新记录的时候，要在子表中检查是否有有关该父表要更新和删除的记录，如果有，则不允许删除个更改 （4）no action：和restrict一样
foreign key(cid) references clubs(cid) on delete set null
delete * from clubs where cname = '马刺' or cname = '魔术'
|