<input id="0qass"><u id="0qass"></u></input>
  • <input id="0qass"><u id="0qass"></u></input>
  • <menu id="0qass"><u id="0qass"></u></menu>

    oraclecode常用語句大全

    --查詢當前數據庫名稱 10

    v$database 10

    --查詢所有表空間的名稱 10

    dba_tablespaces 10

    --創建永久表空間 10

    create tablespace 10

    datafile 10

    size 10

    autoextend on 10

    next 10

    maxsize 10

    --創建臨時表空間 10

    create temporary tablespace 10

    --刪除表空間 10

    drop tablespace 10

    including contents and datafiles 11

    drop tablespace 11

    --查詢所有用戶的名稱 11

    dba_users 11

    --創建用戶 11

    create user 11

    identified by 11

    default tablespace 11

    quota 11

    temporary tablespace 11

    --更改用戶密碼 11

    alter user ?identified by 11

    alter user 11

    --刪除用戶 11

    drop user ?cascade 11

    drop user 11

    --查詢用戶的系統權限 11

    user_sys_privs 11

    dba_sys_privs 11

    --查詢用戶的對象權限 12

    user_tab_privs 12

    dba_tab_privs 12

    --查詢角色 12

    dba_roles 12

    --創建角色 12

    create role 12

    --為角色授權 12

    grant to 12

    grant 12

    --為用戶授權 13

    --查詢用戶(角色)與角色的關系 13

    dba_role_privs 13

    --查詢角色與權限的關系 13

    --刪除角色 13

    drop role 13

    --創建學生信息表 13

    create table 13

    --添加主鍵約束確保字段值不能為空且不能重復 13

    primary key 13

    --添加非空屬性確保字段不能為空 14

    not null 14

    --添加檢查約束確保字段只能填寫指定數據 14

    check 14

    unique 14

    --添加默認值 14

    default 14

    --刪除表的列、添加表的列

    ?

    --創建課程信息表 14

    --創建學生成績表 15

    --添加外鍵約束 15

    foreign key 15

    --組合主鍵 16

    --查詢當前用戶的可用數據表 16

    --查詢表所有信息 16

    select 16

    --僅查詢多字段信息 16

    --帶時字段帶表達式 16

    --簡單的數學運算 17

    dual 17

    --獲得當前日期時間 17

    sysdate 17

    systimestamp 17

    current_date 17

    current_timestamp 17

    --使用字段別名 17

    as 17

    --查詢員工姓名和年收入 17

    nvl(comm,0) 17

    --字符串連接 18

    || 18

    --查詢時消除冗余 18

    distinct 18

    --同時查詢部門編號與崗位 18

    --帶條件的查詢 18

    where 18

    ename='CLARK 18

    sal>1500 18

    sal>1500 18

    ename>'FORD' 19

    where sal>=800 and sal<=1500 19

    where sal between and 19

    is null 19

    is not null 19

    where sal in (800,1100,1500) 19

    -- 模糊查詢 20

    like?%A%’ 20

    __A% 20

    order by asc 20

    desc 20

    avg(sal) 21

    group by 21

    max(sal) 21

    --交叉連接 22

    cross join 22

    --內連接 23

    inner join 23

    --三個表的嵌套查詢 23

    --求各個部門薪水最高的員工信息 25

    偽列 25

    rownum 25

    --取出第十條記錄之后的數據 25

    --按薪水由高至低排列,找出第6-10名的員工信息 26

    --保存數據 26

    insert into 26

    values 26

    --同時插入多條記錄 26

    union all 26

    --更新數據 27

    update set 27

    update 27

    rollback; 27

    --刪除數據 27

    delete from 27

    commit; 27

    --數據控制語言 27

    grant 27

    revoke 27

    --各種函數 27

    abs 28

    ceil 28

    floor 28

    trunc 28

    round 28

    dbms_random.value 28

    concat 28

    initcap 28

    upper 28

    lower 28

    instr 28

    length 28

    rpad 29

    lpad 29

    rtrim 29

    ltrim 29

    substr 29

    replace 29

    reverse 29

    to_char 29

    L9999999.9999999 29

    --字符變數字 29

    to_number 29

    --字符變日期,數字變日期 30

    to_date 30

    add_months 30

    sysdate+1 30

    count 30

    --行最大值,行最小值 30

    greatest 30

    least 31

    分析函數?decode 31

    嵌套查詢 31

    --行級鎖 31

    --表級鎖 31

    in share mode 31

    in exclusive mode 31

    lock 31

    --私有同義詞 31

    synonym for 31

    drop synonym 32

    --公有同義詞 32

    public synonym 32

    --序列 32

    sequence 32

    start with 32

    .nextval 32

    .currval 32

    --視圖 32

    create view 32

    --復制表 32

    --索引 33

    create index on 33

    create index 33

    create unique index 33

    重建索引 33

    刪除索引 33

    組合索引 33

    --PL/SQL 33

    主體 33

    begin 33

    end; 34

    --聲明 34

    declare 34

    x number; 34

    變量賦值 34

    x := 1000; 34

    系統輸出 34

    dbms_output.put_line 34

    使用某表某字段的類型 34

    sal%type 34

    if語句 35

    if 35

    end if; 35

    if elsif 35

    return 36

    case 36

    when then 36

    when 36

    end case; 36

    loop 37

    loop 37

    end loop; 38

    exit when 38

    while 38

    for 39

    for cnt in 1..1000 loop 39

    異常處理 39

    exception 39

    no_data_found 39

    too_many_rows 40

    自定義異常?拋出異常 40

    raise 40

    raise_application_error 40

    raise 40

    raise_application_error 41

    隱式游標 41

    sql%found 41

    sql%rowcount 41

    顯式游標 42

    cursor is 42

    cur%notfound 42

    cursor 42

    open 42

    fetch 42

    close 42

    取余mod 42

    變量聲明同時賦值 43

    當前游標current of 43

    procedure 43

    as 43

    帶參數的過程 44

    帶返回值得過程 44

    --根據員工編號求取該員工的姓名和薪水 45

    function 46

    return number 46

    --根據部門編號獲取該部門的薪水總和 47

    --觸發器 47

    --語句級觸發器 47

    trigger 47

    after 48

    on 48

    行級觸發器 49

    for each row 49

    :new.a 49

    :old.a 49


    --查詢當前數據庫名稱

    v$database;

    select?name?from?v$database;

    ?

    --查詢所有表空間的名稱

    dba_tablespaces

    select?tablespace_name from?dba_tablespaces;

    ?

    --創建永久表空間

    create tablespace?MYTABLESPACE

    datafile?'D:\oracle_lesson\oradata\orcl\MYTABLESPACE.DBF'

    size?100M

    autoextend on??next?50M

    maxsize?500M;

    ?

    --創建臨時表空間

    create temporary tablespace?MYTEMP

    tempfile?'D:\oracle_lesson\oradata\orcl\MYTEMP.DBF'

    size?50M

    autoextend?on

    next?10M

    maxsize?200M;

    ?

    --刪除表空間

    drop tablespace

    including contents and datafiles

    drop tablespace?mytablespace including?contents?and?datafiles;

    drop?tablespace?mytemp including contents and datafiles;

    ?

    --查詢所有用戶的名稱

    dba_users

    select?username from?dba_users;

    ?

    --創建用戶[注意:密碼不用數字也不要和用戶名一樣]

    create user?tom

    identified by?orcl

    default tablespace?mytablespace

    quota?20M on?mytablespace

    temporary tablespace?mytemp;

    ?

    --更改用戶密碼

    alter user ?identified by

    alter user?tom identified by?orcl;

    ?

    --刪除用戶

    drop user ?cascade

    drop user?tom cascade;

    ?

    --查詢用戶的系統權限

    user_sys_privs

    dba_sys_privs

    select?* from?user_sys_privs;

    select?* from?dba_sys_privs;

    ?

    --查詢用戶的對象權限

    user_tab_privs

    dba_tab_privs

    select?* from?user_tab_privs;

    select?* from?dba_tab_privs;

    ?

    --查詢角色

    dba_roles

    select?role?from?dba_roles;

    ?

    --創建角色

    create role?student;

    create?role?teacher;

    create?role?director;

    ?

    --為角色授權

    grant to

    grant?select?any?table?to?student;

    ?

    grant?student to?teacher;

    grant?insert?any?table?to?teacher;

    ?

    grant?teacher to?director;

    grant?update?any?table?to?director;

    grant?delete?any?table?to?director;

    ?

    --為用戶授權

    grant?director to?tom;

    grant?connect?to?tom;

    ?

    --查詢用戶(角色)與角色的關系

    dba_role_privs

    select?* from?dba_role_privs;

    ?

    --查詢角色與權限的關系

    select?* from?dba_tab_privs;

    select?* from?dba_sys_privs;

    ?

    --刪除角色

    drop role?director;

    --創建學生信息表

    create table?student

    (

    ??id???number(4),

    ??name?nvarchar2(4),

    ??gender ????char(2),

    ??birthday ??date,

    ??address ???varchar2(100)

    );

    ?

    --刪除表的列

    alter?table?表名稱 ?Drop column 列名稱

    --添加表的列

    alter?table?表名稱 ?add column 列名稱

    ?

    --添加主鍵約束確保字段值不能為空且不能重復

    primary key

    alter?table?student

    add?constraint?pk_student_id primary key?(id);

    ?

    --添加非空屬性確保字段不能為空

    not null

    alter?table?student

    modify?name?not null;

    ?

    --添加檢查約束確保字段只能填寫指定數據

    check

    alter?table?student

    add?constraint?ck_student_gender check?(gender=''?or?gender='');

    ?

    --在假設生日不能重復的前提下--添加唯一約束確保字段僅僅不能重復

    unique

    alter?table?student

    add?constraint?uq_student_birthday unique?(birthday);

    ?

    --添加默認值屬性確保在不填寫的情況下自動填寫的數據

    default

    alter?table?student

    modify?address default?('北京八維研修學院宿舍');

    ?

    ?

    --創建課程信息表

    create?table?course

    (

    ??id???number(2),

    ??title ?????varchar2(20),

    ??period ????number(2)

    );

    ?

    alter?table?course

    add?constraint?pk_course_id primary?key?(id);

    ?

    alter?table?course

    modify?title not?null;

    ?

    alter?table?course

    add?constraint?ck_course_period check(period>=40?and?period<=80);

    ?

    ?

    --創建學生成績表

    create?table?grade

    (

    ??sid ?number(4),

    ??cid ?number(2),

    ??mark number(3)

    );

    ?

    --添加外鍵約束確保所用數據來源于其它表中的數據

    foreign key

    alter?table?grade

    add?constraint?fk_grade_sid foreign key?(sid) references?student(id);

    ?

    alter?table?grade

    add?constraint?fk_grade_cid foreign?key?(cid) references?course(id);

    ?

    --組合主鍵

    alter?table?grade

    add?constraint?pk_grade_sid_cid primary?key?(sid,cid);

    ?

    alter?table?grade

    add?constraint?ck_grade_mark check(mark>=0?and?mark<=100);

    ?

    --查詢當前用戶的可用數據表

    select?table_name from?user_tables;

    ?

    --查詢表所有信息

    select?* from?emp;

    ?

    select?* from?dept;

    ?

    select?* from?salgrade;

    ?

    --僅查詢多字段信息

    select?empno,ename,deptno from?emp;

    ?

    --帶時字段帶表達式?

    select?ename,12*sal from?emp;

    ?

    --簡單的數學運算

    dual

    select?200+300,12*3?from?dual;

    ?

    --獲得當前日期時間

    sysdate

    systimestamp

    current_date

    current_timestamp

    select?sysdate?from?dual;

    select?systimestamp?from?dual;

    select?current_date?from?dual;

    select?current_timestamp?from?dual;

    ?

    --使用字段別名

    as

    select?ename as?"姓名",12*sal as?"年薪" from?emp;

    select?ename "姓名",12*sal "年薪" from?emp;

    ?

    --查詢員工姓名和補貼

    select?ename,comm from?emp;

    ?

    --查詢員工姓名和年收入

    Select decode(comm,500,A,300,B,C) from emp ;

    nvl(comm,0)

    select?ename "姓名",12*(sal+nvl(comm,0)) "年收入" from?emp;

    ?

    --字符串連接

    ||

    select?'abc'||'def'||'gh'?"con" from?dual;

    select?'Mr.'||ename from?emp;

    ?

    --查詢時消除冗余

    distinct?

    select?distinct?deptno from?emp;

    ?

    --同時查詢部門編號與崗位

    select?distinct?deptno,job from?emp;

    ?

    --帶條件的查詢?

    where

    ename='CLARK

    sal>1500

    sal>1500;

    select?* from?emp where?deptno=10;

    ?

    --查詢部門編號不是10的員工信息

    select?* from?emp where?deptno!=10;

    select?* from?emp where?deptno<>10;

    ?

    --查詢CLARK”員工的信息

    select?* from?emp where?ename='CLARK';

    ?

    --查詢薪水多于1500元的員工信息

    select?* from?emp where?sal>1500;

    ?

    --查詢排在FORD”之后的員工信息

    ename>'FORD'

    select?* from?emp where?ename>'FORD';

    ?

    --查詢薪水在8001500元之間的員工信息

    where sal>=800 and sal<=1500

    where sal between and

    select?* from?emp where sal>=800 and sal<=1500;

    select?* from?emp where sal between?800?and?1500;

    ?

    --查詢沒有津貼的員工信息

    is null

    is not null

    select?* from?emp where?comm is null;

    ?

    --查詢有津貼的員工信息

    select?* from?emp where?comm is not null;

    ?

    --查詢薪資指定的員工信息

    where sal in (800,1100,1500)

    select?* from?emp where?sal=800?or?sal=1100?or?sal=1500;

    select?* from?emp where sal in (800,1100,1500);

    ?

    --查詢在1981123日之后入職的員工信息

    select?* from?emp where?hiredate > '3-12-1981';

    ?

    --查詢在10號部門工作并且薪水多于1000元的員工信息

    select?* from?emp where?deptno=10?and?sal>1000;

    ?

    --查詢在10號部門工作或者薪水多于1000元的員工信息

    select?* from?emp where?deptno=10?or?sal>1000;

    ?

    --查詢在10號部門工作但薪水只有13005000元的員工信息

    select?* from?emp where?deptno=10?and?(sal=1300?or?sal=5000);

    select?* from?emp where?deptno=10?and?sal in(1300,5000);

    -- 模糊查詢

    like?%A%’

    __A%

    --查詢姓名中包含A”字符串的員工信息(模糊)

    select?* from?emp where?ename like?'%A%';

    select?* from?emp where?ename like?'%A';

    select?* from?emp where?ename like?'A%';

    select?* from?emp where?ename like?'A____';

    select?* from?emp where?ename like?'__A%';

    select?* from?emp where?ename like?'%A__';

    ?

    --按員工編號升序排列顯示員工信息

    order by asc

    select?* from?emp order by?empno asc;

    ?

    --按員工編號降序排列顯示員工信息

    select?* from?emp order?by?empno desc;

    ?

    --按部門編號升序且姓名降序的方式排列員工信息

    desc

    select?* from?emp order?by?deptno asc,ename desc;

    ?

    --按員工編號升序排列不在10號部門工作的員工信息

    select?*

    from?emp

    where?deptno!=10?

    order?by?empno desc;

    ?

    --查詢姓名第二個字母不是A”且薪水大于800元的員工信息,按年薪降序排列

    select?emp.*,12*sal a

    from?emp

    where?ename not?like?'_A%'?and?sal>800

    order?by?a desc;

    ?

    --求每個部門的平均薪水

    avg(sal)

    group by

    select?avg(sal)?from?emp;

    select?deptno from?emp group?by?deptno;

    select?deptno,avg(sal) from?emp group by?deptno;

    ?

    --求各個部門的最高薪水

    max(sal)

    select?max(sal)?from?emp;

    select?deptno,max(sal) from?emp group?by?deptno;

    ?

    select?min(sal) from?emp;

    select?sum(sal) from?emp;

    ?

    --求每個部門每個崗位的最高薪水

    select?* from?emp;

    select?job,deptno from?emp group?by?job,deptno;

    select?job,deptno,max(sal) from?emp group?by?job,deptno;

    ?

    --求平均薪水大于2000的部門編號

    select?deptno,avg(sal)

    from?emp

    group?by?deptno

    having?avg(sal)>2000;

    ?

    --將員工薪水大于1200且部門平均薪水大于2000的部門編號列出來,

    --按部門平均薪水降序排列

    select?deptno,avg(sal) av

    from?emp

    where?sal>1200

    group?by?deptno

    having?avg(sal)>2000

    order?by?av desc;

    ?

    --求最高薪水的員工信息

    select?max(sal) from?emp;

    select?* from?emp where?sal=5000;

    select?* from?emp where?sal=(select?max(sal) from?emp);

    ?

    --求多于平均薪水的員工信息

    select?avg(sal) from?emp;

    select?* from?emp where?sal>(select?avg(sal) from?emp);

    ?

    --交叉連接

    cross join

    select?* from?emp;

    select?* from?dept;

    select?* from?emp,dept;

    select?* from?emp cross join?dept;

    --內連接

    inner join?on

    --查詢員工姓名及所在部門名稱

    select?ename,dname from?emp,dept where?emp.deptno=dept.deptno;

    select?ename,dname

    from?emp inner join?dept on?emp.deptno=dept.deptno;

    ?

    --求每個員工及他的經理姓名

    select?* from?emp;

    ?

    select?employee.ename as?"員工",manager.ename as?"經理"

    from?emp employee cross?join?emp manager

    where?employee.mgr=manager.empno;

    ?

    select?employee.ename as?"員工",manager.ename as?"經理"

    from?emp employee inner?join?emp manager

    on?employee.mgr=manager.empno;

    ?

    --查詢員工姓名及其薪水等級

    select?* from?emp;

    select?* from?salgrade;

    select?* from?emp cross?join?salgrade;

    select?ename as?"員工姓名",grade as?"薪水等級"

    from?emp inner?join?salgrade

    on?sal between?losal and?hisal;

    ?

    --三個表的嵌套查詢

    --輸出非辦事員的員工姓名,所在部門名稱及薪水等級

    select?* from?emp;

    select?* from?dept;

    select?* from?salgrade;

    ?

    select?* from?emp,dept,salgrade;

    ?

    select?*

    from?emp cross?join?dept

    ?????????cross?join?salgrade;

    ?

    select?ename,dname,grade

    from?emp cross?join?dept

    ?????????cross?join?salgrade

    where?emp.deptno=dept.deptno

    ??????and?emp.sal between?losal and?hisal

    ??????and?job!='CLERK';

    ?

    select?ename,dname,grade

    from?emp inner?join?dept on?emp.deptno=dept.deptno

    ?????????inner?join?salgrade on?emp.sal between?losal and?hisal

    where?job!='CLERK';

    ?

    --輸出第二個字母不是A”員工姓名,所在部門名稱及薪水等級

    select?ename,dname,grade

    from?emp inner?join?dept on?emp.deptno=dept.deptno

    ?????????inner?join?salgrade on?emp.sal between?losal and?hisal

    where?ename not?like?'_A%';

    ?

    --求各個部門薪水最高的員工信息

    select?deptno,max(sal) from?emp group?by?deptno;

    select?* from?emp;

    ?

    select?ename,deptno,sal

    from?emp inner?join?(select?deptno d,max(sal) m from?emp group?by?deptno) e

    ?????????on?emp.sal=e.m and?emp.deptno=e.d

    order?by?deptno asc;

    ?

    --求取所有部門的員工姓名

    select?* from?dept;

    select?* from?emp;

    select?* from?dept cross?join?emp;

    select?dept.deptno,dname,ename from?dept inner?join?emp on?dept.deptno=emp.deptno;

    select?dept.deptno,dname,ename from?dept left?join?emp on?dept.deptno=emp.deptno;

    ?

    偽列

    --輸出記錄的序號

    rownum

    select?rownum,emp.* from?emp;

    ?

    --輸出前五條記錄

    select?rownum,emp.* from?emp where?rownum<=5;

    ?

    --取出第十條記錄之后的數據

    select?* from?(

    ????select?rownum?r,emp.* from?emp)

    where?r>10;

    ?

    --按薪水由高至低排列,找出第6-10名的員工信息

    select?* from

    ??(select?rownum?r,e.* from?

    ??????(select?*

    ???????from?emp

    ???????order?by?sal desc) e) e2

    where?r>=6?and?r<=10; ???

    ?

    ?

    --保存數據

    insert into?student(id,name,gender,birthday,address)

    values(1000,'李四','','12-11-1990',default);

    ?

    insert?into?student(id,name,gender,birthday,address)

    values(1001,'王五','',sysdate,'北京市東城區小燕胡同');

    ?

    --同時插入多條記錄

    insert?into?student(id,name,gender,birthday,address)

    select?1002,'趙六','','18-2-1993','北京市東城區小燕胡同'?from?dual。

    union?all

    select?1003,'張三','','20-8-1989','北京市海淀區小燕胡同'?from?dual;

    ?

    ?

    select?* from?student;

    ?

    --更新數據

    update set

    update?student set?birthday='26-6-1992'?where?id=1001;

    ?

    update?student

    set?birthday='18-10-1991',address='北京市朝陽區光明里小區'?

    where?id=1003;

    rollback;

    ?

    --刪除數據

    delete from?student where?id=1003;

    commit;

    ?

    --數據控制語言

    grant?select?on?student to?tom;

    grant?insert?on?student to?tom;

    revoke?select?on?student from?tom;

    ?

    --各種函數

    --絕對值,向上取整,向下取整,取若干位小數不四舍五入,取若干位小數四舍五入,取特定范圍內隨機數,

    --字符串鏈接,變首字母為大寫,全部大寫,全部小寫,從字符串第若干位查詢第若干次出現的字符串的索引值

    --字符串長度,為字符串增長并補特定符號(左右),使用特定字符截取字符,從特定字符截取特定長字符,

    --替換字符,字符反轉,

    abs

    ceil

    floor

    trunc

    round

    dbms_random.value

    select?abs(-25) from?dual;

    select?ceil(-35.68) from?dual;

    select?floor(-35.68) from?dual;

    select?trunc(12.345678,2) from?dual;

    select?round(12.345678,2) from?dual;

    select?dbms_random.value(10,20) from?dual;--包括10,不包括20

    concat

    initcap

    upper

    lower

    select?concat('app','le') word from?dual;

    select?concat('Mr.',ename) ename from?emp;

    select?initcap(ename) from?emp;

    select?upper(lower(ename)) from?emp;

    instr

    length

    select?instr('aabbaaccaaddaaeeaaff','aa',5,3) from?dual;

    select?ename,length(ename) from?emp;

    rpad

    lpad

    rtrim

    ltrim

    substr

    replace

    reverse

    select?rpad('abc',8,'*') from?dual;

    select?lpad('abc',8,'*') from?dual;

    select?rtrim('aabbccbbaa','ab') from?dual;

    select?ltrim('aabbccbbaa','ab') from?dual;

    select?substr('abcdefg',3,4) from?dual;

    select?replace('aabbaacc','aa','X') from?dual;

    select?reverse('12345') from?dual;

    ?

    --數字變字符,數字變人民幣字符,日期變字符

    to_char

    L9999999.9999999

    select?123,to_char(123) from?dual;

    select?to_char(1000.12345,'L9999999.9999999') from?dual;

    select?to_char(1000.12345,'L0000000.0000000') from?dual;

    select?to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from?dual;

    select?to_char(sysdate,'yyyy""mm""dd"" hh24:mi:ss') from?dual;

    ?

    --字符變數字

    to_number

    select?to_number('1200') from?dual;

    select?'1200'+'1300'?from?dual;

    ?

    --字符變日期,數字變日期

    to_date

    select?to_date('2002-12-20','yyyy-mm-dd') from?dual;

    select?to_date(20021220,'yyyymmdd') from?dual;

    ?

    --加月份,減月份,加天,加小時,加分鐘,算月時間差

    add_months

    sysdate+1

    select?sysdate,add_months(sysdate,2) from?dual;

    select?sysdate,add_months(sysdate,-12) from?dual;

    select?sysdate,sysdate+1?from?dual;

    select?sysdate,sysdate+1/24/60?from?dual;

    select?months_between(to_date('2014-2-5','yyyy-mm-dd'),

    ??????????????????????to_date('2013-12-11','yyyy-mm-dd')) from?dual;

    ?

    --統計個數

    count

    select?ename,comm from?emp;

    select?count(comm) from?emp;

    ?

    ?

    select?to_char(round(avg(sal),2),'L9999.99') from?emp;

    ?

    --行最大值,行最小值

    greatest

    least

    select?greatest(1,2,3,4,5) from?dual;

    select?least(1,2,3,4,5) from?dual;

    ?

    select?user?from?dual;

    ?

    分析函數?decode

    Select decode(floor(mark/10),10,優秀,9,?優秀,8,良好,7,中等,6,及格,不及格);

    嵌套查詢

    Select * from?emp where?sal>(select avg(sal) from emp);

    ?

    --行級鎖

    此四種操作自動加行級鎖:

    insert

    delete

    update

    select * from emp where for update;

    ?

    --表級鎖

    in share mode

    in exclusive mode

    lock?table?emp in share mode 共享鎖;

    lock?table?emp in exclusive mode 排它鎖;

    ?

    --序列

    sequence?

    start with

    .nextval

    .currval

    create?sequence?s0 start with?1000;

    select?s0.nextval?from?dual;

    create sequence sq_t03 start with 100 increment by?10;

    select sq|_to1.currval?from dual;

    ?

    --視圖

    create view?v0

    as

    select?* from(

    select?rownum?r,e.* from(

    select?* from?emp order?by?sal desc)e)

    where?r>=6?and?r<=10;

    ?

    select?* from?v0;

    ?

    --復制表

    create?table?emp0

    as

    select?* from?emp;

    ?

    select?* from?emp0 where?empno=7900;

    select?* from?emp0 where?ename='SMITH';

    select?* from?emp0 where?empno>7000?and?ename<'ZOO'

    --索引

    create index on

    create index?i0 on?emp0 (empno);

    create unique index?i1 on?emp0(ename);

    create?index?i2 on?emp0 (empno,ename);

    重建索引

    Alter index <索引名> rebuild;

    刪除索引

    Drop index <索引名>;

    組合索引

    Select * from emp0 where empno>7000 and ename<’ral’;

    Create index ins2 on emp0 (empno,ename);

    ?

    ?

    • 1
      點贊
    • 0
      評論
    • 2
      收藏
    • 打賞
      打賞
    • 掃一掃,分享海報

    ??2022 CSDN 皮膚主題:游動-白 設計師:我叫白小胖 返回首頁

    打賞作者

    陽光小禹

    你的鼓勵將是我創作的最大動力

    ¥2 ¥4 ¥6 ¥10 ¥20
    輸入1-500的整數
    余額支付 (余額:-- )
    掃碼支付
    掃碼支付:¥2
    獲取中
    掃碼支付

    您的余額不足,請更換掃碼支付或充值

    打賞作者

    實付
    使用余額支付
    點擊重新獲取
    掃碼支付
    錢包余額 0

    抵扣說明:

    1.余額是錢包充值的虛擬貨幣,按照1:1的比例進行支付金額的抵扣。
    2.余額無法直接購買下載,可以購買VIP、C幣套餐、付費專欄及課程。

    余額充值
    多乐彩