美文网首页
常用知识点

常用知识点

作者: 林ze宏 | 来源:发表于2018-05-09 14:33 被阅读0次

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

    创建表空间: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);
    

    用户、权限、角色:

    概念
    关系
    常用查询

    导出表结构和表数据

    • 表结构


      方式一
      方式二
    • 表数据


      方式一
      方式二
      方式三

    导出方案

    导出方案

    导出数据库

    导出数据库

    导入

    导入

    相关文章

      网友评论

          本文标题:常用知识点

          本文链接:https://www.haomeiwen.com/subject/fkinrftx.html