SQL语句查询关键字 多表查询

目录

• SQL语句查询关键字
  • select
  • from
  • 编写顺序和查询数据
• 前期数据准备
• 编写SQL语句的小技巧
• 查询关键字之筛选 where
  • 逻辑运算符 not and or
  • between
  • not between
  • 成员运算符 in
  • 模糊查询 like
    • 匹任意 常用符号%
    • 匹单个 常用符号_
    • 题目
      • 统计字符长度 char_length
      • 查看帮助 help
• NULL 只能用 is 查找
• 查询关键字之分组 group by
  • 聚合函数 max in sum avg count
    • 修改结果集的名字 as
  • 严格模式 only_full_group_by
  • 获取组内数据 group_concat()
    • 多个字段拼接
• 查询关键字之二次筛选 having
  • 代码实现
• 查询关键字之去重 distinct
  • 一模一样才能去重
• 查询关键字之排序 order by
  • 升序 降序 asc desc
  • 依据多个字段排序
  • 代码实现
• 查询关键字之分页 limit
  • 限制展示的条数
  • 读取指定数据
• 聚合函数的问题
• 查询关键字之正则表达式 regexp
• 多表查询的思路
  • 建立逻辑层面上的关系
  • 笛卡尔积现象
  • 筛选时明确来自哪个表
• 练习
• SQL执行顺序图

SQL语句查询关键字

select

指定需要查询的字段信息
    select *                  查所有字段
    select name               查name字段
    select char_length(name)  支持对字段做处理

from

指定需要查询的表信息
    from mysql.user
    from t1

编写顺序和查询数据

SQL语句中关键字的执行顺序和编写顺序并不是一致的 可能会错乱
	eg:
    select id,name from userinfo;
 	我们先写的select在写的from 但是执行的时候是先执行的from在执行select

对应关键字的编写顺序和执行顺序我们没必要过多的在意 熟练之后会非常自然的编写
我们只需要把注意力放在每个关键字的功能上即可

写sql和执行sql 的不同 可能是因为英文的语言习惯

前期数据准备

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender 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
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','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);

编写SQL语句的小技巧

1. 针对select后面的字段名可以先用*占位往后写 最后再回来修改
2. 在实际应用中select后面很少直接写* 因为*表示所有 当表中字段和数据都特别多的情况下非常浪费数据库资源
3. 在cmd终端可能会出现显示错乱的现象，在语句后面空格加\G 可以改变显示
4. SQL语句的编写类似于代码的编写 不是一蹴而就的 也需要反反复复的修修补补

ps：SQL语句的编写类似于代码的编写 不是一蹴而就的 也需要反反复复的修修补补

查询关键字之筛选 where

where相当于是对表中的数据加限制条件。

# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6;  支持逻辑运算符
select * from emp where id between 3 and 6;

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000); 支持成员运算

# 3.查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;

# 4.查询员工姓名中包含字母o的员工姓名与薪资
条件不够精确的查询 称之为 模糊查询
  模糊查询的关键字是：
  like
  模糊查询的常用符号：
  %:匹配任意个数的任意字符
   eg:
          %o%   o jason owen loo wwoww
          %o    o asdasdo asdo
  _:匹配单个个数的任意字符
         _o_    aox wob iok 
          o_    oi ok ol
	select * from emp where name like '%o%';

# 5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like '____';
select * from emp where char_length(name) = 4;

# 6.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select * from emp where post_comment=NULL;  		不可以
select * from emp where post_comment is NULL;		可以
"""
在MySQL中也有很多内置方法 我们可以通过查看帮助手册学习
	help 方法名
"""

逻辑运算符 not and or

支持逻辑运算符。

1. 查询id大于等于小于等于6的数据
   
2. 查询薪资是20000或者18000或者17000的数据
   

between

用between也可以实现上述需求：
查询id大于等于小于等于6的数据

not between

使用not和between组合使用，查找id不在[3,6]区间的数据。

成员运算符 in

查询薪资是20000或者18000或者17000的数据：

模糊查询 like

条件不够精确的查询 称之为 模糊查询 使用模糊查询的关键字是 like

匹任意 常用符号%

%:匹配任意个数的任意字符

匹单个 常用符号_

_:匹配单个个数的任意字符

题目

1. 查询员工姓名中包含字母o的员工姓名与薪资
   
   %o% 只要名字里带有字母o 就可以被匹配出来
   %o 只能是名字末尾带o
   o% 只能是名字开头带o
   2.查询员工姓名是由四个字符组成的员工姓名与其薪资
   

统计字符长度 char_length

查询员工姓名是由四个字符组成的员工姓名与其薪资：

查看帮助 help

NULL 只能用 is 查找

因为NULL是SQL里面的关键字，所以不能用等号来筛选。

1. 查询岗位描述为空的员工名与岗位号：
   
   示例：
   

查询关键字之分组 group by

分组:按照指定的条件将单个单个的数据组成一个个整体
  	eg:
      将班级学生按照性别分组
     	将全国人民按照民族分组
      将全世界的人按照肤色分组
      
分组的目的是为了更好的统计相关数据
eg:可以很快的统计出下列数据：
      每个班级的男女比例
      每个民族的总占比
      每个部门的平均薪资

聚合函数 max in sum avg count

聚合函数
	专门用于分组之后的数据统计
  	max\min\sum\avg\count
    最大值、最小值、求和、平均值、计数

修改结果集的名字 as

1. 获取每个部门的最高薪资
   

2. 修改结果集展示的名字，变得更好看，使用关键字as
   

