mysql distinct两个字段其他字段保留 mysql两个字段相除

以下是我自己本人整理的sql学习笔记，知识学习本身是一个不断遗忘的过程，通过不断地回顾，总结和实践，才能够自己消化，为自己所用。oracle数据库仍然是众多企业使用最多的数据库，而且，每个数据库的基本语法都差不多，因此，我在学习的过程中，都是以oracle数据库在练习sql，希望自己能够在今后工作和学习中，能够及时回过头看看，也可以提供给大家学习和参考。

--在关系运算中称为投影
select emp_name,sex,email from employee_new;
--扩展操作
select 
emp_name as "姓名",salary*12 as "年薪",upper(email) as "电子邮箱"
from employee_new;

select * from department;
select * from job;

--通过查询条件过滤数据的操作在关系运算中称为选择
select * from employee_new where emp_name='刘备';

/*
运算符：
比较运算符：
等于(=)、不等于(！=)、大于(>)、小于(<)、大于等于(>=)、小于等于(<=)
between 包含两端的值
in运算符：用于查找列表中的值
空值判断：sql中的一个特殊值，代表了缺失或者未知的数据

逻辑运算符：
逻辑与(and)、逻辑或(or)、逻辑非(not)

not运算符:
not between、not in、not lke、not exists、not is null=not is null
*/


/*运算符优先级
1.=、!=/<>、<、<=、>、>=
2.is[not]、null、[not]like、[not]between、[not]in、[not]exists
3.not 
4.and
5.or
*/
--查询人力资源部或财务部门哪些员工拥有奖金
select
emp_name,dept_id,bonus
from employee_new 
where dept_id=2 or dept_id=3
and bonus is not null;

--and运算符优先级高,上面语句查询的是人力资源部(dept_id=2)的员工或者财务部(dept_id=3)中拥有奖金的员工。
--应改为：

select
emp_name,dept_id,bonus
from employee_new 
where (dept_id=2 or dept_id=3)
and bonus is not null;


--去除重复值
select distinct sex from employee_new;


--查询2018年1月1日之后入职，月薪少于5000，并且奖金小于1000(包括没有奖金)的员工
select
emp_name,dept_id,salary,bonus 
from employee_new 
where hire_date>to_date(20180101,'yyyymmdd') 
and salary<5000
and (bonus<1000 or bonus is null);


select * from employee_new;


/*
SQL提供了两种模糊匹配的方法:like运算符、正则表达式
1.like运算符
（1）支持两个通配符，用于指定模式：
%：可以匹配零个或者多个任意字符
_：可以匹配一个字符
2.正则表达式
*/



select emp_id, emp_name, sex from employee_new where emp_name like '赵%';



--转义字符：将通配符"%"和"_"进行转义，将它们当作普通字符使用。默认的转义字符为反斜杠(|)
create table t_like(c1 varchar2(20));
insert into t_like(c1) values('进度：25%已完成');
insert into t_like(c1) values('日期：2019年5月25日');


--查询包含字符串25%的数据
select c1 from t_like where c1 like '%25\%%' escape '\'';


--正则表达式
/*用于检索或者替换符合某个模式（规则）的文本*/



--单列排序
/*order by用于指定排序的字段；ASC表示升序排列，desc表示降序排列，默认按升序排列
对于升序排列：
数字按照从小到大的顺序排列，字符按照编码的顺序排列，日期时间按照从早到晚的顺序排列。
降序排列正好相反。
*/
select emp_name,salary,hire_date
from employee_new where dept_id=4 order by salary desc;

--多列排序
/*
多列排序是指基于多个字段或表达式的排列，使用逗号进行分隔。首先，基于第一字段进行排序，对于第一字段
排序相同的数据，再基于第二个字段进行排序。依此类推
*/
select emp_name,salary,hire_date 
from employee_new 
where dept_id=4
order by salary desc,hire_date;


