oracle学习笔记之基本查询

2024-08-29 13:55:01

字体：大中小

来源：转载

供稿：网友

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

数据库，Oracle 11g，用户Scott

清屏SQL> host cls SQL> --清屏当前用户SQL> --当前用户SQL> show userUSER 为 "SCOTT"查询当前用户下的表 SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------BONUS TABLEDEPT TABLEEMP TABLESALGRADE 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 empSQL> / 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 13200concat字符串拼接函数SQL> select concat('Hello ',' World') from dual;CONCAT('HELL------------Hello WorldSQL>--dual伪表‘||’用法，字符串拼接 SQL> select ename||'的薪水是'||sal 字符串 from emp;字符串----------------------------------------------------------SMITH的薪水是800ALLEN的薪水是1600WARD的薪水是1250JONES的薪水是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-06between … and …SQL> select * from emp where sal between 1000 and 2000;in 在集合中查询SQL> select * from emp where deptno in (10,20);如果在集合中含有null，不能使用not in，可以使用inSQL> 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 CLERKSCOTT ANALYSTSQL> --查询名字是四个字的员工SQL> select ename , empjob from emp where ename like '____';ENAME EMPJOB-------- ---------WARD SALESMANKING PRESIDENTFORD ANALYSTorder by，作用域后面所有的列，先按照第一个列排序，如果相同再按照第二个排序，以此类推。此外，desc作用于靠它最近的列SQL> select ename , empjob ,deptno , sal from emp order by deptno , sal desc;ENAME EMPJOB DEPTNO SAL-------- --------- ---------- -----KING PRESIDENT 10 5000CLARK MANAGER 10 2450MILLER CLERK 10 1300SCOTT ANALYST 20 3000FORD ANALYST 20 3000JONES MANAGER 20 2975ADAMS CLERK 20 1100SMITH CLERK 20 800BLAKE MANAGER 30 2850ALLEN SALESMAN 30 1600TURNER SALESMAN 30 1500

a命令用于追加命令

SQL> select ename , empjob,deptno from emp order by deptno ;ENAME EMPJOB DEPTNO-------- --------- ----------CLARK MANAGER 10KING PRESIDENT 10MILLER CLERK 10JONES MANAGER 20FORD ANALYST 20ADAMS CLERK 20SMITH CLERK 20SCOTT ANALYST 20WARD SALESMAN 30TURNER SALESMAN 30ALLEN SALESMAN 30已选择14行。SQL> a desc; 1* select ename , empjob,deptno from emp order by deptno descSQL> /ENAME EMPJOB DEPTNO-------- --------- ----------BLAKE MANAGER 30TURNER SALESMAN 30ALLEN SALESMAN 30MARTIN SALESMAN 30WARD SALESMAN 30JAMES CLERK 30SCOTT ANALYST 20JONES MANAGER 20SMITH CLERK 20ADAMS CLERK 20FORD 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 WorldSQL> select substr('Hello World',3,3) 子串 from dual;子---llolength()返回字符长度，lengthb()返回字节长度SQL> select length('世界你好') 字符 , 2 lengthb('世界你好') 字节 from dual; 字符字节---------- ---------- 4 8instr（a，b）查找函数，在a中查找b，返回位置SQL> select instr('Hello World','ll') 位置 from dual; 位置---------- 3lpad左填充，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 0SQL> 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)---------- 29025count()计算个数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 ACCOUNTINGKING 7839 5000 ACCOUNTINGMILLER 7934 1300 ACCOUNTINGJONES 7566 2975 RESEARCHFORD 7902 3000 RESEARCHADAMS 7876 1100 RESEARCHSMITH 7369 800 RESEARCHSCOTT 7788 3000 RESEARCHWARD 7521 1250 SALESTURNER 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(+)，符号左边的表将比包含。

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

上一篇：oracle 基础操作

下一篇：Linux 64位下Oracle 12C图文安装