oracle学习笔记之基本查询

最新推荐文章于 2024-07-25 17:46:17 发布

alingluo

最新推荐文章于 2024-07-25 17:46:17 发布

阅读量324

点赞数

分类专栏：数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/luowangcan/article/details/60579351

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

把学习的Oracle的基本操作记录一下，同时也加深一下记忆

数据库，Oracle 11g，用户Scott

清屏

SQL> host cls 
SQL> --清屏

当前用户

SQL> --当前用户
SQL> show user
USER 为 "SCOTT"

查询当前用户下的表

 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

查看emp表的表结构

SQL> desc emp
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 EMPJOB                                             VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

设置行宽和列宽

 SQL>set linesize 120
 SQL>col ename for a8
 SQL>col sal for 9999

在编写sql语句时尽量少使用 *，用列名替代，提高查询效率
c命令用来修改上一次输入的错误命令，/ 表示执行上一条sql语句。如：

 SQL> select empno , ename , sal , sal*12 年薪
  2  form emp;
form emp
*
第 2 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字


SQL> c /form/from
  2* from emp
SQL> /

     EMPNO ENAME      SAL       年薪
---------- -------- ----- ----------
      7369 SMITH      800       9600
      7499 ALLEN     1600      19200
      7521 WARD      1250      15000
      7566 JONES     2975      35700
      7654 MARTIN    1250      15000
      7698 BLAKE     2850      34200
      7782 CLARK     2450      29400
      7788 SCOTT     3000      36000
      7839 KING      5000      60000
      7844 TURNER    1500      18000
      7876 ADAMS     1100      13200

     EMPNO ENAME      SAL       年薪
---------- -------- ----- ----------
      7900 JAMES      950      11400
      7902 FORD      3000      36000
      7934 MILLER    1300      15600

已选择14行。

所有包含null的sql表达式都为null，null永远都不等于null，需要用is null代替，如：当查询员工的年收入(由月薪*12+奖金组成)时，可以看到没有奖金的员工的年收入查出来为空

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

  SAL*12       COMM SAL*12+COMM
---------- ---------- -----------
      9600
     19200        300       19500
     15000        500       15500
     35700
     15000       1400       16400
     34200
     29400
     36000
     60000
     18000          0       18000
     13200

解决办法,nvl函数，nvl函数会判断奖金（comm）值是否为空，如果为空comm的值则为0，否则为comm的值。如：

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

    SAL*12       COMM SAL*12+NVL(COMM,0)
---------- ---------- ------------------
      9600                          9600
     19200        300              19500
     15000        500              15500
     35700                         35700
     15000       1400              16400
     34200                         34200
     29400                         29400
     36000                         36000
     60000                         60000
     18000          0              18000
     13200                         13200

concat字符串拼接函数

SQL> select concat('Hello ',' World') from dual;

