oracle10g_sqlplus

sqlplus
the following notes is the key to 007 lessons.
1）char
1@@@@#man ascii , check the ascii number
SQL> create table t00702_a(a varchar2(20));
SQL> insert into t00702_a values('''');
SQL> insert into t00702_a values('a');
SQL> insert into t00702_a values('_1');
SQL> insert into t00702_a values('%111');
SQL> insert into t00702_a values('%');
SQL> insert into t00702_a values('_');
SQL> insert into t00702_a values(chr(39)||'2');
SQL> insert into t00702_a values(chr(40));
SQL> insert into t00702_a values('');
SQL> select * from t00702_a;
A
--------------------
%
%111
_
_1
a
'
'2
:
(
10 rows selected.
2@@@@some spical command about char.
SQL> select * from t00702_a where a like '\%' escape '\';
A
--------------------
%
SQL> select * from t00702_a where a like '\%___' escape '\';
A
--------------------
%111
SQL> select * from t00702_a where a like '\%_' escape '\';
no rows selected
SQL> select * from t00702_a where a like '\%%' escape '\';
A
--------------------
%
%111
3@@@@you can search the ascii form in linux.
[root@station23 ~]# man ascii
       047   39    27    ’                           147   103   67    g
       050   40    28    ( 
2) null
1@@@@147 (4-16) (p142) logical relationship true,false,null
null AND false = false;
null OR true= true;
not null = null;
2@@@@not null= null;
SQL> select * from employees where employee_id not in (select manager_id from employees where manager_id is not null);
SQL> select * from employees where manager_id not in (102,200,201,null);
no rows selected
3) logical parentheses
1@@@@logical priority change, because the sql in the parentheses is firstly executed;
SQL> select count(*) from employees where (salary<7100 or salary>2100) and salary<5000;
  COUNT(*)
----------
        49
SQL> select count(*) from employees where salary<7100 or salary>2100 and salary<5000;
  COUNT(*)
----------
        63
4) order by
1@@@@default is ascending, the descending abbreviation is desc.
SQL> select last_name, department_id, salary from employees order by department_id, salary desc;
SQL> select last_name,salary from hr.employees order by 2 desc;
5) single-row functions
1@@@@the boundary of the date is 15 in the function round(x,y)
SQL> select round(to_date('2011-2-15','yyyy-mm-dd'),'month') from dual;
ROUND(TO_DATE('201
------------------
01-2-11
SQL> select round(to_date('2011-2-16','yyyy-mm-dd'),'month') from dual;
ROUND(TO_DATE('201
------------------
01-3-11
2@@@@the Chinese characters occupy the three or more bytes, it's up to your character set.
SQL> create table t00702_a(a varchar2(20));
SQL> insert into t00702_a values('tangjun');
SQL> insert into t00702_a values('堂君');
SQL> select a,length(a), lengthb(a) from t00702_a ;
3@@@@ the different culture have different date.
SQL> show parameter t