本次的数据库练习题,是小编我总结了上次发布的数据库知识点,后动手操作的一部分。重点是想加深对数据库的理解。面对语句的编写,各位同仁可以多多批评与指正呀。
1.创建数据库表。
CREATE DATABASE school;(创建数据库school) USE school; CREATE TABLE student( `sno` VARCHAR(20) NOT NULL, `sname` VARCHAR(20) NOT NULL, `ssex` VARCHAR(20) NOT NULL DEFAULT '男', `sbirthday` DATETIME, `sclass` VARCHAR(20)); CREATE TABLE teacher( tno VARCHAR(20) NOT NULL, tname VARCHAR(20) NOT NULL, tsex VARCHAR(20) NOT NULL DEFAULT '男', tbirthday DATETIME, prof VARCHAR(20) NOT NULL, depart VARCHAR(20) ); CREATE TABLE course( cno VARCHAR(20) NOT NULL, cname VARCHAR(20) NOT NULL, tno VARCHAR(20) NOT NULL); USE school; CREATE TABLE score( sno VARCHAR(20) NOT NULL, cno VARCHAR(20) NOT NULL, degree DECIMAL(4,1) NOT NULL); ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(sno); ALTER TABLE student ADD CONSTRAINT FOREIGN KEY(sno) REFERENCES student(sno); ALTER TABLE student ADD CONSTRAINT FOREIGN KEY(cno) REFERENCES course(cno); ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(sno,cno); ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(sno); ALTER TABLE teacher ADD CONSTRAINT PRIMARY KEY(tno); ALTER TABLE course ADD CONSTRAINT PRIMARY KEY(cno); ALTER TABLE course ADD CONSTRAINT FOREIGN KEY(tno) REFERENCES teacher(tno); ALTER TABLE score ADD CONSTRAINT PRIMARY KEY(sno,cno); ALTER TABLE score ADD CONSTRAINT FOREIGN KEY(cno) REFERENCES course(cno);
2、插入数据
INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(108,'曾华','男','1997-09-01',95033); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(105,'匡明','男','1975-10-02',95031); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(107,'王丽','女','1976-01-23',95033); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(101,'李军','男','1976-02-20',95033); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(109,'王芳','女','1975-02-10',95031); INSERT INTO student(sno,sname,ssex,sbirthday,sclass) VALUES(103,'陆君','男','1974-06-03',95031); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES(804,'李诚','男','1958-12-02','副教授','计算机系'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES(856,'张旭','男','1969-03-12','讲师','电子工程系'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES(825,'王萍','女','1972-05-05','助教','计算机系'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES(831,'刘冰','女','1977-08-14','助教','电子工程系'); INSERT INTO course(cno,cname,tno) VALUES('3-105','计算机导论',825); INSERT INTO course(cno,cname,tno) VALUES('3-245','操作系统',804); INSERT INTO course(cno,cname,tno) VALUES('6-166','数据电路',856); INSERT INTO course(cno,cname,tno) VALUES('19-888','高等数学',831); INSERT INTO score(sno,cno,degree) VALUES(103,'3-245',86); INSERT INTO score(sno,cno,degree) VALUES(105,'3-245',75); INSERT INTO score(sno,cno,degree) VALUES(109,'3-245',68); INSERT INTO score(sno,cno,degree) VALUES(103,'3-105',92); INSERT INTO score(sno,cno,degree) VALUES(105,'3-105',88); INSERT INTO score(sno,cno,degree) VALUES(109,'3-105',76); INSERT INTO score(sno,cno,degree) VALUES(101,'3-105',64); INSERT INTO score(sno,cno,degree) VALUES(107,'3-105',91); INSERT INTO score(sno,cno,degree) VALUES(108,'3-105',78); INSERT INTO score(sno,cno,degree) VALUES(101,'6-166',85); INSERT INTO score(sno,cno,degree) VALUES(107,'6-166',79); INSERT INTO score(sno,cno,degree) VALUES(108,'6-166',81);
任务
1)查询表
查询school下的所有表 use school select * from student; select * from teacher; select * from course; select * from score;
student:
teacher:
course:
score:
2)插入数据:
向成绩表中插入一个数据 insert into score(sno,cno,degree) values(110,'19-888',99.0)
3)连接查询
第一题:查询学生的学号、班级、性别、成绩、授课老师
第一步:
//连表查询--学生表和成绩表连接到一起 select a.sno,a.sclass,a.ssex,b.cno,b.degree from student a join score b where a.sno=b.sno
第二步:
//teacher表和课程表相连 select c.cno,c.cname,d.tname from course c join teacher d where c.tno=d.tno
第三步:
SELECT E.sno AS '学号',E.sclass AS '班级',E.ssex AS '性别', F.cname AS '课程', F.tname AS '授课教师', E.degree AS '成绩' FROM( SELECT a.sno,a.sclass,a.ssex,b.cno,b.degree FROM student a JOIN score b WHERE a.sno=b.sno)E LEFT JOIN( SELECT c.cno,c.cname,d.tname FROM course c JOIN teacher d WHERE c.tno=d.tno )F ON E.cno=F.cno
第二题:查询学号、姓名、各科课程成绩、总成绩。
SELECT sno,c.cno,degree,cname FROM course c JOIN score b WHERE c.cno=b.`cno`
SELECT S.sno AS '学号' ,S.sname AS '姓名', MAX(CASE WHEN G.cname='高等数学' THEN degree ELSE 0 END) AS '高等数学', MAX(CASE WHEN G.cname='计算机导论' THEN degree ELSE 0 END) AS '计算机导论', MAX(CASE WHEN G.cname='操作系统' THEN degree ELSE 0 END) AS '操作系统', MAX(CASE WHEN G.cname='数据电路' THEN degree ELSE 0 END) AS'数据电路', SUM(degree) AS 总分, AVG(degree) AS 平均分 FROM ( SELECT * FROM student)S LEFT JOIN( SELECT sno,c.cno,degree,cname FROM course c JOIN score b WHERE c.cno=b.`cno`)G ON S.sno=G.sno GROUP BY S.sno
第三题:在第二题的基础上,进行模糊查询姓"王的学生"。
SELECT S.sno AS '学号' ,S.sname AS '姓名', MAX(CASE WHEN G.cname='高等数学' THEN degree ELSE 0 END) AS '高等数学', MAX(CASE WHEN G.cname='计算机导论' THEN degree ELSE 0 END) AS '计算机导论', MAX(CASE WHEN G.cname='操作系统' THEN degree ELSE 0 END) AS '操作系统', MAX(CASE WHEN G.cname='数据电路' THEN degree ELSE 0 END) AS'数据电路', SUM(degree) AS 总分, AVG(degree) AS 平均分 FROM ( SELECT * FROM student)S LEFT JOIN( SELECT sno,c.cno,degree,cname FROM course c JOIN score b WHERE c.cno=b.`cno`)G ON S.sno=G.sno WHERE S.sname LIKE '王%'//添加的条件判断,进行筛选。 GROUP BY S.sno
第四题:把教师表上的日期进行时间戳转换,转换format格式为:日-月-年;
FROM_UNIXTIME(UNIX_TIMESTAMP(t.`tbirthday`),'%D-%M-%Y') AS '转化后出生日期' FROM teacher t GROUP BY tno;
第五题:基于第三题,使用Round函数,对学生成绩进行取整、以及保留相应的小数位的操作。
SELECT S.sno AS '学号' ,S.sname AS '姓名', ROUND(MAX(CASE WHEN G.cname='高等数学' THEN degree ELSE 0 END),0) AS '高等数学',//取整 ROUND(MAX(CASE WHEN G.cname='计算机导论' THEN degree ELSE 0 END),1)AS '计算机导论',//保留一位小数 ROUND(MAX(CASE WHEN G.cname='操作系统' THEN degree ELSE 0 END),2) AS '操作系统',//保留两位小数 ROUND(MAX(CASE WHEN G.cname='数据电路' THEN degree ELSE 0 END),3)AS'数据电路',//保留三位小数 ROUND(SUM(degree),0)AS 总分,//保留整数 ROUND(AVG(degree),2) AS 平均分//保留两位小数 FROM ( SELECT * FROM student)S LEFT JOIN( SELECT sno,c.cno,degree,cname FROM course c JOIN score b WHERE c.cno=b.`cno`)G ON S.sno=G.sno GROUP BY S.sno
第六题:现因校扩大招生,增加一个新的班级,因此,另一管理员,创建了一个新的学生表Sudent1.请利用Union方法,把两张表合成一张表,并用order by 排序。
(1)创建新学生表:
CREATE TABLE Newstudent( `sno` VARCHAR(20) NOT NULL, `sname` VARCHAR(20) NOT NULL, `ssex` VARCHAR(20) NOT NULL DEFAULT '男', `sbirthday` DATETIME, `sclass` VARCHAR(20));
(2)导入学生信息
INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(102,'张小明','男','1997-09-01',95033); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(104,'匡正义','男','1995-10-02',95031); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(106,'王丽娟','女','1992-01-23',95033); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(108,'李小军','男','1990-02-20',95033); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(111,'王芳芳','女','1997-02-10',95031); INSERT INTO newstudent(sno,sname,ssex,sbirthday,sclass) VALUES(112,'陆怡君','女','1999-06-03',95031);
(3)用Union方法,把两张表合成一张表 并用order by 进行学号排序。
SELECT * FROM student UNION SELECT * FROM newstudent ORDER BY sno ;
以上的数据库就是很基本的school数据库。网上很多。重点通过对各个数据库的联表操作。为了训练自己的数据库语法相关知识,所以很多题目是我自己想着,想要训练某个不熟悉的知识点而编写的,并不全面。对于数据库语句,如果不够好,请大家可以批评指正呀。、