MYSQL数据库实验报告(建表和建表,(简单,连接,嵌套,集合)查询,存储过程) |
您所在的位置:网站首页 › django连接mysql数据库实验报告 › 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) 创作不易,欢迎点赞👍+转载,也请不吝赐教,指正错误😀 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |