建立表间关系的补充
'''
表与表之间如果有关系的话 可以有两种建立联系的方式
1.就是通过外键强制性的建立关系
2.就是自己通过sql语句逻辑层面上建立关系
delete from emp where id=1;
delete from dep where id=1;
创建外键会消耗一定的资源 并且增加了表与表之间的耦合度
在实际项目中 如果表特别多 其实可以不做任何外键处理 直接
通过sql语句来建立逻辑层面上的关系
到底用不用外键取决于实际项目需求
'''
如何查询表
前期表准备
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;
# 在插入中文的时候还是会出现乱码或者空白的现象 可以将字符编码统一设置成GBK
重要关键字的执行顺序
# 书写顺序
select * from emp where id>3;
# 执行顺序 from>where>select
# 在不知道select后面要写什么时可以先用*作为占位符，先写from跟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.查询员工姓名中包含字母o的员工的姓名和薪资
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的 姓名和薪资 char_length()
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
# 5.查询id小于3或者id大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000,17000,18000);
# 7.查询岗位描述为空的员工姓名和岗位名 针对null不用等号 用is
select name,post from emp where post_comment is null;
group by分组
# 1 按照部门分组
# 没设置严格模式的情况下是可以执行的
select * from emp group by post;
'''
分组之后没办法直接获取组内单个数据，最小可操作单位是组
'''
# 设置严格模式加入 'ONLY_FULL_GROUP_BY'
set global ' ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# 设置严格模式后分组默认只能拿到分组的数据
select post from emp group by post;
# 按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)
'''
常用的聚合函数：max、min、sum、count、avg
'''
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的人数
# count()无法统计null字段的个数
select post,count(id) from emp group by post; # 常用 因为是唯一标识,符合逻辑
# 6.查询分组之后的部门名称和每个部门下所有的员工姓名
select post,group_concat(name) from emp group by post;
# group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用
select concat(id,':',name,':',salary) from emp;
# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select name as '员工姓名' from emp where name like '____';
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; 报错
select t1.id,t1.name from emp as t1;
# 查询每个人的年薪 12薪
select name, salary*12 from emp;
# 注意：
'''
1、关键字where和group by同时出现的时候group by必须在where的后面
2、where筛选条件时不能使用聚合函数
3、不分组 默认整体就是一组
'''
# 统计各部门年龄在30岁以上的员工平均薪资
select post,avg(salary) from emp where age>30 group by post;
having分组之后的筛选条件
'''
having语法与where一致
having要跟在group by后面
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;
select distinct age from emp;
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因此可以省略不写
'''
# 先按照age降序排 如果碰到age相同 则再按照salary升序排
select * from emp order by age desc,salary asc;
# 统计各部门年龄在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;
# 当数据量很庞大的时候执行上述语句电脑可能会卡死
# 解决方法: limit限制条数
select * from emp limit 3; # 只展示三条
select * from emp limit int1,int2; # int1表示起始位置，int2表示条数
regexp正则
select * from emp where name regexp '^j.*(n|y)$';
like模糊查询
# 查询员工姓名中包含字母o的员工的姓名和薪资
select name,salary from emp where name like '%o%';
# 查询员工姓名是由四个字符组成的 姓名和薪资 char_length()
select name,salary from emp where name like '____';
多表查询
前期建表准备
#建表
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,'运营'),
(204,'管理');
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 dep,emp; # 结果 笛卡尔积
'''
首先，先简单解释一下笛卡尔积。
现在，我们有两个集合A和B。
A = {0,1} B = {2,3,4}
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：
A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；
B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
从以上的数据分析我们可以得出以下两点结论：
1，两个集合相乘，不满足交换率，既 A×B ≠ B×A;
2，A集合和B集合相乘，包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;
'''
# 数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。
select * from emp,dep where emp.dep_id=dep.id;
'''
表的拼接
inner join 内连接
left join 左连接
right join 右连接
union 全连接
'''
# 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;
子查询
'''
子查询就是平时解决问题的思路:分步骤解决
将一个查询语句的结果当作另外一个查询语句的条件去用
'''
# 查询部门是技术或者是人力资源的员工信息
1 先获取部门的id号
2 再去员工表里面筛选出对应的员工
select * from emp inner join dep on emp.dep_id=dep.id where dep_id in (select id from dep where name in ('技术','人力资源'));
总结
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
"""
多表查询就两种方式
先拼接表再查询
子查询 一步一步来
"""
