数据库学习14-回顾

数据字典
information_schema(mysql)
--oracle
SELECT * FROM tab$;
SELECT * FROM user_objects;
SELECT * FROM dba_roles;
SELECT * FROM all_tab_cols;

连接查询
联表查询
SELECT ... FROM tab1,tab2,tab3 ...
  WHERE tab1.con = tab2.conn
     AND-- tab1.com = tab3.cnn 
        tab2.ccn = tab3.conn...;
        
SELECT ... FROM tab3 JOIN         
 (SELECT ... FROM tab1 [INNER] JOIN tab2 
   ON tab1.con = tab2.conn) aa
   ON tab3.conn = aa.con;
   
SELECT ... FROM tab1,tab2 
  WHERE tab1.conn(+) = tab2.con;   
  
SELECT ... FROM tab1 LEFT [OUTER] JOIN tab2 
  ON tab1.con = tab2.conn;    
SELECT ... FROM tab1 RIGHT [OUTER] JOIN tab2 
  ON tab1.con = tab2.conn; 
  
SELECT * FROM emp x,emp y
  WHERE x.mgr = y.empno;
  
--递归查询  
SELECT LEVEL,t.* FROM emp t 
  START WITH empno=7839
  CONNECT BY PRIOR empno=mgr
   ORDER BY LEVEL;

表空间


触发器，函数和过程，游标 视图，索引，导入导出
create index 索引名 on emp(empno) reverse;
create bitmap index 索引名 on emp(job);
--游标
DECLARE
  CURSOR cur IS
   SELECT * FROM emp;
   
  e emp%ROWTYPE; 
BEGIN
  OPEN cur;
  FETCH cur INTO e; --iterator.next();
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  FETCH cur INTO e; 
  dbms_output.put_line(e.ename);
  
  CLOSE cur;
END;   

DECLARE
  CURSOR cur IS
   SELECT * FROM emp;
   
  e emp%ROWTYPE; 
BEGIN
  OPEN cur;
  /*
  LOOP
    FETCH cur INTO e; 
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line(e.ename);
  END LOOP;
  */
  FETCH cur INTO e;
  WHILE cur%FOUND LOOP
    dbms_output.put_line(e.ename);
    FETCH cur INTO e; 
  END LOOP;
  CLOSE cur;
END;

DECLARE
  CURSOR cur IS
   SELECT ename FROM emp;
BEGIN
  FOR ee IN cur LOOP
    dbms_output.put_line(ee.ename); 
  END LOOP;
END;

DECLARE
  CURSOR cur IS
   SELECT ename FROM emp;
  e VARCHAR2(10);
BEGIN   
  OPEN cur;

  LOOP
    FETCH cur INTO e; 
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line(e);
  END LOOP; 
END;

--过程
CREATE OR REPLACE PROCEDURE pro_cur(n OUT NUMBER)
 IS
  CURSOR cur IS
   SELECT ename FROM emp;
  e VARCHAR2(10);
BEGIN   
  dbms_output.put_line('参数:'||n);
  OPEN cur;

  LOOP
    FETCH cur INTO e; 
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line(e);
  END LOOP; 
END;

SELECT * FROM user_objects 
  WHERE object_type = 'PROCEDURE';

ORACLE中回滚和重做的细节。。。

java中使用存储过程和方法，批量删除