成长笔记二、MySQL数据库--学生管理系统数据库手写SQL实现(实践篇)

• 学生对象----> 学生表(student)
• 老师对象----> 老师表(teacher)
• 课程对象----> 课程表(course)
• 成绩对象----> 成绩表(score)
• 班级对象----> 班级表(前缀_class,因为user或class等英文单词是程序中的关键字.尽量不直接用)
• 等.....

数据库设计

• 设计参考上篇博客

• https://www.cnblogs.com/itzixueba/p/11610070.html

• 注意： 真实项目中数据库外键一般不创建，删除逻辑通过代码控制

手写SQL实现

/*
MySQL: 手写的SQL语句.可直接执行
学生表,老师表,课程表,班级表,成绩表
*/
drop table if EXISTS  tbl_score;   -- 删除成绩(先删除外键表再删主表)
drop table if EXISTS  tbl_student; -- 删除学生
drop table if EXISTS  tbl_class;   -- 删除班级
drop table if EXISTS  tbl_teacher; -- 删除老师
drop table if EXISTS  tbl_course;  -- 删除课程

-- 班级对象(tbl_class) : 唯一标识id,班级编号,人数
create table tbl_class(
	id int primary key auto_increment,
  cname varchar(50),
  size  int # 班级人数
);

insert into tbl_class values(default,'人工智能一班',30);
insert into tbl_class values(default,'数据分析一班',30);
insert into tbl_class values(default,'爬虫一班',30);
insert into tbl_class values(default,'大数据一班',30);

-- 学生对象(student): 唯一标识id,姓名,性别,年龄,生日,电话,地址,邮箱,班级编号
create table tbl_student(
	id int primary key auto_increment,  #id自增
  sname varchar(50) not null,
  sex char(2),
  age int ,
  birthday date,
  phone VARCHAR(11),
  email varchar(50),
  addr  varchar(100),
  c_id int,
  foreign key(c_id) references tbl_class(id)

);
-- 插入数据
insert into tbl_student values(default,'张A','男',20,'1980-04-03','18888888888','[email protected]','北京',1);
insert into tbl_student values(default,'张B','男',20,'1980-04-03','18888888888','[email protected]','北京',1);
insert into tbl_student values(default,'赵C','女',21,'1980-04-03','18888888888','[email protected]','天津',1);
insert into tbl_student values(default,'赵D','女',21,'1980-04-03','18888888888','[email protected]','天津',1);
insert into tbl_student values(default,'钱E','女',21,'1980-04-03','18888888888','[email protected]','上海',1);
insert into tbl_student values(default,'钱F','男',17,'1980-04-03','18888888888','[email protected]','上海',1);
insert into tbl_student values(default,'孙G','男',16,'1980-04-03','18888888888','[email protected]','杭州',1);
insert into tbl_student values(default,'孙H','女',17,'1980-04-03','18888888888','[email protected]','杭州',2);
insert into tbl_student values(default,'李I','男',26,'1980-04-03','18888888888','[email protected]','苏州',2);
insert into tbl_student values(default,'李J','女',34,'1980-04-03','18888888888','[email protected]','苏州',2);
insert into tbl_student values(default,'周K','女',40,'1980-04-03','18888888888','[email protected]','四川',2);
insert into tbl_student values(default,'周L','男',32,'1980-04-03','18888888888','[email protected]','四川',2);
insert into tbl_student values(default,'吴M','女',16,'1980-04-03','18888888888','[email protected]','广州',2);
insert into tbl_student values(default,'吴N','女',20,'1980-04-03','18888888888','[email protected]','广州',3);
insert into tbl_student values(default,'郑O','男',22,'1980-04-03','18888888888','[email protected]','重庆',4);
insert into tbl_student values(default,'郑P','男',23,'1980-04-03','18888888888','[email protected]','重庆',4);
insert into tbl_student values(default,'王Q','女',24,'1980-04-03','18888888888','[email protected]','北京',1);
insert into tbl_student values(default,'王R','男',28,'1980-04-03','18888888888','[email protected]','天津',1);
insert into tbl_student values(default,'冯S','女',39,'1980-04-03','18888888888','[email protected]','山东',1);
insert into tbl_student values(default,'冯T','男',20,'1980-04-03','18888888888','[email protected]','山东',2);
insert into tbl_student values(default,'陈U','女',25,'1980-04-03','18888888888','[email protected]','山西',2);
insert into tbl_student values(default,'陈V','男',28,'1980-04-03','18888888888','[email protected]','山西',3);
insert into tbl_student values(default,'楚W','女',28,'1980-04-03','18888888888','[email protected]','山西',4);
insert into tbl_student values(default,'楚X','男',29,'1980-04-03','18888888888','[email protected]','河北',1);
insert into tbl_student values(default,'魏Y','女',22,'1980-04-03','18888888888','[email protected]','河北',2);
insert into tbl_student values(default,'魏Z','男',21,'1980-04-03','18888888888','[email protected]','河南',3);

