Oracle NOTE 2

2011-08-24

Alter user Scott account unlock change users unlock
Conn Scott/tiger re-login with the above user

SELECT statement:

1. DESC EMP describes the EMP table and lists the basic information of the table.
2. Data type: Number (7,2) 7 digits, 2 decimal places

Learning Examples:
Three tables: EMP Employee table, Dept Department table, Salgrade salary scale

3. Select from dept; Note: Each statement ends with;
4. desc Dual Empty Table DUMMY empty table empty table use
5, select Sysdate from dual;
6, select Ename,salfrom EMP; Calculate each person's annual salary
Select Ename,salanuual_sal from EMP;
Select Ename,sal"anuual sal" from EMP; There can be no spaces between anuual Sal, if there is space, double quotation marks should be added

7, select Ename,sal*12 + comm from EMP; Null value for any mathematical expression of NULL value
8, two-character connected expression (two inter-field connection): Select Ename| | Sal from EMP;
9. Field and Character connection: select Ename | | ' ABCDE ' from EMP;
If there is a quotation mark in the string, you can use two quotation marks to denote a quotation mark, such as: Select Ename | | ' abc ' de ' from EMP;
10, select distinct deptno from EMP; Distinct (keywords, removing duplicate sections in the Department field)
11, where filter condition, not like ' _a% ' second letter cannot be A;
12. Desc Description Descending ASC ascending order (default)

13. The lower () function turns uppercase into lowercase such as: select Lower (ename) from EMP; All to uppercase function: Upper (), use the same as lower ();

14, substr () function such as: Select SUBSTR (ename,2,3) from EMP, starting from the second character to take 3 characters.
15, Chr () to escape the number of characters such as: Select Chr (+) from dual;
16, ASCII () the character escapes into numbers such as: Select ASCII (' A ') from dual;
17, select Round (23.652) from dual; Rounded
Select Round (23.652,2) from dual; Rounded to two digits after the decimal point,-1, if 1 is rounded to bits;
18, To_char (Sal, ' $99,999.9999 ') output in the specified format, such as: Select To_char (sal, ' $99,999.99999 ') from dual; 9 represents a digit, l represents a local currency, such as: To_char (Sal, ' l99,999.9999 '), 0 represents a numeric, numeric format model.
19. Conversion Date Format: Select To_char (hiredate, ' Yyyy-mm-dd hh:mi:ss ') from EMP;
20. To_date () function, convert string to date, as
Select Ename,hiredate from emp where hiredate > To_date (' 1981-8-23 12:23:44 ', ' yyyy-mm-dd hh:mi:ss ');
21, To_number () function, convert the string to a number, such as:
Select Ename,salFrom EMP where Sal> To_number (' $123,3456.8997 ', ' $999,9999.9999 ');
22, NVL () processing null function, NVL (comm,0) means that if the value inside the comm is empty then replace with 0, if not empty, the original number is represented
such as: Select Ename,sal*12 +NVL (comm,0) from EMP;
23. Group functions:
Max ()
Min ()
AVG ()
Sun ()
Count ()
24, the importance of group by grouping function, appears in the select list of fields, if not appear in the group function, must appear in the groups by function, otherwise there will be an error:
such as: Select Deptno,avg (SAL) from the EMP group by DEPTNO; To find out the average salary of each department;
Select Deptno,job,max (SAL) from the EMP group by Deptno,job;
Group BY Deptno Job
25. Sub-query application: Select Ename,sal from emp where sal = (select Max (SAL) from EMP); Find the name of the person with the highest salary;
26. Having a statement limits the grouping
Select AVG (SAL), Deptno from EMP Group BY DEPTNO have avg (SAL) > 2000; Find a department with an average salary greater than 2000;

27. Connection sub-query:
Select Ename,sal from emp join (select MAX (SAL) max_sal, deptno from EMP Group by Deptno) T on (emp.sal = T.max_sal and E Mp.deptno =t.deptno);
Find the name of the person with the highest salary in each department, and the salary
28. Self-connection of the table: select E1.ename,e2.ename from emp e1,emp E2 where e1.mgr=e2.empno;

SQL 99 new syntax, where only filters are used

Cross connection: Select Dname,ename from emp crosses join dept;

29. Table connection two different methods: where and join ... on
such as: Select Ename,dname from emp,dept where emp.deptno = dept.no;
Select Ename,dname from EMP Join dept on (Emp.deptno = Dept.deptno);

30, not equal to the connection
such as: Select Ename,grade from emp E, Salgrade s where e.sal between S.losal and S.hisal;
Select Ename,grade from emp e joins Salgrade s on (e.sal between S.losal and S.hisal);

31. Multi-Table Connection:
Select Ename,dname,grade from emp E
Join Dept D on (e.deptno = D.deptno)
Join Salgrade s on (e.sal between S.losal and S.hisal)
where ename not like ' _a% '
ORDER BY e.sal Desc;

32. Left Outer connection:
Select E1.ename,e2.name from EMP E1 LEFT join EMP E2 on (E1.mgr =e2.empno);
32. Right Outer connection:
Select Ename,dname from the EMP e right outer joins Dept D on (e.deptno = D.deptno);
33, around full connection
Select Ename,dname from emp e full join Dept D on (e.deptno = D.deptno);

34, the Department average salary level:
Select T.deptno,dname,grade from
(select Deptno,avg (SAL) from EMP Group by DEPTNO) T
Join Dept D on t.deptno = D.deptno
Join Salgrade s on t.avg_sal between S.losal and S.hisal
ORDER BY t.avg_sal Desc;

35. User authorization:
Conn Sys/tiger as Sysdba

To create a view:
Create V$_dept_avg_sal_info as
Select Deptno,grade,avg_sal from
(select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO) T
Join Salgrade s on (t.avg_sal between S.losal and S.hisal)
;

Grant CREATE TABLE, create view to Scott;
Assign the user Scott to set up a table, establish View permissions
Delete users: Drop user Scott Cascade; (note: To be performed as an administrator)

36, Conn Sys/tiger as SYSDBA; Log in as Administrator

37. Export User data:
Enter an empty directory and create an empty folder to store the data that is being sent out:
C:\>CD Temp
C:\temp>del .;
C:\temp>exp

38, create users: creat user Ming identified by Tiger (password) default tablespace users quota 10M on users;
Grant Create session,create table,create view to Ming; Authorized

39. Import User data:
C:\temp>imp

40. DML Statements:
Insert a piece of data in dept: INSERT INTO Dept values (' Game ', ' BJ ');
Note: String data to use single quotation marks
INSERT INTO Dept (deptno,dname) VALUES (' Game2 ')
INSERT INTO Dept select * FROM dept; Insert the whole table in there.

41, Rollback: Undo the last step of the operation

42. Back up a table: CREATE TABLE EMP2 as SELECT * from EMP;

43, the use of RowNumber

Oracle NOTE 2