002 sql语句

完成sql语句

学生Student（stu_id,stu_name,stu_age,stu_dept）

课程Course（course_id,course_name,course_teacher_name）,

选课Score（stu_id,course_id,grade）

其中stu_id代表学号，stu_name代表学生姓名，stu_age代表学生年龄，stu_dept代表学生所在系，course_id代表课程号，course_name代表课程名，course_teacher_name代表任课老师姓名，grade代表成绩 

• 请用SQL语句建立上面三张表，注意加上约束
• 将下列学生信息插入学生关系中：李丹，18岁，电信系，学号：20070206
• 找出选修了课程为“112002”的学生学号和姓名(2)
• 修改学号为“20070206”的学生所在的系为计算机(2)
• 查询选修了数据库系统原理这门课的学生的姓名和成绩(2)
• 统计出各门功课的最高分，显示课程编号，课程名称，与最高分 (2)
• 查询每门课程学生选课的人数，没有被选到的课程也要统计出来
• 查询出选课超过3门的学生信息

创建表

CREATE TABLE Student (  
    stu_id INT PRIMARY KEY,  
    stu_name VARCHAR(255) NOT NULL,  
    stu_age INT NOT NULL,  
    stu_dept VARCHAR(255) NOT NULL  
);  
  
CREATE TABLE Course (  
    course_id INT PRIMARY KEY,  
    course_name VARCHAR(255) NOT NULL,  
    course_teacher_name VARCHAR(255) NOT NULL  
);  
  
CREATE TABLE Score (  
    stu_id INT,  
    course_id INT,  
    grade FLOAT,  
    PRIMARY KEY (stu_id, course_id),  
    FOREIGN KEY (stu_id) REFERENCES Student(stu_id),  
    FOREIGN KEY (course_id) REFERENCES Course(course_id)  
);

插入学生信息

INSERT INTO Student (stu_id, stu_name, stu_age, stu_dept) VALUES (20070206, '李丹', 18, '电信系');

找出选修课程为“112002”的学生学号和姓名

SELECT s.stu_id, s.stu_name   
FROM Student s   
JOIN Score sc ON s.stu_id = sc.stu_id   
WHERE sc.course_id = 112002;

修改学号为“20070206”的学生所在的系为计算机

UPDATE Student   
SET stu_dept = '计算机'   
WHERE stu_id = 20070206;

查询选修了数据库系统原理这门课的学生的姓名和成绩

SELECT s.stu_name, sc.grade   
FROM Student s   
JOIN Score sc ON s.stu_id = sc.stu_id   
JOIN Course c ON sc.course_id = c.course_id   
WHERE c.course_name = '数据库系统原理';

统计出各门功课的最高分，显示课程编号，课程名称，与最高分

SELECT c.course_id, c.course_name, MAX(s.grade) AS max_grade  
FROM Course c  
JOIN Score s ON c.course_id = s.course_id  
GROUP BY c.course_id, c.course_name;

SELECT   
    c.course_id,   
    c.course_name,   
    (SELECT MAX(grade) FROM Score s WHERE s.course_id = c.course_id) AS max_grade  
FROM   
    Course c;



-- 这个查询的工作原理如下：

-- FROM Course c - 从Course表中选择所有的课程。
-- 对于Course表中的每一行（即每一门课程），执行子查询：
-- sql
-- (SELECT MAX(grade) FROM Score s WHERE s.course_id = c.course_id)
-- 这个子查询从Score表中找出当前课程（由外层查询的c.course_id指定）的最高成绩。

-- 最终，查询返回每门课程的编号（course_id）、名称（course_name）以及对应的最高成绩（max_grade）。
-- 这种方法允许在不使用GROUP BY子句的情况下，获取每门课程的最高分。请注意，虽然这种方法通常很有效，但如果Course表中的课程数量非常多，或者Score表中的记录非常多，这个查询的性能可能会受到影响，因为需要对Course表中的每一行执行一次子查询。在实际应用中，根据数据量和数据库的具体实现，使用GROUP BY可能会更高效。

所有课程中分数最高的那一门课及对应的分数

-- 这个语句会返回分数最高的课程ID、课程名和分数。注意，如果有多个课程有相同的最高分，这个查询只会返回其中一个

SELECT c.course_id, c.course_name, sc.grade   
FROM Score sc   
JOIN Course c ON sc.course_id = c.course_id   
WHERE sc.grade = (SELECT MAX(grade) FROM Score)   
LIMIT 1;

SELECT e.course_id, c.course_name, e.grade   
FROM Course c   
LEFT JOIN Score e ON e.course_id = c.course_id   
ORDER BY e.grade DESC   
LIMIT 1;