3. 关键字as可以不写 直接空格隔开 但是不建议这么做.
   

严格模式 only_full_group_by

输入代码或者修改配置set global sql_mode='strict_trans_tables,only_full_group_by'

select * from emp group by post;
由于分组之后是以组为单位查询，从逻辑上来说，我们应该查询的是组 这时候就不应select * 了。
"""
MySQL5.6默认不会报错
	set global sql_mode='strict_trans_tables,only_full_group_by'
MySQL5.7及8.0默认都会直接报错
	原因是分组之后 select后面默认只能直接填写分组的依据 不能再写其他字段
		select post from emp group by post;
		select age from emp group by age;		
	分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段
"""

1. 将员工数据按照部门分组 非严格模式
   
   很不清晰
2. 使用严格模式
   

获取组内数据 group_concat()

直接写name不能对应组内的name。

这里是想把部门和部门下的员工姓名一同拿出来，这样写是不行的
使用group_concot函数，拿出组内的数据：

支持多个字段：

多个字段拼接

对每一个name都会进行拼接括号内的字符串。

查询关键字之二次筛选 having

having与where本质是一样的 都是用来对数据做筛选
	只不过where用在分组之前(首次筛选)
  having用在分组之后(二次筛选)
 1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
'''
稍微复杂一点的SQL 跟写代码几乎一样 也需要提前想好大致思路
		每条SQL的结果可以直接看成就是一张表 基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'''
	步骤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;
'''
操作完SQL之后会有结果，每条SQL的结果可以直接看成就是一张表
'''

代码实现

1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
步骤1：先筛选出所有年龄大于30岁的员工数据

步骤2：再对筛选出来的数据按照部门分组并统计平均薪资

步骤3:针对分组统计之后的结果做二次筛选

ps:avg聚合函数 重复写不太方便 我们可以节省操作avg(salary) as avg_salary 这样底层调用函数的次数会变少 节省了资源 提高了效率

查询关键字之去重 distinct

语法：

去重有一个必须的条件也是很容易被忽略的条件
	数据必须一模一样才可以去重

select distinct id,age from emp; 关键字针对的是多个字段组合的结果
select distinct age from emp; 
select distinct age,post from emp;

一模一样才能去重

红色框的重复部分全部去掉：

查询关键字之排序 order by

order by对查询到的数据 进行排序 处于 所有操作的末端
1.可以是单个字段排序
select * from emp order by age;  默认升序
select * from emp order by age asc;  默认升序(asc可以省略)
select * from emp order by age desc;  默认升序

2.也可以是多个字段排序
select * from emp order by age,salary desc; 先按照年龄升序排 相同的情况下再按照薪资降序排


统计各部门年龄在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);

"""
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率 代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"""

升序 降序 asc desc

order by排序是默认升序的，升序的关键字是asc。关键字可以忽略：

使用降序：

依据多个字段排序

除了可以对单个字段age排序，还可以对多个字段排序：先按age排序 如果都一样再按照salary排序

代码实现

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

查询关键字之分页 limit

当表中数据特别多的情况下 我们很少会一次性获取所有的数据
	很多网站也是做了分页处理 一次性只能看一点点
  
select * from emp limit 5;  直接限制展示的条数
select * from emp limit 5,5;  从第5条开始往后读取5条

查询工资最高的人的详细信息
'''千万不要关系思维 一看到工资最高就想着用分组聚合'''
select * from emp order by salary desc limit 1;

限制展示的条数

读取指定数据

意思是从第五条开始，往后再读取五条。

聚合函数的问题

聚合函数只能在分组之后才能用 但是默认情况下 一张表也算一组 所以也可以用聚合函数 但是有小问题

用了聚合函数，相当于将整个表当成一组，这样就不能单独拿出来字段用了

查询关键字之正则表达式 regexp

SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询
	select * from emp where name regexp '^j.*?(n|y)$';

多表查询的思路

表数据准备
create table dep(
  id int primary key auto_increment,
  name varchar(20) 
);

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);


select * from emp,dep;  会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据

建立逻辑层面上的关系

要查找的数据可能在多张表内 这样查找起来就比较麻烦。所以我们在建表的时候可以建立逻辑意义上的关系 无需建立数据库层面上的外键：

注意dep_id不是外键 而是我们存数据的时候人为写成这样的

笛卡尔积现象

from后面填写多个表的名字时：

错误的SQL写法：

筛选时明确来自哪个表

练习

  1. 查询岗位名以及岗位包含的所有员工名字
   select post,group_concat(name) from emp group by post;
  2. 查询岗位名以及各岗位内包含的员工个数
  select post,count(id) from emp group by post;
  3. 查询公司内男员工和女员工的个数
  select gender,count(id) from emp group by gender;
  4. 查询岗位名以及各岗位的平均薪资
  select post as 岗位,avg(salary) as 平均工资 from emp group by post;
  5. 查询岗位名以及各岗位的最高薪资
  select post as 岗位,max(salary) as 最高薪资 from emp group by post;
  6. 查询岗位名以及各岗位的最低薪资
  select post as 岗位,min(salary) as 最低薪资 from emp group by post;
  7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
  select gender,avg(salary) from emp group by gender
  8. 统计各部门年龄在30岁以上的员工平均工资
   select post,avg(salary) from emp where age>=30 group by post;

SQL执行顺序图

https://blog.csdn.net/Boy_Martin/article/details/126828940

常见书写顺序：
select distinct table,func(table) from where desc group by like relegx not and or is in between having order by limit