mysql数据库补充知识2 查询数据库记录信息之单表查询

一单表查询的语法

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

                  WHERE 条件

                  GROUP BY field

                  HAVING 筛选

                  ORDER BY field

                  LIMIT 限制条数

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

重点中的重点：关键字的执行优先级

from                     #从库中找到某张表

where　　　　　　　　　　　　#用where约束条件从表中取出符合条件的数据

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

having　　　　　　　　　　#将分组的结果进行having过滤

select　　　　　　　　　　 #执行select

distinct　　　　　　　　　#去重

order by　　　　　　　　　#将结果按条件排序：order by

limit　　　　　　　　　　　#限制结果的显示条数

三SELECT语句关键字的定义顺序

SELECT DISTINCT <select_list>

FROM <left_table>

<join_type> JOIN <right_table>

ON <join_condition>

WHERE <where_condition>

GROUP BY <group_by_list>

HAVING <having_condition>

ORDER BY <order_by_condition>

LIMIT <limit_number>

四 SELECT语句关键字的执行顺序

(7)     SELECT

(8)     DISTINCT <select_list>

(1)     FROM <left_table>

(3)     <join_type> JOIN <right_table>

(2)     ON <join_condition>

(4)     WHERE <where_condition>

(5)     GROUP BY <group_by_list>

(6)     HAVING <having_condition>

(9)     ORDER BY <order_by_condition>

(10)    LIMIT <limit_number>

五测试sql语句的执行顺序

　　1. 新建一个测试数据库TestDB；

create database TestDB;

　　2.创建测试表table1和table2；

CREATE TABLE table1

 (

     customer_id VARCHAR(10) NOT NULL,

     city VARCHAR(10) NOT NULL,

     PRIMARY KEY(customer_id)

 )ENGINE=INNODB DEFAULT CHARSET=UTF8;

 CREATE TABLE table2

 (

     order_id INT NOT NULL auto_increment,

     customer_id VARCHAR(10),

     PRIMARY KEY(order_id)

 )ENGINE=INNODB DEFAULT CHARSET=UTF8;

　　3.插入测试数据；

 INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');

 INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');

 INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');

 INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');

 INSERT INTO table2(customer_id) VALUES('163');

 INSERT INTO table2(customer_id) VALUES('163');

 INSERT INTO table2(customer_id) VALUES('9you');

 INSERT INTO table2(customer_id) VALUES('9you');

 INSERT INTO table2(customer_id) VALUES('9you');

 INSERT INTO table2(customer_id) VALUES('tx');

 INSERT INTO table2(customer_id) VALUES(NULL);

　　注释：准备工作做完以后，table1和table2看起来应该像下面这样：

mysql> select * from table1;

 +-------------+----------+

 | customer_id | city     |

 +-------------+----------+

 | 163         | hangzhou |

 | 9you        | shanghai |

 | baidu       | hangzhou |

 | tx          | hangzhou |

 +-------------+----------+

 4 rows in set (0.00 sec)

 mysql> select * from table2;

 +----------+-------------+

 | order_id | customer_id |

 +----------+-------------+

 |        1 | 163         |

 |        2 | 163         |

 |        3 | 9you        |

 |        4 | 9you        |

 |        5 | 9you        |

 |        6 | tx          |

 |        7 | NULL        |

 +----------+-------------+

 7 rows in set (0.00 sec)

  4、准备SQL逻辑查询测试语句

#查询来自杭州，并且订单数少于2的客户。

 SELECT a.customer_id, COUNT(b.order_id) as total_orders

 FROM table1 AS a

 LEFT JOIN table2 AS b

 ON a.customer_id = b.customer_id

 WHERE a.city = 'hangzhou'

 GROUP BY a.customer_id

 HAVING count(b.order_id) < 2

 ORDER BY total_orders DESC;

六执行顺序分析

在这些SQL语句的执行过程中，都会产生一个虚拟表，用来保存SQL语句的执行结果（这是重点），我现在就来跟踪这个虚拟表的变化，得到最终的查询结果的过程，来分析整个SQL逻辑查询的执行顺序和过程。

　　1、执行FROM语句

第一步，执行FROM语句。我们首先需要知道最开始从哪个表开始的，这就是FROM告诉我们的。现在有了<left_table>和<right_table>两个表，我们到底从哪个表开始，还是从两个表进行某种联系以后再开始呢？它们之间如何产生联系呢？——笛卡尔积