-- 这条语句会返回分数最高的课程和对应的分数，但请注意，由于使用了LEFT JOIN，即使某门课程没有分数（即Score表中没有对应记录），它也会出现在结果集中，但其分数会显示为NULL。在上面的ORDER BY e.grade DESC排序中，NULL值会被视为最低值。如果只关心有分数的课程，应该使用INNER JOIN代替LEFT JOIN。

查询每门课程学生选课的人数，没有被选到的课程也要统计出来

SELECT c.course_id, c.course_name, COUNT(sc.stu_id) AS student_count   
FROM Course c   
LEFT JOIN Score sc ON c.course_id = sc.course_id   
GROUP BY c.course_id, c.course_name;

查询选课超过3门的学生信息

SELECT s.*   
FROM Student s   
WHERE (SELECT COUNT(*) FROM Score sc WHERE sc.stu_id = s.stu_id) > 3;

-- 在这个查询中，我们首先通过JOIN将Student表和Score表连接起来，连接条件是学生的ID（stu_id）。然后，我们按照学生的ID以及其他需要选择的学生信息进行分组（GROUP BY）。HAVING子句用于在分组后过滤结果，只保留那些选课数（即Score表中与该学生相关的course_id的数量）超过3的学生。

-- 请注意，GROUP BY子句中包含了所有SELECT子句中提到的Student表的列，这是因为在使用GROUP BY时，SELECT子句中的非聚合列必须出现在GROUP BY子句中，这是SQL的标准要求。

SELECT s.*  
FROM Student s  
JOIN Score sc ON s.stu_id = sc.stu_id  
GROUP BY s.stu_id, s.stu_name, s.stu_age, s.stu_dept  
HAVING COUNT(sc.course_id) > 3;

-- 在这个查询中，我们使用了窗口函数COUNT(course_id) OVER (PARTITION BY stu_id)来计算每个学生的选课数，并将其作为一个子查询。然后，我们将这个子查询与Student表进行连接，并在外部查询中使用WHERE子句来过滤出选课数超过3的学生。这种方法通常比使用GROUP BY和HAVING更高效，因为它避免了分组操作。
SELECT DISTINCT s.*  
FROM Student s  
JOIN (  
    SELECT stu_id, COUNT(course_id) OVER (PARTITION BY stu_id) AS course_count  
    FROM Score  
) sc ON s.stu_id = sc.stu_id  
WHERE sc.course_count > 3;

EXISTS

SELECT product_name,sale_price
FROM PRODUCT AS P
WHERE EXISTS (SELECT * FROM SHOPPRODUCT AS SP
WHERE SP.shop_id = ‘00C’ AND SP.product_id = P.product_id);
其中PRODUCT表示商品表，SHOPPRODUCT指商店在售商品表，其中shop_id字段表示商店id，product_id表示产品id。
以上这段sql主要是完成了哪个操作？

这段SQL查询主要完成了从PRODUCT表中检索商品名称（product_name）和销售价格（sale_price）的操作，但它并不是简单地检索所有商品，而是有一个筛选条件：只检索在SHOPPRODUCT表中有对应shop_id为‘00C’的记录的商品。

EXISTS后面带的是一个子查询，这个子查询用于检查是否存在满足特定条件的记录。具体来说，这个子查询从SHOPPRODUCT表中查找shop_id为‘00C’且product_id与外层查询中的PRODUCT表的product_id相匹配的记录。

如果EXISTS后面的子查询至少返回一条记录，这意味着外层查询中的当前PRODUCT记录有一个或多个在SHOPPRODUCT表中与shop_id为‘00C’相关联的记录。只有当这个条件满足时，外层查询才会选择该PRODUCT记录。

这里的筛选逻辑是：

对于PRODUCT表中的每一条记录，都会执行一次子查询。
子查询检查SHOPPRODUCT表中是否存在与当前PRODUCT记录的product_id相匹配，并且shop_id为‘00C’的记录。
如果存在这样的记录，那么EXISTS条件为真，外层查询就会选择这条PRODUCT记录。
如果不存在这样的记录，EXISTS条件为假，这条PRODUCT记录就不会被选择。
因此，虽然EXISTS后面的子查询本身可能返回多条记录，但外层查询并不是简单地选择所有PRODUCT表中的记录。相反，它只选择那些在SHOPPRODUCT表中有对应shop_id为‘00C’的记录的商品。

总结来说，这段SQL查询的筛选逻辑是通过EXISTS子句和子查询来实现的，它确保了只有那些在指定商店（shop_id为‘00C’）中出售的商品才会被检索出来。