MySQL关键字查询

• SQL语句查询关键字

select
from
where
group by
having
distinct
order by
limit
regexp

• 多表查询的两种方式

内容详细

select与from

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

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

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

数据的前期准备

create table emp(
	in int primary key auto_increment,   # 主键
	name varchar(20) not null,		# 不为空的名字
	gender enum('male','female') not null default'male',
    age int unsigned not null default 28,  # 不为空默认为28
 	hrie_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语句的小技巧

针对select后面的字段名可以先用*占位往后写，最后再回来修改

在实际应用中select后面很少直接写* 因为*表示所有，当表中字段和数据都特别多的情况下非常浪费数据库资源
"""
SQL语句的编写类似于代码的编写，不是一蹴而就的，也需要反反复复的修修补补
"""

查询关键字之where筛选

1.查询id大于等于3小于等于6的数据
select * from 表名 where id >= 3 and id <=6;
select * from 表名 where id between 3 and 6;

2.查询薪资是20000或者18000或者17000的数据
方式1:  或者的方式筛选
select * from 表名 where salary = 20000 or salary = 18000 salary = 17000;
方式2:  运用成员运算符
select * from 表名 where salary in (20000,18000,17000);

3.查询id小打3大于6的数据
方法1:	小于3或者大于6
select * from 表名 where id < 3 or id > 6;
方法2:
select * from 表名 id not betweend 3 and 6;

4.查询员工姓名中包含字母o的员工姓名与薪资
"条件不够精确的查询称之为模糊查询"
"""
模糊查询的关键字是:
				like
模糊查询的差用符号
	%	匹配任意个数,匹配的个数不受限制
			eg: %o%  o jason owen loo wwoww
			%o 	o	asdasdo asdo
			o%	o  ob	oeda
	_	匹配单个个数的任意字符,只能匹配单个字符
			eg: _o_  aox  wob iok
			_o  ko bo mo				
"""
select * from 表名 where name like %o%;

5.查询员工姓名是由两个字符组成的姓名与其薪资
select name,salary 表名 where name like '____';
方法二:	条件为名字长度
select * from emp where char_length(name) = 4;  

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

查询关键字之group by分组

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

聚合函数

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

1.将员工数据按照部门分组
select post from 表名 group by post
"""
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;		
	分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段
"""

2.获取每个部门的最高工资
"""
要不要分组我们完全可以从题目的需求中分析出来尤其是出现关键字每个、平均
"""
写法1:	用英语名字
select post,max(salary) from 表名 group by post;
写法2:	给一个中文名字
select post as '部门名称',max(salary) as '平均薪资' from 表名 group by post;

3.一次获取部门薪资相关统计
select post,max(salary),min(salary),avg(salary),sum(salary) from 表名 group by post;
select post as '部门',max(salary) as '最高薪资',min(salary) as '最低薪资',avg(salary) as '最低薪资',sum(salary) as '部门薪资支出' from emp group by post;

4.统计每个部门的人数
select post,count(id) from 表名 group by post;
select post,count(id) as '部门人数' from emp group by post;

分组以外的字段无法直接填写 需要借助于方法

select post,group_concat(name) from 表名 group by post;
select post,group_concat(name,age) from 表名 group by post;
select post,group_concat(nane,'|',age) from 表名 group by post;
select post,group_concat(name,'_NB') from 表名 group by post;
select post,group_concat('DSB',name,'_NB') from 表名 group by
post;

查询关键字之having过滤

having与where本质是一样的，都是用来对数据做筛选
	只不过where用在分组之前(group by 首次筛选)
	having用在分组(group by)之后(二次筛选)
1.统计各部门年龄在30岁以上的员工平均工资，并且保留大于10000的数据
"""
稍微复杂的SQL跟写代码几乎一样 也需要提前想好大致思路
	每条SQL的结果可以直接看成就是一张表	基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
"""
步骤1:筛选出来年龄大于30岁的员工
select * from where age > 30;
步骤2:筛选出来的数据按照部门分组进行平均工资计算
select post,avg(salary) from where age > 30 group by post;
步骤3;再次筛选平均薪资大于10000的
select post avg(salary) from where age > 30 group by post having 
avg(salary) > 10000;

查询关键字distinct去重

去重有一个必须的条件，也是很容易被忽略的条件
	数据必须一模一样才可以去重
select distinct id,age from 表名; 关键字针对的是多个字段组合的结果
select distinct age from emp;
select distinct age,post from emp;

查询关键字之order by排序

1.可以是单个字段排序
select * from emp order by age;		默认升序
select * from emp order by age desc;  修改为降序

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

3.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
a:先统计各部门年龄在10岁以上的员工
select * from emp where age > 10;
b:部门分组，计算平均薪资
select post,avg(salary) from emp where age > 10 group by post;
c:再次筛选平均薪资大于1000的
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000;
d:对大于1000的平均工资进行排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);

