数据库命令数据操作2-查询表selete,where,form,group by,having,order by,limit

查询在sql命令中占据绝对主导地位,整个项目90%都在查询,重点学习和深入!

[]可省略

语法:

语法：select [选项] 列名 from 表名 [where 条件]  [group by 分组] [order by 排序][having 条件] [limit 限制]
标序:select [1选项] 2列名 from 3表名 [4where 条件]  [5group by 分组] [6order by 排序][7having 条件] [8limit 限制]

案例:

select * from database where id =4 order by cat_id desc limit 3,5

查询basebase数据库id为4的结果, 按照cat_id 的倒序排列,且从第三条开始取,一共取5条

注:在查询过程中执行顺序：from>where>group（含聚合）>having>order>select。

拓展阅读:

1. sql的常用聚合函数

2. sql语句的模糊查询和模糊查询的4种通配符

3. sql语句中的union联合语句以及它的作用 

一、选项

查询语句中的选项有两个

1、 all：显示所有数据 【默认】

2、 distinct：去除结果集中重复的数据

mysql> select all stuaddress from stu;
+------------+
| stuaddress |
+------------+
| 北京           |
| 北京           |
| 天津           |
| 河南           |
| 河北          |
| 北京           |
+------------+
6 rows in set (0.00 sec)

-- 去除重复的项
mysql> select distinct stuaddress from stu;
+------------+
| stuaddress |
+------------+
| 北京           |
| 天津           |
| 河南           |
| 河北          |
+------------+
4 rows in set (0.00 sec)

二、列名/字段

通过as给字段取别名

mysql> select '锄禾日当午' as '标题';   -- 取别名
+------------+
| 标题           |
+------------+
| 锄禾日当午          |
+------------+
1 row in set (0.00 sec)

mysql> select ch,math,ch+math as '总分' from stu;
+------+------+------+
| ch   | math | 总分    |
+------+------+------+
|   80 | NULL | NULL |
|   77 |   76 |  153 |
|   55 |   82 |  137 |
| NULL |   74 | NULL |

-- 多学一招：as可以省略
mysql> select ch,math,ch+math '总分' from stu;
+------+------+------+
| ch   | math | 总分    |
+------+------+------+
|   80 | NULL | NULL |
|   77 |   76 |  153 |
|   55 |   82 |  137 |
| NULL |   74 | NULL |

字段表达式

表达式并不是表中的一个真正的字段，它是由一个表达式决定的字段。

-- 可以直接输出内容
mysql> select '锄禾日当午';
+------------+
| 锄禾日当午          |
+------------+
| 锄禾日当午          |
+------------+
1 row in set (0.00 sec)

-- 输出表达式
mysql> select 10*10;
+-------+
| 10*10 |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

mysql> select ch,math,ch+math from stu;
+------+------+---------+
| ch   | math | ch+math |
+------+------+---------+
|   80 | NULL |    NULL |
|   77 |   76 |     153 |
|   55 |   82 |     137 |
| NULL |   74 |    NULL |

表达式部分可以用函数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6669325378415478 |
+--------------------+
1 row in set (0.00 sec)

三、from子句

from后面跟的是数据源。如果数据源是多个。返回笛卡尔积。

笛卡尔积:表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合,在实际应用中，笛卡尔积本身大多没有什么实际用处，只有在两个表连接时加上限制条件，才会有实际意义

案例:

插入测试表

create table t1(
       str char(2)
);
insert into t1 values ('aa'),('bb');
create table t2(
       num int
);
insert into t2 values (10),(20);

测试

-- from子句
mysql> select * from t1;
+------+
| str  |
+------+
| aa   |
| bb   |
+------+
2 rows in set (0.00 sec)

-- 多个数据源,返回笛卡尔积
mysql> select * from t1,t2;
+------+------+
| str  | num  |
+------+------+
| aa   |   10 |
| bb   |   10 |
| aa   |   20 |
| bb   |   20 |
+------+------+
4 rows in set (0.00 sec)

笛卡尔积

dual表

dual表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证select语句的完整又必须要一个表名，这时候就使用伪表。

dual表案例:

mysql> select 10*10 as 结果 from dual;
+------+
| 结果     |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

四、where子句

where后面跟的是条件，在数据源中进行筛选。返回条件为真记录

因为是筛选,所以我们需要先了解mysql支持的运算符

mysql支持的运算符

-- 比较运算符
>    大于
<    小于
>=   大于等于
<=   小于等于
=   等于
!=  不等于
-- 逻辑运算符
and  与
or   或
not  非
-- 其他
in | not in                      字段的值在枚举范围内
between…and|not between…and      字段的值在数字范围内
is null | is not null            字段的值不为空

案例

-- 查找语文成绩及格的学生
mysql> select * from stu where ch>=60;
-- 查询语文和数学都及格的学生
mysql> select * from stu where ch>=60 and math>=60;
-- 查询语文或数学不及格的学生
mysql> select * from stu where ch<60 or math<60;

思考：如下语句输出什么？

解惑:1代表ture,0代表false

mysql> select * from stu where 1;       -- 输出所有数据
mysql> select * from stu where 0;        -- 不输出数据

思考：如何查找北京和上海的学生

用or、in都可以实现

-- 通过or实现
mysql> select * from stu where stuaddress='北京' or stuaddress='上海';

-- 通过in语句实现
mysql> select * from stu where stuaddress in ('北京','上海');

-- 查询不是北京和上海的学生
mysql> select * from stu where stuaddress not in ('北京','上海');

思考：查找年龄在20~25之间

用运算符和between and都可以实现

-- 方法一：
mysql> select * from stu where stuage>=20 and stuage<=25;

-- 方法二：
mysql> select * from stu where not(stuage<20 or stuage>25);

-- 方法三：between...and...
mysql> select * from stu where stuage between 20 and 25;

-- 年龄不在20~25之间
mysql> select * from stu where stuage not between 20 and 25;

思考：

null和not null 检查字段值是否为空

-- 查找缺考的学生
mysql> select * from stu where ch is null or math is null;

+--------+----------+--------+--------+---------+------------+------+------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           |   80 | NULL |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | NULL |   74 |
+--------+----------+--------+--------+---------+------------+------+------+

-- 查找没有缺考的学生
mysql> select * from stu where ch is not null and math is not null;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25302 | 李文才        | 男       |     31 |       3 | 上海          |   77 |   76 |
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           |   55 |   82 |
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           |   72 |   56 |
| s25318 | 争青小子        | 男       |     26 |       6 | 天津           |   86 |   92 |
| s25319 | 梅超风        | 女      |     23 |       5 | 河北          |   74 |   67 |
| s25320 | Tom      | 男       |     24 |       8 | 北京           |   65 |   67 |
| s25321 | Tabm     | 女      |     23 |       9 | 河北          |   88 |   77 |
+--------+----------+--------+--------+---------+------------+------+------+
7 rows in set (0.00 sec)

-- 查找需要补考的学生
mysql> select * from stu where ch<60 or math<60 or ch is null or math is null;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽         | 男       |     18 |       1 | 北京           |   80 | NULL |
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           |   55 |   82 |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | NULL |   74 |
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           |   72 |   56 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)

in练习

-- 1、查找学号是s25301,s25302,s25303的学生

mysql> select * from stu where stuno in ('s25301','s25302','s25303');
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京           |   80 | NULL |
| s25302 | 李文才       | 男       |     31 |       3 | 上海          |   77 |   76 |
| s25303 | 李斯文       | 女      |     22 |       2 | 北京           |   55 |   82 |
+--------+---------+--------+--------+---------+------------+------+------+
3 rows in set (0.00 sec)

between and练习

-- 2、查找年龄是18~20的学生

mysql> select * from stu where stuage between 18 and 20;
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京           |   80 | NULL |
+--------+---------+--------+--------+---------+------------+------+------+
1 row in set (0.00 sec)

五、group by 【分组查询】

将查询的结果分组，分组查询目的在于统计数据。

案例:

-- 查询男生和女生的各自语文平均分

mysql> select stusex,avg(ch) '平均分' from stu group by stusex;
+--------+---------+
| stusex | 平均分       |
+--------+---------+
| 女      | 72.2500 |
| 男       | 77.0000 |
+--------+---------+
2 rows in set (0.00 sec)

-- 查询男生和女生各自多少人

mysql> select stusex,count(*) 人数 from stu group by stusex;
+--------+------+
| stusex | 人数     |
+--------+------+
| 女      |    4 |
| 男       |    5 |
+--------+------+
2 rows in set (0.00 sec)

-- 查询每个地区多少人

mysql> select stuaddress,count(*) from stu group by stuaddress;
+------------+----------+
| stuaddress | count(*) |
+------------+----------+
| 上海          |        1 |
| 北京           |        3 |
| 天津           |        2 |
| 河北          |        2 |
| 河南           |        1 |
+------------+----------+
5 rows in set (0.00 sec)

-- 每个地区的数学平均分

mysql> select stuaddress,avg(math) from stu group by stuaddress;
+------------+-----------+
| stuaddress | avg(math) |
+------------+-----------+
| 上海          |   76.0000 |
| 北京           |   74.5000 |
| 天津           |   83.0000 |
| 河北          |   72.0000 |
| 河南           |   56.0000 |
+------------+-----------+
5 rows in set (0.00 sec)

如果查询字段是普通字段，只取第一个值

通过group_concat()函数将同一组的值连接起来显示

mysql> select group_concat(stuname),stusex,avg(math) from stu group by stusex;
+-------------------------------------+--------+-----------+
| group_concat(stuname)               | stusex | avg(math) |
+-------------------------------------+--------+-----------+
| 李斯文,诸葛丽丽,梅超风,Tabm             | 女      |   70.5000 |
| 张秋丽,李文才,欧阳俊雄,争青小子,Tom      | 男       |   77.2500 |
+-------------------------------------+--------+-----------+
2 rows in set (0.00 sec)

