MYSQL数据库实验报告（建表和建表，（简单，连接，嵌套，集合）查询，存储过程）

一、首先要学会建库，其次建表

建库（1.JXGL库 2.GYXT库）

1.JXGL库

如上图第一步选中数据库后，到第二步新建查询，在编辑面板输入以下MYSQL命令

CREATE  DATABASE 建库的名称

建表

同建库的方法，以下为MYSQL命令

//在JXGL数据库中建立STUDENT表，并插入记录。
CREATE TABLE STUDENT
( Sno char(5) not null unique,
   Sname char(20) not null unique,
   Ssex char(1),
Sage int,
Sdept char(20));
INSERT INTO STUDENT VALUES('95001','李勇','M',20,'CS');
INSERT INTO STUDENT VALUES('95002','刘晨','F',19,'IS');
INSERT INTO STUDENT VALUES('95003','王敏','F',18,'MA');
INSERT INTO STUDENT VALUES('95004','张立','M',18,'IS');

// JXGL数据库中建立COURSE表，并插入记录。
CREATE TABLE COURSE
(  Cno char(2) not null PRIMARY KEY(Cno),
   Cname char(20),
Pcno char(2),
Ccredit smallint);
INSERT INTO COURSE VALUES('1','数据库','5',4);
INSERT INTO COURSE VALUES('2','数学',' ',2);
INSERT INTO COURSE VALUES('3','信息系统','1',4);
INSERT INTO COURSE VALUES('4','操作系统','6',3);
INSERT INTO COURSE VALUES('5','数据结构','7',4);
INSERT INTO COURSE VALUES('6','数据处理',' ',2);
INSERT INTO COURSE VALUES('7','C语言','6',4);

JXGL数据库中建立SC表，并插入记录。
CREATE TABLE SC
( Sno char(5) not null,
   Cno char(2) not null,
Grade smallint,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES STUDENT(Sno),
FOREIGN KEY(Cno) REFERENCES COURSE(Cno));
INSERT INTO SC VALUES('95001','1',92);
INSERT INTO SC VALUES('95001','2',85);
INSERT INTO SC VALUES('95001','3',88);
INSERT INTO SC VALUES('95002','2',90);
INSERT INTO SC VALUES('95002','3',80); 
INSERT INTO SC (Sno,Cno) VALUES('95003','3');
INSERT INTO SC VALUES('95001','4',78);
INSERT INTO SC(sno,cno) VALUES('95001','5');
INSERT INTO SC VALUES('95001','6',90);
INSERT INTO SC(sno,cno)  VALUES('95001','7');

2.GYXT库、表

创建供应系统“GYXT”数据库。
CREATE  DATABASE GYXT;

//建立供应商表S。
CREATE TABLE S
( SNO char(5) not null unique,
   SNAME char(20) not null unique,
   CITY char(20));
INSERT INTO S VALUES('S1','精益','天津');
INSERT INTO S VALUES('S2','万胜','北京');
INSERT INTO S VALUES('S3','东方','北京');
INSERT INTO S VALUES('S4','丰泰隆','上海');
INSERT INTO S VALUES('S5','康健','南京');

//建立零件表P。
CREATE TABLE P
(  PNO char(5) not null PRIMARY KEY(PNO),
   PNAME char(20),
COLOR char(20),
WEIGHT smallint);
INSERT INTO P VALUES('P1','螺母','红',12);
INSERT INTO P VALUES('P2','螺栓','绿',17);
INSERT INTO P VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P VALUES('P4','螺丝刀','红',14);
INSERT INTO P VALUES('P5','凸轮','蓝',40);
INSERT INTO P VALUES('P6','齿轮','红',30);

//建立工程项目表J。
CREATE TABLE J
( JNO char(5) not null,
   JNAME char(20) not null,
CITY char(20));
INSERT INTO J VALUES('J1','三建','北京');
INSERT INTO J VALUES('J2','一汽','长春');
INSERT INTO J VALUES('J3','弹簧厂','天津');
INSERT INTO J VALUES('J4','造船厂','天津');
INSERT INTO J VALUES('J5','机车厂','唐山');
INSERT INTO J VALUES('J6','无线电厂','常州');
INSERT INTO J VALUES('J7','半导体厂','南京');

