创建一个学生表和成绩表mysql 用mysql创建学生成绩表

一、项目需求

某高校拟开发一套高校信息管理系统，要进行后台数据库的设计，以“学生成绩管理”为例，创建一个数据库。此模块具有成绩输入，成绩查询，名次排序，分数统计，删除学籍等功能。

二、表的设计

语法：
		create table 表名(
			列名1 数据类型1,
			列名2 数据类型2,
			....
			列名n 数据类型n
		);
		注意：最后一列，不需要加逗号（,）

1.创建学生信息表（stu_info)

sql语句如下

create table stu_info(
	stu_name varchar(20) not null,
	stu_num varchar(6) not null primary key,
	stu_sex char(1) not null default '男',
	stu_age int(3) not null,
	stu_seat int(3) not null auto_increment unique,
	stu_address varchar(30) default '地址不详',
	key(stu_seat)
)

2.创建学生成绩表(stu_marks)

sql语句如下

create table stu_marks(
	exam_num varchar(11)not null primary key,
	stu_num varchar(6) not null,
	written_score int(3) default 0,
	lab_score int(3) default 0,
	foreign key(stu_num) references stu_info(stu_num)
)

三、插入数据

语法：
		insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
		注意：
		1. 列名和值要一一对应。
		2. 如果表名后，不定义列名，则默认给所有列添加值
			insert into 表名 values(值1,值2,...值n);
		3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来

1.往学生信息表插入数据

sql语句如下

insert into stu_info values('张三','rw1001','男',18,null,'湖南长沙')
insert into stu_info values('李四','rw1002','男',18,default,default)
insert into stu_info values('王五','rw1003','男',19,null,'湖南邵阳')
insert into stu_info values('赵六','rw1004','男',20,null,'湖南娄底')

2.往学生成绩表插入数据

sql语句如下

insert into stu_marks values('h2020090101','rw1001',99,77)
insert into stu_marks values('h2020090102','rw1002',88,default)
insert into stu_marks values('h2020090103','rw1003',86,98)

四、查询数据

语法：
	select
		字段列表
	from
		表名列表
	where
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	limit
		分页限定

1.查询两个表中的所有记录，检查数据插入是否正确

select * from stu_info
select * from stu_marks

2.查询所有男学员的名单

select * from stu_info where stu_sex='男'

3.查询笔试成绩优秀的学员情况（75-100之间），显示学号、笔试成绩

select stu_num 学号,written_score 笔试成绩 
from stu_marks where written_score between 75 and 100

4.查询参加本次考试的学员成绩，包括学员姓名、笔试成绩及机试成绩

select stu_name 学员姓名,written_score 笔试成绩,lab_score 机试成绩 
from stu_info inner join stu_marks on stu_info.stu_num=stu_marks.stu_num

5.统计笔试考试平均分和机试考试平均分

select avg(written_score) 笔试平均分,avg(lab_score) 机试平均分 from stu_marks

6.统计参加本次考试的学员人数

select count(stu_num) 考试人数 from stu_marks

7.查询没有通过考试的人数（笔试或机试小于60分）

select count(*) 考试未通过人数 from stu_marks where written_score < 60 or lab_score < 60

8.查询学员姓名、显示学号、笔试成绩、机试成绩及平均分

select stu_name 学员姓名,stu_info.stu_num 学号,written_score 笔试成绩,lab_score 机试成绩,(written_score + lab_score)/2 平均分 
from stu_info inner join stu_marks on stu_info.stu_num= stu_marks.stu_num

9.排名次（按平均分从高到低排序），显示学号和平均分

select stu_num 学号,(written_score + lab_score)/2 平均分 from stu_marks order by 平均分 desc

10.排名次（按平均分从高到低排序），显示姓名、笔试成绩、机试成绩和平均分

select stu_name 学生姓名,written_score 笔试成绩,lab_score 机试成绩,(written_score + lab_score)/2 平均分 
from stu_info inner join stu_marks on stu_info.stu_num= stu_marks.stu_num order by 平均分 desc

11.根据平均分显示前两名信息，包括姓名、笔试成绩、机试成绩、平均分

select stu_name 学生姓名,written_score 笔试成绩,lab_score 机试成绩,(written_score + lab_score)/2 平均分 
from stu_info inner join stu_marks on stu_info.stu_num= stu_marks.stu_num order by 平均分 desc limit 2

五、修改数据

语法：
	update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

	注意：
		如果不加任何条件，则会将表中所有记录全部修改。

由于笔试试卷难度过大，改试卷后经商议决定：所有学员的笔试成绩都提高5分，超过100分的按100分计算

提示：分两步进行，首先所有笔试成绩都提高5分，然后将所有超过100的成绩更改为100分。
查询修改后的结果

update stu_marks set written_score=100 where written_score>=95
update stu_marks set written_score=written_score+5 where written_score<95

select * from stu_marks

六、删除数据

语法：
	delete from 表名 [where 条件]
	注意：
	1. 如果不加条件，则删除表中所有记录。
	2. 如果要删除所有记录
		delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
		TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。

学号为rw1004的同学退学了，系统决定删除他在数据库的所有信息

delete from stu_info where stu_num='rw1004'
delete from stu_marks where stu_num='rw1004'