sql查询基础

建站1490 更新时间：2025-06-08 02:42:27

2023年6月21日发(作者：)

sql查询基础以下是我⾃⼰本⼈整理的sql学习笔记，知识学习本⾝是⼀个不断遗忘的过程，通过不断地回顾，总结和实践，才能够⾃⼰消化，为⾃⼰所⽤。oracle数据库仍然是众多企业使⽤最多的数据库，⽽且，每个数据库的基本语法都差不多，因此，我在学习的过程中，都是以oracle数据库在练习sql，希望⾃⼰能够在今后⼯作和学习中，能够及时回过头看看，也可以提供给⼤家学习和参考。8293637383946474849565758not运算符:not between、not in、not lke、not exists、not is null=not is null*/

/*运算符优先级1.=、!=/<>、<、<=、>、>=[not]、null、[not]like、[not]between、[not]in、[not]

*/--查询⼈⼒资源部或财务部门哪些员⼯拥有奖⾦selectemp_name,dept_id,bonusfrom employee_new

where dept_id=2 or dept_id=3and bonus is not null;

--and运算符优先级⾼,上⾯语句查询的是⼈⼒资源部(dept_id=2)的员⼯或者财务部(dept_id=3)中拥有奖⾦的员⼯。--应改为：

selectemp_name,dept_id,bonusfrom 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(包括没有奖⾦)的员⼯逻辑运算符：逻辑与(and)、逻辑或(or)、逻辑⾮(not)--在关系运算中称为投影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中的⼀个特殊值，代表了缺失或者未知的数据5859666768697677787986878889969798995912--查询2018年1⽉1⽇之后⼊职，⽉薪少于5000，并且奖⾦⼩于1000(包括没有奖⾦)的员⼯selectemp_name,dept_id,salary,bonus

from employee_new

where hire_date>to_date(20180101,'yyyymmdd')

and salary<5000and (bonus<1000 or bonus is null);

select * from employee_new;

/*SQL提供了两种模糊匹配的⽅法: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_datefrom employee_new where dept_id=4 order by salary desc;--多列排序/*多列排序是指基于多个字段或表达式的排列，使⽤逗号进⾏分隔。⾸先，基于第⼀字段进⾏排序，对于第⼀字段排序相同的数据，再基于第⼆个字段进⾏排序。依此类推*/select emp_name,salary,hire_date

from employee_new

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

where dept_id=46486486486187188where dept_id=4order 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函数将空值转换为0selectemp_name,coalesce(bonus,0) as bonusfrom employee_newwhere dept_id=2order by coalesce(bonus,0);--查询所有员⼯信息，按照员⼯总收⼊（年薪+奖⾦）从⾼到底进⾏排序，总收⼊相同再按照姓名的拼⾳顺序排列selectemp_name,salary,nvl(bonus,0) as bonus,(salary*12+nvl(bonus,0)) as "总收⼊"from employee_neworder 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,salaryfrom employee_neworder by salary desclimit 5 offset 0;--分页查询/*分页查询的原理就是先跳过指定的⾏数，再返回Top-N记录*/select emp_name, salary from employee_new where salary =592222422522622722822923423523623723823924424524624724824925 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*/

selectemp_name,salaryfrom employee_neworder by salary descfetch first 10 percent rows only;--使⽤limit实现分页查询/*MySql以及postgresql实现*/select emp_name,salaryfrom employee_neworder by salary desclimit 5 offset 10;--其他实现⽅式--sqlserver实现select top(5) emp_name,salaryfrom employee_neworder by salary desc;--oracle实现/*可以使⽤⼦查询和rownum函数实现相同功能*/selectemp_name,salaryfrom

(select emp_name,salary,rownum as rnfrom(select emp_name,salary

from employee_neworder by salary desc))where rn>=11 and rn<=15;/*⼩结：查询语句中的fetch和offset⼦句可以限定返回结果的数量和偏移量，从⽽实现排⾏榜和分页查询效果。limit和offset⼦句也是实现该功能的⼀种常见的⽤法。*/--函数/*sql语句主要功能就是对数据进⾏处理和分析，为了避免重复造轮⼦，提⾼数据处理的效率，sql提供了许多标准的功能模块。sql函数是⼀种具有某种功能的模块，可以接收零个或多个输⼊值，并且返回⼀个输出值在sql中，函数分为两种类型：1.标量函数：对每个输⼊参数，返回⼀个输出结果2.聚合函数：基于⼀组数据进⾏计算，返回⼀个输出结果*/--绝对值函数selectabs(-2),abs(2)from employee_newwhere emp_id=1;2532542552562572582592642652662672682692742752762772782792842852862872882892942952962972982993316317318--字符串长度--⼤⼩写转换/*upper函数将字符转换为⼤写，lower函数将字符转换为⼩写*/select lower('SQL'),upper('sql') from dual;/*使⽤连接符将字符串进⾏连接*/select 's'||'q'||'l' from dual;--字符串拼接select concat('sql','world') from dual;--ASCIIselect ascii('sql'),chr(83) from dual;

