【无标题】

CREATE DATABASE ee_2204_4_28
`ee_2204_4_28`
CREATE TABLE student
(id INT,-- 编号
NAME VARCHAR(20),-- 姓名
chinese INT,-- 语文成绩
english INT,-- 英语成绩
math INT-- 数学成绩 
);
DELETE  FROM student;
INSERT INTO student
VALUES
(1,'行哥',89,78,90),
(2,'潘金莲',67,53,95),
(3,'凤姐',87,78,77),
(4,'旺财',88,98,92),
(5,'白小黑',82,84,67),
(6,'李白',55,85,45),
(7,'范蹦蹦',75,65,30);
SELECT * FROM student;

-- 查询表中所有学生的信息
SELECT 
	*
FROM
	student
	
-- 查询表中所有学生的姓名和英语成绩
SELECT
	NAME,
	english
FROM
	student;
	
-- 过滤表中重复数据
ALTER TABLE student DROP city
 ;
 SELECT	
	DISTINCT english
FROM
	student;
	
-- 统计每个学生的总分
SELECT id,
NAME,
chinese,
english,
math,
(chinese+english+math)'总分'
FROM
student;

--  在所有学生总分数上加10分特长分

SELECT 
id,
NAME,
chinese,
english,
math,
(chinese+english+math+10) '总分+特长分'
FROM
student;

-- 使用别名表示学生分数
SELECT
	S.id,
	S.name,
	S.chinese,
	S.english,
	S.math
FROM
	Student S


-- 查询姓名为行哥的学生成绩
SELECT 
	*
FROM
	student
WHERE
	NAME='行哥';
	
-- 查询英语成绩大于90分的同学
SELECT 
	*
FROM
	student
WHERE
	english>90;
	
-- 查询总分大于200分的所有同学
 SELECT
	id,
	NAME,
	chinese,
	english,
	math,
	(chinese+english+math)'总分'
FROM
	student
WHERE
	(chinese+english+math)>200;
	
--  查询英语分数在80-90之间的同学
SELECT
	*
FROM
	student
WHERE
	english>80 AND english<90;
	
-- 查询数学分数为89 90 91的同学
SELECT
	*
FROM
	student
WHERE
	math=89 OR math=90 OR math=91;
	

--  查询所有姓白的学生英语成绩
SELECT
	english
FROM student
WHERE 
	NAME LIKE'%白%';

-- 查询数学分>80并且语文分＞80的同学
SELECT 
	*
FROM
	student
WHERE
	math>80 AND chinese>80;
	
-- 查询英语>80或者总分>200的同学
SELECT
	id,
	NAME,
	math,
	english,
	chinese,
	(math+english+chinese)'总分'
FROM
	student
WHERE 
	english>80 OR (math+english+chinese)>200;
	
-- 对数学成绩升序排序
SELECT
	*
FROM
	student
ORDER BY
	math ASC;
	
-- 16.对总分降序排序，然后再按数学从高到低的顺序输出
SELECT
	id,
	NAME,
	math,
	english,
	chinese,
	(math+english+chinese)'总分'
FROM
	student
ORDER BY
	(math+english+chinese) DESC,math ASC;