/*在指定排序字段时，除了使用字段名或者表达式之外，也可以使用这些字段在select列表中出现的顺序表示。*/
select emp_name,salary,hire_date
from employee_new 
where dept_id=4
order by 2 desc,3;



--中文排序
/*在创建数据库或者表时，我们会指定一个字符集Charset和排序规则Collation
字符集：决定了数据库能够存储哪些字符
ASCII：只能存储简单的英文、数字和一些控制字符；
GB2312：可以存储中文；
Unicode：能够支持世界上的各种语言
排序规则：定义了字符集中字符的排序顺序，包括是否区分大小写，是否区分重音等
*/

--oracle默认按中文的拼音排序
select emp_name from employee_new where dept_id=4 order by emp_name desc;


--空值排序
/*Mysql和sql server认为空值最小，oracle和postgreSql认为空值最大。*/

select emp_name,bonus from employee_new where dept_id=2 order by bonus nulls first;


--解决空值排序问题，利用coalesce函数将空值转换为0
select
emp_name,coalesce(bonus,0) as bonus
from employee_new
where dept_id=2
order by coalesce(bonus,0);


--查询所有员工信息，按照员工总收入（年薪+奖金）从高到底进行排序，总收入相同再按照姓名的拼音顺序排列
select
emp_name,salary,nvl(bonus,0) as bonus,(salary*12+nvl(bonus,0)) as "总收入"
from employee_new
order by (salary*12+nvl(bonus,0)) desc,emp_name;


--Top-N排行榜
/*
Top-N排行榜的原理就是先排序，再返回前N条记录。
实现Top-N排行榜的方式主要有两种，
1.标准SQL提供的fetch语法；
2.另一种常见的limit语法
*/

--oracle/sql server/postgreSQL实现
--使用fetch子句查询月薪排名在前5的员工
select emp_name,salary from employee_new order by salary desc offset 0 rows fetch first 5 rows only;


--使用limit实现Top-N排行榜
/*适用于MySql和PostgreSQL支持使用Limit替代fetch实现相同的功能*/
select emp_name,salary
from employee_new
order by salary desc
limit 5 offset 0;


--分页查询
/*分页查询的原理就是先跳过指定的行数，再返回Top-N记录*/

select emp_name, salary
  from employee_new
 where salary =
       (select salary
          from employee_new
         order by salary desc offset 2 rows fetch first 1 rows only);

--完整的fetch语法
[offset m {row|rows}] fetch {first|next} [num_rows|N percent] {row|rows} {only|with ties};

/*offset表示偏移量，即从第M+1行开始返回；如果不指定，表示从第1行开始返回；row和rows作用相同
num_rows表示按照行数计算返回的数据量，n percent表示按照百分比计算返回的数据量，row和rows作用相同
only和with ties的差别在于，如果在最后有多个排名相同的数据行，with ties会返回更多的数据，默认为only
*/ 

select
emp_name,salary
from employee_new
order by salary desc
fetch first 10 percent rows only;

--使用limit实现分页查询
/*MySql以及postgresql实现*/
select emp_name,salary
from employee_new
order by salary desc
limit 5 offset 10;

--其他实现方式
--sqlserver实现
select top(5) emp_name,salary
from employee_new
order by salary desc;

--oracle实现
/*可以使用子查询和rownum函数实现相同功能*/
select
emp_name,salary
from 
(select emp_name,salary,rownum as rn
from
(select emp_name,salary 
from employee_new
order by salary desc))
where rn>=11 and rn<=15;

/*小结：
查询语句中的fetch和offset子句可以限定返回结果的数量和偏移量，从而实现排行榜和分页查询效果。
limit和offset子句也是实现该功能的一种常见的用法。
*/


--函数
/*sql语句主要功能就是对数据进行处理和分析，为了避免重复造轮子，提高数据处理的效率，sql提供了许多
标准的功能模块。
sql函数是一种具有某种功能的模块，可以接收零个或多个输入值，并且返回一个输出值
在sql中，函数分为两种类型：
1.标量函数：对每个输入参数，返回一个输出结果
2.聚合函数：基于一组数据进行计算，返回一个输出结果
*/