CONCAT('HELL
------------
Hello  World
SQL>--dual伪表

‘||’用法，字符串拼接

 SQL> select ename||'的薪水是'||sal 字符串 from emp;

字符串
----------------------------------------------------------
SMITH的薪水是800
ALLEN的薪水是1600
WARD的薪水是1250
JONES的薪水是2975

修改系统默认日期格式。select * from v$nls_parameters，查询会话参数，可以看到日期对应的格式是 DD-MON-RR，修改其值即可，如

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

会话已更改。

SQL> select sysdate from dual;

SYSDATE
----------
2017-03-06

between … and …

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

in 在集合中查询

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

如果在集合中含有null，不能使用not in，可以使用in

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

未选定行

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

     EMPNO ENAME    EMPJOB           MGR HIREDATE     SAL       COMM     DEPTNO
---------- -------- --------- ---------- ---------- ----- ---------- ----------
      7369 SMITH    CLERK           7902 1980-12-17   800                    20
      7566 JONES    MANAGER         7839 1981-04-02  2975                    20
      7782 CLARK    MANAGER         7839 1981-06-09  2450                    10

like模糊查询

SQL> --查询名字以S开头的员工
SQL> select ename,empjob from emp where ename like 'S%';

ENAME    EMPJOB
-------- ---------
SMITH    CLERK
SCOTT    ANALYST
SQL> --查询名字是四个字的员工
SQL> select ename , empjob from emp where ename like '____';

ENAME    EMPJOB
-------- ---------
WARD     SALESMAN
KING     PRESIDENT
FORD     ANALYST

order by，作用域后面所有的列，先按照第一个列排序，如果相同再按照第二个排序，以此类推。此外，desc作用于靠它最近的列

SQL> select ename , empjob ,deptno , sal from emp order by deptno , sal desc;

ENAME    EMPJOB        DEPTNO   SAL
-------- --------- ---------- -----
KING     PRESIDENT         10  5000
CLARK    MANAGER           10  2450
MILLER   CLERK             10  1300
SCOTT    ANALYST           20  3000
FORD     ANALYST           20  3000
JONES    MANAGER           20  2975
ADAMS    CLERK             20  1100
SMITH    CLERK             20   800
BLAKE    MANAGER           30  2850
ALLEN    SALESMAN          30  1600
TURNER   SALESMAN          30  1500

a命令用于追加命令

SQL> select ename , empjob,deptno from emp order by deptno ;

ENAME    EMPJOB        DEPTNO
-------- --------- ----------
CLARK    MANAGER           10
KING     PRESIDENT         10
MILLER   CLERK             10
JONES    MANAGER           20
FORD     ANALYST           20
ADAMS    CLERK             20
SMITH    CLERK             20
SCOTT    ANALYST           20
WARD     SALESMAN          30
TURNER   SALESMAN          30
ALLEN    SALESMAN          30


已选择14行。

SQL> a desc;
  1* select ename , empjob,deptno from emp order by deptno desc
SQL> /

ENAME    EMPJOB        DEPTNO
-------- --------- ----------
BLAKE    MANAGER           30
TURNER   SALESMAN          30
ALLEN    SALESMAN          30
MARTIN   SALESMAN          30
WARD     SALESMAN          30
JAMES    CLERK             30
SCOTT    ANALYST           20
JONES    MANAGER           20
SMITH    CLERK             20
ADAMS    CLERK             20
FORD     ANALYST           20

函数：函数分为单行函数与分组函数，单行函数只对一行进行变换，每行只返回一个结果。分组函数作用于一组数据，并对一组数据返回一个值。

单行函数操作：
字符函数，lower()转小写，upper()转大写，initcap()首字母大写

 SQL> select lower('Hello') 转小写 , upper('Hello') 转大写 , initcap('hello') 首字母大写 from dual;

转小写  转大写  首字母大写 
----- ----- -----
hello HELLO Hello

取子串函数，substr(a,b)从a中，第b位开始取。substr(a, b，c)从a中第b位开始取，取c位

SQL> select substr('Hello World',3) 子串 from dual;

子串
---------
llo World
SQL> select substr('Hello World',3,3) 子串 from dual;

子
---
llo

length()返回字符长度，lengthb()返回字节长度

SQL> select length('世界你好') 字符 ,
  2  lengthb('世界你好') 字节 from dual;

      字符       字节
---------- ----------
         4          8

instr（a，b）查找函数，在a中查找b，返回位置

SQL> select instr('Hello World','ll') 位置 from dual;

      位置
----------
         3

lpad左填充，rpad右填充

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

左         右
---------- ----------
******abcd abcd******

trim()去掉前后指定的字符

SQL> select trim('H' from 'Hello WorldH') from dual;

TRIM('H'FR
----------
ello World

四舍五入

SQL> select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2) from dual;

ROUND(45.926,2) ROUND(45.926,1) ROUND(45.926,0) ROUND(45.926,-1) ROUND(45.926,-2)
--------------- --------------- --------------- ---------------- ----------------
          45.93            45.9              46               50                0

SQL> select trunc(45.926,2),trunc(45.926,1),trunc(45.926,0),trunc(45.926,-1),trunc(45.926,-2) from dual;

TRUNC(45.926,2) TRUNC(45.926,1) TRUNC(45.926,0) TRUNC(45.926,-1) TRUNC(45.926,-2)
--------------- --------------- --------------- ---------------- ----------------
          45.92            45.9              45               40                0

格式化显示时间

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

TO_CHAR(SYSDATE,'YY
-------------------
2017-03-06 15:05:12

多行函数

sum()函数计算总和

SQL> select sum(sal) from emp;

  SUM(SAL)
----------
     29025

count()计算个数

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

组函数自动滤空，可以嵌套滤空函数来屏蔽滤空功能

多表查询
等值连接

SQL> select e.ename , e.empno ,e.sal , d.dname
  2  from emp e ,dept d
  3  where e.deptno=d.deptno;

ENAME         EMPNO   SAL DNAME
-------- ---------- ----- --------------
CLARK          7782  2450 ACCOUNTING
KING           7839  5000 ACCOUNTING
MILLER         7934  1300 ACCOUNTING
JONES          7566  2975 RESEARCH
FORD           7902  3000 RESEARCH
ADAMS          7876  1100 RESEARCH
SMITH          7369   800 RESEARCH
SCOTT          7788  3000 RESEARCH
WARD           7521  1250 SALES
TURNER         7844  1500 SALES

右外连接

 SQL> select d.deptno , d.dname , count(e.empno) from
  2  emp e , dept d
  3  where e.deptno(+)=d.deptno
  4  group by d.deptno , d.dname;

    DEPTNO DNAME          COUNT(E.EMPNO)
---------- -------------- --------------
        10 ACCOUNTING                  3
        40 OPERATIONS                  0
        20 RESEARCH                    5
        30 SALES                       6

左外连接语法，e.deptno=d.deptno(+)，符号左边的表将比包含。

暂时先记这么多吧。。。。。。。

alingluo

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle学习笔记之基本查询

把学习的Oracle的基本操作记录一下，同时也加深一下记忆数据库，Oracle 11g，用户Scott清屏SQL> host cls SQL> --清屏当前用户SQL> --当前用户SQL> show userUSER 为 "SCOTT"查询当前用户下的表 SQL> select * from tab;TNAME TABTYPE
复制链接

扫一扫

专栏目录