单表查询

一.单表查询之where关键字

#创建表
create table employee(
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
);

desc employee;

alter table employee rename emp
#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;

#二 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;  //null只能用is 不能用等号  什么叫空？空就是不占任何存储空间

 

二.单表查询之group by关键字

group by分组

show variables like 'sql_mode';#查询sql模式 #NO_ENGINE_SUBSTITUTION
#设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据
mysql> set global sql_mode="strict_trans_tables,only_full_group_by";
# 这条sql命令表示了，只能取到组名，组里面的记录是取不出来的

#每个部门的最高工资(聚合函数)
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;

#group_concat（分组之后用）        #这是一种字符串拼接操作 也是聚合函数
select post,group_concat(name) from emp group by post;
select post,group_concat(name,"_SB") from emp group by post;
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;

分组小练习

1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪

select name,age from employee where post = 'teacher';
select name,age from employee where post='teacher' and age > 30; 
select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
select name,salary*12 from employee where post='teacher' and name like 'jin%';

 三.单表查询之having关键字

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;
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;

语法顺序    from --> where --> group
执行顺序   from --> where--> group--> having -->select post avg

四.单表查询之distinct关键字

distinct去重

select distinct post,avg(salary) from emp
            where age >= 30
            group by post
            having avg(salary) > 10000;


select post from emp;
select distinct post from emp   #去重

五.单表查询之order by关键字

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;    #从0(id=1)开始往后取5条
select * from emp limit 5,5;    #从5开始往后取5条

七.语法优先级和运行优先级******

一.语法优先级按顺序#下面是语法顺序

   select distinct 查询字段1，查询字段2，。。。 from 表名
        where 分组之前的过滤条件
        group by 分组依据
        having 分组之后的过滤条件
        order by 排序字段
        limit 显示的条数;

 

二.运行优先级

def from(dir,file):#传文件夹和文件进来   打开文件
    f=open('%s\%s' %(dir,file),'r')
    return f

def where(f,pattern):#循环满足条件出结果
    for line in f:
        if pattern：
            yield line

def group():#接受where传进来的结果
    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)#limit本质就是打印操作

八.正则匹配

select * from emp where name regexp '^jin.*(n|g)$';#以jin开头的,以n|g结尾的