基本查询

说明

使用

-- 使用SELECT语句查询一个数据表(查询所有)
select * from passenger_user;
-- 查询表中的一列或多列(查询name和city)
select name,city from passenger_user;
-- 从一个或多个表中获取数据(passenger_user表的name和log表的level)
select passenger_user.name,log.level from passenger_user,log;

单表查询

执行顺序

1. from
2. join
3. on
4. where
5. group by(开始使用select中的别名,后面的语句中都可以使用)
6. avg,sum...
7. having
8. select
9. distinc
10. order by

查询所有字段

-- 查询passenger_user所有字段
select * from passenger_user;

查询指定字段

-- 查询passenger_user表的name,city字段
select name,city from passenger_user;

查询指定数据

-- 查询passenger_user表name字段是a的值
select * from passenger_user where name='a';

查询指定范围数据

-- 查询name在(a,b)中的数据
select * from passenger_user where name in ('a','b');
-- 查询name不在(a,b)中的数据
select * from passenger_user where name not in ('a','b');

查询指定区间数据

-- 查询city在(1~4)之间的数据
select * from passenger_user where city between 1 and 4;
-- 查询city不在(1~4)之间的数据
select * from passenger_user where city not between 1 and 3;

模糊查询

-- 查询name包含a的数据
select * from passenger_user where name like '%a%';
-- 查询name以a开头的数据
select * from passenger_user where name like '%a';
-- 查询name以a结尾的数据
select * from passenger_user where name like 'a%';
-- 查询name不包含a的数据
select * from passenger_user where name not like '%a%';

查询空值

-- 查询city为空的数据
select * from passenger_user where city is null
-- 查询city为非空的数据
select * from passenger_user where city is not null;

多条件AND

-- 查询姓名包含a 并且id 大于1的数据
select * from passenger_user where name like '%a%' and id > 1;

多条件OR

-- 查询id=1或id=2的数据
select * from passenger_user where id = 1 or id = 2;

去重查询

select name from passenger_user where name = 'angel';
+---------+
| name    |
+---------+
| qvbilam |
| qvbilam |
+---------+
-- 去重查询
select distinct name from passenger_user where name = 'angel';
+---------+
| name    |
+---------+
| qvbilam |
+---------+

排序

-- 通过id倒叙排序
select * from passenger_user order by id desc;
-- 通过id正序排序
select * from passenger_user order by id asc;

分组

-- 用关键字GROUP BY分组查询(在mysql5中查询groupby以外的字段会报错)
select sex from passenger_user group by sex;
select sex,group_concat(name) from passenger_user group by sex;
-- 分组条件having(如多条件,用 AND/OR/NOT组合)
select city from passenger_user group by city having city>2;
-- 多字段分组
select sex,name from passenger_user group by name,sex;

限制数量

-- 查询2条结果
select * from passenger_user limit 2;
-- 查询从第二条开始的2条数据
select * from passenger_user limit 1,2;

聚合查询

统计数量

-- count()函数
select count(id) from passenger_user;

统计综合

-- sum()函数
select sum(id) from passenger_user;

平均数

-- avg()函数
select avg(id) from passenger_user;

最大值

-- max()函数
select max(id) from passenger_user;

最小值

-- min()函数
select min(id) from passenger_user;

连接查询

数据准备

-- 创建学生表
create table student(
    id int primary key auto_increment,
    name varchar(20) not null,
    skill varchar(20) null
);
-- 插入学生数据
insert into student (name,skill) 
values ('二滑大魔王','php'),
('二滑大天使','mysql'),
('二滑大可爱','redis'),
('二滑小魔王','mongodb'),
('二滑小天使','python'),
('二滑小可爱','html');
insert into student (name,skill) 
values ('二滑大坏蛋','css'),
('二滑小坏蛋','javascript');
-- 创建成绩表
create table result(
    id int primary key auto_increment,
    sid int(10) not null,
    mark int(10) not null
);
-- 插入成绩数据
insert into result (sid,mark) values (1,90),(2,88),
(3,78),
(4,42),
(5,22),
(6,61);
insert into result (sid,mark) values (11,90),
(12,88);

内链接查询

select student.name,result.mark from result,student where result.sid = student.id;
+-----------------+------+
| name            | mark |
+-----------------+------+
| 二滑大魔王      |   90 |
| 二滑大天使      |   88 |
| 二滑大可爱      |   78 |
| 二滑小魔王      |   42 |
| 二滑小天使      |   22 |
| 二滑小可爱      |   61 |
+-----------------+------+

外链接查询

-- 左外链接
select student.name,result.mark from student left join result on result.sid = student.id;
+-----------------+------+
| name            | mark |
+-----------------+------+
| 二滑大魔王      |   90 |
| 二滑大天使      |   88 |
| 二滑大可爱      |   78 |
| 二滑小魔王      |   42 |
| 二滑小天使      |   22 |
| 二滑小可爱      |   61 |
| 二滑大坏蛋      | NULL |
| 二滑小坏蛋      | NULL |
+-----------------+------+
-- 右外连接
select student.*,result.* from student right join result on result.sid = student.id;
+------+-----------------+---------+----+-----+------+
| id   | name            | skill   | id | sid | mark |
+------+-----------------+---------+----+-----+------+
|    1 | 二滑大魔王      | php     |  1 |   1 |   90 |
|    2 | 二滑大天使      | mysql   |  2 |   2 |   88 |
|    3 | 二滑大可爱      | redis   |  3 |   3 |   78 |
|    4 | 二滑小魔王      | mongodb |  4 |   4 |   42 |
|    5 | 二滑小天使      | python  |  5 |   5 |   22 |
|    6 | 二滑小可爱      | html    |  6 |   6 |   61 |
| NULL | NULL            | NULL    |  7 |  11 |   90 |
| NULL | NULL            | NULL    |  8 |  12 |   88 |
+------+-----------------+---------+----+-----+------+

总结

1. 内连接:显示所有的数据复合条件的数据
2. 左连接:显示以左表为主的数据,链表没有的为空
3. 右连接:显示已右表为主的数据,链表没有的为空

子查询

关键字IN

-- 带关键字IN的子查询(可以是多个结果)
select * from student where id in (select sid from result);

比较运算

-- 带比较运算的子查询(注意子查询的结果必须是一个值,不可是一个集合)
select * from student where id >= (select sid from result where mark < 30);

关键字EXISTS

-- 带关键字EXISTS的子查询(子查询的结果只是true和false,如果true在查询)
select * from student where exists (select sid from result where mark>=90);

关键字ANY

-- 带关键字ANY的子查询(只满足任意一条件,<any表示小于所有值)
select * from student where id > any(select mark from result order by id desc);

关键字ALL

-- 带关键字ALL的子查询(满足所有的条件)
select * from student where id > all(select mark from result where mark<10);

合并查询

  合并查询结果是将多个SELECT语句的查询结果合并到一起.合并查询使用关键字UNION和UNION ALL.UNION是将所有的查询结果合并到一起,然后去除相同记录;而UNION ALL只是简单的将结果合并到一起.

-- UNION
select id from student union select id from result;
-- UNION ALL
select id from student union all select id from result;