mysql> select * from emp;
+----+------------------+--------+--------+
| id | emp_name | gender | dep_id |
+----+------------------+--------+--------+
| 2 | egon | male | 200 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)
select id,name from emp
where id > 1 and name like "%on%"
group by dep_id
having 分组后的过滤条件
order by 排序依据
limit n;
一 语法
select distinct 查询字段1，查询字段2，。。。 from 表名
where 分组之前的过滤条件
group by 分组依据
having 分组之后的过滤条件
order by 排序字段
limit 显示的条数;
def from(dir,file):
open('%s\%s' %(dir,file),'r')
return f
def where(f,pattern):
for line in f:
if pattern：
yield line
def group():
pass
def having():
pass
def distinct():
pass
def order():
pass
def limit():
pass
def select():
res1=from()
res2=where(res1,pattern)
res3=group(res2,)
res4=having(res3)
res5=distinct(res4)
res6=order(res5)
limit(res6)
二 where过滤
select id,name from db39.emp where id >= 3 and id <= 6
select * from db39.emp where id between 3 and 6;
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);
要求：查询员工姓名中包含i字母的员工姓名与其薪资
select name,salary from db39.emp where name like '%i%'
要求：查询员工姓名是由四个字符组成的的员工姓名与其薪资
select name,salary from db39.emp where name like '____';
select name,salary from db39.emp where char_length(name) = 4;
select * from db39.emp where id not between 3 and 6;
select * from emp where salary not in (20000,18000,17000);
要求：查询岗位描述为空的员工名与岗位名
select name,post from db39.emp where post_comment is NULL;
select name,post from db39.emp where post_comment is not NULL;
三 group by分组
#设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据
mysql> set global sql_mode="strict_trans_tables,only_full_group_by";
#每个部门的最高工资
select post,max(salary) from emp group by post; #最大数
select post,min(salary) from emp group by post; #最小数
select post,avg(salary) from emp group by post; #平均数
select post,sum(salary) from emp group by post; #总数
select post,count(id) from emp group by post; #统计每个部门的人总数，选择id是因为id不重复
#group_concat（分组之后用）
select post,group_concat(name) from emp group by post; #找到所有名字这个属性
select post,group_concat(name,"_SB") from emp group by post; #字符串的拼接，在后面跟_SB
select post,group_concat(name,": ",salary) from emp group by post; #在两个名字和工资的数据之间用冒号隔开
select post,group_concat(salary) from emp group by post;
# 补充concat（不分组时用）
select name as 姓名,salary as 薪资 from emp;#把表头取了一个别名
select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp; #每一个值之前都拼接了一个结果
# 补充as语法
mysql> select emp.id,emp.name from emp as t1; # 报错
mysql> select t1.id,t1.name from emp as t1;
# 查询四则运算
select name,salary*12 as annual_salary from emp; #可以做加减乘除运算 as后面跟名字防止被覆盖
分组练习
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
3. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
8、统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age >= 30 group by post;
四 having过滤
having的语法格式与where一模一样，只不过having是在分组之后进行的进一步过滤
即where不能用聚合函数，而having是可以用聚合函数，这也是他们俩最大的区别
1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
where age >= 30
group by post
having avg(salary) > 10000;
#强调：having必须在group by后面使用
select * from emp
having avg(salary) > 10000;
五 distinct去重
select distinct post,avg(salary) from emp
where age >= 30
group by post
having avg(salary) > 10000;
六 order by 排序
select * from emp order by salary asc; #默认升序排
select * from emp order by salary desc; #降序排
select * from emp order by age desc; #降序排
select * from emp order by age desc,salary asc; #先按照age降序排，再按照薪资升序排
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于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 3;
select * from emp order by salary desc limit 1;
# 分页显示
select * from emp limit 0,5;
select * from emp limit 5,5;
八 正则表达式
select * from emp where name regexp '^jin.*(n|g)$';
多表查询
在多个表中查询需要的数据
例如：有班级表 和学生表
给你已给班级名称 请查询所有的学员数据
先查班级表 得到一个班级的id 再根据id去学院表查询对应的学员
准备数据：
create table emp (id int,name char(10),sex char,dept_id int);
insert emp values(1,"大黄","m",1);
insert emp values(2,"老王","m",2);
insert emp values(3,"老李","w",30);
create table dept (id int,name char(10));
insert dept values(1,"市场");
insert dept values(2,"财务");
insert dept values(3,"行政");
多表查询的方式
1.笛卡尔积查询
什么是笛卡尔积,用坐标中的一条记录 去链接另一张表的所有记录
就像是把 两张表的数据做了一个乘法
这将导致 产生大量的无用重复数据
我们要的效果是：员工表中的部门id 与 部门表中的id相同 就拼接在一起
用 where 筛选出正确的数据
select *from emp,dept where emp.dept_id = dept.id;
on关键字
作用 用于多表查询是 进行条件限制
select *from emp,dept on emp.dept_id = dept.id; 这是错误的语法 因为 on 它只能用在专门多表查询语句中
2.内连接查询
inner join
select *from emp inner join dept on emp.dept_id = dept.id;
# 查询 所有的员工以及他们所属的部门信息
3.左外连接
left join
左边表中的数据完全显示 右边表中的数据匹配上才显示
select *from emp left join dept on emp.dept_id = dept.id;
# 查询 所有的部门以及他们所有的员工信息
4.右外连接
right join
左边表中的数据匹配上才显示 右边表中的数据完全显示
select *from emp right join dept on emp.dept_id = dept.id;
# 在一个表中 显示多个表中的所有数据
5. 全外链接
full join mysql不支持 oracle支持
可以通过union 间接实现
union 表示合并查询 意思是把多个查询结果合并在一起显示
要求是 被合并的表结构必须相同
默认去除重复
合并但是不去除重复
union all
select *from emp right join dept on emp.dept_id = dept.id
union
select *from emp left join dept on emp.dept_id = dept.id;
总结：多表链接 在书写时 按照填空来书写 如果左边要全部显示 用left join
右边全部显示 用right join
全部显示 把左链接的结果和右链接的结果 合并
当然 也可以更多表一起查 但是 没有意义 并且你要尽量避免 太多表 一起查
最多三张 在多对多的时候
select *from emp left join dept left join xxtable on emp.dept_id = dept.id;