/建立供应情况表SPJ。
CREATE TABLE SPJ
( SNO char(5) not null,
   PNO char(5) not null,
   JNO char(5) not null,
QTY smallint,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO));
INSERT INTO SPJ VALUES('S1','P1','J1',200);
INSERT INTO SPJ VALUES('S1','P1','J3',100);
INSERT INTO SPJ VALUES('S1','P1','J4',700);
INSERT INTO SPJ VALUES('S1','P2','J2',100);
INSERT INTO SPJ VALUES('S2','P3','J1',400);
INSERT INTO SPJ VALUES('S2','P3','J2',200);
INSERT INTO SPJ VALUES('S2','P3','J4',500);
INSERT INTO SPJ VALUES('S2','P3','J5',400);
INSERT INTO SPJ VALUES('S2','P5','J1',400);
INSERT INTO SPJ VALUES('S2','P5','J2',100);
INSERT INTO SPJ VALUES('S3','P1','J1',200);
INSERT INTO SPJ VALUES('S3','P3','J1',200);
INSERT INTO SPJ VALUES('S4','P5','J1',100);
INSERT INTO SPJ VALUES('S4','P6','J3',300);
INSERT INTO SPJ VALUES('S4','P6','J4',200);
INSERT INTO SPJ VALUES('S5','P2','J4',100);
INSERT INTO SPJ VALUES('S5','P3','J1',200);
INSERT INTO SPJ VALUES('S5','P6','J2',200);
INSERT INTO SPJ VALUES('S5','P6','J4',500);

---

二、简单查询和连接查询

1．在教学管理JXGL数据库中进行如下操作：

(1) 求数学系学生的学号和姓名。

SELECT Sno,Sname FROM STUDENT WHERE Sdept='MA'

(2) 求选修了课程的学生学号。

SELECT Sno FROM SC GROUP BY Sno

(3) 求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

SELECT Sno,Grade FROM SC WHERE Cno='2' ORDER BY Grade DESC,Sno ASC

(4) 求选修了数学课且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。

SELECT Sno,Grade*0.8 FROM SC WHERE Grade BETWEEN 80 AND 90 AND Cno='2' 

(5) 求数学系或计算机系姓刘的学生的信息。

SELECT * FROM STUDENT WHERE Sdept='MA' OR Sdept='CS'AND Sname LIKE '刘%'

(6) 求缺少了成绩的学生的学号和课程号。

SELECT Sno,Cno FROM SC WHERE Grade IS NULL

(7) 查询每个学生的情况以及他（她）所选修的课程。

SELECT  Student.*,Cno FROM  Student, SC WHERE  Student.Sno = SC.Sno

(8) 求学生的学号、姓名、选修的课程名及成绩。

SELECT Student.Sno,Sname,Grade,COURSE.Cname FROM Student,SC,COURSE WHERE  Student.Sno = SC.Sno AND COURSE.CNO=SC.CNO

(9) 求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。

SELECT  Student.Sno,Sname,Grade FROM  Student,SC WHERE  Student.Sno = SC.Sno AND CNO='2' AND GRADE>90

(10)查询每一门课的间接先行课（即先行课的先行课）。

select first.* from course first,course second where first.pcno=second.cno;

2．在供应系统GYXT数据库中进行如下操作：

(1) 求供应工程J1零件的供应商号SNO。

SELECT SNO FROM S,J WHERE S.CITY=J.CITY AND JNO='J1'

(2) 求供应工程J1零件P1的供应商号SNO。

SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1'

(3) 统计每种零件的供应总量。

SELECT distinct PNO,SUM(QTY) QTY FROM SPJ GROUP BY PNO

三、嵌套查询、集合查询和存储过程

1．在教学管理JXGL数据库中进行如下操作：

