oracle第一天，select和函数

sql 语句四类

1.DQL 主要查询

2DML：数据操作语言，对数据进行操作

3DDL: 数据定义语言 关于定义的操作（ create，after,drop ）

4DCL： 数据控制语言，主要针对权限授予和回收 （ grant revolce)

1.select 查询字段 from 表

调整字符宽度

colum tname format a30

关键词 表里列的名字 关键词 30个字符的宽度

SQL> select ename,sal,sal*12 as nianxin from emp;

#该语句中，查询2个字段，其中sal字段为工资，sal*12表示工资乘以12，即计算年薪 as 别名，nianxin 是自己写的，相当于临时字段

注意这个查询语句查到的计算结果不会记录进表中，不建议使用这种方式进行计算，不记录还占用内存

SQL> select ename a,sal b,sal*12 "nian xin" from emp;

#查询的每个字段都可以别名，别名为长字符时如有空格需要用“”引号

||链接符，在ORACLE中可以链接任意字段，和符号，目前用处相对简单

select ename||","||sal||":"||dklfjdl aa from emp

去除重复项，该例子中查询到用工作职位的选项，只有几种，通过distinct加列名的方式去重

SQL> select distinct job from emp;

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL> select ename,(sal+comm)*12,sal*12+comm from emp;

ENAME (SAL+COMM)*12 SAL*12+COMM

---------- ------------- -----------

SMITH

ALLEN 22800 19500

WARD 21000 15500

JONES

MARTIN 31800 16400

BLAKE

CLARK

SCOTT

KING

TURNER 18000 18000

ADAMS

JAMES

FORD

MILLER

这个例中，原表中因有人没有奖金相当于空，null值，运算公式又要求必须运算，无法比较NULL的大小所以显示空白

空值做任何运算都等于空值

后面要讲的函数,nvl 作用为列里的数据有空值，则赋予一个数值代替，如COMM列里有人为NULL，则用0代替值进行运算

切记一定时列的名字

SQL> select ename,sal*12+nvl(comm,0) from emp;

2.1where条件查询

select * from emp where job='clerk' or job='abc'

3.SQL> alter session set nls_date_format='yyyy-mm-dd';

#这个语句的为设置当前会话的时间显示格式为国际通用，其中session为会话，nls为国际通用

然后时设置的内容，data表示日期类型，format为格式，后面时具体显示方式

#SQL> select sysdate from dual;

这个语句是在SQL中读取系统时间用的，其中dual是系统准备的空表，没有数据，满足格式用的

SQL> select * from emp

2 where ename like '_A%';

模糊查询，条件语句后接 like ，本例中，查询要求第二位字符为A，后面多少为不管的输，注意

ORACEL中_ 是一个位置的通配符，%是所有通配符，不是？

3SQL> select * from emp2 where ename like '%\%%' escape '\';

查询名字中有%的人，因为%为通配符需要设定反义，\为换位码，意思为这个符号后面的字符只有原来的意思，不在有特殊含义

escape 为关键词

查询中的函数应用，例：

SQL> select * from emp where deptno in (10,20)

查询数字在10到20之间

SQL> select * from emp where deptno not in (10,20);

not in，意思就是不在设置区间的数字，这个例子就是去除10和20编号的

排序

SQL> select * from emp where deptno in(10,20) order by empno;

这一条是最简单的升序排列，也就是从小往大，格式为条件查询后加order by 加列名

SQL> select * from emp where deptno in(10,20) order by sal desc,empno;

这一条是以sal字段降序排列，也就是从高向低，如果有相同数据，则取工号empno这个值的升序排列

SQL> select * from emp order by comm desc nulls last;

这条语句中以comm字段排序，因为这里有空值，后面加的语句 nulls last 意思为空值放在后面

SQL> select * from (select * from emp order by sal desc) haha where rownum<=5;

#这个要注意下，该语句理解为，加你了一个临时表，查看表EMP的数据，并以SAL降序排列 ，最后rownum这个字段为

隐藏字段，没张表都有的，是一个行号隐藏，着张临时表里就表示降序排列后，截取小于等于5的行，也就是前5位

select tt.*,rownum owa from (select t.*,rownum ina from emp t order by sal desc) tt;

新建临时tt.* rownum owa 意思显示查看临时表t.*,和rownum这个隐藏值，并给个别名 WOA，显示的结果是TMP的，并给

