[基础教程]Mysql单表查询优先级实例讲解

ronpris
ronpris
ronpris
253
文章
1
评论
2020年6月3日18:11:54 评论 57 19030字阅读63分26秒

这篇文章主要介绍了MySQL单表查询操作,结合实例形式详细分析了mysql单表查询的语法、约束、分组、聚合、过滤、排序等相关原理、操作技巧与注意事项,需要的朋友可以参考下

本文实例讲述了MySQL单表查询操作。分享给大家供大家参考,具体如下:

语法

一、单表查询的语法

SELECT 字段1,字段2... FROM 表名

WHERE 条件

GROUP BY field

HAVING 筛选

ORDER BY field

LIMIT 限制条数

二、关键字的执行优先级(重点)

关键字的执行优先级

FROM

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

LIMIT

1.找到表:FROM

2.拿着where指定的约束条件,去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.通过distinct进行数据去重

7.将结果按条件排序:order by

8.限制结果的显示条数

(1)where 约束

where运算符

where子句中可以使用以下运算符

1.比较运算符:>、<、>=、<=、<>、!=

2.between 80 and 100 :值在80到100之间

3.in(80,90,100)值是10或20或30

4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符,_表示一个字符

5.逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not

(2)group by 分组查询

#1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的

#2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等

#3、为何要分组呢?

取每个部门的最高工资

取每个部门的员工数

取男人数和女人数

小窍门:‘每'这个字后面的字段,就是我们分组的依据

#4、大前提:

可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数

当执行以下sql语句的时候,没有报错,但本身是没有意义的

mysql> select * from employee group by post;
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post      | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss      | 老板         |  20000 |   1001 |         1 |
|  2 | 小刚   | male   |   27 | 1991-10-21 | operation | 操作         |   6000 |   1003 |         1 |
|  1 | 李明   | male   |   28 | 1990-01-02 | sale      | 销售         |   8000 |   1002 |         1 |
|  4 | 小丽   | female |   15 | 2005-08-04 | student   | 学徒         |   2000 |   1005 |         1 |
|  8 | 王红   | female |   22 | 2015-03-08 | teacher   | 教授         |   5500 |   1004 |         1 |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
5 rows in set (0.00 sec)

设置sql_mode为ONLY_FULL_GROUP_BY,并且退出,再进入才会生效

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

退出后,再次进入

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------+
| @@sql_mode                                                                        |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

设置完sql_mode之后,重新执行上面的sql,则会报错,报错如下:

mysql> select * from employee group by post;
ERROR 1055 (42000): 'demo_ronpris.employee.id' isn't in GROUP BY

报错原因:

ONLY_FULL_GROUP_BY的意思是:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。

示例1:

mysql> select post from employee group by post;
+-----------+
| post      |
+-----------+
| boss      |
| operation |
| sale      |
| student   |
| teacher   |
+-----------+
5 rows in set (0.01 sec)

示例2:

mysql> select name,post from employee group by post,name;
+--------+-----------+
| name   | post      |
+--------+-----------+
| 夏敏   | boss      |
| 明珠   | boss      |
| 小刚   | operation |
| 小红   | operation |
| 光明   | sale      |
| 小明   | sale      |
| 李强   | sale      |
| 李明   | sale      |
| 小丽   | student   |
| 小霞   | teacher   |
| 张强   | teacher   |
| 王红   | teacher   |
+--------+-----------+
12 rows in set (0.00 sec)

示例3:

mysql> select post,count(id) from employee group by post;
+-----------+-----------+
| post      | count(id) |
+-----------+-----------+
| boss      |         2 |
| operation |         2 |
| sale      |         4 |
| student   |         1 |
| teacher   |         3 |
+-----------+-----------+
5 rows in set (0.00 sec)

(3)聚合函数

max()求最大值

min()求最小值

avg()求平均值

sum() 求和

count() 求总个数

#强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组

示例1:每个部门有多少个员工

mysql> select depart_id as 部门ID,count(id) as 人数 from employee group by depart_id;
+----------+--------+
| 部门ID   | 人数   |
+----------+--------+
|        1 |      2 |
|        2 |      4 |
|        3 |      3 |
|        4 |      2 |
|        5 |      1 |
+----------+--------+
5 rows in set (0.00 sec)

示例2:每个部门的最高薪水