多列分组

mysql> select stuaddress,stusex,avg(math) from stu group by stuaddress,stusex;
+------------+--------+-----------+
| stuaddress | stusex | avg(math) |
+------------+--------+-----------+
| 上海          | 男       |   76.0000 |
| 北京           | 女      |   82.0000 |
| 北京           | 男       |   67.0000 |
| 天津           | 男       |   83.0000 |
| 河北          | 女      |   72.0000 |
| 河南           | 女      |   56.0000 |
+------------+--------+-----------+
6 rows in set (0.00 sec)

 group小结：

1、如果是分组查询，查询字段是分组字段和聚合函数。

2、查询字段是普通字段，只取第一个值

3、group_concat()将同一组的数据连接起来

六、order by排序

asc：升序【默认】

desc：降序

-- 按年龄的升序排列 两个sql语句查询结果一样
mysql> select * from stu order by stuage asc;
mysql>  select * from stu order by stuage;       -- 默认是升序

-- 按总分降序
mysql> select *,ch+math '总分' from stu order by ch+math desc;  --降序

多列排序

-- 年龄升序，如果年龄一样，按ch降序排列
mysql> select * from stu order by stuage asc,ch desc;

思考如下代码表示什么含义

select * from stu order by stuage desc,ch desc;     #年龄降序，语文降序
select * from stu order by stuage desc,ch asc;      #年龄降序，语文升序
select * from stu order by stuage,ch desc;          #年龄升序、语文降序
select * from stu order by stuage,ch;               #年龄升序、语文升序

七、having条件

having：是在结果集上进行条件筛选

-- 查询女生
mysql> select * from stu where stusex='女';
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           |   55 |   82 |
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           |   72 |   56 |
| s25319 | 梅超风        | 女      |     23 |       5 | 河北          |   74 |   67 |
| s25321 | Tabm     | 女      |     23 |       9 | 河北          |   88 |   77 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)

-- 查询女生
mysql> select * from stu having stusex='女';
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           |   55 |   82 |
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           |   72 |   56 |
| s25319 | 梅超风        | 女      |     23 |       5 | 河北          |   74 |   67 |
| s25321 | Tabm     | 女      |     23 |       9 | 河北          |   88 |   77 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)

-- 查询女生姓名
mysql> select stuname from stu where stusex='女';
+----------+
| stuname  |
+----------+
| 李斯文        |
| 诸葛丽丽         |
| 梅超风        |
| Tabm     |
+----------+
4 rows in set (0.00 sec)

-- 使用having报错，因为结果集中没有stusex字段
mysql> select stuname from stu having stusex='女';
ERROR 1054 (42S22): Unknown column 'stusex' in 'having clause'

where也是限定条件,having也是限定条件?那么where和having的区别是什么?

where一般用于对原始数据进行筛选，having是对记录集进行筛选。 

拓展阅读:sql语句中where和having的区别是什么?它们的使用场景介绍

八、limit限定

语法:

limit [起始位置]，显示长度

案例:

-- 从第0个位置开始取，取3条记录
mysql> select * from stu limit 0,3;

-- 从第2个位置开始取，取3条记录
mysql> select * from stu limit 2,3;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25303 | 李斯文        | 女      |     22 |       2 | 北京           |   55 |   82 |
| s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           | NULL |   74 |
| s25305 | 诸葛丽丽         | 女      |     23 |       7 | 河南           |   72 |   56 |
+--------+----------+--------+--------+---------+------------+------+------+
3 rows in set (0.00 sec)

起始位置可以省略，默认是从0开始

mysql> select * from stu limit 3;
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽        | 男       |     18 |       1 | 北京           |   80 | NULL |
| s25302 | 李文才       | 男       |     31 |       3 | 上海          |   77 |   76 |
| s25303 | 李斯文       | 女      |     22 |       2 | 北京           |   55 |   82 |
+--------+---------+--------+--------+---------+------------+------+------+
3 rows in set (0.00 sec)

例题：找出班级总分前三名

mysql> select *,ch+math total from stu order by (ch+math) desc limit 0,3;
+--------+----------+--------+--------+---------+------------+------+------+-------+
| stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math | total |
+--------+----------+--------+--------+---------+------------+------+------+-------+
| s25318 | 争青小子   | 男       |     26 |       6 | 天津           |   86 |   92 |   178 |
| s25321 | Tabm     | 女      |     23 |       9 | 河北          |   88 |   77 |   165 |
| s25302 | 李文才    | 男       |     31 |       3 | 上海          |   77 |   76 |   153 |
+--------+----------+--------+--------+---------+------------+------+------+-------+
3 rows in set (0.00 sec)

多学一招：limit在update和delete语句中也是可以使用的。

--     前3名语文成绩加1分
mysql> update stu set ch=ch+1 order by ch+math desc limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

-- 前3名删除
mysql> delete from stu order by ch+math desc limit 3;
Query OK, 3 rows affected (0.00 sec)