Oracle commands (course notes)

Oracle commands (Course Notes) 1. start the Service (must be started) (1) listener (2) database Note: E: \ oracle \ product \ 10.1.0 \ db_1 \ NETWORK \ ADMIN listener. ora tnsnames. ora2. SQL plus visual tool dos 3. three system accounts

Sys chang_on_install role sysdba system manage scott tiger (Common User) conn is used to connect to the database. conn username/password @ // ip/Database Name conn scott/tiger @ // 192.168.41.2/niit conn username/password as sysdba

 

4. Some common oracle commands

(1) conn: establish a connection (2) disconn: disconnect (3) show user; view the current user (4) start or @: used to execute statements in the file (5) edit: used to edit a file (6) passw: used to modify your own password. To modify another user's password, you must use the Administrator's identity to log on to alter user scott identified by tiger; unlock alter user scott account unlock for the user; (7) spool: output the content in the console to the file to spool d: \ aa.txt all operations performed by spool off (8 )&: interaction with data select empno from emp where ename = & aa;

 

5. oracle Data Type (1) character class char 2000 characters a Chinese character occupies two characters length is fixed length char (15) 'niit' uses spaces to complete the varchar2 4000 characters varchar2 (36) 'niit' 32 clob 4G content (2) the 38-power integer and floating point number (n [, m]) number (3)-999 999 number (5, 3)-99.999 99.999 (3) of the value type number-10 to the 38-power 10) date type date year month day hour minute second timestamp millisecond get current system time sysdate day month year 01-13 ~imestamp 6. simple Query

(1) select * from emp; (2) select empno from emp where ename = & aa; (3) +-*/obtain the annual salary of all employees. select sal * 13 from emp; (4) as Alias select sal * 13 annual salary from emp; (5) processing of null values nvl (comm, 0) select sal * 13 + nvl (comm, 0) from emp; (6) character connector | select 'name is '| ename from emp; (7) and or select empno from emp where ename = 'dd' or 5> 3; (8) in not in select sal from emp where sal in (1500,); (9) between and (10)> >=< =! = (<>) (11) like fuzzy query % indicates that there are 0 to multiple characters _ indicates that one character finds the information of employees with S in all employee names select ename from emp where ename like '% S % '; select ename from emp where ename like '_ O %'; (12) sort order by asc/desc select sal from emp order by sal;

 

8. Single Row Functions

(1) numeric function (a) abs (n); absolute value select abs (-9) from dual; 9 (B) ceil (n ): select ceil (3.4) from dual; 4 (c) floor (n): select floor (3.4) from dual; 3 (d) round (n, m) rounded to select round (3.5), round (3.4) from dual; select round (333.35, 1), round (343.33,-2) from dual; (e) trunc (n, m) intercept select trunc (3.5), trunc (3.4) from dual; select trunc (333.35, 1), trunc (353.33, -2) from dual; (2) character function (1) Concat (char, char) connection character select concat (ename, sal) from emp; (2) initcap (char) capitalized select initcap (ename) from emp; (3) instr (char, char [, n, m]) Find the substring subscript select instr ('abcsdgsdsd', 'A') from dual; select instr ('abcsdgsdsdsd ', 'D',-1, 3) from dual; (4) length (char): returns the string length select ename from emp where length (ename) = 4; (5) lower () to lowercase (6) upper () to uppercase (7) lpad () left fill (8) rpad () Right fill select lpad (ename, 10 ,'*') fr Om emp; (9) trim () Remove space at both ends select trim ('A') from dual; select trim ('A' from 'aaabbbaaacccdddaa') from dual; rtrim () ltrim () (10) substr (char, n, m) select substr ('niit', 1) from dual; (3) date and time (1) sysdate (2) systimestamp (3) add_months (sysdate, 2) plus/minus a few months. Select sysdate, add_months (sysdate, 2), add_months (sysdate,-2) from dual; (4) alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; (5) months_between (d, d ). the oracle 31 month for calculating the number of months the employee has worked so far select months_between (hiredate, sysdate) from emp; (6) extract (year from d) year month day hour minute second select extract (year from sysdate) from dual; (7) last_day (d) Get the last day of the date (8) next_day (); next workday (9) round: returns a date rounded to (10) trunc: truncates the year month, day select round (sysdate, 'month') from dual; select trunc (sysdate, 'month ') from dual; yyyy mm dd hh hh24 mi ss $ indicates the expression of usd l local currency, separated between values. decimal separation 9 indicates the value 0 indicates the decimal place (4) Conversion Function to_char (c, format) select to_char (sysdate, 'dd') from dual; select to_char (123456, '$999,999.99') from dual; $123,456 to_date ('Today is July 11', '"today is" yyyy "years"') from dual; to_number () (5) other functions nvl () decode (); if else if select decode (job, 'cler', sal, 'manager', sal * 1.2) from emp;

 

9. Grouping Functions

Count () count sum () sum avg () average max () Maximum min () Minimum (1) number of members in each department group by select count (empno) from emp group by deptno; select deptno, count (empno) from emp group by deptno; (2) obtain the average salary of each department select avg (sal) from emp group by deptno; the average salary of Department 10 except select deptno, avg (sal) from emp where deptno not in (10) group by deptno; select deptno, avg (sal) from emp where avg (sal) >= 2000 group by deptno; (3) select deptno, avg (sal) from emp where deptno not in (10) group by deptno having avg (sal)> = 800 order by avg (sal) asc; 1. where filter some records 2. group by group second record 3. filtering having records after grouping 4. order by sorting