编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE goods(
	id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '商品编号',
	type varchar(30) NOT NULL COMMENT '商品类别',
    name varchar(30) unique COMMENT '商品名称',
    price DECIMAL(7,2) COMMENT '商品价格',
    num INT(11) DEFAULT 0 COMMENT '商品库存',
    add_time DATETIME COMMENT '添加时间'
);
INSERT INTO goods(id, type, name, price, num, add_time)
VALUES(1,'书籍','西游记',50.4,20,'2018-01-01 13:40:40'),
      (2,'糖类','牛奶糖',7.5,200,'2018-02-02 13:40:40'),
      (3,'糖类','水果糖',2.5,100,null),
      (4,'服饰','休闲西服',800,null,'2018-04-04 13:40:40'),
      (5,'饮品','果汁',2.5,70,'2018-05-05 13:40:40');
INSERT INTO goods(id, type, name, price, num, add_time)
VALUES(6,'书籍','论语',109,50,'2018-01-03 13:40:40'),
      (7,'水果','西瓜',1.5,null,'2018-02-05 13:40:40'),
      (8,'水果','苹果',3,100,'2018-03-05 13:40:40'),
      (9,'服饰','牛仔裤',120,10,'2018-05-04 13:40:40'),
      (10,'书籍','红楼梦',50.5,15,'2018-05-06 13:40:40');

CREATE TABLE student(
    ID INT PRIMARY KEY,
    xh VARCHAR(20) COMMENT '学号',
    xm VARCHAR(50) COMMENT '姓名',
    xb VARCHAR(10) COMMENT '性别',
    csny VARCHAR(20) COMMENT '出生年月',
    nj VARCHAR(20) COMMENT '年级',
    xy VARCHAR(50) COMMENT '学院'
);

INSERT INTO student (ID, xh, xm, xb, csny, nj, xy) 
VALUES(1, 'J171001', '张三丰', '男', '2001年3月', '21', '信息工程'),
      (2, 'J171002', '李静', '女', '2001年2月', '21', '工商管理'),
      (3, 'J171003', '张明', '男', '2001年3月', '21', '计算机科学与应用'),
      (4, 'J1721001', '王丽', '女', '2002年6月', '22', '轮机工程'),
      (5, 'J1721002', '李大成', '男', '2002年9月', '22', '机电工程'),
      (6, 'J1721002', '张白云', '女', '2000年4月', '22', '艺术学院'),
      (7, 'J1731003', '刘芳芳', '女', '2002年8月', '23', '材料学院'),
      (8, 'J1731003', '赵明明', '男', '2001年12月', '23', '智能工程学院');

Select 
      stu.xh AS 'account',
      stu.xm AS 'name',
      stu.xb AS 'gender',
      stu.csny AS 'birth',
      stu.nj AS 'grade',
      stu.xy AS 'department'
from student stu
where nj in('22','23');

Select 
      stu.xh AS 'account',
      stu.xm AS 'name',
      stu.xb AS 'gender',
      stu.csny AS 'birth',
      stu.nj AS 'grade',
      stu.xy AS 'department'
from student stu
where csny like '2002年%';

Select 
      stu.xh AS 'account',
      stu.xm AS 'name',
      stu.xb AS 'gender',
      stu.csny AS 'birth',
      stu.nj AS 'grade',
      stu.xy AS 'department'
from student stu
where xy='计算机科学与应用';

Select 
       stu.xb as 'gender', 
       count(*) 
       from student stu
       group by stu.xb;

Select 
      stu.xh AS 'account',
      stu.xm AS 'name',
      stu.xb AS 'gender',
      stu.csny AS 'birth',
      stu.nj AS 'grade',
      stu.xy AS 'department'
from student stu
where stu.xh='J1721001';


CREATE TABLE stu_learning_info (
    ID INT PRIMARY KEY,
    name VARCHAR(50),
    course_name VARCHAR(100),
    buy_time VARCHAR(20),
    view_duration INT,
    play_times INT,
    valid VARCHAR(10),
    last_score INT
);

INSERT INTO stu_learning_info (ID, name, course_name, buy_time, view_duration, play_times, valid, last_score) 
VALUES(1, '张三丰', '数据采集（一）', '2023年10月', 3600, 5, '是', 90),
      (2, '李静', '数据处理', '2022年11月', 3240, 4, '是', 85),
      (3, '张明', '数据标注工程', '2023年11月', 1120, 3, '是', 72),
      (4, '王丽', '数据分析', '2023年11月', 56, 2, '否', 60),
      (5, '李大成', '数据采集（一）', '2023年11月', 8200, 6, '是', 98),
      (6, '张白云', '数据采集（二）', '2023年12月', 450, 1, '是', 20),
      (7, '刘芳芳', '机器学习', '2023年11月', 3980, 2, '是', 40),
      (8, '赵明明', '机器学习', '2023年11月', 2500, 4, '是', 80);

SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s;

SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s
WHERE s.valid= '是';

SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s
WHERE s.view_duration > 1800;

SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s
WHERE s.buy_time like '2022年%';

SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s
WHERE s.course_name = '机器学习';

SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s
WHERE s.last_score between 60 AND 100;


SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s
WHERE s.play_times > 3;


SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s
WHERE s.buy_time='2023年11月' 
AND s.course_name='机器学习';

SELECT
      s.name AS '学生姓名',
      s.course_name AS '课程',
      s.buy_time AS '购买课程时间',
      s.view_duration AS '观看时长',
      s.play_times AS '累计播放次数',
      s.last_score AS '最近的模拟考成绩'
FROM stu_learning_info s
WHERE s.course_name='数据采集（一）'
AND s.valid='是';