mysql 查询学生与课程关系表

-- 已经学生和课程之间是多对多关系
-- 学生表(学生编号(主键)、学生名称(唯一约束不为空)、性别(默认值男)、年龄)
drop table if EXISTS student;
create table student (
	stu_no int PRIMARY KEY,
	stu_name VARCHAR(20) not null UNIQUE,
	sex CHAR(1) DEFAULT '男' COMMENT '性别',
	age VARCHAR(3)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '学生表';
desc student;

-- 课程表(课程编号(主键)、课程名称(唯一约束不为空)、学分)
drop table if exists class;
create table class (
	class_no int PRIMARY KEY,
	class_name VARCHAR(20) not null UNIQUE,
	score int
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '课程表';
desc class;

-- 学生和课程关系表(学生编号,课程编号,成绩)
drop table if exists student_course;
create table student_course (
 	stu_no INT,
 	class_no INT,
 	results VARCHAR(20) NOT NULL,
-- 1、课程编号和学生编号为联合主键。
 	PRIMARY KEY(stu_no,class_no),
-- 2、课程编号和学号编号为外键 数据来源于学生表和课程表的主键值。
  FOREIGN key(stu_no) REFERENCES student(stu_no),
  FOREIGN key(class_no) REFERENCES class(class_no) 
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '关系表';
desc student_course;

INSERT INTO student(stu_no,stu_name,age) VALUES(8101,'张三',18);
INSERT INTO student(stu_no,stu_name,age) VALUES(8102,'钱四',16);
INSERT INTO student(stu_no,stu_name,age) VALUES(8105,'李飞',19);
INSERT INTO student(stu_no,stu_name,age) VALUES(8201,'张飞',18);
INSERT INTO student(stu_no,stu_name,age) VALUES(8302,'周瑜',16);
INSERT INTO student(stu_no,stu_name,age) VALUES(8203,'王亮',17);

INSERT INTO class VALUES (101,'软件',2),(102,'微电子',3),(103,'无机化学',3),(104,'高分子化学',3),(105,'统计数学',4);

DELETE FROM student_course;
INSERT into student_course VALUES (8101,101,93),(8101,102,68),(8101,103,99),(8101,104,93),(8101,105,45),(8102,101,66),(8102,102,77),(8102,103,88),(8102,104,66),(8102,105,88),(8105,101,99),(8105,102,99),(8105,103,99),(8105,104,99),(8105,105,99),(8201,101,33),(8201,102,93),(8201,103,33),(8201,104,63),(8201,105,73),(8302,101,33),(8302,102,93),(8302,103,33),(8302,104,63),(8302,105,73),(8203,101,33),(8203,102,93),(8203,103,33),(8203,104,63),(8203,105,73);

-- 1、查询学生与课程关系表(显示的字段有:学生姓名、课程姓名、课程成绩)
SELECT sc.*,s.stu_name,c.class_name 
FROM student s
join student_course sc
ON s.stu_no=sc.stu_no 
JOIN class c
ON c.class_no=sc.class_no
ORDER BY sc.stu_no

-- 2、统计每个学生选了几门课程 (学生编号、学生姓名、课程数量,具体课程名称)。
SELECT s.stu_no,s.stu_name,COUNT(*) c,GROUP_CONCAT(c.class_name) className
FROM student s
JOIN student_course sc
ON s.stu_no=sc.stu_no
JOIN class c
ON c.class_no=sc.class_no
GROUP BY s.stu_no;

-- 3、统计每个课程被几个学生选。 (课程编号、课程名称、学生数量、具体学生的名称)
SELECT c.class_no,c.class_name,COUNT(*) studentSum,GROUP_CONCAT(s.stu_name) stuName
FROM student s
JOIN student_course sc
ON s.stu_no=sc.stu_no
JOIN class c
ON c.class_no=sc.class_no
GROUP BY c.class_no



 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
查询功能代码(包括普通查询和全部查询):protected void Button2_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); DataBS(ds, strsql); GridView1.DataSource = ds.Tables["table"]; GridView1.DataBind(); DropDownList1.SelectedValue = "不限"; DropDownList2.SelectedValue = "不限"; DropDownList3.SelectedValue = "不限"; DropDownList4.SelectedValue = "不限"; DropDownList5.SelectedValue = "不限"; Label8.Text = "查询结果: 共" + ds.Tables[0].Rows.Count + "条记录"; } protected void Button1_Click(object sender, EventArgs e) { string str = ""; string str1 = ""; if (DropDownList1.SelectedItem.Text != "不限") str += " and CourseGrade='" + DropDownList1.SelectedValue + "'"; if (DropDownList2 .SelectedItem .Text !="不限") str +=" and CourseTerm='" + DropDownList2.SelectedValue + "'"; if (DropDownList3.SelectedItem.Text != "不限") str += " and CourseSort='" + DropDownList3.SelectedValue + "'"; if (DropDownList4.SelectedItem.Text != "不限") str += " and SpecialtyDirection='" + DropDownList4.SelectedValue + "'"; if (DropDownList5.SelectedItem.Text != "不限") str += " and CourseTeacher='" + DropDownList5.SelectedValue + "'"; if (str != "") { str1 = str.Substring(5); strsql += " where " + str1; } DataSet ds = new DataSet(); DataBS(ds, strsql); if (ds.Tables[0].Rows.Count != 0) { GridView1.DataSource = ds.Tables["table"]; GridView1.DataBind(); } else { Response.Write("<script language=JavaScript>alert('没有符合条件的记录!');</script>"); GridView1.Visible = true ; } Label8.Text = "查询结果: 共" + ds.Tables[0].Rows.Count + "条记录"; }

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值