Oracle_1：入门

程序员文章站 2022-07-02 11:37:45

...

登录Oracle

C:\Users\往常>sqlplus scott/tiger

--查看系统中有哪些表
SQL> select * from tab;

SQL> set linesize 300		--设置每行显示字符数
SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-12月-80            800                    20
      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20
      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                    30
      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                    20
      7839 KING                 PRESIDENT                     17-11月-81           5000                    10
      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30
      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                    20

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7900 JAMES                CLERK                    7698 03-12月-81            950                    30
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20
      7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10

已选择14行。


SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON

起别名

SQL> select empno as “编号”,ename “姓名”,job 工作 from emp;

三者区别：前两者没区别。如果在起别名时，遇到一些特殊符号：空格，关键字。则需要用引号引起来。

SQL> select empno as “编号”,ename “姓名”,job 工作 from emp; 此时会报错 job别名识别为工

SQL> select empno as "编号",ename "姓名",job 工作 from emp;

      编号 姓名                 工作
---------- -------------------- ------------------
      7369 SMITH                CLERK
      7499 ALLEN                SALESMAN
      7521 WARD                 SALESMAN
      7566 JONES                MANAGER
      7654 MARTIN               SALESMAN
      7698 BLAKE                MANAGER
      7782 CLARK                MANAGER
      7788 SCOTT                ANALYST
      7839 KING                 PRESIDENT
      7844 TURNER               SALESMAN
      7876 ADAMS                CLERK

      编号 姓名                 工作
---------- -------------------- ------------------
      7900 JAMES                CLERK
      7902 FORD                 ANALYST
      7934 MILLER               CLERK

已选择14行。

清屏：

host cls;

一、基本概念：

实体：java中的类

记录：java的对象一行数据

字段：java的属性一列

表：同一个实体中，所有的记录、字段组合起来就是一张表

查看字段：输出有哪些列和列的数据类型

SQL> desc emp;

NUMBER(7,2) 七位，两位小数

字符串：VARCHAR2

时间：DATE

二进制（图片，视频）：BLOB

大文本：CLOB

oracle默认时间格式：DD-MON-RR。修改：

SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd';

SQL> alter session set NLS_DATE_FORMAT = 'DD-MON-RR';

二、范围查询：

betweem 小 and 大 [小，大] 闭区间

SQL> select * from emp where sal between 2000 and 5000 ;

between and一般是数字，也可以是日期

SQL> select * from emp where hiredate between '1981-12-03' and '1987-05-23';

(not) in 范围内不能出现null

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

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

SQL> select * from emp where empno not in (select sal from emp);

三、模糊查询：

like

一般配合通配符使用：_ 一个字符 % 多个字符

数字，日期都可以

SQL> select * from emp where ename like '%W%';

SQL> select * from emp where empno like '7%';

SQL> select * from emp where hiredate like '03%';

SQL> select * from emp where empno like '%4';

--姓名中第二个字母是U的：
SQL> select * from emp where ename like '_U%';
--姓名中包含U的：
SQL> select * from emp where ename like '%U%';
--姓名长度>6的员工信息：
SQL> select * from emp where ename like '_______%';		--七个下划线

姓名中包含下划线的：需要转译符了。

oracle中可以自定义转译符：

SQL> select * from emp where ename like '%\_%' escape'\';
--此处sacape定义了\就是转译符，遇到\后一位就是本意

四、排序

order by 字段名|表达式|序号

升序asc 降序desc 默认升序

排序时NULL是最大值

SQL> select * from emp order by sal;		--默认asc

SQL> select * from emp order by sal desc;
--序号
SQL> select empno,ename,sal from emp order by 3;  --1是empno 2是ename 3是sal
--表达式 没啥意义
SQL> select * from emp order by sal+100 desc;  --通过sal+100来排序 null最大值

--把null放在最后
SQL> select empno,mgr from emp order by mgr desc nulls last;

多列排序

--首先拿工资排序，降序。工资一样拿入职时间排序，升序。
SQL> select * from emp order by sal desc , hiredate asc;

五、自带函数

5.1 单行函数

一个输入值，给出一个输出值。一行一个输出值

--把job列的信息全部小写，并输出。14行14个输出	lower()函数
SQL> select lower(job) from emp;

5.1.1 字符函数：

lower
upper
initcap(首字符大写)

Oracle中的dual表是一个单行单列的虚拟表，学习的时候用一下就行

SQL> select 'hello WorLd',lower('hello WorLd'),upper('hello WorLd'),initcap('hello WorLd') from dual;

