<title>Horse Soldier Big Brother Database Tutorial 1 stage</title> Well-known 2 Oracle Learning client: TOAD Toad, nasty guy, PL/SQL Four categories: query statement, DML statement (data operation), DDL (data definition), transaction operation statement, 1, any mathematical expression with null value is finally null 2,DISTINTCT field 1, Field 2 indicates that the field combination is de-duplicated; 3, the string comparison is compared to ASCII code; 4,where CONDITION between 1 and 2, including 1 and 2; 5,is Null,is not Null6,in (cod1,cod2,cod3); 7,lik E in _ represents a letter,% is 0 or more, 8, the translation character \ or use escape:like '%\%% ' represents the middle has a% of the character
Sort: ORDER by case:form? ORDER by. ORDER by A Asc,b ASC. A is ascending, while a is in ascending order of B. The ORDER by row is after the where.
Common SQL functions: 1,lower () Convert to lowercase, UPPER () uppercase. 2,substring (object, starting position, length) 3 chr (65) corresponds to the character of the number, asci (' a ') corresponds to the ASCII code of the letter, 4 ROUND (2.234) = 2 ROUND (2.234,2) = 2.23 ROUND (23.234,-1) = 205,to_char (SAL, ' $99,999.9999 ') 9 for the number, before the decimal point is displayed, no unrealistic, the decimal point when the signature shows 0. ' l ' stands for local currency; 6,to_date (' 1988-08-20 12:34:50 ', ' Yyyy-mm-dd HH:MM:SS '); 7, group functions: Many records merge to produce an output count (*), SUM (*), AVG (*) , Max (*), MIN (*) 8,group by the Department group for the average salary, according to the specific field to find the average value of the group; Looking for the most high-paying in the department of the process to bring out the most high-paying people, how to do? SELECT Ename,max (SALARY) from ... Max does not match this. You need to use a subquery. That's why you can't match one to the other. Selecct Enmae,salaru from the EEP WHERE SALARY = SELECT MAX (SALARY) from eep9,having. Where to restrict a single statement. Use having to restrict grouping data. Summary: SELECT * from where ... group by ... order BY10, consider the result of a subquery as a table: select Ename,salary from EMP JOIN (select MAX (SAL ARY) Max_salary,depno from the EMP GROUP by EMPNO) T on (t.max_salary = EEP. salary,eep.no = t.empno) Self-connect connect yourself to yourself: 11,slq1999 Standard: Old syntax mixes table joins and conditions. SELECT ename,dname FORM EEP cross JOIN: Cross Connect. New syntax: The join condition is not written in the join condition. From table 1 join table 2 on (connection bar) or on using (field) . LEFT outer connection to the left of 2 tables can not generate a connection to the record to take out, the right outer join will be the right table can not generate a connection to the record to show, full join all outside the connection, all can not produce the record of the connection ; select Depno,avg_salary,grade from (SELECT depno,avg (SALARY) avg_salary GROUP by Depno) T JOIN Salgra DE S on (t.avg_sal between S.losal and S.hisal) 12. Group function nesting of up to 2; 13,conn sys/username as Sysdba log in with Super Administrator; Drop user username cascade; Add Username: Backup Scott (username) beifen
From for notes (Wiz)
Horse Soldier Big Brother Database Tutorial 1 stage
