ORACLE随心笔记

2. Oracle认证考试：OCP
3. 查看oracle基本命令可以用help index命令寻求帮助；
4. 设置回车不会中断用set sqlblanklines on；
5. 采用变量语句：select * from dept where dept=&tt；可以随意输入tt的值；
6. 显示上一条的命令用list命令，简写是l。  list+数字显示缓冲区的第几行；
7. change简写c是更改上一条已经执行的错误语句，例如：c/fron/from;同时对于缓冲区的命令用/执行；
8. delete简写del，可以删除缓冲区的指定行，例如del 3；
9. 保存路径命令save；执行保存的文件用@+路径+文件名；
10. 查看磁盘里面的文件的SQL语句用get命令+路径+文件名；
11. exit退出oracle连接；
12. 显示sql语句的标题用ttitle center+标题名+skip（表示空几行）+“-”；关闭标题用ttitle oof；
13. SQL语句截取命令:ltrim（截取左边的空格），rtrim（截取右边的空格），trim（截取两边的空格）；substr（‘字符串’，第几个位置，取的长度）；
14. count（字段）表示返回该字段的行数，count(*)表示返回该表的记录的行数；
15. 增删改查简写（CRUD）,Create(增加)，查询(Retrieve)(重新得到数据)，更新（Update），删除（Delete）
16. 查询数据库中有多少个名字以'S_'开头的表：

• select count(*) from user_tables where table_name like'S\_%' escape'\';
• 如果要查询的数据中有特殊字符（比如_或%），
• 在做模糊查询时，需要加上\符号进行转义，并且用escape短语指明转义字符\

15.  SQL语言的分类：

数据定义语言DDL：

• create  数据库对象的创建
• alter   数据库对象的修改
• drop    数据库对象的删除
• truncate  清空数据表

数据操纵语言DML:

• insert  插入操作
• update  更新操作
• delete  删除操作

数据查询语言DQL:

• select  查询操作

事务控制语句DML:

• commit  提交数据
• rollback  数据回滚
• savepoint 保存点

16.复制全表：

• create table 表名 as 查询语句；
• 只复制结构，不复制数据

eg：create table emp_xxx as select * from emp_yyy where 1<>1;

• 复制一部分数据（给查询语句加条件）

如果复制表时的查询语句中有表达式或者函数（包括单行函数和组函数），必须指定新表中的列名指定方式：

给列设置列名；或者给新表中设置列名；

eg:create table emp_xxx as select empno,ename,salary*12 year_sal from emp_yyy where deptno=10;--year_sal为新表的列名

• 向新表中插入指定记录数的数据，比如前8条

eg：insert into emp_xxx (select * from emp_yyy where rownum<8) ;--rownum行号

注：复制表的时候不复制约束条件；

17.nvl函数使用：

• nvl(d1,d2)方法：如果d1为null，则用d2代替；
• select ename,salary,bonus,salary+nvl(bonus,0) month_sal from emp_xxx;  (bonus奖金)
• nvl函数的两个参数可以是数字、字符或日期，但两个参数的数据类型必须一致；
• select ename,nvl(hiredate,'10-OCT-11') from emp_xxx;

18.查询语句中列的别名：select ename,salary,salary*12 year_sal from emp_xxx;

19.数字函数：

• round(数字，小数点后的位数)用于数字的四舍五入
• select ename,round(salary*0.123456,2) from emp_xxx;
• trunc（数字，小数点后的位数）用于截取（如果没有第二个参数，默认为0）
• case语句是数据中的分支语句相当于java中的switch-case语句：

select ename,salary,job,case job when 'Analyst' then salary * 1.1

                                 when 'Programmer' then salary * 1.05

                                 when 'clerk' then salary * 1.02

                                 else salary  --else 相当于JAVA中case语句的default

end new_salary from emp_xxx;   --end是case语句的结束标识；

decode()函数与Oracle中case when语句等价：

decode（）语法：decode（判断条件，匹配1，值1，匹配2，值2，...，默认值）

        意思是：如果判断条件 = 匹配1，则返回值1

                    判断条件 = 匹配2，则返回值2

20.to_date和to_char时间处理函数：

• to_date  将字符串数据按指定格式转换为日期数据；
• to_char  将日期数据按指定格式转换为字符串数据；
• insert into emp_xxx(empno,ename,hiredate) values(1012,'TOM',to_date('2014-11-30','yyyy-mm-dd'));
• select ename,to_char(hiredate,'yyyy-mm-dd') from emp_xxx;

21.查询结果排序order by（一般都放在select语句最后面）：

select ename,salary from emp_xxx order by salary asc;(正序排列，asc可以省略)（空值被看着最大值）

select ename,salary from emp_xxx order by salary desc;(降序排列，不可省略)

22.数据字典（只读，不能修改）

• user_tables  用户所有数据表
• user_constraints 用户所有的约束条件
• user_objects  用户所有的对象（表、视图、索引等）
• all_tables   用户能访问的数据表（包括自己的和别的用户允许自己访问的）
• all_constraints  用户能访问的约束条件
• all_objects  用户能访问的对象（表、视图、索引等）
• 数据字典的格式：