'HELLOWORLD'           LOWER('HELLOWORLD')    UPPER('HELLOWORLD')    INITCAP('HELLOWORLD')
---------------------- ---------------------- ---------------------- ----------------------
hello WorLd            hello world            HELLO WORLD            Hello World

substr 字符串截取 substr(str,begin,len) 从1开始数

SQL> select substr('hello world',3,3) from dual;

SUBSTR
------
llo

length() 字符数 / lengthb() 字节数

utf-8：一个汉字或者符号三个字节。gbk：两个

SQL> select length('helloworld'),lengthb('helloworld') from dual;

LENGTH('HELLOWORLD') LENGTHB('HELLOWORLD')
-------------------- ---------------------
                  10                    10

SQL> select length('你好'),lengthb('你好') from dual;

LENGTH('你好') LENGTHB('你好')
-------------- ---------------
             2               6
--查看当前编码格式：
SQL> select * from nls_database_parameters;

insert(str,substr) 在str中找substr的位置

SQL> select instr('helloworld','el') from dual;

INSTR('HELLOWORLD','EL')
------------------------
                       2

lpad 左填充 / rpad 右填充

SQL> select lpad('hello',10,'~') 左,rpad('hello',10,'*') 右  from dual;

左                   右
-------------------- --------------------
~~~~~hello           hello*****

trim() 去掉任意字符

SQL> select trim('    hello    ') from dual;	--默认去空格

