CREATE TABLE GRADE (
ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
NAME VARCHAR (20) COMMENT '姓名',
AGE INT COMMENT '年龄',
CLASS INT COMMENT '班级',
CHINESE INT COMMENT '语文分数',
ENGLISH INT COMMENT '英语分数',
MATH INT COMMENT '数学分数'
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COMMENT '学生表';
INSERT INTO GRADE
VALUES
(
1,
'zhangsan',
18,
1832,
80,
90,
77
),
(2, 'lisi', 18, 1832, 90, 90, 90),
(
3,
'wangwu',
19,
1833,
99,
98,
100
),
(
4,
'zhaoliu',
17,
1833,
85,
78,
85
),
(
5,
'xiaoqi',
17,
1832,
60,
80,
88
),
(
6,
'xiaoba',
18,
1833,
70,
82,
90
);
SELECT
*
FROM
GRADE;
1,查询1832班的成绩信息 SELECT
*
FROM
GRADE
WHERE
CLASS = 1832;
2,查询1833班,语文成绩大于80小于90的所有成绩信息 SELECT
*
FROM
GRADE
WHERE
CHINESE BETWEEN 80
AND 90
AND CLASS = 1833;
3,查询学生表中5到10行的数据 SELECT
*
FROM
GRADE
WHERE
ID
LIMIT 4,
6;
4,显示1832班英语成绩为98,数学成绩为77的姓名与学号,
SELECT
ID,
NAME,
CLASS
FROM
GRADE
WHERE
CLASS = 1832
AND ENGLISH = 98
AND MATH = 77;
5,查询出1832班成绩并且按语文成绩排序(降序) SELECT
*
FROM
GRADE
WHERE
CLASS = 1832
ORDER BY
CHINESE DESC;
6,查询1833班与1832班,语文成绩与数学成绩都小于80的姓名。 SELECT
NAME
FROM
GRADE
WHERE
CHINESE < 80
AND MATH < 80;
7,查询出没有参加语文考试的学生姓名和班级名称。 SELECT
NAME,
CLASS
FROM
GRADE
WHERE
CHINESE IS NULL;
8,求出班上语文成绩不及格的学生姓名 SELECT
NAME
FROM
GRADE
WHERE
CHINESE < 60;
9,求出每个班的数学平均成绩 SELECT
AVG(MATH),
CLASS
FROM
GRADE
GROUP BY
CLASS;
10、求出每个班级语文成绩总分 -- 涉及到每个的时候都需要分组
SELECT
SUM(CHINESE),
CLASS
FROM
GRADE
GROUP BY
CLASS;
11、将语文成绩不及格的学生语文成绩改为60分 UPDATE GRADE
SET CHINESE = 60
WHERE
CHINESE < 60;
12、三科分数都大于70分的人名和年纪 SELECT
NAME,
AGE
FROM
GRADE
WHERE
CHINESE > 70
AND ENGLISH > 70
AND MATH > 70;
13、求出英语分数高于70且其它任何一科目大于60分的人和班级 SELECT
NAME,
CLASS
FROM
GRADE
WHERE
ENGLISH > 70
AND CHINESE > 60
OR MATH > 60;
14、统计每个班的人数 SELECT
CLASS,
COUNT(ID)
FROM
GRADE
GROUP BY
CLASS;
15、求每个班数学成绩大于80的人数 SELECT
CLASS,
COUNT(ID)
FROM
GRADE
WHERE
MATH > 80
GROUP BY
CLASS;
16、求出每个班英语成绩最高的分数和班级名称 SELECT
CLASS,
NAME,
MAX(ENGLISH)
FROM
GRADE
GROUP BY
CLASS;
17、给GRADE表增加3个字段(数据类型及长度自定义,建议要合理, INSERT INTO GRADE
VALUES
(
7,
"XIAOHONG",
19,
1833,
80,
90,
60
),
(
8,
"XIAOJIU",
17,
1832,
70,
50,
66
),
(
9,
"XIAOHEI",
18,
1832,
90,
80,
50
);
|
|