查询表关键字,多表操作

查询表关键字

#前期数据准备
create table emp(
	id int not null unique auto_increment,
    name varchar(20) not null,
    sex 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,sex,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);

#补充
当表字段特别多,
可以用\G分行展示
select * from emp\G;

几个重要关键字的执行顺序

#输入顺序
select id,name from emp where id>3;
#执行顺序
from 
where
select

虽然执行顺序和书写顺序不一致
先用select * 占位
最后将*替换为想要的字段

where 筛选条件

#作用:是对整体数据的筛选
#查询id>=3 ,<=6的数据
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6;
#查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000; 
select * from emp where salary in (20000,18000,17000); 
#查询员工姓名中包含字母o的员工的姓名和薪资
'''
like
	%表示任意多个字符
	-表示任意一个字符
'''

select name,salary from emp where name like '%o%';
#查询员工姓名是由四个字符组成的姓名和薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
#查询id<3或者id>6的数据
select * from emp where id not between 3 and 6;
#查询薪资不在20000,18000,17000范围的数据
select  * from emp where salary not in (20000,18000,17000);
#查询岗位描述为空的员工姓名和岗位名 , 针对null用is,非空 is not null
select name,post from emp where post_comment is null; 

group by 分组

#按照部门分组
select * from emp group by post;
#分组之后最小的操作单位应该是组 而不是组内的单个数据
#上述命令在不符合分组规范,分组之后不应该考虑单个数据,而应该以组为操作单位
#如果设置了严格模式,会报错
set global sql_mode='strict_trans_tables,only_full_group_by';
通过
show variables like '%mode'; #查看
#设置严格模式后 分组默认只能拿到分组,其他字段不能直接获取,需要借助一些方法
select post from emp group by post;

#获取每个部门的最高薪资 as 可以给字段取别名,也可以省略,但是不推荐,语义不明确容易错乱
select post as "部门",max(salary) as "最高薪资" from emp group by post;
select post "部门",max(salary) "最高薪资" from emp group by post;
#最低薪资
select post as "部门",min(salary) as "最低薪资" from emp group by post;
#平局你薪资
select post as "部门",avg(salary) as "平均薪资" from emp group by post;
#工资总和
select post as "部门",sum(salary) as "薪资总和" from emp group by post;
#每个部门的人数 count后面放任意字段都可以,不能放null,count无法计数
select post as "部门",count(post) as "人数" from emp group by post;
select post as "部门",count(post_comment) as "人数" from emp group by post;

#查找分组后的部门名称和每个部门下所有的员工的姓名
#group_concat 获取到分组之后的普通字段的值
select post,group_concat(name) from emp group by post;
#group_concat 还支持拼接
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
#concat 不分组的时候用
select concat('NAME:',name),concat('SAL:',salary) from emp;

#补充 as语法不单单可以给字段起别名,也可以给表起
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; #报错,找不到emp,因为是首先执行from 后执行select,所以报错
select t1.id,t1.name from emp as t1;

#查询每个人的年薪
select name,sum(salary)*12 from emp group by name;

分组注意事项

#关键字where 和 group by 同时出现的时候
#group by必须在where 的后面
#where 先对整体数据进行过滤,之后再分组操作
#聚合函数只能在分组之后使用
#where的筛选条件不能使用聚合函数
select id,name from emp where max(salary)>3000; 报错
select max(salary) from emp; #不分组 默认整体就是一组

#统计各部门年龄在30以上的员工平均薪资
select post,avg(salary) from emp where age>30 group by post;

having分组之后的筛选

#语法和where是一致的
#只不过having是分组之后的过滤操作 即having可以直接使用聚合函数

#统计各部门年龄在30岁以上的员工工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp 
	where age>30 
    group by post 
    having avg(salary) > 10000;

distinct去重

#必须是完全一样的数据才可以去重
#一定不要将主键忽视了,有主键在的情况下是不可能去重的

select distinct id,age from emp; #有主键id无法去重
select distinct age from emp; #去重成功了

#ORM 对象关系映射 让不懂sql语句的人也能够非常牛逼的操作数据库

order by排序

select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc; #降序
#order by默认升序 asc可以省略
select * from emp order by age desc,salary asc;
#先按照age降序,在按照salary升序

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

limit限制展示条数

select * from emp;
#针对数据过多的情况,我们通常做分页处理
select * from emp limit 3; #只展示3条数据

select * from emp limit 0,5;
select * from emp limit 5,5;
#第一各参数是起始位置,第二个参数是展示条数

正则

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

多表操作

#建表
create table dep(
	id int,
    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('jason','male',18,200),('egon','female',48,201),('kevin','male',18,201),('nick','male',28,202),('owen','male',18,203),('jerry','female',18,204);

表操作

select * from emp,dep; #笛卡尔积

select * from emp,dep where emp.dep_id = dep.id;
#先做笛卡尔积 然后筛选

#mysql特定开设了对应的连表方法
	inner join
    left join
    right join
    union
    
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接两张表上相同部分的数据

select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来,

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;

子查询

子查询就是我们平时解决问题的思路
分步骤解决问题
	第一步
    第二步
    ...
将一个查询语句的结果当做另外一个查询语句的条件取用

#查询部门是技术或者人力资源的员工信息
1. 现获取部门的id号
select id from dep where name in ('人力资源','技术');
2. 再去获取emp表中员工的
select * from emp where dep_id in (select id from dep where name in ('人力资源','技术'));

总结

表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它做为虚拟表和其他表关联 
多表查询就两种方式
	拼接查询
    子查询
    

连表操作理论