Oracle 学习笔记

1.       ||表示连接2个字段

2.       NVL (start_date, '01-JAN-95')把空值用后面的代替

3.       DESC 或者 describe 显示表的结构

4.       Search conditions can contain either literal characters or numbers.

a)         % denotes zero or many characters

b)        _ denotes one character

                               SELECT ename FROM emp

                               WHERE ename LIKE ‘S%’;

5. SELECT ename, deptno, sal FROM emp

                               ORDER BY deptno, sal DESC;

 

6. Character functions:

•          Case conversion functions

–         LOWER( column | expr )

–         UPPER( column | expr )

–         INITCAP( column | expr ) – for first letter in each word

•          Character manipulation functions

–         CONCAT ( col1, col2 ) – same as ‘||’

–         SUBSTR ( column | expr, m [,n])

–         LENGTH ( column | expr )

–         INST( column | expr, c ) – return numeric position of character c

–         LPAD( column | expr, n, c ) – pads character c right justified to total width of n

–          

7. ROUND(column|expression,n) – rounds value to specified decimal

                               ROUND(45.926, 2) – 45.93

•          TRUNC(column|expression,n) – truncates value to specified decimal

                               TRUNC(45.926, 2) – 45.92

•          MOD(m,n) – returns remainder of division

                               MOD(1600, 300) – 100

 

8．Decode函数的语法结构如下: decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default) decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

 

8. Outer Joins

SELECT                 table1.column, table2.column

FROM                         table1, table2

WHERE                 table1.column(+) = table2.column;

 

9. COUNT(expr) returns the number of non-null rows；

 

10. <ANY means less than the maximum

•         >ANY means more than the minimum

•         =ANY is equivalent to IN

11. Single ampersand (&)

–     Double ampersand (&&)

–     DEFINE and ACCEPT commands

 

SQL> SELECT            empno, ename, sal, deptno

  2  FROM                emp

  3  WHERE             deptno = &department_number;

Enter value for department_number: 30

 

 

SQL> SET VERIFY ON

SQL> SELECT            empno, ename, sal, deptno

  2  FROM                emp

  3  WHERE             deptno = &department_number;

 

Enter value for department_number: 30

 

old   3: WHERE deptno = &department_number

new   3: WHERE deptno = 30

 

12. Use the double ampersand (&&) if you want to reuse the variable value without prompting the user each time.

SQL> SELECT            empno, ename, sal, &&column_name

  2  FROM                emp

  3  ORDER BY    &column_name;

 

Enter value for column_name: deptno

 

SQL> SELECT            empno, ename, sal, deptno

  2  FROM                emp

  3  WHERE             job = '&job_title';

 

Enter value for job_title: CLERK

 

 

13. Data Manipulation and
Transaction Control Commands

 

  

 

 

 

 

14. SQL> INSERT INTO managers (id, name, salary,

  2  hiredate)

  3  SELECT             empno, ename, sal,

  4                                                 hiredate

  5  FROM                emp

  6  WHERE             job = ‘MANAGER’;

3 rows created.

 

CONCAT(‘SQL’, ‘Course’) SUBSTR(‘String’,1,3) LENGTH(‘String’) INSTR(‘String’, ‘r’) LPAD(sal,10,’*’)

SQLCourse Str 6 3 ******5000

 

 

SQL> CREATE TABLE s_emp
  2  (id                      NUMBER(7)
  3     CONSTRAINT s_emp_id_pk PRIMARY KEY,
  4   last_name     VARCHAR2(25)
  5     CONSTRAINT s_emp_last_name_nn NOT NULL,

  6   first_name    VARCHAR2(25),
  7   userid           VARCHAR2(8)
  8     CONSTRAINT s_emp_userid_nn NOT NULL
  9     CONSTRAINT s_emp_userid_uk UNIQUE,
 10   start_date     DATE   DEFAULT SYSDATE,
 11   comments     VARCHAR2(25),
 12   manager_id  NUMBER(7),
 13   title              VARCHAR2(25),
 14   dept_id         NUMBER(7)
 15     CONSTRAINT s_emp_dept_id_fk REFERENCES
 16       s_dept (id),
 17   salary           NUMBER(11,2),
 18   commission_pct NUMBER(4,2)
 19     CONSTRAINT s_emp_commission_pct_ck CHECK
 20       (commission_pct IN(10,12.5,15,17.5,20)));

 

 

select                      constraint_name,                                                                            constraint_type,                                                                                              search_condition

                                   from       user_constraints

                                  where table_name = ‘EMP’;

 

 

SQL> CREATE OR REPLACE VIEW empvu10

  2    (id_number, employee, title)

  3  AS SELECT   empno, ename, job

  4  FROM           emp

  5  WHERE         deptno = 10;

View created.

 

SQL> CREATE VIEW dept_sum_vu

  2    (name, minsal, maxsal, avgsal)

  3  AS SELECT   d.dname, MIN(e.sal),

  4    MAX(e.sal), AVG(e.sal)

  5  FROM           emp e, dept d

  6  WHERE         e.deptno = d.deptno

  7  GROUP BY   d.dname;

View created.

 

CREATE SEQUENCE name

                               [INCREMENT BY n]

                               [START WITH n]

                               [{MAXVALUE n | NOMAXVALUE}]

                               [{MINVALUE n | NOMINVALUE}]

                               [{CYCLE | NOCYCLE}]

                               [{CACHE n | NOCACHE}]

 

SQL> INSERT INTO   dept(deptno, dname,                                      loc)

  2  VALUES             (dept_deptno.NEXTVAL,

  3                            ‘MARKETING', ‘SAN DIEGO’);

1 row created.

 

CREATE INDEX index

ON table (column[, column]...);