USE hutao;

CREATE TABLE IF NOT EXISTS
    student_info
(
    number INT PRIMARY KEY COMMENT '学生ID信息表的主键',
    name VARCHAR(5) NOT NULL COMMENT '学生姓名',
    sex ENUM('男', '女') COMMENT '学生性别',
    id_number CHAR(18) COMMENT '身份证',
    department VARCHAR(30) COMMENT '学院',
    major VARCHAR(30) COMMENT '专业',
    enrollment_time DATE COMMENT '入学时间',
    UNIQUE KEY id_number (id_number)
) COMMENT '学生信息表';

CREATE TABLE IF NOT EXISTS
    student_score
(
    number INT COMMENT '学生ID',
    subject VARCHAR(30) COMMENT '学科',
    score INT NOT NULL COMMENT '成绩',
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY (number) REFERENCES student_info(number)
) COMMENT '学生成绩表';

SHOW CREATE TABLE student_info;

INSERT INTO
    student_info(number, name, sex, id_number, department, major, enrollment_time)
VALUES
    (20210101, '狗哥', '男', '158177200301044792', '计算机学院', '计算机科学与工程', '2021-09-01'),
    (20210102, '猫爷', '男', '151008200201178529', '计算机学院', '计算机科学与工程', '2021-09-01'),
    (20210103, '艾希', '女', '17156320010116959X', '计算机学院', '软件工程', '2021-09-01'),
    (20210104, '亚索', '男', '141992200201078600', '计算机学院', '计算机科学与工程', '2021-09-01'),
    (20210105, '莫甘娜', '女', '181048200008156368', '航天学院', '飞行器设计', '2021-09-01'),
    (20210106, '赵信', '男', '197995200201078445', '航天学院', '电子信息', '2021-09-01');

INSERT INTO
    student_score(number, subject, score)
VALUES
    (20210101, '计算机是怎样运行的', 78),
    (20210101, 'MySQL是怎样运行的', 88),
    (20210102, '计算机是怎样运行的', 100),
    (20210102, 'MySQL是怎样运行的', 98),
    (20210103, '计算机是怎样运行的', 59),
    (20210103, 'MySQL是怎样运行的', 61),
    (20210104, '计算机是怎样运行的', 55),
    (20210104, 'MySQL是怎样运行的', 46);


USE hutao;
SELECT * FROm student_info;
SELECT * FROM student_score;
# 查询单列
SELECT student_info.number FROM student_info;
# 列的别名
SELECT number '学号' FROM student_info;
# 查询多列
SELECT number, name, id_number, major FROM student_info;
SELECT number '学号', name '姓名', id_number '身份证号', major '专业' FROM student_info;
# 查询去重
SELECT DISTINCT department FROM student_info;
SELECT DISTINCT department, major FROM student_info;
# 分页
# 查询前两条
SELECT * FROM student_info LIMIT 2;
# 查询两条，从5+1开始
SELECT * FROM student_info LIMIT 5, 2;
# 排序
SELECT student_info.number FROM student_info ORDER BY student_info.number ASC;
SELECT * FROM student_info ORDER BY student_info.number ASC;
SELECT * FROM student_info ORDER BY student_info.number DESC;
# 多列排序
SELECT * FROM student_score ORDER BY subject, score DESC;

# 简单查询
# 查id等于20210101的学生的所有成绩
SELECT * FROM student_score WHERE number = 20210101;
# 查看除了这个学生的其他的学生的成绩
SELECT * FROM student_score WHERE number != 20210101;
SELECT * FROM student_score WHERE number <> 20210101;
-- 查成绩及格
SELECT * FROM student_score WHERE score >= 60;
# 查成绩不及格
SELECT * FROM student_score WHERE score < 60;
# 查成绩在60到80之间的成绩
SELECT * FROM student_score WHERE score BETWEEN 60 AND 80;
# 查成绩不在60到80之间的成绩
SELECT * FROM student_score WHERE score NOT BETWEEN 60 AND 80;

