mysql查4个表_MySQL(四） 单表查询

4 单表查询

搭建环境：在test数据库下创建数据表grade：

CREATE TABLEgrade(

idINT(10) NOT NULL,

sexCHAR(1),

firstnameVARCHAR(20) NOT NULL,

lastnameVARCHAR(20) NOT NULL,

englishFLOAT,

mathFLOAT,

chineseFLOAT);

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

INSERT INTOgradeVALUES (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);

4.1. 简单查询

4.1.1 查询所有字段

格式：SELECT * FROM 表名;

SELECT * FROM grade;

4.1.2 查询指定字段

格式：SELECT 指定字段名 FROM 表名;

例：查询grade表中的id,firstname,lastname字段

SELECT id,firstname,lastname FROM grade;

4.2. 按条件查询

格式：SELECT 字段名 FROM 表名

WHERE 条件表达式;

4.2.1 带关系运算符的查询

例1：查询grade表中id大于4的学生姓名

SELECT id,firstname,lastname FROMgradeWHERE id>4;

例2：查询grade表中女生的记录

SELECT * FROMgradeWHERE sex='f';

4.2.2 带IN关键字的查询

例：查询grade表中id值为2,4,6的学生记录

SELECT * FROMgradeWHERE id IN (2,4,6);

4.2.3 带 BETWEEN AND关键字的查询

例：查询grade表中math成绩在85到94之间的记录

SELECT * FROMgradeWHERE math BETWEEN 85 AND 94;

4.2.4 带LIKE关键字的查询

(1) 百分号(%)通配符

SELECT * FROM grade WHERE firstname LIKE "A%";

SELECT * FROM grade WHERE firstname LIKE "A%e";

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

(2) 下划线(_)通配符

SELECT * FROM grade WHERE firstname LIKE "A____";

SELECT * FROM grade WHERE firstname LIKE "_e___";

4.2.5 带AND关键字的条件查询

SELECT * FROM grade WHERE english>80 AND math>90;

4.2.6 带关键字OR的条件查询

SELECT * FROM grade WHERE math>90 OR chinese>90;

4.2.7 带关键字NOT的条件查询

SELECT * FROM grade WHERE id NOT IN (1,3,5,7);

4.2.8 空值查询

SELECT * FROM grade WHERE id IS NULL;

4.2.9 带DISTINCT关键字的查询(过滤)

使用DISTINCT关键字可以过滤掉查询记录中重复的值，并且只有DISTINCT关键字后指定的多个字段值都相同，才会被认作是重复记录。

格式：SELECT DISTINCT 指定过滤的字段名 FROM 表名；

例1：查询grade表中的性别有哪些

SELECT DISTINCT sex FROM grade;

例2：查询grade表中的lastname有哪几种

SELECT DISTINCT lastname FROM grade;

4.3. 高级查询

4.3.1 聚合函数

COUNT( )函数：统计记录的条数

格式：SELECT COUNT(*) FROM 表名 [WHERE 条件表达式];

例1：求出表中所有记录的条数

SELECT COUNT(*) FROM grade;

例2：求出表中英语成绩的80的记录的条数

SELECT COUNT(*) FROM grade WHERE english>80;

SUM( )函数：求出表中某个字段的所有值的总和

格式：SELECT SUM(字段名) FROM 表名 [WHERE 条件表达式];

例1：计算所有学生的数学成绩的和

SELECT SUM(math) FROM grade;

例2：计算女生的数学成绩的和

SELECT SUM(math) FROM grade WHERE sex='f';

AVG( )函数：用于求出某个字段所有值的平均值

格式：SELECT AVG(字段名) FROM 表名 [WHERE 条件表达式];

例1：计算英语成绩平均分

SELECT AVG(english) FROM grade;

例2：计算男生的英语成绩平均分

SELECT AVG(english) FROM grade WHERE sex='m';

MAX( )函数：用于求出某个字段的最大值

格式：SELECT MAX(字段名) FROM 表名 [WHERE 条件表达式];

例1：求出数学成绩的最高分

SELECT MAX(math) FROM grade;

例2：求出男生中的数学最高分

SELECT MAX(math) FROM grade WHERE sex='m';

MIN( )函数：用于求出某个字段的最小值

格式：SELECT MIN(字段名) FROM 表名 [WHERE 条件表达式];

与MAX( )函数用法一致，不再举例。

4.3.2 对查询结果排序

格式：SELECT 字段名 FROM 表名

ORDER BY 排序依据字段名 [ASC|DESC]......

例1：按照math成绩的升序进行排列

SELECT * FROMgradeORDER BY math ASC;

例2：按照sex字段的升序和chinese字段的降序排列

SELECT sex,english,math,chinese FROMgradeORDER BY sex ASC,chinese DESC;

4.3.3 分组查询

格式：SELECT 字段名 FROM 表名

GROUP BY 分组依据字段名

[HAVING 条件表达式];

(1) 单独使用GROUP BY 分组

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

SELECT * FROMgradeGROUP BY sex;

注意：这样的查询结果只显示每个分组中的一条记录，意义并不大，一般情况下GRUOP BY都会和聚合函数一起使用。

(2) GROUP BY 和聚合函数一起使用

例：将grade表按照lastname字段值分组，并计算每个分组中的学生数

SELECT COUNT(*),lastname FROMgradeGROUP BY lastname;

(3) GROUP BY和HAVING关键字一起使用

注意：HAVING和WHERE都用于对查询结果进行过滤，两者的区别在于，HAVING关键字后可以跟聚合函数，而WHERE关键字不能。即：

WHERE 是在分组之前进行过滤，HAVING是在分组之后进行过滤。

例：对grade表按照lastname字段分组，查出math字段和小于100的组

SELECT SUM(math),lastname FROMgradeGROUP BYlastnameHAVING SUM(math)<100;

4.3.4 使用LIMIT限制查询结果的数量

格式：SELECT 字段名 FROM 表名

LIMIT [OFFSET],记录数;

例1：查询grade表的前4条记录

SELECT * FROMgrade

LIMIT4;

例2：查询grade表中的第3到第6条记录

SELECT * FROMgrade

LIMIT2,4;

4.3.5   函数(列表)

MySQL中提供了丰富的函数，通过这些函数可以简化用户对数据的操作。

包括：数学函数、字符串函数、日期和时间函数、条件判断函数、加密函数等

SELECT CONCAT(id,'_',english,'_',math,'_',chinese) FROM grade;

SELECT id,IF(sex='m',1,0) FROM grade;

4.4为表和字段取别名

4.4.1 为表取别名

格式：SELECT 字段名 FROM 表名 [AS] 别名;

例：为grade表取一个别名g，并查询grade表中女生的成绩

SELECT id,sex,english,math,chinese FROM grade ASgWHERE g.sex='f';

注意：条件表达式中可以为g.sex或者sex，但是不能为grade.sex

4.4.2 为字段取别名

格式：SELECT 字段名 [AS] 别名 FROM 表名;

SELECT id,math+english+chinese total FROM grade;