--绝对值函数
select
abs(-2),abs(2)
from employee_new
where emp_id=1;

--取整函数
/*ceil和ceiling函数向上取整，floor函数向下取整，round函数执行四舍五入运算*/
select
ceil(1.9),
floor(4.5),
round(9.456,2)
from dual;


--指数函数
/*exp函数计算自然常数e的n次方，power函数计算指定底数的n次方*/
select
exp(1),power(2,4)
from dual;


--对数函数
/*ln函数计算以e为底的自然对数，log函数计算指定底数的对数*/
select
log(6,36),ln(2.71828182845905)
from dual;


--平方根函数
/*sqrt函数计算数据的平方根*/
select sqrt(25) from dual;


--求余函数
/*mod函数用于计算两个数相除的余数*/
select mod(7,3) from dual;



--最大值与最小值
/*greatest与least函数分别计算参数列表中的最大值与最小值*/
select greatest(1,2,3),least(1,2,3) from dual;



--生成随机数
select dbms_random.value from dual;



--字符函数
/*字符函数用于字符数据的处理，例如字符串的拼接，大小写转换，子串的查找和替换*/


--ASCII
select ascii('sql'),chr(83) from dual; 

--字符串拼接
select concat('sql','world') from dual;

/*使用连接符将字符串进行连接*/
select 's'||'q'||'l' from dual;


--大小写转换
/*upper函数将字符转换为大写，lower函数将字符转换为小写*/
select lower('SQL'),upper('sql') from dual;


--字符串长度
/*charlength函数用于计算字符串包含的字符数量，octetlength函数用于计算字符串包含的字节数量
oracle使用length函数和lengthb函数计算字符数量和字节数量
*/

select length('数据库'),lengthb('数据库') from dual;

--获取子串
/*substring函数或者substr函数用于返回字符串中的子串*/
select substr(123456,1,2) from dual;

--截断字符串
/*trim函数用于删除字符串开头和结尾的指定字符
ltrim函数用于删除字符串左侧的字符，rtrim函数用于删除字符串右侧的字符
*/
select trim('-' from '--s-q-l--'),trim('  s-q-t '),ltrim('--s-q-l--','-' ),rtrim('--s-q-l--','-')
from dual;


--查找与替换
/*instr函数用于在字符串中查找并返回子串的位置，没有找到时返回0
replace函数用于替换字符串中的子串。
*/
select instr('[e-mail]','@'),replace('[e-mail]','@','.') from dual;

/*为了保护员工的隐私，在显示信息时将员工姓名进行隐藏处理，对于两个字的姓名
将姓氏显示为星号；对于三个字或更多字的姓名，将倒数第二个字显示为星号
*/

select 
replace(emp_name,substr(emp_name,length(emp_name)-1,1),'*')
from employee_new;


--日期和时间的存储与格式转换
/*在数据库中，日期时间类型存在3种形式：
date:日期类型，包含年月日（其中oracle的date类型包含了日期和时间两部分，但不支持time类型）
time:时间类型，包含时、分、秒
timestamp:时间戳类型，包含年、月、日、时、分、秒以及小数秒。
*/



--日期时间函数
/*日期时间函数用于操作日期和时间数据*/


--返回当前日期时间
select current_date,current_timestamp from dual;


--提取日期时间信息
select emp_name,hire_date from employee_new
where extract(year from hire_date)=2018;

select emp_name,extract(year from hire_date) from employee_new;


--日期和时间的数学运算
/*日期和时间的运算主要包括两个日期相减以及一个日期加/减一个时间间隔
oracle以及postgresql中两个日期相减就可以得到它们之间相差的天数。
*/
select emp_name,hire_date,current_date,current_date-hire_date as days from employee_new;


