Navicate上sql练习（select中级篇）

前置条件：安装Navicate,以及配置好了mysql8.0以上

基本介绍：一个包含学生，课程和成绩的数据库结构,学习基本的分页查询和，排序查询，分组查询，连接查询

第一步新建查询

-- 创建数据库
CREATE DATABASE IF NOT EXISTS school_db DEFAULT CHARACTER SET utf8mb4;
USE school_db;

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男','女') NOT NULL,
    age TINYINT UNSIGNED NOT NULL,
    class VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    credit TINYINT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建成绩表
CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入学生数据
INSERT INTO students (name, gender, age, class) VALUES
('张三', '男', 20, '计算机1班'),
('李四', '男', 21, '计算机2班'),
('王芳', '女', 19, '软件工程1班'),
('陈伟', '男', 22, '网络工程1班'),
('刘洋', '女', 20, '计算机1班'),
('周杰', '男', 21, '软件工程2班'),
('吴倩', '女', 20, '网络工程1班'),
('郑浩', '男', 19, '计算机2班'),
('孙丽', '女', 21, '软件工程1班'),
('赵敏', '女', 20, '网络工程2班');

-- 插入课程数据
INSERT INTO courses (course_name, credit) VALUES
('数据库原理', 3),
('数据结构', 4),
('操作系统', 3),
('计算机网络', 4),
('Java编程', 3),
('Web开发', 3),
('算法设计', 4),
('Python基础', 2),
('C语言', 3),
('软件工程', 3);

-- 插入成绩数据
INSERT INTO scores (student_id, course_id, score) VALUES
(1, 1, 85.5), (1, 2, 78.0), (1, 3, 92.5),
(2, 1, 76.0), (2, 4, 88.5), (2, 5, 95.0),
(3, 2, 82.0), (3, 6, 79.5), (3, 7, 91.0),
(4, 3, 68.5), (4, 8, 85.0), (4, 9, 77.5),
(5, 4, 94.5), (5, 1, 81.0), (5, 10, 89.0),
(6, 5, 75.5), (6, 2, 88.0), (6, 3, 92.5),
(7, 6, 83.0), (7, 7, 79.0), (7, 8, 86.5),
(8, 9, 91.5), (8, 10, 84.0), (8, 1, 77.0),
(9, 2, 89.5), (9, 3, 93.0), (9, 4, 82.5),
(10, 5, 80.0), (10, 6, 87.5), (10, 7, 95.5);

-- 创建索引
CREATE INDEX idx_student_id ON scores(student_id);
CREATE INDEX idx_course_id ON scores(course_id);

第二步 在数据库中新建查询

第三步 完成以下4道题

第一题

分页查询students表，每页五条数据，查询第二页

在刚刚你添加的查询里写sql语句

SELECT * FROM students 
ORDER BY student_id 
LIMIT 5 OFFSET 5;

正确结果

第二题

排序查询students表按年龄降序

SELECT * FROM students 
ORDER BY age desc;

成功结果

第三题

分组查询 统计每个班级的学生数量

select class,count(*) as student_count
from students
group by class

成功结果

 

第四题

连接查询 查询成绩大于90分的学生名称，课程名称，以及分数

select s.name , c.course_name, sc.score
from scores sc
join students s on sc.student_id = s.student_id
join courses c on sc.course_id = c.course_id
where sc.score > 90

正确结果