关于什么是笛卡尔积，请自行Google补脑。经过FROM语句对两个表执行笛卡尔积，会得到一个虚拟表，暂且叫VT1（vitual table 1），内容如下：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| 163         | hangzhou |        1 | 163         |

| 9you        | shanghai |        1 | 163         |

| baidu       | hangzhou |        1 | 163         |

| tx          | hangzhou |        1 | 163         |

| 163         | hangzhou |        2 | 163         |

| 9you        | shanghai |        2 | 163         |

| baidu       | hangzhou |        2 | 163         |

| tx          | hangzhou |        2 | 163         |

| 163         | hangzhou |        3 | 9you        |

| 9you        | shanghai |        3 | 9you        |

| baidu       | hangzhou |        3 | 9you        |

| tx          | hangzhou |        3 | 9you        |

| 163         | hangzhou |        4 | 9you        |

| 9you        | shanghai |        4 | 9you        |

| baidu       | hangzhou |        4 | 9you        |

| tx          | hangzhou |        4 | 9you        |

| 163         | hangzhou |        5 | 9you        |

| 9you        | shanghai |        5 | 9you        |

| baidu       | hangzhou |        5 | 9you        |

| tx          | hangzhou |        5 | 9you        |

| 163         | hangzhou |        6 | tx          |

| 9you        | shanghai |        6 | tx          |

| baidu       | hangzhou |        6 | tx          |

| tx          | hangzhou |        6 | tx          |

| 163         | hangzhou |        7 | NULL        |

| 9you        | shanghai |        7 | NULL        |

| baidu       | hangzhou |        7 | NULL        |

| tx          | hangzhou |        7 | NULL        |

+-------------+----------+----------+-------------+

总共有28（table1的记录条数 * table2的记录条数）条记录。这就是VT1的结果，接下来的操作就在VT1的基础上进行。

　　2、执行ON过滤

执行完笛卡尔积以后，接着就进行ON a.customer_id = b.customer_id条件过滤，根据ON中指定的条件，去掉那些不符合条件的数据，得到VT2表，内容如下：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| 163         | hangzhou |        1 | 163         |

| 163         | hangzhou |        2 | 163         |

| 9you        | shanghai |        3 | 9you        |

| 9you        | shanghai |        4 | 9you        |

| 9you        | shanghai |        5 | 9you        |

| tx          | hangzhou |        6 | tx          |

+-------------+----------+----------+-------------+

VT2就是经过ON条件筛选以后得到的有用数据，而接下来的操作将在VT2的基础上继续进行。

　　3、添加外部行

这一步只有在连接类型为OUTER JOIN时才发生，如LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。在大多数的时候，我们都是会省略掉OUTER关键字的，但OUTER表示的就是外部行的概念。

LEFT OUTER JOIN把左表记为保留表，得到的结果为：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| 163         | hangzhou |        1 | 163         |

| 163         | hangzhou |        2 | 163         |

| 9you        | shanghai |        3 | 9you        |

| 9you        | shanghai |        4 | 9you        |

| 9you        | shanghai |        5 | 9you        |

| tx          | hangzhou |        6 | tx          |

| baidu       | hangzhou |     NULL | NULL        |

+-------------+----------+----------+-------------+

RIGHT OUTER JOIN把右表记为保留表，得到的结果为：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| 163         | hangzhou |        1 | 163         |

| 163         | hangzhou |        2 | 163         |

| 9you        | shanghai |        3 | 9you        |

| 9you        | shanghai |        4 | 9you        |

| 9you        | shanghai |        5 | 9you        |

| tx          | hangzhou |        6 | tx          |

| NULL        | NULL     |        7 | NULL        |

+-------------+----------+----------+-------------+

FULL OUTER JOIN把左右表都作为保留表，得到的结果为：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| 163         | hangzhou |        1 | 163         |

| 163         | hangzhou |        2 | 163         |

| 9you        | shanghai |        3 | 9you        |

| 9you        | shanghai |        4 | 9you        |

| 9you        | shanghai |        5 | 9you        |

| tx          | hangzhou |        6 | tx          |

| baidu       | hangzhou |     NULL | NULL        |

| NULL        | NULL     |        7 | NULL        |

+-------------+----------+----------+-------------+

添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表VT3。

由于我在准备的测试SQL查询逻辑语句中使用的是LEFT JOIN，过滤掉了以下这条数据：

| baidu       | hangzhou |     NULL | NULL        |

