归纳语句.docx
《归纳语句.docx》由会员分享,可在线阅读,更多相关《归纳语句.docx(18页珍藏版)》请在冰豆网上搜索。
归纳语句
归纳语句
2012年7月10日星期二
17:
43
Insertintostudent(xh,xm,sex,birthday,classid,sal)select*fromstudent;
自我复制
Insertintostudent(xh,xm,sex,birthday,classid,sal)values(1,'li','男','10-8月-1991',1,100);
Altersessionsetnls_date_format='yyyy-mm-dd';
修改插入日期的格式
insertintostudent(xh,xm,sex,birthday,classid,sal)values(1,'li','男','1991-02-02',1,100);
Altertablestudentadd(familyvarchar(20));
Select*fromuserwhererownum<10orderbyid;
SELECT*FROM`user`WHEREbirthdayISNOTNULL;
SELECTcount(*)TABLES,table_schemaFROMinformation_schema.TABLES
wheretable_schema='gcsx'GROUPBYtable_schema;
统计数据库有多少张表
select*from(selectstmt_day,ticket_type,sam_id,rownumrnfromtbl_metro_tick_20120704whererownum<=20orderbyrownum)wherern>=10
选择记录的第10条到第20条的11条记录。
。
。
。
用rownum关键字。
但是要注意外面的rownum需要是子查询中的rownum取的别名
Select*fromtable1leftouterjointable2ontable1.no=table2.no;
Select*fromtalbe1rightouterjointable2ontable1.no=talbe2.no;
Spoold:
\a.txt;
Select*fromuser;
Spooloff;
将Select出来的记录导入文件中
AltertabletableNamemodify(field_namevarchar(20));
AltertabletableNameaddprimarykey(field_name);
AltertabletableNamedropprimarykey(field_name);
AltertabletableNameaddcolNamecolType;
AltertabletableNmedropcolumncolName;
AltertableOldtableNamerenametoNewtableName;
CreatetabletableName(field_name1type[notnull][primarykey],field_name2type);
SUM,AVG,MAX,MIN,COUNT
Selectsum(field_name)fromtable
Conntest/test;
Showuser;
Grantconnect,resource,dab,sysdbatoLiYunwithgrantconnect;
Select*fromall_users;
创建表,加外键的建立方法
Createtablemytable(mynonumber(3),mynamevarchar(10),myclassvarchar(10),myForeignIdnumber(3),foreignkey(myForeignId)referencesmytable(tableid);
查询当前用户下所有对象
SQL>select*fromtab;
Selec'HelloWorld!
'||chr(10)||'tothenewworld!
'
在字符串中插入回车符
Select*fromtableNamewhereusername='&name‘;可以输入要查找的字段名称
select(selectcount(userid)fromliyingyun)+(selectcount(s_id)fromschool)fromdual;
Selectadd_months(sysdate,24)fromdual;给现有时间加上两年
GrantallontableNametoliyun;
授权给用户
RevokeselectontableNamefromliyun;
收回liyun对表tableName的权限
GrantselectontableNametoliyunwithgrantoption;
对象权限的授权
如果是系统权限
Grantconnecttoxiaomingwithadminoption
Settimingon;
Selectsal*13+nvl(comm,0)*13
年工资fromemp;
Selectename,hirdatefromempwherehirdate>'1-1月-1982';
Select*fromempwhere(deptno,jop)=(selectdeptno,jopfromempwhereename='SMITH';
CreatetablemyTable(id,username,passsword,address)adselectuserid,username,password,addressfromStudent;
创建一表,将Student表中的数据和字段导入到新建的表中
Uinon(查询相同的字段并去除内容相同的)
Selectusername,schoolfromstudentwhereuserid>100unionselectusername,schoolfromclasswhereschoolName='YunNanNormalUniversity';
Unionall重复的就不能去除
Minus求差集
Updateempset(job,sal,comm)=(selectjob,sal,commfromempwhereename='SMITH')where
Ename='SCOTT';
事物和锁
Commit;
Savepointa1;
Deletefromempwhereuserid=10;
Savepointa2;
Deletefromempwhereuserid=20;
Rollbacktoa2;回滚到操作点
Rollbacktoa1;
Rollback回滚所有的操作
提交了就不可以回滚。
只能是没有commit的才可以进行rollback;
Settransactionreadonly;
Selectlower(ename)fromemp;小写
Selectlower(ename),salfromemp;小写
Selectupper(ename)fromemp;大写
Selectenamefromempwherelength(eaname)=5;
Selectsubstr(ename,1,3)fromemp;截取字符从第1个开始到第3个
Selectupper(substr(ename,1,1)fromemp;取出第一个字母大写
Selectlower(substr(ename,2,length(ename))fromemp;取出后面所有的小写
Selectupper(substr(ename,1,1)||lower(substr(ename,2,length(ename))fromemp;组合为首字母大写的单词
Replace
Selectreplace(fieldName,'被替换的字段内容','我的老鼠')fromtableName;
用”我是老鼠“来替换'A'这个字母
Round(n,[m]);selectround(sal,1),roundfromempwhereename='liyun';
截取sal后面小数点一位
Trunc(n,[m]);截取数字
Mod
Floor向下取整
Ceil向上取整
Mod
Last_day
selectmod(10,2)fromdual;取模的函数
在做Oracle测试时候,可以使用dual这个表来测试。
Sysdate返回系统时间
Add_months(d,n);加多少个月
distinct
Selectename,hirdatefromempwheresysdate>=add_months(hiredate,120);
Selectfloor(sysdate-hiredate)"入职天数"fromemp;
Selectenamefromempwherehiredate=(last_day(hiredate)-2);
查出每个月倒数第三天入职的员工
Selectename,to_char(hiredate,'yyyy-mm-ddhh24:
mi:
ss)fromemp;
显示时间的格式,以及时间的时分秒。
。
。
。
To_char(field_Name,'L99,999.99');将钱的单位加上去,L是local的意思。
中国酒是RMB
注意格式
Selectename,hiredate,to_char(sal,'$99,999.99')fromemp;
显示:
名字,雇用时间,薪水($19,222.98);
显示1980年入职的所有员工
Select*fromempwhereto_char(hiredate,'yyyy')=1980;
Select*fromempwhereto_char(hiredtae,'mm')=12;
Sys_context系统函数
Selectsys_context('USERENV','db_name')fromdual;
Selectsys_context('USERENV','language')fromdual;
Selectsys_context('USERENV','')fromdual;
备份恢复数据库和表的方法
Sysdba
Showparameter;
Showuser;
Selct*fromdba_users;显示数据库所有用户的详细信息
Select*fromdba_role_privs;显示用户具有的角色
Select*fromdba_sys_privs;显示用户具有的系统权限
Select*fromdba_col_privs;显示用户具有的列权限
Select*fromdba_tab_privs;显示用户具有的对象权限
数据库表的逻辑备份:
导出使用exp命令来完成,常用选项有:
Userid=scott/m123
Tables=(emp)
Owner=system
Full=y
Inctype=complete
Rows
File=d:
/aaa.dmp
导出表:
Expuserid=scott/tiger@myoral_192.168.6.30tables=(emp)file=d:
/e1.dmp;
Expuserid=scott/tiger@myoraltables=(emp,dept)file=d:
/my.txtdirect=y;
Expuserid=scott/tiger@myoralowner=scottfile=d:
/e1.dmp;
导出自己的方案
导出数据库
导出数据库是指利用exp导出所有数据库中的对象及数据,要求该用户具有dba的权限或者
是exm_full_database权限
Expuserid=system/manager@myoralfull=yinctype=completefile=d:
/no1.dmp;
增量备份
Impuserid=scott/m123@myoraltables=(emp)file=:
/emp.dmp;
(导入导出都在命令窗口中完成)
表空间数据字典性能视图
PL/SQL(procedurallanguage/sql)
模块化的设计思想[分页的过程,订单的过程,转账的过程]
减少网络传输量
提高安全性,提高系统的运行效率
编写一个存储过程,该过程可以向某表中添加记录
1.创建一张表
Createtablemytest(namevarchar(10),idnumber1(10));
2.创建过程
Create[orreplace]procedureliyun_pro1is
Begin
--执行
Insertintomytestvalues('黎云',1);
End;
/
查看错误信息:
showerror;
3.如何调用:
exec过程名(参数值);
Execliyun_pro1;
Callliyun_pro1;
屏幕剪辑的捕获时间:
2012/7/12星期四18:
07
PL/SQL规范
注释:
--
多行:
/**/
定义变量:
v_sal;
定义常量:
c_rate;
定义游标:
_cursor后缀,emp_cursor
定义例外:
e_error;
Block(块)
1.PL/SQL块的三个部分:
定义部分,执行部分,例外部分
Declare--定义常量,变量,游标,例外,复杂数据类型
Begin--执行的PL/SQL语句和sql语句
Exception--处理运行的各种错误
End;
2.
3,最简单的块:
Begin
Dbms_output.put_line("HelloWorld!
");(开发一个包,重要)
End;
/
Setserveroutputoff;(关闭输出选项)
块的实例2:
Declare
v_enamevarchar2(5);
Begin
Selectenameintov_enamefromempwhereempno=&no;
Dbms_output.put_line('雇员名'||v_ename);
End;
/
有定义和执行部分的块:
Declare--定义变量
v_enamevarchar(5);
Begin
--执行部分
Selectenameintov_enamefromempwhereempno=&no;
--在控制台显示用户名
Dbms_output.put_line('用户名'||v_ename):
--异常处理
Excepiton
Whenno_data_foundthen
Dbms_output.put_line('你的编号出错:
');
--end;
/
过程
编写一个过程:
可以输入雇员名,新工资,可以修改员工的工资
如何调用过程的两种方法:
execcall
如何在java中调用一个存储过程
?
如何使用过程返回值
Createprocedureli_pro(inNamevarchar2,newsSalnumber)is
Begin
Updateempsetsal=newsSalwhereename=spName;
End;
/
调用:
Execli_pro('scott',4567);
查看:
Select*fromemp;
存储过程主要还是给程序调用,例如Java
在Java中调用存储过程:
函数
输入雇员的姓名,返回改雇员的年薪
Createfuntionli_fun1(liNamevarchar)returnnumberisyearSalnumber(7,2);
begin
Selectsal*12+nvl(comm,0)*12intoyearSalfromempwhereename=liName;
ReturnyearSal;
End;
/
函数调用:
Varabcnumber;
Callli_fun1('scott')into:
abc;
包
1.创建一个包,li_package,声明该包有一个过程update_sal,
还有一个函数annual_income,但是具体的实现是没有的
Createpackageli_packageis
Procedureupdate_sal(namevarchar2,newSalnumber();
Functionannual_income(namevarchar2)returnnumber;
End;
2.建立包体:
createpackagebody
(给li_package实现包体)
Createorreplacepackagebodyli_packageis
Procedureupdate_sal(namevarchar2,newesalnumber)
is
Begin
Updateempsetsal=newsalwhereename=name;
End;
Functionannual_income(namevarchar2);
Returnnumberis
Annual_salarynumber;
Begin
Selectsal*12+nvl(comm,0)intoannual_salaryfromempwhereename=name;
Returnannual_salary;
End;
End;
3.调用包的过程,或者函数
Callli_package.update_sal('scott',100);
触发器
指隐含执行的存储过程。
常用的事件包括:
insert,update,delete
触发操作就是一个pl/sql块,可使用createtrigger来创建
变量
定义变量和常量
标量类型(scalar)
1.定义一个变长字符串:
v_enamevarchar(10);
2.定义一个小数范围-9999,99~9999.99
v_salnumber(6,2);
3.定义一个小数并给一个初始值为5.4
v_sal2number(6,2):
=5.4
4.定义一个布尔变量,不能为空,初始值为False
v_validbooleannotnulldefaultfalse;
5.定义一个日期类型的数据:
v_hiredatedate;
使用标量:
输入员工号,显示员工的姓名,工资,个人所得税(0.03)
说明变量的使用,看看如何编写
Declare
c_tax_ratenumber(3,2):
=0.03;//定义税率为0.03
v_usernamevarchar2(5);
v_salnumber2(7,2);
v_tax_salnumber2(7,2);
Begin
Selectename,salintov_username,v_salfromempwhereempno=&no;
v_tax_sal:
=v_sal*c_tax_rate;
Dbms_output.putline('姓名是:
'||v_useranme||'工资是:
'||v_sal||'所得税:
'||v_tax_sal);
End;
标量—使用%type类型
v_usernameemp.ename%type;//长度就是表emp中ename字段类型的长度
,查出来是多大就的多大的字段长度
符合变量(composite)—介绍
用于存放多个值的变量
主要用于:
pl/sql记录,pl/sql表,嵌套表,varray
1.pl/sql记录:
类似于高级语言中的结构体
Typeemp_record_typeisrecord{nameemp.ename%type,salaryemp.sal%type,titleemp.job%type};
li_recordemp_record_type;
Begin
Selectename,sal,jobintoli_recordfromempwhereempno=7788;
dbms_output.putline('员工名:
'||li_record.name||'工资是:
'||li_record.salary);
End;
结果:
SCOTT工资是120
------------------------------------------28讲-48分钟-pl/sql表
select*from(selectt.*,dense_rank()over(orderbycardid)rankfromtbl_metro_tick_20120704t)whererank=10;
@