关于数据库原理与应用-基于大规模实践的数据思维与能力训练第8期实验的自我学习（2）

已于 2025-02-10 14:58:12 修改
阅读量1k 收藏 29
点赞数 9
文章标签： 数据库 大数据 学习
于 2025-02-09 21:45:05 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2301_76934422/article/details/145453804
版权

目录

三、国产数据库实例——以达梦数据库为例

3-1达梦数据库 - 表空间管理

第1关：表空间管理

3-2达梦数据库 - 模式管理

第1关：模式管理

3-3达梦数据库 - 表管理

第1关：表管理

3-4达梦数据库-数据查询

第1关：简单查询

第2关：条件查询

第3关：列运算与别名查询

第4关：函数查询

3-5达梦数据库 - 表数据操作

第1关：表数据操作

3-6达梦数据库 - 分区交换

第1关：分区交换

3-7达梦数据库 - 视图管理

3-8达梦数据库 - 索引管理

3-9达梦数据库 - 序列管理

3-10达梦数据库 - 同义词管理

3-11达梦数据库 - 数据库安全

四、关系型数据库实战——以MySQL为例

4-1MySQL数据库 - 初识MySQL

第1关：创建数据库

第2关：创建表

第3关：添加主键约束

第4关：添加外键约束

第5关：添加常用约束

第6关：视图

第7关：创建索引（单列索引）

第8关：创建索引（组合索引）

4-2MySQL数据库 -修改数据库

第1关：查看表结构与修改表名

第2关：修改字段名与字段数据类型

第3关：添加与删除字段

第4关：修改字段的排列位置

第5关：删除表的外键约束

4-3MySQL数据库 - 插入、更新、删除数据

第1关：插入数据

第2关：更新数据

第3关：删除数据

4-4MySQL数据库 - 单表查询

第1关：基本查询语句

第2关：带 IN 关键字的查询

第3关：带 BETWEEN AND 的范围查询

第4关：带 LIKE 的字符匹配查询

第5关：查询空值与去除重复结果

第6关：带 AND 与 OR 的多条件查询

第7关：使用 LIMIT 限制查询结果的数量

4-5MySQL数据库 - 连接查询和子查询

第1关：内连接查询

第2关：外连接查询

第3关：关键字子查询

第4关：分页子查询

4-6MySQL数据库 - 使用聚合函数查询

第1关：COUNT( )函数

第2关：SUM( )函数

第3关：AVG( )函数

第4关：MAX( )函数

第5关：MIN( )函数

4-7MySQL数据库 - 其他函数的使用

第1关：字符函数

第2关：数学函数

第3关：日期时间函数和流程控制类函数

第4关：自定义函数

4-8MySQL数据库 - 分组选择数据

第1关：对查询结果进行排序

第2关：分组查询

第3关：GROUP BY 与 聚合函数

第4关：使用 HAVING 与 ORDER BY

4-9MySQL数据库 - 复杂查询

第1关：分数排名

第2关：体育馆的人流量

第3关：统计总成绩

第4关：查询学生平均分

第5关：查询修课相同学生信息

第6关：查询各科成绩并排序

第7关：查询张老师课程成绩最高的学生信息

第8关：查询两门课程不及格同学信息

第9关：交换工资

第10关：换座位

三、国产数据库实例——以达梦数据库为例

3-1达梦数据库 - 表空间管理
第1关：表空间管理

3-2达梦数据库 - 模式管理
第1关：模式管理

3-3达梦数据库 - 表管理
第1关：表管理

3-4达梦数据库-数据查询
第1关：简单查询
-- 请在此编写sql语句
---------- Begin ----------
select * from stock.total;
---------- End ----------
-- 请勿删除下面语句
exit
第2关：条件查询
-- 请在此编写sql语句
---------- Begin ----------
SELECT 
    stu_id,
    stu_name,
    sex,
    birth,
    department,
    addr