/*日期时间加上/减去一个时间间隔，可以得到一个新的日期时间*/
select hire_date,hire_date+interval '1' year as anniversary from employee_new;


/*当不同类型的数据在一起进行处理时，就会涉及到类型之间的转换，我们可以使用函数执行明确的类型转换*/


--类型转换函数
/*类型转换可能导致精度的丢失，并且cast函数在各种数据库中支持的转换类型取决于数据库的实现*/
--修改日期显示格式
alter session set nls_date_format='yyyy--mm--dd';
select cast('666' as integer),cast(hire_date as char(20))
from employee_new;




/*除了明确指定的类型转换之外，数据库可能执行某些操作时尝试隐式的类型转换*/
select 
'666'+123,concat('hiredate:',hire_date)
from employee_new;



/*sql中的case表达式可以根据不同条件产生不同的结果
1.简单case表达式
2.case表达式
*/

--1.简单case表达式
select
emp_name,
case dept_id
     when 1 then '行政管理部'
     when 2 then '人力资源部'
     when 3 then '财务部'
     when 4 then '研发部'
     when 5 then '销售部'
     when 6 then '保卫部'
     else '其他部门'
end as department
from employee_new;

/*case表达式的一个常见应用就是实现表的行列转换*/

--创建成绩表
create table t_case(sname varchar2(10),cname varchar2(10),score integer);

--插入测试数据
insert into t_case(sname,cname,score) values('张三','语文',80);
insert into t_case(sname,cname,score) values('李四','语文',77);
insert into t_case(sname,cname,score) values('王五','语文',91);
insert into t_case(sname,cname,score) values('张三','数学',85);
insert into t_case(sname,cname,score) values('李四','数学',90);
insert into t_case(sname,cname,score) values('王五','数学',60);
insert into t_case(sname,cname,score) values('张三','英语',81);
insert into t_case(sname,cname,score) values('李四','英语',69);
insert into t_case(sname,cname,score) values('王五','英语',82);


select * from t_case;

select 
sname,
case when cname='语文' then score else 0 end as "语文",
case when cname='数学' then score else 0 end as "数学",
case when cname='英语' then score else 0 end as "英语"
from t_case;

/*目前结果还是9条记录，然后将每个学生成绩合并为一条记录，此时需要使用分组汇总*/
select 
sname,
sum(case when cname='语文' then score else 0 end) as "语文",
sum(case when cname='数学' then score else 0 end) as "数学",
sum(case when cname='英语' then score else 0 end) as "英语"
from t_case
group by sname;

--2.搜索case表达式
/*所有简单case表达式都可以替换为等价的搜索case表达式*/
select
emp_name,
case 
   when dept_id=1 then '行政管理部' 
   when dept_id=2 then '人力资源部' 
   when dept_id=3 then '财务部' 
   when dept_id=4 then '研发部' 
   when dept_id=5 then '销售部' 
   when dept_id=6 then '保卫部' 
   else '其他部门'
end as department 
from employee_new;

/*使用case表达式通常用于处理更加复杂的逻辑条件*/
select
emp_name,salary,
case when salary<10000 then '低收入'
     when salary<20000 then '中收入'
     else '高收入'
end as grade
from employee_new;


/*case表达式
case表达式除了可以用于查询语句的select列表，也可以出现在其他子句中
*/

select
emp_name,
case when bonus is null then 0 else bonus end as bonus
from employee_new
where dept_id=2
order by case when bonus is null then 0 else bonus end;

--decode函数
/*oracle中的decode函数可以实现类似于简单case表达式的功能
decode(expression,value1,result1,value2,result2,...[,default_result]);
*/
select 
emp_name,
decode(dept_id,1,'行政管理部',2,'人力资源部',3,'财务部',4,'研发部',5,'销售部',6,'保卫部','其他部门') as department
from employee_new;