user_xxx:用户自己的对象

all_xxx:用户能访问的对象

dba_xxx:数据库所有的对象

select count(*) from user_tables;  --count(*)有多少条记录  count函数是忽略空值的

23.组函数：count/avg/sum/max/min 如果函数写的有列名，默认忽略空值

avg/sum  针对数字的操作

max/min  对所有数据类型都可以操作

24.分组查询：group by 列名：按指定列分组查询

先是group by 分组查询后，再order by 进行排序

eg：select job,max(salary) max_s,

               min(salary) min_s,

               count(*) emp_num

     from emp_xxx

     group by job

     order by emp_num;--select后出现的列，凡是没有被组函数包围的列，必须出现在group by短语中；

25.having子句用于分组后得到的结果数据进行过滤，where 后面的条件语句是对表中的数据进行过滤；

eg：select job,count(*) emp_num

     from emp_xxx

     where job is not null

     group by job

     having count(*)>2

     order by emp_num;  --order by  一定要放在最后面

26.select 字段1，字段2，表达式，函数，...

   from 表名

   where 条件

   group by 列名

   having  带组函数的条件

   order by 列名

27.字符函数：

• upper 转换为大写
• lower 转换为小写
• initcap 转换为首字母大写
• length 取长度
• lpad  左补丁
• rpad  右补丁
• replace 字符替换
• trim  去除前后的空格

eg:将ename字段设置为10个长度，如果不够左边用“*”号补齐

select lpad(ename,10,'*') from emp_xxx;

28.结果集操作：

• 两个结果集必须结构相同，也就是说列的个数，列的顺序，列的数据类型一致时，才能做集合操作；
• 合集  union和union all区别

union去掉重复记录，union all不去重

union排序，union all不排序 （在满足功能的前提下，优选union all）

• 交集   intersect
• 差集   minus（两个集合做减法）

29.主键(PK)和外键(FK)

• 主键（primary key）PK --主键要求不重复，不能是空值
• 外键（foreign key）FK --外键参照主键的数据

30.内（自）连接:join 关键字用于连接两个表

表1  join  表2   on  条件

31.驱动表和匹配表

• 表1 join 表2  on 条件
• 表1叫做驱动表，表2叫做匹配表
• 等值连接方式下，驱动表和匹配表位置可以互换，不影响结果集
• 执行方式：不论谁做驱动表，都会遍历驱动表，在匹配表中查找匹配数据

32.外连接

• 左外连接：表1 left outer join 表2 on 条件
• 右外连接：表1 right outer join 表2 on 条件
• 外连接的特征：
• 如果驱动表在匹配表中找不到匹配记录，则匹配一行空行
• 外连接的结果集=内连接的结果集+驱动表在匹配表中匹配不上的记录和空值
• 外连接的本质是驱动表中的数据一个都不能少
• left outer join 以左边的表为驱动表
• right outer join 以右边的表为驱动表
• 驱动表和匹配表不能互换
• 写外连接的方式：

a)先写内连接

b)使用left或right不重要，关键是确定谁做驱动表

33.full outer join 全外连接

• 全外连接可以把两个表中的记录全部查出来
• 全外连接的结果集=内连接的结果集+驱动表中在匹配表中找不到匹配记录的数据和null值+匹配表中在驱动表中找不到匹配记录的数据和null值
• 驱动表和匹配表可以互换

34.exists 是存在的意思，检查是否存在这一数据，而in是指在哪一字段里面，例如：select * from emp where empno in('','','');

distinct 只显示一次重复出现的值，最好和order by结合使用，可以提高效率；

eg：select distinct 字段名1，字段名2 from table order by 字段名1

35.update(更新数据)：

• update 表名 set 列名 = 新的列值，

                    列名 = 新的列值

                    ......     

    where 条件；

注：更新（update）数据表时，注意条件，如果不加条件，修改的是全部表记录；rollback 回退，commit 确认。

36.delete（删除数据）：

delete [from] 表名 where 条件；

注：

• 如果删除语句中不加where条件，将删除表中的全部记录；
• rollback 回退，commit 确认；
• drop table 会删除表结构和数据；truncate 删除表数据，保留表结构。drop和truncate都不可以回退，delete只删除数据，可以回退。

37.更改表名：rename emp_xxx to emp_yyy;(把emp_xxx改名为emp_yyy)

38.rowid 删除重复数据

• rowid 是oracle数据库的伪列，可以看作是一条数据在数据库中的物理位置；
• rowid 是oracle数据库独有的，每一条记录的rowid在数据库中都是唯一的；

eg：delete from emp_xxx where rowid not in (select max(rowid) from emp_xxx group by empno,ename);

--子查询：查询出empno，ename相同的rowid最大的记录

--主查询：删除rowid不在子查询之列的重复数据

39.alter（修改结构）：

• 增加列（只能增加在最后一列）：alter table emp_xxx add(name char(10));
• 修改列的数据类型为pwd char（8）：alter table emp_xxx modify(pwd char(8));
• 删除列alter table emp_xxx drop column pwd;

注：对加列，减列，修改列的操作谨慎进行，一般不会轻易操作； 

