Oracle 学习笔记
1. ||表示连接2个字段2. NVL (start_date, '01-JAN-95')<span style="font-size: small;">把空值用后面的代替
3. DESC <span style="font-size: small;">或者 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<span style="font-size: small;">；
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
<span />
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
n]
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]...);
页:
[1]