八、单表数据记录查询操作

---------------------------------------------------------       小路原创           ------------------------------------------------

一、简单数据记录查询

1、所有数据字段查询

use db_name;

select [fields1,……fieldsn] from table_name;

--fields顺序可以变换

或

use db_name;

select * from table_name;

2、去掉重复的数据记录

use db_name;

select distinct fields1,fields2……

from tbale_name;

example：

use school;

select distinct Age

from students;

3、实现算术四则运算数据查询

+、-、*、/、%

example:

use school;

select Name,Age+1 from students;

select Name,Age-2 from students;

select Name,Age*2 from students;

select Name,Age/2 from students;

select Name,Age%2 from students;

select * from students;

--结果说明，是先查询出数据再进行运算，不修改表中的数据。

4、设置显示格式数据查询

--使用concat()函数来连接字符串，设置显示数据的格式。

example:

use school;

select concat('学号为',ID,'的学生叫',Name,',今年',Age,'岁了。') as '学生信息'

from students;

二、条件数据记录查询

1、带关系运算符的条件查询

>、<、=、！=（<>）、>=、<=、and（&&）、or（||）、xor、not（!）、between……and……、not between ……and……、is null、is not null、in、not in 

example:

use school;

select * from students

where ID >= 4 && Age between 18 and 30; 

select * from students

where Age in(18,19,20);

select * from students

where Age not in(18,19,20);

或者

select * from students

where not Age in(18,19,20);

--注意：

--使用in关键字，查询集合中存在null，不会影响查询结果。

select * from students

where Age in(18,19,20,null);

--使用not in 关键字，查询集合中存在null，不会有任何结果。

select * from students

where Age not in(18,19,20,null);

2、带like的模糊查询

通配符：

"_"：能匹配一个字符；

"%"：能匹配任意长度的字符串；

example:

use school;

select * from students where Name like '李__';

select * from students where Name like '李%';

select * from students where ! Name like 'xxx';--不显示任何数据 

select * from students where !(Name like 'xxx');--正确显示

select * from students where Name not like '李%';--正确显示

三、排序数据记录查询

MySQL中默认为升序，如果字段的值为null，则值为最小，升序排列时排在第一条。

1、按照单字段排序

use db_name;

select fields1,……,fieldsn

from table_name

where condition

order by fields1 ASC|DESC;

example:

use school;

select * from 

students 

order by ID desc;

2、按照多字段排序

先按照第一个字段排序，第一个字段相同时再按照第二个字段排序……

use db_name;

select fields1,……,fieldsn

from table_name

where condition

order by fields1 [ASC|DESC] ,fields2 ASC|DESC……;

example:

use school;

select * from 

students 

order by Name asc,

Age desc;

四、限制数据记录查询数量

use db_name;

select fields1,fields2……

from table_name

where condition 

limit offset_start,row_count;

--offset_start指定记录的起始偏移量，row_count指定显示的行数。

1、不指定初始位置

不指定初始位置，默认值为0，从第一条记录开始。

当row_count小于查询出的记录数量时，只显示row_count条记录。

如果row_count大于查询出的记录数量时，只显示查询结果。

example:

--row_count小于查询出的记录数量

use school;

select * from students 

limit 5;

--row_count大于查询出的记录数量

use school;

select * from students

where Age > 10

limit 50;

2、指定初始位置

limit经常跟order by关键字一起使用，先对结果进行排序，然后再讲记录分页显示出来。

由于默认初始位置为0，所以下面两个例子有一样的效果。

limit 0,row_count;

limit row_count;

example:

use school;

--第一页从第一条记录开始

select * from students

order by Age limit 0,5;//--或者order by Age limit 5;

--第二页从第六条记录开始

select * from students

order by Age limit 5,5;

五、统计函数

统计函数：count()、avg()、sum()、max()、min()

select function(field)

from table_name

where condition;

1、count()函数

coun(*)：统计表中所有记录数量

count(field):统计指定字段，将忽略null值

example:

use school;

select count(*) as '全部记录' from students;

select count(distinct Age) as '年龄' from students where Age>=20;

2、avg()函数

avg(field):将算出指定字段的平均值，忽略null值

example:

use school;

select avg(Age) as '平均年龄' from students;--将输出小数

3、sum()函数

sum(field):求出指定字段的和，忽略null值

example:

use school;

select sum(Age) as '总年龄' from students;

4、max()、min()函数

max(fields):统计计算出指定字段的最大值，忽略null值

min(fields):统计计算出指定字段的最小值，忽略null值

example:

use school;

select max(Age) as '最大年龄' from students;

select min(Age) as '最小年龄' from students;

注意：如果所操作的表中没有任何数据记录，则count()函数返回0，而其他函数返回null。

use school;

create table test_table(Id int,Name varchar(10));

select count(Id),sum(Id),avg(Id),max(Id),min(Id) from test_table;--输出0,null,null,null,null

六、分组记录数据查询

在实际应用中，经常会把所有记录进行分组，然后才对这些分组后的数据记录进行统计计算。

注意：分组所依据的字段上的值要具有重复值分组查询才有意义。

1、简单分组查询

select function()

from table_name

where condition 

group by field;--表中field字段值要有重复值才有意义

example:

use school;

select Age,count(*) as '人数' 

from students

group by Age;

2、实现统计功能的分组查询

group_concat(field):显示每个分组中指定字段field的所有字段值。

--下面的例子显示以Age分组后每个分组的Age值、每个分组中所有人的姓名、每个分组中的人数

example:

use school;

select Age as '年龄',Group_concat(Name) as '姓名',count(Name) as '人数' 

from students

group by Age;

3、实现多个字段分组查询

--下面的例子将先按照Age分组，然后再按照Name分组，输出每个分组的年龄、每个分组中的全部姓名和分组中的人数

example:

use school;

select Age as '年龄',Group_concat(Name) as '姓名',count(Name) as '人数' 

from students

group by Age,Name;

 

 

4、实现having子句限定分组查询

MySQL中提供having关键字实现对分组进行条件限制，而where关键字是条件限制数据记录

select function(field)

from table_name

where condition 

group by field1,field2……

having condition;

--下面的例子就是对上面的例子的改进。也就是说只有满足Age>=19的才进行分组并显示出来，小于19的不分组显示。

example：

use school;

select Age as '年龄',Group_concat(Name) as '姓名',count(Name) as '人数' 

from students

group by Age,Name

having Age>=19;

 

---------------------------------------------------------       小路原创           ------------------------------------------------