一、查询关键字

1、表准备

# 创建表
create table emp(
  id int primary key auto_increment,
  姓名 varchar(20) not null,
  性别 enum(‘男‘,‘女‘) not null default ‘男‘, #大部分是男的
  年龄 int(3) unsigned not null default 28,
  聘用日期 date not null,
  部门 varchar(50),
  薪资 double(15,2),
  办公室 int, #一个部门一个屋子
  depart_id int
);

# 录入数据
# 三个部门：教学，销售，运营
insert into emp(姓名,性别,年龄,聘用日期,部门,薪资,办公室,depart_id) values
(‘瑶妹‘,‘女‘,18,‘20170301‘,‘形象代言人‘,11300,404,1), # 以下是教学部
(‘杰洛特‘,‘男‘,38,‘20150302‘,‘老师‘,10000,404,1),
(‘崔佛‘,‘男‘,45,‘20130305‘,‘老师‘,8800,401,1),
(‘钟离‘,‘男‘,47,‘20140701‘,‘老师‘,11500,401,1),
(‘凯爹‘,‘男‘,29,‘20181101‘,‘老师‘,9100,401,1),
(‘瑞雯‘,‘女‘,26,‘20210211‘,‘老师‘,9000,401,1),
(‘鲁班大师‘,‘男‘,34,‘20200301‘,‘老师‘,11000,401,1),
(‘盖伦‘,‘男‘,48,‘20101111‘,‘老师‘,10000,401,1),

(‘派蒙‘,‘女‘,28,‘20150311‘,‘销售‘,6000,402,2),# 以下是销售部门
(‘蒂法‘,‘女‘,30,‘20191101‘,‘销售‘,11000,402,2),
(‘迪卢克‘,‘男‘,31,‘20170312‘,‘销售‘,16000,402,2),
(‘德莉莎‘,‘女‘,18,‘20160513‘,‘销售‘,7000,402,2),
(‘绫波丽‘,‘女‘,28,‘20170127‘,‘销售‘,6000,402,2),

(‘但丁‘,‘男‘,28,‘20160311‘,‘运营‘,10000,403,3), #以下是运营部门
(‘阿泰尔‘,‘男‘,34,‘20180312‘,‘运营‘,20000,403,3),
(‘史蒂夫‘,‘男‘,35,‘20130311‘,‘运营‘,19000,403,3),
(‘劳拉‘,‘女‘,20,‘20150411‘,‘运营‘,18000,403,3),
(‘2B‘,‘女‘,18,‘20140512‘,‘运营‘,17000,403,3);

2、查询关键字

# 1.select：用来指定表的字段数据
select * from emp; 
select id,name from emp;
# 在工作中一般很少使用*号，

# 2.from
后面跟需要查询的表名即可

(1) 查询关键字之where

# 1.查询id大于等于3小于等于6的数据
select id,姓名 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 薪资 = 20000 or 薪资 = 18000 or 薪资 = 17000;
select * from emp where 薪资 in (20000,18000,17000);  # 简写

"""
模糊查询
	关键字：like
	关键符号
			%：匹配任意个数的任意字符
			_：匹配单个个数的任意字符
"""

# 3.查询姓名中带有 蒂 的员工姓名和薪资
select 姓名,薪资 from emp where 姓名 like ‘%蒂%‘;

# 4.查询姓名由四个字符组成的员工姓名和薪资
select 姓名,薪资 from emp where 姓名 like ‘____‘;
select 姓名,薪资 from emp where char_length(姓名) =4;

# 5.查询id小于3或者大于6的数据
select *  from emp where id not between 3 and 6;

# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where 薪资 not in (20000,18000,17000);

(2) 查询关键字之group by分组

# 分组：将个体按照指定的条件分成一个整体
# 分组之后默认只能直接获取到分组的依据，其他字段无法再直接获取(可以间接获取)

# 严格模式
set global sql_mode=‘STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by‘

# 1.每个部门的最高薪资
select 部门,max(薪资) from emp group by 部门;
# 2.每个部门的最低薪资
select 部门,min(薪资) from emp group by 部门;
# 3.每个部门的平均薪资
select 部门,avg(薪资) from emp group by 部门;
# 4.每个部门的人数
select 部门,count(id) from emp group by 部门;
# 5.每个部门的月工资总和
select 部门,sum(薪资) from emp group by 部门;


# 可以给字段起别名(as还可以给表起别名)
select 部门 as ‘小组‘,sum(薪资) as ‘总和‘ from emp group by 部门;

