Shulou(Shulou.com)06/01 Report--

To match.

! = to match data that is not equal to a certain value can use and to join two expressions, indicating that all records that satisfy both expressions can use or to join two expressions, as long as the expression that satisfies one of them matches Select*from table_name where id=1;Select*from table_name where sal 800 (or sal! = 800) Select*from table_name where sal > 800 and sal=800 and sal10 2. Find the average salary of the department whose department number is greater than 10: first filter the data, then group the function Select avg (sal) from emp where deptno > 10 group by deptno;3, find the department number of the department whose average salary is greater than 1600 and their average wage: evaluate (grouping) with the group function first, and then filter the data Select deptno,avg (sal) from emp group by deptno having avg (sal) > 1600 having: used to filter the grouped data, only after group by. The execution order of SQL statements: 1, from sentences: from right to left, from back to front, it is recommended to put the tables with small amount of data in the back, and the tables with large data in front; 2. Where sentences: from right to left, from bottom to top; it is recommended to put the statements with large amount of filtered data behind; 3. Group by statements: very resource-consuming, recommended to use less; it is recommended to filter as much data as possible in where 4. Having statement: from left to right 5, Select statement: when parsing *, oracle will first find the fields of the table (parsing fields) according to the table name, which takes time, so try to avoid using *, but replace it with specific fields; 6. Order by statement: from left to right, very resource-consuming. Multi-table query: 1. Display employee number, employee name, employee department number and department name association query select * from emp;//14 data Select * from dept;//4 statement Select * from emp,dept / / 56 data Cartesian product: the result of multiplying the records of two tables is Cartesian product equivalent join Select empno,ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno multi-table query: multiple tables are separated by commas If two tables have fields with the same name, if you need to display fields with the same name, you need the table name. For a table, you can also have the alias Select e.empnoree.enamedepartment d.deptnored.dnameFrom emp e dmpt dWhere e.deptnodeptno.deptno.Inner join: replace', 'between table names in the equivalent join with', 'and then change where to on. Select e.empnoree.enamered.deptnored.dnameFrom emp e join dept dOn e.deptno=d.deptno external join: left outer join: left join table on the left side of the data will not be less (all match), the left table is also known as the driver table, and then according to the conditions to the right table to match, do not match the records, the fields of the table are automatically filled with null values. Select e.empnoree.enamered.deptnored.dnameFrom emp e left join dept dOn e.deptnored.deptnored.ename.deptnored.dnameFrom emp e, dept dWhere e.deptno=d.deptno (+); / / (+) must be written on the opposite right outer connection of the driver table: Select d.deptnored.dnameFrom emp e right join dept dOn e.deptno=d.deptno. It can also be written as: Select d.deptnored.dnameree.empnoree.enameFrom emp e, dept dWhere e.deptno (+) = d.deptno.full external connection: Select d.deptno.enameFrom emp e full join dept dOn e.deptno.self-join: expand the same table to two tables with aliases, and then correlate match select distinct e1.empnoparting e1.ename from emp e1, emp e2 where e1.empno=e2.mgr Remember: the outer connection has a keyword outer Left outer join Sql advanced query: the condition and table may be obtained from a select statement as a clause of where: the condition that where depends on is not a definite value, nor is it obtained by a function. A select statement is embedded in a select statement that depends on the return value of a select. The embedded select statement is called the select clause, the Select clause is executed first, and then the parent select is compared. According to the return value of the select statement, it can be divided into one-line select clause (= in clause).

! =.) Multi-row select clause (ANY ALL in not in) Multi-column select clause (in) Select statement: returns one row of data, multiple rows of data, multiple column data returns multiple statements: ANY,ALL function, cannot be used alone, can only be used with

= use together

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.