归纳语句.docx

1、归纳语句归纳语句2012年7月10日 星期二17:43Insert into student(xh,xm,sex,birthday,classid,sal)select * from student;自我复制Insert into student(xh,xm,sex,birthday,classid,sal)values(1,li,男,10-8月-1991,1,100);Alter session set nls_date_format=yyyy-mm-dd; 修改插入日期的格式insert into student(xh,xm,sex,birthday,classid,sal）values(

2、1,li,男,1991-02-02,1,100);Alter table student add(family varchar(20);Select * from user where rownum10 order by id;SELECT * FROM user WHERE birthday IS NOT NULL;SELECT count(*) TABLES, table_schema FROM information_schema.TABLES where table_schema = gcsx GROUP BY table_schema; 统计数据库有多少张表select * from

3、 (select stmt_day,ticket_type,sam_id,rownum rn from tbl_metro_tick_20120704 where rownum =10选择记录的第10条到第20条的11条记录。用rownum关键字。但是要注意外面的rownum需要是子查询中的rownum取的别名Select * from table1 left outer join table2 on table1.no = table2.no;Select * from talbe1 right outer join table2 on table1.no = talbe2.no;Spool

4、 d:a.txt;Select * from user;Spool off;将Select 出来的记录导入文件中Alter table tableName modify(field_name varchar（20）；Alter table tableName add primary key(field_name);Alter table tableName drop primary key(field_name);Alter table tableName add colName colType;Alter table tableNme drop column colName;Alter ta

5、ble OldtableName rename to NewtableName;Create table tableName(field_name1 type not nullprimary key,field_name2 type);SUM , AVG ,MAX, MIN ,COUNTSelect sum(field_name) from tableConn test/test;Show user;Grant connect,resource,dab,sysdba to LiYun with grant connect;Select * from all_users;创建表，加外键的建立方法

6、Create table mytable(myno number(3),myname varchar(10),myclass varchar(10),myForeignId number(3),foreign key(myForeignId) references mytable(tableid);查询当前用户下所有对象SQL select * from tab;Selec Hello World!|chr(10)| to the new world!在字符串中插入回车符Select * from tableName where username=&name;可以输入要查找的字段名称selec

7、t(select count(userid)from liyingyun)+(select count(s_id)from school) from dual;Select add_months(sysdate,24)from dual; 给现有时间加上两年Grant all on tableName to liyun;授权给用户 Revoke select on tableName from liyun;收回liyun对表tableName 的权限Grant select on tableName to liyun with grant option; 对象权限的授权如果是系统权限Grant

8、 connect to xiaoming with admin optionSet timing on;Select sal*13+nvl(comm,0)*13 年工资 from emp;Select ename,hirdate from emp where hirdate1-1月-1982;Select * from emp where (deptno,jop)=(select deptno,jop from emp where ename=SMITH;Create table myTable(id,username,passsword,address) ad select userid,u

9、sername,password,address from Student;创建一表，将Student表中的数据和字段导入到新建的表中Uinon (查询相同的字段并去除内容相同的)Select username,school from student where userid100 union select username ,school from class where schoolName=YunNanNormalUniversity;Union all 重复的就不能去除Minus 求差集Update emp set （job,sal,comm) =(select job,sal,com

10、m from emp where ename=SMITH) whereEname =SCOTT;事物 和锁Commit;Savepoint a1;Delete from emp where userid =10;Savepoint a2;Delete from emp where userid=20;Rollback to a2; 回滚到操作点Rollback to a1; Rollback 回滚所有的操作提交了就不可以回滚。只能是没有commit的才可以进行rollback；Set transaction read only;Select lower(ename) from emp; 小写S

11、elect lower(ename)，sal from emp; 小写Select upper(ename) from emp; 大写Select ename from emp where length(eaname)=5;Select substr(ename,1,3) from emp;截取字符 从第1个开始到第3个Select upper(substr(ename,1,1) from emp; 取出第一个字母大写Select lower(substr(ename,2,length(ename)from emp;取出后面所有的小写Select upper(substr(ename,1,1)

12、 |lower(substr(ename,2,length(ename)from emp;组合为首字母大写的单词Replace Select replace(fieldName,被替换的字段内容,我的老鼠) from tableName;用”我是老鼠“来替换A这个字母Round (n,m); select round(sal,1),round from emp where ename=liyun;截取sal后面小数点一位Trunc(n,m);截取数字ModFloor 向下取整Ceil 向上取整Mod Last_dayselect mod(10,2) from dual; 取模的函数在做Orac

13、le测试时候，可以使用dual这个表来测试。Sysdate 返回系统时间Add_months(d,n);加多少个月distinctSelect ename,hirdate from emp where sysdate=add_months(hiredate,120); Select floor(sysdate-hiredate) 入职天数 from emp;Select ename from emp where hiredate=(last_day(hiredate)-2);查出每个月倒数第三天入职的员工Select ename, to_char(hiredate,yyyy-mm-dd hh2

14、4:mi:ss)from emp;显示时间的格式，以及时间的时分秒。To_char(field_Name,L99，999.99);将钱的单位加上去，L是local的意思。中国酒是RMB注意格式Select ename,hiredate,to_char(sal,$99,999.99)from emp;显示: 名字，雇用时间，薪水（$19,222.98）;显示1980年入职的所有员工Select * from emp where to_char(hiredate,yyyy)=1980;Select * from emp where to_char(hiredtae,mm)=12;Sys_conte

15、xt 系统函数Select sys_context(USERENV,db_name)from dual;Select sys_context(USERENV,language)from dual;Select sys_context(USERENV,)from dual;备份 恢复 数据库和表的方法SysdbaShow parameter;Show user;Selct * from dba_users;显示数据库所有用户的详细信息Select * from dba_role_privs;显示用户具有的角色Select * from dba_sys_privs;显示用户具有的系统权限Selec

16、t * from dba_col_privs;显示用户具有的列权限Select * from dba_tab_privs;显示用户具有的对象权限数据库表的逻辑备份：导出使用exp命令来完成，常用选项有：Userid=scott/m123Tables=(emp)Owner=systemFull=yInctype=completeRowsFile=d:/aaa.dmp导出表：Exp userid=scott/tigermyoral_192.168.6.30 tables=(emp) file=d:/e1.dmp;Exp userid= scott/tigermyoral tables=(emp,d

17、ept) file=d:/my.txt direct=y;Exp userid=scott/tigermyoral owner=scott file=d:/e1.dmp;导出自己的方案导出数据库导出数据库是指利用exp导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exm_full_database权限Exp userid=system/managermyoral full=y inctype=complete file=d:/no1.dmp;增量备份Imp userid=scott/m123myoral tables=(emp) file=:/emp.dmp;(导入导出都在命令

18、窗口中完成)表空间 数据字典 性能视图PL/SQL (procedural language/sql) 模块化的设计思想分页的过程，订单的过程，转账的过程减少网络传输量提高安全性，提高系统的运行效率编写一个存储过程，该过程可以向某表中添加记录1.创建一张表Create table mytest(name varchar(10),id number1(10);2.创建过程Create or replace procedure liyun_pro1 isBegin-执行Insert into mytest values(黎云，1）；End;/查看错误信息： show error;3.如何调用: e

19、xec 过程名(参数值);Exec liyun_pro1;Call liyun_pro1;屏幕剪辑的捕获时间: 2012/7/12 星期四 18:07PL/SQL规范注释：-多行：/* */定义变量：v_sal;定义常量：c_rate;定义游标：_cursor后缀,emp_cursor 定义例外：e_error;Block(块)1.PL/SQL块的三个部分：定义部分，执行部分，例外部分Declare-定义常量，变量，游标，例外，复杂数据类型Begin-执行的PL/SQL语句和sql语句Exception-处理运行的各种错误End;2.3，最简单的块：BeginDbms_output.put_l

20、ine(Hello World!); (开发一个包，重要)End;/Set serveroutput off;(关闭输出选项)块的实例2：Declarev_ename varchar2(5);BeginSelect ename into v_ename from emp where empno=&no;Dbms_output.put_line(雇员名|v_ename);End;/有定义和执行部分的块：Declare -定义变量v_ename varchar(5); Begin-执行部分Select ename into v_ename from emp where empno=&no;-在控制

21、台显示用户名Dbms_output.put_line(用户名|v_ename):-异常处理ExcepitonWhen no_data_found thenDbms_output.put_line(你的编号出错：);-end;/过程编写一个过程：可以输入雇员名，新工资，可以修改员工的工资如何调用过程的两种方法:exec call如何在java中调用一个存储过程？如何使用过程返回值Create procedure li_pro(inName varchar2,newsSal number) isBeginUpdate emp set sal=newsSal where ename=spName;E

22、nd;/调用：Exec li_pro(scott,4567);查看：Select * from emp;存储过程主要还是给程序调用，例如Java在Java中调用存储过程：函数输入雇员的姓名，返回改雇员的年薪Create funtion li_fun1(liName varchar) return number is yearSal number(7,2);begin Select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=liName;Return yearSal；End;/函数调用：Var abc number;Call

23、li_fun1(scott) into:abc;包1.创建一个包，li_package,声明该包有一个过程update_sal,还有一个函数 annual_income，但是具体的实现是没有的Create package li_package isProcedure update_sal(name varchar2,newSal number();Function annual_income(name varchar2)return number;End;2.建立包体：create package body(给li_package实现包体)Create or replace package b

24、ody li_package isProcedure update_sal(name varchar2,newesal number)is BeginUpdate emp set sal=newsal where ename=name;End;Function annual_income(name varchar2);Return number isAnnual_salary number;BeginSelect sal*12+nvl(comm,0) into annual_salary from emp where ename=name;Return annual_salary;End;En

25、d;3.调用包的过程，或者函数Call li_package.update_sal(scott,100);触发器指隐含执行的存储过程。常用的事件包括：insert,update,delete触发操作就是一个pl/sql块，可使用create trigger来创建变量定义变量和常量标量类型(scalar)1.定义一个变长字符串：v_ename varchar(10);2.定义一个小数 范围-9999,999999.99v_sal number(6,2);3.定义一个小数并给一个初始值为5.4v_sal2 number(6,2):=5.44.定义一个布尔变量，不能为空，初始值为Falsev_val

26、id boolean not null default false; 5.定义一个日期类型的数据：v_hiredate date;使用标量：输入员工号，显示员工的姓名，工资，个人所得税（0.03）说明变量的使用，看看如何编写Declarec_tax_rate number(3,2):=0.03; /定义税率为0.03v_username varchar2(5);v_sal number2(7,2);v_tax_sal number2(7,2);BeginSelect ename,sal into v_username,v_sal from emp where empno=&no;v_tax_s

27、al:=v_sal*c_tax_rate;Dbms_output.putline(姓名是：|v_useranme|工资是：|v_sal|所得税：|v_tax_sal);End;标量使用%type类型v_username emp.ename%type;/长度就是表emp中ename字段类型的长度，查出来是多大就的多大的字段长度符合变量（composite）介绍用于存放多个值的变量主要用于：pl/sql记录，pl/sql表,嵌套表,varray1.pl/sql记录:类似于高级语言中的结构体Type emp_record_type is recordname emp.ename%type, sala

28、ry emp. sal%type, title emp. job%type ;li_record emp_record_type;BeginSelect ename,sal,job into li_record from emp where empno=7788;dbms_output.putline(员工名：|li_record.name|工资是：|li_record.salary);End;结果：SCOTT工资是120-28讲 -48分钟 -pl/sql表select * from(select t.*,dense_rank() over (order by cardid) rank from tbl_metro_tick_20120704 t)where rank =10;