Oracle常见语句操作

1、  进入SQL语句输入环境，命令：sqlplus/nolog

2、  连接Oracle中用户，命令：conn 用户名/密码@net环境名

3、  创建表空间，命令：Create  tablespace 表空间名 datafile  ‘安装Oracle时oradata下的dbf路径’ size50M  autoextend on next 10M;

4、  增加表空间内容，命令：

Alter  tablespace 表空间名 Adddatafile ‘文件路径’;

5、  删除表空间内容，命令：

6、  Alter  tablespace 表空间名 dropdatafile ‘文件路径’;

7、  删除表空间，命令：

Drop  tablespace 表空间名：

8、  在表空间下创建用户，命令：

Create  user 用户名Identifiedby 密码 default tablespace 表空间名

9、  给用户授予权限，命令：

Grant  connect(权限名) tousername(用户名);

10、             删除用户，命令：

Drop  user  username(用户名)  cascade;

 

数据的备份：

1、  exp 用户名/用户密码@net环境名 file=路径名+文件名.dmp full=y log=log文件名;

 

2、  exp ultranms/ultranms@nmsdb owner=ultranms file=../ultrnams.dmplog=../exp.log

3、按表导出

exp ultranms/ultranms@nmsdb tables=(table1,table2)file=../table.dmp

log=../exp.log

 

恢复：实验过程中为了防止导入文件导致数据库发生变化，所以新创一个用户进行操作。

Imp 用户名/用户密码@net环境名 file=文件路径名 ingore=y  full=y

 

impultranms/ultranms fromuser=ultranms touser=ultranms ignore=yfile=../ultranms.dmp  log=../imp.log

• 按表导入

impultranms/ultranms@nmsdb fromuser=ultranms touser=ultranmsfile=pm_raw_p_reshost_1day.dmp tables=table1 ignore=y  log=../exp.log

 

数据泵

1、  创建directory路径，

Create  directory 名字 as  ‘路径名’

2、  授权directory的读写权限给所创建的用户

Grant read,write  on  directory 名字 to 用户名；

3、  导出用户下的数据：

Expdp 用户名/密码 directory=名字 dumpfile=名字.dmp logfile=名字.log schemas=用户名

 

 

 

1、  别名的创建：

(1)    Select name “姓名”, no “学号” fromclass;

(2)    Select name as “姓名”,no as “学号” fromclass;

二者的作用是相同的，都实现创建别名。

2、  连接两个列的值

命令：select name||no “合并” from class;

 

3、  连接其他字符

Select name||’is a id’||no as “合并” fromclass;

4、  输出单引号’’，格式命令

Select name||q’[‘is a id’]’||no from class;

 

5、  去除重复列，命令DISTINCT

Select distinct name from class;

 

6、  查询表的结构

Describe class;

 

Where语句的判断：

 

7、  order by中可以用列的序号代替列：

 

 

8、  查询过程中不需要立即赋值的，可以通过&声明一个变量来进行手动输入进行执行，命令：

(1)Select name,no from class where credit=&clscredit;

(2)当变量的值为字符串时，要注意加上分号；

Select no ,credit from class where name=’&clsname’;

Select no ,credit from class where name=’&clsname’;注意输入的值区分大小写

 

变量运用在输入行时：

 

SQL语句中的函数：

1、  Lower

Select Lower(name) from class;

 

2、  Upper

3、  concat的用法：

 

4、  substr截取字符串的：

5、  length的用法：

数据表：

 

结果图：

显然结果是字段的大小，并不是值得长度；原因是由于定义是采用了varchar类型，如果用varchar2类型即可正常显示。

 

6、  instr是用于判断某个字母在字段中出现的位置，如果没有则为0；

 

 

 

Last_date(date)：回滚到本月末最后一天

next_Date(date,7)：回滚到周六

Round(date,’Date’)：使日期回滚到距离最近的星期天

Round(date,’Month’)：回滚到月初，超过15号进位到下月，或者回滚到本月初 Round(date,’Year’)：大于6月进位到下一年年初，否则回滚到本年年初

Trunc(date,’Year’)：回滚到本年初

Trunc(date,’Month’):回滚到本月初

Trunk(date,’Day’):回滚到上个星期天；

Datebetween ..and

 

日期的格式：

 

例如：

Selectename,to_char(hiredate,’yyyy-mm-dd’) from emp;

 

 

 

 

 

 

 

 

To_char()用法的其他格式：

(1)     select ename,to_char(mgr,'$99,999.00') from emp;

(2)     select ename,to_char(mgr,'L99,999.00') from emp;

 