-- 匹配查询
SELECT * FROM student_info WHERE major IN ('软件工程', '飞行器设计');
SELECT * FROM student_info WHERE major NOT IN ('软件工程', '飞行器设计');

-- NULL
SELECT * FROM student_info WHERE name IS NULL;
SELECT * FROM student_info WHERE name IS NOT NULL;

-- 多条件查询
-- AND
SELECT * FROM student_info WHERE sex = '男' AND department = '计算机学院';
-- OR
SELECT * FROM student_info WHERE sex = '男' OR department = '计算机学院';
-- 复杂条件搜索
-- sorce < 60 + subject = 'MySQL是怎样运行的'
-- source > 90
SELECT * FROM student_score WHERE score > 90 OR score < 60 AND student_score.subject = 'MySQL是怎样运行的';
-- source > 90 + sorce < 60
-- subject = 'MySQL是怎样运行的'
SELECT * FROM student_score WHERE (score > 90 OR score < 60) AND student_score.subject = 'MySQL是怎样运行的';

-- 通配符
SELECT *  FROM student_info WHERE name LIKE '狗%';
SELECT *  FROM student_info WHERE name LIKE '%甘%';
SELECT *  FROM student_info WHERE name LIKE '狗__';

-- 转义通配符
SELECT *  FROM student_info WHERE name LIKE '狗\__';
SELECT *  FROM student_info WHERE name LIKE '狗\%_';


-- 表达式
-- 放在SELECT中
SELECT number,  subject, score + 100 FROM student_score;
SELECT number,  subject, score + 100 AS `score` FROM student_score;
-- ``用来装列名,''用来装普通字符串
SELECT name, '我是一个装饰', `sex`, 'sex' FROM student_info;
-- 不能用``将不是列明的内容装起来
SELECT name, `我是一个装饰` FROM student_info;
-- 计算器
SELECT 5 * 6 +128 -32;

-- 放在WHERE中
-- 假 查不出来数据
SELECT * FROM student_info WHERE 0;
SELECT * FROM student_info WHERE NULL;
SELECT * FROM student_info WHERE 2 - 2;
SELECT * FROM student_info WHERE 2 < 1;
-- 真 查出所有数据
SELECT * FROM student_info WHERE 2 > 1;
SELECT * FROM student_info WHERE 2 + 2;
SELECT * FROM student_info WHERE 2;

-- 表达式中的NULL
SELECT 1>NULL, 2-NULL;
SELECT NULL IS NULL;

-- 字符处理函数
-- LEFT
SELECT LEFT('abc123', 4); -- abc1
-- RIGHT
SELECT RIGHT('abc123',2); -- 23
-- LENGTH
SELECT LENGTH('何先生'); -- 9
-- LOWER
SELECT LOWER('ABCDEF'); -- abcdef
-- UPPER
SELECT UPPER('abcdef'); -- ABCDEF
-- LTRIM
SELECT LTRIM('   ABC'); -- 'ABC'
SELECT '   ABC'; -- '   ABC'
-- RTRIM
SELECT RTRIM('   ABC   '); -- '   ABC'
-- TRIM
SELECT TRIM('   A B C   '); -- 'A B C'
-- SUBSTRING
SELECT SUBSTRING('何先生abcdefg何先生', 2, 5); -- '先生abc'
-- CONCAT
SELECT CONCAT('a_', 'b', 'CD21', 'AA'); -- 'a_bCD21AA'
-- CHAR_LENGTH
SELECT CHAR_LENGTH('何先生abcdefg何先生'); -- 13

-- 和数据库结合使用
SELECT CONCAT('学号为', number, '的同学在《', subject, '》课程上的成绩是：', score) AS '成绩描述' FROM student_score;

-- 时间格式化
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i|%s 星期%w');
-- 2024年03月30日 06:07|31 星期6

-- 流程控制
SELECT
    *,
    CASE
        WHEN score < 60 THEN '不及格'
        WHEN score < 90 THEN '及格'
        ELSE '优秀'
        END AS '评价'
FROM
    student_score;
