用来测试用的table.sql文件上传到了我的资源库里面。。。如果要用的话可以去下,只是测试用的,三个表,各个表具体内容如下:
部门表
区域表
员工表
具体内容太多,这里显示部分内容
1、基础查询
1.1 基本使用
例一:查询s_dept表中所有记录
select *
from s_dept;
例二:查询s_dept表中的id,name,region_id三个字段
select id,name,region_id
from s_dept;
例三:查看所有员工,名字(last_name)和薪资(salary)
select id,last_name,salary
from s_emp;
1.2 加上基础运算
例一:查看每个员工的id,名字和年薪
select id,last_name,salary*12
from s_emp;
例二:查看每个员工的员工id,名字和月薪涨100以后的年薪
select id,last_name,(salary+100)*12;
from s_emp;
1.3 给字段起别名
新名字写在就名字的后面
语法:
select old_column[as] new_column_name from table_name;
中括号里面的as可有可无
例一:查看员工的员工id,名字和年薪,年薪列名为annual
select id,last_name,salary*12 as annual
from s_emp;
或者
select id,last_name,salary*12 annual
from s_emp;
1.4 拼接
语法:
select col_name||'spe_char'||col_name from table_name;
例一:查看员工的员工id,全名
select id,first_name||last_name
from s_emp;
例二:查看员工的员工id,全名和职位名称,全名和职位名称合并成一列显示,且格式为:姓名,职位名称
select id,first_name||' '||last_name||','||title as name
from s_emp;
|| 表示拼接连个字段,例如上面的first_name||’ ‘||last_name表示将first_name和空格’ '和last_name拼接在一起组成一个新的字段
1.5 替换
使用nvl函数可以将null进行替换
用法:
select nvl(col_name,change_value) from table_name;
例题:查询所有员工的员工id,名字和提成,如果提成为空,则显示成0
select id,last_name,nvl(commission_pct,0) commission_pct
from s_em;
1.6 去重
distinct关键字可以去除重复数据
语法:
select distinct col_name,col_name...
from table_name;
distinct关键词只能放在select关键词后面
如果distinct关键词后面如果出现多列,表示多列联合去重,即多列的值都相同的时候才会认为是重复的记录。
例如:查看所有员工的职位名称和部门id,同职位同部门的只显示一次
select distinct dept_id,title
from s_emp;
1.7 调字段宽度
使用 format 可以将查询结果显示的宽度进行调整。
当还未调整显示结果宽度时
select id,last_name
from s_emp;
结果如下:
使用format调整last_name列所占的宽度,其实就是“-”的个数
例如:
//表示last_name列的下方有15个“-”
column last_name format a15;
//或者简写成
col last_name for a15;
//之后在查看下格式
select id,last_name
from s_emp;
结果显示如下:
清除设置好的格式
clear column
//或者
clear col
format只能设置字符类型的字段列,否则会乱码
2、条件查询
语法:
select col_name,...
from table_name
where 筛选条件
限制查询条件,使用where子句;
条件可以多个,使用逻辑操作符或者小括号进行条件的逻辑整合;
where子句的优先级别最高;逻辑比较操作符= > < >= <= !=
不等于操作符,以下三个都表示不等于的意思,经常用的是 !=
!= <> ^=
按条件表达式筛选
条件表达式:= > < >= <= !=
不等于操作符,以下三个都表示不等于的意思,经常用的是 !=
!= <> ^=
例题:查看员工工资小于1000的员工id和名字
select id,last_name,salary
from s_emp
where salary<1000;
按逻辑表达式筛选
逻辑运算符:and or not
作用:连接条件表达式,当条件有多个的时候可以使用。
注意:and比or的优先级要高
注意:
& 用来创建一个临时变量,每当遇到这个临时变量时,都会提示你输入一个值。
&&用来创建一个持久变量,当用&&命令引用这个变量时,不会每次遇到该变量就提示用户键入值,而只是在第一次遇到时提示一次。
和mysql中不一样,mysql中and 就等于 && ,or就等于|| ,not 就等于!,但oracle中是有区别的
例一:查看员工部门id为41且职位名称为Stock Clerk(存库管理员)的员工id和名字
select id,last_name,dept_id,title
from s_emp
where
dept_id = 41
and
title = 'Stock Clerk';
例二:查看员工部门为41 或者 44号部门,且工资大于1000的员工id和名字
select id,last_name,dept_id,title
from s_emp
where salary > 1000
and
(dept_id = 41
or
dept_id = 44
);
例三:查看员工部门为41且工资大于1000,或者44号部门的员工id和名字
select id,last_name,dept_id,title
from s_emp
where salary > 1000and
dept_id = 41
or
dept_id = 44;
例三和例二的结果是不一样的,区别在于条件中有无圆括号
模糊查询
between…and…表示在俩个值之间
例题:查看员工工资在700 到 1500之间的员工id和名字
select id,last_name,salary
from s_emp
where salary between 700 and 1500;
//其实等于下面的
select id,last_name,salary
from s_emp
where
salary>=700
and
salary<=1500;
in() 表示值在一个指定的列表中
例题:查看员工1,3,5,7,9员工的工资
select id,last_name,salary
from s_emp
where id in(1,3,5,7,9);
like 在值不精确的时候使用
通常会结合通配符使用:
%,通配0到多个字符
-,通配一个字符,并且是一定要有一个字符
\,转义字符,需要使用escape关键字指定,转移字符只能转义后面的一个字符
例一:查看员工名字以C字母开头的员工的id,工资
select id,last_name,salary
from s_emp
where last_name like 'C%';
例二:查看员工名字长度不小于5,且第四个字母为n字母的员工id和工资
select id,last_name,salary
from s_emp
where last_name like '___n_%';
例三:查看员工名字中包换一个_的员工id和工资
select id,last_name,salary
from s_emp
where last_name like '%\_%' escape '\';
is null和is not null
注意:判断值为null的时候使用,null值的判断不能使用等号
例题:查看员工提成为为空的员工的id和名字
select id,last_name,commission_pct
from s_emp
where commission_pct is null;
3、排序查询
语法:
select col_name
from table_name
order by col_name [asc|desc];
注意:
(1)order by语句,只对查询记录显示调整,并不改变查询结果,所以执行权最低,最后执行
(2)排序的默认值是asc:表示升序,desc:表示降序
(3)如果有多个列排序,后面的列排序的前提是前面的列排好序以后有重复(相同)的值。
例题:查看员工的id,名字和薪资,按照薪资的降序排序显示,工资相同就按名字升序排
select id,last_name,salary
from s_emp
order by salary desc,last_name;
先升序排第一列,如果第一列有重复的值,再降序排第二列,以此类推
4、函数
oracle数据库中内置了很多常用的函数
4.1 单行函数
也可以称为单值函数,每操作一行数据(某个字段值),都会返回一个结果。
哑表
Oracle中,有一张特殊的表:dual
dual被称之为哑表,它是一个单行单列的虚拟表,是Oracle内部自动创建的,这个表只有1列:DUMMY,数据类型为VERCHAR2(1),dual表中只有一个数据’X’,Oracle有内部逻辑保证dual表中永远只有一条数据。
如:
在实际使用中,Dual表主要用来选择系统变量或求一个表达式的值,因为要使用dual来构造完成的查询
例如,查询表达式1+1的结果
select 1+1 from dual;
按照sql语句的要求,没有表就没法查询,而表达式1+1,不属于任何表,那么就有了哑表dual的概念了,但注意,只有只有oracle数据库中有这个哑表 dual
4.1.1 字符函数
【】里面的内容表示可以加上可以不加上
1、ASCII(X),返回字符X的ASCII码
select ascii('a') as result from dual;
2、CONCAT(X,Y),连接字符串x和y
select concat('hello','world') as result from dual;
3、INSTR(X,STR, [, START ] [,N]),从X中查找str,可以指定从start开始,也可以指定从n开始
例一:
select instr('Hello World','o') as result from dual;
从第一个字母H开始, o是第五个字母
例二:
select instr('Hello World','o',6) as result from dual;
注意,这里从第6个字母空格开始,空格后前后各有一个o字母,这里取得是后面一个o字母,即为第8的位置
例三:
select instr('Hello World','o',-1) as result from dual;
这里是从最后一个开始,从后往前找,但每个字母的顺序都已经固定,所以找的是第二个o的序号,即从前往后数第8的位置
4、LENGTH(X), 返回X的长度
select length('world') from dual;
5、LOWER(X),X转换成小写
select lower('HELLO') from dual;
6、UPPER(X),X转换成大写
select upper('hello') from dual;
7、INITCAP(X) ,X首字母转换为大写,其他字母小写
select initcap('hello') from dual;
8、LTRIM(X[,TRIM_STR]),把X的左边截去trim_str字符串,缺省截去空格
select LTRIM('--hello--','-') from dual;
这里是将左边的‘-’全部都截去了
9、RTRIM(X[,TRIM_STR]),把X的右边截去trim_str字符串,缺省截去空格
select RTRIM('--hello--','-') from dual;
这里是将右边的‘-’全部都截去了
10、TRIM([TRIM_STR FROM X),把X的两边截去trim_str字符串,缺省截去空格
11、REPLACE(X,old,new),在X中查找old,并替换成new
select replace('cast','a','o') from dual;
12、SUBSTR(X,start[,length]),返回X的字串,从start处开始(包括start),截取length个字符,缺省length时,默认到结尾
select substr('hello',2,3) as result from dual;
4.1.2 数字函数
专门操作数字的函数,常用的数字函数有:
有如下需要特别注意:
1、ROUND(X[,Y]),X在第Y位四舍五入,第一个参数表示要进行四舍五入操作的数字,第二个参数表示保留到哪一位
select round(31.415,2) as result from dual;
保留到个位时
select round(31.415,0) as result from dual;
如果第二个参数不写,默认就是0,表示保留到个位
保留到十位时,
select round(31.415,-1) as result from dual;
2、TRUNC(X[,Y]),X在第Y位截断,trunc和round的用法一样,但是trunc只会舍去不会进位
select trunc(31.415,2) as result from dual;
3、MOD(X,Y),X除以Y的余数,第一个参数表示要进行取余操作的数字,第二个参数表示参数1和谁取余
select mod(10,3) from dual;
4.1.3 日期函数
sysdate,是Oracle中用来表示当前时间的关键字,并且可以使用它来参与时间运算
//显示当前时间
select sysdate from dual;
//显示明天这个时候
select sysdate + 1 from dual;
//显示昨天这个时候
select sysdate - 1 from dual;
//显示1小时之后的这个日期
select sysdate + 1/24 from dual;
sysdate参与时间的加减操作的时候,单位是天
特别注意,oracle中不同的会话环境中,日期数据默认的格式也不同,如下
中文环境下:
alter session set nls_language='simplified chinese';
select sysdate from dual;
英文环境下:
alter session set nls_language=english;
select sysdate from dual;
常见日期函数:
1、months_between
例如,30天之后和现在相差多少个月
select months_between(sysdate+30,sysdate) as result
from dual;
2、add_months
例如,指定日期,往后推2个月
中文环境下
select add_months('01-10月-2020',2) as result from dual;
英文环境下
select add_months('01-OCT-2021',2) as result from dual;
注意,这个数字也可以是负数,表示之前多少月
3、next_day
例如:离当前时间最近的下一个星期5是哪一个天
中文环境下:
select next_day(sysdate,'星期五') from dual;
英文环境下:
select next_day(sysdate,'FRIDAY') from dual;
4、last_day
例如,当前日期所在月份的最后一天(月底)
select last_day(sysdate)
from dual;
5、round
例如,把当前日期四舍五入到月
假设今天日期为22-11月-21
select round(sysdate,'MONTH') from dual;
当‘日’那天大于15时会使得‘月’进位
例如,把当前日期四舍五入到年
select round(sysdate,'YEAR') from dual;
当‘月’大于6时,会让年份进位
6、trunc
对日期进行截取,和round类似,但是只舍弃不进位。
select trunc(sysdate,'MONTH') from dual;
可以看到月份部分并没有和round一样进行进位
4.2 转换函数
可以将一个类型的数据转换为另一种类型的数据。主要有以下三种
1、TO_CHAR,把一个数字或者日期函数转换为字符
2、TO_NUMBER,把字符转换为数字
3、TO_DATE,把字符转换为日期
4.2.1 TO_CHAR
数字转字符
常用格式:
例如:
select to_char(salary,'$999,999,00') as result from s_emp;
结果:
RESULT
$25,00
$14,50
$14,00
$14,50
$15,50
$12,00
$12,50
$11,00
$13,00
......
select to_char(salary,'L999,999.00') as result from s_emp;
结果
RESULT
¥2,500.00
¥1,450.00
¥1,400.00
¥1,450.00
¥1,550.00
¥1,200.00
¥1,250.00
¥1,100.00
¥1,300.00
.......
select to_char(-10,'999PR') as result from dual;
日期转字符
常用格式:
例如:
select to_char(sysdate,'yyyy mm MONTH mon MON D DD DDD DAY DY') from dual;
select to_char(sysdate,'dd-mm-yy') from dual;
select to_char(sysdate,'yy-mm-dd hh:mi:ss') from dual;
4.2.2 TO_NUMBER
把字符转换为数字
select to_number('1000')
from dual;
但注意,字符字母abc等不能转换为数字
4.2.3 TO_DATE
把字符转换为日期
select to_date('10-12-2022','dd-mm-yyyy') as result from dual;
alter session set nls_language='simplified chinese';
select to_date('25-5月-22','dd-month-yy') as result from dual;
select to_date('22/5月/25','yy/month/dd') as resultfrom dual;
alter session set nls_language=english;
select to_date('25-MAY-22','dd-MONTH-yy') as result from dual;
4.2 聚合函数
也可以称为多行函数、分组函数、组函数,它可以操作多行数据,并返回一个结果,一般会结合着group分组来使用,当然也可以单独使用,那么默认全部数据就是一个小组。
聚合函数可以操作多行数据,并按要求计算出一个结果,一般会结合分组操作,把一堆数据按照某个条件,划分为几个不同的小组,然后对每个小组执行聚合函数,最后每个小组得出一个结果。
常用分组函数:
avg,求平均值
count,计算有多少条数据
max,求最大值
min,求最小值
sum,求和
求员工平均工资,所有员工工资总和,最大工资,最小工资,员工总数
select avg(salary) from s_emp;
select sum(salary) from s_emp;
select max(salary) from s_emp;
select min(salary) from s_emp;
select count(*) from s_emp;