Complex queries & pagination of tables in the database

A complex query of tables in a database 1) connection Query 1.0 the basic syntax format of the connection: from TABLE1 join_type TABLE2 [On (join_condition)][where (query_condition)]table1: Left table TABLE2: Right table Join_type: type of connection. Cross, inner connection, left outer connection, right outer connection on: Set connection condition where: The result of a join query 1.1 cross Connect select * from customer crosses join ORDERS; or select * from Customer, Orders;select C.name,o.order_number from Customer c,orders o;1.2 connection: Implicit INNER join: (Do not use Onkeyword, where) select * FROM Customer C,orders o where c.id=o.customer_id; explicit intra-connection: (using Onkeyword) SELECT * FROM customer C inner join ORDERS o on c.id=o.customer_id; 1.3 Outer joins: Left OUTER join: (returns all records that meet the connection criteria.) At the same time, all remaining records in the left table are returned) SELECT * from CUSTOMER C to outer join ORDERS O on c.id=o.customer_id; right outer join: (returns all records that meet the connection criteria. Return all remaining records in the right table at the same time) select * from CUSTOMER C outer join ORDERS O on c.id=o.customer_id;2) subquery (nested query) subquery: SELECT * from O Rders where customer_id= (select ID from customer where name= ' Zhang San '); 3) union query select * FROM orders where price>200 UNION Sele CT * from Orders WHERE customer_id=1; Take a set of two statements. and remove the repeated records. Ii. paging in the database: 1) pages in MySQL: (LIMIT m,n Note: M indicates the starting subscript, n indicates the number of bars to query) SELECT * From table WHERE ... LIMIT 1; #返回第0行select * FROM table WHERE ... LIMIT 10; #返回0到9行select * FROM table WHERE ... LIMIT 0, 10; #返回0到9行: Start with 0, check 10 select * FROM Table WHERE ... LIMIT 10,15 #返回10到24行: Starting from 10, check 15 article 2) pages in Oracle: 1) rownum and ROWID in Oracle are pseudo-columns, But the two are fundamentally different: rownum is assigning a logical number to each row based on the results of the SQL query. So the difference between your SQL will lead to finally rownum different.
Note: RowNum is a physical structure that records from the beginning of 1, and there is a unique physical record when each record is insert into the database. rowID is unique. 2) rownum is usually used when SQL paging or when looking for a range of records, and is used frequently to ROWID when working with a table with repeated records. Sample one: Find Records less than 3 (note: If you are using rownum directly, the range you are looking for must include 1) sql:select * from EMP a WHERE rownum < 3. Sample two: Find records in the range 2 to 10 (this includes 2 and 10 records) strategy: The rownum is checked out and placed in a virtual table as the field of the virtual table in accordance with the conditions of the query Sql:select * FROM (select RowNum rn, a.* from EMP A ) T where T.rn between 2 and 10;

Complex queries & pagination of tables in the database