Oracle 学习笔记

一、基础知识

数据库管理系统：dbms
数据库客户端（dos命令行界面）：splplus
客户端界面插件（替代dos操作，实际也是调用dos）：SL/SQL Developer

数据库名=实例名
默认创建一个实例(instance)：orcl

client-----------------instance--------------------db

现在用户同一密码：Rt123456

1、登录用户：
(1)对于自己安装服务器的
sqlplus  scott/Rt123456
sqlplus  scott/Rt123456@orcl;               //ip+instance
(2)对于使用虚拟机
sqlplus  scott/Rt123456@//192.168.137.100/orcl      //服务器ip
sqlplus  scott/Rt123456@orcl100                               //自己配置文件，实例名起为orcl100

2、登录管理员账户 sysdba，因为安装时默认将sys用户与本地操作系统绑定，所以不需要密码
cmd:  sqlplus / as sysdba

3、修改HR、sysdba、system、scott账户密码
alter user scott account unlook;
alter user scott identified by 11;

表由描述信息、数据、关系组成
行：记录
列：字段

4、用户方案，每个用户对应一些方案
SQL> sqlplus  scott/Rt123456

//查有哪些表
SQL> select * from tab;

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

//查dept表的描述信息
SQL> desc dept
 名称                                      是否为空?           类型
 ----------------------------------------- -------- ----------------------------
 DEPTNO                              NOT NULL   NUMBER(2)
 DNAME                                                   VARCHAR2(14)
 LOC                                                         VARCHAR2(13)

//查看dept表结构
SQL> select * from dept;        //查询全部的列

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

//设置宽度
SQL> set linesize 120;                //设置行宽
SQL> /
SQL> set pagesize 100;             //设置页宽
SQL> /
SQL> col deptno for 9999;          //设置deptno列宽度，一个9代表一位数字的宽度
SQL> /
SQL> col dname for a12              //设置dname 列宽度，a代表的设置是字符的宽度，12代表宽度值
SQL> 

//清屏
SQL> host cls;

双引号表示别名；单引号表示字符串。
//描述对大小写不敏感，可用双引号，也可省略双引号。
//数据对大小写敏感，需要完全匹配，且需要用单引号。

查数据用单引号
查描述用双引号，别名

二、select...

select...                     //哪些列：全部列，部分列，表达式，给描述起别名(双引号)，去除重复
from...                      //哪个表
where condition       //什么条件：单条件，多条件(右侧优先)

1、写指定列比写*效率高

select * from emp;                                                     //全部列
select empno, ename from emp;                               //指定列
select empno as 编号,ename as "姓 名" from emp;    //给列描述 起别名(双引号)
select ename sal*comm from emp;                           //表达式,第一列是ename,列描述为ename,可以起别名;
                                                                                  //           第二列是sal*comm,列描述为sal*emp,可以起别名;
2、nvl(a,b)          //空值转换函数
null与表达式运算，结果为null，有null的运算全部没有结果，列中该行为空
select ename,sal*nvl(comm,0)  from emp;                //nvl(a,b) 用来a列中的null值转换为指定值b

3、去重复
select distinct deptno from emp;       //加distinct，去除重复的deptno，只打印不同的deptno
select distinct deptno,job from emp;      //一行所有描述都不同，才算是不同；有一个不同，就是不同

4、edit/ed指令，sql错误修改方法

5、伪表和伪列，数学计算
dual 伪表：本来不存在的表。一般的临时数学计算可以用伪表。
伪列：本来就存在的列，不依赖于某一个表
sysdata 系统提供的当前时间

SQL> select 3+4*50,sysdate from dual;

    3+4*50 SYSDATE
---------- --------------
       203 10-5月 -19

6、修改日期格式
select * from v$nls_parameter;            //查看表参数的设置
alter session set NLS_DATE_FORMAT FOR = 'yyyy-mm-dd';   //
//如果有人修改了日期格式，将带来错误，待解决

7、运算符
条件符号：= 、 != 、<> 、 
select * from emp where hiretate = 'KING';
select * from emp where empno != 7369;
select * from emp where hiredate='17-12月-80';     //日期中 - /  . \  , 等可以混用作为日期分隔符

逻辑运算符：and 、or 、>= 、 <= 、 between...and... 、  //b...t...是闭区间的，日期的b...t...是智能的

8、null
null ;    //不能通过  =  或者  != 去判断，这样判断永远为假

//多个条件右侧的先执行，因此，为了减少判断左侧
//and的时候，容易假的放在右侧
//or的时候，容易真的放在右侧

select * from emp where comm is not null;           //is not null
select * from emp where comm is null;                  //is null

9、集合查询：在集合中，不在集合中      in (10,20)、not in (30)
SQL> select * from emp where deptno in (10,20);              //选择    in (10,20)
SQL> select * from emp where deptno not in (30);            //选择   not in (30)

在 not in (30,null)    //是错误的，not in 的集合中不能有 null

10、模糊查询，如 like 'S%'
"%" 匹配多个字符，
"_" 匹配一个字符，

where ename like '%S%'          //代表ename中所有含有 S 的行，S可以是开头，可以是结尾
where ename like '%S'            //以 S 结尾
where ename like 'S%'            //以 S 开头

where ename like '%'             //至少含有一个字符，非null
where ename like '_%'
where ename like '%_'

where ename like '_'              //只含有一个字符
where ename like '__'            //只含有两个字符

11、转义字符
select * from emp where ename like '%\_%' escape '\';         // '\'是转义
先插入一行，insert into emp (empno, ename, job, sal, deptno) values (1001, 'Tom_abc', 'SALESMAN',2000,10);

12、null
表达式中nvl()转换
不能用等不等号
集合中不能用

二、排序
select...

from...

order by...desc/asc

select * from emp order by ename,sal;          //人名按字母顺序，薪水按数字升序，人名优先于薪水

select * from emp order by sal desc;                           //逆序
select * from emp order by sal desc, ename desc;      //逆序，desc只作用于

select * from emp order by sal asc;          //正序
select * from emp order by 1, 8;
select ename,sal from emp order by 1;   //按 sal 排序，是按所选择的列中的第n个排序，从查询结果集中选择

null代表无穷大，正序是排在最后，逆序时排在最前面。
一般应该将逆序时的null排在后面，最后加 nulls last，也可以用 nvl()函数
select * from emp order by comm desct;                   //null排在最前，然后再从大到小
select * from emp order by comm desc nulls last;     //从大到小，null排在最后
select * from emp order by nvl(comm,-1) desc;           //从大到小，null排在最后 
select * from emp order by nvl(comm,0) desc;            //从大到小，原来的0和原来的null混在一起排在最后