查询关键字之limit分页

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

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

多表查询的思路

表数据准备
creare teble dep(
	id int primary key auto_incerment,
    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;
基于上述的额操作就可以将多张表合并到一起然后一洗慈宁宫获取更多的数据

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

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

多表查询的两种方法

方法1:连表操作

inner join   内连接
select * from 表名 inner join 表名 on 连表用来匹配的字段
如:select * from emp inner join dep on emp.dep_id=dep.id;

left join 	 左连接
select * from 表名 left join 表名 on 连表用来匹配的字段
以左表为基准，展示左表所有的数据，如果没有对应项则用NULL

right join	 右连接
select * from 表名 right join 表名 on 连表用来匹配的字段
以右表为基准，展示右表所有的数据，如果没有对应项则用NULL

union		全连接
select * from 表名 left join 表名 on 连表用来匹配的字段
union
select * from 表名 right join 表名 on 连表用来匹配的字段
以左右表为基准，展示所有的数据，各自没有的全部NULL填充

"""
学会了连表操作之后也就可以连接N多张表
	思路:将拼接之后的表起表名当成一张表再去与其他表拼接，再起别名当一张表，再去与其他表拼接，其次往复即可
"""

方式2:子查询

将一条SQL语句用括号括起来当做另外一条SQL语句的查询条件
	题目:求姓名是jason的员工部门名称
 子查询类似于我们生活中解决问题的方式>>>:分布操作
    步骤1:先根据jason获取部门编号
       select dep_id from emp where name = 'jason';
    获取的是部门id
    步骤2:再根据部门编号获取部门名称
       select name from dep where id=200;
    总结:
    select name from dep where id=(select dep_id from emp where name = 'jason')
"""
很多时候多表查询需要结合实际情况判断用哪种，更多时候甚至是互相配合使用
"""

小知识点补充说明

1.concat与concat_ws
concat用于分组之前的字段拼接操作
	select concat(name,'$',gender) from emp; 
concat_ws用于拼接多个字段并且中间的连接符一致
	select concat_ws('|',name,gender,age,salary) from emp;

2.exists
	sql1 exists sql2
    sql2有结果的情况下才会执行sql1，否则不zhixingsql1，返回空数据
3.表相关SQL补充
alter table 表名 rename 新表名;		# 修改表名

alter table 表名 add 字段名 字段类型() 约束条件;  
# 字段名末尾添加新的字段名

alter table 表名 add 字段名 字段类型() 约束条件 after 已有字段;
# 添加字段名在已有的字段名下面

alter table 表名 add 字段名 字段类型() 约束条件 first;
# 在字段名首行添加新的字段名

alter table 表名 change 旧字段名 新字段名 字段类型() 约束条件;
# 修改字段名

alter table 表名 modify 字段名 新字段类型() 约束条件;
# 修改字段类型

alter table 表名 drop 字段名;
# 删除字段

作业

1. 查询岗位名以及岗位包含的所有员工名字
select post as '岗位名称' group_concat(name) as '岗位名字' from emp group by post
2. 查询岗位名以及各岗位内包含的员工个数
select post as '岗位名称' count(id) as '岗位人数' from emp group by post;
3. 查询公司内男员工和女员工的个数
select gender,count(id) from emp group by gender;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;select post,max(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) 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;