常用知识点

表空间：主要就是用于存放表的物理空间

创建表空间：create tablespace ts1 datafile 'E:\ts1.dbf' size 50M;

自动扩展大小：create tablespace ts2 datafile 'E:\ts2.dbf' size 50M autoextend on next 10M;

设置最大空间：create tablespace ts3 datafile 'E:\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;

查看用户的表空间，在视图  Dba_Users  中查看
SELECT a.* from Dba_Users a

更改用户默认表空间：alter database default tablespace ts1;

表空间改名：alter tablespace ts1 rename to tss1;

删除表空间：drop tablespace ts3 including contents and datafiles;

序列：作为数据库里的对象，主要作用就是生成主键的唯一值

SELECT a.*,rowid FROM aa a

1:NEXTVAL、CURRVAL 序列两个重要属性

CREATE SEQUENCE A1_SEQ;

SELECT A1_SEQ.nextval FROM dual

SELECT A1_SEQ.currval FROM dual

2: START WITH 100

CREATE SEQUENCE A2_SEQ START WITH 100

SELECT A2_SEQ.nextval FROM dual

3: MINVALUE 5 MAXVALUE 100

CREATE SEQUENCE A3_SEQ MINVALUE 5 MAXVALUE 100

SELECT A3_SEQ.nextval FROM dual

4:INCREMENT BY

CREATE SEQUENCE A4_SEQ INCREMENT BY 3

SELECT A4_SEQ.nextval FROM dual


5:综合

CREATE SEQUENCE A5_SEQ START WITH 10 INCREMENT BY 1

SELECT A5_SEQ.nextval FROM dual

数据类型和函数

1：字符串

SELECT a.*,rowid FROM scott.emp a

substr 从位置1开始，截取长度为3位
SELECT substr(ename,1,3) FROM scott.emp a WHERE a.empno=7369

trim 删除左右两边空格
SELECT TRIM(ename) FROM scott.emp a WHERE a.empno=7369


2：NUMBER 数字：NUMBER(6,3) -> 123.456，六位数字，小数占三位

round
SELECT round(sal) FROM scott.emp a WHERE a.empno=7369
SELECT round(sal,1) FROM scott.emp a WHERE a.empno=7369

CEIL
SELECT CEIL(12.8) 薪水 FROM scott.emp a WHERE a.empno=7369
SELECT CEIL(-12.8) 薪水 FROM scott.emp a WHERE a.empno=7369

floor
SELECT floor(12.8) 薪水 FROM scott.emp a WHERE a.empno=7369;
SELECT floor(-12.8) 薪水 FROM scott.emp a WHERE a.empno=7369;


to_char：格式化数值
SELECT to_char(123.45,'0000.000') 薪水 FROM dual;
SELECT to_char(123.45,'9999.999') 薪水 FROM dual;
SELECT to_char(1231232.45,'99,999,999.99') 薪水 FROM dual;
SELECT to_char(1231232.454,'FM99,999,999.99') 薪水 FROM dual;
SELECT to_char(1231232.456,'FM99,999,999.99') 薪水 FROM dual;
SELECT to_char(1231232.45,'$99,999,999.99') 薪水 FROM dual;
SELECT to_char(1231232.45,'99,999,999.99C') 薪水 FROM dual;



3：日期函数

SELECT Sysdate from dual
SELECT Systimestamp from dual
SELECT add_months(Sysdate,2) from dual
SELECT last_day(SYSDATE) from dual


SELECT to_date('2055-12-25 15:55:11','YYYY-MM-DD HH24:MI:SS') from dual

SELECT extract(YEAR FROM Sysdate) from dual
SELECT extract(MONTH FROM Sysdate) from dual
SELECT extract(DAY FROM Sysdate) from dual

to_char：日期格式化

SELECT to_char(Sysdate,'yyyy-MM-DD') from dual
SELECT to_char(Sysdate,'yyyy-MM-DD HH24:MI:SS') from dual



4：聚合函数

SELECT sum(a.sal) FROM scott.emp a
SELECT AVG(a.sal) FROM scott.emp a
SELECT MIN(a.sal) FROM scott.emp a
SELECT MAX(a.sal) FROM scott.emp a
SELECT COUNT(a.sal) FROM scott.emp a