现在就把这条数据添加到VT2表中，得到的VT3表如下：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| 163         | hangzhou |        1 | 163         |

| 163         | hangzhou |        2 | 163         |

| 9you        | shanghai |        3 | 9you        |

| 9you        | shanghai |        4 | 9you        |

| 9you        | shanghai |        5 | 9you        |

| tx          | hangzhou |        6 | tx          |

| baidu       | hangzhou |     NULL | NULL        |

+-------------+----------+----------+-------------+

接下来的操作都会在该VT3表上进行。

　　4、执行WHERE过滤

对添加外部行得到的VT3进行WHERE过滤，只有符合<where_condition>的记录才会输出到虚拟表VT4中。当我们执行WHERE a.city = 'hangzhou'的时候，就会得到以下内容，并存在虚拟表VT4中：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| 163         | hangzhou |        1 | 163         |

| 163         | hangzhou |        2 | 163         |

| tx          | hangzhou |        6 | tx          |

| baidu       | hangzhou |     NULL | NULL        |

+-------------+----------+----------+-------------+

复制代码

但是在使用WHERE子句时，需要注意以下两点：

由于数据还没有分组，因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对分组统计的过滤；
由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，如：SELECT city as c FROM t WHERE c='shanghai';是不允许出现的。

　　　5、执行GROUP BY分组

GROU BY子句主要是对使用WHERE子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer_id，就会得到以下内容(默认只显示组内第一条)：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| 163         | hangzhou |        1 | 163         |

| baidu       | hangzhou |     NULL | NULL        |

| tx          | hangzhou |        6 | tx          |

+-------------+----------+----------+-------------+

得到的内容会存入虚拟表VT5中，此时，我们就得到了一个VT5虚拟表，接下来的操作都会在该表上完成。

　　　6、执行HAVING过滤

HAVING子句主要和GROUP BY子句配合使用，对分组得到的VT5虚拟表进行条件过滤。当我执行测试语句中的HAVING count(b.order_id) < 2时，将得到以下内容：

+-------------+----------+----------+-------------+

| customer_id | city     | order_id | customer_id |

+-------------+----------+----------+-------------+

| baidu       | hangzhou |     NULL | NULL        |

| tx          | hangzhou |        6 | tx          |

+-------------+----------+----------+-------------+

这就是虚拟表VT6。

　　7、SELECT列表

现在才会执行到SELECT子句，不要以为SELECT子句被写在第一行，就是第一个被执行的。

我们执行测试语句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders，从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容：

+-------------+--------------+

| customer_id | total_orders |

+-------------+--------------+

| baidu       |            0 |

| tx          |            1 |

+-------------+--------------+

还没有完，这只是虚拟表VT7。

　　8、执行DISTINCT子句

如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT7是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。

由于我的测试SQL语句中并没有使用DISTINCT，所以，在该查询中，这一步不会生成一个虚拟表。

　　9、执行ORDER BY子句

对虚拟表中的内容按照指定的列进行排序，然后返回一个新的虚拟表，我们执行测试SQL语句中的ORDER BY total_orders DESC，就会得到以下内容：

+-------------+--------------+

| customer_id | total_orders |

+-------------+--------------+

| tx          |            1 |

| baidu       |            0 |

+-------------+--------------+

可以看到这是对total_orders列进行降序排列的。上述结果会存储在VT8中。

　　10、执行LIMIT子句

LIMIT子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句，得到的结果同样是无序的，所以，很多时候，我们都会看到LIMIT子句会和ORDER BY子句一起使用。

MySQL数据库的LIMIT支持如下形式的选择：

LIMIT n, m

表示从第n条记录开始选择m条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据，使用LIMIT子句没有任何问题，当数据量非常大的时候，使用LIMIT n, m是非常低效的。因为LIMIT的机制是每次都是从头开始扫描，如果需要从第60万行开始，读取3条数据，就需要先扫描定位到60万行，然后再进行读取，而扫描的过程是一个非常低效的过程。所以，对于大数据处理时，是非常有必要在应用层建立一定的缓存机制（现在的大数据处理，大都使用缓存）

 八、sql语句的具体用法
　　　1、 简单查询

company.employee

    员工id      id                  int

    姓名        emp_name            varchar

    性别        sex                 enum

    年龄        age                 int

    入职日期     hire_date           date

    岗位        post                varchar

    职位描述     post_comment        varchar

    薪水        salary              double

    办公室       office              int

    部门编号     depart_id           int

