DQL:对表进行查询。
基本查询
- 基本查询:
select 字段名 from 表名;
- 去除重复,只显示不同的数据(当查询两列或多列时,需满足两列或多列完全相同,才可以去除重复):
select distinct 字段名 from 表名;
mysql> select * from stu;
+--------+------+-------+
| name | age | score |
+--------+------+-------+
| 鐜嬩簲 | 19 | 90.80 |
| li | 6 | 98.00 |
| ws | 19 | 90.80 |
| hu | 6 | 98.00 |
| uytx | 19 | 9.00 |
| dh | 6 | 90.80 |
| rg | 19 | 90.80 |
| zfc | 6 | 8.00 |
+--------+------+-------+
8 rows in set (0.00 sec)
mysql> select distinct age,score from stu;
+------+-------+
| age | score |
+------+-------+
| 19 | 90.80 |
| 6 | 98.00 |
| 19 | 9.00 |
| 6 | 90.80 |
| 6 | 8.00 |
+------+-------+
5 rows in set (0.00 sec)
- 数值类型下,可以相加求和(也可以使用其他的四则运算;如果某一字段为
null
,可以只用ifnull
函数进行判断;as
可以用来给列起个新名字):select 字段名1,字段名2,列名1+ifnull(字段名,0) as 总分 from 表名;
mysql> select age,score,age+ifnull(score,0) as luan from stu;
+------+-------+--------+
| age | score | luan |
+------+-------+--------+
| 19 | 90.80 | 109.80 |
| 6 | 98.00 | 104.00 |
| 19 | 90.80 | 109.80 |
| 6 | 98.00 | 104.00 |
| 19 | 9.00 | 28.00 |
| 6 | 90.80 | 96.80 |
| 19 | 90.80 | 109.80 |
| 6 | 8.00 | 14.00 |
+------+-------+--------+
8 rows in set (0.00 sec)
- 在范围内条件查询或者是并列条件(< > = != >= <= 同理):
select 字段名1 from 表名 where 字段1>范围1 and 字段1<范围2 ;
也可以使用:select 字段名1 from 表名 where 字段1 between 范围1 and 范围2;
mysql> select name,score from stu where score>8 and score < 95;
+--------+-------+
| name | score |
+--------+-------+
| 鐜嬩簲 | 90.80 |
| ws | 90.80 |
| uytx | 9.00 |
| dh | 90.80 |
| rg | 90.80 |
+--------+-------+
5 rows in set (0.00 sec)
mysql> select name,score from stu where score between 8 and 95;
+--------+-------+
| name | score |
+--------+-------+
| 鐜嬩簲 | 90.80 |
| ws | 90.80 |
| uytx | 9.00 |
| dh | 90.80 |
| rg | 90.80 |
| zfc | 8.00 |
+--------+-------+
6 rows in set (0.03 sec)
- 并列条件查询(< > = != >= <= 同理):
select 字段名 from 表名where 字段名1=条件1 or 字段名1=条件2 or 字段名1 =条件3
也可以使用:select 字段名1 from 表名 where 字段名1 in (条件1,条件2,条件3);
,相同的字段,可以使用in
mysql> select name,age,score from stu where age=19 or age =8;
+--------+------+-------+
| name | age | score |
+--------+------+-------+
| 鐜嬩簲 | 19 | 90.80 |
| ws | 19 | 90.80 |
| uytx | 19 | 9.00 |
| rg | 19 | 90.80 |
+--------+------+-------+
4 rows in set (0.00 sec)
mysql> select name,age,score from stu where age in (19,8);
+--------+------+-------+
| name | age | score |
+--------+------+-------+
| 鐜嬩簲 | 19 | 90.80 |
| ws | 19 | 90.80 |
| uytx | 19 | 9.00 |
| rg | 19 | 90.80 |
+--------+------+-------+
4 rows in set (0.00 sec)
- 判断是否为空时,不可以使用
= !=
之类,只可以使用is null
或者is not null
例如:select name,age from stu where name is not null;
like
模糊查询:-
单个下划线表示一个字符,%
表示一个或多个字符;
例如:where name like '_风':表示名字为两个字,姓名第二个字为风的查询;where name like '%风%':表示名字中含有风字的姓名;
复杂查询
(就是我认为难一点的查询,没啥好区分的)
- 升序或降序排列:
order by 排序字段1 排序条件1,排序字段2 排序字段2;
。在条件1出现相同情况下,再使用排序条件2。
ASC 升序;DESC 降序
。例如:select name from stu order by age ASC; 按照年龄从小到大排序
mysql> select name,age,score from stu order by age asc;
+--------+------+-------+
| name | age | score |
+--------+------+-------+
| li | 6 | 98.00 |
| hu | 6 | 98.00 |
| dh | 6 | 90.80 |
| zfc | 6 | 8.00 |
| 鐜嬩簲 | 19 | 90.80 |
| ws | 19 | 90.80 |
| uytx | 19 | 9.00 |
| rg | 19 | 90.80 |
+--------+------+-------+
8 rows in set (0.00 sec)
mysql> select name,age,score from stu order by age asc,score asc;
+--------+------+-------+
| name | age | score |
+--------+------+-------+
| zfc | 6 | 8.00 |
| dh | 6 | 90.80 |
| li | 6 | 98.00 |
| hu | 6 | 98.00 |
| uytx | 19 | 9.00 |
| 鐜嬩簲 | 19 | 90.80 |
| ws | 19 | 90.80 |
| rg | 19 | 90.80 |
+--------+------+-------+
8 rows in set (0.00 sec)
- 聚合函数:
count(字符名):select count(name) from stu;
计算包含name的行的个数;
max(字符名):select max(score) from stu;
计算stu表中score的最大值;
min(字符名):select min(score) from stu;
计算stu表中score的最小值;
sum(字符名):select sum(score) from stu;
计算stu表中score值的和;
avg(字符名):select avg(score) from stu;
计算stu表中score的平均值;
聚合函数自动排除null
值,可以使用ifnull(字段名,0)
来进行解决。
mysql> select count(age) as ageNum from stu where age>10;
+--------+
| ageNum |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
- 分组查询:
select 字段名1 count(name) from stu group by 字段名1;
。
select 1 from 表名 where 2 group by 3 having 4;
1:1处可以使用分组字段、聚合函数,除分组字段和聚合函数,其他的不可以使用。
2:2处可以使用上面的基础查询里面的东西,不可以使用聚合函数。
3:3处可以使用字段名
4:4处可以使用上面的基础查询里面的东西,还可以使用聚合函数。
where 后面的东西是对分组之前进行限制,不满足条件的,不参与分组。
having后面的东西是对分组以后进行限制,不满足条件的,不会被1处的查询条件查询除了
where不可以跟聚合函数,having可以。
例如:根据年龄进行分组,分别查询相同年龄的人数,要求得分不低于90的参与分组,分组后,人数要大于2;
select age,count(name) as 个数 from stu where score > 90 group by age having count(name) > 2;
显示结果应该是相同年龄,成绩不低于90的,人数大于2的人数。
mysql> select age,count(name) from stu where score > 90 group by age having count(name) > 2;
+------+-------------+
| age | count(name) |
+------+-------------+
| 6 | 3 |
| 19 | 3 |
+------+-------------+
2 rows in set (0.00 sec)
- 分页查询:
select 字符名 from stu limit 开始索引,每页查询显示个数;
开始索引=(想要跳转的页码-1)*每页查询显示个数;
mysql> select * from stu limit 0,3;
+--------+------+-------+
| name | age | score |
+--------+------+-------+
| 鐜嬩簲 | 19 | 90.80 |
| li | 6 | 98.00 |
| ws | 19 | 90.80 |
+--------+------+-------+
3 rows in set (0.00 sec)