单表查询

发布时间：2020-12-12 00:02:57 所属栏目：MySql教程来源：网络整理

导读：在进行查询操作之前，先让我们建一个表，来方便执行后面的查询操作创建表 span style="color: #0000ff;"create span style="color: #0000ff;"table span style="color: #000000;" employee( id span style="color: #0000ff;"int span style="color: #808080

在进行查询操作之前，先让我们建一个表，来方便执行后面的查询操作

创建表

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,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1),#以下是教学部
('alex',78,'20150302','teacher',1000000.31,('wupeiqi',81,'20130305',8300,('yuanhao',73,'20140701',3500,('liwenzhou',28,'20121101',2100,('jingliyang','female','20110211',9000,('jinxin','19000301',30000,('成龙',48,'20101111',10000,('歪歪','20150311','sale',3000.13,402,#以下是销售部门
('丫丫',38,'20101101',2000.35,('丁丁','20110312',1000.37,('星星','20160513',3000.29,('格格','20170127',4000.33,('张野','20160311','operation',10000.13,403,3),#以下是运营部门
('程咬金','19970312',20000,('程咬银','20130311',19000,('程咬铜','20150411',18000,('程咬铁','20140512',17000,3)
;

一查询语法

字段1,字段2... 表名条件字段筛选字段 limit 限制条数

二关键字的执行优先级

 字段1,字段2...  表名  约束条件   字段  过滤条件   字段 limit 限制条数

上面这个查询语句的执行顺序如下：

1：先执行from,从硬盘中读取找到表

2：如果有where,则再通过where后面的约束条件，去文件/表中取出一条条数据（没有where则是取出表中的所有数据）

3：where执行完后，如果有group by，则将取出来的数据按group by后面的字段名分组，如果没有group by则默认将第二步中的数据分为一组

4：按照select后的字段得到一张新的虚拟表，如果有聚合函数，则将组内数据进行聚合，

5：再进一步通过having后面的条件过滤数据（如果有having的话）

6：查出结果 select

7：去重distinct

8.将结果按条件排序：order by

9.限制结果的显示条数

详细过程：请点击此处

三简单查询

</span><span style="color: #0000ff;"&gt;select</span> <span style="color: #808080;"&gt;*</span> <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee;

</span><span style="color: #0000ff;"&gt;select</span> name,salary <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee;

避免重复(去重) distinct

</span><span style="color: #0000ff;"&gt;select</span> <span style="color: #0000ff;"&gt;distinct</span> post <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee;

通过四则运算查询

</span><span style="color: #0000ff;"&gt;select</span> name as 名字,salary<span style="color: #808080;"&gt;*</span><span style="color: #800000; font-weight: bold;"&gt;12</span> as 年薪 <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee;    #在字段名后面as新名称，可以重新定义其在表中的名字，也可以省略as直接写新名字
</span><span style="color: #0000ff;"&gt;select</span> name,salary<span style="color: #808080;"&gt;*</span><span style="color: #800000; font-weight: bold;"&gt;12</span> <span style="color: #0000ff;"&gt;as</span> Annual_salary <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee;
</span><span style="color: #0000ff;"&gt;select</span> name,salary<span style="color: #808080;"&gt;*</span><span style="color: #800000; font-weight: bold;"&gt;12</span> Annual_salary <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee;

定义显示格式

CONCAT() 函数用于连接字符串
select CONCAT('姓名: ',' 年薪: ',salary*12) as Annual_salary from employee;

CONCAT_WS() 第一个参数为分隔符
select CONCAT_WS(':',salary*12) as Annual_salary from employee;

四 where约束

强调：where是一种约束条件，mysql会拿着where指定的条件去表中取数据，而having则是在取出数据后进行过滤

where字句中可以使用：

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

# name employee post2:多条件查询

</span><span style="color: #0000ff;"&gt;select</span> name,salary <span style="color: #0000ff;"&gt;from</span> employee <span style="color: #0000ff;"&gt;where</span> post<span style="color: #808080;"&gt;=</span><span style="color: #ff0000;"&gt;'</span><span style="color: #ff0000;"&gt;teacher</span><span style="color: #ff0000;"&gt;'</span> <span style="color: #808080;"&gt;and</span> salary<span style="color: #808080;"&gt;></span><span style="color: #800000; font-weight: bold;"&gt;10000</span><span style="color: #000000;"&gt;;

3:关键字BETWEEN AND

<span style="color: #0000ff;"&gt;select</span> name,salary <span style="color: #0000ff;"&gt;from</span> employee  <span style="color: #0000ff;"&gt;where</span> salary <span style="color: #808080;"&gt;between</span> <span style="color: #800000; font-weight: bold;"&gt;10000</span> <span style="color: #808080;"&gt;and</span> <span style="color: #800000; font-weight: bold;"&gt;20000</span>;    #<span style="color: #800000; font-weight: bold;"&gt;10000</span>&le;结果&le;<span style="color: #800000; font-weight: bold;"&gt;20000</span>

<span style="color: #0000ff;"&gt;select</span> name,salary <span style="color: #0000ff;"&gt;from</span> employee  <span style="color: #0000ff;"&gt;where</span> salary <span style="color: #808080;"&gt;not</span> <span style="color: #808080;"&gt;between</span> <span style="color: #800000; font-weight: bold;"&gt;10000</span> <span style="color: #808080;"&gt;and</span> <span style="color: #800000; font-weight: bold;"&gt;20000</span>;    # 结果&le;<span style="color: #800000; font-weight: bold;"&gt;10000</span>，结果&ge;<span style="color: #800000; font-weight: bold;"&gt;20000</span><span style="color: #000000;"&gt;

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

</span><span style="color: #0000ff;"&gt;select</span> name,post_comment <span style="color: #0000ff;"&gt;from</span> employee   <span style="color: #0000ff;"&gt;where</span> post_comment <span style="color: #0000ff;"&gt;is</span> <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;

</span><span style="color: #0000ff;"&gt;select</span> name,post_comment <span style="color: #0000ff;"&gt;from</span> employee  <span style="color: #0000ff;"&gt;where</span> post_comment <span style="color: #0000ff;"&gt;is</span> <span style="color: #808080;"&gt;not</span> <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;

</span><span style="color: #0000ff;"&gt;select</span> name,post_comment <span style="color: #0000ff;"&gt;from</span> employee   <span style="color: #0000ff;"&gt;where</span> post_comment<span style="color: #808080;"&gt;=</span><span style="color: #ff0000;"&gt;''</span>; 注意<span style="color: #ff0000;"&gt;''</span><span style="color: #000000;"&gt;是空字符串，不是null
ps：
    执行
    </span><span style="color: #0000ff;"&gt;update</span> employee <span style="color: #0000ff;"&gt;set</span> post_comment<span style="color: #808080;"&gt;=</span><span style="color: #ff0000;"&gt;''</span> <span style="color: #0000ff;"&gt;where</span> id<span style="color: #808080;"&gt;=</span><span style="color: #800000; font-weight: bold;"&gt;2</span><span style="color: #000000;"&gt;;
    再用上条查看，就会有结果了

5:关键字IN集合查询

</span><span style="color: #0000ff;"&gt;select</span> name,salary <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee 
    </span><span style="color: #0000ff;"&gt;where</span> salary<span style="color: #808080;"&gt;=</span><span style="color: #800000; font-weight: bold;"&gt;3000</span> <span style="color: #808080;"&gt;or</span> salary<span style="color: #808080;"&gt;=</span><span style="color: #800000; font-weight: bold;"&gt;3500</span> <span style="color: #808080;"&gt;or</span> salary<span style="color: #808080;"&gt;=</span><span style="color: #800000; font-weight: bold;"&gt;4000</span> <span style="color: #808080;"&gt;or</span> salary<span style="color: #808080;"&gt;=</span><span style="color: #800000; font-weight: bold;"&gt;9000</span><span style="color: #000000;"&gt; ;

</span><span style="color: #0000ff;"&gt;select</span> name,salary <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee 
    </span><span style="color: #0000ff;"&gt;where</span> salary <span style="color: #808080;"&gt;in</span> (<span style="color: #800000; font-weight: bold;"&gt;3000</span>,<span style="color: #800000; font-weight: bold;"&gt;4000</span>,<span style="color: #800000; font-weight: bold;"&gt;9000</span><span style="color: #000000;"&gt;) ;

</span><span style="color: #0000ff;"&gt;select</span> name,salary <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee 
    </span><span style="color: #0000ff;"&gt;where</span> salary <span style="color: #808080;"&gt;not</span> <span style="color: #808080;"&gt;in</span> (<span style="color: #800000; font-weight: bold;"&gt;3000</span>,<span style="color: #800000; font-weight: bold;"&gt;9000</span><span style="color: #000000;"&gt;) ;

6:关键字LIKE模糊查询

通配符&rsquo;</span><span style="color: #808080;"&gt;%</span><span style="color: #000000;"&gt;&rsquo;
</span><span style="color: #0000ff;"&gt;select</span> <span style="color: #808080;"&gt;*</span> <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee 
        </span><span style="color: #0000ff;"&gt;where</span> name <span style="color: #808080;"&gt;like</span> <span style="color: #ff0000;"&gt;'</span><span style="color: #ff0000;"&gt;eg%</span><span style="color: #ff0000;"&gt;'</span><span style="color: #000000;"&gt;;  #匹配name为eg开头的数据

</span><span style="color: #0000ff;"&gt;select</span> <span style="color: #808080;"&gt;*</span> <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee 
        </span><span style="color: #0000ff;"&gt;where</span> name <span style="color: #808080;"&gt;like</span> <span style="color: #ff0000;"&gt;'</span><span style="color: #ff0000;"&gt;%e%</span><span style="color: #ff0000;"&gt;'</span><span style="color: #000000;"&gt;;  #匹配name中含有e的数据

通配符&rsquo;_&rsquo;
</span><span style="color: #0000ff;"&gt;select</span> <span style="color: #808080;"&gt;*</span> <span style="color: #0000ff;"&gt;from</span><span style="color: #000000;"&gt; employee 
        </span><span style="color: #0000ff;"&gt;where</span> name <span style="color: #808080;"&gt;like</span> <span style="color: #ff0000;"&gt;'</span><span style="color: #ff0000;"&gt;al__</span><span style="color: #ff0000;"&gt;'</span>;  #匹配name为al开头，且为长度为4个字符的数据</pre>

五分组查询:GROUP BY

post employee 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;#按照岗位分组，并查看每个组有多少人

强调：

#参考链接：http:www.ywnds.com?p id, tt (): Expression # list clause nonaggregated which functionally dependent columns clause; this incompatible sql_mode查看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模式，如下操作：

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

！！！注意

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

1234567. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

#题目2：
mysql> select post,count(id) from employee group by post;
+-----------------------------------------+-----------+
| post | count(id) |
+-----------------------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-----------+

#题目3：
mysql> select sex,count(id) from employee group by sex;
+--------+-----------+
| sex | count(id) |
+--------+-----------+
| male | 10 |
| female | 8 |
+--------+-----------+

#题目4：
mysql> select post,avg(salary) from employee group by post;
+-----------------------------------------+---------------+
| post | avg(salary) |
+-----------------------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
+-----------------------------------------+---------------+

#题目5
mysql> select post,max(salary) from employee group by post;
+-----------------------------------------+-------------+
| post | max(salary) |
+-----------------------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+-----------------------------------------+-------------+

#题目6
mysql> select post,min(salary) from employee group by post;
+-----------------------------------------+-------------+
| post | min(salary) |
+-----------------------------------------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+-----------------------------------------+-------------+

#题目七
mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 110920.077000 |
| female | 7250.183750 |
+--------+---------------+

六使用聚合函数查询

先from找到表

再用where的条件约束去表中取出记录

然后进行分组group by，没有分组则默认一组

然后进行聚合

最后select出结果

**) FROM employee WHERE depart_id=1=3;

七 HAVING过滤

HAVING与WHERE在语法上是一样的

employee salary employee salary ;

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

group by > 聚合函数 > having select count(id) employee where salary > 10000; select count(id) employee having salary > 10000;10000的过滤

#以上两条sql的顺序是
1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(id)--->select执行查看组内id数目
2:找到表employee--->没有分组则默认一组执行聚合count(id)---->having 基于上一步聚合的结果（此时只有count(id)字段了）进行salary>10000的过滤，很明显，根本无法获取到salary字段

验证不同之处

#验证之前再次强调：执行优先级从高到低：聚合函数 (id) employee salary (id) employee salary ;#错误，分析：先执行聚合count(id)，后执行having过滤，无法对id进行salary以上两条sql的顺序是

1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(id)--->select执行查看组内id数目
2:找到表employee--->没有分组则默认一组执行聚合count(id)---->having 基于上一步聚合的结果（此时只有count(id)字段了）进行salary>10000的过滤，很明显，根本无法获取到salary字段

其他需要注意的问题

post,group_concat(name) employee post salary post,group_concat(name) employee post (salary) ;

. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

post,group_concat(name),(id) employee post (id) post group_concat(name) (id) teacher egon post,(salary) employee post (salary) post (salary) operation teacher post,(salary) employee post (salary) (salary) post (salary) operation

八查询排序:ORDER BY

employee employee salary employee salary 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
SELECT * from employee
ORDER BY age,salary DESC;

. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

employee age ,hire_date 题目2

mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+

题目3

mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| teacher | 151842.901429 |
| operation | 16800.026000 |
+-----------+---------------+

九限制查询的记录数:LIMIT

employee salary

</span><span style="color: #0000ff;"&gt;SELECT</span> <span style="color: #808080;"&gt;*</span> <span style="color: #0000ff;"&gt;FROM</span> employee <span style="color: #0000ff;"&gt;ORDER</span> <span style="color: #0000ff;"&gt;BY</span> salary <span style="color: #0000ff;"&gt;DESC</span><span style="color: #000000;"&gt;
    LIMIT </span><span style="color: #800000; font-weight: bold;"&gt;0</span>,<span style="color: #800000; font-weight: bold;"&gt;5</span><span style="color: #000000;"&gt;; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

</span><span style="color: #0000ff;"&gt;SELECT</span> <span style="color: #808080;"&gt;*</span> <span style="color: #0000ff;"&gt;FROM</span> employee <span style="color: #0000ff;"&gt;ORDER</span> <span style="color: #0000ff;"&gt;BY</span> salary <span style="color: #0000ff;"&gt;DESC</span><span style="color: #000000;"&gt;
    LIMIT </span><span style="color: #800000; font-weight: bold;"&gt;5</span>,<span style="color: #800000; font-weight: bold;"&gt;5</span>; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条</pre>

mysql> select * from employee limit 0,5;
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from employee limit 5,5;
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from employee limit 10,5;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)

十使用正则表达式查询

employee name REGEXP 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$';

<span style="color: #0000ff;">select <span style="color: #808080;"> <span style="color: #0000ff;">from employee <span style="color: #0000ff;">where name regexp <span style="color: #ff0000;">'<span style="color: #ff0000;">^jin.[gn]$<span style="color: #ff0000;">';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

单表查询

查看表结构

插入记录

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

一查询语法

二关键字的执行优先级

三简单查询

避免重复(去重) distinct

通过四则运算查询

定义显示格式

四 where约束

<span style="color: #800000; font-weight: bold;">3:关键字BETWEEN <span style="color: #808080;">AND

<span style="color: #800000; font-weight: bold;">4:关键字IS <span style="color: #0000ff;">NULL<span style="color: #000000;">(判断某个字段是否为空不能用等号，需要用IS)

<span style="color: #800000; font-weight: bold;">5<span style="color: #000000;">:关键字IN集合查询

<span style="color: #800000; font-weight: bold;">6<span style="color: #000000;">:关键字LIKE模糊查询

五分组查询:GROUP BY

去掉ONLY_FULL_GROUP_BY模式，如下操作：

！！！注意

六使用聚合函数查询

七 HAVING过滤

八查询排序:ORDER BY

题目3

九限制查询的记录数:LIMIT

十使用正则表达式查询

单表查询

查看表结构

插入记录

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

一 查询语法

二 关键字的执行优先级

三 简单查询

避免重复(去重) distinct

通过四则运算查询

定义显示格式

四 where约束

<span style="color: #800000; font-weight: bold;">3:关键字BETWEEN <span style="color: #808080;">AND

<span style="color: #800000; font-weight: bold;">4:关键字IS <span style="color: #0000ff;">NULL<span style="color: #000000;">(判断某个字段是否为空不能用等号，需要用IS)

<span style="color: #800000; font-weight: bold;">5<span style="color: #000000;">:关键字IN集合查询

<span style="color: #800000; font-weight: bold;">6<span style="color: #000000;">:关键字LIKE模糊查询

五 分组查询:GROUP BY

去掉ONLY_FULL_GROUP_BY模式，如下操作：

！！！注意

六 使用聚合函数查询

七 HAVING过滤

八 查询排序:ORDER BY

题目3

九 限制查询的记录数:LIMIT

十 使用正则表达式查询

一查询语法

二关键字的执行优先级

三简单查询

五分组查询:GROUP BY

六使用聚合函数查询

八查询排序:ORDER BY

九限制查询的记录数:LIMIT

十使用正则表达式查询