#创建表

create table employee(

id int not null unique auto_increment,

name varchar(20) not null,

sex enum('male','female') not null default 'male', #大部分是男的

age int(3) unsigned not null default 28,

hire_date date not null,

post varchar(50),

post_comment varchar(100),

salary double(15,2),

office int, #一个部门一个屋子

depart_id int

);

#查看表结构

mysql> desc employee;

+--------------+-----------------------+------+-----+---------+----------------+

| Field        | Type                  | Null | Key | Default | Extra          |

+--------------+-----------------------+------+-----+---------+----------------+

| id           | int(11)               | NO   | PRI | NULL    | auto_increment |

| name         | varchar(20)           | NO   |     | NULL    |                |

| sex          | enum('male','female') | NO   |     | male    |                |

| age          | int(3) unsigned       | NO   |     | 28      |                |

| hire_date    | date                  | NO   |     | NULL    |                |

| post         | varchar(50)           | YES  |     | NULL    |                |

| post_comment | varchar(100)          | YES  |     | NULL    |                |

| salary       | double(15,2)          | YES  |     | NULL    |                |

| office       | int(11)               | YES  |     | NULL    |                |

| depart_id    | int(11)               | YES  |     | NULL    |                |

+--------------+-----------------------+------+-----+---------+----------------+

#插入记录

#三个部门：教学，销售，运营

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values

