Oracle中的DDL,DML,DCL总结
来源:互联网 发布:数据粒度 综合程度 编辑:程序博客网 时间:2024/05/12 23:00
转自:http://blog.csdn.net/w183705952/article/details/7354974
DML(Data Manipulation Language,数据操作语言):用于检索或者修改数据。 DML包括: SELECT:用于检索数据; INSERT:用于增加数据到数据库; UPDATE:用于从数据库中修改现存的数据 DELETE:用于从数据库中删除数据。 DDL(Data Definition Language,数据定义语言): 用于定义数据的结构,比如 创建、修改或者删除数据库对象。 DDL包括:DDL语句可以用于创建用户和重建数据库对象。下面是DDL命令: CREATE TABLE:创建表 ALTER TABLE DROP TABLE:删除表 CREATE INDEX DROP INDEX DCL(Data Control Language,数据控制语言):用于定义数据库用户的权限。 DCL包括: ALTER PASSWORD GRANT REVOKE CREATE SYNONYM一:DCL(数据控制语言) 1、创建用户test2,密码也是test2(记得最有以分;号结束): create user test2 identified by test2; 2、给test2授权:create session;(允许用户登陆Oracle): grant create session to test2; 3、给test2分配创建表的权限; grant create table to test2; 4、给test2分配表空间的使用权限; grant unlimited tablespace to test2;二:DDL(数据定义语言练习) 1、创建表:SQL> create table t_user( userid number(30) not null primary key, username varchar(20) not null, age number(3), sex varchar(2), departid number(30) not null, foreign key(departid) references t_depart(departid) ); ★alter table student add primary key(userid)这样用alter也可以创建关联主外键。 2、删除表:SQL> drop table t_depart; 3、创建序列: create sequence seq_a minvalue 1000 maxvalue 99999999 start with 1000 increment by 1 nocache; 三:DML(数据操作语言): 1、insert插入SQL: (1)insert into t_depart (departid,departname,createdate) values (1,'市场部',sysdate); (2)insert into t_user values (seq_user.nextval,'马文涛',23,'男'); 2、delete删除SQL: (1)delete t_user;(太可怕了,如果在删除时不加条件,则把此表中的所有数据都会删除!) (2)delete t_user where userid = 3; 3、update更新SQL: (1) update t_user set username = '争伟',sex = '男'; (太恐怖了,更新时不加条件表中所有行记录的姓名都被修改了!) (2)update t_user set username = '文涛' where userid = 7; ★小结:我发现在增、删、改的SQL语句中用不到from关键字。 4、select查询SQL:格式——>select···from···where···group by···having···order by···; (1)查询所有的用户:select * from t_user; ★在oracle中这里的表明用别名时不能加as关键字 如: select * from t_user u;正确 select * from t_user as u;错误 (2)查询指定的列: select username,sex from t_user; (3)as给列以别名显示:select username as 用户名 from t_user;(这里的as关键字可以省略) (4)distinct去掉重复的行:select distinct username from t_user; (5)使用运算符:select age+10 from t_user;(给每个人的年龄都加10岁) (6)连接字符串: select '用户名:' || username from t_user;(Oracle用||做连接字符串操作符) (7)where子句:select * from t_user where username = '宝宝'; (8)between and在···之间: select * from t_user where userid between 9 and 10;这也等价于下面这条SQL: select * from t_user where userid >=9 and userid <=10; (9)in匹配集合中的任意值:select * from t_user where username in('马文涛','宝宝'); (10)like模糊查询:%匹配0个或多个任意字符串,_匹配1个任意字符串。 select * from t_user where username like '%涛%'; (11)null判断某列为空:select * from t_user where sex is null; (这里用is,不能用=,如果要返回不为null的记录就可以用is not null) (12)order by排序:ASC: 升序排列(可以省略),DESC: 降序排列 升序:select u.userid,u.username from t_user u order by u.userid; 降序:select u.userid,u.username from t_user u order by u.userid desc; (13)系统函数(对一组数据进行处理,返回一个值): AVG–求平均值,COUNT–统计记录数,MAX–最大值,MIN–最小值,SUM–求和 <1>返回最小和最大的用户编号: select min(userid),max(userid) from t_user; <2>返回总记录数: select count(*) from t_user; <3>返回某个字段不为空的记录数: select count(sex) from t_user; <4>返回不为空且不重复的记录数: select count(distinct sex) from t_user; (14)group by分组(分组了就不能直接返回*,经常和聚合函数count(age)一起使用): <1>按姓名分组,并统计每组人数:select count(*),username from t_user group by username; <2>根据多个字段分组: select username,age,count(*) from t_user group by username,age; ◆group by有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。 (15)having过滤分组:select username from t_user group by username having count(*) > 2; (16)子查询(子查询自身只能返回一个单独的值): <1>子查询放在select后面,作为其中的一个字段返回。 select u.username,(select d.departname from t_depart d where d.departid = u.departid) from t_user u; (返回用户和所属的部门,这中子查询理论上先执行外面的查询,只是我的感觉哦,嘿嘿!) <2>子查询放在from后面,作为一张临时表。 select * from (select username,sex s from t_user where departid=1) where s = '男'; (这种子查询应该先执行里面) <3>子查询放在where后面,作为条件的一部分。 select * from t_user where departid = (select departid from t_depart where departname = '财务部'); (这种子查询也应该先执行里面) (17)联合查询(当n张表连接时, 需要n-1个连接条件): <1>等值连接(内连接):select u.username,d.departname from t_user u,t_depart d where u.departid = d.departid; <2>外连接:即把不满足条件的记录也返回,用个+就行了, (+)操作符在哪边就代表另外一边不满足联合条件的记录可以被输出。这个感觉不太常用。 select b.book_id,b.book_name from book_info as b,book_click_num as c where b.book_id = c.book_id(+);四、触发器trigger:当特定事件发生时自动执行的代码块。 这些事件包括: (1)DML语句(INSERT,UPDATE,DELETE):before在DML语句被执行前触发操作,after在DML语句被执行后触发操作。 (2)DDL语句(CREATE 及 ALTER) (3)系统事件,例如启动/关闭[startup/shutdown]、错误[error] (4)用户事件, 例如登录/退出[logon/logoff]★两个特殊的变量——>:new新的记录值,:old保留原来的记录值 简单示例:create or replace trigger update_depart_trigger after update on t_depart for each row begin update t_user u set u.departid = :new.departid where u.departid = :old.departid; end update_depart_trigger; 此示例的作用:当部门的id改变后,由于用户中有外键引用了部门,所以用户的departid这个外键自动执行更新操作。 (这个触发器很奇怪,在黑窗口用sqlplus就不能创建,用plsql Developer工具就可以创建,很有意思,嘿嘿!)五、游标cursor:以循环取SQL语句的SELECT内容,它是存放结果集的数据对象,使用游标,我们只能逐条记录地得到查询结果。 作用:查询数据库,获取记录集合(结果集)的指针,可以让开发者一次访问一行结果集,在每条结果集上作操作。 使用:用游标有四种基本的步骤:声明游标(declare)、打开游标(open)、提取数据(fetch)、关闭游标(close)。 ★当你要往每一行插入一个数据只能用游标,或者更新结果集中的每行记录时也可用游标,用存储过程返回一个结果集。 ★触发器和存储过程会和数据库绑定,即一直保存在数据库中,而游标不会,它是任意时刻创建再打开再执行再关闭, 与数据库没有任何直接关系。 ★在Oracle中,不需要显示销毁游标.因为在Oracle中,很多东西是由JAVA写的.Oracle会自动销毁游标。 简单示例: -- 定义一个游标 declare cursor cursor_user is select username,age from t_user; //变量的定义也可以放到游标定义上面 a t_user.username%type;//定义个a变量,类型是t_user表中username列的类型。 b t_user.age%type;//同上 begin//SQL中可执行代码都在begin和end之间。 -- 打开游标 open cursor_user; -- 遍历游标 loop//循环抓取数据(loop是其中一种循环方式) fetch cursor_user into a,b; -- 将一行记录放入到变量中 dbms_output.put_line(a || ' ' || b);打印到输出控制台 exit when cursor_user%notfound; -- 当没有记录时退出循环 end loop;//退出循环 -- 关闭游标 close cursor_user; end; 加if条件判断游标示例: declare cursor cursor_user is select username,age from t_user; a t_user.username%type; b t_user.age%type; begin open cursor_user; loop fetch cursor_user into a,b; if a='宝宝'and b=43 then dbms_output.put_line(a || ' ' || b); end if; exit when cursor_user%notfound; end loop; close cursor_user; end; 六、存储过程procedure 概念:其实就是一组存放在数据库中SQL语句,普通SQL操作都在项目中写死的,而他只跟数据库进行绑定。 更准确的说存储过程是数据库服务器端的一段程序,它有两种类型。一种类似于SELECT查询,用于检索数据,检索到的数据能够以数据集的形式返回给客户(oracle存储过程本身没返回值,只是用out参数代替)。另一种类似于INSERT或DELETE查询,它不返回数据,只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。 优点: 1、提高效率。存储过程本身的执行速度非常快,而且,调用存储过程可以大大减少同数据库的交互次数。 2、提高安全性。假如将SQL语句混合在JSP代码中,代码外漏以后,也就意味着库结构外漏。 3、有利于SQL语句的重用。 ★oracle函数有返回值,但存储过程没有返回值,它的所有返回值都是通过out参数来替代的。 ★什么时候需要用存储过程 如果服务器定义了存储过程,应当根据需要决定是否要用存储过程。存储过程通常是一些经常要执行的任务,这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程,可以改善应用程序的性能。这是因为: .服务器往往具有强大的计算能力和速度。 .避免把大量的数据下载到客户端,减少网络上的传输量。 例如,假设一个应用程序需要计算一个数据,这个数据需要涉及到许多记录。如果不使用存储过程的话,把这些数据下载到客户端,导致网络上的流量剧增。 不仅如此,客户端可能是一台老掉牙的计算机,它的运算速度很慢。而改用存储过程后,服务器会很快地把数据计算出来,并且只需传递一个数据给客户端,其效率之高是非常明显的。 ★存储过程的参数 要执行服务器上的存储过程,往往要传递一些参数。这些参数分为四种类型: 第一种称为输入参数(in),由客户程序向存储过程传递值。 第二种称为输出参数(out),由存储过程向客户程序返回结果。 第三种称为输入/输出参数(in out),既可以由客户程序向存储过程传递值,也可以由存储过程向客户程序返回结果。 第四种称为状态参数,由存储过程向客户程序返回错误信息。 要说明的是,并不是所有的服务器都支持上述四种类型的参数,例如,InterBase就不支持状态参数。 简单示例: 1、无返回值存储过程(插入一个用户) create or replace procedure saveuser ( username in varchar2 , age in number, departid in number //定义两个输入参数,参数类型可以是自己写死,也可以用某个列的类型 比如:username t_user.username%type; ) as begin insert into t_user (userid,username,age,departid) values (seq_user.nextval,username, age,departid); end saveuser; (1)在sql中执行存储过程call saveuser('文涛',23,1);,也可以用execute和exec。 (2)在java里调用时就用下面的代码: package com.hyq.src; import java.sql.*; import java.sql.ResultSet; public class TestProcedureOne { public TestProcedureOne() { } public static void main(String[] args ){ String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:ORCLA"; Statement stmt = null; ResultSet rs = null; Connection conn = null; CallableStatement cstmt = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, " hyq ", " hyq "); CallableStatement proc = null; proc = conn.prepareCall("{ call saveuser(?,?,?) }"); proc.setString(1, "马文涛"); proc.setInt(2,23); proc.setInt(3,1); proc.execute(); } catch (SQLException ex2) { ex2.printStackTrace(); } catch (Exception ex2) { ex2.printStackTrace(); } finally{ try { if(rs != null){ rs.close(); if(stmt!=null){ stmt.close(); } if(conn!=null){ conn.close(); } } } catch (SQLException ex1) { } } } } 2、有返回值的存储过程(oracle存储过程本身没返回值)只是用out参数代替(非列表) //获得某部门下的所有用户姓名和年龄 create or replace procedure pro_getUserByDepart//oracle中不区分大小写,这里只是为了自己方便。 ( i_departid in t_user.departid%type, o_username out t_user.username%type, o_age out t_user.age%type )as begin select username,age into o_username,o_age from t_user where departid = i_departid; end pro_getUserByDepart; ★在pl/sql Developer中右击存储过程选择测试就可以在下面输入值直接看打印结果了。 ★由于这个存储过程会返回行记录集,所有在Developer中为了测试成功就在此过程中加入rownum=1条件判断, 即:select username,age into o_username,o_age from t_user where departid = i_departid and rownum=1; 如果不加那个rownum=1(当然你也可以让它等于2),多行记录会报“实际返回的行数超出请求的行数”这个异常。 ★其实我们用上面这种存储过程一般都只返回一行记录集,如果是返回多行那就要用到要用包pagkage和游标cursor了。 ★在利用select...into...语法时(把查询的结果放入输出参数中),必须先确保数据库中有该条记录,否则会报出"no data found"异常。 3、返回列表(必须要用包了,package里包含了游标),分两个步骤: ★为什么要在存储过程中用到游标时,要把此游标封装到一个包中呢,个人理解:由于游标是机开机关的一个 对象,它无法被存储过程调用,而包中恰恰能封装游标、函数等这些对象,所有就把游标放在一个包中。 (1)1, 建一个程序包。如下: create or replace package userpackage as type usercursor is ref cursor; end userpackage; (2)建立存储过程,存储过程为: create or replace procedure pro_getalluser ( pro_cursor out userpackage.usercursor )is begin open pro_cursor for select * from t_user; end pro_getalluser; 可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。 ★在java里调用时就用下面的代码(列出主要代码): package com.mwt.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import oracle.jdbc.OracleTypes; public class TestProcedure { public static void main(String...args){ try{ Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL9I"; String user = "test2"; String password = "test2"; Connection con = DriverManager.getConnection(url, user, password); String sql = "{call pro_getalluser(?)}"; CallableStatement cs = con.prepareCall(sql); cs.registerOutParameter(1, OracleTypes.CURSOR); cs.execute(); //返回第一个存储过程的输出参数,我只返回了一个游标也就只有一个输出参数,即就是获得第一个返输出参数getObject(1); ResultSet rs = (ResultSet)cs.getObject(1); while(rs.next()){ System.out.println(rs.getString(1)+"----"+rs.getString(2)+"----"+rs.getString(3)); } rs.close(); cs.close(); con.close(); }catch(Exception e){ e.printStackTrace(); } } }
0 0
- Oracle中的DDL,DML,DCL总结
- Oracle中的DDL,DML,DCL总结
- Oracle中的DDL,DML,DCL总结
- Oracle中的DDL,DML,DCL总结
- Oracle DDL,DML,DCL,TCL 总结
- Oracle DDL,DML,DCL,TCL 总结
- Oracle DCL、DDL、DML
- oracle DML,DDL,DCL区别
- Oracle DML DDL DCL TCL
- Oracle的DML、DDL、DCL
- Oracle DDL,DML,DCL,TCL 基础概念
- Oracle DDL,DML,DCL,TCL 基础概念
- Oracle DDL,DML,DCL,TCL 基础概念
- Oracle DDL,DML,DCL,TCL 基础概念
- Oracle DDL,DML,DCL,TCL 基础概念
- Oracle DDL,DML,DCL,TCL 基础概念
- Oracle DDL,DML,DCL,TCL 基础概念
- Oracle DDL,DML,DCL,TCL 基础概念
- spring整合rabbitmq(需要的jar包,spring如何整合rabbitmq)
- printk 优点
- spring整合Hibernate事务不能自动回滚
- Java实现解压Apk、往apk中增加文件
- FutureTask的用法
- Oracle中的DDL,DML,DCL总结
- OJ 系列之字符串分割
- iOS汉字转拼音
- 使用unity进行测试驱动开发的方法
- Android LruCache 缓存 类 源码 注解 分析
- java 后台 如何实现 谷歌 消息推送
- log4j
- 基础知识总结
- 网站字符编码