40.rowid

• select ... for update不如select a.*,a.rowid from t,因为前者可能会造成锁表,后者不会锁表;

• 使用rowid快速唯一确定重复行结合max或min函数来实现删除重复行。

SQL>delete from stu a where rowid not in (select max(b.rowid) from stu b where a.no=b.no and a.name = b.name and a.sex = b.sex);                                          //这里max使用min也可以

或者用下面的语句

SQL>delete from stu a where rowid < (select max(b.rowid) from stu b where a.no=b.no and a.name = b.name and a.sex = b.sex);       //这里如果把max换成min的话，前面的where子句中需要把"<"改为">"

跟上面的方法思路基本是一样的，不过使用了group by，减少了显示的比较条件，提高效率。

SQL>delete from stu where rowid not in (select max(rowid) from stu t group by t.no, t.name, t.sex );

41.约束条件：constraint

• 主键（Primary Key，PK）

--主键约束（primary key）= 不能重复+不能为空

--主键约束可以用两种方式定义：列级约束和表级约束

eg：主键表级约束：

create table emp(

     deptno number(2),

     dname varchar2(20),

     location varchar2(40),

     constraint emp_deptno_pk primary key(deptno)

    );

--约束条件命名规则：表名_列名_约束条件的类型

--这样做的好处就是出错时能很快定位到是那张表和具体的列名受约束。

42.非空约束not null，简称NN,这个只能定义在列级

43.唯一约束Unique，简称UK，列级和表级都能约束

eg： create table emp(

          id number(4),

          name varchar2(10) not null,

          email varchar2(30),

          age number(2),

          constraint emp_id_pk primary key(id),

          constraint emp_email_uk unique(email)

);--主键约束和唯一约束都建立在表级

44.检查约束check，简称CK

eg：create table emp(

     id number(4),

     name varchar2(10) not null,

     email varchar2(30),

     age number(2),

     gender char(1), --'F'代表女生；'M'代表男生

     constraint emp_id_pk primary key(id),

     constraint emp_email_uk unique(email),

     constraint emp_gender_ck check(gender in('F','M','f','m')),

     constraint emp_age_ck check(age>10)

)；

45.外键约束（Foreign key,简称FK）:用于定义两个表的两个字段之间的约束，或者一个表的两个字段之间的约束。

• constraint foreign key 表1字段 references  表2字段
• on delete cascade作用：级联删除（主表数据删除，从表相关联的数据统统删除）
• on delete set null作用：一旦主表数据被删除，从表关联数据值为null

eg：alter table emp add constraint emp_id_fk

     foreign key (表1的字段) references emp(id) on delete set null;

46.数据库中的主要对象：

• 表table：是数据库存储的基本单元，是一个二维结构，由行（row）和列（record）组成
• 视图view：视图被称为虚拟的表，里面只包含复杂的sql查询语句，此查询得到的结果被赋予一个名字就是视图的名字，方便以后调用查询，目的就是简化复杂查询。

create or replace view 视图名 as 复杂的查询语句

• 索引index：用来在数据库中加速表查询的数据库对象，通过快速路径的访问方式快速定位数据，可有效减少磁盘I/O操作，提高访问性能。

1)索引的结构：数据+地址（如：张三+Room215）

注：对于数据变更频繁（DML操作频繁）的表，索引会影响查询性能

2)自动创建索引：如果数据表有PK/Unique两种约束，索引自动创建，除此之外，索引必须手动创建。

3)自定义索引语法：create index 索引名 on 表名（列名）;

• 序列sequence：是一种用来生成唯一数字值的数据库对象，序列的值由oracle程序按递增或递减顺序自动生成，通常用来自动生成表的主键值，是一种高效率获得唯一键值的途径。

1)序列的特性：产生连续的不同的数字值用来作为数据表的主键。

2)一个序列为一个表产生主键

3)序列在oracle、db2等数据库中有，在MySQL、sqlserver中没有。

4)创建序列，主键值从1000开始，步进为2：

create sequence wei start with 1000 increment by 2;

5)insert into wei values(myseq_wei.nextval,'hua',null);

6)删除序列（对曾经产生过的数据没有任何影响）：

drop sequence wei;

7)查看序列产生的值：

select myseq_wei.nextval from dual;

• 存储过程procedure：是一种pl/sql存储程序单元，主要用于在数据库中完成特定的操作或者任务，如果在程序中经常需要执行某个操作，可以基于这些操作建立一个过程，用于简化客户端的开发和维护，以及提高执行性能。
• 函数function：pl/sql中的函数被用来执行复杂的计算，并返回计算结果。
• 包package：是一种比较特殊的pl/sql程序，它并不是一个pl/sql存储程序块，而是用于将相关的存储过程和函数组织起来，组成pl/sql存储程序组，它有两部分组成，包头和包体。
• 触发器trigger：pl/sql程序中的触发器的结构类似于函数和过程，与函数和过程不同，触发器是在事件发生时隐式地运行的，相当于java中的事件监听器。
• 同义词synonym：同义词是数据库的别名，目的是简化sql查询，以及隐藏数据库对象的信息。