mysql> select depart_id as 部门ID,max(salary) as 最高薪水 from employee group by depart_id;
+----------+--------------+
| 部门ID   | 最高薪水     |
+----------+--------------+
|        1 |        20000 |
|        2 |         8200 |
|        3 |         5800 |
|        4 |         6600 |
|        5 |         2000 |
+----------+--------------+
5 rows in set (0.00 sec)

示例3:每个部门的最低薪水

mysql> select depart_id as 部门ID,min(salary) as 最低薪水 from employee group by depart_id;
+----------+--------------+
| 部门ID   | 最低薪水     |
+----------+--------------+
|        1 |        18000 |
|        2 |         7800 |
|        3 |         5200 |
|        4 |         6200 |
|        5 |         2000 |
+----------+--------------+
5 rows in set (0.00 sec)

示例4:每个部门的平均薪水

mysql> select depart_id as 部门ID,avg(salary) as 平均薪水 from employee group by depart_id;
+----------+--------------+
| 部门ID   | 平均薪水     |
+----------+--------------+
|        1 |   19000.0000 |
|        2 |    7975.0000 |
|        3 |    5500.0000 |
|        4 |    6400.0000 |
|        5 |    2000.0000 |
+----------+--------------+
5 rows in set (0.00 sec)

示例5:每个部门的所有薪水

mysql> select depart_id as 部门ID,sum(salary) as 所有薪水 from employee group by depart_id;
+----------+--------------+
| 部门ID   | 所有薪水     |
+----------+--------------+
|        1 |        38000 |
|        2 |        31900 |
|        3 |        16500 |
|        4 |        12800 |
|        5 |         2000 |
+----------+--------------+
5 rows in set (0.00 sec)

(4)HAVING过滤

HAVING与WHERE区别

执行优先级从高到低排序:where > group by > having

注释:Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
注释: Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数

示例1

mysql> select * from employee where salary>10000;
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss | 老板         |  18000 |   1001 |         1 |
| 10 | 明珠   | female |   43 | 2015-03-10 | boss | 老板         |  20000 |   1001 |         1 |
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
2 rows in set (0.00 sec)

示例2

mysql> select * from employee having salary>10000;
ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause

必须使用group by才能使用group_concat()函数,将所有的name值进行关联

示例3

mysql> select post,group_concat(name) from employee group by post having salary > 7000;
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

修改后的语句如下

mysql> select post,group_concat(name) from employee where salary > 7000  group by post;
+------+-----------------------------+
| post | group_concat(name)          |
+------+-----------------------------+
| boss | 夏敏,明珠                   |
| sale | 李明,小明,光明,李强         |
+------+-----------------------------+
2 rows in set (0.00 sec)

课后练习

  1. 查询各岗位内包含的员工个数小于3的岗位名、岗位内包含员工名字、个数
  2. 查询各岗位平均薪资大于7000的岗位名、平均工资
  3. 查询各岗位平均薪资大于7000且小于20000的岗位名、平均工资

示例1:课后答案

mysql> select post,group_concat(name),count(id) from employee group by post having count(id)<3;
+-----------+--------------------+-----------+
| post      | group_concat(name) | count(id) |
+-----------+--------------------+-----------+
| boss      | 夏敏,明珠          |         2 |
| operation | 小刚,小红          |         2 |
| student   | 小丽               |         1 |
+-----------+--------------------+-----------+
3 rows in set (0.00 sec)

示例2:课后答案

mysql> select post,avg(salary) from employee group by post having avg(salary) > 7000;
+------+-------------+
| post | avg(salary) |
+------+-------------+
| boss |  19000.0000 |
| sale |   7975.0000 |
+------+-------------+
2 rows in set (0.00 sec)

示例3:课后答案

mysql>  select post,avg(salary) from employee group by post having avg(salary) > 7000 and avg(salary) <20000;
+------+-------------+
| post | avg(salary) |
+------+-------------+
| boss |  19000.0000 |
| sale |   7975.0000 |
+------+------------+
2 rows in set (0.01 sec)

(5)order by 查询排序

示例1:按年龄正序排列,默认ASC隐藏