FROM 
    test.student
WHERE 
    addr LIKE '河北省%' OR addr LIKE '安徽省%';
---------- End ----------
-- 请勿删除下面语句
exit
第3关：列运算与别名查询
-- 请在此编写sql语句
---------- Begin ----------
SELECT 
    stu_name,
    2022 - CAST(birth AS INTEGER) AS age
FROM 
    test.student;  
---------- End ----------
-- 请勿删除下面语句
exit
第4关：函数查询
-- 请在此编写sql语句
---------- Begin ----------
SELECT 
    stu_name,
    2021 - CAST(birth AS INTEGER) AS age,
    SUBSTR(addr, 1, 3) AS province
FROM 
    test.student
WHERE 
    addr LIKE '河北省%';
---------- End ----------
-- 请勿删除下面语句
exit
3-5达梦数据库 - 表数据操作
第1关：表数据操作
-- 请在此编写sql语句
---------- Begin ----------
-- 插入数据
insert into test.tb_salary(id,language,salary) values(6,'C++',9000);
-- 修改数据
UPDATE test.tb_salary SET salary=salary+500 where language='Java';
-- 删除数据
DELETE FROM  test.tb_salary where id=4;
commit;
---------- End ----------
-- 请勿删除下面语句
exit
3-6达梦数据库 - 分区交换
第1关：分区交换
-- 请在此编写代码
----------Begin----------
CREATE TABLE test.t_partitiontab (
    id INT,
    name VARCHAR(20)
) PARTITION BY RANGE (id) (
    -- 分区 p1 存储 id 小于 201 的数据
    PARTITION p1 VALUES LESS THAN (201),
    -- 分区 p2 存储 id 大于等于 201 且小于 401 的数据
    PARTITION p2 VALUES LESS THAN (401),
    -- 分区 p3 存储 id 大于等于 401 且小于 601 的数据
    PARTITION p3 VALUES LESS THAN (601),
    -- 分区 p4 存储 id 大于等于 601 且小于 801 的数据
    PARTITION p4 VALUES LESS THAN (801),
    -- 分区 p5 存储 id 大于等于 801 的数据
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);
-- 插入 1 - 1000 的测试数据
INSERT INTO test.t_partitiontab (id, name)
SELECT LEVEL, 'test_name'
FROM DUAL
CONNECT BY LEVEL <= 1000;
-- 提交插入操作
COMMIT;
-- 查询每个分区的数据行数
SELECT 
    ROW_NUMBER() OVER (ORDER BY PARTITION_NAME) AS 行号,
    PARTITION_NAME,
    NUM_ROWS AS NUM
FROM 
    DBA_TAB_PARTITIONS
WHERE 
    TABLE_NAME = 'T_PARTITIONTAB' AND OWNER = 'TEST';
 ----------End----------
 /
commit
/
exit
3-7达梦数据库 - 视图管理
第1关：视图管理
-- 请在此编写sql语句
---------- Begin ----------
-- 1.创建视图
CREATE VIEW test.salary_view  AS SELECT * FROM test.tb_salary WHERE language = 'Python';
-- 2.删除视图
DROP VIEW test.view_salary;
---------- End ----------
-- 请勿删除下面语句
exit
3-8达梦数据库 - 索引管理
第1关：索引管理
-- 请在此编写sql语句
---------- Begin ----------
-- 1.创建索引
CREATE UNIQUE INDEX test.index2 ON test.total(tradedate, tradetime);
-- 2.删除索引
DROP INDEX test.index1;
---------- End ----------
 /
commit
/
-- 请勿删除下面语句
exit
3-9达梦数据库 - 序列管理
第1关：序列管理
-- 请在此编写sql语句
---------- Begin ----------
-- 1.创建序列
CREATE SEQUENCE test.my_seq START WITH 10 INCREMENT BY 2 ORDER;
---------- End ----------
insert into test.tb_salary(id,language,salary) 
values(test.my_seq.NEXTVAL,'Java',8000),
    (test.my_seq.NEXTVAL,'Java',8400),
    (test.my_seq.NEXTVAL,'Java',9000),
    (test.my_seq.NEXTVAL,'Python',6500),
    (test.my_seq.NEXTVAL,'Python',10000);
