数据库的一些基本语法一

-- 创建作业数据库
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运行在分组前，所以不能使用聚合函数