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);
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 territory
NAME TYPE VALUE
nls_territory string AMERICA
select next_day(sysdate,6) from dual; 6=>Friday
SQL> alter session set nls_territory='china';
Session altered.
select next_day(sysdate,5 from dual; 6=>Friday
4@@@@alter territory=' Australia ' to test.the last_day is universal function to calculate the last day
every month in different year.
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------
2011-09-30 15:55:22
SQL> select last_day(add_months(sysdate,1)) from dual;
LAST_DAY(ADD_MONTHS
-------------------
2011-10-31 15:55:37
SQL> select to_char(sysdate,'d dd ddd') from dual;
TO_CHAR(
--------
1 18 261
SQL> alter session set nls_territo