课程第10次（2016-04-18星期一）：WHERE，SORT和单行函数

botang · 发表于 2016-4-19 08:55:38

课程第10次（2016-04-18星期一）：WHERE，SORT和单行函数
【上完1Z0-051的第2章】：WHERE, SORT
【1Z0-051】：共3章
【1Z0-052】：共3章

2016-04-18.txt:

SQL> conn hr/oracle_4U
Connected.
SELECT last_name
FROM employees
3 WHERE hire_date = '17-FEB-96' ;
no rows selected
SQL> SELECT last_name
2 FROM employees
3 WHERE hire_date = '2/17/1996 00:00:00 AM' ;
WHERE hire_date = '2/17/1996 00:00:00 AM'
*
ERROR at line 3:
ORA-01843: not a valid month
SELECT last_name
FROM employees
3 WHERE hire_date = to_date ('17-FEB-96','DD-MON-RR') ;
no rows selected
SQL> show user
USER is "HR"
SQL> create table t05102_a( a varchar2(20)) ;
Table created.
SQL> insert into t05102_a values ('A'||chr(37));
1 row created.
SQL> insert into t05102_a values ('B');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05102_a ;
A
--------------------
A%
B
SQL> select * from t05102_a where a like '%%' ;
A
--------------------
A%
B
SQL> select * from t05102_a where a like '%\%' escape '\';
A
--------------------
A%
SQL> select q'[']' from dual;
Q
-
'
SQL> select q'["]' from dual;
Q
-
"
SQL> select q'[%]' from dual;
Q
-
%
SQL> select q'["]' from dual;
Q
-
"
SQL> create table t05102_b ( a varchar2(20)) ;
Table created.
SQL> insert into t05102_b values ('A'||chr(95)) ;
1 row created.
SQL> insert into t05102_b values ('B') ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05102_b;
A
--------------------
A_
B
SQL> update t05102_b set a='BB' where a='B';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t05102_b;
A
--------------------
A_
BB
SQL> select * from t05102_b where a like '__';
A
--------------------
A_
BB
SQL> select * from t05102_b where a like '_\_' escpe '\';
select * from t05102_b where a like '_\_' escpe '\'
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from t05102_b where a like '_\_' escape '\';
A
--------------------
A_
SQL> insert into t05102_b values ('C') ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05102_b where a like '__';
A
--------------------
A_
BB
SQL>
----------------------------------------------------
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected.
SQL> set feedback off
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
SQL> select * from v$datafile;
select * from v$datafile
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET
---------- ---------------- ------------------ ---------- ---------- ------- ---------- ------------------ ------------------ --------------------- ------------------ ------------ ------------------ --------------- -------------- ------------------ ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- -------------
AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TI FOREIGN_DBID FOREIGN_CREATION_CHANGE# FOREIGN_CREATION_T PLU PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_T
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------------ ------------ ------------------------ ------------------ --- -------------- ------------------------ ------------------
1 7 15-AUG-09 0 1 SYSTEM READ WRITE 1313408 18-APR-16 0 945183 945184 23-MAY-13 723517440 88320 0 8192 +DATA/orcl/datafile/system.256.816169553 0 4294967295
NONE 0 0 0 NO 0 0
2 2140 15-AUG-09 1 2 ONLINE READ WRITE 1313408 18-APR-16 0 945183 945184 23-MAY-13 629145600 76800 0 8192 +DATA/orcl/datafile/sysaux.257.816169553 0 4294967295
NONE 0 0 0 NO 0 0
3 942603 15-AUG-09 2 3 ONLINE READ WRITE 1313408 18-APR-16 0 945183 945184 23-MAY-13 104857600 12800 0 8192 +DATA/orcl/datafile/undotbs1.258.816169553 0 4294967295
NONE 0 0 0 NO 0 0
4 17993 15-AUG-09 4 4 ONLINE READ WRITE 1313408 18-APR-16 0 945183 945184 23-MAY-13 7864320 960 0 8192 +DATA/orcl/datafile/users.259.816169553 0 4294967295
NONE 0 0 0 NO 0 0
5 973659 23-MAY-13 6 5 ONLINE READ WRITE 1313408 18-APR-16 1118860 11-APR-16 973659 973664 23-MAY-13 104857600 12800 104857600 8192 +DATA/orcl/datafile/example.265.816169651 0 4294967295
NONE 0 4023594539 945509 15-AUG-09 NO 965811 945184 23-MAY-13
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/orcl/datafile/system.256.816169553
+DATA/orcl/datafile/sysaux.257.816169553
+DATA/orcl/datafile/undotbs1.258.816169553
+DATA/orcl/datafile/users.259.816169553
+DATA/orcl/datafile/example.265.816169651
SQL> col name format a60 trunc
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
+DATA/orcl/datafile/system.256.816169553
+DATA/orcl/datafile/sysaux.257.816169553
+DATA/orcl/datafile/undotbs1.258.816169553
+DATA/orcl/datafile/users.259.816169553
+DATA/orcl/datafile/example.265.816169651
SQL> conn hr/oracle_4U
Connected.
SQL> select salary from employees where employee_id = &s_1;
Enter value for s_1: 100
old 1: select salary from employees where employee_id = &s_1
new 1: select salary from employees where employee_id = 100
SALARY
----------
8000
SQL> /
Enter value for s_1: 101
old 1: select salary from employees where employee_id = &s_1
new 1: select salary from employees where employee_id = 101
SALARY
----------
17000
SQL> /
Enter value for s_1: 102
old 1: select salary from employees where employee_id = &s_1
new 1: select salary from employees where employee_id = 102
SALARY
----------
17000
SQL> set feedback off
SQL> set verify off
SQL> /
Enter value for s_1: 103
SALARY
----------
9000
SQL> select salary from employees where last_name like '&s_2%';
Enter value for s_2: T
SALARY
----------
8600
3200
2800
10000
7000
SQL> select last_name, salary from employees where last_name like '&s_2%';
Enter value for s_2: T
LAST_NAME SALARY
------------------------- ----------
Taylor 8600
Taylor 3200
Tobias 2800
Tucker 10000
Tuvault 7000
SQL> /
Enter value for s_2: S
LAST_NAME SALARY
------------------------- ----------
Sarchand 4200
Sciarra 7700
Seo 2700
Sewall 7000
Smith 8000
Smith 7400
Stiles 3200
Sullivan 2500
Sully 9500
SQL> select last_name, salary from employees where last_name like '&&s_3%';
Enter value for s_3: T
LAST_NAME SALARY
------------------------- ----------
Taylor 8600
Taylor 3200
Tobias 2800
Tucker 10000
Tuvault 7000
SQL> /
LAST_NAME SALARY
------------------------- ----------
Taylor 8600
Taylor 3200
Tobias 2800
Tucker 10000
Tuvault 7000
SQL> undefine s_3
SQL> /
Enter value for s_3: T
LAST_NAME SALARY
------------------------- ----------
Taylor 8600
Taylor 3200
Tobias 2800
Tucker 10000
Tuvault 7000
SQL> /
LAST_NAME SALARY
------------------------- ----------
Taylor 8600
Taylor 3200
Tobias 2800
Tucker 10000
Tuvault 7000
SQL> undefine s_3
SQL> define s_4 = G
SQL> select last_name, salary from employees where last_name like '&s_4%';
LAST_NAME SALARY
------------------------- ----------
Gates 2900
Gee 2400
Geoni 2800
Gietz 8300
Grant 2600
Grant 7000
Greenberg 12008
Greene 9500
SQL> select last_name, salary from employees where last_name like '&&s_5%' or first_name like '&s_5%';
Enter value for s_5: A
LAST_NAME SALARY
------------------------- ----------
Hunold 9000
Austin 4800
Khoo 3100
Fripp 8200
Atkinson 2800
Errazuriz 12000
McEwen 9000
Ande 6400
Banda 6200
Abel 11000
Hutton 8800
Bull 4100
Cabrio 3000
Walsh 3100
SQL> select last_name, first_name, salary from employees where last_name like '&&s_5%' or first_name like '&s_5%';
LAST_NAME FIRST_NAME SALARY
------------------------- -------------------- ----------
Hunold Alexander 9000
Austin David 4800
Khoo Alexander 3100
Fripp Adam 8200
Atkinson Mozhe 2800
Errazuriz Alberto 12000
McEwen Allan 9000
Ande Sundar 6400
Banda Amit 6200
Abel Ellen 11000
Hutton Alyssa 8800
Bull Alexis 4100
Cabrio Anthony 3000
Walsh Alana 3100
SQL>

复制代码

2016-04-18.sql:

select sysdate from dual;
SELECT last_name
FROM employees
WHERE hire_date = to_date ('17-FEB-96','DD-MON-RR') ;
SELECT employee_id,last_name, manager_id
FROM employees
WHERE manager_id IS NULL ;
--ERROR
SELECT employee_id,last_name, manager_id
FROM employees
WHERE manager_id = NULL ;
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;
SELECT employee_id, last_name, salary*12 annsal , department_id
FROM employees
ORDER BY department_id desc ;
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
-----
select * from user_ind_columns c
where c.TABLE_NAME='EMPLOYEES';
select * from user_indexes i
where i.INDEX_NAME='EMP_NAME_IX';
select
SUBSTR('HelloWorld',-1,5)
from dual;
select
SUBSTR('HelloWorld',-5)
from dual;
select
SUBSTR('HelloWorld',5)
from dual;
-----
select * from database_properties;
select INSTR('HelloWorld', 'Z') from dual;
select replace ( 'HelloWorld','W' , '')
from dual;
select * from employees where employee_id =&s_7;

复制代码