TRIM('HELL
----------
hello

SQL> select trim('X' from 'XXXXXhe  lloXXXX') from dual;  --去掉X

TRIM('X'FROM'X
--------------
he  llo

replace() 替换

SQL> select replace('hello','l','*') from dual;		--将l替换成*

REPLACE('H
----------
he**o

5.1.2 数值函数

round() 四舍五入保留n位小数，-1就是判断个位数四舍五入到十位数

SQL> select round(3.1415926,2) 一，round(3.1415926,1) 二,round(3.1415926,0) 三,round(3.1415926,-1) 四,round(3.1415926,-2) 五 from dual;

        一         二         三         四         五
---------- ---------- ---------- ---------- ----------
      3.14        3.1          3          0          0

trunc 截取，舍尾

SQL> select trunc(3.1415926,2) 一，trunc(3.1415926,1) 二,trunc(3.1415926,0) 三,trunc(3.1415926,-1) 四,trunc(3.1415926,-2) 五 from dual;

        一         二         三         四         五
---------- ---------- ---------- ---------- ----------
      3.14        3.1          3          0          0

mod() 求余

SQL> select mod(9,2) from dual;

  MOD(9,2)
----------
         1

5.1.3 日期函数

当前时间：sysdate

SQL> select sysdate from dual;

SYSDATE
--------------
05-4月 -20

格式化 to_char()

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

TO_CHAR(SYSDATE,'YYY
--------------------
2020-04-05

日期±数字默认是加减一天

SQL> select sysdate+1 from dual;

SYSDATE+1
--------------
06-4月 -20

日期-日期 只能减

SQL> select ename,(sysdate - hiredate) from emp;

months_between(日期1,日期2) 日期1 - 日期2的月数

SQL> select ename,months_between(sysdate,hiredate) from emp;

add_months(日期,月数) 日期加几个月

SQL> select add_months(sysdate,1) from dual;

ADD_MONTHS(SYS
--------------
05-5月 -20

last_day() 当月最大的日期

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA
--------------
30-4月 -20

next_day() 下一个星期几是多少号

SQL> select next_day(sysdate,'星期五') from dual; --当前时间下一个星期五是多少号
												-- '星期五'的写法是固定的
NEXT_DAY(SYSDA
--------------
10-4月 -20

round() 对时间四舍五入

SQL> select round(sysdate,'year') 一, round(sysdate,'month') 二,round(sysdate,'day') 三 from dual;

一             二             三
-------------- -------------- --------------
01-1月 -20     01-4月 -20     05-4月 -20
-- 对于年来说，现在是四月，没有过半，所以一月一号
-- 对于月来说，今天四月五号。没有过半，所以四月一号

trunc() 舍尾

SQL> select trunc(sysdate,'year') 一, trunc(sysdate,'month') 二,trunc(sysdate,'day') 三 from dual;

一             二             三
-------------- -------------- --------------
01-1月 -20     01-4月 -20     05-4月 -20

5.1.4 通用函数

nvl/nvl2

nvl(a,0)如果a为空，则设置a为0

SQL> select ename,nvl(comm,0) from emp;

ENAME                NVL(COMM,0)
-------------------- -----------
SMITH                          0
ALLEN                        300
WARD                         500
JONES                          0
MARTIN                      1400
BLAKE                          0
CLARK                          0
SCOTT                          0
KING                           0
TURNER                         0
ADAMS                          0

ENAME                NVL(COMM,0)
-------------------- -----------
JAMES                          0
FORD                           0
MILLER                         0


SQL> select ename,nvl(comm,0) from emp;

ENAME                NVL(COMM,0)
-------------------- -----------
SMITH                          0
ALLEN                        300
WARD                         500
JONES                          0
MARTIN                      1400
BLAKE                          0
CLARK                          0
SCOTT                          0
KING                           0
TURNER                         0
ADAMS                          0

ENAME                NVL(COMM,0)
-------------------- -----------
JAMES                          0
FORD                           0
MILLER                         0

已选择14行。

nvl2(a,x,0) 如果a为null则设a为0，如果a不为null则设a为x

SQL> select ename,nvl2(comm,comm,0) from emp;

ENAME                NVL2(COMM,COMM,0)
-------------------- -----------------
SMITH                                0
ALLEN                              300
WARD                               500
JONES                                0
MARTIN                            1400
BLAKE                                0
CLARK                                0
SCOTT                                0
KING                                 0
TURNER                               0
ADAMS                                0

ENAME                NVL2(COMM,COMM,0)
-------------------- -----------------
JAMES                                0
FORD                                 0
MILLER                               0

已选择14行。

nullif(a,b) 如果ab一样返回none，如果不一样返回a

SQL> select nullif('abc','abc') from dual;

NULLIF
------


SQL> select nullif('abc','a') from dual;

NULLIF
------
abc

coalesce 从左往右找，找第一个不为空的

SQL> select comm,sal,coalesce(comm,sal) from emp;

      COMM        SAL COALESCE(COMM,SAL)
---------- ---------- ------------------
                  800                800
       300       1600                300
       500       1250                500
                 2975               2975
      1400       1250               1400
                 2850               2850
                 2450               2450
                 3000               3000
                 5000               5000
         0       1500                  0
                 1100               1100

      COMM        SAL COALESCE(COMM,SAL)
---------- ---------- ------------------
                  950                950
                 3000               3000
                 1300               1300

5.1.4.1decode()

decode(字段，条件1，返回值1，条件2，返回值2，…)

SQL> select ename,job,sal 涨前，decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+500,sal+300) 涨后 from emp;

ENAME                JOB                      涨前       涨后
-------------------- ------------------ ---------- ----------
SMITH                CLERK                     800       1100
ALLEN                SALESMAN                 1600       1900
WARD                 SALESMAN                 1250       1550
JONES                MANAGER                  2975       3475
MARTIN               SALESMAN                 1250       1550
BLAKE                MANAGER                  2850       3350
CLARK                MANAGER                  2450       2950
SCOTT                ANALYST                  3000       3300
KING                 PRESIDENT                5000       6000
TURNER               SALESMAN                 1500       1800
ADAMS                CLERK                    1100       1400

ENAME                JOB                      涨前       涨后
-------------------- ------------------ ---------- ----------
JAMES                CLERK                     950       1250
FORD                 ANALYST                  3000       3300
MILLER               CLERK                    1300       1600

已选择14行。

5.1.4.2 case

select ename,job,sal 涨前，case job

when ‘PRESIDENT’ then sal+1000

when ‘MANAGER’ then sal+500

else sal+300 end 涨后

from emp;

SQL> select ename,job,sal 涨前，case job
  2  when 'PRESIDENT' then  sal+1000
  3  when 'MANAGER'  then sal+500
  4  else sal+300  end 涨后
  5  from emp;

ENAME                JOB                      涨前       涨后
-------------------- ------------------ ---------- ----------
SMITH                CLERK                     800       1100
ALLEN                SALESMAN                 1600       1900
WARD                 SALESMAN                 1250       1550
JONES                MANAGER                  2975       3475
MARTIN               SALESMAN                 1250       1550
BLAKE                MANAGER                  2850       3350
CLARK                MANAGER                  2450       2950
SCOTT                ANALYST                  3000       3300
KING                 PRESIDENT                5000       6000
TURNER               SALESMAN                 1500       1800
ADAMS                CLERK                    1100       1400

ENAME                JOB                      涨前       涨后
-------------------- ------------------ ---------- ----------
JAMES                CLERK                     950       1250
FORD                 ANALYST                  3000       3300
MILLER               CLERK                    1300       1600

已选择14行。

5.1.5 转换函数

5.1.5.1 隐式转换（自动转换）

字符 —— 数字/日期

SQL> select * from emp where empno='7788'; #自动转换为7788

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                    20

SQL> select * from emp where empno=7788;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                    20
      

SQL> select * from emp where hiredate= '19-4月-87';

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                    20

SQL> select '123'+123 from dual;

 '123'+123
----------
       246

5.1.5.2 显式转换

to_char（数字/字符，格式）
to_number(字符，格式)
to_date（字符，格式）

Oracle_1：入门

SQL> select to_number('￥123,456.7', 'L999,999.9') from dual;

TO_NUMBER('￥123,456.7','L999,999.9')
-------------------------------------
                             123456.7
  

SQL> select to_char(123456.7, 'L999,999.9') from dual;

TO_CHAR(123456.7,'L999,999.9')
----------------------------------------
        ￥123,456.7


SQL> select to_date('2020-4-15', 'yyyy-mm-dd') from dual;

TO_DATE('2020-
--------------
15-4月 -20

5.2 多行函数、组函数、聚合函数

多个输入值，给出一个输出值。多行一个输出值

5.2.1 count() 统计个数自动排除空值

--输出job的行数。14行输出一个值 count()函数
SQL> select count(job) from emp;

COUNT(JOB)
----------
        14

统计有多少个部门编号（distinct）

SQL> select count(distinct deptno) from emp;

COUNT(DISTINCTDEPTNO)
---------------------
                    3

5.2.2 sum() min() max() avg()

SQL> select count(*) 员工总数，sum(sal) 总工资，max(sal) 最大工资，min(sal) 最小工资，avg(sal) 平均工资 from emp;

  员工总数     总工资   最大工资   最小工资   平均工资
---------- ---------- ---------- ---------- ----------
        14      29025       5000        800 2073.21429

5.3 分组

5.3.1gourp by

分组查询时，不在组函数（多行函数）中的列，必须在group by中。

下面的例子中，avg(sal)是组合函数。deptno是不在组合函数的列，所以它必须在gourp by中

-- 按照部门编号分组，各个部门的平均工资
SQL> select deptno,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667

5.3.2 多次分组

各个部门中各个工作的平均工资

先根据部门分组，再根据工作分组

SQL> select deptno,job,avg(sal) from emp group by deptno,job;

    DEPTNO JOB                  AVG(SAL)
---------- ------------------ ----------
        20 CLERK                     950
        30 SALESMAN                 1400
        20 MANAGER                  2975
        30 CLERK                     950
        10 PRESIDENT                5000
        30 MANAGER                  2850
        10 CLERK                    1300
        10 MANAGER                  2450
        20 ANALYST                  3000

5.3.3 对组进行筛选用having

对行进行筛选用where 对组进行筛选用having

having中可以使用多行函数，where不行（这是语法要求）

SQL> select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>3000 ;

    DEPTNO JOB                  AVG(SAL)
---------- ------------------ ----------
        10 PRESIDENT                5000

六、查询语句的注意事项

修改上一句sql语句

SQL> select * form emp;
select * form emp
         *
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字


SQL> c /form/from     -- 将上一句中的form改为from
  1* select * from emp --预览
SQL> /				  --执行上一句


SQL> ed               --执行修改
已写入 file afiedt.buf

  1* select * from emp

列的计算

SQL> select ename,sal,sal*10 from emp;

ENAME                       SAL     SAL*10
-------------------- ---------- ----------
SMITH                       800       8000
ALLEN                      1600      16000
WARD                       1250      12500
JONES                      2975      29750
MARTIN                     1250      12500
BLAKE                      2850      28500
CLARK                      2450      24500
SCOTT                      3000      30000
KING                       5000      50000
TURNER                     1500      15000
ADAMS                      1100      11000

ENAME                       SAL     SAL*10
-------------------- ---------- ----------
JAMES                       950       9500
FORD                       3000      30000
MILLER                     1300      13000

已选择14行。

关系型数据库中，select控制列，where控制行。
字符\字符串\日期都是 ’ ’
大小写问题：
1. 命令/关键字：不敏感（不区分）
2. 数据：敏感（区分）
运算符：
1. 操作运算符：+ - * / %
2. 关系云算法：< > <= <= = !=或者<>(这俩都是不等于)
  - 如果是null，必须用 is null 或者is not null
3. 逻辑运算符：or and not

where语句后的执行顺序，从右往左

select * from emp where mgr=7788 and job='CLERK';
--先执行job='CLERK'后执行mgr=7788

null的计算：任何数字和null计算结果为null

需要对null进行处理

对查询的结果集去重。distinct

SQL> select distinct deptno from emp;

    DEPTNO
----------
        30
        20
        10

连接符/拼接

concat()

SQL> select concat('hello','world') from dual;

CONCAT('HELLO','WORL
--------------------
helloworld

SQL> select 'hello'||'world' from dual;

'HELLO'||'WORLD'
--------------------
helloworld

修改oracle默认日期格式

--查看：
SQL> select * from v$nls_parameters;
NLS_DATE_FORMAT
         DD-MON-RR
--修改
SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd';

会话已更改。

and not

where语句后的执行顺序，从右往左

select * from emp where mgr=7788 and job='CLERK';
--先执行job='CLERK'后执行mgr=7788

null的计算：任何数字和null计算结果为null

需要对null进行处理

对查询的结果集去重。distinct

SQL> select distinct deptno from emp;

    DEPTNO
----------
        30
        20
        10

连接符/拼接

concat()

SQL> select concat('hello','world') from dual;

CONCAT('HELLO','WORL
--------------------
helloworld

SQL> select 'hello'||'world' from dual;

'HELLO'||'WORLD'
--------------------
helloworld

修改oracle默认日期格式

--查看：
SQL> select * from v$nls_parameters;
NLS_DATE_FORMAT
         DD-MON-RR
--修改
SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd';

会话已更改。

相关标签： Oracle的学习数据库 oracle

上一篇： D3D9学习笔记之基础几何体的深入应用（一）

下一篇： Oracle事务和常用数据库对象

Oracle_1：入门

登录Oracle

起别名

一、基本概念：

二、范围查询：

三、模糊查询：

like

姓名中包含下划线的：需要转译符了。

四、排序

order by 字段名|表达式|序号

多列排序

五、自带函数

5.1 单行函数

5.1.1 字符函数：

5.1.2 数值函数

5.1.3 日期函数

5.1.4 通用函数

5.1.4.1decode()

5.1.4.2 case

5.1.5 转换函数

5.1.5.1 隐式转换（自动转换）

5.1.5.2 显式转换

5.2 多行函数、组函数、聚合函数

5.2.1 count() 统计个数自动排除空值

5.2.2 sum() min() max() avg()

5.3 分组

5.3.1gourp by

5.3.2 多次分组

5.3.3 对组进行筛选用having

六、查询语句的注意事项

软件编程入门自学教程（零基础编程软件）

spring培训哪个好（spring入门经典）

SQLServer XML查询快速入门(18句话)

html文件用什么打开文件（零基础入门HTML）

android开发基础知识点（有关android入门知识解析）

html开发工具有哪些（html零基础入门教程）

linux常用工具和命令（linux基础入门知识）

java计算机编程入门教程（免费的编程自学网站）

javaweb之web入门基础

快速入门ASP.NET Core看这篇就够了

Oracle_1：入门

登录Oracle

起别名

一、基本概念：

二、范围查询：

三、模糊查询：

like

姓名中包含下划线的：需要转译符了。

四、排序

order by 字段名|表达式|序号

多列排序

五、自带函数

5.1 单行函数

5.1.1 字符函数：

5.1.2 数值函数

5.1.3 日期函数

5.1.4 通用函数

5.1.4.1decode()

5.1.4.2 case

5.1.5 转换函数

5.1.5.1 隐式转换（自动转换）

5.1.5.2 显式转换

5.2 多行函数、组函数、聚合函数

5.2.1 count() 统计个数 自动排除空值

5.2.2 sum() min() max() avg()

5.3 分组

5.3.1gourp by

5.3.2 多次分组

5.3.3 对组进行筛选用having

六、查询语句的注意事项

软件编程入门自学教程（零基础编程软件）

spring培训哪个好（spring入门经典）

SQLServer XML查询快速入门(18句话)

html文件用什么打开文件（零基础入门HTML）

android开发基础知识点（有关android入门知识解析）

html开发工具有哪些（html零基础入门教程）

linux常用工具和命令（linux基础入门知识）

java计算机编程入门教程（免费的编程自学网站）

javaweb之web入门基础

快速入门ASP.NET Core看这篇就够了

5.2.1 count() 统计个数自动排除空值