(1) 求选修了数学的学生的学号和姓名。

SELECT STUDENT.Sno,Sname FROM STUDENT,COURSE,SC WHERE SC.CNO=COURSE.CNO AND SC.SNO=STUDENT.SNO AND SC.CNO='2'

(2) 求数学课程成绩高于李勇的学生学号和成绩。

SELECT Sno,Grade FROM SC WHERE CNO='2' AND Grade>85

(3) 求其它系中年龄小于计算机系年龄最大者的学生。

 SELECT * FROM student WHERE Sdept != 'CS'  and Sage< ANY (SELECT Sage FROM student WHERE Sdept='CS') ;

(4) 求其它系中比计算机系学生年龄都小的学生。

SELECT * FROM student WHERE Sdept != 'CS'  and Sage< ANY (SELECT Sage FROM student WHERE Sdept='CS') ;

(5) 求选修了数学课的学生姓名。

SELECT Sname FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO AND SC.CNO='2'

(6) 求没有选修数学课的学生姓名。

SELECT DISTINCT Sname FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO AND SC.CNO!='2'

(7) 查询选修了全部课程的学生的姓名。

SELECT Sname FROM STUDENT S
WHERE NOT EXISTS ( 
SELECT * FROM COURSE C
WHERE not exists (
SELECT * FROM SC  
WHERE S.Sno=Sc.sno AND SC.cno=C.cno))

(8) 求至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。

SELECT Sno,Sname FROM STUDENT a
WHERE Sno <> '95002' AND NOT EXISTS ( 
SELECT * FROM Sc b
WHERE Sno='95002' AND NOT EXISTS (
SELECT * FROM Sc c WHERE a.sno=c.sno and c.cno=b.cno))

(9) 求选修各门课的人数及平均成绩。

SELECT Cname,COUNT(SC.CNO) AS '人数',AVG(Grade) AS '平均成绩' FROM SC,COURSE WHERE COURSE.CNO=SC.CNO GROUP BY COURSE.CNAME

(10)求选修课程在2门以上且都及格的学生号及总平均分。

SELECT SNO,AVG(Grade) AS 总平均分 FROM SC GROUP BY SNO HAVING COUNT(SC.CNO)>=2 AND MIN(GRADE)>60

(11)求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。

SELECT SNO,AVG(Grade) AS 总平均分 FROM SC GROUP BY SNO HAVING COUNT(SC.CNO)>=2 AND MIN(GRADE)>60 ORDER BY AVG(GRADE)

(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。

SELECT SNO,AVG(GRADE) AS 成绩平均值,COUNT(CNO) AS 及格门数 FROM SC WHERE 
GRADE>60  GROUP BY SNO ORDER BY COUNT(SC.CNO) DESC,AVG(GRADE) DESC

(13)统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。

SELECT Sno,AVG(Grade)AS 平均成绩,COUNT(*)AS 及格的门数 FROM SC
WHERE EXISTS(SELECT * FROM STUDENT
WHERE NOT EXISTS(SELECT * FROM SCWHERE Sno=STUDENT.Sno))
AND Grade>='60'GROUP BY Sno ORDER BY 平均成绩 DESC, 及格的门数 DESC;

2．在供应系统GYXT数据库中进行如下操作：

(1) 求供应工程J1红色零件的供应商号SNO。

第一种：SELECT SNO FROM SPJ,P WHERE SPJ.PNO=P.PNO AND COLOR='红' AND JNO='J1'
第二种：SELECT DISTINCT SPJ.SNO
FROM SPJ WHERE PNO IN(SELECT PNO FROM P WHERE COLOR='红')AND JNO='J1'

(2) 编写存储过程，求零件供应总量在1000种以上的供应商名字。

SELECT SNAME FROM S WHERE SNO IN(SELECT SNO FROM SPJ GROUP BY SNO HAVING SUM(QTY)>=1000)

---

创作不易，欢迎点赞👍+转载，也请不吝赐教，指正错误😀