NVL 如果为空，则为第二个数，不为空则为第一个结果
SELECT a.* FROM scott.emp a
SELECT a.ename,nvl(a.comm, 0) FROM scott.emp a
SELECT a.ename,nvl(a.comm+ 111, 10) FROM scott.emp a

LIKE 模糊查询
SELECT a.* FROM scott.emp a WHERE a.ename LIKE '%M%'
SELECT a.* FROM scott.emp a WHERE a.ename LIKE 'M%'
SELECT a.* FROM scott.emp a WHERE a.ename LIKE '_M%'

权限：

SELECT a.* from User_Role_Privs a;查看用户的角色
SELECT a.* from Dba_Sys_Privs a WHERE a.grantee='DBA';查看角色对应的权限
GRANT DBA TO scott;授权用户dba角色

视图：虚拟的表，所以dml也就是对实体表进行操作。我们一般创建只读视图

SELECT a.*,rowid from emp a;
创建视图
CREATE VIEW e_emp1 AS SELECT empno,ename,job FROM emp;
SELECT a.* from  e_emp1 a
INSERT INTO e_emp1 (empno,ename,job) VALUES('8888','sb','销售');
SELECT a.* from  emp a;
创建只读视图
CREATE VIEW e_emp2 AS SELECT empno,ename,job FROM emp with READ ONLY;

流程结构:

在SQL 命令窗口 执行 后面 +/ 再按enter
SET serverout ON;
DECLARE n NUMBER:=1;
        v Varchar2(20):='world';
BEGIN
  dbms_output.put_line('hello'||n||v);
END;

注意分号
SET serverout ON;
DECLARE emp_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO emp_count FROM emp WHERE sal>=3000;
  IF emp_count >0 THEN
    dbms_output.put_line('有'||emp_count||'个员工大于3000');
   ELSE
    dbms_output.put_line('没有员工大于3000');
   END IF;
END;

• 有多少个if 就有多少个end if

SET serverout ON;
DECLARE emp_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO emp_count FROM emp WHERE sal>=3000;
  IF emp_count =1 THEN
    dbms_output.put_line('有'||emp_count||'个员工等于3000');
  ELSE IF emp_count>1 THEN
    dbms_output.put_line('有'||emp_count||'个员工大于3000');
   ELSE
    dbms_output.put_line('没有员工大于3000');
   END IF;
   END IF;
END;

• CASE WHEN

SET serverout ON;
DECLARE emp_count NUMBER;
BEGIN
 SELECT COUNT(*) INTO emp_count FROM emp WHERE sal>=3000;
 CASE emp_count
   WHEN 0 THEN dbms_output.put_line('有'||emp_count||'个员工等于3000');
   WHEN 1 THEN dbms_output.put_line('有'||emp_count||'个员工等于3000');
   WHEN 2 THEN dbms_output.put_line('有'||emp_count||'个员工等于3000');
   WHEN 3 THEN dbms_output.put_line('有'||emp_count||'个员工等于3000');
 ELSE dbms_output.put_line('有'||emp_count||'个员工等于3000');
 END CASE;
END;

SELECT a.* from salgrade a;

• LOOP 循环：

SET serverout ON;
DECLARE g_id NUMBER:=2;
        g_losal NUMBER;
        g_hisal NUMBER;
BEGIN
  LOOP
        IF (g_id>4) THEN
          EXIT;
        END IF;
        
        SELECT losal,hisal INTO g_losal,g_hisal FROM salgrade WHERE grade=g_id;
        dbms_output.put_line('编号'||g_id||'的最低工资'||g_losal||',和最高工资'||g_hisal);
      
        g_id:=g_id+1;
  END LOOP;
END;

• WHILE：

SET serverout ON;
DECLARE g_id NUMBER:=2;
        g_losal NUMBER;
        g_hisal NUMBER;
