MySQL的查询数据

查询数据是指从数据库中获取所需的数据。查询数据是数据库中最常用,也是最重要的操作。用户可以根据自己的需求,使用不同的查询方式。


基本查询语句

select属性列表
from 表名和视图列表
where 条件表达式1
group by 属性名1 [having 条件表达式2]
order by 属性名2 [asc|desc]
\\显示员工表内的全部数据
mysql> select * from employee;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
|   3 | 1004 | wangwu   |   29 |   1 | tianjin  |
|   4 | 1005 | zhaoliu  |   35 |   1 | tieling  |
|   5 | 1007 | maba     |   18 |   1 | luoyang  |
+-----+------+----------+------+-----+----------+
\\显示员工中小于25岁的人,并按num序号从大到小排列
mysql> select * from employee where age < 25 order by num desc;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   5 | 1007 | maba     |   18 |   1 | luoyang  |
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
+-----+------+----------+------+-----+----------+
2 rows in set (0.00 sec)
\\查询指定字段,改变显示顺序,并按年龄排序
mysql> select age,name from employee order by age;
+------+----------+
| age  | name     |
+------+----------+
|   18 | maba     |
|   20 | zhangsan |
|   24 | lisi     |
|   29 | wangwu   |
|   35 | zhaoliu  |
+------+----------+
5 rows in set (0.00 sec)
\\查询指定记录,员工号大于等于1005的人员
mysql> select * from employee where d_id >= 1005;
+-----+------+---------+------+-----+----------+
| num | d_id | name    | age  | sex | homeaddr |
+-----+------+---------+------+-----+----------+
|   4 | 1005 | zhaoliu |   35 |   1 | tieling  |
|   5 | 1007 | maba    |   18 |   1 | luoyang  |
+-----+------+---------+------+-----+----------+
2 rows in set (0.00 sec)
\\带IN关键字的查询,将集合中的记录显示出来
mysql> select * from employee where num in(3,5);
+-----+------+--------+------+-----+----------+
| num | d_id | name   | age  | sex | homeaddr |
+-----+------+--------+------+-----+----------+
|   3 | 1004 | wangwu |   29 |   1 | tianjin  |
|   5 | 1007 | maba   |   18 |   1 | luoyang  |
+-----+------+--------+------+-----+----------+
\\带between and的范围查询
mysql> select age,name from employee where age between 15 and 25 order by age;
+------+----------+
| age  | name     |
+------+----------+
|   18 | maba     |
|   20 | zhangsan |
|   24 | lisi     |
+------+----------+
\\not是可选参数
mysql> select age,name from employee where age not between 15 and 25 order by age;
+------+---------+
| age  | name    |
+------+---------+
|   29 | wangwu  |
|   35 | zhaoliu |
+------+---------+
\\使用like字符匹配查询所有名字中带有a的人员
mysql> select num,name from employee where name like '%a%';
+-----+----------+
| num | name     |
+-----+----------+
|   1 | zhangsan |
|   3 | wangwu   |
|   4 | zhaoliu  |
|   5 | maba     |
+-----+----------+

\\可以使用is null来查询空值
\\使用and进行多条件查询,年龄小于25,名字带a的男性
mysql> select name,age,sex from employee where age between 15 and 25
    -> and sex = 1
    -> and name like '%a%';
+----------+------+-----+
| name     | age  | sex |
+----------+------+-----+
| zhangsan |   20 |   1 |
| maba     |   18 |   1 |
+----------+------+-----+
\\使用distinct来除重
mysql> select sex from employee;
+-----+
| sex |
+-----+
|   1 |
|   0 |
|   1 |
|   1 |
|   1 |
+-----+
mysql> select distinct sex from employee;
+-----+
| sex |
+-----+
|   1 |
|   0 |
+-----+
\\按组进行查询,并按组显示
mysql> select * from employee group by sex;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
+-----+------+----------+------+-----+----------+
\\group by与group_contcat()函数一起使用,分组显示全部内容
mysql> select sex,group_concat(name) from employee group by sex;
+-----+------------------------------+
| sex | group_concat(name)           |
+-----+------------------------------+
|   0 | lisi                         |
|   1 | zhangsan,wangwu,zhaoliu,maba |
+-----+------------------------------+
\\group by与集合函数一起使用
mysql> select sex,count(sex) from employee group by sex;
+-----+------------+
| sex | count(sex) |
+-----+------------+
|   0 |          1 |
|   1 |          4 |
+-----+------------+
\\group by 与having一起使用
mysql> select sex,count(sex) from employee group by sex having count(sex)>3;
+-----+------------+
| sex | count(sex) |
+-----+------------+
|   1 |          4 |
+-----+------------+
\\where作用于表或者视图,是表和试图的查询条件。
\\having作用于分组后的记录,用于选择满足条件的组。
\\多个字段分组,第一个组相同,则按第二个组排序,同样1001的按年龄排
mysql> select d_id,group_concat(name),age from employee group by d_id,age;
+------+--------------------+------+
| d_id | group_concat(name) | age  |
+------+--------------------+------+
| 1001 | maba               |   18 |
| 1001 | zhangsan           |   20 |
| 1002 | lisi               |   24 |
| 1004 | wangwu             |   29 |
| 1005 | zhaoliu            |   35 |
+------+--------------------+------+
\\group by与with rollup进行汇总
mysql> select sex,count(sex) from employee group by sex with rollup;
+-----+------------+
| sex | count(sex) |
+-----+------------+
|   0 |          1 |
|   1 |          4 |
| NULL |          5 |
+-----+------------+
\\使用limit限制查询结果的数量
mysql> select * from employee limit 2;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
+-----+------+----------+------+-----+----------+
2 rows in set (0.00 sec)
mysql> select * from employee limit 4;
+-----+------+----------+------+-----+----------+
| num | d_id | name     | age  | sex | homeaddr |
+-----+------+----------+------+-----+----------+
|   1 | 1001 | zhangsan |   20 |   1 | beijing  |
|   2 | 1002 | lisi     |   24 |   0 | taiyuan  |
|   3 | 1004 | wangwu   |   29 |   1 | tianjin  |
|   4 | 1005 | zhaoliu  |   35 |   1 | tieling  |
+-----+------+----------+------+-----+----------+
4 rows in set (0.00 sec)
\\指定初始位置
mysql> select * from employee limit 2,4;
+-----+------+---------+------+-----+----------+
| num | d_id | name    | age  | sex | homeaddr |
+-----+------+---------+------+-----+----------+
|   3 | 1004 | wangwu  |   29 |   1 | tianjin  |
|   4 | 1005 | zhaoliu |   35 |   1 | tieling  |
|   5 | 1001 | maba    |   18 |   1 | luoyang  |
+-----+------+---------+------+-----+----------+
3 rows in set (0.00 sec)

