sql语句基础练习1

学生管理系统-数据库模块

业务目标

​根据需求完善 sql 语句

技能目标

​掌握 SQL 语句的 CRUD

数据表

年级表(Grade)：年级 id(主键)-gradeid,年级名称(gname)

成绩表(score)：id(主键)-scoreid,学员编号-stuno，科目 id-subjectid,分数-score，考试时间-examtime

学生表(student)：学生编号(主键)-stuid，学生姓名-stuname，登录密码-password，性别-sex，年级 id-gid,电话-telphone，地址-address，出生日期-birthday，邮箱-email

科目表(subject)：科目 id(主键)-subjectid,科目名称-subjectname，课时-studycount，年级 id-gradeid

需求

1.grade 表增加一个阶段，“就业期”

2.将第三阶段的学生的 gradeid 改为就业期的 id

3.查询所有得了 100 分的学号

4.查询所有 1989 年出生的学生（1989-1-1~1990-1-1）

5.查询学生姓名为“金蝶”的全部信息

6.查询 subjectid 为 8 的科目考试未及格（60 分）的学号和成绩

7.查询第 3 阶段课时大于 50 的课程全部信息

8.查询 S1101001 学生的考试信息

9.查询所有第二阶段的女生信息

10.“基于.NET 平台的软件系统分层开发”需要多少课时

11.查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用 in)

12 查询所有地址在山东的学生信息

13 查询所有姓凌的单名同学

14.查询 gradeid 为 1 的学生信息，按出生日期升序排序

15.查询 subjectid 为 3 的考试的成绩信息，用降序排序

16.查询 gradeid 为 2 的课程中课时最多的课程信息

17.查询北京的学生有多少个

18.查询有多少个科目学时小于 50

19.查询 gradeid 为 2 的阶段总课时是多少

20.查询 subjectid 为 8 的课程学生平均分

21.查询 gradeid 为 3 的课程中最多的学时和最少的学时

22.查询每个科目有多少人次考试

23.每个阶段课程的平均课时

24.查询每个阶段的男生和女生个数（group by 两列）

任务过程

1. 了解数据表以及各列的含义

2. 分析需求，梳理数据表新职课教研教学中心

3. 根据语法，结合需求操作数据库

涉及知识点

1. MySql 数据表的创建

2. MySql CRUD 操作

源码如下

/**
 * @Author:cheng
 * @Date:2021-8-5
 */
-- 创建名为kkb01的库
create database if not exists kkb01;
/*
    年级表(Grade):年级 id(主键)-gradeid,年级名称(gname)
 */
create table grade(
    gradeid int unsigned not null primary key,
    gname varchar(5)
);
/*
    成绩表(score):id(主键)-scoreid,学员编号-stuno，科目 id-subjectid,分数-score，考试时间-examtime
 */
create table score(
    scoreid int not null primary key,
    stuno varchar(20),
    subjectid int(2),
    score varchar(3),
    examtime date
);
/*
    学生表(student):学生编号(主键)-stuid，学生姓名-stuname，登录密码-password，
    性别-sex，年级 id-gid,电话-telphone，地址-address，出生日期-birthday，邮箱-email
 */
create table student (
    stuid varchar(10) not null primary key,
    stuname varchar (10),
    passward varchar(10),
    sex enum('男', '女'),
    gid varchar(10),
    telphone varchar(20),
    address varchar(20),
    birthday date,
    email varchar(20)
);
/*
  科目表(subject):科目 id(主键)-subjectid,科目名称-subjectname，课时-studycount，年级 id-gradeid
 */
create table subject (
    subjectid int(2) not null primary key,
    subjectname varchar(20),
    subjectcount int(2),
    gradeid varchar(3)
);

-- 各需求解决如下
-- 1、grade 表增加一个阶段， “就业期”
insert into grade values(4,'就业期');
-- 2.将第三阶段的学生的 gradeid 改为就业期的 id
update student a set a.gid = '4' where a.gid = '3';
-- 3.查询所有得了 100 分的学号
select scoreid from score where score = 100;
-- 4.查询所有 1989 年出生的学生（1989-1-1~1990-1-1）
select stuname from student where birthday >= '1989-01-01' and birthday < '1990-01-01';
-- 5.查询学生姓名为“金蝶”的全部信息
select * from student where stuname = '金蝶';
-- 6.查询 subjectid 为 8 的科目考试未及格（60 分） 的学号和成绩
select score, stuno from score where subjectid = 8 and score < 60;
-- 7.查询第 3 阶段课时大于 50 的课程全部信息
select * from subject where subjectid = 3 and subjectcount > 50;
-- 8.查询 S1101001 学生的考试信息
select * from score where stuno = 'S1101001';
-- 9.查询所有第二阶段的女生信息
select * from student where gid = 2 and sex = '女';
-- 10.“基于.NET 平台的软件系统分层开发” 需要多少课时
select subjectcount from subject where subjectname = '基于.NET 平台的软件系统分层开发';
-- 11.查询“设计 MySchool 数据库” 和“面向对象程序设计” 的课时(使用in)
select subjectcount from subject where subjectname in ('设计 MySchool 数据库', '面向对象程序设计');
-- 12.查询所有地址在山东的学生信息
select * from student where address = '山东';
-- 13.查询所有姓凌的单名同学
select * from student where stuname like '凌_';
-- 14.查询 gradeid 为 1 的学生信息， 按出生日期升序排序
select * from student where gid = 1 order by birthday asc;
-- 15.查询 subjectid 为 3 的考试的成绩信息， 用降序排序
select * from score where subjectid = 3  order by score desc;
-- 16.查询 gradeid 为 2 的课程中课时最多的课程信息
select * from subject where gradeid = 2 and subjectcount = (select MAX(subjectcount) from subject where gradeid = 2);
-- 17.查询北京的学生有多少个
select COUNT(*) from student where address = '北京';
-- 18.查询有多少个科目学时小于 50
select COUNT(subjectcount) from subject where subjectcount < 50;
-- 19.查询 gradeid 为 2 的阶段总课时是多少
select SUM(subjectcount) from subject where gradeid = 2;
-- 20.查询 subjectid 为 8 的课程学生平均分
select AVG(score) from score where subjectid = 8;
-- 21.查询 gradeid 为 3 的课程中最多的学时和最少的学时
select MAX(subjectcount) from subject where gradeid = 3;
select Min(subjectcount) from subject where gradeid = 3;
-- 22.查询每个科目有多少人次考试
select subjectid, COUNT(*) from score where examtime is not null group by subjectid;
-- 23.每个阶段课程的平均课时
select gradeid, AVG(subjectcount) from subject group by gradeid;
-- 24.查询每个阶段的男生和女生个数（group by 两列）
select gid, sex, COUNT(*) from student group by gid, sex;