MySQL数据库：SQl语句查询关键字
SQl语句查询关键字
执行的优先级
优先级关键字作用1from指定要查询的表信息2where规定查询条件，然后查询并筛选3group by根据指定字段进行分组，如果没有指定字段，则整个表为一组4having指定查询的条件，筛选数据（二次筛选用在where之后）5select指定要查询的信息6distinct将数据去重7order by对查询的结果进行排序8limit限制查询结果展示的数量
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的正则要求
链接：https://www.dianjilingqu.com/622336.html
