Mysql中SQL语句的强化使用

一、查询性别为男的人数有多少？

1.先看看表的情况

select * from doctor;mysql> select * from doctor;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  1 | 夏红 |  18 | 女   ||  1 | 夏红 |  19 | 女   ||  2 | 小强 |  12 | 男   ||  3 | 大枪 |  13 | 女   ||  4 | 太阳 |  14 | 男   |+----+------+-----+------+5 rows in set (0.00 sec)

2.查看男生的数量

mysql> select count(*) from doctor where sex ="男";+----------+| count(*) |+----------+|        2 |+----------+1 row in set (0.09 sec)select count(*) as "男生数量" from doctor where sex = "男";

二、group by

select sex,count(*) from doctor group by sex;运行：mysql> select sex,count(*) from doctor group by sex;+------+----------+| sex  | count(*) |+------+----------+| 女   |        3 || 男   |        2 |+------+----------+2 rows in set (0.00 sec)

三、having: 就是对group by分组之后再加上一些条件

mysql> select sex, count(*) from doctor group by sex having count(*) >2;+------+----------+| sex  | count(*) |+------+----------+| 女   |        3 |+------+----------+1 row in set (0.00 sec)

别名的理解

mysql> select sex, count(*) as one from doctor group by sex having one>2;+------+-----+| sex  | one |+------+-----+| 女   |   3 |+------+-----+1 row in set (0.04 sec)

四、limit 条件

mysql> select * from doctor;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  1 | 夏红 |  18 | 女   ||  1 | 夏红 |  19 | 女   ||  2 | 小强 |  12 | 男   ||  3 | 大枪 |  13 | 女   ||  4 | 太阳 |  14 | 男   |+----+------+-----+------+5 rows in set (0.00 sec)mysql> select * from doctor limit 1;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  1 | 夏红 |  18 | 女   |+----+------+-----+------+1 row in set (0.00 sec)mysql> select * from doctor limit 2,2;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  2 | 小强 |  12 | 男   ||  3 | 大枪 |  13 | 女   |+----+------+-----+------+2 rows in set (0.00 sec)mysql> select * from doctor limit 4,1;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  4 | 太阳 |  14 | 男   |+----+------+-----+------+1 row in set (0.00 sec)mysql> select * from doctor limit 4;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  1 | 夏红 |  18 | 女   ||  1 | 夏红 |  19 | 女   ||  2 | 小强 |  12 | 男   ||  3 | 大枪 |  13 | 女   |+----+------+-----+------+4 rows in set (0.00 sec)mysql> select * from doctor order by id desc limit 1;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  4 | 太阳 |  14 | 男   |+----+------+-----+------+1 row in set (0.00 sec)

五、等于不等于

等于： =，<=>
不等于：!= , <>

mysql> select 1=1;+-----+| 1=1 |+-----+|   1 |+-----+1 row in set (0.00 sec)mysql> select 1<=>1;+-------+| 1<=>1 |+-------+|     1 |+-------+1 row in set (0.01 sec)mysql> select 1!=1;+------+| 1!=1 |+------+|    0 |+------+1 row in set (0.00 sec)mysql> select 1<>1;+------+| 1<>1 |+------+|    0 |+------+1 row in set (0.00 sec)

六、null

mysql> select * from doctor;+----+----------+-----+------+| id | name     | age | sex  |+----+----------+-----+------+|  1 | 夏红     |  18 | 女   ||  1 | 夏红     |  19 | 女   ||  2 | 小强     |  12 | 男   ||  3 | 大枪     |  13 | 女   ||  4 | 太阳     |  14 | 男   ||  8 | 增加一个 |   0 | NULL |+----+----------+-----+------+6 rows in set (0.00 sec)mysql> select * from doctor where sex is null;+----+----------+-----+------+| id | name     | age | sex  |+----+----------+-----+------+|  8 | 增加一个 |   0 | NULL |+----+----------+-----+------+1 row in set (0.00 sec)mysql> select * from doctor where sex is not null;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  1 | 夏红 |  18 | 女   ||  1 | 夏红 |  19 | 女   ||  2 | 小强 |  12 | 男   ||  3 | 大枪 |  13 | 女   ||  4 | 太阳 |  14 | 男   |+----+------+-----+------+5 rows in set (0.00 sec)

七、between and

mysql> select * from doctor where id between 2 and 3;+----+------+-----+------+| id | name | age | sex  |+----+------+-----+------+|  2 | 小强 |  12 | 男   ||  3 | 大枪 |  13 | 女   |+----+------+-----+------+2 rows in set (0.00 sec)