MySQL之Select

-- 新建学校school表
CREATE TABLE `school` (
  `id` int(11) NOT NULL COMMENT '主键id',
  `school_name` varchar(255) DEFAULT NULL COMMENT '学校',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- school插入数据
INSERT INTO `sqlclass`.`school`(`id`, `school_name`) VALUES (1, '菜鸟大学');
INSERT INTO `sqlclass`.`school`(`id`, `school_name`) VALUES (2, '微软大学');
INSERT INTO `sqlclass`.`school`(`id`, `school_name`) VALUES (3, '阿里大学');
INSERT INTO `sqlclass`.`school`(`id`, `school_name`) VALUES (4, '腾讯大学');

-- 新建学生表
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '名字',
  `school` int(11) DEFAULT NULL COMMENT '学校',
  `score` int(11) DEFAULT NULL COMMENT '得分',
  `subject` varchar(255) DEFAULT NULL COMMENT '科目',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;
-- student插入数据
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (1, '张三', 1, 60, '语文');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (2, '张三', 1, 77, '数学');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (3, '张三', 1, 59, '英语');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (4, '李四', 2, 80, '语文');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (5, '李四', 2, 88, '数学');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (6, '李四', 2, 87, '英语');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (7, '王五', 3, 90, '语文');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (8, '王五', 3, 80, '数学');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (9, '王五', 3, 77, '英语');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (10, '赵六', 5, 95, '语文');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (11, '赵六', 5, 89, '数学');
INSERT INTO `sqlclass`.`student`(`id`, `name`, `school`, `score`, `subject`) VALUES (12, '赵六', 5, 90, '英语');

-- 简单查询所有数据
SELECT * FROM student;
SELECT * from school;

-- 选择字段并重命名
select name as 名字,score as 分数,SUBJECT as 科目 from student;

-- 条件查询
SELECT * FROM student where subject='语文';
SELECT * FROM student HAVING subject='语文';
SELECT * FROM student where subject like CONCAT('%','语','%');
SELECT * from student where id in (1,2,3);
SELECT * from student where id BETWEEN 1 and 3;

-- 排序
-- 升序
SELECT * from student order by score;
-- 降序
SELECT * from student order by score desc;

-- 去重
SELECT subject from student GROUP BY subject;
SELECT DISTINCT subject from student;

-- 查询前十条数据
SELECT * from student LIMIT 1,10;

-- 连接查询
-- 内连接
select * from student t1 
INNER JOIN school t2 on t1.school=t2.id;
-- 左连接
select * from student t1 
left JOIN school t2 on t1.school=t2.id;
-- 右连接
select * from student t1 
RIGHT JOIN school t2 on t1.school=t2.id;
-- 全连接
select * from student t1 
full JOIN school t2 on t1.school=t2.id;

-- 条件语句
SELECT 
name as 名字,
CASE subject
WHEN '语文' THEN '国学'
WHEN '数学' THEN '算术'
WHEN '英语' THEN '外语'
ELSE '无' END AS '科目',
score as 分数
 from student;
 
-- 常用函数
-- 计数
SELECT count(*) from student;
-- 取最大
SELECT MAX(score) from student;
-- 取最小
SELECT MIN(score) from student;
-- 取平均
SELECT avg(score) from student;
-- 求和
SELECT sum(score) from student;
-- 当前时间
select NOW();
-- 时间段YEAR()、MONTH()、DAY()
SELECT YEAR(NOW());

-- 要求查询所有科目成绩均大于80分的学生名字
select `name` from student GROUP BY name HAVING MIN(score)>80;

-- 要求查询结果列为：名字、学校、语文、数学、英语、总分
select 
t1.`name` 名字,
t2.school_name 学校,
sum(CASE subject
WHEN '语文' THEN score
ELSE 0 END) AS 语文,
sum(CASE subject
WHEN '数学' THEN score
ELSE 0 END) AS 数学,
sum(CASE subject
WHEN '英语' THEN score
ELSE 0 END) AS 英语,
SUM(score) as 总分
from student t1 
left JOIN school t2 on t1.school=t2.id
GROUP BY t1.`name`,t2.school_name;