首先创建一个table
mysql> create table Total (id int AUTO_INCREMENT PRIMARY KEY,name char(20),stu_num int NOT NULL,teacher_num int NOT NULL);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into Total (name,stu_num,teacher_num) values ("小学1",400,20),("小学2",356,24),("小学3",403,19),("小学4",367,26),("小学5",373,20),("小学6",406,21);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from Total;
+----+---------+---------+-------------+
| id | name | stu_num | teacher_num |
+----+---------+---------+-------------+
| 1 | 小学1 | 400 | 20 |
| 2 | 小学2 | 356 | 24 |
| 3 | 小学3 | 403 | 19 |
| 4 | 小学4 | 367 | 26 |
| 5 | 小学5 | 373 | 20 |
| 6 | 小学6 | 406 | 21 |
+----+---------+---------+-------------+
6 rows in set (0.00 sec)
接下来就以这个表格为例,介绍select语句的深层运用。
一、查询不重复的记录
语句:select distinct [属性1,属性2] from 表名
说明:distinct必须放在最前的位置;
distinct只能使用需要去重的字段进行操作,也就是说如果distinct采用了name,stu_num两个字段,但是后面想利用id进行排序是不可以的,因为只能对name和stu_name两个字段进行操作;
distinct去重多个字段时,含义是几个字段同时重复时才会被过滤。
示例:
mysql> select distinct name,teacher_num from Total;
+---------+-------------+
| name | teacher_num |
+---------+-------------+
| 小学1 | 20 |
| 小学2 | 24 |
| 小学3 | 19 |
| 小学4 | 26 |
| 小学5 | 20 |
| 小学6 | 21 |
+---------+-------------+
6 rows in set (0.00 sec)
mysql> select distinct teacher_num from Total;
+-------------+
| teacher_num |
+-------------+
| 20 |
| 24 |
| 19 |
| 26 |
| 21 |
+-------------+
5 rows in set (0.00 sec)
二、排序
语法:select * from 表名 [where 条件] [ order by 属性1 [desc/asc],属性2 [desc/asc]... ];
说明:desc 降序排列,asc 升序排列;
order by 可以有多个参数,每个排序参数可以有不同的排序顺序;
如果第一个排序字段的值一样,则按照第二个排序字段进行排序;
如果只有一个排序字段,则字段值相同的记录将会无序排列。
示例:
mysql> select id,name from Total where id<4 order by teacher_num desc;
+----+---------+
| id | name |
+----+---------+
| 2 | 小学2 |
| 1 | 小学1 |
| 3 | 小学3 |
+----+---------+
3 rows in set (0.00 sec)
三、限制
语句:select ... [limit 起始偏移量,行数]; 或 select ... [limit 行数] offset 偏移量;
说明:.默认情况下,起始偏移量为0
示例:
mysql> select * from Total order by teacher_num asc;
+----+---------+---------+-------------+
| id | name | stu_num | teacher_num |
+----+---------+---------+-------------+
| 3 | 小学3 | 403 | 19 |
| 1 | 小学1 | 400 | 20 |
| 5 | 小学5 | 373 | 20 |
| 6 | 小学6 | 406 | 21 |
| 2 | 小学2 | 356 | 24 |
| 4 | 小学4 | 367 | 26 |
+----+---------+---------+-------------+
6 rows in set (0.00 sec)
mysql> select * from Total limit 4 offset 1;
+----+---------+---------+-------------+
| id | name | stu_num | teacher_num |
+----+---------+---------+-------------+
| 2 | 小学2 | 356 | 24 |
| 3 | 小学3 | 403 | 19 |
| 4 | 小学4 | 367 | 26 |
| 5 | 小学5 | 373 | 20 |
+----+---------+---------+-------------+
4 rows in set (0.00 sec)
mysql> select * from Total order by teacher_num asc limit 4 offset 1;
+----+---------+---------+-------------+
| id | name | stu_num | teacher_num |
+----+---------+---------+-------------+
| 1 | 小学1 | 400 | 20 |
| 5 | 小学5 | 373 | 20 |
| 6 | 小学6 | 406 | 21 |
| 2 | 小学2 | 356 | 24 |
+----+---------+---------+-------------+
4 rows in set (0.00 sec)
mysql> (select * from Total limit 4 offset 1) order by teacher_num asc;
+----+---------+---------+-------------+
| id | name | stu_num | teacher_num |
+----+---------+---------+-------------+
| 3 | 小学3 | 403 | 19 |
| 5 | 小学5 | 373 | 20 |
| 2 | 小学2 | 356 | 24 |
| 4 | 小学4 | 367 | 26 |
+----+---------+---------+-------------+
4 rows in set (0.00 sec)
四、聚合
语句:select 字段 fun_name from 表名 [where 条件] [group by 属性1,属性2...] [with rollup] [having 条件];
说明:fun_name表示要做的聚合操作,也就是聚合函数,常用的有sum(求和)、count(*)(记录数)、max(最大值)、min(最小值)等;
group by 表示要进行分类聚合的字段,比如要按照部门分类统计员工数量;
with rollup是可选项,表示是否对分类聚合后的结果进行再汇总;
having 表示对分类后的结果再进行条件过滤。
示例:
mysql> create table Staff
-> (id int AUTO_INCREMENT PRIMARY KEY,
-> xing char(20),
-> ming char(20),
-> slary int NOT NULL);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into Staff (xing,ming,slary) value('guo','ding',3200),
-> ('ding','tao',2800),
-> ('hao','fugui',3500),
-> ('guo','ming',4000),
-> ('hao','tian',2900),
-> ('feng','fei','3200'),
-> ('guo','ting',2600);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from Staff;
+----+------+-------+-------+
| id | xing | ming | slary |
+----+------+-------+-------+
| 8 | guo | ding | 3200 |
| 9 | ding | tao | 2800 |
| 10 | hao | fugui | 3500 |
| 11 | guo | ming | 4000 |
| 12 | hao | tian | 2900 |
| 13 | feng | fei | 3200 |
| 14 | guo | ting | 2600 |
+----+------+-------+-------+
7 rows in set (0.00 sec)
mysql> select count('xing') as xing_num from Staff where xing='guo'; //此处涉及了新的语法
+----------+
| xing_num |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(slary) from Staff;
+------------+
| sum(slary) |
+------------+
| 22200 |
+------------+
1 row in set (0.00 sec)
mysql> select xing,sum(slary) from Staff group by xing;
+------+------------+
| xing | sum(slary) |
+------+------------+
| ding | 2800 |
| feng | 3200 |
| guo | 9800 |
| hao | 6400 |
+------+------------+
4 rows in set (0.00 sec)
mysql> select xing,sum(slary) from Staff group by xing with rollup;
+------+------------+
| xing | sum(slary) |
+------+------------+
| ding | 2800 |
| feng | 3200 |
| guo | 9800 |
| hao | 6400 |
| NULL | 22200 |
+------+------------+
5 rows in set (0.00 sec)
mysql> select xing,sum(slary) from Staff group by xing having sum(slary)>5000; +------+------------+ | xing | sum(slary) | +------+------------+ | guo | 9800 | | hao | 6400 | +------+------------+ 2 rows in set (0.00 sec)
五、表连接
表连接分为内连接和外连接,其中内连接仅选出两张表中互相匹配的记录,外连接会选出其他不匹配的记录。
假设有以下Staff表和post表:
mysql> select * from staff; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | +----+----------+-------+ 7 rows in set (0.00 sec) mysql> select * from post; +-----+----------+-------+ | num | name | level | +-----+----------+-------+ | 1 | | 6 | | 2 | fengfei | 4 | | 3 | haotian | 3 | | 4 | guoming | 1 | | 5 | haofugui | 2 | | 6 | dingtao | 5 | | 7 | guoding | 4 | +-----+----------+-------+ 7 rows in set (0.00 sec)
示例:(内连接),语句:select 属性1,属性2 from 表1,表2 where 表1.属性值=表2.属性值
mysql> select post.name,level,slary from staff,post where staff.name=post.name; +----------+-------+-------+ | name | level | slary | +----------+-------+-------+ | fengfei | 4 | 3200 | | haotian | 3 | 2900 | | guoming | 1 | 4000 | | haofugui | 2 | 3500 | | dingtao | 5 | 2800 | | guoding | 4 | 3200 | +----------+-------+-------+
外连接又分为左连接与右连接:
左连接:包含所有左边表中的记录,甚至是右边表中没有和他匹配的记录。
右连接:包含所有右边表中的记录,甚至是右边表中没有和他匹配的记录。
示例:(左连接),语句:select 属性1,属性2 from 表1 left join 表2 on 表1.属性值=表2.属性值
mysql> select post.name,level,slary from staff left join post on staff.name=post.name; +----------+-------+-------+ | name | level | slary | +----------+-------+-------+ | guoding | 4 | 3200 | | dingtao | 5 | 2800 | | haofugui | 2 | 3500 | | guoming | 1 | 4000 | | haotian | 3 | 2900 | | fengfei | 4 | 3200 | | NULL | NULL | 2600 | +----------+-------+-------+ 7 rows in set (0.00 sec)
示例:(右连接),语句:select 属性1,属性2 from 表1 right join 表2 on 表1.属性值=表2.属性值
mysql> select post.name,level,slary from staff right join post on staff.name=post.name; +----------+-------+-------+ | name | level | slary | +----------+-------+-------+ | | 6 | NULL | | fengfei | 4 | 3200 | | haotian | 3 | 2900 | | guoming | 1 | 4000 | | haofugui | 2 | 3500 | | dingtao | 5 | 2800 | | guoding | 4 | 3200 | +----------+-------+-------+ 7 rows in set (0.00 sec)
需要说明的是,如果根据ON给出的连接条件,两个欲进行连接查询的表中存在一对多的匹配关系,则会输出对应的多条记录。
比如有以下两个表:
mysql> select * from YSHA; +------+------+ | code | NAME | +------+------+ | 1 | A1 | | 2 | A2 | +------+------+ 2 rows in set (0.01 sec) mysql> select * from YSHB; +------+------+ | code | col | +------+------+ | 1 | Row1 | | 1 | Row2 | | 1 | Row3 | | 3 | Row1 | +------+------+ 4 rows in set (0.00 sec)
接下来以YSHA.code=YSHB.code作为连接条件:
mysql> select * from YSHA left join YSHB on YSHA.code=YSHB.code; +------+------+------+------+ | code | NAME | code | col | +------+------+------+------+ | 1 | A1 | 1 | Row1 | | 1 | A1 | 1 | Row2 | | 1 | A1 | 1 | Row3 | | 2 | A2 | NULL | NULL | +------+------+------+------+ mysql> select * from YSHA right join YSHB on YSHA.code=YSHB.code; +------+------+------+------+ | code | NAME | code | col | +------+------+------+------+ | 1 | A1 | 1 | Row1 | | 1 | A1 | 1 | Row2 | | 1 | A1 | 1 | Row3 | | NULL | NULL | 3 | Row1 | +------+------+------+------+
六、联合查询
MySQL使用UNION和UNION ALL实现数据的联合查询。
假设有以下4个table:
mysql> select * from staff; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | +----+----------+-------+ rows in set (0.00 sec) mysql> select * from staff_1; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | liding | 2700 | | 3 | haofugui | 3500 | | 4 | xiaoli | 3600 | | 5 | yazhi | 3200 | | 6 | yuanfei | 3200 | | 7 | guoting | 3500 | +----+----------+-------+ rows in set (0.00 sec) mysql> select * from staff_2; +----+-------+----------+ | id | slary | name | +----+-------+----------+ | 1 | 3200 | guoding | | 2 | 2700 | liding | | 3 | 3500 | haofugui | | 4 | 3600 | xiaoli | | 5 | 3200 | yazhi | | 6 | 3200 | yuanfei | | 7 | 3500 | guoting | +----+-------+----------+ rows in set (0.00 sec) mysql> select * from staff_3; +----------+-------+ | name | slary | +----------+-------+ | guoding | 3200 | | liding | 2700 | | haofugui | 3500 | | xiaoli | 3600 | +----------+-------+ rows in set (0.00 sec)
1. UNION
语句:SELECT
column_name
FROM
table1
UNION
SELECT
column_name
FROM
table2
说明:1)用于合并两个或多个 SELECT 语句的结果集,并消去表中任何重复行;
2)UNION 内部的 SELECT 语句必须拥有相同数量的列,列也必须拥有相似的数据类型;
3)同时,每条 SELECT 语句中的列的顺序必须相同;
4)如果子句中有order by,limit等,需用括号()包起来,推荐放到所有子句之后,即对最终合并的结果来排序或筛选。
mysql> select * from staff union select * from staff_1; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | | 2 | liding | 2700 | | 4 | xiaoli | 3600 | | 5 | yazhi | 3200 | | 6 | yuanfei | 3200 | | 7 | guoting | 3500 | +----+----------+-------+ rows in set (0.00 sec) mysql> select * from staff union select * from staff_2; +----+----------------------+----------------------+ | id | name | slary | +----+----------------------+----------------------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | | 1 | 3200 | guoding | | 2 | 2700 | liding | | 3 | 3500 | haofugui | | 4 | 3600 | xiaoli | | 5 | 3200 | yazhi | | 6 | 3200 | yuanfei | | 7 | 3500 | guoting | +----+----------------------+----------------------+ rows in set (0.00 sec) mysql> select * from staff union select name from staff_1; ERROR 1222 (21000): The used SELECT statements have a different number of columns mysql> select * from staff union select * from staff_3; ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> (select * from staff order by id asc) union (select * from staff_1 order by slary desc); +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | | 2 | liding | 2700 | | 4 | xiaoli | 3600 | | 5 | yazhi | 3200 | | 6 | yuanfei | 3200 | | 7 | guoting | 3500 | +----+----------+-------+ rows in set (0.00 sec)
2. UNION ALL
作用及规则与UNION相同,区别是,UNION ALL不消除重复行
mysql> select * from staff union select * from staff_1; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | | 2 | liding | 2700 | | 4 | xiaoli | 3600 | | 5 | yazhi | 3200 | | 6 | yuanfei | 3200 | | 7 | guoting | 3500 | +----+----------+-------+ rows in set (0.00 sec) mysql> select * from staff union all select * from staff_1; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 1 | guoding | 3200 | | 2 | dingtao | 2800 | | 3 | haofugui | 3500 | | 4 | guoming | 4000 | | 5 | haotian | 2900 | | 6 | fengfei | 3200 | | 7 | guoting | 2600 | | 1 | guoding | 3200 | | 2 | liding | 2700 | | 3 | haofugui | 3500 | | 4 | xiaoli | 3600 | | 5 | yazhi | 3200 | | 6 | yuanfei | 3200 | | 7 | guoting | 3500 | +----+----------+-------+ rows in set (0.00 sec)