-- 创建作业数据库
CREATE DATABASE homework;
-- 创建学生表
CREATE TABLE t_student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(10),
s_sex VARCHAR(5),
s_birthday DATE,
s_grade INT,
s_class VARCHAR(10)
);
INSERT INTO t_student (s_name,s_sex,s_birthday,s_grade,s_class)
VALUES ('张无忌','男','1988-01-05',99,'T120'),
('赵敏','女','1988-09-05',79,'T121'),
('刘基','男','1988-04-05',89,'T120'),
('李小璐','女','1978-01-05',59,'T120'),
('王建国','男','1988-05-15',39,'T120'),
('唐伯虎','男','1988-10-25',69,'T120'),
('黎波','女','1990-12-14',91,'T120'),
('张伯伦','男','1984-02-13',24,'T120'),
('蔡国庆','男','1982-11-15',88,'T120'),
('吉拉','女','1978-05-23',35,'T121'),
('赵晓','女','1988-04-23',55,'T121'),
('李连杰','男','1968-03-13',77,'T121'),
('王国平','男','1972-02-03',88,'T121'),
('王建','男','1989-01-16',92,'T121');
-- 1.把刘基的名字修改为刘伯温
UPDATE t_student SET s_name='刘伯温' WHERE s_name='刘基';
-- 2.删除姓名为唐伯虎的学生
DELETE FROM t_student WHERE s_name='唐伯虎';
-- 3.查询出所有姓名包括'伯'的所有的人员的信息
SELECT * FROM t_student WHERE s_name LIKE '%伯%';
-- 4,查询T120班所有的女生。
SELECT * FROM t_student WHERE s_sex='女' AND s_class='T120';
-- 5,查询前5条80后的学生的信息
SELECT * FROM t_student WHERE s_birthday>='1980-01-01' LIMIT 0,5;
-- 6,查询T120班,所有不及格的学生
SELECT * FROM t_student WHERE s_grade<60 AND s_class='T120';
-- 7、查询所有的班级
SELECT DISTINCT s_class FROM t_student;
-- 8、查询所有缺考学生
SELECT * FROM t_student WHERE s_grade IS NULL;
-- 9、将T121班的学生,按成绩降序排列显示
SELECT * FROM t_student WHERE s_class='T121' ORDER BY s_grade DESC;
-- 10、查询所有成绩在80分以上的90后学生。
SELECT * FROM t_student WHERE s_grade>80 AND s_birthday>='1990-01-01';
-- 给指定条件的学生全部加分
UPDATE t_student SET s_grade=s_grade+5 WHERE s_class='T121';
-- 显示所有学生的成绩并且追加评价
-- ts 表示这个表的别名 ,case when 表示赛选条件 类似于 java switch 语句
-- 别名是来区分多个表,例如我要查询一个表中的价格,和另外一个表中的编号
SELECT ts.*,(CASE WHEN s_grade>=90 THEN '优秀'
WHEN s_grade>=80 AND s_grade <90 THEN '良好'
WHEN s_grade>=60 AND s_grade<80 THEN '一般'
ELSE '很差'
END
) '评价',(CASE WHEN s_birthday >='1970-01-01' AND s_birthday <'1980-01-01' THEN '七0后'
WHEN s_birthday>='1980-01-01' AND s_birthday <'1990-01-01' THEN '80后'
WHEN s_birthday>='1990-01-01' AND s_birthday<'2000-01-01' THEN '90后'
ELSE '其他年代'
END
)'年代' FROM t_student ts;
-- 给列写别名
SELECT s_id '编号',s_name '学生名字' FROM t_student;
SELECT ts.*,(CASE WHEN s_birthday >='1970-01-01' AND s_birthday <'1980-01-01' THEN '70后'
WHEN s_birthday>='1980-01-01' AND s_birthday <'1990-01-01' THEN '80后'
WHEN s_birthday>='1990-01-01' AND s_birthday<'2000-01-01' THEN '90后'
ELSE '其他年代'
END
)AS'年代' FROM t_student AS ts;
INSERT INTO t_student (s_name,s_sex,s_birthday,s_class)
VALUES ('张小花','女','1986-02-12','T120');
-- 给T120 加2分 给T121 减去2分 其他不变
UPDATE t_student SET s_grade=(CASE WHEN s_class='T120' THEN s_grade+2
WHEN s_class='T121' THEN s_grade-2
ELSE s_grade
END
);
-- count() 颜色粉色,里面是*表示所有行,里面有具体的列名,表示统计该列所有非null的行数
SELECT COUNT(*),COUNT(s_grade) FROM t_student ;
-- sum() 统计某一列总和
SELECT SUM(s_grade) FROM t_student;
-- avg() 求某一列的 平均分( avg 不会把值为null 的加入计算中)
-- 如果要计算为null值的元素 sum(s_grade)/count(*)
SELECT AVG(s_grade)AS '参考人员平均分',SUM(s_grade)/COUNT(*) AS '总体平均分' FROM t_student;
-- 计算最高分和最低分 max() min()
-- 需要注意的是,函数和后面的括号之间不要有空格
SELECT MAX(s_grade)AS'最高分',MIN(s_grade)AS '最低分' FROM t_student;
-- 统计属于某个组的所有人
SELECT s_class,COUNT(*) FROM t_student GROUP BY s_class;
-- 统计每个班的最高分和最低分
SELECT s_class,MAX(s_grade),MIN(s_grade) FROM t_student GROUP BY s_class;
-- 统计某个范围内的总人数, 在then后面必须要加一个东西,1 '',都行
SELECT s_class,COUNT(CASE WHEN s_grade>=90 THEN '' END)AS '统计90以上',
COUNT(CASE WHEN s_grade>=80 AND s_grade<90 THEN '' END)AS '统计80-90',
COUNT(CASE WHEN s_grade IS NULL THEN '' END)AS '缺考' FROM t_student GROUP BY s_class;
-- 找出人数大于8个人的班级 ,having 和 where 都是过滤条件,但是 where 在group by 前执行
-- having 在group by 后执行
SELECT s_class FROM t_student GROUP BY s_class HAVING COUNT(*)>=8;
-- SQL 执行顺序, from-->where-->group by--> select -->having -->order by
-- 因为执行属性 可以直接用别名来使用 下列的 count(*) c,c是别名,因为select 在having前执行 所以可以 c>=8
SELECT s_class,COUNT(*) c FROM t_student GROUP BY s_class HAVING c>=8;
-- 在分组查询中,只能是分组字段和聚合函数 count sum,avg max min ,因为where运行在分组前,所以不能使用聚合函数
数据库的一些基本语法一
最新推荐文章于 2021-03-29 15:35:29 发布