/*case表达式为sql语句提供了逻辑处理的能力，可以基于不同的条件返回不同的结果。
case表达式支持两种形式:简单case表达式和搜索case表达式
*/

select * from employee_new;

/*按照部门编号进行排序，并且确保同一个部门中的女性员工排在男性员工之前*/
select dept_id, emp_name, sex
  from employee_new
 order by dept_id,
       case sex
         when '女' then 0
         else 1
       end;


--聚合函数
/*
使用聚合函数时，需要注意两点：
1.在聚合函数的参数中加上distinct关键字，可以在计算之前排除重复值；
2.聚合函数在计算时，忽略输入值为null的数据行，count(*)除外
*/
/*在sql中，聚合函数用于对一组数据进行汇总计算，并且返回单个分析结果。常见的聚合函数包括：
1.count()
2.avg()
3.sum()
4.max()
5.min()
6.var_samp():计算一组数据的方差
7.stddev_samp:计算一组数据的标准差
*/

--使用count()函数用于统计行数
select count(1) as "员工数量" from employee_new;
select count(emp_id),count(0) from employee_new;
select count(sex),count(distinct sex) from employee_new;


--使用avg函数计算平均值
/*avg函数用于计算一组数据的平均值
当avg函数中存在空值时，计算之前会忽略这些空值。
*/
select avg(salary) from employee_new;
select avg(distinct salary) from employee_new;


--使用sum函数进行求和（不考虑null值）
select sum(salary) from employee_new;

--使用max函数计算最大值(不考虑null值)
select max(salary) from employee_new;


--使用min函数计算最小值(不考虑null值)
select min(salary) from employee_new;

/*聚合函数可以用于数据的汇总分析*/


/*统计每个部门中的人数，一个部门显示为一列*/

select dept_id,count(1) from employee_new group by dept_id;

select     
       sum(case when dept_id=1 then count(1) else 0 end) as "行政管理部",
       sum(case when dept_id=2 then count(1) else 0 end) as "人力资源部",
       sum(case when dept_id=3 then count(1) else 0 end) as "财务部",
       sum(case when dept_id=4 then count(1) else 0 end) as "研发部",
       sum(case when dept_id=5 then count(1) else 0 end) as "销售部",
       sum(case when dept_id=6 then count(1) else 0 end) as "保卫部"
from employee_new
group by dept_id;
       


--数据分组
/*sql中的group by子句可以将数据按照某种规则进行分组*/
select sex from employee_new group by sex;


--多字段分组
/*group by也可以基于多个字段或表达式进行分组*/
select
dept_id,sex
from employee_new
group by dept_id,sex
order by dept_id;

--按照性别统计员工数量和平均工资
select
sex,count(1),avg(salary)
from employee_new
group by sex;



--按照部门和职位统计员工的数量和平均月薪
select
dept_id,job_id,count(1),avg(salary)
from employee_new
group by dept_id,job_id
order by avg(salary) desc;



--基于表达式分组统计
select extract(year from hire_date) as "入职年份",count(1) as "员工数量" from employee_new
group by extract(year from hire_date)
order by extract(year from hire_date);



--空值分组
/*对于分组操作，需要注意空值问题。对于group by，如果分组字段中存在多个null值，它们将会被分为一组*/
select bonus,count(1) from employee_new group by bonus;



--分组后的过滤
select
dept_id,avg(salary)
from employee_new
group by dept_id
having avg(salary)>10000;



/*在sql语句中可以使用where子句对表进行过滤，同时使用having对分组结果进行过滤*/
select
dept_id,count(1)
from employee_new
where salary>10000
group by dept_id
having count(1)>2;



select 
sex,
case  
   when salary<10000 then '低收入'
   when salary>20000 then '中收入'
   else '高收入'
end as salary_level,
count(1),
avg(salary)
from employee_new
group by sex,case  
   when salary<10000 then '低收入'
   when salary>20000 then '中收入'
   else '高收入'
end
order by sex;