SELECT * FROM `hello-mysql`.student;
# 插入数据
INSERT INTO `hello-mysql`.`student` (`name`, `age`, `sex`, `email`, `create_time`) VALUES ('bbb', '23', '1', 'bbb@qq.com', '2023-05-27 10:50:00');
INSERT INTO `hello-mysql`.`student` (`name`, `age`, `sex`, `email`, `create_time`) VALUES ('ccc', '21', '0', 'ccc@qq.com', '2023-05-26 10:50:00');
INSERT INTO `hello-mysql`.`student` (`name`, `age`, `sex`, `email`, `create_time`) VALUES ('ddd', '22', '1', 'ddd@qq.com', '2023-05-28 10:50:00');
# 删除表
drop table student;
# 创建表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
name VARCHAR(50) NOT NULL COMMENT '学生名',
gender VARCHAR(10) NOT NULL COMMENT '性别',
age INT NOT NULL COMMENT '年龄',
class VARCHAR(50) NOT NULL COMMENT '班级名',
score INT NOT NULL COMMENT '分数'
) CHARSET=utf8mb4
SELECT * FROM student;
INSERT INTO student (name, gender, age, class, score)
VALUES
('张三', '男',18, '一班',90),
('李四', '女',19, '二班',85),
('王五', '男',20, '三班',70),
('赵六', '女',18, '一班',95),
('钱七', '男',19, '二班',80),
('孙八', '女',20, '三班',75),
('周九', '男',18, '一班',85),
('吴十', '女',19, '二班',90),
('郑十一', '男',20, '三班',60),
('王十二', '女',18, '一班',95),
('赵十三', '男',19, '二班',75),
('钱十四', '女',20, '三班',80),
('孙十五', '男',18, '一班',90),
('周十六', '女',19, '二班',85),
('吴十七', '男',20, '三班',70),
('郑十八', '女',18, '一班',95),
('王十九', '男',19, '二班',80),
('赵二十', '女',20, '三班',75);
SELECT name, score FROM student;
# 别名和组合查询
SELECT name as '名字', score as '分数' FROM student;
SELECT name as '名字', score as '分数' FROM student WHERE age >= 19;
SELECT name as '名字', score as '分数' FROM student WHERE age >= 19 AND score >= 90;
# 模糊查询 LIKE
SELECT * FROM student WHERE name LIKE '王%';
# 集合查询 IN
SELECT * FROM student WHERE class IN ('一班', '二班');
SELECT * FROM student WHERE class NOT IN ('一班', '二班');
# 区间 BETWEEN AND
SELECT * FROM student WHERE age BETWEEN 19 AND 20;
# 分页 LIMIT
SELECT * FROM student LIMIT 0,5;
# 简化
SELECT * FROM student LIMIT 5;
SELECT * FROM student LIMIT 5,5;
# 排序 根据score升序，score相同再根据age降序
SELECT * FROM student ORDER BY score asc, age desc;
# 分组
SELECT class AS '班级', AVG(score) AS '平均成绩'FROM student GROUP BY class ORDER BY '平均成绩' DESC;
# 计数
SELECT class, COUNT(*) AS count FROM student GROUP BY class;
# 分组后进一步过滤 HAVING
select class, AVG(score) AS avg_score FROM student GROUP BY class HAVING avg_score > 90;
# 去重 DISTINCT
SELECT DISTINCT class FROM student;
# 聚合函数
select avg(score) as '平均成绩',count(*) as '人数',sum(score) as '总成绩',min(score) as '最低分', max(score) as '最高分' from student;
# 字符串函数
SELECT CONCAT('xx', name, 'yy'), SUBSTR(name,2,3), LENGTH(name), UPPER('aa'), LOWER('TT') FROM student;
# 数值函数
SELECT ROUND(1.234567, 2), CEIL(1.234567), FLOOR(1.234567), ABS(-1.234567), MOD(5, 2);
# 日期函数
SELECT YEAR('2023-06-01 22:06:03'), MONTH('2023-06-01 22:06:03'),DAY('2023-06-01 22:06:03'),DATE('2023-06-01 22:06:03'), TIME('2023-06-01 22:06:03');
# 条件函数
select name, if(score >=60, '及格', '不及格') from student;
SELECT name, score, CASE WHEN score >=90 THEN '优秀' WHEN score >=60 THEN '良好'ELSE '差' END AS '档次' FROM student;
# 系统函数
select VERSION(), DATABASE(), USER();
# 其他函数
# 如果相等返回 null，不相等返回第一个值
select NULLIF(1,1), NULLIF(1,2);
# 返回第一个非 null 的值
select COALESCE(null, 1), COALESCE(null, null, 2);
# 返回几个值中最大最小的
select GREATEST(1,2,3),LEAST(1,2,3,4);
# 类型转换函数
select greatest(1, convert('123', signed),3);
select greatest(1, cast('123' as signed),3);
SELECT DATE_FORMAT('2022-01-01', '%Y年%m月%d日');
