数据库练习题（接上次博客文章）

2023-12-19 20:23:15

       本次的数据库练习题，是小编我总结了上次发布的数据库知识点，后动手操作的一部分。重点是想加深对数据库的理解。面对语句的编写，各位同仁可以多多批评与指正呀。

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数据库。网上很多。重点通过对各个数据库的联表操作。为了训练自己的数据库语法相关知识，所以很多题目是我自己想着，想要训练某个不熟悉的知识点而编写的，并不全面。对于数据库语句，如果不够好，请大家可以批评指正呀。、

上一篇：FFMpeg学习——4 SDL


下一篇：MySQL常用操作、插入函数