MySQL数据库：SQl语句查询关键字

目录

• SQl语句查询关键字
  • 1.select
    • ①字符串拼接—concat(str1,str2,...)与concat_ws(separator,str1,str2,...)
  • 2.from
  • 3.where筛选
    • ①比较运算 - <、>、<=、>=、!=、<>
    • ② 逻辑运算 - and、or、not
    • ③ 成员运算 - in、not in
    • ④ 区间 - between
    • ⑤ 模糊查询 - like
      • 1）匹配任意多个字符：%
      • 2）匹配任意单个字符：_
    • ⑥身份运算符is
  • 4.group by 分组
    • （1）分组的含义
    • （2）分组的目的：
    • （3）聚合函数
    • （4）如何分组
      • 1）例1:将员工按照部门分组
      • 2）例2：获取每个部门的最高工资
    • *as起别名
      • 3）例3：一次性获取部门薪资统计
      • 4）例4：统计每个部门的人数count()
      • 5）例5：统计每个部门的部门名称以及部门下的员工姓名group_concat()
  • 5.having过滤
    • • （1）having和where的区别
  • 6.distinct去重
  • 7.查询关键字之order by 排序
    • （1）单个字段排序
    • （2）多个字段排序
  • 8.limit分页
    • （1）limit num 直接限制展示的条数
    • （2）limit num1,num2 限制展示范围
    • （3）limit展示的结果与筛选结果的顺序有关
    • （4）通过limit来限制查询的信息
  • 9.regexp正则表达式

SQl语句查询关键字

执行的优先级

优先级	关键字	作用
1	from	指定要查询的表信息
2	where	规定查询条件，然后查询并筛选
3	group by	根据指定字段进行分组，如果没有指定字段，则整个表为一组
4	having	指定查询的条件，筛选数据（二次筛选用在where之后）
5	select	指定要查询的信息
6	distinct	将数据去重
7	order by	对查询的结果进行排序
8	limit	限制查询结果展示的数量

1.select

指定要查询的信息

select *   查找所有字段
select name  查找name字段
select char_length(name)   支持对字段做处理

①字符串拼接—concat(str1,str2,...)与concat_ws(separator,str1,str2,...)

1）concat()拼接任意字符串

# concat()
select concat('姓名：',name,'|','年龄：',age) from emp;

+----------------------------------------------+
| concat('姓名：',name,'|','年龄：',age)       |
+----------------------------------------------+
| 姓名：jason|年龄：18                         |
| 姓名：tom|年龄：78                           |
| 姓名：kevin|年龄：81                         |
| 姓名：tony|年龄：73                          |
| 姓名：owen|年龄：28                          |
| 姓名：jack|年龄：18                          |
| 姓名：jenny|年龄：18                         |
| 姓名：sank|年龄：48                          |
| 姓名：哈哈|年龄：48                          |
| 姓名：呵呵|年龄：38                          |
| 姓名：西西|年龄：18                          |
| 姓名：乐乐|年龄：18                          |
| 姓名：拉拉|年龄：28                          |
| 姓名：僧龙|年龄：28                          |
| 姓名：程咬金|年龄：18                        |
| 姓名：程咬银|年龄：18                        |
| 姓名：程咬铜|年龄：18                        |
| 姓名：程咬铁|年龄：18                        |
+----------------------------------------------+
18 rows in set (0.01 sec)

2）concat_ws() 指定一个连接符号拼接字符串，第一个参数separator是指定的连接符

# concat_ws()
mysql> select concat('信息>>>:',name,age) from emp;
+-------------------------------+
| concat('信息>>>:',name,age)   |
+-------------------------------+
| 信息>>>:jason18               |
| 信息>>>:tom78                 |
| 信息>>>:kevin81               |
| 信息>>>:tony73                |
| 信息>>>:owen28                |
| 信息>>>:jack18                |
| 信息>>>:jenny18               |
| 信息>>>:sank48                |
| 信息>>>:哈哈48                |
| 信息>>>:呵呵38                |
| 信息>>>:西西18                |
| 信息>>>:乐乐18                |
| 信息>>>:拉拉28                |
| 信息>>>:僧龙28                |
| 信息>>>:程咬金18              |
| 信息>>>:程咬银18              |
| 信息>>>:程咬铜18              |
| 信息>>>:程咬铁18              |
+-------------------------------+
18 rows in set (0.00 sec)

2.from

指定要查询的表信息

from mysql.user
from t1

