4.22 MYSQL 作业

-- 创建数据库
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;
 
 -- 插入数据

#1.查询全部课程的信息。
SELECT * FROM course;

#2.查询信工学院开设的课程名、课程号及学分。
SELECT cs_name AS '课程名', cs_id AS '学号',cs_credit AS '学分' FROM course ;

#3.查询学分超过3学分的课程代码、课程名和开课单位。
SELECT cs_id AS '课程代码', cs_name AS '课程名', cs_depart AS '开课单位'
FROM course WHERE  cs_credit >3;

#4.查询计科专业和大数据专业的学生信息。
SELECT * FROM student WHERE stu_major IN('计科','大数据');

#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 IN(17,18,19);

#7.查询学分在2到4之间课程的信息。
SELECT * FROM course WHERE cs_credit>=2 && cs_credit <=4;

#8.查询课程名称中带“数据”的课程名、课程号及开课单位。
SELECT cs_name AS '课程名', cs_id AS '课程号',cs_depart AS '开课单位' FROM course
WHERE cs_name LIKE '%数据%';

#9.查询信工学院的的专业有哪些。
SELECT stu_major AS '专业' FROM student WHERE stu_college='信工学院';

#10.查询年龄为空的学生信息。
SELECT * FROM student WHERE stu_age IS NULL;

#11.查询不是信工学院开设的集中实践课的开课单位和课程名称。
SELECT  cs_depart AS '开课单位',cs_name AS '课程名'  FROM course WHERE
cs_depart !='信工' AND cs_type='集中实践';

#12.查询信工学院开设的课程的类型有哪些。
SELECT cs_type AS '课程类型' FROM course WHERE cs_depart='信工';

#13.查询学生所在的专业个数。
SELECT  stu_major, COUNT(stu_major) FROM student GROUP BY stu_major;

#14.查询信工学院开设的课程的平均学分。
SELECT AVG(cs_credit) FROM course WHERE cs_depart='信工';

#15.查询学生的信息，查询结果按姓名升序排序。
SELECT * FROM student ORDER BY stu_name 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,COUNT(cs_depart) FROM course GROUP BY cs_depart  ORDER BY COUNT(cs_depart) ASC;
#18.查询单位开课门数少于2门的开课单位和课程名称。
SELECT cs_depart AS '开课单位' ,cs_name AS '课程名称' FROM course GROUP BY cs_depart
HAVING COUNT(cs_type)<2;