0428作业＋代码＋知识点总结-CSDN博客

本文链接：https://blog.csdn.net/lrtdfgd/article/details/124482270

任务

1.单表练习题

create table student(
   id int, -- id编号
   name varchar(20), -- 姓名
   chinese int, -- 语文
   english int, -- 英语
   math int -- 数学
);

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.查询表中所有学生的信息。

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

3 过滤表中重复数据。(去重的关键字 distinct)

4.统计每个学生的总分

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

6.使用别名表示学生分数。

7.查询姓名为行哥的学生成绩

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

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

10.查询英语分数在 80－90之间的同学。

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

12.查询所有姓白的学生英语成绩。

13.查询数学分>80并且语文分>80的同学

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

15.对数学成绩升序排序

16.对总分降序排序，然后再按数学从高到低的顺序输出

17.对姓白的学生成绩排序输出(排序规则自己定义)

18.查询数学成绩大于等于平均分的所有学生信息

19.查询出英语成绩最高分的学生信息

20.模糊查询出学生姓名中第二个字是金的学生信息

21.查询出英语成绩平均分是多少

22.查询出数学总分是多少

23.查询学生表中有多少条记录

USE ee200402;
CREATE TABLE student(
	id INT,   -- id编号
	NAME VARCHAR(20), -- 姓名
	chinese INT,  -- 语文
	english INT, -- 英语
	math INT -- 数学
);
SELECT * FROM student;

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);

SELECT * FROM student;

SELECT 
NAME,
english
 FROM student;
 
 SELECT DISTINCT
 chinese,
 english
 FROM student;
 
 SELECT
 NAME,
(chinese+english+math)
AS total
 FROM student;
 
  SELECT
 NAME,
 (chinese+english+math)
AS "总分",
(chinese+english+math+10)
AS "加了特长分"
 FROM student;

SELECT
student.`id` ,
student.`name` ,
(chinese+english+math)
AS "总分"
FROM student
WHERE id = '1';


SELECT
 NAME,
 
 (chinese+math+english) AS '总分'
 
 
FROM
 st
WHERE (SELECT (chinese+math+english) AS '总分') > 200;

-- 英语大于90 或者总分大于200
SELECT
 NAME,
 
 (chinese+math+english) AS '总分',
 english
 
FROM
 student
WHERE ((SELECT (chinese+math+english) AS '总分') > 200) || english > 90;

-- 数学大于80并且语文大于80

SELECT
*
FROM
student
WHERE english>80 && chinese >80;


SELECT
*
FROM
student
WHERE english>90;

-- 数学 升序
SELECT 
    *
FROM 
    student
ORDER BY 
math ;

SELECT
*
FROM 
student
WHERE english>=80 && english<=90;

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

SELECT
*
FROM
student
WHERE NAME LIKE "%白%";

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

SELECT
*
FROM
student
ORDER BY
math ASC;



-- 16.对总分降序排序，然后再按数学从高到低的顺序输出

SELECT
student.`name` ,
student.`math` ,

(chinese+english+math)
AS "总分"
FROM student
ORDER BY 总分 DESC,math ASC;

-- 17.对姓白的学生成绩排序输出(排序规则自己定义)

SELECT
student.`name` ,
student.`math` ,
student.`chinese` ,
student.`english` ,
(chinese+english+math)
AS "总分"
FROM student
WHERE NAME LIKE "%白%"
ORDER BY 总分 DESC,math ASC;

-- 18.查询数学成绩大于等于平均分的所有学生信息

SELECT
student.`name` ,
student.`math`,
AVG(MATH) AS "av"
FROM
student
WHERE math > 'av';

-- 19.查询出英语成绩最高分的学生信息
SELECT
student.`name` ,
MAX(english)
FROM
student;
-- 20.模糊查询出学生姓名中第二个字是金的学生信息

select
*
from
student
where
name like '%_金%';
-- 21.查询出英语成绩平均分是多少
SELECT
avg(english)
AS "英语平均分"
FROM student;
-- 22.查询出数学总分是多少

SELECT
sum(math)
AS "数学总分"
FROM student;

-- 23.查询学生表中有多少条记录
	select
	count(id) as '总记录数'
	from
	student;