#select * from tbl_student;

-- 老师对象(teacher): 唯一标识id,姓名,性别,年龄,电话
create table tbl_teacher(
	id int primary key auto_increment ,
	tname varchar(50) not null,
  sex char(2),
  age int,
  phone varchar(11)
);
insert into tbl_teacher values(default,'成龙','男',58,'1766666601');
insert into tbl_teacher values(default,'黎明','男',58,'1766666601');
insert into tbl_teacher values(default,'孙俪','女',35,'1766666601');
insert into tbl_teacher values(default,'范冰冰','女',35,'1766666601');

# select * from tbl_teacher

-- 课程对象(course): 唯一标识id,课程名,开课时间,课时数量
create table tbl_course(
	id int primary key auto_increment ,
	cname varchar(11) not null unique, #非空,唯一
	start_time datetime,
	count int 
);
insert into tbl_course values(default,'人工智能AI',now(),80);
insert into tbl_course values(default,'数据分析',now(),70);
insert into tbl_course values(default,'爬虫',now(),60);
insert into tbl_course values(default,'大数据',now(),90);

-- 成绩对象(score) : id,分数,课程名, 学生名
create table tbl_score(
	id int primary key auto_increment,
  score float(4,1),
  s_id int not null,
	c_id int not null,
	foreign key(s_id) references tbl_student(id),  #建表时指定外键
  foreign key(c_id) references tbl_course(id)  #建表时指定外键
);

# 修改表添加外键
#alter table tbl_score add constraint FK_scoreid_courseid foreign key(c_id) references tbl_course(id);
-- 第1个人,第4门课
insert into tbl_score values (default,58,1,1);
insert into tbl_score values (default,89,1,1);
insert into tbl_score values (default,78,1,2);
insert into tbl_score values (default,65,1,3);
insert into tbl_score values (default,90,1,4);
-- 第2个人4门课
insert into tbl_score values (default,90,2,1);
insert into tbl_score values (default,54,2,2);
insert into tbl_score values (default,89,2,2);
insert into tbl_score values (default,87,2,3);
insert into tbl_score values (default,92,2,4);

-- 第3个人3门课
insert into tbl_score values (default,90,3,1);
insert into tbl_score values (default,89,3,2);
insert into tbl_score values (default,87,3,3);


-- 第4个人3门课
insert into tbl_score values (default,87,4,1);
insert into tbl_score values (default,86,4,2);
insert into tbl_score values (default,90,4,3);
insert into tbl_score values (default,67,4,4);

-- 第5个人4门课
insert into tbl_score values (default,98,5,1);
insert into tbl_score values (default,90,5,2);
insert into tbl_score values (default,78,5,3);
insert into tbl_score values (default,98,5,4);
# select * from tbl_score;
-- 删除主键和外键
# alter table 表名 drop foreign key 外键（区分大小写）;
# alter table 表名 drop primary key;
# alter  table tbl_score drop foreign key  FK_scoreid_courseid

基础练习题目

# 1. 查询所有学生信息
# 2. 查询所有学生的名字和年龄
# 3. 查询所有男生信息
# 4. 查询所有年龄大于20的学生资料
# 5. 查询所有年龄大于20的女生资料
# 6. 查询男人总人数
# 7. 查询男人和女人各多少人
# 8. 查询张A的名字和年龄
# 9. 查询学生名单(包含班级名)
# 10. 查询人工智能一班的学生名单
# 11. 查询人工智能一班的总人数
# 12. 查询人工智能一班的男生人数
# 13. 查询人工智能一班的男女生各多少人
# 14. 查询每个班各多少人(显示班级编号和总人数)
# 15. 查询每个班各多少人(显示班级名和总人数)
# 16. 查询姓张的所有人
# 17. 统计男生平均年龄
# 18. 统计男生和女生平均年龄
# 19. 统计每个班学生平均年龄
# 20. 统计每个班男生和女生平均年龄
# 21. 查询最大年龄的男生信息
# 22. 查询大数据一班,年龄最小人的信息
# 23. 查询各课的平均分
# 24. 查询各班的平均分
# 25. 查询各班的平均分(班级名和平均分)
# 26. 查询爬虫一班的成绩单(显示班级名,学生名)
# 27. 统计挂科人员资料(班级,名字,科目,分数)
# 28. 统计每个科目的挂科人数
# 29. 查询各课的平均分
# 30. 查询各科的最高分
# 31. 查询各科的最低分
# 32. 查询各科的最高分,最低分,平均分.一行SQL实现

注意：真实项目中数据库外键一般不创建，删除逻辑通过代码控制