原表的ROWNUM列一个别名IWA，并且给工资降序排列

select t.* from emp t order by sal desc

offset 0 row

fetch next 5 row only;

窃取一张表中第5行到第10行的数据，记录从0开始，也就是0是第一行，相当于步长的概念

然后offset,设置起始，也就是从第几行开始，例子中是0，也就是第一行，如果要取5到10名开始就是5

然后fetch next表示接下来要取几行设定为5就是5行

select t.* from emp t order by sal desc

offset 5 row

fetch next 5 row only;

偏移量这个查询方式从12C开始，11G是没有的

单行函数

1lower 所有输出的字符串全部转换为大写

SQL> select lower('hoW ArE you') from dual;

LOWER('HOWA

-----------

how are you

然后list可以显示上一次语句也可以简写为l

2UPPER 所有输出的字符都为小写

change 替换/原来的函数/替换后的函数

例子

SQL> change /lower/upper

1* select upper('hoW ArE you') from dual

SQL> run

1* select upper('hoW ArE you') from dual

UPPER('HOWA

-----------

HOW ARE YOU

用lower替换upper，系统会自动询问是否运行上一样的语句，输入RUN，然后运行

change 可以简写为C

SQL> c /upper/initcap

1* select initcap('hoW ArE you') from dual

SQL> /

INITCAP('HO

-----------

How Are You

#initcap 所有输出的字符首字母大写

substr 截取字符串，默认从左开始截取，不设置长度截取到最后一位，下面为例子

SQL> select substr('15038572400',4,4) from dual;

SUBS

----

3857

LENGTH 计算字符串的长度

SQL> select length('abc') from dual;

LENGTH('ABC')

-------------

3

instr 定位一个字符串的位置

SQL> select instr('15038572400','0',1,2)

2 from dual;

INSTR('15038572400','0',1,2)

----------------------------

10

启动第一个选项为要查找的字符串，比如0，1的意思为从左边开始第1位开始查第2次出现的0

lpad,rpad 左右补齐，根据填写的位数和字符进行补齐操作

SQL> c /lpad/rpad

1* select rpad('abc',10,'??') from dual

SQL> run

1* select rpad('abc',10,'??') from dual

RPAD('ABC'

----------

abc???????

trim 去除字符串中两边的空格

SQL> select trim(' a b c ') from dual;

TRIM('ABC')

-----------

a b c

也可以只去除左边或者右边改为ltrim 或者 rtrim

select ltrim(' a b c ') from dual;

LTRIM('ABC')

---------------

a b c

select substr(tel,1,3)||'****'||substr(tel,-4,4);

select substr(tel,1,3)||'****'||substr(tel,-4,4) from tel;

SQL>

SUBSTR(TEL,

-----------

185****1250

137****1253

135****2252

用截取加连接的方式实现后记号码中间为*号，后面正常显示

SQL> select replace(tel,substr(tel,4,4),'****') from tel;

REPLACE(TEL,SUBSTR(TEL,4,4),'****')

--------------------------------------------------------------------------------

185****1250

137****1253

135****2252

这个是通过replace 函数进行

输入函数然后表 截取从第4为后截取4位，替换位*，注意要用引号''设置位字符串，然后输出表

数字函数

round 按着设定位数进位取整

SQL> SQL> select round(123.456,2) from dual;

ROUND(123.456,2)

----------------

123.46

如果定位是负输，比如select round(123.456,-2)，则从小数点有右边截取2位，取证

本例中取整后数字为100

数据类型转换

1数字转字符串

SQL> select to_char(123456789) from dual;

TO_CHAR(1

---------

123456789

SQL> select to_char(123456789,'L099,999,999') from dual;

TO_CHAR(123456789,'L09

----------------------

$123,456,789

也是字符串转数字，后面表示显示方式，L表示本地货币符号也可以用其它符号代替，后面的数字是占位符

以这个语句为例，如果不够位数，前面会显示0

2日期转字符串

SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;

TO_CHAR(SY

----------

2022-11-04

判断语句相当于SHELL中的if .....then 也就是if判断语句的用法，格式如下

select ename,job,sal old_sal,

case when job='CLERK' then sal*2

when job='SALESMAN' then sal/2

when job='MANAGER' then sal

else sal*1.1

end as new_sal

from emp;

注意，查询列对应全大写字母，写语句时也必须全部大写