BEGIN
  WHILE g_id<5 LOOP       
      SELECT losal,hisal INTO g_losal,g_hisal FROM salgrade WHERE grade=g_id;
      dbms_output.put_line('编号'||g_id||'的最低工资'||g_losal||',和最高工资'||g_hisal);
      g_id:=g_id+1;
  END LOOP;
END;

• FOR 循环

SET serverout ON;
DECLARE g_losal NUMBER;
        g_hisal NUMBER;
BEGIN
  for g_id IN 2..4 LOOP       
      SELECT losal,hisal INTO g_losal,g_hisal FROM salgrade WHERE grade=g_id;
      dbms_output.put_line('编号'||g_id||'的最低工资'||g_losal||',和最高工资'||g_hisal);
  END LOOP;
END;

SELECT a.* FROM emp a;

游标

SET serverout ON;
DECLARE CURSOR cu_emp IS SELECT empno,ename,sal FROM emp;
        c_no emp.empno%TYPE;
        c_name emp.ename%TYPE;
        c_sal emp.sal%TYPE;
BEGIN
  OPEN cu_emp;
  FETCH cu_emp INTO c_no,c_name,c_sal;
  WHILE cu_emp%FOUND LOOP
    dbms_output.put_line('编号'||c_no||','||c_name||','||c_sal);
    FETCH cu_emp INTO c_no,c_name,c_sal;
    END LOOP;
  CLOSE cu_emp;
END;

• 弱类型游标

SET serverout ON;
DECLARE TYPE customType IS REF CURSOR;
e_count NUMBER;
e emp%ROWTYPE;
s salgrade%ROWTYPE;
cType customType;
BEGIN
  SELECT COUNT(*) INTO e_count FROM emp WHERE job = 'clerk';
  IF e_count > 0 THEN
    OPEN cType FOR SELECT * FROM salgrade;
    FETCH cType INTO s;
    WHILE cType%FOUND LOOP
      dbms_output.put_line(s.grade||s.losal);
      FETCH cType INTO s;
    END LOOP;
    CLOSE cType;
   ELSE 
     OPEN cType FOR SELECT * FROM emp;
    FETCH cType INTO e;
    WHILE cType%FOUND LOOP
      dbms_output.put_line(e.empno||e.ename);
      FETCH cType INTO e;
    END LOOP;
    CLOSE cType;
   END IF;
END;

触发器：一般是用于权限控制

• 语句触发器

CREATE TRIGGER t_trigger
BEFORE INSERT 
ON t_book
BEGIN
  IF USER !='SCOTT' THEN
     raise_application_error(-20001,'权限不足');
  END IF;
END;

INSERT INTO t_book(ID,name,idtype) VALUES (4,'xx',1);

SELECT a.* from t_book a;


CREATE TRIGGER t_trigger2
BEFORE UPDATE OR DELETE 
ON t_book
BEGIN
  IF USER !='SCOTT1' THEN
     raise_application_error(-20001,'权限不足');
  END IF;
END;

UPDATE t_book SET NAME='ttyy',idtype=2  WHERE ID =4;

DELETE FROM t_book WHERE ID=4;



CREATE TRIGGER t_trigger3
AFTER UPDATE OR DELETE OR INSERT
ON t_book
BEGIN
  IF updating THEN
     INSERT INTO actionlog(actionUser,actionType,actionTime) VALUES(USER,'update',SYSDATE);
  ELSE IF inserting THEN
    INSERT INTO actionlog(actionUser,actionType,actionTime) VALUES(USER,'inert',SYSDATE);
  ELSE IF deleting THEN
    INSERT INTO actionlog(actionUser,actionType,actionTime) VALUES(USER,'delete',SYSDATE);
  END IF;
  END IF; 
  END IF;
END;

UPDATE t_book SET NAME='yy',idtype=2  WHERE ID =4;

DELETE FROM t_book WHERE ID=4;

SELECT a.* from actionlog a;


SELECT a.* from t_book a FOR UPDATE;
SELECT a.* from  t_type a FOR UPDATE;
SELECT a.* from actionlog a FOR UPDATE;

• 行触发器：针对每一行 两个内置对象 :OLD :NEW

