mysql练习

练习

创建表的操作

CREATE TABLE student(
    id INT,
    name VARCHAR(20),
    chinese FLOAT,
    english FLOAT,
    math FlOAT
);

插入数据

INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);

完成下面的功能

1.查询表中所有学生的信息

SELECT * FROM student;

2.查询表中所有学生的姓名和对应的英语成绩。

SELECT name,english FROM student;

3.统计每个学生的总分

SELECT name,(chinese+math+english) 总分 FROM student; 

4.在所有学生总分数上加10分特长分。

-- 重新计算总分数
SELECT name, (chinese+math+english+10) as 总分数 FROM student order by 总分数;

5.使用别名表示学生分数

SELECT name,english 英语, math 数学,chinese 语文 FROM student;

6.查询姓名为李一的学生成绩

SELECT * FROM student WHERE name = '李一';

7.查询英语成绩大于90分的同学

SELECT * FROM student where english > 90;

8.查询总分大于200分的所有同学

SELECT name,(chinese+math+english)as sumScore FROM student WHERE (chinese+math+english)>200;

9.查询英语分数在80-90分的同学

SELECT name,english FROM student WHERE english between 80 and 90;

10.查询数学分数为89,90,91的同学

SELECT name,math FROM student WHERE math IN(89,90,91);

11.查询所有姓李的学生英语成绩

SELECT name,english FROM student where name like '李%';

12.查询数学分>80并且语文分>80以上的同学

SELECT * FROM student WHERE math >80 AND chinese >80;

13.查询英语>80或者总分>200的同学

SELECT * FROM student WHERE english >80 OR (chinese+math+english)>200;

14.查询所有学生的成绩，按照数学成绩降序输出

SELECT * FROM student ORDER BY math DESC;

15.对每个同学的总分排序后输出，然后再按照从高到底的顺序输出

SELECT name,(chinese+math+english) as sumScore FROM student ORDER BY (chinese+math+english)  DESC;

16.对姓李的学生总成绩升序输出

SELECT name,(chinese+math+english) as 总成绩 FROM student where name like '李%' order by (chinese+math+english) asc;