MySql的综合应用

/*/*/*接下来要说的知识点所涉及到的表较多，stusys库已无法满足需求，故在此插一篇实验报告进行衔接，今后的内容以此报告中创建的study库为准*/*/*/

一、实验目的

1、掌握查询语句的五个子句：select、from、where、group by、order by

2、掌握单表查询的基本语法格式

二、实验内容

在“study”数据库上进行。

1.  不带where条件的查询；

（1）查找课程表中所有课程的信息。

（2）在学生表中，查找所有学生的学号、姓名和籍贯。

（3）在班级表中，输出所有班级的所属学院。

（4）在选课表中，将学生选课的成绩提高5%后输出。

（5）在学生表中，输出学生的平均年龄。

（6）输出学生表中所有学生的学号和姓名，并按新字段名“学生学号”和“学生姓名”来显示查询结果。

2.  带where条件的查询；

（1）查找选修了“c04”课程的学生的学号和成绩，结果按成绩降序排列。

（2）查找选修了课程的学生的基本信息和选课信息。

（3）查找选修了课程的学生的基本信息和选课信息，结果中去掉重复字段。

（4）查找选修了“高等数学”的学生的学号和成绩，结果按成绩降序排列。

三、实验步骤

创建study数据库：

在study数据库下创建表：

在数据表班级中插入数据：

在数据表学生中插入数据：

在数据表课程中插入数据：

在数据表选课中插入数据：

不带where条件的查询；

查找课程表中所有课程的信息。

在学生表中，查找所有学生的学号、姓名和籍贯。

在班级表中，输出所有班级的所属学院。

在选课表中，将学生选课的成绩提高5%后输出。

或

在学生表中，输出学生的平均年龄。

或

输出学生表中所有学生的学号和姓名，并按新字段名“学生学号”和“学生姓名”来显示查询结果。

   

带where条件的查询：

查找选修了“c04”课程的学生的学号和成绩，结果按成绩降序排列。

查找选修了课程的学生的基本信息和选课信息。

 查找选修了课程的学生的基本信息和选课信息，结果中去掉重复字段。

 查找选修了“高等数学”的学生的学号和成绩，结果按成绩降序排列。

源代码：

create database study;

use study;

create table 班级(班级名称 varchar(10) primary key,
  	所属学院 varchar(10) not null,
  	辅导员 varchar(8),
  	自习室 varchar(12));

create table 学生(学号 char(6) primary key,
	姓名 varchar(8) not null,
	性别 char(1),
	年龄 tinyint default 20,
	所在班级 varchar(10),
	籍贯 varchar(20),
	foreign key (所在班级) references 班级(班级名称) on update restrict on delete restrict);

create table 课程(课程号 char(3) primary key,
	课程名 varchar(20) unique not null,
	先修课程 char(3),
	课程性质 enum('选修','公共必修','专业必修'),
	学分 tinyint);

create table 选课(学号 char(6),
	课程号 char(3),
	成绩 smallint,
	primary key(学号,课程号),
	foreign key(学号) references 学生(学号),
    Constraint 选课_fk2 foreign key(课程号) references 课程(课程号));

insert into 班级
	values('19计科1','信息与计算机','章英','J06-301'),
	      ('19计科2','信息与计算机','章英','J06-302'),
	      ('19计科3','信息与计算机','刘建','J06-303'),
	      ('19信管','信息与计算机','刘建','J06-401'),
	      ('19电商1','管理','王小丽','J07-101'),
	      ('19电商2','管理','王小丽','J07-102'),
	      ('19市营','管理','王小丽','J07-103'),
	      ('19会计1','管理','王婷','J07-201'),
	      ('19会计2','管理','王婷','J07-202'),
	      ('19大英1','外语','刘俊飞','J06-311'),
	      ('19大英2','外语','刘俊飞','J06-312'), 
	      ('19法语','外语','代晶','J06-313');

insert into 学生
	values('060101','王小辉','男',20,'19计科1','湖北武汉'),
	      ('060102','吴珍萍','女',20,'19计科1','江西南昌'),
	      ('060103','吴鑫','男',19,'19计科2','湖北武汉'),
	      ('070101','孙晓英','女',21,'19电商1','河南郑州'),
	      ('070102','林小慧','女',20,'19电商1','湖南长沙'),
	      ('070103','吴俊','男',19,'19电商1','湖北武汉'), 
	      ('070301','李霖','男',21,'19会计1','江西南昌'),
	      ('070302','张小慧','女',22,'19会计1','湖北黄石'),
	      ('070303','李霖','男',21,'19会计2','江西南昌'),
	      ('080101','江毅飞','男',20,'19大英1','湖北黄石'),
	      ('080102','郑文曦','女',20,'19大英1','湖北随州'),
	      ('080103','江毅飞','男',20,'19大英1','湖北黄石'),
	      ('080104','胡梦','女',18,'19大英1','江西南昌');

insert into 课程
	values('c01','高等数学',null,'公共必修',4),
	      ('c02','计算机基础',null,'公共必修',3),
	      ('c03','C程序设计','c02','公共必修',3),
	      ('c04','大学英语',null,'公共必修',4),
	      ('c05','数据结构','c03','专业必修',4),
	      ('c06','数据库原理','c03','专业必修',3),
	      ('c07','音乐欣赏',null,'选修',2),
	      ('c08','论文写作指导',null,'选修',2);

insert into 选课
	values ('060101','c01',91),
 	       ('060101','c03',null),
 	       ('060101','c04',95),
 	       ('060101','c05',80),
 	       ('060102','c01',81),
 	       ('060102','c03',null),
 	       ('060102','c04',92),
	       ('070101','c01',50),
 	       ('070101','c02',86),
 	       ('070101','c06',90),
 	       ('070103','c04',52),
 	       ('070103','c06',47),
	       ('070301','c01',87),
	       ('070301','c04',83),
	       ('080101','c02',73),
	       ('080101','c07',69);

select * from 课程;

select 学号,姓名,籍贯 from 学生;

select ALL 所属学院 from 班级;

select ALL 所属学院 from 班级;

select 学号,成绩*1.05 from 选课;

select 学号,成绩*1.05 as 提高后的成绩 from 选课;

select avg(年龄) from 学生;

select avg(年龄) as 学生平均年龄 from 学生;

select 学号 学生学号,姓名 学生姓名 from 学生;

select 学号,成绩 from 选课 where 课程号='c04' order by 成绩 desc;

select * from 学生 join 选课 on 学生.学号=选课.学号;

select * from 学生 natural  join 选课;

select c.学号,姓名 from 课程 a join 选课 b on a.课程号=b.课程号 
	join 学生 c on b.学号=c.学号
	where 课程名='高等数学'  order by 成绩 desc;