-- 2.删除序列
---------- Begin ----------
DROP SEQUENCE test.my_seq;
---------- End ----------
-- 请勿删除下面语句
exit
3-10达梦数据库 - 同义词管理
第1关：同义词管理
-- 请在此编写sql语句
---------- Begin ----------
-- 1.创建同义词
CREATE  SYNONYM FOR test.tb_class '班级' ;
-- 2.删除同义词
DROP PUBLIC SYNONYM '学生';
---------- End ----------
-- 请勿删除下面语句
exit
3-11达梦数据库 - 数据库安全
第1关：用户管理
-- 请在此编写sql语句
---------- Begin ----------
-- 1.创建用户
CREATE USER user1 IDENTIFIED BY abcabcabc ;
-- 2.修改用户
ALTER USER user2 IDENTIFIED BY a88888888;
---------- End ----------
-- 请勿删除下面语句
exit
这里老是错，花了500金币发现我的答案和参考答案一样，真的是醉了。浪费了我500金币！！！

第2关：权限管理

-- 请在此编写sql语句
---------- Begin ----------
-- 1.给用户授权查询权限
GRANT SELECT ON test.total TO user1;
-- 2.收回用户权限
REVOKE INSERT ANY TABLE FROM user2;
---------- End ----------
-- 请勿删除下面语句
exit

四、关系型数据库实战——以MySQL为例

4-1MySQL数据库 - 初识MySQL
第1关：创建数据库
----##连接数据库##----
mysql -uroot -p123123
----##创建数据库##----
create database MyDb;
第2关：创建表
create table t_emp(
    id INT comment'员工编号',
    name VARCHAR(32) comment'员工名称',
    deptId INT comment'所在部门标号',
    salary FLOAT comment'工资');
第3关：添加主键约束
----##创建数据库###----
create database TestDb;
----##创建表##----
create table t_user1(
    userId INT comment'用户ID',
    name VARCHAR(32) comment'用户名',
    password VARCHAR(11) comment'密码',   
    phone VARCHAR(11) comment'电话号码',
    email VARCHAR(32) comment'邮箱',
    PRIMARY KEY(userId)); 
create table t_user2(
    name VARCHAR(32) comment'用户名',
    phone VARCHAR(11) comment'电话号码',
    email VARCHAR(32) comment'邮箱',
    PRIMARY KEY(name,phone));
第4关：添加外键约束
create table t_class(
    id INT PRIMARY KEY comment'班级Id',
    name VARCHAR(22) comment'班级名称');
create table t_student(
    id INT comment'学号',
    name VARCHAR(22) comment'学生姓名',
    classId	INT	comment'班级ID',
    CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id)
);
第5关：添加常用约束
----##使用 MyDb 数据库##---
USE MyDb;
----##创建表##----
CREATE TABLE t_user (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户 ID',
    username VARCHAR(32) UNIQUE NOT NULL COMMENT '用户名',
    sex VARCHAR(4) DEFAULT '男' COMMENT '性别'
) DEFAULT CHARSET = utf8;

----##显示表结构，模拟预期输出##----
SHOW COLUMNS FROM t_user;

此处的代码我的运行结果与预期结果相同，但是未通过，不知道是什么原因

第6关：视图
use School;
#请在此处添加实现代码
########## Begin ##########
#1.创建单表视图
CREATE VIEW stu_view AS
SELECT 
    math,
    chinese,
    math + chinese AS 'math+chinese'
FROM 
    student;
#2.创建多表视图
CREATE VIEW stu_classes AS
SELECT 
    s.stu_id,
    s.name,
    si.classes