mysql> SELECT * FROM employee ORDER BY age;
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post      | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
|  4 | 小丽   | female |   15 | 2005-08-04 | student   | 学徒         |   2000 |   1005 |         5 |
|  5 | 小红   | female |   18 | 2012-07-05 | operation | 操作         |   6200 |   1003 |         4 |
| 12 | 小霞   | female |   19 | 2001-12-12 | teacher   | 教授         |   5500 |   1004 |         3 |
|  9 | 张强   | male   |   20 | 2000-11-09 | teacher   | 教授         |   5800 |   1004 |         3 |
|  8 | 王红   | female |   22 | 2015-03-08 | teacher   | 教授         |   5200 |   1004 |         3 |
|  2 | 小刚   | male   |   27 | 1991-10-21 | operation | 操作         |   6600 |   1003 |         4 |
| 11 | 李强   | male   |   27 | 1990-04-11 | sale      | 销售         |   8000 |   1002 |         2 |
|  1 | 李明   | male   |   28 | 1990-01-02 | sale      | 销售         |   7800 |   1002 |         2 |
|  3 | 小明   | male   |   28 | 1990-11-15 | sale      | 销售         |   7900 |   1002 |         2 |
|  6 | 光明   | female |   29 | 1989-06-06 | sale      | 销售         |   8200 |   1002 |         2 |
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss      | 老板         |  18000 |   1001 |         1 |
| 10 | 明珠   | female |   43 | 2015-03-10 | boss      | 老板         |  20000 |   1001 |         1 |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
12 rows in set (0.01 sec)

示例2:按年龄正序排列,显示ASC

mysql> SELECT * FROM employee ORDER BY age ASC;
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post      | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
|  4 | 小丽   | female |   15 | 2005-08-04 | student   | 学徒         |   2000 |   1005 |         5 |
|  5 | 小红   | female |   18 | 2012-07-05 | operation | 操作         |   6200 |   1003 |         4 |
| 12 | 小霞   | female |   19 | 2001-12-12 | teacher   | 教授         |   5500 |   1004 |         3 |
|  9 | 张强   | male   |   20 | 2000-11-09 | teacher   | 教授         |   5800 |   1004 |         3 |
|  8 | 王红   | female |   22 | 2015-03-08 | teacher   | 教授         |   5200 |   1004 |         3 |
|  2 | 小刚   | male   |   27 | 1991-10-21 | operation | 操作         |   6600 |   1003 |         4 |
| 11 | 李强   | male   |   27 | 1990-04-11 | sale      | 销售         |   8000 |   1002 |         2 |
|  1 | 李明   | male   |   28 | 1990-01-02 | sale      | 销售         |   7800 |   1002 |         2 |
|  3 | 小明   | male   |   28 | 1990-11-15 | sale      | 销售         |   7900 |   1002 |         2 |
|  6 | 光明   | female |   29 | 1989-06-06 | sale      | 销售         |   8200 |   1002 |         2 |
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss      | 老板         |  18000 |   1001 |         1 |
| 10 | 明珠   | female |   43 | 2015-03-10 | boss      | 老板         |  20000 |   1001 |         1 |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
12 rows in set (0.00 sec)

示例3:按年龄倒序排列,显示desc

mysql> SELECT * FROM employee ORDER BY age DESC;
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post      | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
| 10 | 明珠   | female |   43 | 2015-03-10 | boss      | 老板         |  20000 |   1001 |         1 |
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss      | 老板         |  18000 |   1001 |         1 |
|  6 | 光明   | female |   29 | 1989-06-06 | sale      | 销售         |   8200 |   1002 |         2 |
|  1 | 李明   | male   |   28 | 1990-01-02 | sale      | 销售         |   7800 |   1002 |         2 |
|  3 | 小明   | male   |   28 | 1990-11-15 | sale      | 销售         |   7900 |   1002 |         2 |
|  2 | 小刚   | male   |   27 | 1991-10-21 | operation | 操作         |   6600 |   1003 |         4 |
| 11 | 李强   | male   |   27 | 1990-04-11 | sale      | 销售         |   8000 |   1002 |         2 |
|  8 | 王红   | female |   22 | 2015-03-08 | teacher   | 教授         |   5200 |   1004 |         3 |
|  9 | 张强   | male   |   20 | 2000-11-09 | teacher   | 教授         |   5800 |   1004 |         3 |
| 12 | 小霞   | female |   19 | 2001-12-12 | teacher   | 教授         |   5500 |   1004 |         3 |
|  5 | 小红   | female |   18 | 2012-07-05 | operation | 操作         |   6200 |   1003 |         4 |
|  4 | 小丽   | female |   15 | 2005-08-04 | student   | 学徒         |   2000 |   1005 |         5 |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
12 rows in set (0.00 sec)

