数据库的创建和查询

-- 创建数据库
CREATE DATABASE student;

-- 创建学生列表
CREATE TABLE s_student(
 id INT PRIMARY KEY AUTO_INCREMENT,
 studentName VARCHAR(10),
 sex VARCHAR(10),
 birthday DATE,
 grade INT,
 class VARCHAR(10)
)DEFAULT CHARSET = utf8;

-- 添加人员
INSERT INTO s_student(studentName,sex,birthday,grade,class) VALUES
('刘基','男','1992-02-09',80,'T120'),
('唐伯虎','男','1991-04-19',90,'T120'),
('刘川','男','1989-12-09',78,'T120'),
('朱涣','男','1995-08-23',85,'T121'),
('朱琴','女','1993-11-11',79,'T121'),
('叶欢','女','1993-09-02',92,'T120'),
('陈雪','女','1991-02-09',59,'T120'),
('马超','男','1994-03-29',77,'T121'),
('朱旭','男','1994-11-03',89,'T120');

INSERT INTO s_student(studentName,sex,birthday,class) VALUES
('陈圆圆','女','1992-08-11','T120'),
('刘诗诗','女','1992-03-23','T120');

 

-- 1.把刘基的名字修改为刘伯温
UPDATE s_student SET studentName='刘伯温' WHERE studentName='刘基';

-- 2.删除姓名为唐伯虎的学生
DELETE FROM s_student WHERE studentName='唐伯虎';

-- 3.查询出所有姓名包括'伯'的所有的人员的信息
SELECT * FROM s_student WHERE studentName LIKE '%伯%';

-- 4，查询T120班所有的女生。
SELECT * FROM s_student WHERE sex='女' AND class='T120';

-- 5，查询前5条80后的学生的信息
SELECT * FROM s_student WHERE birthday >= '1980-01-01' AND birthday < '1990-01-01' LIMIT 0,5;

-- 6,查询T120班,所有不及格的学生
SELECT * FROM s_student WHERE class='T120' AND grade < 60;

-- 7、查询所有的班级

SELECT DISTINCT class FROM s_student;

-- 8、查询所有缺考学生

SELECT * FROM s_student WHERE grade IS NULL;

-- 9、将T121班的学生，按成绩降序排列显示

SELECT * FROM s_student WHERE class='T121' ORDER BY grade DESC;

-- 10、查询所有成绩在80分以上的90后学生

SELECT * FROM s_student WHERE birthday >= '1990-01-01' AND birthday < '2000-01-01' AND grade >= 80;

-- 所有人的成绩加五分
UPDATE s_student SET grade = grade + 5 WHERE class = 'T120';

-- 显示所有学生成绩，并且追加评价
SELECT b.*,(CASE WHEN grade >= 90 THEN '优'
  WHEN grade >= 80 AND grade <90 THEN '良'
  WHEN grade >=60 AND grade <80 THEN '中'
  WHEN grade< 60 AND grade >  0 THEN '差'
  ELSE '缺考'
  END
) '评价' , -- g代表的是给表取名  g.*表示显示表中所有的列

-- 显示所有学生的年纪，并且追加年龄显示
(CASE WHEN birthday >= '1990-01-01' AND birthday < '1992-01-01' THEN '大'
  WHEN birthday >= '1992-01-01' AND birthday < '1994-01-01' THEN '中'
  WHEN birthday >= '1994-01-01' AND birthday < '1996-01-01' THEN '小'
  ELSE '太小'
  END

)'年龄'FROM s_student b;

-- T120每个人加2分，T121每个人加1分

UPDATE s_student SET grade=(CASE WHEN class='T120' THEN grade+2
    WHEN class = 'T121' THEN grade+1
    ELSE grade
    END

);

-- count（*） 表示统计所有的记录，count（grade） 表示统计非空记录
SELECT COUNT(*) '学生总人数',COUNT(grade) '参考人数' FROM s_student;

-- 统计所有学生的总分
SELECT SUM(grade) '学生总分' FROM s_student;

-- 统计所有学生的平均分,avg只是计算非空列
SELECT AVG(grade),SUM(grade)/COUNT(*)'学生平均分' FROM s_student;

-- 统计最高分和最低分
SELECT MAX(grade) '最高分',MIN(grade) '最低分'FROM s_student;

-- 每个班有几个学生，group by 把一个班的学生统一为一个组
SELECT class '班级', COUNT(*) '学生人数' FROM s_student GROUP BY class;

-- 统计每个班的最高分和最低分
SELECT class '班级',MAX(grade),MIN(grade) FROM s_student GROUP BY class;

-- 统计每个班80分以上的人数，60-80的人数，不及格人数，和缺考人数
SELECT class ,COUNT(CASE WHEN grade >= 80 THEN 1 END) '80以上',
  
 COUNT(CASE WHEN grade >= 60 AND grade < 80 THEN 1 END) '60-80之间',
 
 COUNT(CASE WHEN grade < 60 THEN 2 END) '不及格',
 
 COUNT(CASE WHEN grade IS NULL THEN 2323 END ) '缺考'

 FROM s_student GROUP BY class;

-- 人数大于4的班级
SELECT class,COUNT(*) FROM s_student GROUP BY class HAVING COUNT(*)>=4;

-- 查询最高分的信息
SELECT * FROM s_student WHERE grade = (SELECT MAX(grade) FROM s_student WHERE grade);

-- 查询哪些人的成绩比陈雪好
SELECT * FROM s_student WHERE grade > (SELECT grade FROM s_student WHERE studentName = '陈雪');

-- 查询每个班的最好成绩信息,子条件返回多个值用 IN ,
SELECT * FROM s_student s1 WHERE grade IN (SELECT MAX(grade) FROM s_student s2 WHERE s1.class = s2.class GROUP BY class);

-- 查询所有记录
SELECT * FROM s_student;

DROP TABLE s_student;