SELECT
    name, department, major,
    CASE department
        WHEN '计算机学院' THEN '一级学院'
        WHEN '航天学院' THEN '二级学院'
        ELSE '未设定'
        END AS '学院类别'
FROM
    student_info;

# 聚合函数、汇总函数
SELECT COUNT(number) AS '人数', MAX(student_score.score) AS '最高分', MIN(student_score.score) AS '最低分', AVG(student_score.score) AS '平均分', SUM(student_score.score) AS '总分' FROM student_score;


-- 分组查询
SELECT subject, COUNT(subject) AS '考试人数' FROM student_score GROUP BY subject;

-- 带有WHERE子句的分组查询
SELECT subject, COUNT(subject) AS '及格人数' FROM student_score WHERE score > 60 GROUP BY subject;

-- 作用于分组的过滤条件
SELECT subject, AVG(score) FROM student_score WHERE score > 60 GROUP BY subject HAVING MAX(score) > 90 AND AVG(score) > 80;
SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING MAX(score) > 90 AND AVG(score) > 70 AND subject != 'MySQL是怎样运行的';
SELECT subject, AVG(score) FROM student_score WHERE subject != 'MySQL是怎样运行的' GROUP BY subject HAVING MAX(score) > 90 AND AVG(score) > 70;

-- 分组排序
SELECT student_score.subject, AVG(student_score.score) FROM student_score GROUP BY student_score.subject ORDER BY AVG(score) DESC;

-- 多个分组列
SELECT department, major, COUNT(number) AS '人数' FROM student_info GROUP BY department, major;

-- 分组采用表达式
SELECT student_score.subject, COUNT(student_score.number) AS 人数 FROM student_score GROUP BY CONCAT('学科:', subject)



-- 普通连接  和内连接是等价的
SELECT * FROM student_info a, student_score b WHERE a.number = b.number;
-- 左外连接，以a表为基准查出所有
SELECT * FROM student_info a LEFT OUTER JOIN student_score b ON a.number = b.number;
-- 右外连接
SELECT * FROM student_info a RIGHT OUTER JOIN student_score b ON a.number = b.number;


-- 自连接，查看和狗哥专业相同的人员信息
SELECT * FROm student_info a JOIN student_info b ON a.major = b.major WHERE a.name = '狗哥';


-- 单表组合查询

SELECT name, student_info.department, student_info.major FROM student_info WHERE sex = '男' OR number > 20210101;
SELECT name, student_info.department, student_info.major FROM student_info WHERE sex = '男' UNION SELECT student_info.department, name, student_info.major FROM student_info WHERE number > 20210101;


-- 14章插入建表

CREATE table first_table(
    first_column INT,
    second_column VARCHAR(100)
);

-- 插入数据
-- 不写列明 默认按照建表的顺序 （每列必须明确写出值，可以用NULL替代）
INSERT INTO first_table VALUE (1, '哈哈');
-- 指定列名
INSERT INTO first_table(first_column, second_column) VALUE (2, '嘿嘿');
-- 可以调换列顺序
INSERT INTO first_table(second_column, first_column) VALUE ('嘿嘿嘿', 3);


-- 批量插入数据 VALUE => VALUES （VALUES也可以只插入一条数据）
INSERT INTO
    first_table(second_column, first_column)
VALUE
    ('嘿哈', 5),
    ('嗯哼', 6),
    ('唉嘿', 7),
    ('嘻嘻', 8),
    ('呜呜呜', 9);

-- 插入其他表的数据

INSERT INTO first_table SELECT * FROM first_table LIMIT 4;
INSERT INTO first_table(first_column, second_column)  SELECT first_column, second_column FROM first_table WHERE first_column = 3;

-- 删除记录
DELETE FROM first_table;

-- 创建存储函数
-- 设置分隔符
SET global log_bin_trust_function_creators = TRUE;
DELIMITER $
CREATE FUNCTION get_score_avg(subject_name VARCHAR(10))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(student_score.score) FROM student_score WHERE subject = subject_name);
END $
DELIMITER ;


SHOW FUNCTION STATUS ;