示例4:按多列排序:先按照age升序排序,如果年纪相同,则按照id降序

mysql> SELECT * from employee ORDER BY age ASC, id DESC;
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post      | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
|  4 | 小丽   | female |   15 | 2005-08-04 | student   | 学徒         |   2000 |   1005 |         5 |
|  5 | 小红   | female |   18 | 2012-07-05 | operation | 操作         |   6200 |   1003 |         4 |
| 12 | 小霞   | female |   19 | 2001-12-12 | teacher   | 教授         |   5500 |   1004 |         3 |
|  9 | 张强   | male   |   20 | 2000-11-09 | teacher   | 教授         |   5800 |   1004 |         3 |
|  8 | 王红   | female |   22 | 2015-03-08 | teacher   | 教授         |   5200 |   1004 |         3 |
| 11 | 李强   | male   |   27 | 1990-04-11 | sale      | 销售         |   8000 |   1002 |         2 |
|  2 | 小刚   | male   |   27 | 1991-10-21 | operation | 操作         |   6600 |   1003 |         4 |
|  3 | 小明   | male   |   28 | 1990-11-15 | sale      | 销售         |   7900 |   1002 |         2 |
|  1 | 李明   | male   |   28 | 1990-01-02 | sale      | 销售         |   7800 |   1002 |         2 |
|  6 | 光明   | female |   29 | 1989-06-06 | sale      | 销售         |   8200 |   1002 |         2 |
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss      | 老板         |  18000 |   1001 |         1 |
| 10 | 明珠   | female |   43 | 2015-03-10 | boss      | 老板         |  20000 |   1001 |         1 |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
12 rows in set (0.00 sec)

(6)limit  限制查询的记录数

示例1:默认初始位置为0

mysql> SELECT * FROM employee ORDER BY salary DESC LIMIT 3;
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
| 10 | 明珠   | female |   43 | 2015-03-10 | boss | 老板         |  20000 |   1001 |         1 |
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss | 老板         |  18000 |   1001 |         1 |
|  6 | 光明   | female |   29 | 1989-06-06 | sale | 销售         |   8200 |   1002 |         2 |
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
3 rows in set (0.00 sec)

示例2:从第0开始,即先查询出第一条,然后包含这一条在内往后查5条

mysql> SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5;
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
| 10 | 明珠   | female |   43 | 2015-03-10 | boss | 老板         |  20000 |   1001 |         1 |
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss | 老板         |  18000 |   1001 |         1 |
|  6 | 光明   | female |   29 | 1989-06-06 | sale | 销售         |   8200 |   1002 |         2 |
| 11 | 李强   | male   |   27 | 1990-04-11 | sale | 销售         |   8000 |   1002 |         2 |
|  3 | 小明   | male   |   28 | 1990-11-15 | sale | 销售         |   7900 |   1002 |         2 |
+----+--------+--------+------+------------+------+--------------+--------+--------+-----------+
5 rows in set (0.00 sec)

示例3:从第5开始,即先查询出第6条,然后包含这一条在内往后查5条

mysql> SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5;
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post      | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
|  1 | 李明   | male   |   28 | 1990-01-02 | sale      | 销售         |   7800 |   1002 |         2 |
|  2 | 小刚   | male   |   27 | 1991-10-21 | operation | 操作         |   6600 |   1003 |         4 |
|  5 | 小红   | female |   18 | 2012-07-05 | operation | 操作         |   6200 |   1003 |         4 |
|  9 | 张强   | male   |   20 | 2000-11-09 | teacher   | 教授         |   5800 |   1004 |         3 |
| 12 | 小霞   | female |   19 | 2001-12-12 | teacher   | 教授         |   5500 |   1004 |         3 |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
5 rows in set (0.00 sec)

# 第1页数据

mysql> select * from employee limit 0,5;
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post      | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
|  1 | 李明   | male   |   28 | 1990-01-02 | sale      | 销售         |   7800 |   1002 |         2 |
|  2 | 小刚   | male   |   27 | 1991-10-21 | operation | 操作         |   6600 |   1003 |         4 |
|  3 | 小明   | male   |   28 | 1990-11-15 | sale      | 销售         |   7900 |   1002 |         2 |
|  4 | 小丽   | female |   15 | 2005-08-04 | student   | 学徒         |   2000 |   1005 |         5 |
|  5 | 小红   | female |   18 | 2012-07-05 | operation | 操作         |   6200 |   1003 |         4 |
+----+--------+--------+------+------------+-----------+--------------+--------+--------+-----------+
5 rows in set (0.00 sec)

