数据库实验---江西中医药大学

viperrrrrrr

已于 2025-03-04 19:43:23 修改

阅读量1.1k

点赞数 12

文章标签：数据库 mysql java

于 2023-05-23 16:11:00 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_61006262/article/details/130829376

版权

实验二：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80026
 Source Host           : localhost:3306
 Source Schema         : hisdb

 Target Server Type    : MySQL
 Target Server Version : 80026
 File Encoding         : 65001

 Date: 16/05/2023 15:35:10
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for curefee
-- ----------------------------
DROP TABLE IF EXISTS `curefee`;
CREATE TABLE `curefee`  (
  `CureFeeID` int(0) NOT NULL,
  `DoctorID` int(0) NOT NULL,
  `PatientID` int(0) NOT NULL,
  `Fee` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`CureFeeID`) USING BTREE,
  INDEX `DoctorID`(`DoctorID`) USING BTREE,
  INDEX `PatientID`(`PatientID`) USING BTREE,
  CONSTRAINT `curefee_ibfk_1` FOREIGN KEY (`DoctorID`) REFERENCES `doctor` (`DoctorID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `curefee_ibfk_2` FOREIGN KEY (`PatientID`) REFERENCES `patient` (`PatientID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of curefee
-- ----------------------------

-- ----------------------------
-- Table structure for doctor
-- ----------------------------
DROP TABLE IF EXISTS `doctor`;
CREATE TABLE `doctor`  (
  `DoctorID` int(0) NOT NULL,
  `DoctorName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`DoctorID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of doctor
-- ----------------------------

-- ----------------------------
-- Table structure for patient
-- ----------------------------
DROP TABLE IF EXISTS `patient`;
CREATE TABLE `patient`  (
  `PatientID` int(0) NOT NULL,
  `PatientName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Age` tinyint(0) NOT NULL,
  `Phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`PatientID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of patient
-- ----------------------------

SET FOREIGN_KEY_CHECKS = 1;



SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `Cno` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Cpno` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Ccredit` int(0) NOT NULL,
  PRIMARY KEY (`Cno`) USING BTREE,
  INDEX `Cpno`(`Cpno`) USING BTREE,
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `Sno` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Cno` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Grade` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`Sno`, `Cno`) USING BTREE,
  UNIQUE INDEX `idx_sc_sno_cno`(`Sno`, `Cno`) USING BTREE,
  INDEX `Cno`(`Cno`) USING BTREE,
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
)

实验三：


# --（1）查询“内科”医生的基本信息；
SELECT * FROM Doctor WHERE Department = '内科';

# --（2）查询年龄大于30岁的女病人的姓名，性别，年龄，电话；
SELECT PatientName, Sex, Age, Phone FROM Patient WHERE Age > 30 AND Sex = '女';

# --（3）查询所有姓李的医生的基本信息；
SELECT * FROM Doctor WHERE DoctorName LIKE '李%';

# --（4）查询没有助手的医生的基本信息；
SELECT * FROM Doctor WHERE DoctorID NOT IN (SELECT DoctorID FROM Assistant);

# --（5）查询病人的就诊信息，查询的结果按就诊总费用降序排列；
SELECT Patient.PatientName, Doctor.DoctorName, SUM(CureFee.Fee) AS TotalFee
FROM Patient
INNER JOIN Doctor ON Patient.DoctorID = Doctor.DoctorID
INNER JOIN CureFee ON Patient.PatientID = CureFee.PatientID
GROUP BY Patient.PatientName, Doctor.DoctorName
ORDER BY TotalFee DESC;

# --（6）统计每个医生接诊的所有病人的就诊总费；
SELECT Doctor.DoctorName, SUM(CureFee.Fee) AS TotalFee
FROM Doctor
INNER JOIN Patient ON Doctor.DoctorID = Patient.DoctorID
INNER JOIN CureFee ON Patient.PatientID = CureFee.PatientID
GROUP BY Doctor.DoctorName;

# --（7）统计科室医生人数超过1个的科室；
SELECT Department, COUNT(*) AS DoctorCount
FROM Doctor
GROUP BY Department
HAVING COUNT(*) > 1;










# --（1）查询全体学生的姓名、年龄及所在系；
SELECT StudentName, Age, Department FROM student;

# --（2）查询选修了课程的学生学号；
SELECT DISTINCT StudentID FROM Score;

# --（3）查询年龄在18-20岁间的2013级的学生姓名及学号；
SELECT StudentName, StudentID FROM Student WHERE Age BETWEEN 18 AND 20 AND Grade = 2013;

# --（4）查询“CS”、“MA”、“IS”等系的学生的学号及姓名；
SELECT StudentID, StudentName FROM Student WHERE Department IN ('CS', 'MA', 'IS');

# --（5）查找所有姓李的学生的基本信息；
SELECT * FROM Student WHERE StudentName LIKE '李%';

# --（6）查找所有已选修但没有成绩的学生学号；
SELECT DISTINCT StudentID FROM Score WHERE Score IS NULL;

# --（7）对所有选课的学生按学号进行升序排列，同时要求每个学生的成绩按照降序排列；
SELECT StudentID, Score FROM Score ORDER BY StudentID ASC, Score DESC;

# --（8）统计每门课程的选课人数；
SELECT CourseName, COUNT(*) AS StudentCount FROM Score
INNER JOIN Course ON Score.CourseID = Course.CourseID
GROUP BY CourseName;

# --（9）统计重名的学生姓名及人数；
SELECT StudentName, COUNT(*) AS NameCount FROM Student
GROUP BY StudentName HAVING COUNT(*) > 1;

# --（10）统计男生与女生的人数；
SELECT Sex, COUNT(*) AS SexCount FROM Student
GROUP BY Sex;

# --（11）查询“CS”系年龄最大的学生的基本信息。
SELECT * FROM Student WHERE Department = 'CS' AND Age = (SELECT MAX(Age) FROM Student WHERE Department = 'CS');

实验四


# --（1）查询“刘秀”医生诊治的病人的基本信息；
SELECT Patient.PatientName, Patient.Sex, Patient.Age, Patient.Department, CureFee.Fee
FROM Patient
INNER JOIN CureFee ON Patient.PatientID = CureFee.PatientID
INNER JOIN Doctor ON CureFee.DoctorID = Doctor.DoctorID
WHERE Doctor.DoctorName = '刘秀';

# --（2）用左外连接完成：查询所有医生诊治病人的情况；
SELECT Doctor.DoctorName, Patient.PatientName, Patient.Sex, Patient.Age, Patient.Department, CureFee.Fee
FROM Doctor
LEFT JOIN Patient ON Doctor.DoctorID = Patient.DoctorID
LEFT JOIN CureFee ON Patient.PatientID = CureFee.PatientID;

# --（3）查询就诊费用超过500元的病人的基本信息及就诊总费用；
SELECT Patient.PatientName, Patient.Sex, Patient.Age, Patient.Department, SUM(CureFee.Fee) AS TotalFee
FROM Patient
INNER JOIN CureFee ON Patient.PatientID = CureFee.PatientID
GROUP BY Patient.PatientName, Patient.Sex, Patient.Age, Patient.Department
HAVING SUM(CureFee.Fee) > 500;

# --（4）用嵌套查询完成：查询与王丹医生同一科室的医生的基本信息；
SELECT *
FROM Doctor
WHERE Department = (SELECT Department FROM Doctor WHERE DoctorName = '王丹');

# --（5）查询每个医生的病人就诊总费用超出该医生接诊所有病人的平均就诊总费用的医生ID、病人和诊治费用；
SELECT Doctor.DoctorID, Patient.PatientName, SUM(CureFee.Fee) AS TotalFee
FROM Doctor
INNER JOIN Patient ON Doctor.DoctorID = Patient.DoctorID
INNER JOIN CureFee ON Patient.PatientID = CureFee.PatientID
GROUP BY Doctor.DoctorID, Patient.PatientName
HAVING SUM(CureFee.Fee) > (SELECT AVG(TotalFee) FROM (SELECT Doctor.DoctorID, SUM(CureFee.Fee) AS TotalFee
FROM Doctor
INNER JOIN Patient ON Doctor.DoctorID = Patient.DoctorID
INNER JOIN CureFee ON Patient.PatientID = CureFee.PatientID
GROUP BY Doctor.DoctorID) AS T);

# --（6）查询至少诊治了p1和p3病人的医生ID。
SELECT DoctorID
FROM Patient
WHERE PatientID IN ('p1', 'p3')
GROUP BY DoctorID
HAVING COUNT(*) = 2;











# --（1）查询“IS”系学生的学号、所选课程名称及该门课程的成绩；
SELECT Student.StudentID, Course.CourseName, Score.Score
FROM Student
INNER JOIN Score ON Student.StudentID = Score.StudentID
INNER JOIN Course ON Score.CourseID = Course.CourseID
WHERE Student.Department = 'IS';

# --（2）查询“CS”系成绩不及格的学生姓名；
SELECT Student.StudentName
FROM Student
INNER JOIN Score ON Student.StudentID = Score.StudentID
WHERE Student.Department = 'CS' AND Score.Score < 60;

# --（3）查询每一门的课程的间接先修课程；
SELECT Course.CourseName, PreCourse.CourseName AS PreCourseName
FROM Course
INNER JOIN PreCourse ON Course.CourseID = PreCourse.CourseID;

# --（4）查询所有的学生的选课情况，要求没有选课的学生的信息也能在结果中显示；
SELECT Student.StudentID, Student.StudentName, Course.CourseName, Score.Score
FROM Student
LEFT JOIN Score ON Student.StudentID = Score.StudentID
LEFT JOIN Course ON Score.CourseID = Course.CourseID;

# --（5）查询每个学生超过他选修课程平均成绩的课程号，课程名称及成绩；
SELECT Score.StudentID, Course.CourseID, Course.CourseName, Score.Score
FROM Score
INNER JOIN Course ON Score.CourseID = Course.CourseID
WHERE Score.Score > (SELECT AVG(Score) FROM Score WHERE StudentID = Score.StudentID);

# --（6）查询“IS”系的学生以及‘数据库系统原理’成绩在70~80之间的学生；
SELECT Student.StudentID, Student.StudentName, Score.Score
FROM Student
INNER JOIN Score ON Student.StudentID = Score.StudentID
INNER JOIN Course ON Score.CourseID = Course.CourseID
WHERE Student.Department = 'IS' AND Course.CourseName = '数据库系统原理' AND Score.Score BETWEEN 70 AND 80;

# --（7）用两种方法实现：选修了“001”课程和“002”课程的学生学号；
# --方法一：使用子查询
SELECT StudentID
FROM Score
WHERE CourseID = '001' AND StudentID IN (SELECT StudentID FROM Score WHERE CourseID = '002');
# --方法二：使用INNER JOIN
SELECT S1.StudentID
FROM Score AS S1
INNER JOIN Score AS S2 ON S1.StudentID = S2.StudentID
WHERE S1.CourseID = '001' AND S2.CourseID = '002';

# --（8）查询至少选修了“001”课程和“002”课程的学生学号。
SELECT StudentID
FROM Score
WHERE CourseID IN ('001', '002')
GROUP BY StudentID
HAVING COUNT(DISTINCT CourseID) = 2;

实验五

试验六

点赞+关注，过几天有时间就更新，三连马上更新。