CREATE TRIGGER t_trigger4
AFTER INSERT
ON t_book
FOR EACH ROW
BEGIN
  UPDATE t_type SET NUM = NUM+1 WHERE ID = :now.idtype;
END;

INSERT into t_book (ID,NAME,Idtype)VALUES(5,'xx',1);


CREATE TRIGGER t_trigger5
AFTER DELETE
ON t_book
FOR EACH ROW
BEGIN
  UPDATE t_type SET NUM = NUM-1 WHERE ID = :old.idtype;
END;

DELETE from t_book WHERE ID = 5;

自定义函数：要有返回值

CREATE FUNCTION getCountBook RETURN NUMBER AS
BEGIN
  DECLARE countNum NUMBER;
  BEGIN
    SELECT COUNT(*) INTO countNum FROM t_book;
    RETURN countNum;
  END;
END getCountBook;

调用：
SET serverout ON;
BEGIN
  dbms_output.put_line('数量：'|| getCountBook());
END;

SELECT getCountBook() 数量 from dual;

• 带参数函数：

CREATE FUNCTION getTableCount(tableName VARCHAR2) RETURN NUMBER AS
BEGIN
  DECLARE countNum NUMBER;
          querySql VARCHAR2(200);
  BEGIN
    querySql:='select count(*) from '||tableName;
    EXECUTE IMMEDIATE querySql INTO countNum;
    RETURN countNum;
  END;
END getTableCount;

SELECT getTableCount('emp') 数量 from dual;

存储过程：项目怎么用？

CREATE PROCEDURE proInsertBook(bname IN VARCHAR2,bidtype IN NUMBER) AS
BEGIN
  DECLARE maxId NUMBER;
  BEGIN
    SELECT MAX(ID) INTO maxId FROM t_book;
    INSERT into t_book(ID,NAME,idtype)VALUES(maxId+1,bname,bidtype);
    COMMIT;
  END;
END proInsertBook;


CREATE PROCEDURE proInsertBook2(bname IN VARCHAR2,bidtype IN NUMBER) AS
BEGIN
  DECLARE maxId NUMBER;
          bookNum NUMBER;
  BEGIN
    SELECT COUNT(*) INTO bookNum FROM t_book WHERE NAME=bname;
    IF bookNum>0 THEN
      RETURN;
    END IF;
    SELECT MAX(ID) INTO maxId FROM t_book;
    INSERT into t_book(ID,NAME,idtype)VALUES(maxId+1,bname,bidtype);
    COMMIT;
  END;
END proInsertBook2;


CREATE PROCEDURE proInsertBook3(bname IN VARCHAR2,bidtype IN NUMBER,n1 OUT NUMBER,n2 OUT NUMBER) AS
BEGIN
  DECLARE maxId NUMBER;
          bookNum NUMBER;
  BEGIN
    SELECT COUNT(*) INTO n1 FROM t_book;
    SELECT COUNT(*) INTO bookNum FROM t_book WHERE NAME=bname;
    IF bookNum>0 THEN
      RETURN;
    END IF;
    SELECT MAX(ID) INTO maxId FROM t_book;
    INSERT into t_book(ID,NAME,idtype)VALUES(maxId+1,bname,bidtype);
    SELECT COUNT(*) INTO n2 FROM t_book;
    COMMIT;
  END;
END proInsertBook3;


调用：
在SQL窗口：
CALL proInsertBook2('小心翼55',2);

begin
proInsertBook('小心翼翼3',2);
end;

在命令窗口：
exec proInsertBook('小心翼翼',2);
EXECUTE proInsertBook('小心翼翼',2);
CALL proInsertBook('小心翼翼',2);

SELECT a.* from t_book a;


DECLARE n1 NUMBER;
        n2 NUMBER;
BEGIN
  proInsertBook3('5454',2,n1,n2);
  dbms_output.put_line('n1='||n1);
END;


CALL proInsertBook3('32321',2,n1,n2);

用户、权限、角色：

概念
关系
常用查询

导出表结构和表数据

• 表结构

  
  方式一
  方式二

• 表数据

  
  方式一
  方式二
  方式三

导出方案

导出方案

导出数据库

导出数据库

导入

导入