DQL:查询表中的记录
select * from 表名;
1.语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
2.基础查询
1.多个字段的查询
select 字段名1,字段名2... from 表名;
mysql> insert into abc003 values(1,'cai',18),(4,'test',30),(5,"ddd",66);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 216
Current database: abc001
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from abc003;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | cai | 18 |
| 2 | chuan | 20 |
| 3 | qi | 99 |
| 4 | test | 30 |
| 5 | ddd | 66 |
+------+-------+------+
5 rows in set (0.00 sec)
mysql> select id,name from abc003;
+------+-------+
| id | name |
+------+-------+
| 1 | cai |
| 2 | chuan |
| 3 | qi |
| 4 | test |
| 5 | ddd |
+------+-------+
5 rows in set (0.10 sec)
注意:
- 如果查询所有字段,则可以使用*来替代字段列表。
2.去除重复
distinct
3.计算列
一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
ifnull(表达式1,表达式2);
- 表达式1:那个字段需要判断是否为null
- 如果改字段为null后的替换值。
4.起别名
as :as也可以省略
条件查询where
https://www.runoob.com/sql/sql-where.html
1.where子句后跟条件
2.运算符
>,<,>=,<=,=,<>
BETWEEN...AND
IN(集合)
LIKE
IS NULL
and or &&
or or ||
not or !
mysql> select * from abc003 where id=2;+------+-------+------+
| id | name | age |
+------+-------+------+
| 2 | chuan | 20 |
+------+-------+------+
1 row in set (0.12 sec)
mysql> select * from abc003 where id>1 and id<4;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 2 | chuan | 20 |
| 3 | qi | 99 |
+------+-------+------+
2 rows in set (0.10 sec)
其他
- 排序查询
- 聚合函数
- 分组查询
- 分页查询
1.排序查询
语法:order by 子句
order by 排序字段1 排序方式1,排序字段2 排序方式2...
mysql> select * from abc003 order by name;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 218
Current database: abc001
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | cai | 18 |
| 2 | chuan | 20 |
| 5 | ddd | 66 |
| 3 | qi | 99 |
| 4 | test | 30 |
+------+-------+------+
5 rows in set (0.10 sec)
mysql> select * from abc003 order by id desc;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 5 | ddd | 66 |
| 4 | test | 30 |
| 3 | qi | 99 |
| 2 | chuan | 20 |
| 1 | cai | 18 |
+------+-------+------+
5 rows in set (0.00 sec)
排序方式:
ASC:升序,默认的
DESC:降序
注意:
如果有多个排序条件,则当前的条件值一样时,才会判断第二条件。
2.聚合函数
将一列数据作为一个整体,进行纵向的计算。
1.count:计算个数
- 一般选择非空的列:主键
- count(*)
mysql> select count(*) as age from abc003;
+-----+
| age |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
2.max:计算最大值
3.min:计算最小值
4.sum:计算和
5.avg:计算平均值
注意:聚合函数的计算,排除null值。
解决方案:
- 选择不包含非空的列进行计算
- IFNULL函数
3.分组查询
语法:group by 分组字段
mysql> select name,sum(age) from abc003 group by name;
+-------+----------+
| name | sum(age) |
+-------+----------+
| cai | 18 |
| chuan | 20 |
| ddd | 66 |
| qi | 99 |
| test | 30 |
+-------+----------+
5 rows in set (0.00 sec)
注意:
1.分组之后查询的字段:分组字段、聚合函数
2.where 和 having的区别?
- where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
- where 后不可以跟聚合函数,having可以进行聚合函数的判断。
4.分页查询
语法:limit开始的索引,每页查询的条数;
公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
--每页显示3条记录
SELECT * FROM student LIMIT 0.3; --第1页
SELECT * FROM student LIMIT 3.3; --第2页
SELECT * FROM student LIMIT 6.3; --第3页
limit 是一个MySQL"方言"
mysql> select * from abc003 limit 1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | cai | 18 |
+------+------+------+
1 row in set (0.04 sec)
mysql> select * from abc003 limit 3;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | cai | 18 |
| 2 | chuan | 20 |
| 3 | qi | 99 |
+------+-------+------+
3 rows in set (0.00 sec)