('egon','male',18,'','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部

('alex','male',78,'','teacher',1000000.31,401,1),

('wupeiqi','male',81,'','teacher',8300,401,1),

('yuanhao','male',73,'','teacher',3500,401,1),

('liwenzhou','male',28,'','teacher',2100,401,1),

('jingliyang','female',18,'','teacher',9000,401,1),

('jinxin','male',18,'','teacher',30000,401,1),

('成龙','male',48,'','teacher',10000,401,1),

('歪歪','female',48,'','sale',3000.13,402,2),#以下是销售部门

('丫丫','female',38,'','sale',2000.35,402,2),

('丁丁','female',18,'','sale

',1000.37,402,2),

('星星','female',18,'20160513','sale',3000.29,402,2),

('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门

('程咬金','male',18,'19970312','operation',20000,403,3),

('程咬银','female',18,'20130311','operation',19000,403,3),

('程咬铜','male',18,'20150411','operation',18000,403,3),

('程咬铁','female',18,'20140512','operation',17000,403,3)

;

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成g

#简单查询

    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id

    FROM employee;

    SELECT * FROM employee;

    SELECT name,salary FROM employee;

#避免重复DISTINCT

    SELECT DISTINCT post FROM employee;    

#通过四则运算查询

    SELECT name, salary*12 FROM employee;

    SELECT name, salary*12 AS Annual_salary FROM employee;

    SELECT name, salary*12 Annual_salary FROM employee;

#定义显示格式

   CONCAT() 函数用于连接字符串

   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary

   FROM employee;

   CONCAT_WS() 第一个参数为分隔符

   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary

   FROM employee;

　　2、WHERE约束

　　where字句中可以使用：

　　1. 比较运算符：> < >= <= <> !=
　　2. between 80 and 100 值在10到20之间
　　3. in(80,90,100) 值是10或20或30
　　4. like 'egon%'
　　　　 pattern可以是%或_，
%表示任意多字符
_表示一个字符
　　5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

#1:单条件查询

    SELECT name FROM employee

        WHERE post='sale';

#2:多条件查询

    SELECT name,salary FROM employee

        WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND

    SELECT name,salary FROM employee

        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee

        WHERE salary NOT BETWEEN 10000 AND 20000;

#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)

    SELECT name,post_comment FROM employee

        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee

        WHERE post_comment IS NOT NULL;

    SELECT name,post_comment FROM employee

        WHERE post_comment=''; 注意''是空字符串，不是null

    ps：

        执行

        update employee set post_comment='' where id=2;

        再用上条查看，就会有结果了

#5:关键字IN集合查询

    SELECT name,salary FROM employee

        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;

    SELECT name,salary FROM employee

        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee

        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询

    通配符’%’

    SELECT * FROM employee

            WHERE name LIKE 'eg%';

    通配符’_’

    SELECT * FROM employee

            WHERE name LIKE 'al__';

　　3、分组查询:GROUP BY

　　1、什么是分组？为什么要分组？　　　

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

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

#3、为何要分组呢？

    取每个部门的最高工资

    取每个部门的员工数

    取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据

#4、大前提：

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

　　2、ONLY_FULL_GROUP_BY

#查看MySQL 5.7默认的sql_mode如下：

mysql> select @@global.sql_mode;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#！！！注意

ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。

#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

复制代码

mysql> select @@global.sql_mode;

+-------------------+

| @@global.sql_mode |

+-------------------+

|                   |

+-------------------+

1 row in set (0.00 sec)

mysql> select * from emp group by post;

+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| id | name | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |

+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| 14 | 张野 | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |

|  9 | 歪歪 | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |

|  2 | alex | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |

|  1 | egon | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |

+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

4 rows in set (0.00 sec)

#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的

mysql> set global sql_mode='ONLY_FULL_GROUP_BY';

Query OK, 0 rows affected (0.00 sec)

mysql> quit #设置成功后，一定要退出，然后重新登录方可生效

Bye

mysql> use db1;

Database changed

mysql> select * from emp group by post; #报错

ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY

mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数

+----------------------------+-----------+

| post                       | count(id) |

+----------------------------+-----------+

| operation                  |         5 |

| sale                       |         5 |

| teacher                    |         7 |

| 老男孩驻沙河办事处外交大使 |         1 |

+----------------------------+-----------+

4 rows in set (0.00 sec)

复制代码

　　3、 GROUP BY　

单独使用GROUP BY关键字分组

    SELECT post FROM employee GROUP BY post;

    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用

    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名

    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用

    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

强调：

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义

多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

四聚合函数

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

示例：

    SELECT COUNT(*) FROM employee;

    SELECT COUNT(*) FROM employee WHERE depart_id=1;

    SELECT MAX(salary) FROM employee;

    SELECT MIN(salary) FROM employee;

    SELECT AVG(salary) FROM employee;

    SELECT SUM(salary) FROM employee;

    SELECT SUM(salary) FROM employee WHERE depart_id=3;

六 HAVING过滤

HAVING与WHERE不一样的地方在于!!!!!!

#！！！执行优先级从高到低：where > group by > having

#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

mysql> select @@sql_mode;

+--------------------+

| @@sql_mode         |

+--------------------+

| ONLY_FULL_GROUP_BY |

+--------------------+

1 row in set (0.00 sec)

mysql> select * from emp where salary > 100000;

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

1 row in set (0.00 sec)

mysql> select * from emp having salary > 100000;

ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause

mysql> select post,group_concat(name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段

ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000;

+-----------+-------------------------------------------------------+

| post | group_concat(name) |

+-----------+-------------------------------------------------------+

| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |

| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |

+-----------+-------------------------------------------------------+

2 rows in set (0.00 sec)

七查询排序:ORDER BY

按单列排序

    SELECT * FROM employee ORDER BY salary;

    SELECT * FROM employee ORDER BY salary ASC;

    SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序

    SELECT * from employee

        ORDER BY age,

        salary DESC;

八限制查询的记录数:LIMIT

示例：

    SELECT * FROM employee ORDER BY salary DESC

        LIMIT 3;                    #默认初始位置为0 

    SELECT * FROM employee ORDER BY salary DESC

        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM employee ORDER BY salary DESC

        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

九使用正则表达式查询

SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}';

小结：对字符串匹配的方式

WHERE name = 'egon';

WHERE name LIKE 'yua%';

WHERE name REGEXP 'on$';

秒客网

mysql数据库补充知识2 查询数据库记录信息之单表查询

一单表查询的语法

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

三SELECT语句关键字的定义顺序

四 SELECT语句关键字的执行顺序

五测试sql语句的执行顺序

六执行顺序分析

3、分组查询:GROUP BY

六 HAVING过滤

七查询排序:ORDER BY

八限制查询的记录数:LIMIT

九使用正则表达式查询

相关文章

mysql数据库补充知识2 查询数据库记录信息之单表查询

一 单表查询的语法

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

三SELECT语句关键字的定义顺序

四 SELECT语句关键字的执行顺序

五 测试sql语句的执行顺序

六 执行顺序分析

3、 分组查询:GROUP BY

六 HAVING过滤

七 查询排序:ORDER BY

八 限制查询的记录数:LIMIT

九 使用正则表达式查询

相关文章

一单表查询的语法

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

五测试sql语句的执行顺序

六执行顺序分析

　　3、分组查询:GROUP BY

七查询排序:ORDER BY

八限制查询的记录数:LIMIT

九使用正则表达式查询