# 查询分组之后的部门名称和每个部门下所有的员工姓名

# group_concat()  获取分组以外的字段数据 并且支持拼接操作
select 部门,group_concat(姓名) from emp group by 部门;
select 部门,group_concat(姓名,‘:‘,薪资) from emp group by 部门;

# concat()  未分组之前使用的拼接功能
select concat(姓名,‘:‘,性别) from emp;

# concat_ws()
select concat_ws(‘:‘,姓名,性别,薪资,年龄) from emp;

(3) 聚合函数

# 分组之后频繁需要使用的
	max		最大值
    min		最小值
    sum		求和
    count	计数
    avg		平均值

(4) 查询关键字之having过滤

# 功能上having与where是一模一样的，但是使用位置上有所不同
# where在分组之前使用，having在分组之后使用
 
# 需求：统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门

# 1.先筛选出所有30岁以上的员工
select * from emp where 年龄>30;

# 2.然后再按照部门分组
‘‘‘SQL语句的查询结构我们也可以直接看成是一张表‘‘‘
select 部门,avg(薪资) from emp where 年龄>30 group by 部门;

# 3.分组之后做过滤操作
select 部门,avg(薪资) from emp 
	where 年龄>30 
    group by 部门
    having avg(薪资)>10000
    ;

(5) 查询关键字之distinct去重

# 去重有一个非常严格的前提条件 数据必须是完全一样，如果数据带有主键那么肯定无法去重
select distinct 年龄 from emp;

(6) 查询关键字之order by排序

select * from emp order by 薪资;  # 默认是升序
select * from emp order by 薪资 asc;  # 升序关键字 可以不写
select * from emp order by 薪资 desc;  # 降序

# 排序也可以指定多个字段
select * from emp order by age desc,salary asc;

# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select 部门,avg(薪资) from emp 
	where 年龄>10
    group by 部门
    having avg(薪资)>1000
    order by avg(薪资);

(7) 查询关键字之limit分页

用来限制数据的展示条数
select * from emp limit 5;  # 前五条
select * from emp limit 5,5;  # 起始位置、条数

# 查询工资最高的人的详细信息
# 先按照工资排序 然后限制展示条数
select * from emp order by 薪资 desc limit 1;

(8) 查询关键字之regexp正则

正则表达式：用一些特殊符号的组合去字符串中筛选出符合条件的数据
   	
select * from emp where 姓名 regexp ‘^迪.*(克|伦)$‘;
# ‘^迪.*(克|伦)$‘  ‘迪‘开头 中间无所谓‘克‘或者‘伦‘结尾

二、多表查询

1.子查询
分步解决问题
将一条SQL语句的查询结果用括号括起来当作另外一条SQL语句的查询条件

2.连表操作
先将所有需要用到的表拼接到一起(一张表)
然后就是转换成单表查询

1、前期表准备

#建表
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,‘运营‘);

insert into emp(name,sex,age,dep_id) values
(‘宇智波斑‘,‘male‘,18,200),
(‘朽木露琪亚‘,‘female‘,38,201),
(‘多弗朗明哥‘,‘male‘,18,201),
(‘绯村剑心‘,‘male‘,28,202),
(‘橘右京‘,‘male‘,18,203),
(‘冲田总司‘,‘female‘,18,204);

2、子查询

# 查询橘右京所在的部门名称
	# 第一步 先获取jason所在的部门id
    select dep_id from emp where name=‘jason‘;
   	# 第二步 根据id号去部门表中筛选
    select * from dep where id = 200;
    # 完整句式
    select * from dep where id=(select dep_id from emp where name=‘橘右京‘);

3、连表操作

# 连表前戏
select * from emp,dep;
# 基于上表筛选数据(了解)
‘‘‘为了避免字段冲突 可以在字段名前面加表名明确‘‘‘
select * from emp,dep where emp.dep_id=dep.id;

########################掌握############################
# inner join	内连接	拼接公共的部分
	select * from emp inner join dep on emp.dep_id=dep.id;
# left join	左连接 以左表为基准展示所有数据 没有的null填充
	select * from emp left join dep on emp.dep_id=dep.id;
# right join	右连接 以右表为基准展示所有数据 没有的null填充
	select * from emp right join dep on emp.dep_id=dep.id;
# union	全连接
	select * from emp left join dep on emp.dep_id=dep.id
    union
    select * from emp right join dep on emp.dep_id=dep.id;

4、查询关键字与多表查询

原文：https://www.cnblogs.com/poco/p/14726184.html