下载文档原格式
Oracle基础SQL语句大全创建数据表空间：Create tablespace yhnLoggingDatafile ‘E:\yhn.dbf’Size 50mAutoextend on next 50mMaxsize 20480mExtent management local;增加数据文件：Alter tablespace yhnAddDatafile ‘E:\yhn1.dbf’Size 50m;修改数据文件的大小：Alter databaseDatafile ‘E:\yhn1.dbf’Resize 1024m;创建新用户：Create user yhn identified by 123456;密码修改：Alter user yhn identified by 654321;用户锁定：Alter user yhn account lock;用户解锁：Alter user yhn account unlock;删除用户：Drop user yhn cascade;用户及角色管理：--创建用户guoqy，密码guoqyCREATE USER guoqy IDENTIFIED BY guoqy;--为用户guoqy授登录权GRANT CREATE SESSION TO guoqy;--为用户guoqy授权创建表GRANT CREATE TABLE TO guoqy;--为用户guoqy授权，允许查看scott下面的emp表中的数据GRANT SELECT ON scott.emp TO guoqy;--为用户guoqy授权，允许用户对scott的emp表所有权限（包括查询、修改、添加、删除等操作）GRANT ALL ON scott.emp TO guoqy;--通过角色进行授权--1.创建一个角色student CREATE ROLE student;--2.为角色授权，允许对scott的emp表进行操作GRANT ALL ON scott.emp TO student;--3允许student角色进行登录、资源访问GRANT RESOURCE,CONNECT TO student;--创建zhangsan账号CREATE USER zhangsan IDENTIFIED BY zhangsan;--将student角色赋予zhangsan，此时zhangsan具有student角色的所有的权限GRANT student TO zhangsan;--从zhangsan处收回student角色权限REVOKE student FROM zhangsan;示范账户：Scott tigerSELECT SYSDATE FROM dual;SELECT systimestamp FROM dual;SELECT ROWID,ROWNUM,empno,ename FROM emp;CREATE TABLE department( --建立表departmentdeptno NUMBER(2) NOT NULL, --列名deptno 类型为number 约束条件为not nulldeptname VARCHAR2(20) NOT NULL, --列名depname 类型为varchar 约束条件为not nullLOCATION VARCHAR2(40) default'呼和浩特' --列名location 类型为varchar 约束条件为默认值=呼和浩特);SELECT * FROM department; --查询表department--DESC department； -- sqlplus显示表的结构ALTER TABLE department ADD(info Varchar2(400)); --增添字段infoALTER TABLE department modify(info Varchar2(800)); --修改字段infoAlter Table department Rename Column info To d_info; --重命名字段info改为d_infoAlter Table department Drop(info); --删除字段infoTruncate Table department; --截断表departmentRename department To depart; --重命名表改department为departComment On Table depart Is '部门信息表'; --为表添加注释Comment On Column depart.location Is '部门的具体位置'; --为字段添加注释Select * From User_Tab_Columns Where table_name='DEPART'; --查看表中字段的基本信息Select * From User_Tab_Comments Where table_name='DEPART'; --查看表中注释的信息Select * From User_Col_Comments Where table_name='DEPART'; --查看字段中注释的信息Create Table yhn(Name,salary,hiredate) --子查询创建表AsSelect ename,sal,hiredate From emp;Create Table employee(eno Number(5) Not Null,ename Varchar2(10) Not Null,age Number(2) Not Null,salary Number(7,2) Not Null,phone varchar2(16),deptno Number(2),Constraint pk_employee_eno Primary Key(eno),--1.参照其他表得主键--2.建立本表得外键约束----FOREIGN KEY (deptno)里面的deptno是本表中的一列----REFERENCES department（deptno）表示关联到department表中的主键deptnoConstraint fk_employee_dept_deptno Foreign Key(deptno)References department (deptno),Constraint ck_employee_age Check (age Between 18 And 70));Alter Table employeeDisable Constraint ck_employee_age;--对约束的禁用Alter Table employeeEnable Constraint ck_employee_age;--对约束的激活Alter Table employeeRename Constraint ck_employee_age To sk_employee_age;--修改约束名Alter Table employeeDrop Constraint sk_employee_age;--删除约束Insert Into department Values (2,'市场部','呼和浩特');Insert Into employee Values (10002,'test',20,3000,'180110111',1);Insert Into employee(eno,ename,deptno) Values (10003,'张三',1);Insert Into employee(ename,deptno,eno) Values ('lisi',1,1003);Insert Into employee(eno,ename,age,deptno) Values (10004,'王五',15,1);--检查约束Insert Into emp Values (1002,'test','teacher',1002,'1-10月-2015',3000,800,10);Insert Into emp Values (1003,'test','teacher',1003,To_date('2015-10-1','yyyy-MM-dd'),3000,800,10);Insert Into emp Values (1004,'jerry','student',1004,Sysdate,1000,300,20);--插入日期Insert Into department Select * From dept Where deptno=10;Insert Into department(deptno,deptname,location) Select deptno,dname,loc From dept Where deptno=20;--插入查询数据Update department Set deptname='研发部',location='北京' Where deptno=20;--更新数据Delete From department Where deptno=10;--清除有条件的数据Truncate Table department;--清除全部数据 --删除数据Delete From department Where deptno=10;Savepoint sp1;Delete From department Where deptno=20;Savepoint sp2;Rollback To sp1;Commit;Select * From department;--事务提交与回滚Select * From department;Select * From employee;Select * From emp;查询Select * From emp;Select empno,ename From emp;Select Distinct deptno From emp;--distinct删除重复Select ename,sal+comm As salary From emp;Select ename,sal+comm salary From emp;--sal+comm运算改为salarySelect ename,sal 月薪,sal*12-10000 年薪 From emp Where deptno=30;Select ename,sal 月薪,sal*12-10000 年薪From emp Where deptno=30 And sal>=1600;--条件查询Select ename,sal 月薪,sal*12-10000 年薪From emp Where deptno=30 Order By 年薪 Asc;--排序查询 asc升序 desc 降序Select * From emp Where ename Like '_A%N';Select * From emp Where ename Like '%A%';--模糊排序Select ename From emp Where ename Like '%A%'UnionSelect dname From dept;--联合查询--查询工资超过1500 的员工信息，包括员工的姓名，薪水，部门名称--并按照员工的工资降序排序--（显示内连接）Select e.ename,e.sal,d.dname From emp e Inner Join dept d On e.deptno=d.deptno Where e.sal>1500 Order By sal Desc;-- ( 隐式内连接 )Select e.ename,e.sal,d.dname From emp e ，dept d Where e.deptno=d.deptno And e.sal>1500 Order By sal Desc;--1.取消了 emp 表中的外键约束--2.插入数据Insert Into emp Values(1008,'test','teacher',1002,'1-10月-2015',3000,800,80);--左联接Select * From emp e Left Join dept d On e.deptno=d.deptno;--右联接Select * From emp e Right Join dept d On e.deptno=d.deptno;--全联接Select * From emp e Cross Join dept d On e.deptno=d.deptno;--自联接--SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL;Select Distinct e1.empno,e1.ename From emp e1,emp e2 Where e1.empno=e2.mgr;Select Avg(sal) 平均工资， sum(sal) 工资总和 From emp;--统计查询Select Count (empno) From emp;Select Count (1) From emp;--查询总人数Select deptno From emp;Select deptno,Max(sal),Min(sal) From emp Group By deptno;--分组时候，查找的内容要么是聚合函数统计结果，要么是分组的条件Select deptno,Count (empno) From empWhere sal>1000 --统计各个部门中，基本工资超过1000 的员工的人数Group By deptnoHaving Count (empno)>3 --（仅显示部门人数3 人以上的部门）Order By Count (empno) Asc, deptno Asc;Select deptno, job,Count(1) From emp Group By deptno , job--Having Count (1)>=2Order By deptno Asc;--查看一下每个部门的各个岗位上的人员数量Select ename,sal From emp Where sal>(Select sal From emp Whereename='ALLEN');Select ename, job,sal,deptno From emp Where job In(Select Distinct job From emp Where deptno=20);--子查询Select * From empWhere Not Exists --Where Not Exists 就是子查询里面有数据不执行父查询，子查询没有数据执行父查询 where exists 反之(Select * From empWhere deptno=20 And job='CLERK')--查询的结果作为另一个查询的临时表Select * From(Select * From emp Where job='CLERK')Where deptno>20;--分页查询，每页显示5 条，查询第二页信息Select * From(Select Rownum As num , empno,ename, job From emp)Where num>=11 And num<=15;select * from emp where hiredate>'01-10月-1981';select '员工号：'|| empno,'员工姓名：'|| ename,'时间：'|| hiredate from emp;select job from emp where deptno=10--Minus 减去--intersect 交--union all 并select job from emp where deptno=20;Select ascii('a'),ascii('A') From dual;--ascii a A 的位置Select chr(97) From dual;--位置为97的字符为aSelect '同学'||'迟到了' From dual;--链接语句Select concat('同学','迟到了') From dual;--同上Select instr('abcdef','c') From dual;--从下标为1开始查询Select instr('abcdefadd','a',5) From dual;--下标为5 开始查询Select instr('abcdefadad','a',5,2) From dual;--下标为5 查询第二个aSelect lower('The student is Lucy') From dual;--小写Select upper('The student is Lucy') From dual;--大写Select ltrim(' student is Lucy ') From dual;--向左对齐Select rtrim(' student is Lucy ')||'**' From dual;--向右对齐Select ltrim('*!*!student is Lucy*!*!','*!') From dual;--把指定的字符向左去除Select substr('The student is Lucy',4,8) From dual;--字符串的截断从第四个字符开始查找并输出8 个字符Select Replace('The student is Lucy','e','a') From dual;--把字符e 换成 aSelect length('The student Is Lucy') From dual;--整句话的字符长度（包括空格）Select * From emp;Select empno,ename, job,decode(deptno,10,'研发部'，20，'市场部',30,'技术部','其他部门') From emp;Select lpad(ename,10,'*'),rpad(ename,10,'*')From emp;--数值函数Select abs(-5) From dual;Select ceil(5.8),ceil(-5.8) From dual;Select floor(5.8),floor(-5.8) From dual;Select round(5.8),round(5.3) From dual;Select trunc(9.357),trunc(9.357,2) From dual;Select empno,ename,trunc(sal /21,2) From emp;--日期函数Select Sysdate,add_months(Sysdate,3) From dual;Select round(Sysdate) From dual;Select trunc(Sysdate) From dual;Select next_day(Sysdate,'星期一') From dual;Select extract(Year From Sysdate) From dual;Select empno,ename,extract(Year From hiredate) 入职年份From emp Where extract (Year From hiredate)=1981;--转换函数Select ename,to_char(sal,'$999,999.99') From emp;游标------------------隐式游标 SQL% -----------------Declarev_no emp.empno%Type;v_name emp.ename%Type;Beginv_no:=&职员编号;Select ename Into v_nameFrom emp Where empno=v_no;If Sql%Rowcount>0 Thendbms_output.put_line('职员的姓名是'||v_name);End If;ExceptionWhen no_data_found Thendbms_output.put_line('职员未找到');End;------------------显式游标 cursor% ---------------DeclareCursor emp_cursor IsSelect ename,empno From emp;--声明游标v_ename emp.ename%Type;v_empno emp.empno%Type;BeginOpen emp_cursor;--打开游标Loop--代表循环的意思Fetch emp_cursor Into v_ename,v_empno;--从游标中取值，并进行处理Exit When emp_cursor%Notfound;dbms_output.put_line('员工号：'||v_empno||' 员工姓名:'||v_ename);--上面三行是取一个值End Loop;--循环结束Close emp_cursor;--关闭游标End;DeclareCursor emp_cursor IsSelect empno,ename,sal From emp Order By sal;--声明游标row_emp emp_cursor%Rowtype; --游标行BeginOpen emp_cursor;--打开游标Loop--开始循环Fetch emp_cursor Into row_emp;--从游标中取值，并进行处理Exit When emp_cursor%Notfound;dbms_output.put_line(row_emp.ename||' '||row_emp.empno||' '||row_emp.sal);End Loop;Close emp_cursor;End;----------------带参数的游标-------------------Declarev_deptno emp.deptno%Type;--定义变量v_deptno，使之字段与deptno相同 Cursor emp_cursor(dno number) IsSelect empno,ename,sal From emp Where deptno=dno;--声明游标row_emp emp_cursor%Rowtype;--游标行Beginv_deptno:='&部门编号';--输入参数值Open emp_cursor(v_deptno);--传递参数LoopFetch emp_cursor Into row_emp;Exit When emp_cursor%Notfound;dbms_output.put_line(row_emp.empno||' '||row_emp.ename||' '||row_emp.sal); End Loop;Close emp_cursor;End;---------------循环游标----------------DeclareCursor emp_Cursor IsSelect empno,ename,sal From emp Order By sal;BeginFor row_emp In emp_cursorLoopdbms_output.put_line(row_emp.empno||' '||row_emp.ename||' '||row_emp.sal);End Loop;End;--------------使用显式游标更新数据-------------Declarecursor emp_cursor IsSelect empno,ename,sal From emp Order By sal DescFor Update;v_increase number:=0;v_new_sal Number;BeginFor row_emp In emp_cursor Loopv_new_sal:=row_emp.sal+v_increase;Update emp Set sal=v_new_salWhere Current Of emp_cursor;dbms_output.put_line(row_emp.empno||' '||row_emp.ename||' '||' Old salary ='||row_emp.sal||' new salary='||v_new_sal);End Loop;End;。
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.（2）WHERE子句中的连接顺序．：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.（3）SELECT子句中避免使用‘* ‘：ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间（4）减少访问数据库的次数：ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量, 读数据块等；（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE 参数, 可以增加每次数据库访问的检索数据量,建议值为200（6）使用DECODE函数来减少处理时间：使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.（7）整合简单,无关联的数据库访问：如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)（8）删除重复记录：最高效的删除重复记录方法( 因为使用了ROWID)例子：DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);（9）用TRUNCATE替代DELETE：当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)（10）尽量多使用COMMIT：只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a. 回滚段上用于恢复数据的信息.b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE为管理上述3种资源中的内部花费（11）用Where子句替换HAVING子句：避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle 中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。
一、简介Oracle是一种常见的数据库管理系统，广泛应用于各类企业和组织中。
在Oracle数据库中，SQL语句是对数据库进行操作的重要工具。
本文将介绍Oracle常用的SQL语句，包括查询、插入、更新、删除等操作，以帮助读者更好地理解和应用这些语句。
二、查询数据1. SELECT语句SELECT语句用于从数据库中检索数据，语法如下：```sqlSELECT column1, column2, ...FROM table_nameWHERE condition;```其中，column1, column2是要检索的列名，可以使用*代表所有列；table_name是要检索的表名；condition是筛选条件，可选。
2. DISTINCT关键字DISTINCT关键字用于返回唯一不同的值，语法如下：```sqlSELECT DISTINCT column1, column2, ...FROM table_name;```3. WHERE子句WHERE子句用于筛选满足条件的数据，可使用运算符（=、>、<等）和逻辑运算符（AND、OR、NOT等），语法如下：```sqlSELECT column1, column2, ...FROM table_nameWHERE condition1 AND condition2;```4. ORDER BY子句ORDER BY子句用于对结果集进行排序，语法如下：```sqlSELECT column1, column2, ...FROM table_nameORDER BY column1 DESC;```其中，DESC代表降序，ASC代表升序，默认为升序。
5. GROUP BY子句GROUP BY子句用于对结果集进行分组统计，常与聚合函数一起使用，语法如下：```sqlSELECT column1, SUM(column2)FROM table_nameGROUP BY column1;```三、插入、更新和删除数据1. INSERT INTO语句INSERT INTO语句用于向数据库中插入新记录，语法如下：```sqlINSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);```2. UPDATE语句UPDATE语句用于更新数据库中的记录，语法如下：```sqlUPDATE table_nameSET column1 = value1, column2 = value2, ... WHERE condition;```3. DELETE语句DELETE语句用于删除数据库中的记录，语法如下：```sqlDELETE FROM table_nameWHERE condition;```四、连接表和子查询1. JOIN子句JOIN子句用于连接多个表的数据，常用的有INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。
Oracle数据库常用Sql语句大全最简单的就是查询：select语句数据库操作语言DML：update、insert、delete等数据库定义语言DDL：create、drop、alter等等oracle取前几条数据语句sqlserver中可以用top n 的方法，oracle中用rownum，但如果只用rownum会随机取数据，如果想按一定顺序取前几条数据则可这样写：select * from (select列 from 表 where 条件 order by列 desc) where rownum<>select * from (select rownum r ,* from test) ttwhere tt.r 50 and tt.r <= 100;=””>这样子写是会全表扫描的的，小的表不觉得会有多慢，数据量大了你就知道有多不好了，改成这样就不会了oracle怎幺写”IF”语句?如果是 SQL 语句里面使用。
使用 DECODE 或者 CASE WHENDECODE 的用法就是DECODE(abc, 50, 'A’, 'B’)CASE WHEN 的用法就是CASE WHEN abc=50 THEN 'A’ ELSE 'B’ END如果是在存储过程里面的话，就是简单的 IF/ELSE 了SQL 语句的话，就是SELECTabc, DECODE(abc, 50, 'A’, 'B’)FROM表什幺是oracle实例，写出在oracle如何查找实例名的sql语句oracle数据库实例(instance_name)是用于和操作系统进行联系的标识，也就是说数据库和操作系统之间的交互使用的是数据库实例。
数据库名和实例名可以相同也可以不同。
在一般情况下，数据库名和实例名是一对一的关系，但如果在oracle并行服务器架构(即oracle实时应用集群)中，数据库名和实例名是一对多的关系。
oracle语句大全和用法Oracle是一个常用的关系型数据库管理系统，提供了丰富的SQL语句用于数据库的操作。
以下是Oracle常用的一些SQL语句及其用法：1.SELECT语句：用于检索数据。
例：SELECT*FROM表名2.INSERTINTO语句：用于向表中插入一条或多条记录。
例：INSERTINTO表名(列1,列2,...)VALUES(值1,值2,...)3.UPDATE语句：用于更新表中的数据。
例：UPDATE表名SET列1=值1,列2=值2WHERE条件4.DELETE语句：用于删除表中的数据。
例：DELETEFROM表名WHERE条件5.CREATETABLE语句：用于创建表。
例：CREATETABLE表名(列1数据类型,列2数据类型,...)6.ALTERTABLE语句：用于修改表结构。
例：ALTERTABLE表名ADD列名数据类型7.DROPTABLE语句：用于删除表。
例：DROPTABLE表名8.CREATEINDEX语句：用于创建索引。
例：CREATEINDEX索引名ON表名(列名)9.ALTERINDEX语句：用于修改索引。
例：ALTERINDEX索引名RENAMETO新索引名10.DROPINDEX语句：用于删除索引。
例：DROPINDEX索引名11.GRANT语句：用于授权。
例：GRANT权限ON对象TO用户12.REVOKE语句：用于撤销授权。
例：REVOKE权限ON对象FROM用户以上只是Oracle中的一些常用SQL语句及其用法，还有很多其他的语句和高级用法。
建议参考Oracle官方文档或相关教程深入学习和了解。
oracle基础sql语句二、SQLStructur query language结构化查询语言，是操作关系型数据库中的对象。
DDL（Data definition language 数据定义语言），用于建表或删表操作，以及对表约束进行修改create table , alter table , drop table 对表结构的增删操作。
DML（Data manipulation language 数据操作语言），向表中插入纪录，修改纪录insert , update , delete , mergetransaction ,事务控制语言，由DML语句组成的，commit; ,rollback;select 查询语句dcl 授权语句 grant三、OracleDBMS 数据库管理系统有Oracle提供，还提供AS，应用服务器DBA 数据库管理员四、相关操作1、sqlplus 访问数据库命令（本地访问/远程访问），和数据库建立连接的命令，是数据库操作的环境sqlplus 用户名/密码2、show user 显示当前用户的用户名改变身份可以直接connect 用户名/密码 --- 这个是sqlplus命令在sqlplus中可以使用 ! 可以在shell和sqlplus间切换，!shell命令可以在sqlplus中使用shell命令。
实际上是sqlplus开了子进程来执行shell命令。
3、Oracle数据库中的表分两类：用户表（用户使用操作的表），系统表（数据库系统维护的表，也叫数据字典）对用户表的DDL操作出发了对系统表的DML操作！五、基本语法1、select查询语句select table_name from user_tables;（查询系统表）以上的查询语句就是查询本用户下所拥有的所有表的表名。
投影操作，只查看选择的字段的信息。
选择操作，查看字段中的特定某些信息。
联接操作，多表查询，通过表间连接，查寻出多表中的信息（1）select table_name from user_tables;（查询系统表）以上的查询语句就是查询本用户下所拥有的所有表的表名。
