mysql 笔记

use job;
alter table company_data 
change companyLabelList companyLabelList varchar(255),
change companyShortName companyShortName varchar(255),
change companySize companySize varchar(255),
change companyFullName companyFullName varchar(255),
change businessZones businessZones varchar(255);

select count(*) from job.company_data;
select city,education from job.jj_data;
select city,education from job.jj_data limit 100;
select * from job.jj_data order by city desc
limit 100;

select * from job.jj_data where companyId=4184;
select * from job.jj_data where city = "上海";
select count(*) from job.jj_data where city<>"上海";
select count(*) from job.jj_data where companyId between 2000 and 8000;
select * from job.jj_data where city  in ("南京","苏州");
select * from job.jj_data where city  not in ("南京","苏州");
desc job.jj_data;
select * from job.jj_data where city="上海"  and education="硕士";
select * from job.jj_data where city="上海"  and education in ("硕士","专科");
select * from job.jj_data where city="长沙" or city="苏州";
select * from job.jj_data
where
city="苏州"
and 
(education="本科"
or
workYear="1-3年");

select * from job.jj_data   -- 括号不可省略，
where
city="苏州"
and 
education="本科"
or
workYear="1-3年";

select * from job.jj_data 
where (city="苏州" and education="本科") 
or (city="长沙" and education="硕士");

--模糊查找

select * from job.jj_data
where `secondType` like "%开发";

show full columns from job.jj_data;

-- 分组，透视表  需要填上
select city,count(*) from job.jj_data
group by city;

select city,count(*),count(1),count(0) from job.jj_data
group by city;
-- 计算公司数量，需要去重，原因一个公司招好几个岗位
select city,count(*),count(distinct(`companyId`)) from job.jj_data
group by city;

--多重筛选，加个计数项
select city,education,count(*),count(1),count(0) from job.jj_data
group by city,education;

--  针对group by 限定条件用的时having 【也可以用and】而不是where ，此外不需要一个语句内，不需要逗号【逗号是看是否逻辑并列的】
--   having 语句只在group by 语句后执行，一般筛选数量
--  具体条件筛选，可以再group by语句前执行where 语句
select city,count(*) from job.jj_data
group by city
having count(*)>=100;

--  group by select 只有选择gruop by的字段，和计数字段，不可以自行选其他字段  ，分组没办法显示
--  having 二次过滤
select city,count(*) from job.jj_data
where `industryField` like "%电子商务%"
group by city
having count(*) >20;

--  可以再group by 语句中嵌套 if 判断语句 ，把上一个where写到having中
--  但是结果和where写在group by 前的count值不一样，因为where在前，表示group by的计数数据，已经被筛选过了
--   where写在having中，仅仅表示逻辑上的判断【内在的判断】，打印的结果，依然是count(*)的数值，只不过筛选了一次
select city,count(*) from job.jj_data
group by city
having  count(if((`industryField` like "%电子商务%"),1,null)) >20;

-- 查看if 中的逻辑表现,因为null替代了非电子商务，所有计数项和这样一样
select if( industryField like "%电子商务%",industryField,null) from job.jj_data;

select count(if(industryField like "%电子商务%",industryField,null)) from job.jj_data;
select count(*) from job.jj_data
where industryField like "%电子商务%";


-- 我们可以再count时就添加计数条件，然后可以再做占比
select city,
count(*),
count(if(industryField like "%电子商务%",industryField,null)) as '电子商务岗位',
count(if(industryField like "%电子商务%",industryField,null))/count(*) as "电子商务职位数量占比"
from job.jj_data
group by city;


-- 筛选电子商务占比大于20%的城市,并且用order by 做以电商的数量，做倒叙排列
select city,
count(*),
count(if(industryField like "%电子商务%",industryField,null)) as '电子商务岗位',
count(if(industryField like "%电子商务%",industryField,null))/count(*) as "电子商务职位数量占比"
from job.jj_data
group by city
having count(if(industryField like "%电子商务%",industryField,null))/count(*) >0.2
order by count(if(industryField like "%电子商务%",industryField,null)) desc ;

-- as 别名如果时英文，可以直接在having中引用,别名时中文就没办法了
-- select中的别名只能在select之外用
select city,
count(*),
count(if(industryField like "%电子商务%",industryField,null)) as ec,
count(if(industryField like "%电子商务%",industryField,null))/count(*) as ec_rate
from job.jj_data
group by city
having ec_rate >0.2
order by ec desc ;

-- 获取左边第一个位置的值
select left(salary,1),salary from job.jj_data;