--字符函数/*字符函数⽤于字符数据的处理，例如字符串的拼接，⼤⼩写转换，⼦串的查找和替换*/--⽣成随机数select dbms_ from dual;--最⼤值与最⼩值/*greatest与least函数分别计算参数列表中的最⼤值与最⼩值*/select greatest(1,2,3),least(1,2,3) from dual;--求余函数/*mod函数⽤于计算两个数相除的余数*/select mod(7,3) from dual;--平⽅根函数/*sqrt函数计算数据的平⽅根*/select sqrt(25) from dual;--对数函数/*ln函数计算以e为底的⾃然对数，log函数计算指定底数的对数*/selectlog(6,36),ln(2.705)from dual;--指数函数/*exp函数计算⾃然常数e的n次⽅，power函数计算指定底数的n次⽅*/selectexp(1),power(2,4)from dual;--取整函数/*ceil和ceiling函数向上取整，floor函数向下取整，round函数执⾏四舍五⼊运算*/selectceil(1.9),floor(4.5),round(9.456,2)from dual;332632732832933433533633733833934434534634734834935435535635735835936436536636736836937437537637737837938--字符串长度/*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函数⽤于在字符串中查找并返回⼦串的位置，没有找到时返回0replace函数⽤于替换字符串中的⼦串。*/select instr('2474872217@','@'),replace('2474872217@','@','.') 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_newwhere 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;

/*⽇期时间加上/减去⼀个时间间隔，可以得到⼀个新的⽇期时间*/38338438538638738838939439539639739839944424425426427428429434435436437438439444445446447448/*⽇期时间加上/减去⼀个时间间隔，可以得到⼀个新的⽇期时间*/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表达式表达式*/

--1.简单case表达式selectemp_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 departmentfrom 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 "数学",448449454455456457458459464465466467468469474475476477478479484485486487488489494495496497498499512513case 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_casegroup by sname;

--2.搜索case表达式/*所有简单case表达式都可以替换为等价的搜索case表达式*/selectemp_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表达式通常⽤于处理更加复杂的逻辑条件*/selectemp_name,salary,case when salary<10000 then '低收⼊' when salary<20000 then '中收⼊' else '⾼收⼊'end as gradefrom employee_new;

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

selectemp_name,case when bonus is null then 0 else bonus end as bonusfrom employee_newwhere dept_id=2order 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 departmentfrom employee_new;

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

select * from employee_new;

/*按照部门编号进⾏排序，并且确保同⼀个部门中的⼥性员⼯排在男性员⼯之前*/55525526527528529534535536537538539544545546547548549554555556557558559564565566567568569574575576577578/*按照部门编号进⾏排序，并且确保同⼀个部门中的⼥性员⼯排在男性员⼯之前*/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中，聚合函数⽤于对⼀组数据进⾏汇总计算，并且返回单个分析结果。常见的聚合函数包括：()()()()()_samp():计算⼀组数据的⽅差_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_newgroup by dept_id;5785795845855865875885895945955965975985996662462562662762862963463563663763863964group by dept_id;

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

--多字段分组/*group by也可以基于多个字段或表达式进⾏分组*/selectdept_id,sexfrom employee_newgroup by dept_id,sexorder by dept_id;

--按照性别统计员⼯数量和平均⼯资selectsex,count(1),avg(salary)from employee_newgroup by sex;

--按照部门和职位统计员⼯的数量和平均⽉薪selectdept_id,job_id,count(1),avg(salary)from employee_newgroup by dept_id,job_idorder by avg(salary) desc;

--基于表达式分组统计select extract(year from hire_date) as "⼊职年份",count(1) as "员⼯数量" from employee_newgroup 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;

--分组后的过滤selectdept_id,avg(salary)from employee_newgroup by dept_idhaving avg(salary)>10000;

/*在sql语句中可以使⽤where⼦句对表进⾏过滤，同时使⽤having对分组结果进⾏过滤*/selectdept_id,count(1)from employee_newwhere salary>10000group by dept_idhaving count(1)>2;

644645646647648649654655656657658659664select

sex,case

when salary<10000 then '低收⼊' when salary>20000 then '中收⼊' else '⾼收⼊'end as salary_level,count(1),avg(salary)from employee_newgroup by sex,case

when salary<10000 then '低收⼊' when salary>20000 then '中收⼊' else '⾼收⼊'endorder by sex;