首页 > 代码库 > mysql语句2-单表查询
mysql语句2-单表查询
mysql 查询以及多表查询
以下所有表格样例都采用下边这个表格
mysql> select * from benet;
+------+------+----------+
| id | name | nianling |
+------+------+----------+
| 3 | a | 16 |
| 4 | b | 17 |
| 3 | a | 16 |
| 5 | b | 15 |
| 3 | b | 15 |
| 5 | b | 15 |
| 3 | b | 15 |
+------+------+----------+
1. 查询所有的内容。
select * from 表名称;
mysql> select * from benet;
2. 查询某个字段的内容
select 字段名1，字段名2，... from 表名称
mysql> select id from benet;
3.根据条件查询
例子1：
mysql> select * from benet where id=5;
+------+------+----------+
| id | name | nianling |
+------+------+----------+
| 5 | b | 15 |
| 5 | b | 15 |
+------+------+----------+
例子2 查询名字等于a的所有字段
mysql> select * from benet where name=‘a‘; 注意：当条件内容是字符的时候，需要用引号引起来，但是数字不用。
+------+------+----------+
| id | name | nianling |
+------+------+----------+
| 3 | a | 16 |
| 3 | a | 16 |
+------+------+----------+
例子3：名字为b并且id大于3的内容
在多条件查询中 && 符号可以用and代替
mysql> select * from benet where name=‘b‘ && id > 3;
+------+------+----------+
| id | name | nianling |
+------+------+----------+
| 4 | b | 17 |
| 5 | b | 15 |
| 5 | b | 15 |
+------+------+----------+
例子4：名字为b并且id大于3并且年龄大于15
mysql> select * from benet where name=‘b‘ && id > 3 && nianling > 15;
+------+------+----------+
| id | name | nianling |
+------+------+----------+
| 4 | b | 17 |
+------+------+----------+
4.查询特定的参数
（1）.disdinct参数：重复的结果只显示一次
例子：
mysql> select * from benet;
+------+------+----------+
| id | name | nianling |
+------+------+----------+
| 3 | a | 16 |
| 4 | b | 17 |
| 3 | a | 16 |
| 5 | b | 15 |
| 3 | b | 15 |
| 5 | b | 15 |
| 3 | b | 15 |
+------+------+----------+
我们查询id字段
mysql> select id from benet;
+------+
| id |
+------+
| 3 |
| 4 |
| 3 |
| 5 |
| 3 |
| 5 |
| 3 |
+------+
查询后的结果有些是重读的，我们可以让这些重复的结果只显示一次
mysql> select distinct id from benet;
+------+
| id |
+------+
| 3 |
| 4 |
| 5 |
+------+
（2）. 在某个范围之间
格式：between....and....
例子：
use benet
create table benet (id int(3),name char(10),nianling int(3));
insert into benet values (1,‘a‘,10), (2,‘a‘,10), (3,‘a‘,10), (3,‘b‘,12),( 3,‘b‘,14), (3,‘b‘,15), (3,‘b‘,16) ,(3,‘c‘,18);
例子：
mysql> select * from benet where nianling between 12 and 16;
+------+------+----------+
| id | name | nianling |
+------+------+----------+
| 3 | b | 12 |
| 3 | b | 14 |
| 3 | b | 15 |
| 3 | b | 16 |
+------+------+----------+
（3）like ‘关键字‘ 根据关键字查找
% ：表示任意长度的任意字符
_ ： 表示单个字符
mysql> create table meinv (name char(30),nianling int(3));
insert into meinv values (‘yangmi‘,25);
insert into meinv values (‘gaoyuanyuan‘,25);
insert into meinv values (‘yangziqiong‘,28);
查找y开头的名字
mysql> select * from meinv where name like ‘y%‘;
+-------------+----------+
| name | nianling |
+-------------+----------+
| yangmi | 25 |
| yangziqiong | 28 |
+-------------+----------+
查找包含yuan的名字
mysql> select * from meinv where name like ‘%yuan%‘;
+-------------+----------+
| name | nianling |
+-------------+----------+
| gaoyuanyuan | 25 |
+-------------+----------+
查询某个字段为空值的数据。
mysql> create table abc (name char(20),nianling int(3));
mysql> insert into abc values (‘a‘,10),(‘b‘,3),(‘c‘,null);
mysql> select * from abc;
+------+----------+
| name | nianling |
+------+----------+
| a | 10 |
| b | 3 |
| c | NULL |
+------+----------+
mysql> select * from abc where nianling is null;
查询年龄字段中为空的数据。
mysql> select * from abc where nianling is not null;
查询年龄字段中不为空的数据。
查询排序
order by 排序的一依据字段
例子1:根据nianling字段内容按照升序排列。
select * from abc order by nianling;
+------+----------+
| name | nianling |
+------+----------+
| c | NULL |
| b | 3 |
| a | 10 |
+------+----------+
例子2：根据nianling字段内容按照降序排列。
mysql> select * from abc order by nianling desc;
+------+----------+
| name | nianling |
+------+----------+
| a | 10 |
| b | 3 |
| c | NULL |
+------+----------+
显示查询后的部分结果limit
例子1：
mysql> select * from abc limit 2;
只显示结果的前两行
+------+----------+
| name | nianling |
+------+----------+
| a | 10 |
| b | 3 |
+------+----------+
聚合计算
依然采用上面的表作为例子
mysql> select sum(nianling) from abc; 求nianling字段所有数据的和
+---------------+
| sum(nianling) |
+---------------+
| 13 |
+---------------+
mysql> select max(nianling) from abc; 求nianling字段所有数据最大值
mysql> select min(nianling) from abc; 求nianling字段所有数据最少值
mysql> select avg(nianling) from abc; 求nianling字段所有数据的平均值
+---------------+
| avg(nianling) |
+---------------+
| 6.5000 |
+---------------+
mysql语句2-单表查询