1、where 语句
- select * from emp where id>3;
- select * from emp where id=3;
- select * from emp where id !=3;
- select * from emp where name='小明';
- select * from emp where sex='F' and age>25;
- select * from emp where sex='F' or age>25;
- select * from emp where not age >24;
- select * from emp where sex is not null;
- select * from emp where id in(1,3,5);
例1:
mysql> select * from emp where id in(1,3,5);
+----+--------+----------+------+------+
| id | dep_id | name | age | sex |
+----+--------+----------+------+------+
| 3 | 1 | zhangsan | 20 | M |
| 5 | 3 | lisi | 35 | F |
+----+--------+----------+------+------+
例2:
mysql> select * from emp where sex='F' and age>25;
+----+--------+--------+------+------+
| id | dep_id | name | age | sex |
+----+--------+--------+------+------+
| 5 | 3 | lisi | 35 | F |
| 11 | 3 | 张华 | 27 | F |
+----+--------+--------+------+------+
2、分组
- select sex,count(*) from emp group by sex;
- select sex,name,avg(age) from emp group by sex;
- select name,sex,sum(age)as total_age from emp group by sex;
例1:
mysql> select sex,count(*) from emp group by sex;
+------+----------+
| sex | count(*) |
+------+----------+
| F | 6 |
| M | 4 |
+------+----------+
例2:
mysql> select sex,name,avg(age) from emp group by sex;
+------+----------+----------+
| sex | name | avg(age) |
+------+----------+----------+
| F | wangwu | 24.0000 |
| M | zhangsan | 28.2500 |
+------+----------+----------+
例3:
mysql> select name,sex,sum(age)as total_age from emp group by sex;
+----------+------+-----------+
| name | sex | total_age |
+----------+------+-----------+
| wangwu | F | 144 |
| zhangsan | M | 113 |
+----------+------+-----------+
3、排序
- select * from emp order by age;//未指明,默认是升序
- select * from emp order by age desc;
- select * from emp order by sex asc,age desc;
例1:
mysql> select * from emp order by age;
+----+--------+----------+------+------+
| id | dep_id | name | age | sex |
+----+--------+----------+------+------+
| 2 | 3 | wangwu | 18 | F |
| 4 | 2 | kk | 18 | F |
| 3 | 1 | zhangsan | 20 | M |
| 7 | 2 | 李四 | 23 | F |
| 10 | 1 | 王五 | 23 | F |
| 8 | 3 | 小明 | 25 | M |
| 9 | 2 | 张明 | 26 | M |
| 11 | 3 | 张华 | 27 | F |
| 5 | 3 | lisi | 35 | F |
| 6 | 2 | lili | 42 | M |
+----+--------+----------+------+------+
例2:
mysql> select * from emp order by age desc;
+----+--------+----------+------+------+
| id | dep_id | name | age | sex |
+----+--------+----------+------+------+
| 6 | 2 | lili | 42 | M |
| 5 | 3 | lisi | 35 | F |
| 11 | 3 | 张华 | 27 | F |
| 9 | 2 | 张明 | 26 | M |
| 8 | 3 | 小明 | 25 | M |
| 7 | 2 | 李四 | 23 | F |
| 10 | 1 | 王五 | 23 | F |
| 3 | 1 | zhangsan | 20 | M |
| 2 | 3 | wangwu | 18 | F |
| 4 | 2 | kk | 18 | F |
+----+--------+----------+------+------+
例3:
mysql> select * from emp order by sex asc,age desc;
+----+--------+----------+------+------+
| id | dep_id | name | age | sex |
+----+--------+----------+------+------+
| 5 | 3 | lisi | 35 | F |
| 11 | 3 | 张华 | 27 | F |
| 7 | 2 | 李四 | 23 | F |
| 10 | 1 | 王五 | 23 | F |
| 2 | 3 | wangwu | 18 | F |
| 4 | 2 | kk | 18 | F |
| 6 | 2 | lili | 42 | M |
| 9 | 2 | 张明 | 26 | M |
| 8 | 3 | 小明 | 25 | M |
| 3 | 1 | zhangsan | 20 | M |
+----+--------+----------+------+------+
4、分页查询
- select * from emp limit 3;//返回从第一条到第三条的数据
- select * from emp limit 0,3;//返回从第一条到第三条的数据
- select * from emp limit 3,5;//从结果的第三条开始,向后显示5条
例1:
mysql> select * from emp limit 3;
+----+--------+----------+------+------+
| id | dep_id | name | age | sex |
+----+--------+----------+------+------+
| 2 | 3 | wangwu | 18 | F |
| 3 | 1 | zhangsan | 20 | M |
| 4 | 2 | kk | 18 | F |
+----+--------+----------+------+------+
例2:
mysql> select * from emp limit 3,5;
+----+--------+--------+------+------+
| id | dep_id | name | age | sex |
+----+--------+--------+------+------+
| 5 | 3 | lisi | 35 | F |
| 6 | 2 | lili | 42 | M |
| 7 | 2 | 李四 | 23 | F |
| 8 | 3 | 小明 | 25 | M |
| 9 | 2 | 张明 | 26 | M |
+----+--------+--------+------+------+
5、多表查询
- select e.id,d.dname as dep_name,e.name,e.sex,e.age from emp e,dep d where
e.dep_id=d.id;
例:
mysql> select e.id,d.dname as dep_name,e.name,e.sex,e.age from emp e,dep d where
-> e.dep_id=d.id;
+----+----------+----------+------+------+
| id | dep_name | name | sex | age |
+----+----------+----------+------+------+
| 2 | 行政 | wangwu | F | 18 |
| 3 | 生产 | zhangsan | M | 20 |
| 4 | 销售 | kk | F | 18 |
| 5 | 行政 | lisi | F | 35 |
| 6 | 销售 | lili | M | 42 |
| 7 | 销售 | 李四 | F | 23 |
| 8 | 行政 | 小明 | M | 25 |
| 9 | 销售 | 张明 | M | 26 |
| 10 | 生产 | 王五 | F | 23 |
| 11 | 行政 | 张华 | F | 27 |
+----+----------+----------+------+------+
6、连接查询
- select e.id,d.dname as dep_name,e.name,e.age,e.sex from emp e inner join dep d on e.dep_id=d.id;
- //内连:两边都匹配的数据显示
- select e.id,d.dname as dep_name,e.name,e.age,e.sex from emp e left join dep d on e.dep_id=d.id;
- //左连:左边的表数据要全部显示,右边有不存在或不匹配的项,数据库用NULL来填充
- select e.id,d.dname as dep_name,e.name,e.age,e.sex from emp e right join dep d on e.dep_id=d.id;
- //右连:右边的表数据要全部显示,左边有不存在或不匹配的项,数据库用NULL来填充
- 例1:
为了效果更明显,又插入了一行数据。
insert into emp values(null,5,'zhangsan',64,'M');
mysql> select e.id,d.dname as dep_name,e.name,e.age,e.sex from emp e inner join dep d on e.dep_id=d.id;
+----+----------+----------+------+------+
| id | dep_name | name | age | sex |
+----+----------+----------+------+------+
| 2 | 行政 | wangwu | 18 | F |
| 3 | 生产 | zhangsan | 20 | M |
| 4 | 销售 | kk | 18 | F |
| 5 | 行政 | lisi | 35 | F |
| 6 | 销售 | lili | 42 | M |
| 7 | 销售 | 李四 | 23 | F |
| 8 | 行政 | 小明 | 25 | M |
| 9 | 销售 | 张明 | 26 | M |
| 10 | 生产 | 王五 | 23 | F |
| 11 | 行政 | 张华 | 27 | F |
+----+----------+----------+------+------+
10 rows in set (0.00 sec)
例2:
mysql> select e.id,d.dname as dep_name,e.name,e.age,e.sex from emp e left join dep d on e.dep_id=d.id;
+----+----------+----------+------+------+
| id | dep_name | name | age | sex |
+----+----------+----------+------+------+
| 3 | 生产 | zhangsan | 20 | M |
| 10 | 生产 | 王五 | 23 | F |
| 4 | 销售 | kk | 18 | F |
| 6 | 销售 | lili | 42 | M |
| 7 | 销售 | 李四 | 23 | F |
| 9 | 销售 | 张明 | 26 | M |
| 2 | 行政 | wangwu | 18 | F |
| 5 | 行政 | lisi | 35 | F |
| 8 | 行政 | 小明 | 25 | M |
| 11 | 行政 | 张华 | 27 | F |
| 12 | NULL | zhangsan | 64 | M |
+----+----------+----------+------+------+
例3:
mysql> select e.id,d.dname as dep_name,e.name,e.age,e.sex from emp e right join dep d on e.dep_id=d.id;
+------+----------+----------+------+------+
| id | dep_name | name | age | sex |
+------+----------+----------+------+------+
| 2 | 行政 | wangwu | 18 | F |
| 3 | 生产 | zhangsan | 20 | M |
| 4 | 销售 | kk | 18 | F |
| 5 | 行政 | lisi | 35 | F |
| 6 | 销售 | lili | 42 | M |
| 7 | 销售 | 李四 | 23 | F |
| 8 | 行政 | 小明 | 25 | M |
| 9 | 销售 | 张明 | 26 | M |
| 10 | 生产 | 王五 | 23 | F |
| 11 | 行政 | 张华 | 27 | F |
| NULL | 技术 | NULL | NULL | NULL |
+------+----------+----------+------+------+
7、子查询
- select e.id,e.name,e.age from emp e where e.dep_id in(select id from dep where id
in(2,3)); - select (select dname from dep d where d.id=e.dep_id) as dname,count(e.dep_id) as total
from emp e group by e.dep_id having total>0 ;
例1:
mysql> select e.id,e.name,e.age from emp e where e.dep_id in(select id from dep where id
-> in(2,3));
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | wangwu | 18 |
| 4 | kk | 18 |
| 5 | lisi | 35 |
| 6 | lili | 42 |
| 7 | 李四 | 23 |
| 8 | 小明 | 25 |
| 9 | 张明 | 26 |
| 11 | 张华 | 27 |
+----+--------+------+
例2:
mysql> select (select dname from dep d where d.id=e.dep_id) as dname,count(e.dep_id) as total
-> from emp e group by e.dep_id having total>0 ;
+--------+-------+
| dname | total |
+--------+-------+
| 生产 | 2 |
| 销售 | 4 |
| 行政 | 4 |
| NULL | 1 |
+--------+-------+
注:以上示例操作都是针对下面这两张表