FROM 
    student s
JOIN 
    stu_info si ON s.stu_id = si.stu_id;
########## End ##########
第7关：创建索引（单列索引）
USE Students;
#请在此处添加实现代码
########## Begin ##########
-- 1. 创建 student 表结构并且设置 id 为主键索引
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `score` int(11), -- 添加 score 字段
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- 2. 对 name 建立唯一索引
CREATE UNIQUE INDEX name_index ON `student`(`name`);

-- 3. 对 score 建立普通索引
CREATE INDEX score_index ON `student`(`score`);

-- 显示表的索引信息
SHOW INDEX FROM student;
########## End ##########

此处的代码我的运行结果与预期结果相同，但是未通过，不知道是什么原因

第8关：创建索引（组合索引）
USE Person;
#请在此处添加实现代码
########## Begin ##########
#1.增加组合索引
CREATE INDEX name_city_score ON person(name, age, address);
########## End ##########
SHOW INDEX FROM person;
4-2MySQL数据库 -修改数据库
第1关：查看表结构与修改表名
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## modify the table name ##########
ALTER TABLE tb_emp RENAME TO jd_emp;
########## show tables in this database ##########
SHOW TABLES;
########## describe the table ##########
DESCRIBE jd_emp;
########## End ##########
第2关：修改字段名与字段数据类型
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## change the column name ##########
ALTER TABLE tb_emp CHANGE Id prod_id INT(11);
########## change the data type of column ##########
ALTER TABLE tb_emp MODIFY Name varchar(30);
########## End ##########
DESCRIBE tb_emp;
第3关：添加与删除字段
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## add the column ##########
ALTER TABLE tb_emp ADD Country varchar(20) AFTER Name;
########## delete the column ##########
ALTER TABLE tb_emp DROP Salary;
########## End ##########
DESCRIBE tb_emp;
第4关：修改字段的排列位置
USE Company;
#请在此处添加实现代码
########## Begin ##########
-- 将 Name 列修改为 VARCHAR(25) 类型并移动到第一列
ALTER TABLE tb_emp MODIFY COLUMN Name VARCHAR(25) FIRST;
-- 将 DeptId 列修改为 INT(11) 类型并移动到 Salary 列之后
ALTER TABLE tb_emp MODIFY COLUMN DeptId INT(11) AFTER Salary;
########## End ##########
-- 查看表结构
DESCRIBE tb_emp;
第5关：删除表的外键约束
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## delete the foreign key ##########
ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;
########## End ##########
SHOW CREATE TABLE tb_emp \G;
4-3MySQL数据库 - 插入、更新、删除数据
第1关：插入数据
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## bundle insert the value ##########
INSERT INTO tb_emp (Id, Name, DeptId, Salary) 
VALUES 
    (1, 'Nancy', 301, 2300.00), 
    (2, 'Tod', 303, 5600.00),
    (3, 'Carly', 301, 3200.00);
########## End ##########
SELECT * FROM tb_emp;
第2关：更新数据
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## update the value ##########
UPDATE tb_emp 
SET 
    Name = 'Tracy', DeptId = 302, Salary = 4300.00 
WHERE 
    Name = 'Carly';
