《Oracle_SQL语句大全---尚学堂马士兵讲义7826.docx》由会员分享，可在线阅读，更多相关《Oracle_SQL语句大全---尚学堂马士兵讲义7826.docx（30页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 Oracle SQL语句大全2009-04-13 15:391. desc(描述） emp 描述emp这张表2.desc dept 部门表3.desc salgrade 薪水等级4.select *from table 查找表中的元素5.dual 是系统中的一张空表 （可以用于计算各种数学表达式）6.select *from dual7.select sysdate from dual 取出系统时间8.select ename,sal*12 annul_sal(取的别名） from emp; 查找用户姓名和用户的年薪9.任何含有空值的数学表达式的值都是空值select ename,sal*1
2、2+comm from emp;10.select ename|sal from emp 其中的| (字符串连接符) 相当于将sal全部转化为字符串11.表示字符串的方法select ename |ajjf from emp;12.如果其中有一个单引号就用2个单引号来代替他select ename|sakj ldsfrom emp;13.select distinct deptno from emp (去除部门字段中重复的部分,关键字distinct)14.select distinct deptno,job from emp;(去除这2个字段中重复的组合)15.select *from de
3、pt where deptno=10; 取出条件(取出部门编号为10的记录)16.select * from emp where ename=CLIRK; 取出部门中姓名为clirk的记录(注意取出过程中ename用单引号隔开)17.select ename,sal from emp where sal1500; 取出部门中薪水大于1500的人的姓名18.select ename,sal,deptno from emp where deptno 10 取出部门中的部门号不等于10的19.select ename,sal,deptno from emp where enameCBA 取出部门中员
4、工名字大于CBA的员工(实际比较的是ACIIS码)20.select ename,sal from emp where sal between 800 and 1500 select ename,sal from emp where sal=800 and sal3-04月-81;宣传符合条件的日期24.select ename,sal,from emp where sal1000 or deptno=10; 找出工资薪水大于1000或者部门号等于10的员工25.select ename,sal from emp where sal not in(500,1000); 查找薪水不在500到10
5、00的员工姓名和月薪26.select ename,sal from emp where ename like %ALL%; 查找姓名中含有ALL的客户信息 %表示0个或多个字母 select ename,sal from emp where ename like _A%; 查找第二个字母是A的客户信息27. select ename,sal from emp where ename like _%$% escape $; 自己指定转义字符 select ename,sal from emp where ename like _%; 查找中间含有%相匹配的客户信息，运用转易字符28.selec
6、t * from dept order by deptno 对表中元素按部门号排序 select *from dept order by deptno desc 默认为升序asc，可以用desc按降序29.select ename,sal from emp where sal 1000 order by sal desc, ename desc 按照查询条件来查询，并排序（asc升序排列）30.select ename,sal*12 from emp where ename not like _%A% and sal800 order by sal desc31.select lower(en
7、ame) from emp 将ename都转化为小写 lower是函数能将字母转化为小写，upper(ename)32.select ename from emp where lower(ename) like _%a%; 找出ename 中所有的含有a的字符33.select substr(ename,2,3) form emp 从第2个字符开始截取3个字符34.select chr(65) from dual; 将65转化为字符35.select ascii(A) from dual 将ACSII码转化为字符串36.select round(23.565)from dual 四舍五入36.
8、select round(23,4565,2)from dual 四舍五入到第二位(第二个参数默认为0)37.select to_char(sal,$99,999.9999) from emp 按指定格式输出(9代表一位数字) select to_char(sal,L99,999.9999) form emp L代表本地字符(￥) 0代表占位符38.select hiredate from emp select to_char(hiredate,YYYY-MM-DD HH:MI:SS) from emp; 时间格式的显示 select to_char(sysdate,YYYY-MM-DD HH
9、:MI:ss) from dual; 十二小时制显示系统时间 select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) from dual 二四小时制显示系统时间39.select ename,hiredate from emp where hiredate to_date(2005-2-3 12:32:23,YYYY-MM-DD HH:MI:SS); 把特定格式字符串转换成日期40 select sal from emp where salto_number($1,250.00,$9,999.99); 取出比它大的一切字符串（把特定格式的数字转化成字符）41
10、 select ename,sal+nvl(comm,0) from emp; 讲comm值为空的用0来替换，单行函数（以一条记录为条件）一条对一条组函数42.select Max(sal) from emp; select Min(sal) from emp; select avg(sal) from emp; select sum(sal) from emp; select count(*) from emp; 查看表中一共有多少条记录 select count(*) from emp where deptno=10; 查找部门10一共有多少人；43.select avg(sal),dep
11、tno from emp group by deptno; 按部门号进行分组 select deptno,job,max(sal) from emp group by job,deptno; 按工作和部门号进行分组；44.select ename from emp where sal=(select max(sal) from emp); 子查询，查找部门中薪水最高的员工姓名45.group by 注意：出现在select列表中的字段，如果没有出现在组函数中必须出现在group by子句中where是过滤单条语句,having可以过滤分组。46.select avg(sal),deptno f
12、rom emp group by deptno having avg(sal)2000; 选出部门中平均薪水大于2000的部门，47.select * from emp where sal100 group by deptno having .order by. 先取数据-过滤数据-分组-对分组限制-排序48.select avg(sal) from emp where sal1200 group by deptno having avg(sal)1500 order by avg(sal) desc; 查找部门中平均薪水大于1200的员工，并按部门号进行排序，查询分组后的平均薪水必须大于15
13、00，查询结果按平均薪水从低到高排列子查询（把它当成一张表）49.select ename from emp where sal(select avg(sal) from emp); 查找出员工中薪水位于部门平均薪水之上的所有员工50.select ename,sal from emp join(select max(sal) max_sal from emp group by deptno) t on(emp.sal=t,max_sal and emp.deptno=t.deptno); 查找每个部门中薪水最高的51.select e1.ename,e2.ename from emp e1,
14、emp e2 where e1.mgr=e2.empno; 表的自连接(当成两张表来用)1999 SQL标准where语句里不写“连接条件”，只写数据过滤条件。join on(表间连接条件)，where写过滤条件，思路清晰。52.select dname,ename from emp cross join dept 交叉连接，笛卡尔SQL99中的新语法53.select ename,dname from emp join dept on(emp.deptno=dept.deptno); /on(连接条件)54.select ename,dname from emp join dept usin
15、g(deptno); 不推荐 查找emp和dept表中deptno相同的部分。55.select ename,dname,grade from emp e join dept d on(e.deptno=d.depno) join salgrade s on(e.sal between s.losal and s.hisal) where ename not like _%A%; (三表查找）56.select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr=e2.deptno); 表的自连接57.select e1.ename,e2.e
16、name from emp e1 left join emp e2 on(e1.mgr=e2.empno) 左外表连接/ 可以将左边表多余的数据拿出来，右外表连接：right (outer) join on select ename,dname from emp e right join dept d on(e.deptno=d.deptno)右外连接 select ename,dname from emp e full join dept d on(e.deptno=d.deptno)全连接58.求部门中薪水最高的 select ename,sal from emp join (select
17、 max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);-求部门平均薪水的等级select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal);-求部门中各成员的平均薪水等级select deptno,ename
18、,grade from emp join salgrade s on(emp.sal between s.losal and s.hisal);59.求部门中薪水等级的平均值 select deptno,avg(grade) from(select deptno,ename,grade from emp join salgrade s on(emp.sal between s.losal and s.hisal)t group by deptno;60.查找雇员中哪些是经理人 select ename from emp where empno in(select distinct mgr fr
19、om emp);61.select distinct e1.sal from emp e1 join emp e2 on(e1.sale2.sal); 自连接(不用组函数求出最高薪水） select distinct sal from emp where sal not in (select ename, e1.sal from emp e1 join emp e2 on(e1.sal( select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);DML（数据操
20、作语言）语句：更、删、改、查创建权限， conn sys/admin as sysdba grant create table,create view to scott;首先在C:下面建个文件夹备份文件一.createNewUser方法1.-backup scott -备份scott exp -执行exp.exe文件/c:tempexp ,.DMP文件。在c:temp下2.create user(创建用户）用超级管理员模式进入 create user 用户名 identified by 密码 default tablespace users quota 10M on users; grant
21、create session,create table,create view to 用户名 -授予权限 drop user 用户名 -删除用户3.import the data(导入备份数据） imp二.insertinsert into dept values (50,game,bj) 插入一条记录insert into dept2 (deptno,dname) values (78,games); 插入指定的几条记录insert into dept2 select *from dept 插入指定的表（表结构要一样）rollback; 回退（取消上一步操作）create table emp
22、2 as select * from emp; 备份emp这张表三. update emp2 set sal=sal*12 where deptno=10; update的用法四. delete from dept2 where deptno25 ; 删除语句的用法rownum r 只能和和或者=或者=transaction 事务:一个事务起始于一个DML语句DDL（数据定义）语言1.create table t(a varchar2(10); 创建表:2.drop table t 删除表mit 所有的(上一条事务的修改)提交，所有修改都结束了。对于rollback(rollback会回到原始
23、状态)无效，一个事务开始于第1条DML语句碰到执行DDL DCL语句事务自动提交(自动执行commit)对于rollback无效，正常断开连接，自动提交。 非正常提交，自动回滚rollbackDCL：数据控制语句-如grant 授权建表语句建学生信息表：create table stu(id number(6),name varchar2(20) constraint stu_name_nn not null,sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varcha
24、r2(50) unique (唯一约束);5个约束条件非空not null唯一unique（不能插入重复值），主键primary key（可以唯一标识整条记录）： 非空，唯一，数字 ，可以使用多个字段的组合作为主键外键 foreign key(class) references class(id), references参考 class中的id，被参考的字段必须是主键。 被参考的记录是无法删除的：两张表，在一张表插入数据的时候，不允许插入另一张表中一个字段没有的数据。默认添加。check：检查约束 create table stu(id number(6) primary key,name v
25、archar2(20) constraint stu_name_nn not null, 约束名constraintsex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50),constraint stu_name_email_uui unique(email,name) （表级约束）组合性约束，name和email的组合不能重复);主键约束方法二create table stu(id number(6),name varchar2(20) constra
26、int stu_name_nn not null,(非空约束)sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4) references class(id),(参考class 这张表，参考字段)email varchar2(50),constraint stu_id_pk primary key(id),constraint stu_name_uui unique(email,name) 组合性约束);外键约束create table class(id number(4) primary
27、 key, (id为被参考字段,被参考的字段必须是主键)name varchar2(20) not null)create table stu(id number(6),name varchar2(20) constraint stu_name_nn not null,(非空约束)sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4)email varchar2(50),constraint stu_class_fk foreign key(class) references class(
28、id), references参考constraint stu_id_pk primary key(id),constraint stu_name_uui unique(email,name) 组合性约束);像外键中插入关键字，1.insert into class values(1000,c1);2.insert into stu(id,name,class,email) values(1,a,1000,a);3.alter table stu add(addr varchar(20);添加表的结构4.alter table stu drop(addr); 删除表结构5.alter tabl
29、e stu modify(addr varchar2(150);修改精度6.alter table stu drop constraint stu_class_fk; 删除约束条件7.alter table stu add constraint stu_class_fk foreign key(class) references class(id),添加约束条件查找当前用户下有哪些表和哪些视图及哪些约束: 数据字典表user_tables：当前用户下有多少张表。user_views：当前用户下有多少个视图。user_constraints：当前用户下有多少个约束。user_indexes：当前
30、用户下有多少个索引。数据字典表的表：dictionary8.select table_name from user_names9.select view_name from view_names10.select constraint_name,table_name from user_constraints;desc dictionary数据字典表desc user_tables当前用户下面有多少张表select table_name from user_tables; 查找当前用户有多少张表索引： 加主键或者唯一约束的时候oracle会自动为该字段加索引。创建索引create index
31、idx_stu_email on stu(email);drop index idx_stu_email;查找索引select index_name from user_indexes;索引读的速度快了，插入速度变慢。经常需要查询的字段可以建立索引。优化数据库表首选。view 视图（子查询，虚表）视图赠加了维护的量create view v$_dept_avg_sal_info as (select .)create view v$_stu as select id, name, age from stu; -只给客户看非关键的信息非必要尽量不适用，因为会带来维护繁琐的问题。尽量不要用视图更新
32、数据。因为视图很可能从多个表拿数据。序列(sequence)：序列对象 产生唯一的，不间断的序列,一般用于主键create table arcticle(id number,title varchar2(1024),cont long);序列的创建sequence产生独一无二的序列，而且是oracle独有的create sequence seq;select seq.nextval from dual; 查找序列号insert into arcticle values(seq.nextval,a,b);往表中插入序列dorp sequence seq; -删除序列。数据库设计的3范式（姓范的设
33、定的规则）：实际当中可以打破不要存在冗余数据：一条数据只存放在一个表里，要用这条数据，通过该数据的主键和其他表联系。第一范式： 设计任何表都要有主键，列不可分 -不存在冗余数据，不能使用012345_张三_23第二范式： 如果有2个主键的话，不能存在部分依赖不是主键的字段，不能依赖于主键的一部分。第三范式， 不能存在传递依赖掌握一张语言：1、数据类型；2、语法。oracle内部的编程语言PL-SQL，用来补充SQL（没有分支，没有循环）PL：过程语言：带有分支、循环的语言。PL-SQL例子1：SQL set serveroutput on;SQL begin(必要的-程序开始执行)2 dbms
34、_output.put_line(hello world); -输出语句3 end;(结束)4 /例子2：SQL declare -声明变量 v_xxx2 v_name varchar2(20);3 begin4 v_name:=myname; -赋值5 dbms_output.put_line(v_name);6 end;7 /例子3：SQL declare2 v_num number:=0;3 begin4 v_num:=2/v_num;5 dbms_output.put_line(v_num);6 end;7 /declare*ERROR 位于第 1 行:ORA-01476: 除数为 0
35、ORA-06512: 在line 4例子4：declarev_num number:=0;beginv_num:=2/v_num;dbms_output.put_line(v_num);exceptionwhen others then -当其他的情况出现的时候dbms_output.put_line(error);end;/选数据库：够用为原则。小公司别用oracle，太贵了。变量声明的规则1.变量名不能够使用保留字，如from,select等2.第一字符必须是字母。3.变量名最多包含30个字符4.不要与数据库的表或者列同名5.每一行只能声明一个变量常用变量类型1. binary_inter
36、ger, 整数，主要用来计数，而不是用来表示字段类型2. number 数字类型 3. char 定长字符串4. varchar2 变长字符串,4096个字节（2048个字）5. date 日期6. long 长字符串，最长2GB(存大文章)7. boolean 布尔类型，可以取true、 false 和null（不初始化）的值例5：declare v_temp number(1); v_count binary_integer:=0; v_sal number(7,2):=4000.00; v_date date:=sysdate; v_pi constant number(3,2):=3.
37、14; -constant 相当于java里的final。常量 v_valid boolean:=false; v_name varchar2(20) not null:=myname;begin dbms_output.put_line(v_temp value:|v_temp); - |字符串连接符end;/用-可以注释一行-变量声明，使用%type属性例6：declare v_empno number(4); v_empno2 emp.empno%type; -声明变了，程序也跟着变 v_empno3 v_empno2%type; -变量变了，另一个变量也跟着变begin dbms_output.put_line(test);end;-table变量类型 ，table是一个数组，指定类型例7set serveroutput on;declare type type_table_emp_empno is table of emp.empno%type index by