-- locate 获取特定值的位置locate(要查找的字符，字段，开始的位置1，2，3）
select left(salary,1),locate('k',salary),salary from job.jj_data;

-- 筛选第一个k,上限
select left(salary,locate('k',salary)),salary from job.jj_data;

-- 筛选下线
select right(salary,locate('k',salary)),salary from job.jj_data;

-- 筛选下线2
select right(salary,(length(salary)-locate('-',salary))),salary from job.jj_data;

--left(salary,0) 不取值
select left(salary,0),salary from job.jj_data;

--筛选数值
select left(salary,locate('k',salary)-1),
length(salary),
right(salary,length(salary)-locate('-',salary)),
left(right(salary,length(salary)-locate('-',salary)),locate('k',right(salary,length(salary)-locate('-',salary)))-1),
salary
from job.jj_data;

-- substr函数截取字符串中的一段,不要kSUBSTR(str,pos,len); 就是从pos开始的位置，截取len个字符(空白也算字符)。 
--  字符是以1而非0

select left(salary,locate('k',salary)-1),
salary,
substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1)
from job.jj_data;

--添加列
alter table job.jj_data
add column salary_1 int;
--删除列
alter table job.jj_data
drop column salary_1;

--添加列
alter table job.jj_data
add column salary_1 int;

-- update  不需要向alter  列名table  colum 等关键字
update job.jj_data set  salary_1=left(salary,locate('k',salary)-1);

-- 添加两列
alter table job.jj_data 
add column salary_1 int after salary;
alter table job.jj_data 
add column salary_2 int after salary_1;

--
alter table job.jj_data
change column salary_2 salary_2 varchar(10);
-- update
update job.jj_data 
set salary_1=left(salary,locate('k',salary)-1),
    salary_2=substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1);

select 
    left(salary,locate('k',salary)-1) as bottom,
    substr(salary,locate('-',salary)+1 ,length(salary)-locate('-',salary)-1) as top,
    salary
    from job.jj_data;

-- 子查询   
select bottom,top
from (
select 
    left(salary,locate('k',salary)-1) as bottom,
    substr(salary,locate('-',salary)+1 ,length(salary)-locate('-',salary)-1) as top,
    salary
    from job.jj_data) as t;

--  case when else end  语句，可以在查询阶段，多一个字段进行数据清洗，或者说数据离散化。
--  如果把子查询作为临时表，必须要 as 指定别名
select 
     case
       when (bottom + top)/2 <= 10 then '0-10'
       when (bottom + top)/2 <= 20 then '10-20'
       when (bottom +top) /2 <=30 then '20-30'
     else '30+'
     end,
     salary
     from (
        select 
            left(salary,locate('k',salary)-1) as bottom,
            substr(salary,locate('-',salary)+1 ,length(salary)-locate('-',salary)-1) as top,
            salary
            from job.jj_data) as t;
            
--  查询符合特定条件的明细，正常情况我们会用where语句做限定，事实上子查询的结果也可在where语句中使用，当然和where指定的条件是同一个字段
--  提取每个城市中职位数量大于20的明细

select * from job.jj_data
    where city in 
      (select city from job.jj_data 
      group by city
      having count(*) >=20);

-- 跨表查询，可以用子查询，把另一张的限制条件保存到子查询里          
select * from job.jj_data
   where companyId in
        (select companyId from job.company_data
                where `companyShortName`='1号店');
--join  类似于excel的vlookup

select * from job.jj_data as jd
   join job.company_data as cd
       on jd.companyId=cd.companyId;
-- 正则表达式  regexp  正则表达式要用''括起来
select * from job.jj_data 
where salary_2 regexp '[a-zA-Z]';

--左连接
select * from job.jj_data as jd
left join 
(select * from job.company_data 
 where `companySize` ='50-150人') as t
 on jd.companyId=t.companyId;
 
-- 筛选左表 不包含右表的部分，及右表都是null
-- 过滤的最后的where需要标注表
select count(*) from job.jj_data as jd
left join 
(select * from job.company_data 
 where `companySize` ='50-150人') as t
 on jd.companyId=t.companyId
 where t.companyId is null;
 
 -- 求50-150人的公司有多少各职位，占比多少
 -- 思路？  
 -- 方法1：我们左连接两张表，在用一个where语句，筛选50-150人这个条件，count() 不求null值，所以count(1)
 --  右表是一张已经筛选过的子表
 
 select count(1),count(companySize) from job.jj_data as jd
 left join (
             select * from job.company_data 
             where `companySize` ='50-150人') as t
 on jd.companyId=t.companyId;
 
 --方法2
 select count(1),count(if(`companySize` ='50-150人',1,null)) from job.jj_data as jd
 left join company_data as cd
 on jd.companyId=cd.companyId;
 
 select city,count(*),max(companyId) from job.jj_data
 group by city;
 
 -- having 只是针对city【group by的字段】,或者计数项，或者count(if())语句，不能针对其他字段做判断，可以写在where语句中
 select city,count(*) from job.jj_data where companyId > 50000 group by city ;
 
 
 alter table job.user_info add column birth Date;
 select count(生日) from job.user_info;
 update job.user_info set birth = str_to_date(生日,'%Y-%m-%d');
 alter table order_info add primary key(iidenx);
 
 select now();
 
 select 日期,date(日期),date_format(日期,'%Y-%m'),date_add(日期,interval + 2 day) from job.order_info;
 select date_format(日期,'%y-%m'),count(*) from job.order_info 
 group by date_format(日期,'%y-%m');
 
 select sum(金额) from job.order_info;