########## End ##########
SELECT * FROM tb_emp;
第3关：删除数据
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## delete the value ##########
DELETE FROM tb_emp WHERE Salary > 3000;
########## End ##########
SELECT * FROM tb_emp;
4-4MySQL数据库 - 单表查询
第1关：基本查询语句
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## retrieving the Name and Salary ##########
SELECT Name, Salary FROM tb_emp;
########## retrieving all the table ##########
SELECT * FROM tb_emp;
########## End ##########
第2关：带 IN 关键字的查询
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## retrieving the Name and Salary with IN statement ##########
SELECT Name, Salary FROM tb_emp WHERE Id != 1;
########## End ##########
第3关：带 BETWEEN AND 的范围查询
USE Company;
#请在此处添加实现代码
########## Begin ##########
########## retrieving the Name and Salary with BETWEEN AND statement ##########
SELECT Name, Salary FROM tb_emp WHERE Salary BETWEEN 3000 AND 5000;
########## End ##########
第4关：带 LIKE 的字符匹配查询
USE Company;
######### Begin #########
SELECT Name, Salary FROM tb_emp WHERE Name LIKE 'C%';
######### End #########
第5关：查询空值与去除重复结果
USE Company;
######### Begin #########
SELECT * FROM tb_emp WHERE DeptId IS NULL;
######### End #########
######### Begin #########
SELECT DISTINCT Name FROM tb_emp;
######### End #########
第6关：带 AND 与 OR 的多条件查询
USE Company;
######### Begin #########
SELECT * FROM tb_emp WHERE DeptId = 301 AND Salary > 3000;
######### End #########
######### Begin #########
SELECT * FROM tb_emp WHERE DeptId IN (301, 303);
######### End #########
第7关：使用 LIMIT 限制查询结果的数量
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询班级中第2名到第5名的学生信息 ##########
SELECT *
FROM tb_score
ORDER BY score DESC
LIMIT 1, 4;
########## End ##########
4-5MySQL数据库 - 连接查询和子查询
第1关：内连接查询
USE School;
########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
SELECT 
    s.name AS studentName,
    c.name AS className
FROM 
    tb_student s
JOIN 
    tb_class c ON s.class_id = c.id
WHERE
    s.class_id IS NOT NULL;
########## End ##########
第2关：外连接查询
USE School;
########## 使用左外连接查询所有学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
SELECT 
    s.name AS studentName,
    c.name AS className
FROM 
    tb_student s
LEFT JOIN 
    tb_class c ON s.class_id = c.id;
########## End ##########
########## 使用右外连接查询所有学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
SELECT 
    s.name AS studentName,
    c.name AS className
FROM 
    tb_student s
RIGHT JOIN 
    tb_class c ON s.class_id = c.id;
########## End ##########
第3关：关键字子查询
USE Company;
#请在此处添加实现代码
########## Begin ##########
#1.使用 ALL 关键字进行查询
SELECT position, salary
FROM tb_salary
WHERE
    salary > (
        SELECT MAX(salary) 
            FROM tb_salary 
                WHERE position = 'Java'
        );
#2.使用 ANY 关键字进行查询
SELECT position, salary
FROM tb_salary
WHERE 
    salary > (
        SELECT MIN(salary) 
            FROM tb_salary 
                WHERE position = 'Java'
                );
#3.使用 IN 关键字进行查询
SELECT position, salary
FROM tb_salary
WHERE 
    position = 'Java';
########## End ##########
第4关：分页子查询
USE Products;
#请在此处添加实现代码
########## Begin ##########
#1.分页查询
SELECT prod_id
FROM products
ORDER BY prod_id
LIMIT 5 OFFSET 5;
#2.用子查询优化分页查询语句
SELECT prod_id
FROM (
    SELECT prod_id
    FROM products
    ORDER BY prod_id
) AS subquery
LIMIT 5 OFFSET 10;
########## End ##########
4-6MySQL数据库 - 使用聚合函数查询
第1关：COUNT( )函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询该表中一共有多少条数据 ##########
select count(*) from tb_class;
########## 查询此表中367班有多少位学生 ##########
select classid ,count(id) as 'count(*)'
from tb_class
where classid=367;
########## End ##########
第2关：SUM( )函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询所有学生总分数 ##########
select sum(score) from tb_class;
########## 查询学生语文科目的总分数 ##########
select course,sum(score) as 'sum(score)' 
from tb_class
where course='语文';
########## End ##########
第3关：AVG( )函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询学生语文科目的平均分数 ##########
SELECT 
    '语文' AS course,
     AVG(score) AS `avg(score)` 
