SQL(二)
文章目录
9.1.3列值计算
-
语法
select 字段名 运算符 数值 from 表名
运算符
:+
,-
,*
,/
-
查询员工的名字和年薪
select last_name,salary * 12 from s_emp;
-
查看每个员工的员工id,名字和月薪涨100以后的年薪
select last_name,salary + 100 * 12 from s_emp;
上面的案例会先算 乘法再算加法,如果想要改变运算的优先级,可以通过
()
去改变select last_name,(salary + 100) * 12 from s_emp;
-
注意事项:
- 查询永远都不会改变表中数据值
- 乘除的优先级高于加减
- 优先级相同时,按照从左到右运算
- 可以使用括号改变优先级
9.1.4 列别名
-
查询员工名和年薪
select last_name,salary * 13 from s_emp;
发现这个列的名字已经改变,同时这个列名有时候也不符合我们的需求,所以我们可以给列起别名
-
语法:
select 字段名 [as] 别名 from 表名;
as
可以省略不要别名
别名可以用双引号包裹起来,以便包含特殊字符,只有此处会用双引号,其他的地方都是单引号 -
查看员工的员工id,名字和年薪,年薪列名为annual
select id,last_name,salary * 13 as annual from s_emp;
select id,last_name,salary * 13 as "annual" from s_emp;
/* sql 是不区分大小写,但是如果双引号包裹起来,里面的别名写了什么就是什么,区分大小写 */
9.1.5 拼接列
把多个列的数据拼接成一个列
-
语法
select 字段1 || 字段2 from 表名;
-
查看员工的员工id,全名
select id,last_name ||frist_name from s_emp; select id,last_name || '.' ||frist_name from s_emp; /* 单引号,拼接字符串 */
-
查看员工的员工id,全名和职位名称,全名和职位名称合并成一列显示,且格式为:姓名:xxx-xx,职位:xxx
select id,'姓名: ' || last_name || '-' || first_name || ', 职位: ' || title info from s_emp;
9.1.6 去重
将重复的数据去除掉
-
语法:
select distinct 字段1,字段2 from 表名;
-
查询公司里面的已有职位名称
select distinct title from s_emp;
-
注意事项:
-
distinct关键词只能放在select关键词后面,下面的语句就是报错了
select id,distinct title from s_emp;
-
如果是多列去重,如下test表:
Id Id2 1 2 1 3 2 4 3 4 3 4 select distinct id,id2 from test;
两列的值共同比较,如果两列的值完全一样则去掉,同理,三列,四列也是一样的。
查询结果如下:
Id Id2 1 2 1 3 2 4 3 4
-
-
查看所有员工的职位名称和部门id,同职位同部门的只显示一次
select distinct dept_id,title from s_emp;
9.1.7 排序
把查询出来的数据,进行排序展示。
注意: 是先查询出来以后才能排序展示。同时排序不改变表里面的数据
-
语法:
select col_name,... from tb_name order by col_name [asc|desc],...
- 排序使用order by字句,asc升序,desc降序 默认升序
- 该子句只对查询记录显示调整,并不改变查询结果,所以执行权最低,即最后执行
-
查询员工的工资和last_name,对工资进行升序排列
select salary,last_name from s_emp order by salary asc;
-
多列排序
如果有多个列排序,后面的列排序的前提是前面的列排好序以后有重复(相同)的值
例如(test)表
Id Id2 1 2 2 3 3 4 4 1 4 2 select id,id2 from test order by id,id2 desc;
结果:
Id Id2 1 2 2 3 3 4 4 2 4 1 -
查看员工的id,名字和薪资,按照薪资的升序排序显示,如果薪资相同则按照id降序排列。
select id,last_name,salary from s_emp order by salary,id desc;
-
order by 后面还可以跟数字,表示使用select后面的第几个列进行排序
使用last_name列进行排序
select last_name,salary from s_emp order by 1;
使用salary列进行排序
select last_name,salary from s_emp order by 2 desc;
-
null值排序规则
升序排列,默认情况下,null值排后面
降序排序,默认情况下,null值排前面
9.1.8 限制查询
指定条件查询,对数据进行筛选
-
语法
select col_name,... from tb_name where col_name 比较操作表达式 逻辑操作符(and|or) col_name 比较操作表达式;
-
比较符
=
>
<
>=
<=
不等于:三个都表示不等于的意思
!=
<>
^=
-
查看员工工资小于1000的员工id和名字
select id,last_name from s_emp where salary < 1000;
-
逻辑比较符
-
BETWEEN … AND …
between x and y:在什么范围之内 [x,y] -
查看员工工资在700 到 1500之间的员工id,和名字
select id,last_name from s_emp where salary between 700 and 1500;
-
IN(list)
在一个列表中筛选
-
查看员工号1,3,5,7,9员工的工资
select id,last_name,salary from s_emp where id in(1,3,5,79); /* 括号里面的数据or关系 不一定非要存在,也可以不存在*/
-
查看名字为Ngao或者Smith的员工信息
select * from s_emp where last_name in ('Ngao','Smith');
-
LIKE
即值不是精确的值的时候使用
通配符,即可以代替任何内容的符号
%
:通配0到多个字符
_
: 当且仅当通配一个字符
转义字符:
默认为,可以指定 指定的时候用escape 符号指明即可,转义字符只能转义后面的一个字符 -
查看员工名字以C字母开头的员工的id,工资
select id,salary,last_name from s_emp where last_name like 'C%';
-
查看员工名字长度不小于5,且第四个字母为n的员工id和工资
select id,salary,last_name from s_emp where last_name like '___n_%';
-
查看员工名字中包换一个_的员工id和工资
select id,salary,last_name from s_emp where last_name like '%\_%' escape '\';
-
Is nul
对null值操作特定义的操作符,不能使用=
-
查看员工提成(commission_pct)为空的员工的id和名字
select id,salary,last_name from s_emp where commission_pct is null;
-
-
逻辑操作符
and:且逻辑
or: 或逻辑
注意:and优先级比or优先级要高-
查看员工部门id为41且职位名称为Stock Clerk(存库管理员)的员工id和名字
select id,last_name from s_emp where dept_id = 1 and title = 'Stock Clerk';
-
查看(员工部门为41) 或者 (44号部门 且工资大于1000的)员工信息
select * from s_emp where (dept_id = 41) or (dept_id or salary > 1000);
-
查看(员工部门为41或44) 且(工资大于1000的)员工信息
select * from s_emp where dept_id in(41,42) and salary > 1000;
not:非逻辑
NOT BETWEEN AND
NOT IN
NOT LIKE
IS NOT NULL-
查看员工提成不为空的员工信息
select * from s_emp where commission_pct is not null;
-
查看员工名字不是以C字母开头的员工信息
select * from s_emp where last_name not like 'C%';
-
实例:
/*
列值计算
1、查询员工的编号、姓名、薪水
*/
select id,last_name,salary
from s_emp;
/*
查询员工的编号,名字,年薪
*/
select id,last_name,salary*16
from s_emp;
/*
列起别名
查询员工的编号,名字,年薪(aual)
*/
select id,last_name,salary*12 aual
from s_emp;
/*
双引号里面写什么显示什么
*/
select id,last_name,salary*12 "aual"
from s_emp;
/*
列的拼接
把多列数据拼接成一列,但是并没有改变存储的值,
只是把查询出来的数据进行拼接
查询员工的编号 全名 薪水
*/
select id,last_name||first_name,salary
from s_emp;
--名.姓
--||可以拼接字符串 字符串用''包裹起来
select id,last_name||'.'||first_name,salary
from s_emp;
/*
查询员工的编号,以及员工的全名和职称(title)信息,全名和职称一列展示
姓名:xxx-xxxx,职位:xxx
*/
select id,'姓名:'||last_name||'-'||first_name||',职位:'||title
from s_emp;
/*
查询每一个员工信息 姓名:xxx-xxxx,职位:xxx emp_info
以及16薪(aual)
*/
select id,'姓名:'||last_name||'-'||first_name||',职位:'||title emp_info,salary*16 aual
from s_emp;
/*
一列中有重复的数据,去掉重复数据去重
distinct:必须加在select,不能加在查询字段名后面
*/
select distinct title
from s_emp;
/*
对多个列去重
distinct 字段1,字段2
*/
select distinct title,dept_id
from s_emp;
/*
排序
讲查询出来的数据,按照指定的顺序进行排列然后展示
select 字段名,...
from 表名
order by 字段名 排序规则;
查询每一个员id,全名(name)薪水, 并且按照薪水的高低进行降序排序
*/
select id,last_name||'.'||first_name name,salary
from s_emp
order by salary desc;
--排序先排序好再查询出来,还是先查询出来数据然后再进行排序?
--排序永远都是先将数据查询出来,再去排序,也就是order by执行的级别是最低的
--查询员工的名字编号以及薪水(升序排序)
select id,last_name||'.'||first_name name,salary
from s_emp
order by salary asc;
--查询员工的名字,编号,薪水,提成(COMMISSION PCT, 按照提成进行降序排序
select id,last_name,salary,COMMISSION_PCT
from s_emp
order by COMMISSION_PCT desc;
/*
sql中空值不是字符串'null' ''
空值的排序规则就是无限大
*/
--查询员工的名字,编号,按照员工的名字进行升序排序
select id,last_name
from s_emp
order by last_name asc;
/*
如果按照字符串去进行排序,那么就先去比较第一个字母,如果有结果,直接排序,后面的字符不比较
如果是第一个字母项目,再去比较第二字母,然后有结果,排序,后面字母不比较,以此类推
*/
--多列排序
--查询员工编号、名字、薪水,按照薪水降序排序,如果薪水一样,按照编号进行升序排序
select id,last_name,salary
from s_emp
order by salary desc,id asc;
/*
如果有队列进行排序,那么就是先根据第一列进行排序
如果值不同,则按照第一列进行排序,
只有当第一列的值相同再根据第二列值排序
*/
--查询员工编号,名字,薪水,按照薪水降序排序,如果薪水-样,那么就按照名字进行降序
select id,last_name,salary
from s_emp
order by salary desc,last_name desc;
-----------------------------------
--限制查询,根据条件查询想要的数据而不是表中全部的数据
/*
select col_name1,col_name2,...
from table_name
where 比较表达式 逻辑操作符 比较表达式 逻辑操作符 比较表达式
*/
-- 比较表达式 > = < >= <= (不等于 != <> ^=)
--查询员工编号大于10的员工信息
select *
from s_emp
where id>10;
--查询薪水超过1500包含1500的员工编号名字薪水
select id ,last_name,salary
from s_emp
where salary>=1500;
--查询员工编号为1的员工信息
select *
from s_emp
where id = 1;
--查询员工编号不是2的员工信息
select *
from s_emp
where id != 2;
--逻辑比较符
/*
between x and y
[x,y] xxx>x&&xxx<y
*/
--查询员工薪水在 1500 到 2000之间的员工编号名字和薪水(包含1500、2000)
select id,last_name,salary
from s_emp
where salary between 1500 and 2000;
/*
dept_id in(x,y,z) dept_id = x||dept_id = y||dept_id = z
*/
--查询员工编号为 1,3,5,79,80的员工信息
select *
from s_emp
where id in(1,3,5,79,80);
--查询员工名字为Ngao、Simth的员工信息
select *
from s_emp
where last_name in('Ngao','Simth');
--Like 模糊查询
/*
%匹配0到多个字符
_代表匹配任意一个字符
*/
--查询名字包含N的员工信息
--不管N开头还是N结尾还是之间包含N查询出来
select *
from s_emp
where last_name like '%N%';
--查询以N开头的员工信息
select*
from s_emp
where last_name like 'N%';
--查询a结尾的员工信息
select*
from s_emp
where last_name like '%a';
-- Like
-- %匹配0-多个字符
-- _匹配一个字符
--查询所有
select *
from s_emp
where last_name like '%%';
--精确查找
select *
from s_emp
where last_name like 'Ngao';
-- 查询第二个字母是a的员工信息
select *
from s_emp
where last_name like '_a%';
--查询名字只有五个字符的员工信息
select *
from s_emp
where last_name like '_____';
--查询倒数第二个字母为n的员工信息
select *
from s_emp
where last_name like '%n_';
--查询倒数第五个字母为h的员工信息
select *
from s_emp
where last_name like '%h____';
--查询名字中包含下划线的员工信息(进行转义)
select *
from s_emp
where last_name like '%/_%';
select *
from s_emp
where last_name like '%\_%' escape'\';
-- is null
-- 查询提成为空的员工信息
select *
from s_emp
where commission_pct is null;
--逻辑操作符
--用来连接一个或者多个条件
--and or
--查询员工编号大于10且薪水超过600的员工信息
select *
from s_emp
where id>10 and salary>600;
--查询员工的新书大于等于600,小于等于1500
select *
from s_emp
where salary>=600 and salary<=1500;
--查询名字中包含N的,同时薪水在600到1500之间的员工信息(不包含600、1500)
select *
from s_emp
where last_name like '%N%'and salary>600 and salary<1500;
-- or是或者的意思 也是用来连接多个条件
-- 主要满足其中一个就会查询出数据
--查询名字中包含N的,或者薪水在600以上(不包括600)
select *
from s_emp
where last_name like '%N%' or salary>600;
--and的优先级比or更高,同时存在先执行and再执行or
-- 查看(员工部门为41)或者(44号部门且工资大于1000的员工信息
--先查询整个部门编号是44薪水大于1000,再去查询部门编号是41
select *
from s_emp
where dept_id=41 or dept_id=44 and salary>1000;
--通过括号改变优先级 先看部门再看薪资
select *
from s_emp
where dept_id=41 or (dept_id=44 and salary>1000);
-------------------------------------------------
/* 非
not in 在 xx列表里面
not between x and y
not like
is not null
*/
--查询不是41或者44部门编号的员工信息
select *
from s_emp
where dept_id not in(41,44);
--查询薪水不在600到1500以内的员工信息
select *
from s_emp
where salary not between 600 and 1500;
--查询名字不包含N的员工信息
select *
from s_emp
where last_name not like '%N%';
-- 查询拥有提成的员工信息
select *
from s_emp
where commission_pct is not null;
--查询包含N的员工信息同时其薪水在1000以上,结果按照薪水降序排序
--如果薪水相同按照名字升序排序
select *
from s_emp
where salary>1000 and last_name like '%N%'
order by salary desc,last_name asc;
9.2 函数
9.2.1 函数分类
函数分为两种:
-
单值函数
-
组函数(讲完分组函数以后再说)
函数的定义跟java方法差不多,必须有返回值,单值函数主要是用来处理一个列中多行数据。例如将某一列的字母数据全部转换成大写等等之类的。
9.2.2 哑表
在oracle中存在着一张不存在的、虚拟的表,这个表只有一行一列数据。这个表叫做哑表(dual)。
主要是用来校验 select 数据的语法。这个表里面的唯一条数据就是查询出来的数据。
select 1+1 from dual;
9.2.3 单值函数分类
- 字符函数
- 日期函数
- 转换函数
- 数字函数
9.2.4 字符函数
-
可以操作表中的列,也可以操作普通字符串
-
LOWER 返回转换成小写的字符串
-
把’HELLO’转换为小写
select lower('HELLO') from dual;
-
把 s_emp 表中的 last_name 列的值转换为小写
select lower(last_name) from s_emp;
-
-
UPPER 转换为大写的字符串
-
把’world’转换为大写
select upper('world') from dual;
-
把 s_emp 表中的 last_name 列的值转换为大写
select upper(last_name) from s_emp;
-
-
INITCAP 首字母大写的字符串
-
把’hELLO’转换为首字母大写,其余字母小写
select initcap('hELLO') from dual;
-
将s_emp中的userid数据 转成 首字母大写
select upper(last_name) from s_emp;
-
-
CONCAT 连接字符串
类似于前面的 ||
-
把’hello’和’world’俩个字符串连接到一起,并且起个别名为 msg
select concat('hello','world') msg from dual; select 'hello' || 'world' from dual;
-
如果想连接多个,需要嵌套使用
将AA,BB,CC 拼接起来
select concat('AA',concat('BB','CC吗')) from dual; select 'AA' || 'BB' || 'CC' from dual;
-
-
SUBSTR 返回截取的字符串
substr(操作项,start,len) 截取字符串
-
截取’hello’字符串,从第 2 个字符开始(包含第二个字符),截取后面连续的 3 个字符
select substr('hello',2,3) from dual;
-
LENGTH 返回字符串的长度
-
获得’world’字符串的长度
select length('world') from dual;
-
获得 s_emp 表中 last_name 列的每个值的字符长度
select length(last_name) from s_emp;
-
-
NVL 转换为空值
nvl(arg1,arg2) 如果参数为空,返回参数2
select nvl(null,1) from dual; select nvl(commission_pct,0) from s_emp;
nvl2(arg1,agr2,agr3) 如果参数为空,返回参数3,不为空返回参数2
select nvl2(null,1,10) from dual; select nvl2(commission_pct,0,100) from s_emp;
nullif(arg1,arg2) 如果参数1=参数2 那么就返回null,否则返回参数1
select nullif(1,1) from dual;
-
9.2.5 函数嵌套使用
-
查询 名字首字母大写的员工信息
select * from s_emp where substr(last_name,1,1) = substr(initcap(last_name),1,1);
-
查询 最后一个字母为小写的员工信息
select * from s_emp where substr(last_name,length(last_name) - 1,1) = substr(lower(last_name),length(last_name) - 1,1);
-
查询 员工首字母为 N 的员工信息
select * from s_emp where substr(last_name,1,1) = 'N';
实例
select *
from dual;
select 1+1
from dual;
-- upper
select upper('abc')
from dual;
-- 将员工所有的名字转换为大写
select upper(last_name)
from s_emp;
--lower
select lower('HELLO')
from dual;
-- 将员工所有的名字转换为小写
select lower(last_name)
from s_emp;
-- initcap
select initcap('abc')
from dual;
-- 将员工所有的名字首字母转换为大写
select initcap(last_name)
from s_emp;
--截取字符串 将are you ok?
--从第二个位置开始截取,往后连续截取4个字符
select substr('are you ok?',2,4)
from dual;
--截取员工名字,从第三个位置开始截取,连续截取2字符
select substr(last_name,3,2),last_name
from s_emp;
--length 求字符串长度
select length('hello')
from dual;
select length(last_name),last_name
from s_emp;
--concat连接字符串 类似||(oracle特有)
--只能俩个参数
select concat('hello','world')
from dual;
--拼接三个
select concat(concat('hello','are'),'you!')
from dual;
/*
nvl(args1,args2)对空值进行处理
如果agr1为空,那么就返回agr2,如果agr1不为空, 那么是什么就显示什么
nvl2(args1,args2,args3)对空值进行处理
如果arg1为空返回arg3非空arg2
nullif(args1,args2)对空值进行处理
如果参数1=参数2 那么就返回null,否则返回参数1
*/
select nvl(null,1)
from dual;
--查询员工编号姓名薪水提成,如果提成为空显示0
--如果部位空显示其提成
select id,last_name,salary,nvl(commission_pct,0)
from s_emp;
select nvl2(null,1,3)
from dual;
--显示员工信息,提成为空显示要努力,如果不为空显示真棒
select id,last_name,nvl2(commission_pct,'真棒','努力'),commission_pct
from s_emp;
select nullif(1,2)
from dual;
--查询最后员工字母为o的员工信息
--1、先获取最后一个字母
--2、将最后一个字母于o进行比较
select *
from s_emp
where substr(last_name,length(last_name),1)='o';
--查询员工首字母为大写N的员工信息
--1、先获取首字母N
--2、将员工名字转为大写(NGAO),截取首字母(N)
--3、相等则查询出来 Ngao
select *
from s_emp
where substr(last_name,1,1)=substr(upper(last_name),1,1);