将一个控制null转换为指定的值：

(1)select  name,nvl(credit,0) fromclass;

select ename,job,length(ename),length(job),nullif(length(ename,length(job)) "result" from emp;

(2)    coalesce如果第一个为空，则显示第二个，如果第二个为空显示第一个，都为空显示第三个；

select ename,mgr,coalesce(to_char(mgr),to_char(ename),'NoResult') from emp;

 

(3)    case语句：

select ename,mgr,case ename when'SMITH'then mgr*1.1

                           when'ALLEN'then mgr*2

                             else mgr end

                                from emp;

(4)    decode语句：

select ename,mgr,decode(ename,'SMITH', mgr*1.1,'ALLEN',mgr*2, mgr)resulttable from emp;

(5)    SQL语句嵌套:

select ename,mgr from emp where mgr=(selectmin(mgr) from emp) or mgr=(selectmax(mgr) from emp);

 

 

表格的连接查询：

左连接属于内部连接，内部连接要求两个表具有相同的字段，外部连接不需要两个表具有相同字段。

(1)    natural join的使用：后面接连接表的表名；

select deptno,dname,loc,job,mgr from dept naturaljoin emp;

(2)    using语句用于两个列的类型不同的情况下；

select deptno,dname,loc,job from dept join emp using(deptno);

(3)    on()语句类似where，里面填写条件，对于外部查询中左连接：left outer join

错误语句：select deptno,ename,dname,loc,job from dept leftouterjoin emp on(dept.deptno=emp.deptno);错误原因在于对于查询结果中的deptno没有指定所属表格。

正确语句：selectemp.deptno,ename,dname,loc,job from dept leftouterjoin emp on(dept.deptno=emp.deptno);

Right是对于右边的表中不符合条件也显示，full是对两个表执行操作。Cross的使用让第一个表的数据分别与表二进行匹配，共m*n个数据

(1)Having()语句，后面接的是函数条件，不是普通的where判断条件

错误语句：select dname from dept groupby dname having deptno>20;

正确语句：select dname from dept groupby dname havingsum(deptno)>20;

 

 

Any的用法，是与查询语句结果的所有值比较，直至成功。

select dname from dept where deptno>any(select deptno from dept where deptno>20);

 

All的用法，与查询结果全部比较，必须全部满足，才成功。

select dname from dept where deptno>all(select deptno from dept where deptno>20);

结果为空，没有满足全部条件的。

1、union是将两个SQL语句的结果合并起来，去掉重复数据，类似or。

select dname,deptno from dept where deptno<20unionselect dname,deptno from dept where deptno>20;

2、union将两个sql语句的结果全部合并。

select dname,deptno from dept where deptno<20unionallselect dname,deptno from dept where deptno>20;

3、intersect是将两个sql语句的共同部分选出来，类似and。

select dname,deptno from dept where deptno<30intersectselect dname,deptno from dept where deptno>=20;

4、 MINUS指令是运用在两个 SQL 语句上，它先找出第一个 SQL 语句所产生的结果，然后看这些结果有没有在第二个 SQL 语句的结果中，如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现。如果第二个 SQL语句所产生的结果并没有存在于第一个 SQL 语句所产生的结果内，那这笔资料就被抛弃。MINUS 的语法如下：

[SQL 语句 1] MINUS [SQL 语句 2]

select dname,deptno from dept where deptno<40minusselect dname,deptno from dept where deptno>=20;

5、 当在进行union操作时候，要求二者具有相同的栏目，如果SQL二者语言不具有相同的列栏，使用to_char等相关转换工具，使其为null；

select dname,to_number(null) deptno from dept where deptno<40unionselect dname,deptno from dept where deptno>=20;

6、 union语句中order by语句只能在组合的最后出现一次，每个SQL语句不能拥有各自的order by语句，并且只能以第一个sql语句中的列对应，默认情况下，第一个   sql语句的第一栏作为排序的列。

7、 创建表的时候数据类型有：not null，unique(值是唯一)，primary key，foreign key，check几个类型。Constraint（约束的意思）

column[CONSTRAINT constraint_name] constraint_type

(1) 修改一个外键字段的值，必须要确保该字段的作为主键的表中有输入的值，不然错。

(2) 不能删除用于其他表中作为外键的主键。

(3) 通过sql语句的select创建表，会把数据复制过去。

createtable lll asselectname,nofromclass;

(4)、ALTER TABLE employees READ ONLY

（5）如果视图中包含group、distinct、rownum关键字不能进行删除行操作。

(6)创建索引：