尚学堂Oracle笔记(一).doc

1、:5560/isqlplus/ 访问自己的或者别人的oracle。2、isqlplus不用装客户端也行。3、sqlplus sys/bjsxt as sysdba4、desc emp/dept/salgrade;/显示表结构5、select sysdate from dual;/显示日期6、select a*12 nihao Y from dual;/别名中有空格用双引号7、select emname|aa from emp;/连接字符8、select wo|aa from emp;两个单引号代表一个一个点印单引号,返回结果为woaa9、select distict a from dual;10、select name from dual where name is null/空值查找。11、select name from dual where name like %a%/包含a的所有name，like %包含%的name。 /为转义字符12、自己定义转义字符select name from dual where name like %$% escape$/name里包含%的纪录，并定义了$为转义字符。13、select name from dual order by id desc/asc ;-按降序/升序排列14、select name from dual order by id desc,age asc;-先按id降序排列再按age升序排列。15、select lower(name) from dual;-name 都小写16、select substr(name,2,3)from dual;-把name从第二个字母开始截取3个字符。17、select chr(65)from dual;-转换成A18、slect ascii（A）from dual;-转换成ascii码65；19、select to_char(sal,$99,999,9999)from emp-结果$800.0000 ,$1,600.0000。如果把$换成L则输出￥800.0000 ,￥1,600.0000。如果把99，999，9999换成00，000，0000则输出为$00800.0000，$01,600.000020、select to_char(sysdate,yyyy-mm-dd hh:mi:ss)from dual ;-格式化时间,hh为12进制小时，hh24为24进制小时。21、select hierdate wehre hierdate to_date(1981-2-30 12:34:51,yyyy-m-dd hh24:mi:ss);-格式化特定字符串为日期22、select sal from emp where sal to_number($1,250.00,$9,999,00);23、select to_char (12.356,999,99) from dual;-12.35select round(12.356,999,99) from dual;-12.3624、组函数：sum avg count max min25、select count(name)from emp;-统计name不为null的纪录数。-1、select deptno,max(sal) from emp group by deptno where deptno=10；-不对，设计分组的条件必须用having而不能用where。2、一条综合查询select avg(sal)from empwhere sal 1200 -重点group by deptnohaving avg(sal) 2000order by avg(sal) desc3、select enamefrom empwhere sal in (select max(sal) from emp group by deptno) ；-条件返回多条值 ，肯定会出错，因为sal满足的是in后（）内的多条值中的一条即可。4、sql1992不支持全外连接，sql1999支持select ename,dname from emp e full join dept d on (e.deptno=d.deptno)；-把全部记录输出，相当于同时用了左右连接。5、笛卡尔积出现的例子select ename,dname from emp,dept;-没有where条件，输出笛卡尔积6、不用组函数求最高工资select salfrom empwhere sal not in(select distinct (e1.sal) from emp e1, emp e2 where e1.sal e2.sal)7、可以把一个很长的sql当作一个view求部门平均工资等级最低的部门名称select dnamefrom deptwhere deptno =(select deptnofrom (select avg(grade) avgrade, deptnofrom (select e.sal, s.grade, e.deptnofrom emp e, salgrade swhere e.sal between s.losal and s.hisal)group by deptno)where avgrade =(select min(avgrade) avgradefrom (select avg(grade) avgrade, deptnofrom (select e.sal, s.grade, e.deptnofrom emp e, salgrade swhere e.sal between s.losal and s.hisal)group by deptno)可以创建视图 deptsalcreate view deptsal as (select avg(grade) avgrade, deptnofrom (select e.sal, s.grade, e.deptnofrom emp e, salgrade swhere e.sal between s.losal and s.hisal)group by deptno)则sql语句可以简写成select dnamefrom deptwhere deptno =(select deptnofrom deptsalwhere avgrade = (select min(avgrade) avgrade from deptsal)8、取工资前五名 用rownumselect t.* from (select ename from emp order by sal desc )t where rownum 5 ；-rownum只能和或5；-错误要想取5到8位只能这么取（论坛分页显示用）select r, enamefrom (select ename, rownum rfrom (select ename from emp order by sal desc)where r = 5and r = 8-1、conn sys/bjsxt as sysdba ;-系统管理员登陆2、查看表所在的表空间select TABLESPACE_NAME from all_tables where table_name=EMP;-导出数据库-2、导出某用户下的所有数据进入一个空文件夹；exp scott/tigerfxjsbps_test ；-执行exp命令3、创建用户create user ynp identified by 507 -用户ynp 密码507default tablespace users -默认的表空间是 usersquota 10M on users; -分配10M空间，也就是这个用户只能存10M的东东。分配权限grant create session, -授予连接数据库权限create table, -授予创建表的权限create view to ynp; -授予创建视图的权限；4、导入数据库进入数据库备份文件所在的文件夹中；ynp/507 - 要导入数据的用户名scott -只导入socct这个用户的数据（这些表从这个用户导出）-1、备份数据create table dept2 as select * from dept;2、插入数据 insert（三种方式）insert into dept2 values(50,jim,beijing)；insert into dept2 (deptno,dname) values(60,tom)；insert into dept2 select * from dept；-注意没有valuesdelete from dept2 where deptno = 20; -删除一行或多行.3、dml语句select、update、insert、delete 后直接跟表名-事务-1、事务的开始起始于第一条dml语句；-1 、varchar2的最大储存是4k2、创建表create table student (id number(6) primary key, -主键约束 (非空,唯一) 代表一条记录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)references class(id), -外键约束email varchar2(50) unique,-创建唯一约束/consraint student_id_pk primary key(id, name) -也可以这样创建主键,也可以两个主键/constraint stu_class_fk foreign key(class)references class(id) -外键约束consraint student_name_email_uniq unique(name,email) -表级约束条 件 name和email字段同时不能重复)create table class (id number(4) primary key, 被参考的必须是主键name varchar2(10) not null)-1、alter table student add (adddress varchar2(10) ;-添加字段2、alter table student drop (adddress)；-删除某个字段3、alter table student modify (adddress varchar2(50)；-修改某个字段4、alter table student drop constraint stu_class_fk;-删除约束5、alter table student add constraint stu_class_fk foreign key(class)references class(id);-添加约束6、几个数据字典表desc user_tables; -查看当前用户有多少张表.select table_name from user_tables; -查看当前用户有哪些张表.select view_name from user_views;-查看当前用户有哪些张视图.select constraint_name from user_constraints;-查看当前用户有哪些约束select * from dictionary -查看数据字典表select * from User_Tables -用户表select * from user_views -用户视图select * from user_constraints -用户约束-1、create index idx_stu_email on students(email);-创建索引drop index idx_stu_email;-删除索引当创建主键约束或唯一约束或着两者组合时索引自动创建。索引提高查询速度，减慢修改效率；-视图-视图增加了维护难度（如修改了表结构），但简化了查询；隐藏敏感信息；create view v$_sal as select sal from emp;-创建视图-序列-1、create sequence seq ；-创建序列对象create sequence seq start with 1 increment by 1; -从1开始,每次递增1。select seq.nextval from dual；-取序列值insert into student (id,name)values(seq.nextval,zhangsan)；-保证了id不重复drop sequence seq；-删除序列对象- 三范式-1、三范式目的是尽量少的冗余数据；2、要求：第一范式：要有主键，列不可分；（不能像xx_xy_zz进行设计）第二范式：不能存在部分依赖（不是主键的字段不能部分依赖主键）；第三范式：不能存在传递依赖（如：学号（主键）-班级编号-班级信息）-PL/SQL-1、一个简单的程序set serveroutput on;begindbms_output.put_line(HelloWorld!);end;/ -执行语句2、一个简单的程序块declarev_name varchar2(20);beginv_name:=i am ynp;dbms_output.put_line(v_name);end;3、一个完整的简单程序declarev_num number := 0;beginv_num :=2/v_num;dbms_output.put_line(v_num);exceptionwhen others thendbms_output.put_line(error);end;-变量声明的规则1.变量名不能够使用保留字,如from、select等2.第一个字符必须是字母3.变量名最多包含30个字符4.不要与数据库的表或者列同名5.每一行只能声明一个变量-4、变量声明尽量以v_ 开头5、常用变量类型binary_integer ：整数，主要用来计数而不是用来表示字段类型number :数字类型char ：定长字符串类型varchar2 ：变长字符串类型 最大4Kdate： 日期long：长字符串，最长2Gboolean：布尔类型，可以取值为 false，True，null。null参与运算值不确定，所以最好声明变量时就给它赋值。6、变量声明declarev_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.14; -constant相当java里的final(常量)v_valid booolean := false; -不能打印出布尔值.v_name varchar2(20) not null :=ynp;begindbms_output.put_line(v_temp is|v_temp); -为连接符end;7、变量声明，使用%type属性声明变量的数据对应表的某个字段的数据，当表结构改变时（如把number(7,2)，改为number(8,3)时，必须修改相应程序），用%type属性可以解决该问题。例：declarev_empno number(4);v_empno2 emp.empno%type; -empno2数据类型于emp表中的empno字段相同；v_empno3 v_empno2%type;-v_empno3数据类型于v_empno2 数据类型相同begindbms_output.put_line(Test);end;-自定义变量-1、table 变量类型(相当于java中的数组)declaretype type_table_emp_empno is tabl