SQL语句中关键字的执行顺序和编写顺序并不是一致的

比如我们编写SQL语句查找数据的时候，select写在from之前，但是先执行的是from

select id from usertable;
# 我们先写的select在写from 但是先执行的是from

3.where筛选

where+条件可以筛选出我们想要的数据

• 编写SQL语句的技巧

SQL语句的编写需要反复修改，一次性写完容易出错

针对select后面的字段名可以先用填写*占位，写完在修改

而在实际情况中，select后面很少直接写*，因为*表示所有，用*查询表中的字段和数据非常浪费资源、效率也很低

①比较运算 - <、>、<=、>=、!=、<>

通过使用比较运算符号筛选数据

# 查询id>=3的数据
select * from emp where id>=3；

② 逻辑运算 - and、or、not

通过逻辑运算符连接比较运算符

# 查询年纪 大于40 以及 小于20的员工
select * from emp where age>40 or age<20;

# 查询id小于3大于6的数据

select * from emp where id<3 or id>6;
-- 可以去反not
select * from emp where id not between 3 and 6;

③ 成员运算 - in、not in

在特定的值中获取：in

取反：not in

# 查询薪资是 20000 或者18000 或者17000

select * from emp where salary=20000 or salary=18000 or salary=17000;

-- 支持成员运算 in
select * from emp where salary in (20000,18000,17000);

④ 区间 - between

取出某一区间：between ... and ...

-- 支持逻辑运算符 between
select * from emp where salary between 999 and 3001;

⑤ 模糊查询 - like

条件不够精确的查询 称之为 模糊查询

1）匹配任意多个字符：%

• %n%可以匹配 字母n，在字符的 开头/结尾/中间，且不限个数

如： %p% 可以匹配 字母p 在字符的 开头/结尾/中间，且不限个数
       如 p pick poop oppo loop

• %n 只可以匹配结尾是n 的字符

2）匹配任意单个字符：_

• _a___ 只匹配一个字母a在第2位的5位字符

# 查询员工姓名是由四个字符组成的员工姓名和薪资

-- 一个下划线表示一个字符
select * from emp where name like '__';

字符长度也可以通过char_length() 函数去查询

通过help 方法名来查看内置方法，查看帮助手册学习MySQL内的方法

⑥身份运算符is

NULL值的判断不能用=而需要用 is

为什么数据库筛选空字节要用is NULL而不是 =NULL

参见这个博客
https://www.cnblogs.com/zhoujinyi/archive/2012/10/17/2726462.html
总结：
null不占用内存空间，是一个标志位判断。

''数据虽然不需要占用任何存储空间，但是在变长字段列表里面还是需要占用一个字节<毕竟还是一个''值>

# 查看岗位是销售的员工

select * from emp where post_comment=NULL; -- 不可以

-- 通过is判断
select * from emp where post_comment is NULL; -- 可以

4.group by 分组

分组行为发生在where条件之后，我们可以将查询到的记录按照某个相同字段进行归类，一般分组都会配合聚合函数进行使用。

需要注意的是select语句是排在group by条件之后的，因此聚合函数也能在select语句中使用。

（1）分组的含义

根据指定字段进行分组，如果没有指定字段，则整个表为一组

如：将班级学生按照性别分组

（2）分组的目的：

​ 为了更好的统计相关数据

（3）聚合函数

​ 专门用于分组之后的数据统计的函数

聚合函数可以在where执行后的所有语句中使用，比如having，select等。

函数名	用途
max()	最大值
min()	最小值
sum()	求和
avg()	平均值
count()	计数

（4）如何分组

1）例1:将员工按照部门分组

当我们使用SQL语句 group by 分组时

select * from emp group by post;

在MySQL 5.6 版本中，不报错

在MySQL5.7及8.0默认都会直接报错

报错原因：分组之后select后面默认只能填写分组的依据，不能再写其他字符段

# 正确的写法
select post from emp group by post; -- 在select后面要写分组的字段名
select name from emp group by name;

分组之后默认的最小单位，应该是组筛选出来的范围也就是组，而不应该在是组内单个数据单个字段

# 正确的查询
mysql> select post from emp group by post;
+-----------------------------+
| post                        |
+-----------------------------+
| operation                   |
| sale                        |
| teacher                     |
| 浦东第一帅形象代言          |
+-----------------------------+
4 rows in set (0.00 sec)

# 报错
mysql> select * from emp group by post;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'empdb.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2）例2：获取每个部门的最高工资