tips:

在MySQL5.7以后的group by中会直接报1055的错误。
原因是5.7后的版本默认开启only_full_group_by属性,需要在group by查询中使用any_value()函数。
解决的方法一个是改查询语句,一个是改配置文件。
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]字段新增一行配置
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
然后,
sudo service mysql restart


使用集合函数查询

\\count()函数,统计函数
mysql> select count(*) from employee;
\\sum()函数,求和函数
mysql> select d_id,sum(age) from employee;
\\avg()函数,求平均值函数
mysql> select avg(age) from employee group by sex;
\\max()函数,求最大值函数
mysql> select max(age) from employee;
\\min()函数,求最小值函数
mysql> select min(age) from employee;

连接查询

\\内连接查询
mysql> select * from employee;
+-----+------+--------+-----+-----+----------------------------------+
| num | d_id | name   | age | sex | homeaddr                         |
+-----+------+--------+-----+-----+----------------------------------+
|   1 | 1001 | 张三   |  26 | 男  | 北京市海淀区学院路16号           |
|   2 | 1001 | 李四   |  24 | 女  | 北京市昌平区北七家镇             |
|   3 | 1004 | Aric   |  18 | 男  | England                          |
+-----+------+--------+-----+-----+----------------------------------+
3 rows in set (0.00 sec)
mysql> select * from department;
+------+-----------+--------------+---------------------+
| d_id | d_name    | function     | address             |
+------+-----------+--------------+---------------------+
| 1001 | 研发部    | 产品研发     | 3号楼5层            |
| 1002 | 生产部    | 产品制造     | 5号楼1层            |
| 1003 | 销售部    | 产品销售     | 1号楼销售大厅       |
+------+-----------+--------------+---------------------+
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id=department.d_id;
+-----+--------+------+-----+-----+-----------+--------------+
| num | name   | d_id | age | sex | d_name    | function     |
+-----+--------+------+-----+-----+-----------+--------------+
|   1 | 张三   | 1001 |  26 | 男  | 研发部    | 产品研发     |
|   2 | 李四   | 1001 |  24 | 女  | 研发部    | 产品研发     |
|   4 | 王五   | 1003 |  25 | 女  | 销售部    | 产品销售     |
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id=department.d_id;
+-----+--------+------+-----+-----+-----------+--------------+
| num | name   | d_id | age | sex | d_name    | function     |
+-----+--------+------+-----+-----+-----------+--------------+
|   1 | 张三   | 1001 |  26 | 男  | 研发部    | 产品研发     |
|   2 | 李四   | 1001 |  24 | 女  | 研发部    | 产品研发     |
|   4 | 王五   | 1003 |  25 | 女  | 销售部    | 产品销售     |
+-----+--------+------+-----+-----+-----------+--------------+
\\左连接查询 left join 表名 on 条件
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee left join department  on employee.d_id=department.d_id;
\\右连接查询 right join 表名 on 条件
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee right join department  on employee.d_id=department.d_id;

子查询

\\带in关键字的子查询
mysql> select * from employee where d_id in(select d_id from department);
+-----+------+--------+-----+-----+----------------------------------+
| num | d_id | name   | age | sex | homeaddr                         |
+-----+------+--------+-----+-----+----------------------------------+
|   1 | 1001 | 张三   |  26 | 男  | 北京市海淀区学院路16号           |
|   2 | 1001 | 李四   |  24 | 女  | 北京市昌平区北七家镇             |
|   4 | 1003 | 王五   |  25 | 女  | 北京市朝阳区双桥                 |
+-----+------+--------+-----+-----+----------------------------------+
\\not查询反向
mysql> select * from employee where d_id not in(select d_id from department);
+-----+------+------+-----+-----+----------+
| num | d_id | name | age | sex | homeaddr |
+-----+------+------+-----+-----+----------+
|   3 | 1004 | Aric |  18 | 男  | England  |
+-----+------+------+-----+-----+----------+