2022-4-22作业-MySQL单表查询

1 准备数据

-- 创建数据库 
create database mydb charset utf8;
-- 使用数据库
use mydb;
-- 创建课程表 
CREATE TABLE course (
    cs_id int(11) NOT NULL COMMENT '课程编号',
    cs_name varchar(50) NOT NULL COMMENT '课程名称',
    cs_credit tinyint(255) unsigned DEFAULT NULL COMMENT '课程学分',
    cs_type char(12) DEFAULT NULL COMMENT '课程类别',
    cs_depart char(6) DEFAULT NULL COMMENT '院系名称',
    PRIMARY KEY (cs_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据 
insert into course
(cs_id,cs_name,cs_credit,cs_type,cs_depart) values
(5200313,'数据库原理及应用',4,'核心专业','信工'),
(5203314,'计算机导论',4,'通识教育','信工'),
(5219314,'数据结构',5,'专业核心','信工'),
(5223013,'大学物理',4,'专业基础','信工'),
(5227614,'毕业实习',4,'集中实践','信工'),
(5230912,'云计算',2,'共同选修','信工'),
(5236212,'机器学习',2,'共同选修','信工'),
(5237514,'c语言',4,'专业基础','信工'),
(5245112,'区块链',2,'任意选修','信工'),
(7200422,'知识产权法',2,'任意选修','文法'),
(20201833,'概率论',3,'专业基础','基础'),
(20202336,'高等数学',6,'专业基础','基础'),
(29299131,'劳动教育',1,'集中实践','学务');

-- 创建 学生表
CREATE TABLE student (
    stu_id bigint(11) unsigned NOT NULL COMMENT '学号',
    stu_name char(12) NOT NULL COMMENT '姓名',
    stu_sex enum('男','女') DEFAULT NULL COMMENT '性别',
    stu_age tinyint(255) unsigned DEFAULT NULL COMMENT '年龄',
    stu_major char(9) DEFAULT NULL COMMENT '专业',
    stu_college char(12) DEFAULT NULL COMMENT '学院',
    PRIMARY KEY (stu_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 -- 插入数据
insert into student
(stu_id,stu_name,stu_sex,stu_age,stu_major,stu_college) values
(201804550101,'郭奎','男',22,'计科','信工学院'),
(201804550102,'吕宇航','男',18,'计科','信工学院'),
(201804550103,'张豪辉','女',19,'计科','信工学院'),
(201804550107,'丁志杰','男',17,'金融学','金贸学院'),
(201804550109,'范伟','男',19,'金融学','金贸学院'),
(201804550116,'张依婷','女',17,'大数据','信工学院'),
(201804550120,'张维','男',19,'计科','信工学院'),
(201804550121,'朱柳阳','女',20,'计科','信工学院'),
(201804550144,'谭兵炎','男',20,'大数据','信工学院'),
(201804550153,'杨志强','男',17,'大数据','信工学院');

 
 

 2 需求

1.查询全部课程的信息。

SELECT cs_type AS "课程名称" FROM course GROUP BY cs_type;

2.查询信工学院开设的课程名、课程号及学分。

SELECT cs_name AS "课程名称" , cs_id AS "课程名称", cs_credit AS "学分" FROM course;

3.查询学分超过3学分的课程代码、课程名和开课单位。

SELECT cs_name AS "课程名称" , cs_id AS "课程代码", cs_credit AS "学分" 
FROM course
WHERE cs_credit > 3;

4.查询计科专业和大数据专业的学生信息。

SELECT *
FROM student
WHERE stu_major = '计科' OR stu_major = '大数据';

5.查询不是信工学院的学生姓名和学号。

SELECT stu_name AS "学生姓名" , stu_id AS "学号"
FROM student
WHERE stu_college != '信工学院' ;

6.查询年龄是17,18,19的学生姓名和专业。

SELECT stu_name AS "学生姓名" , stu_major AS "专业"
FROM student
WHERE stu_age = 17 OR 18 OR 19 ;

7.查询学分在2到4之间课程的信息。

SELECT *
FROM course
WHERE cs_credit BETWEEN 2 AND 4;

8.查询课程名称中带“数据”的课程名、课程号及开课单位。

SELECT cs_name AS "课程名" , cs_id AS "课程号",cs_depart AS "开课单位"
FROM course
WHERE cs_name LIKE ('%数据%');

9.查询信工学院的的专业有哪些。

SELECT DISTINCT(stu_major) AS  "专业名称"
FROM student
WHERE stu_college = '信工学院' ;

10.查询年龄为空的学生信息。

SELECT stu_name AS  "学生姓名"
FROM student
WHERE stu_age != 0 ;

11.查询不是信工学院开设的集中实践课的开课单位和课程名称。

SELECT cs_name AS "课程名" , cs_id AS "课程号"
FROM course
WHERE cs_type = '集中实践' AND cs_credit != '信工学院';

12.查询信工学院开设的课程的类型有哪些。

SELECT DISTINCT(cs_type)
FROM course
WHERE cs_depart = '信工' ;

13.查询学生所在的专业个数。

SELECT DISTINCT( stu_major ) AS "专业名称" ,COUNT(*) AS "专业个数"
FROM student
GROUP BY stu_major;

虽然题目是查询专业个数，但是不显示出名字感觉很呆

14.查询信工学院开设的课程的平均学分。

SELECT AVG(cs_credit) AS "平均学分"
FROM course
WHERE cs_depart = '信工' ;

15.查询学生的信息，查询结果按姓名升序排序。

SELECT *
FROM student
ORDER BY CONVERT( stu_name USING gbk) ASC;

16.查询 每个专业的学生的最大年龄、最小年龄和平均年龄，查询结果按平均年龄降序排列。

SELECT stu_major AS "专业" ,MAX(stu_age) AS "最高",MIN(stu_age) AS "最小",AVG(stu_age) AS "平均"
FROM student
GROUP BY stu_major
ORDER BY AVG(stu_age)  DESC;

17.查询每个开课单位开设的课程门数的数量，直询结果按课程门数升序排列。

SELECT cs_depart AS "课程",COUNT(*) AS "课程门数"
FROM course
GROUP BY cs_depart
ORDER BY COUNT(*) ASC; 

18.查询单位开课门数少于2门的开课单位和课程名称。

SELECT cs_depart AS "开课单位",cs_name AS "课程名称"
FROM course
GROUP BY cs_depart
HAVING COUNT(*) < 2;