需求中出现 关键字 比如最高、平均...

==>使用聚合函数

# max获取得最高工资
select post,max(salary) from emp group by post;

mysql> select post,max(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | max(salary) |
+-----------------------------+-------------+
| operation                   |    20000.00 |
| sale                        |     4000.33 |
| teacher                     |  1000000.31 |
| 浦东第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
4 rows in set (0.01 sec)

# 获取每个部门最小的年龄
select post,min(age) from emp group by post;

*as起别名

针对sql语句执行后的结果，我们可以通过as来起别名修改名称字段

select post as '部门',max(salary) as '最高薪资' from emp group by post;

mysql> select post as '部门',max(salary) as '最高薪资' from emp group by post;
+-----------------------------+--------------+
| 部门                        | 最高薪资     |
+-----------------------------+--------------+
| operation                   |     20000.00 |
| sale                        |      4000.33 |
| teacher                     |   1000000.31 |
| 浦东第一帅形象代言          |      7300.33 |
+-----------------------------+--------------+
4 rows in set (0.00 sec)

# 起别名
mysql> select age as '年龄',min(salary) as '最低薪' from emp group by age;
+--------+------------+
| 年龄   | 最低薪     |
+--------+------------+
|     18 |    1000.37 |
|     28 |    2100.00 |
|     38 |    2000.35 |
|     48 |    3000.13 |
|     73 |    3500.00 |
|     78 | 1000000.31 |
|     81 |    8300.00 |
+--------+------------+
7 rows in set (0.00 sec)

3）例3：一次性获取部门薪资统计

连续跟多个聚合函数并用as起别名

• 用职位分组

select post,max(salary) as '最高薪水',min(salary) as '最少薪水',avg(salary) as '平均薪水',sum(salary) as '月支出' from emp group by post;

mysql> select post,max(salary) as '最高薪水',min(salary) as '最少薪水',avg(salary) as '平均薪水',sum(salary) as '月支出' from emp group by post;
+-----------------------------+--------------+--------------+---------------+------------+
| post                        | 最高薪水     | 最少薪水     | 平均薪水      | 月支出     |
+-----------------------------+--------------+--------------+---------------+------------+
| operation                   |     20000.00 |     10000.13 |  16800.026000 |   84000.13 |
| sale                        |      4000.33 |      1000.37 |   2600.294000 |   13001.47 |
| teacher                     |   1000000.31 |      2100.00 | 151842.901429 | 1062900.31 |
| 浦东第一帅形象代言          |      7300.33 |      7300.33 |   7300.330000 |    7300.33 |
+-----------------------------+--------------+--------------+---------------+------------+
4 rows in set (0.01 sec)

• 用年龄分组

mysql> select age,min(salary) as '最低工资',max(salary) as '最高工资',avg(salary) as '平均工资' from emp group by age;
+-----+--------------+--------------+----------------+
| age | 最低工资     | 最高工资     | 平均工资       |
+-----+--------------+--------------+----------------+
|  18 |      1000.37 |     30000.00 |   13811.221111 |
|  28 |      2100.00 |     10000.13 |    5366.820000 |
|  38 |      2000.35 |      2000.35 |    2000.350000 |
|  48 |      3000.13 |     10000.00 |    6500.065000 |
|  73 |      3500.00 |      3500.00 |    3500.000000 |
|  78 |   1000000.31 |   1000000.31 | 1000000.310000 |
|  81 |      8300.00 |      8300.00 |    8300.000000 |
+-----+--------------+--------------+----------------+

4）例4：统计每个部门的人数count()

count()统计的时候通常用唯一标识字段作为统计的依据

select post,count(id) from emp group by post;

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)

5）例5：统计每个部门的部门名称以及部门下的员工姓名group_concat()

group_concat()分组之后其他字段的筛选与拼接

# 错误 不符合SQL语句的语法
select post,name from emp group by post;

# 正确写法，用group_concat()来拼接筛选后的结果
select post,group_concat(name) from emp group by post;

mysql> select post,group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post                        | group_concat(name)                             |
+-----------------------------+------------------------------------------------+
| operation                   | 僧龙,程咬金,程咬银,程咬铜,程咬铁               |
| sale                        | 哈哈,呵呵,西西,乐乐,拉拉                       |
| teacher                     | tom,kevin,tony,owen,jack,jenny,sank            |
| 浦东第一帅形象代言          | jason                                          |
+-----------------------------+------------------------------------------------+
4 rows in set (0.00 sec)

