Python社区  »  Python

Python全栈(三)数据库优化之3.数据的修改和删除及数据的条件查询

cupyter • 1 周前 • 15 次点击  

一、修改&删除&简单查询

1.修改

update 表名 set 列1=值1,列2=值2,… where 条件;

--不加where,全部修改
update students set name = 'Jack';
  • 1
  • 2
--加where,修改符合条件的记录
update students set name = 'Jack' where name = 'John';
  • 1
  • 2
update students set name = 'John',gender = 'secret' where name = 'Jack';
  • 1

2.删除

物理删除

delete from 表名 where 条件;

--删除的是表中的全部数据
delete from demo1;
  • 1
  • 2

打印

Query OK, 2 rows affected (0.00 sec)
  • 1
--删除的是表中符合条件的数据
delete from students where id = 5;
  • 1
  • 2

打印

Query OK, 1 row affected (0.01 sec)
  • 1

删除的是表中的数据,数据被真实地删除,删除操作后被删除的数据已不存在。

逻辑删除

is_delete 字段表示是否删除,相当于对原有数据增加一个标识,并未真正删除数据。
这样能保存原有的数据,因为在互联网时代数据有巨大的价值,不应该被任意删除。
查找:

select * from students where is_delete = 0;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+ 
| id | NAME  | age  | gender | cls_id | birth      | is_delete | 
+----+-------+------+--------+--------+------------+-----------+ 
|  1 | Tom   |   18 | male   |      1 | 1999-09-09 | 0         | 
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 | 0         | 
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 | 0         | 
|  4 | John  |   19 | secret |      1 | 1999-07-09 | 0         | 
|  6 | John  |   16 | secret |      1 | 1999-05-09 | 0         | 
|  7 | John  |   19 | secret |      1 | 1999-07-09 | 0         | 
|  8 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
|  9 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 10 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 11 | Rose  |   19 | female |      2 | 1999-07-09 | 0         | 
| 12 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 13 | Rose  |   19 | female |      2 | 1999-07-09 | 0         | 
| 14 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 15 | Rose  |   19 | female |      2 | 1999-07-09 | 0         | 
| 16 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 17 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 18 | Rose  |   19 | female |      2 | 1999-07-09 | 0         | 
| 19 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 20 | Rose  |   19 | female |      2 | 1999-07-09 | 0         | 
| 21 | John  |   19 | secret |      1 | 1999-07-09 | 0         | 
| 22 | John  |   19 | secret |      1 | 1999-07-09 | 0         | 
| 23 | Tony  |   18 | male   |      1 | 1990-01-01 | 0         | 
| 24 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 25 | John  |   16 | secret |      1 | 1999-05-09 | 0         | 
| 26 | John  |   19 | secret |      2 | 1999-07-09 | 0         | 
| 27 | Rose  |   19 | female |      2 | 1999-07-09 | 0         | 
+----+-------+------+--------+--------+------------+-----------+ 
26 rows in set (0.00 sec)                                        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

此时删除即修改is_delete字段:




    
update students set is_delete = 1 where id = 7;
  • 1

打印

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
  • 1
  • 2

3.MySQL简单查询

(1)查询所有列:

select * from students;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  1 | Tom   |   18 | male   |      1 | 1999-09-09 |         1 |
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 23 | Tony  |   18 | male   |      1 | 1990-01-01 |         1 |
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
26 rows in set (0.01 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

(2)去除重复字段的查询:
消除重复行:distinct字段

select distinct name from students;
  • 1

打印

+-------+               
| name  |               
+-------+               
| Tom   |               
| Jerry |               
| Nancy |               
| John  |               
| Rose  |               
| Tony  |               
+-------+               
6 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

distinct关注的是整个行是否重复。

select distinct name,age from students;
  • 1

打印

+-------+------+        
| name  | age  |        
+-------+------+        
| Tom   |   18 |        
| Jerry |   19 |        
| Nancy |   17 |        
| John  |   19 |        
| John  |   16 |        
| Rose  |   19 |        
| Tony  |   18 |        
+-------+------+        
7 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

(3)查询指定列:
select 列1,列2,… from 表名;

select name,age,gender from students;
  • 1

打印

+-------+------+--------+ 
| name  | age  | gender | 
+-------+------+--------+ 
| Tom   |   18 | male   | 
| Jerry |   19 | male   | 
| Nancy |   17 | male   | 
| John  |   19 | secret | 
| John  |   16 | secret | 
| John  |   19 | secret | 
| John  |   19 | secret | 
| John  |   19 | secret | 
| John  |   19 | secret | 
| Rose  |   19 | female | 
| John  |   19 | secret | 
| Rose  |   19 | female | 
| John  |   19 | secret | 
| Rose  |   19 | female | 
| John  |   19 | secret | 
| John  |   19 | secret | 
| Rose  |   19 | female | 
| John  |   19 | secret | 
| Rose  |   19 | female | 
| John  |   19 | secret | 
| John  |   19 | secret | 
| Tony  |   18 | male   | 
| John  |   19 | secret | 
| John  |   16 | secret | 
| John  |   19 | secret | 
| Rose  |   19 | female | 
+-------+------+--------+ 
26 rows in set (0.01 sec) 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

(4)可以为表、字段等重命名,

select name as n,gender from students as stu;
  • 1

打印

+-------+--------+       
| n     | gender |       
+-------+--------+       
| Tom   | male   |       
| Jerry | male   |       
| Nancy | male   |       
| John  | secret |       
| John  | secret |       
| John  | secret |       
| John  | secret |       
| John  | secret |       
| John  | secret |       
| Rose  | female |       
| John  | secret |       
| Rose  | female |       
| John  | secret |       
| Rose  | female |       
| John  | secret |       
| John  | secret |       
| Rose  | female |       
| John  | secret |       
| Rose  | female |       
| John  | secret |       
| John  | secret |       
| Tony  | male   |       
| John  | secret |       
| John  | secret |       
| John  | secret |       
| Rose  | female |       
+-------+--------+       
26 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

通常在对 多个表 进行联合查询时,会对表重命名。
as关键字表示在查询的结果中显示时,替换掉原来的名称,而不是将字段替换,只是显示时发生了变化,不能被引用,否则会报错。

select name as n,gender from students where n = 'Tom';
  • 1

打印

ERROR 1054 (42S22): Unknown column 'n' in 'where clause'
  • 1

即报错,字段别名不能在where条件中使用。

二、条件查询-比较&逻辑运算符

1.比较运算符:

(1) > 大于

select * from students where age > 18;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+  
| id | NAME  | age  | gender | cls_id | birth      | is_delete |  
+----+-------+------+--------+--------+------------+-----------+  
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |  
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 |  
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 |  
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 |  
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 |  
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
+----+-------+------+--------+--------+------------+-----------+  
21 rows in set (0.01 sec)                                         
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

(2) < 小于

select * from students where age < 18;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
3 rows in set (0.01 sec)                                        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

(3) >= 大于等于

select * from students where age >= 18;
  • 1

打印




    
+----+-------+------+--------+--------+------------+-----------+ 
| id | NAME  | age  | gender | cls_id | birth      | is_delete | 
+----+-------+------+--------+--------+------------+-----------+ 
|  1 | Tom   |   18 | male   |      1 | 1999-09-09 |         1 | 
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 | 
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
| 23 | Tony  |   18 | male   |      1 | 1990-01-01 |         1 | 
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
+----+-------+------+--------+--------+------------+-----------+ 
23 rows in set (0.00 sec)                                        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

(4) <= 小于等于

select * from students where age <= 18;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  1 | Tom   |   18 | male   |      1 | 1999-09-09 |         1 |
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
| 23 | Tony  |   18 | male   |      1 | 1990-01-01 |         1 |
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
5 rows in set (0.00 sec)                                        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

(5) = 等于

select * from students where age = 18;
  • 1

打印

+----+------+------+--------+--------+------------+-----------+
| id | NAME | age  | gender | cls_id | birth      | is_delete |
+----+------+------+--------+--------+------------+-----------+
|  1 | Tom  |   18 | male   |      1 | 1999-09-09 |         1 |
| 23 | Tony |   18 | male   |      1 | 1990-01-01 |         1 |
+----+------+------+--------+--------+------------+-----------+
2 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

(6) != <> 不等于

select * from students where name != 'John';
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  1 | Tom   |   18 | male   |      1 | 1999-09-09 |         1 |
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 23 | Tony  |   18 | male   |      1 | 1990-01-01 |         1 |
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
10 rows in set (0.00 sec)                                       
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
select * from students where name <> 'John';
  • 1

结果与前面相同。

2.逻辑运算符

(1) and

select * from students where age > 18 and age < 28;
  • 1

打印




    
+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
21 rows in set (0.00 sec)                                       
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
select * from students where age > 18 and gender = 'female';
  • 1

打印

+----+------+------+--------+--------+------------+-----------+
| id | NAME | age  | gender | cls_id | birth      | is_delete |
+----+------+------+--------+--------+------------+-----------+
| 11 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 13 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 15 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 18 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 20 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 27 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
+----+------+------+--------+--------+------------+-----------+
6 rows in set (0.00 sec)                                       
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

(2) or

select * from students where id < 4 or is_delete = 0;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  1 | Tom   |   18 | male   |      1 | 1999-09-09 |         1 |
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
3 rows in set (0.00 sec)                                        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

(3) not 取反

select * from students where not (age = 18 and gender = 'female');
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  1 | Tom   |   18 | male   |      1 | 1999-09-09 |         1 |
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 23 | Tony  |   18 | male   |      1 | 1990-01-01 |         1 |
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
26 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

又如

select * from students where (not age <= 18) and gender = 2;
  • 1

打印

+----+------+------+--------+--------+------------+-----------+
| id | NAME | age  | gender | cls_id | birth      | is_delete |
+----+------+------+--------+--------+------------+-----------+
| 11 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 13 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 15 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 18 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 20 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 27 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
+----+------+------+--------+--------+------------+-----------+
6 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

等价于

select * from students where age > 18 and gender = 2;
  • 1

结果与前者相同。
括号 解决优先级问题,会使代码可读性更高。

三、条件查询-模糊&范围查询&空判断

1.模糊查询:

(1) like 关键字
% :替换0个或多个,即任意多个字符
_ :替换1个字符

select * from students where name like 'J%';
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+ 
| id | NAME  | age  | gender | cls_id | birth      | is_delete | 
+----+-------+------+--------+--------+------------+-----------+ 
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 | 
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 | 
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 | 
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
+----+-------+------+--------+--------+------------+-----------+ 
17 rows in set (0.00 sec)                                        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
select * from students where name like '%o%';
  • 1

打印

+----+------+------+--------+--------+------------+-----------+
| id | NAME | age  | gender | cls_id | birth      | is_delete |
+----+------+------+--------+--------+------------+-----------+
|  1 | Tom  |   18 | male   |      1 | 1999-09-09 |         1 |
|  4 | John |   19 | secret |      1 | 1999-07-09 |         1 |
|  6 | John |   16 | secret |      1 | 1999-05-09 |         1 |
|  7 | John |   19 | secret |      1 | 1999-07-09 |         1 |
|  8 | John |   19 | secret |      2 | 1999-07-09 |         1 |
|  9 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 10 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 11 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 12 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 13 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 14 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 15 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 16 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 17 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 18 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 19 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 20 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
| 21 | John |   19 | secret |      1 | 1999-07-09 |         1 |
| 22 | John |   19 | secret |      1 | 1999-07-09 |         1 |
| 23 | Tony |   18 | male   |      1 | 1990-01-01 |         1 |
| 24 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 25 | John |   16 | secret |      1 | 1999-05-09 |         1 |
| 26 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 27 | Rose |   19 | female |      2 | 1999-07-09 |         1 |
+----+------+------+--------+--------+------------+-----------+
24 rows in set (0.00 sec)                                      
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

查询字段值长度为3的记录:

select * from students where name like '___';
  • 1

打印

+----+------+------+--------+--------+------------+-----------+
| id | NAME | age  | gender | cls_id | birth      | is_delete |
+----+------+------+--------+--------+------------+-----------+
|  1 | Tom  |   18 | male   |      1 | 1999-09-09 |         1 |
+----+------+------+--------+--------+------------+-----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

查询长度至少为4的数据值:

select * from students where name like '____%';
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 23 | Tony  |   18 | male   |      1 | 1990-01-01 |         1 |
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
25 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

(2) rlike 匹配正则表达式:

select * from students where name rlike '^J.*';
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
17 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
select * from students where name rlike '^J.*y$';
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

2.范围查询:

(1) in 表示在一个非连续的范围内

select * from students where age in (18,34);
  • 1

打印




    
+----+------+------+--------+--------+------------+-----------+
| id | NAME | age  | gender | cls_id | birth      | is_delete |
+----+------+------+--------+--------+------------+-----------+
|  1 | Tom  |   18 | male   |      1 | 1999-09-09 |         1 |
| 23 | Tony |   18 | male   |      1 | 1990-01-01 |         1 |
+----+------+------+--------+--------+------------+-----------+
2 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
select * from students where name in ('Tom','John');
  • 1

打印

+----+------+------+--------+--------+------------+-----------+
| id | NAME | age  | gender | cls_id | birth      | is_delete |
+----+------+------+--------+--------+------------+-----------+
|  1 | Tom  |   18 | male   |      1 | 1999-09-09 |         1 |
|  4 | John |   19 | secret |      1 | 1999-07-09 |         1 |
|  6 | John |   16 | secret |      1 | 1999-05-09 |         1 |
|  7 | John |   19 | secret |      1 | 1999-07-09 |         1 |
|  8 | John |   19 | secret |      2 | 1999-07-09 |         1 |
|  9 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 10 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 12 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 14 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 16 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 17 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 19 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 21 | John |   19 | secret |      1 | 1999-07-09 |         1 |
| 22 | John |   19 | secret |      1 | 1999-07-09 |         1 |
| 24 | John |   19 | secret |      2 | 1999-07-09 |         1 |
| 25 | John |   16 | secret |      1 | 1999-05-09 |         1 |
| 26 | John |   19 | secret |      2 | 1999-07-09 |         1 |
+----+------+------+--------+--------+------------+-----------+
17 rows in set (0.00 sec)                                      
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

(2) not in 表示不在一个非连续的范围内

select * from students where age not in (18,34);
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+  
| id | NAME  | age  | gender | cls_id | birth      | is_delete |  
+----+-------+------+--------+--------+------------+-----------+  
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 |  
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |  
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 |  
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |  
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 |  
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 |  
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 |  
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 |  
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 |  
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 |  
+----+-------+------+--------+--------+------------+-----------+  
24 rows in set (0.00 sec)                                         
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

(3) between … and … 表示在一个连续的范围内

select * from students where id between 3 and 8;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 |
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
5 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
select * from students where (id between 3 and 8) and gender = 1;
  • 1

打印




    
+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

(4) not between … and … 表示不在一个连续的范围内

select * from students where age not between 18 and 34;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+
| id | NAME  | age  | gender | cls_id | birth      | is_delete |
+----+-------+------+--------+--------+------------+-----------+
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 |
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 |
+----+-------+------+--------+--------+------------+-----------+
3 rows in set (0.00 sec)                                        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

等价于

select * from students where not age between 18 and 34;
  • 1

3.空判断:

判空 is null ,不能用“=”。

select * from students where name is null;
  • 1

打印

Empty set (0.00 sec)
  • 1
select * from students where name is not null;
  • 1

打印

+----+-------+------+--------+--------+------------+-----------+ 
| id | NAME  | age  | gender | cls_id | birth      | is_delete | 
+----+-------+------+--------+--------+------------+-----------+ 
|  1 | Tom   |   18 | male   |      1 | 1999-09-09 |         1 | 
|  2 | Jerry |   19 | male   |      1 | 1999-10-09 |         1 | 
|  3 | Nancy |   17 | male   |      2 | 1999-08-09 |         1 | 
|  4 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
|  6 | John  |   16 | secret |      1 | 1999-05-09 |         1 | 
|  7 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
|  8 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
|  9 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 10 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 11 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 12 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 13 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 14 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 15 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 16 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 17 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 18 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 19 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 20 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
| 21 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
| 22 | John  |   19 | secret |      1 | 1999-07-09 |         1 | 
| 23 | Tony  |   18 | male   |      1 | 1990-01-01 |         1 | 
| 24 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 25 | John  |   16 | secret |      1 | 1999-05-09 |         1 | 
| 26 | John  |   19 | secret |      2 | 1999-07-09 |         1 | 
| 27 | Rose  |   19 | female |      2 | 1999-07-09 |         1 | 
+----+-------+------+--------+--------+------------+-----------+ 
26 rows in set (0.00 sec)                                        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

三、聚合函数

1.count()总数

select count(*) from students;
  • 1

打印

+----------+
| count(*) |
+----------+
|       26 |
+----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
select count(*) as numofMale from students where gender = 1;
  • 1

打印

+-----------+          
| numofMale |          
+-----------+          
|         4 |          
+-----------+          
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
select count(*) as numofFemale from students where gender = 2;
  • 1

打印

+-------------+
| numofFemale |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

2.max()最大值

select max(age) from students;
  • 1

打印

+----------+
| max(age) |
+----------+
|       19 |
+----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
select max(id) from students where gender = 2;
  • 1

打印

+---------+
| max(id) |
+---------+
|      27 |
+---------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

3.min()最小值

select min(age) from students where is_delete = 1;
  • 1

打印

+----------+
| min(age) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

4.sum()求和

select sum(age) from students where gender = 1;
  • 1

打印

+----------+           
| sum(age) |           
+----------+           
|       72 |           
+----------+           
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

对于非数值型字段,如果全部数据均为字符型,求和为0,如果有部分为数字,会将所有数值进行相加。

select sum(name) from students;
  • 1

打印

+-----------+
| sum(name) |
+-----------+
|         0 |
+-----------+
1 row in set, 26 warnings (0.01 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

5.avg()平均值

默认保留4位小数,可以用 round(num,n) 可以使num保留n位小数。

select avg(age) from students where gender = 2 and is_delete = 1;
  • 1

打印

+----------+           
| avg(age) |           
+----------+           
|  19.0000 |           
+----------+           
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
select round(avg(age),2) from students where gender = 2 and is_delete = 1;
  • 1

打印

+-------------------+  
| round(avg(age),2) |  
+-------------------+  
|             19.00 |  
+-------------------+  
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

小编那么拼,赞一个再撤!
公众号二维码
大家也可以关注我的公众号: Python极客社区 ,在我的公众号里,经常会分享很多Python的文章,而且也分享了很多工具、学习资源等。另外回复“电子书”还可以获取十本我精心收集的Python电子书。

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/50741
 
15 次点击  
分享到微博