侧边栏

SQL分类之DQL:查询表中的记录

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. 排序查询
  2. 聚合函数
  3. 分组查询
  4. 分页查询

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:计算个数

  1. 一般选择非空的列:主键
  2. 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值。
解决方案:

  1. 选择不包含非空的列进行计算
  2. 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的区别?

  1. where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
  2. 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) 
posted @ 2019-07-15 20:45  菜鸟-传奇  阅读(353)  评论(0编辑  收藏  举报