# 第2页数据

mysql> select * from employee limit 5,5;
+----+--------+--------+------+------------+---------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post    | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+---------+--------------+--------+--------+-----------+
|  6 | 光明   | female |   29 | 1989-06-06 | sale    | 销售         |   8200 |   1002 |         2 |
|  7 | 夏敏   | male   |   35 | 2009-12-07 | boss    | 老板         |  18000 |   1001 |         1 |
|  8 | 王红   | female |   22 | 2015-03-08 | teacher | 教授         |   5200 |   1004 |         3 |
|  9 | 张强   | male   |   20 | 2000-11-09 | teacher | 教授         |   5800 |   1004 |         3 |
| 10 | 明珠   | female |   43 | 2015-03-10 | boss    | 老板         |  20000 |   1001 |         1 |
+----+--------+--------+------+------------+---------+--------------+--------+--------+-----------+
5 rows in set (0.00 sec)

# 第3页数据

mysql> select * from employee limit 10,5;
+----+--------+--------+------+------------+---------+--------------+--------+--------+-----------+
| id | name   | sex    | age  | hire_date  | post    | post_comment | salary | office | depart_id |
+----+--------+--------+------+------------+---------+--------------+--------+--------+-----------+
| 11 | 李强   | male   |   27 | 1990-04-11 | sale    | 销售         |   8000 |   1002 |         2 |
| 12 | 小霞   | female |   19 | 2001-12-12 | teacher | 教授         |   5500 |   1004 |         3 |
+----+--------+--------+------+------------+---------+--------------+--------+--------+-----------+
2 rows in set (0.00 sec)

以上所使用的建表语句

CREATE TABLE `employee` (
  `id` int(32) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `post` varchar(255) DEFAULT NULL,
  `post_comment` varchar(255) DEFAULT NULL,
  `salary` int(32) DEFAULT NULL,
  `office` int(32) DEFAULT NULL,
  `depart_id` int(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入测试数据

INSERT INTO employee VALUES(1,'李明','male',28,'1990/1/2','sale','销售',7800,1002,1);
INSERT INTO employee VALUES(2,'小刚','male',27,'1991/10/21','operation','操作',6600,1003,1);
INSERT INTO employee VALUES(3,'小明','male',28,'1990/11/15','sale','销售', 7900,1002,1);
INSERT INTO employee VALUES(4,'小丽','female',15,'2005/8/4','student','学徒',2000,1005,1);
INSERT INTO employee VALUES(5,'小红','female',18,'2012/7/5','operation','操作', 6200,1003,1);
INSERT INTO employee VALUES(6,'光明','female',29,'1989/6/6','sale','销售', 8200,1002,1);
INSERT INTO employee VALUES(7,'夏敏','male',35,'2009/12/7','boss','老板', 18000,1001,1);
INSERT INTO employee VALUES(8,'王红','female',22,'2015/3/8','teacher','教授', 5200,1004,1);
INSERT INTO employee VALUES(9,'张强','male',20,'2000/11/9','teacher','教授', 5800,1004,1);
INSERT INTO employee VALUES(10,'明珠','female',43,'2015/3/10','boss','老板', 20000,1001,1);
INSERT INTO employee VALUES(11,'李强','male',27,'1990/4/11','sale','销售', 8000 ,1002,1);
INSERT INTO employee VALUES(12,'小霞','female',19,'2001/12/12','teacher','教授', 5500,1004,1);

 

总结

以上所述是小生给大家介绍的Mysql数据库单表查询关键字优先级的实例讲解,希望对大家有所帮助,如果大家有任何疑问请给我留言,小生会及时回复大家的。

继续阅读
[基础教程]Linux系统常用文件管理命令 原创推荐

[基础教程]Linux系统常用文件管理命令

这篇文章主要介绍了linux文件管理命令,结合实例形式分析Linux文件管理的显示、查看、统计等相关操作命令使用技巧,需要的朋友可以参考下 本文实例讲述了linux文件管理命令。分享给大家供大家参考,...