FROM 
    tb_class 
WHERE 
    course = '语文';
########## 查询学生英语科目的平均分数 ##########
SELECT 
    '英语' AS course, 
    AVG(score) AS `avg(score)` 
FROM 
    tb_class 
WHERE 
    course = '英语';
########## End ##########
第4关：MAX( )函数
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询语文课程中的最高分数 ##########
SELECT 
    '语文' AS course,
     MAX(score) AS `max(score)` 
FROM 
    tb_class 
WHERE 
    course = '语文'; 
########## 查询英语课程中的最高分数 ##########
SELECT 
    '英语' AS course,
     MAX(score) AS `max(score)` 
FROM 
    tb_class 
WHERE 
    course = '英语'; 
########## End ##########
第5关：MIN( )函数

USE School;
#请在此处添加实现代码
########## Begin ##########
########## 查询语文课程中的最低分数 ##########
SELECT 
    '语文' AS course,
     MIN(score) AS `min(score)` 
FROM 
    tb_class 
WHERE 
    course = '语文'; 
########## 查询英语课程中的最低分数 ##########
SELECT 
    '英语' AS course,
     MIN(score) AS `min(score)` 
FROM 
    tb_class 
WHERE 
    course = '英语'; 
########## End ##########
4-7MySQL数据库 - 其他函数的使用
第1关：字符函数
#请在此添加实现代码
########## Begin ##########
-- 假设表名为 employee
SELECT 
    CONCAT(UPPER(LEFT(Name, 1)), 
    LOWER(SUBSTRING(Name, 2))) AS Name
FROM 
    employee;
########## End ##########
第2关：数学函数

--#请在此添加实现代码
--########## Begin ##########
update Score set s_score=TRUNCATE(s_score - ROUND(SQRT((POWER(4,4)-POWER(3,3))/POWER(2,2)),2),2);
--########## End ##########
第3关：日期时间函数和流程控制类函数
#请在此添加实现代码
--########## Begin ##########
-- 查询所有学生的出生年份及至2019-01-01时的年龄
select year(s_birth) as year,(2021-01-01)-year(s_birth)  as '年龄' from Student;
-- 查询所有课程中的最高分、最低分、平均分和及格率，按课程id升序输出
SELECT 
    s.c_id AS `课程id`,
    c.c_name AS `课程名`,
    MAX(s.s_score) AS `最高分`,
    MIN(s.s_score) AS `最低分`,
    ROUND(AVG(s.s_score), 2) AS `平均分`,
    ROUND(SUM(CASE WHEN s.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s.s_score) * 100, 2) AS `及格率` 
FROM 
    Score s
JOIN 
    Course c ON s.c_id = c.c_id
GROUP BY 
    s.c_id, c.c_name
ORDER BY 
    s.c_id ASC;
--########## End ##########
第4关：自定义函数

#请在此添加实现代码
########## Begin ##########
create function fn_three_max(param_1 int,param_2 int, param_3 int)returns int
return 999;
########## End ##########
4-8MySQL数据库 - 分组选择数据
第1关：对查询结果进行排序
USE School;
#请在此处添加实现代码
########## Begin #########
####查询1班同学的所有信息以成绩降序的方式显示结果 ####
SELECT * FROM tb_score 
    WHERE class_id=1
    ORDER BY score DESC;
########## End ##########
第2关：分组查询
USE School;
#请在此处添加实现代码
########## Begin ##########
########## 对班级名称进行分组查询 ##########
SELECT * FROM tb_class GROUP BY class_id;
########## End ##########
第3关：GROUP BY 与 聚合函数
USE School;
#请在此处添加实现代码
########## Begin ##########
#1.查询表中2,3,4年级中分别男女的总人数
SELECT 
    gradeId,
    sex,
    COUNT(stuId) AS `count(*)`
FROM 
    student
WHERE 
    gradeId IN (2, 3, 4)
