MySQL数据库创建与查询（MySQL数据库技术与应用）

/创建stuexpm数据库/
CREATE DATABASE stuexpm;
USE stuexpm;

/创建StudentInfo表/
CREATE TABLE StudentInfo
(
StudentID varchar(6) NOT NULL PRIMARY KEY,
Name varchar(8) NOT NULL,
Sex varchar(2) NOT NULL DEFAULT '男',
Birthday date NOT NULL,
Speciality varchar(12) NULL,
Address varchar(20) NULL,
bz varchar(30) null
);

/创建CourseInfo表/
CREATE TABLE CourseInfo
(
CourseID varchar(4) NOT NULL PRIMARY KEY,
CourseName varchar(16) NOT NULL,
Credit tinyint NULL,
bz varchar(30) null
);

/创建ScoreInfo表/
CREATE TABLE ScoreInfo
(
StudentID varchar(6) NOT NULL,
CourseID varchar(4) NOT NULL,
Grade tinyint NULL,
bz varchar(30) null,
PRIMARY KEY(StudentID, CourseID)
);

/创建TeacherInfo表/
CREATE TABLE TeacherInfo
(
TeacherID varchar(6) NOT NULL PRIMARY KEY,
TeacherName varchar(8) NOT NULL,
TeacherSex varchar(2) NOT NULL DEFAULT '男',
TeacherBirthday date NOT NULL,
School varchar(12) NULL,
Address varchar(20) NULL,
bz varchar(30) null
);

/插入数据到StudentInfo表/
INSERT INTO StudentInfo
VALUES('181001','成志强','男','19980817','计算机','北京市海淀区',NULL),
('181002','孙红梅','女','19971123','计算机','成都市锦江区',NULL),
('181003','朱丽','女','19980219','计算机','北京市海淀区',NULL),
('184001','王智勇','男','19971205','电子信息工程',NULL,NULL),
('184002','周潞潞','男','19980224','电子信息工程','上海市浦东区',NULL),
('184004','郑永波','女','19970919','电子信息工程','上海市浦东区',NULL);

/插入数据到CourseInfo表/
INSERT INTO CourseInfo
VALUES('1004','数据库系统',4,NULL),
('1025','物联网技术',3,NULL),
('4002','数字电路',3,NULL),
('8001','高等数学',4,NULL),
('1201','英语',4,NULL);

/插入数据到ScoreInfo表/
INSERT INTO ScoreInfo
VALUES('181001','1004',95,NULL),
('181002','1004',85,NULL),
('181003','1004',91,NULL),
('184001','4002',93,NULL),
('184002','4002',76,NULL),
('184004','4002',88,NULL),
('181001','8001',94,NULL),
('181002','8001',89,NULL),
('181003','8001',86,NULL),
('184001','8001',85,NULL),
('184002','8001',NULL,NULL),
('184004','8001',94,NULL),
('181001','1201',92,NULL),
('181002','1201',78,NULL),
('181003','1201',94,NULL),
('184001','1201',85,NULL),
('184002','1201',79,NULL),
('184004','1201',94,NULL);

/插入数据到TeacherInfo表/
INSERT INTO TeacherInfo
VALUES('100005','李慧强','男','19680925','计算机学院','北京市海淀区',NULL),
('100024','刘松','男','19760217','计算机学院','北京市海淀区',NULL),
('400021','陈霞飞','女','19751207','通信学院','上海市黄浦区',NULL),
('800004','刘泉明','男','19780816','数学学院','广州市越秀区',NULL),
('120007','张莉','女','19820321','外国语学院','成都市锦江区',NULL);

update studentinfo set bz='0000';
update courseinfo set bz='0000';
update scoreinfo set bz='0000';
update teacherinfo set bz='0000';

img

img

img

img

1.查询课程不同、成绩相同的学生的学号、性别和成绩（备注也要）
（1）使用inner join的显示语法结构
（2）使用where子句定义连接条件的隐示语法结构
2.查找选修8001课程且为计算机专业学生的姓名及成绩，查出的成绩按降序排序（备注也要）

studentid：学号 name：名字 sex：性别
birthday：生日 speciality：专业 address：地址
bz：备注 courseid：课程号 coursename：课程名
gredit：学分 grade：成绩