• 统计各个性别的人名

# 用 group_concat() 与 group by
select gender,group_concat(name) from emp group by gender;

+--------+-----------------------------------------------------------------+
| gender | group_concat(name)                                              |
+--------+-----------------------------------------------------------------+
| male   | jason,tom,kevin,tony,owen,jenny,sank,僧龙,程咬金,程咬铜         |
| female | jack,哈哈,呵呵,西西,乐乐,拉拉,程咬银,程咬铁                     |
+--------+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

• group_concat()拼接字段与字符串

select post,group_concat(name,'|',age) from emp group by post;

mysql> select post,group_concat(name,'|',age) from emp group by post;
+-----------------------------+---------------------------------------------------------------+
| post                        | group_concat(name,'|',age)                                    |
+-----------------------------+---------------------------------------------------------------+
| operation                   | 僧龙|28,程咬金|18,程咬银|18,程咬铜|18,程咬铁|18               |
| sale                        | 哈哈|48,呵呵|38,西西|18,乐乐|18,拉拉|28                       |
| teacher                     | tom|78,kevin|81,tony|73,owen|28,jack|18,jenny|18,sank|48      |
| 浦东第一帅形象代言          | jason|18                                                      |
+-----------------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

5.having过滤

指定查询的条件，筛选数据（二次筛选用在where之后）

（1）having和where的区别

相同：having 与 where 本质是一样的，都是用来对数据做筛选

不同： where用在分组之前(首次筛选)

​ having用在分组之后(二次筛选)

1）统计各部门年龄在30以上的员工工资，并且保留平均工资大于10000的部门

对于复杂的SQL语句，我们可以先分解成几步，然后将每条SQL的结果可以看成一张表，并基于这个结果再去进行复杂的操作

比如：我们可以分布编写，复杂度从简入难

编写顺序：1） where 条件 2） group by 分组 3）having 条件

# 1 先筛选出所有年龄大于30岁的员工数据
select * from emp where age >30;

# 2 在对筛选出来的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age >30 group by post;

# 3 针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age >30 group by post having avg(salary) > 10000;

----结果
+---------+---------------+
| post    | avg(salary)   |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.00 sec)

• 按年龄分组

select age,avg(salary) as avg_salary -- 起别名
from emp 
where id>15 -- 第一次筛选
group by age 
having avg_salary >10000; -- 二次筛选

mysql> select age,avg(salary) as avg_salary from emp where id>15
    -> group by age having avg_salary >10000;
+-----+--------------+
| age | avg_salary   |
+-----+--------------+
|  18 | 18000.000000 |
+-----+--------------+
1 row in set (0.00 sec)

6.distinct去重

distinct用于数据去重

去重的条件是：存在数据必须一模一样才可以去重

当distinct后面有多个关键字的时候，针对的是多个字段组合后的结果去重

# age和id的组合不重复
select distinct age,id from emp;

mysql> select distinct age,id from emp;
+-----+----+
| age | id |
+-----+----+
|  18 |  1 |
|  78 |  2 |
|  81 |  3 |
|  73 |  4 |
|  28 |  5 |
|  18 |  6 |
|  18 |  7 |
|  48 |  8 |
|  48 |  9 |
|  38 | 10 |
|  18 | 11 |
|  18 | 12 |
|  28 | 13 |
|  28 | 14 |
|  18 | 15 |
|  18 | 16 |
|  18 | 17 |
|  18 | 18 |
+-----+----+
18 rows in set (0.00 sec)

# 去重 age和salary的组合
select distinct age,salary from emp;
+-----+------------+
| age | salary     |
+-----+------------+
|  18 |    7300.33 |
|  78 | 1000000.31 |
|  81 |    8300.00 |
|  73 |    3500.00 |
|  28 |    2100.00 |
|  18 |    9000.00 |
|  18 |   30000.00 |
|  48 |   10000.00 |
|  48 |    3000.13 |
|  38 |    2000.35 |
|  18 |    1000.37 |
|  18 |    3000.29 |
|  28 |    4000.33 |
|  28 |   10000.13 |
|  18 |   20000.00 |
|  18 |   19000.00 |
|  18 |   18000.00 |
|  18 |   17000.00 |
+-----+------------+
18 rows in set (0.00 sec)

7.查询关键字之order by 排序

ordery by用于对查询结果进行排序

默认的排序是按照主键进行排序的

（1）单个字段排序