GROUP BY 
    gradeId, sex
ORDER BY 
    gradeId, sex;
########## End ##########
第4关：使用 HAVING 与 ORDER BY
USE School;
#请在此处添加实现代码
########## Begin ##########
#1.查询表中至少有两门课程在90分以上的学生信息
SELECT 
    sno,
    COUNT(*) AS `count(*)`
FROM 
    tb_grade
WHERE 
    score >= 90
GROUP BY 
    sno
HAVING 
    COUNT(*) >= 2;
#2.查询表中平均成绩大于90分且语文课在95分以上的学生信息
########## End ##########
SELECT 
    tg.sno,
    ROUND(AVG(tg.score), 4) AS `avg(score)`
FROM 
    tb_grade tg
JOIN (
    -- 子查询筛选出语文成绩大于等于 95 分的学生学号
    SELECT 
        sno
    FROM 
        tb_grade
    WHERE 
        pno = '语文' AND score >= 95
) qualified_students ON tg.sno = qualified_students.sno
GROUP BY 
    tg.sno
HAVING 
    AVG(tg.score) >= 90;
4-9MySQL数据库 - 复杂查询
第1关：分数排名
#请在此添加实现代码
########## Begin ##########
-- 情况一：平分后的下一个名次是下一个连续的整数值，名次之间无“间隔”
-- 创建一个临时表，将分数去重并按降序排序
SELECT 
    s1.Score,
    (
        -- 子查询统计大于等于当前分数的去重后的分数数量，即为排名
        SELECT COUNT(DISTINCT s2.Score)
        FROM score s2
        WHERE s2.Score >= s1.Score
    ) AS `Rank`
FROM 
    score s1
GROUP BY 
    s1.Score, s1.Id
ORDER BY 
    s1.Score DESC;

-- 情况二：排名是非连续的
-- 创建一个临时表，将分数去重并按降序排序
SELECT 
    s1.Score,
    (
        -- 子查询统计大于当前分数的分数数量加 1，即为排名
        SELECT COUNT(*) + 1
        FROM score s2
        WHERE s2.Score > s1.Score
    ) AS `Rank`
FROM 
    score s1
GROUP BY 
    s1.Score, s1.Id
ORDER BY 
    s1.Score DESC;
########## End ##########
第2关：体育馆的人流量
#请在此添加实现代码
--########## Begin ##########
SELECT 
    id,
    `date`,
    visitors_flow
FROM (
    SELECT 
        id,
        `date`,
        visitors_flow,
        id - (SELECT COUNT(*) FROM gymnasium g2 WHERE g2.id <= g1.id AND g2.visitors_flow >= 100) AS grp
    FROM 
        gymnasium g1
    WHERE 
        g1.visitors_flow >= 100
) subquery
WHERE 
    grp IN (
        SELECT 
            grp
        FROM (
            SELECT 
                id - (SELECT COUNT(*) FROM gymnasium g2 WHERE g2.id <= g1.id AND g2.visitors_flow >= 100) AS grp
            FROM 
                gymnasium g1
            WHERE 
                g1.visitors_flow >= 100
        ) inner_subquery
        GROUP BY 
            grp
        HAVING 
            COUNT(*) >= 3
    )
ORDER BY 
    id;
--########## End ##########
第3关：统计总成绩
#请在此添加实现代码
########## Begin ##########
SELECT 
    tc.classname,
    SUM(CASE WHEN ts.chinese >= 60 THEN ts.chinese ELSE 0 END) AS chinese,
    SUM(CASE WHEN ts.maths >= 60 THEN ts.maths ELSE 0 END) AS maths
FROM 
    tb_score ts
JOIN 
    tb_class tc ON ts.name = tc.stuname
GROUP BY 
    tc.classname
ORDER BY 
    tc.classname;
########## End ##########
第4关：查询学生平均分
#请在此添加实现代码
########## Begin ##########
SELECT 
    s.s_id,
    s.s_name,
    ROUND(IFNULL(AVG(sc.s_score), 0), 2) AS avg_score
