数据库作业三

CREATE TABLE grade( id INT NOT NULL, sex CHAR(1), firstname VARCHAR(20) NOT NULL, lastname VARCHAR(20) NOT NULL, english FLOAT, math FLOAT, chinese FLOAT );

#创建数据表grade

向数据表grade中插入几条数据：

INSERT INTO grade VALUES (1,'m','John','Smith',88.0,85.0,82.0), (2,'f','Adam','Smith',76.0,78.0,90.0), (3,'m','Allen','William',88.0,92.0,95.0), (4,'m','George','William',62.0,58.0,72.0), (5,'f','Alice','Davis',89.0,94.0,98.0), (6,'m','Kevin','Miller',77.0,88.0,99.0), (7,'f','Helen','Davis',79.0,83.0,91.0), (8,'m','Andrew','Johnson',81.0,86.0,88.0);

SELECT * FROM grade;

#查询所有字段

SELECT id,firstname,lastname FROM grade;
#查询grade表中的id,firstname,lastname字段

SELECT firstname FROM grade WHERE id >4;
#查询grade表中id大于4的学生姓名

SELECT * FROM grade WHERE sex = 'f';
#查询grade表中女生的记录

SELECT * FROM grade WHERE id = 2 OR id = 4 OR id = 6;
#查询grade表中id值为2,4,6的学生记录

SELECT * FROM grade WHERE math > 85 AND math <94;
#查询grade表中math成绩在85到94之间的记录

SELECT * FROM grade WHERE firstname LIKE 'a%';
#查询grade表中firstname以A开头的记录

SELECT * FROM grade WHERE firstname LIKE '%e';
#查询grade表中firstname以A开头以e结尾的记录

SELECT * FROM grade WHERE firstname LIKE '%l%';

#查询grade表中firstname包含l的记录

SELECT * FROM grade WHERE firstname LIKE 'a____';
#查询grade表中firstname以A开头后面有4个字符的记录

SELECT * FROM grade WHERE english > 80 AND english < 90;
#查询grade表中english在80到90之间的记录

SELECT * FROM grade WHERE math > 90 or chinese > 90;
#查询grade表中math大于90 或者 chinese大于90的记录

SELECT * FROM grade WHERE NOT (id = 1 or id = 3 or id = 5 or id = 7);
#查询grade表中id不是1、3、5、7的记录

SELECT DISTINCT(sex) FROM grade;
#查询grade表中的性别有哪些

SELECT DISTINCT(lastname) FROM grade;
#查询grade表中的lastname有哪几种

SELECT COUNT(1) FROM grade;
#求出表中所有记录的条数

SELECT COUNT(1) FROM grade WHERE english = 80;
#求出表中英语成绩的80的记录的条数

SELECT SUM(math) FROM grade;
#计算所有学生的数学成绩的和

SELECT SUM(math) FROM grade WHERE sex = 'f';
#计算女生的数学成绩的和

SELECT AVG(english) FROM grade;
#计算英语成绩平均分

SELECT AVG(english) FROM grade WHERE sex = 'm';
#计算男生的英语成绩平均分

SELECT MAX(math) FROM grade;
#求出数学成绩的最高分

SELECT MAX(math) FROM grade where sex = 'm';
#求出男生中的数学最高分

SELECT * FROM grade order BY math asc;
#按照math成绩的升序进行排列

SELECT * FROM grade order BY sex ASC,chinese desc;
#按照sex字段的升序和chinese字段的降序排列

SELECT sex,group_concat(id) FROM grade group BY sex;

#查询数据表grade中的记录，按照sex字段进行分组

SELECT lastname,COUNT(1) FROM grade GROUP BY lastname;
#将grade表按照lastname字段值分组，并计算每个分组中的学生数

SELECT lastname FROM grade GROUP BY lastname HAVING SUM(math) < 100;
#对grade表按照lastname字段分组，查出math字段和小于100的组

SELECT * FROM grade WHERE id BETWEEN 3 AND 6;

#查询grade表中的第3到第6条记录