# 默认升序
select * from emp order by age;
-- asc可以升序

# desc可以改成降序
select * from emp order by age desc;

（2）多个字段排序

先按照一个条件升序排列，相同的情况下再按照薪资降序排列

select * from emp order by age,salary desc; -- desc 修改为倒叙

1）例1：统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

1# 先筛选出所有年龄大于10岁的员工
select * from emp where age >10;

2# 再按照部门分组
select post,avg(salary) from emp where age >10 group by post;

3# 针对分组的结果做二次筛选
select post,avg(salary) from emp where age >10 group by post having avg(salary)>1000;

4# 最后按照指定字段排序
select post,avg(salary) from emp where age >10 group by post having avg(salary)>1000 order by avg(salary);

mysql> select post,avg(salary) from emp where age >10 group by post having avg(salary)>1000 order by avg(salary);
+-----------------------------+---------------+
| post                        | avg(salary)   |
+-----------------------------+---------------+
| sale                        |   2600.294000 |
| 浦东第一帅形象代言          |   7300.330000 |
| operation                   |  16800.026000 |
| teacher                     | 151842.901429 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)

当一条sql语句中重复使用聚合函数计算的结果的时候，我们可以通过as起别名节省操作

用as 起别名，可以节省调用次数，也就是节省了底层运行效率

# 用as 起别名，节省调用次数，也就是节省了底层运行效率
select post,avg(salary) as asg from emp where age >10 group by post having asg>1000 order by asg;

mysql> select post,avg(salary) as asg from emp where age >10 group by post having asg>1000 order by asg;
+-----------------------------+---------------+
| post                        | asg           |
+-----------------------------+---------------+
| sale                        |   2600.294000 |
| 浦东第一帅形象代言          |   7300.330000 |
| operation                   |  16800.026000 |
| teacher                     | 151842.901429 |
+-----------------------------+---------------+
4 rows in set (0.01 sec)

8.limit分页

limit用于控制显示的条数

当表中数据特别多的情况下，不会一次性展示所有的数据

比如：网站的分页出来，一次只展示固定数量的数据

（1）limit num 直接限制展示的条数

limit num

mysql> select * from emp limit 3;
+----+-------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name  | gender | age | hire_date  | post                        | post_comment | salary     | office | depart_id |
+----+-------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
|  1 | jason | male   |  18 | 2017-03-01 | 浦东第一帅形象代言          | NULL         |    7300.33 |    401 |         1 |
|  2 | tom   | male   |  78 | 2015-03-02 | teacher                     | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin | male   |  81 | 2013-03-05 | teacher                     | NULL         |    8300.00 |    401 |         1 |
+----+-------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
3 rows in set (0.00 sec)

（2）limit num1,num2 限制展示范围

limit num1,num2 表示至展示结果中的 排序为num1-num2的数据

mysql> select * from emp limit 3,6;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | gender | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  4 | tony   | male   |  73 | 2014-07-01 | teacher | NULL         |  3500.00 |    401 |         1 |
|  5 | owen   | male   |  28 | 2012-11-01 | teacher | NULL         |  2100.00 |    401 |         1 |
|  6 | jack   | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jenny  | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
|  8 | sank   | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |
|  9 | 哈哈   | female |  48 | 2015-03-11 | sale    | NULL         |  3000.13 |    402 |         2 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
6 rows in set (0.01 sec)

（3）limit展示的结果与筛选结果的顺序有关

比如：where筛选的结果，按照主键的顺序来排列的

# 表示查询 id name age 信息，并筛选出其中age>40的数据，并只展示其中的第3条到第6条消息
select id,name,age from emp where age > 40 limit 3,6;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  8 | sank   |  48 |
|  9 | 哈哈   |  48 |
+----+--------+-----+
2 rows in set (0.01 sec)

（4）通过limit来限制查询的信息

查询工资最高的人的详细信息

# 错误sql语句，比如用了聚合函数后不能对其他字段进行操作
select max(salary) from emp;

# 正确
select * from emp order by salary desc limit 1;

# 查询年龄分组中最高的工资，并打印 拼接每组的name和ID，且限制展示两条
select age,group_concat(name,id),max(salary) from emp group by age limit 2; 

9.regexp正则表达式

SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询

select * from emp where name regexp '^j.';

• 正则表达式报错

select * from emp where name regexp '^j.*?(n|y)$';

在MySQL中并非所有的正则表达式都可以使用，所以我们需要看是否编写的正则表达式符合MySQL的正则要求