FROM 
    student s
LEFT JOIN 
    score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    ROUND(IFNULL(AVG(sc.s_score), 0), 2) < 60;
########## End ##########
第5关：查询修课相同学生信息
#请在此添加实现代码
########## Begin ##########
-- 子查询找出 s_id = 01 学习的课程数量
SELECT 
    s.s_id,
    s.s_name,
    s.s_sex
FROM 
    student s
JOIN (
    -- 对每个学生学习的课程进行分组统计
    SELECT 
        sc.s_id,
        GROUP_CONCAT(sc.c_id ORDER BY sc.c_id) AS course_list,
        COUNT(DISTINCT sc.c_id) AS course_count
    FROM 
        score sc
    GROUP BY 
        sc.s_id
) student_course_summary ON s.s_id = student_course_summary.s_id
-- 筛选出学习课程数量和课程集合都与 s_id = 01 相同的学生
WHERE 
    student_course_summary.course_count = (
        SELECT COUNT(DISTINCT c_id) 
        FROM score 
        WHERE s_id = '01'
    )
    AND student_course_summary.course_list = (
        SELECT GROUP_CONCAT(c_id ORDER BY c_id) 
        FROM score 
        WHERE s_id = '01'
    )
    AND s.s_id != '01';
########## End ##########
第6关：查询各科成绩并排序

#请在此添加实现代码
########## Begin ##########
select a.* ,count(b.s_score)+1 rank from score a left join score b
on a.c_id = b.c_id and a.s_score < b.s_score
group by a.c_id,a.s_id
order by a.c_id,count(b.s_score);
########## End ##########
第7关：查询张老师课程成绩最高的学生信息
--#请在此添加实现代码
--########## Begin ##########
-- 查询选修“张三”老师所授课程且成绩最高的学生信息及其成绩
SELECT 
    s.s_id,
    s.s_name,
    s.s_sex,
    sc.s_score,
    c.c_id,
    c.c_name
FROM 
    student s
JOIN 
    score sc ON s.s_id = sc.s_id
JOIN 
    course c ON sc.c_id = c.c_id
JOIN 
    teacher t ON c.t_id = t.t_id
WHERE 
    t.t_name = '张三'
    AND sc.s_score = (
        SELECT 
            MAX(s_score)
        FROM 
            score sc2
        JOIN 
            course c2 ON sc2.c_id = c2.c_id
        JOIN 
            teacher t2 ON c2.t_id = t2.t_id
        WHERE 
            t2.t_name = '张三'
    );
--########## End ##########
第8关：查询两门课程不及格同学信息
--#请在此添加实现代码
--########## Begin ##########
-- 查询两门及其以上不及格课程的同学的学号、姓名及其平均成绩
SELECT 
    s.s_id,
    s.s_name,
    ROUND(AVG(sc.s_score)) AS avg_score
FROM 
    student s
JOIN 
    score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) >= 2;
--########## End ##########
第9关：交换工资
--#请在此添加实现代码
--########## Begin ##########
UPDATE tb_Salary
SET sex =
case
    when sex='f' then 'm'
    ELSE 'f'
    END;
--########## End ##########
第10关：换座位

--#请在此添加实现代码
--########## Begin ##########
SELECT 
    -- 对于座位号为偶数的学生，将其座位号减 1
    CASE 
        WHEN id % 2 = 0 THEN id - 1
        -- 对于座位号为奇数且不是最后一个座位的学生，将其座位号加 1
        WHEN id % 2 = 1 AND id < (SELECT MAX(id) FROM tb_Seat) THEN id + 1
        -- 对于座位号为奇数且是最后一个座位的学生，保持座位号不变
        ELSE id
    END AS id,
    name
FROM 
    tb_Seat
ORDER BY 
    id;
--########## End ##########

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值