SQL Summary (3)---compare all

Cross joins (Cross joins): There are two, explicit and implicit, without an ON clause, which returns the product of two tables, also called the Cartesian product. For example: The result of the following statement 1 and statement 2 is the same. Statement 1: An implicit cross join, there are no crosses joins. SELECT o.id, O.order_number, C.id, C.namefrom ORDERS O, CUSTOMERS cwhere o.id=1; Statement 2: Explicit cross-joins, using the Intersect join. SELECT o.id,o.order_number,c.id,c.namefrom ORDERS O cross JOIN CUSTOMERS cwhere o.id=1; The result of statement 1 and statement 2 is the same, the query results are as follows: Ii. INNER JOIN (INNER join) INNER JOIN (INNER join): There are two kinds, explicit and implicit, that return data rows in the join table that meet the join criteria and query criteria. (The so-called link table is the database in the form of queries formed in the intermediate table). For example: The result of the following statement 3 and statement 4 is the same. Statement 3: Implicit inner join, without inner join, forms a Cartesian product of two tables. SELECT o.id,o.order_number,c.id,c.namefrom CUSTOMERS c,orders owhere c.id=o.customer_id; Statement 4: Internal connections shown, generally called Inner joins, inner JOIN, the resulting intermediate table is a Cartesian product with two tables filtered by on condition. SELECT o.id,o.order_number,c.id,c.namefrom CUSTOMERS C INNER JOIN ORDERS O on c.id=o.customer_id; query results for statement 3 and statement 4: Iii. Outer JOIN (OUTER join): The outer joins not only return rows of data that meet the connection and query criteria, but also return some rows that do not meet the criteria. The outer joins are divided into three categories: Left OUTER join (OUTER join), right outer join (OUTER join), and full outer join (fully OUTER join). What all three have in common is that they return rows of data that meet the join criteria and query criteria (that is, inner joins). The difference is as follows: the left OUTER join also returns data rows in the left table that do not meet the criteria of the join criteria for the query. The right outer join also returns the data rows in the right table that do not meet the criteria for the join criteria. The full outer join also returns the data rows in the left table that do not meet the criteria for the join criteria, and also returns the non-conformance in the right tableThe data row that the Join condition list conforms to the query criteria. The full outer join is actually a mathematical collection of upper left outer joins and right outer joins (removing duplicates), i.e. "all outside = left outer UNION right outside". Description: The left table is the table to the left of the "(OUTER JOIN)" keyword. The right table is, of course, on the right. In three types of outer joins, the OUTER keyword can be omitted. The following example shows: statement 5: Left OUTER join (OUTER join) SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O OUTER Join CUSTOMERS C on c.id=o.customer_id; Statement 6: Right out of the connection (R OUTER join) SELECT O.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O Right OUTER joins CUSTOMERS C on c.id=o.customer_id; note: The Where condition is placed on the back of the query results are not the same. For example: statement 7:where condition is independent. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O left OUTER joins CUSTOMERS C on C.id=o.customer_idwhere O.order_number<> ' mike_order001 '; statement 8: Place the WHERE condition in statement 7 after on. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O left OUTER joins CUSTOMERS C on c.id=o.customer_id and O. Order_number<> ' mike_order001 '; from the results of statement 7 and statement 8 queries, it is clear that the result of statement 8 is difficult to understand. Therefore, it is recommended that when writing a connection query, on is followed only by the join condition, and the conditions for the intermediate table restrictions are written in the WHERE clause. Statement 9: Full outer join (OUTER join). SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O full OUTER Join CUSTOMERS C on c.id=o.customer_id; Note: MySQL does not support all-out connections, and the notation given here is for Oracle and DB2. However, it is possible to obtain the query result of the full outer join through the left outer and right outside to find the collection. Is the result of the above SQL execution under Oracle: statement 10: A collection of left and right outside, in fact the query results and statement 9 are the same. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O left OUTER joins CUSTOMERS C on C.id=o.customer_ Idunionselect o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O right OUTER joins CUSTOMERS C on C.id=o.customer _id; The query results for statement 9 and statement 10 are the same, as follows: Iv. Union join: This is a rare way to connect. Both Oracle and MySQL do not support the purpose of finding all the rows that are different between the full outer and inner connections. This is more commonly used in troubleshooting data analysis. You can also use the collection operations of the database to implement this functionality. Statement 11: Union query (Union JOIN) example, no SQL environment can be found for execution. SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O UNION JOIN CUSTOMERS C on C.id=o.customer_ ID Statement 12: The equivalent implementation of statement 11 under DB2. It is not yet known if DB2 supports statement 11! SELECT o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O full OUTER JOIN CUSTOMERS C on C.id=o.customer_ Idexceptselect o.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O INNER JOIN CUSTOMERS C on c.id=o.customer_id; Statement 13: The equivalent implementation of statement 11 under Oracle. SELECT O.id,o.order_number,o.customer_id,c.id,c.namefrom ORDERS O full OUTER joins CUSTOMERS C on C.id=o.customer_idminusselect o.id,o.order_number,o.customer _id,c.id,c.namefrom ORDERS O INNER join CUSTOMERS C on c.id=o.customer_id; the query results are as follows: V. Natural connection (NATURAL INNER JOIN): Seriously, This connection query does not exist value, since it is defined in the SQL2 standard, give an example to see it. Natural connections without specifying a connection column, SQL checks two tables for columns of the same name, assuming they are used in the join condition, and contains only one connection column in the join condition. The ON statement is not allowed, the display column is not allowed, and the display column can only be represented by * (tested in the Oracle environment). You can specify natural for each type of connection (except for cross-connections). Here are a few examples. Statement 14:select *from orders o NATURAL INNER join CUSTOMERS C; statement 15:select *from ORDERS o NATURAL left OUTER JOIN CUSTOMERS C; Sentence 16:select *from orders o NATURAL right OUTER join CUSTOMERS C; statement 17:select *from ORDERS o NATURAL full OUTER JOIN CUSTOMER S C; Six, the basic principle of SQL query: Two kinds of situations are introduced. First, single-table query: Filter the records in the table according to where conditions, form an intermediate table (this intermediate table is not visible to the user), and then select the corresponding column according to the Select column of select to return the final result. Second, two-table connection query: The two-table quadrature (Cartesian product) with on conditions and connection type filtering to form an intermediate table; then the records of the intermediate table are filtered based on the Where condition and the query results are returned based on the column specified by select. Third, multi-table connection query: First and second table according to two table connection query, and then use the query results and the third table to make a connection query, and so on, until all the tables are connected, and eventually form an intermediate result table, and then filter the records of the intermediate table according to where conditions, and returns the result of the query based on the column specified by select. The process of understanding SQL query is the theoretical basis for SQL optimization. Seven, on the back of theThe difference between a condition (on condition) and a WHERE condition: on condition: is the constraint condition that the Cartesian product of two linked tables is filtered to form the intermediate table. Where Condition: In a SELECT statement with an on condition, the constraint that filters the intermediate table. In a single-table query that does not have on, it is a constraint that restricts the return of records to physical tables or intermediate query results. In a two-table or multiple-table connection, a constraint that restricts the return result of the connection to form the final intermediate table. As you can see from here, it is inappropriate to move the where condition into the back. The recommended practice is to connect only, where only the records of the intermediate table are filtered. Viii. Summary Connection query is the core of SQL query, the connection type of connection query is selected according to the actual demand. If you choose improperly, not only can not improve query efficiency, but will bring some logic errors or poor performance. The following summarizes the two-table connection query selection method based on: 1, check two tables related columns equal data with the internal connection. 2, col_l is a subset of the Col_r when using the right outer connection. 3, Col_r is a subset of col_l with left outer connection. 4, Col_r and col_l each other has the intersection but each other is not a subset of time with the whole outside. 5, the differential operation when using a joint query. When querying multiple tables, these different connection types can be written to a piece. For example: SELECT T1. C1,t2. Cx,t3. Cyfrom TAB1 T1 INNER JOIN TAB2 T2 on (T1. C1=t2. C2) INNER JOIN TAB3 T3 on (T1. C1=t2. C3) left OUTER JOIN TAB4 on (T2. C2=t3. C3); WHERE T1. X >t3. Y; The above SQL query is a demonstration of multi-table joins. SQL statement optimization? 1. Fuzzy matching of query try to avoid using like '%parm1% ' in a complex query--the red mark position percent sign will cause the index of the related column to be unusable, preferably not used. Workaround: In fact, only need to improve the script slightly, the query speed will be raised nearly hundred times. The improved method is as follows: A, modify the foreground program-the query condition of the Supplier Name column from the original text input to the drop-down list, the user fuzzy input supplier name, directly in the foreground to help locate the specific supplier, so in the background program, this column can be directly associated with equals. b, directly modify the background-according to the input criteria, first identify the eligible suppliers, and keep the relevant records in a temporary table header, and then use temporary tables to do complex association. 2. Indexing problems in the performance tracking analysis process, often found that a lot of background program performance problems due to the lack of suitable indexes, and some tables even an index did not. This is often the case when the table is designed,In the early stages of development, the lack of table records and the creation or otherwise of indexes may have little impact on performance, so developers are not paying much attention. Once the program is released into the production environment, the table records more and more over time. When the index is missing, the impact on performance is getting bigger. Rule: Do not do the following on the data column of the indexed index: avoid calculations on indexed fields avoid using not,<>,!= on indexed fields avoid using is null and is not on indexed columns Null to avoid data type conversions on indexed columns avoid using functions on indexed fields to avoid using null values in indexed columns 3. Complex operation part of update, SELECT statement is very complex (often nested multi-level subquery)-can consider the appropriate split into a few steps, Sir into some temporary data table, And then the associated operation.  4.update modification of the same table appears dozens of times in a process, such as: Update table1 set col1= ...; where col2= ...; Update table1 set col1= ... where col2= ... This type of script can be easily integrated in an UPDATE statement to complete (some time ago when the XXX project to do performance analysis of the existence of this situation) 5. In a statement that can use union ALL, Unionunion is used because the records for each subset of queries are compared. Therefore, the speed is usually much slower than the union all. In general, if you use union all to meet the requirements, be sure to use union ALL. There is a situation that you may ignore, that is, although a few subsets of the Union need to filter out duplicate records, but because of the specificity of the script, it is not possible to duplicate records, then you should use the UNION all, such as XX module of a query program has existed this situation, see, due to the specificity of the statement, The records of several subsets in this script are absolutely impossible to duplicate, so you can use union ALL instead. 6. In the where statement, try to avoid the calculation of the indexed fields the common sense that most developers should know, but still a lot of people use it, I think one of the main reasons may be to write simple and damage performance, it is not taken. September in the performance analysis of XX system found that there are a large number of background programs similar to the use of, such as: where Trunc (create_date) =trunc (:d ate1), although the Create_date field has been indexed, but because of the addition of Trunc, Makes the index unusable. The correct wording here should be where Create_date>=trunc (:d atE1) and create_date< pre= "" ><> either where create_date between Trunc (:d ate1) and trunc (:d ate1) +1-1/(24*60*60). Note: The range of the between is a closed interval (greater than or equal to low value and less than or equal to high value), so it should be deducted a decimal point that tends to be 0 in strict sense. Minus 1 seconds (1/(24*60*60)), you can skip this step if you don't need to be so precise. 7. The rule of the WHERE statement 7.1 avoid using In,not in,or or having in the WHERE clause. You can use exist and not exist instead of in and not. You can use table links instead of exist. Having can be replaced by where, if not replaced, can be handled in two steps. Example SELECT * from the orders where customer_name not in (select Customer_name from CUSTOMER) optimize select * FROM Orders where CUS Tomer_name not exist (SELECT customer_name from CUSTOMER) 7.2 Do not declare numbers in character format, you want to declare character values in numeric format. (The same date) otherwise invalidates the index, resulting in a full table scan.  Example use: Select Emp.ename, emp.job from emp where emp.empno = 7369;--do not use: Select Emp.ename, emp.job from emp where emp.empno = ' 7369 ' 8. The rule of the SELECT statement restricts the use of select * from table in applications, packages, and procedures. See the example below--using the Select Empno,ename,category from emp where empno = ' 7369 '--instead of using the SELECT * from emp where empno = ' 7369 ' 9. Sorting avoids the use of resource-intensive operations, SQL statements with Distinct,union,minus,intersect,order bywill start the SQL engine execution, which consumes the resource sorting (sort) function. Distinct requires a sort operation, while the others need to perform at least two sorting. 10. Temporary tables using temporal tables cautiously can greatly improve system performance. 11. To optimize the query, avoid full-table scanning as far as possible, and first consider establishing an index on the columns involved in the Where and order by. 12. You should try to avoid null values in the WHERE clause to judge the field, otherwise it will cause the engine to abandon using the index for full table scan, such as: Select ID from the where num is null can set the default value of 0 on NUM, to ensure that the table NUM column does not have a null value, Then query: Select ID from t where num=013. You should try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.  14. You should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon using the index for a full table scan, such as: Select ID from t where num=10 or num=20 can query: Select ID from t where Num=10union allselect ID from T where num=2015.in and not in are also used with caution, otherwise it will result in a full table scan, such as: Select ID from t where num in (three-to-three) for consecutive values, can Do not use between in: The Select ID from t where num between 1 and 316. The following query will also cause a full table scan: The Select ID from t where the name like '%abc% ' 17. You should try to avoid expression operations on the fields in the WHERE clause, which causes the engine to discard full table scans using the index. For example: Select ID from t where num/2=100 should be changed to: Select ID from t where num=100*218. You should try to avoid function operations on fields in the WHERE clause, which will cause the engine to discard full table scans using the index. For example: Select ID from t where substring (name,1,3) = ' abc '--name the ID beginning with ABC should be changed to: Select ID from t where name like ' abc% ' 19. Do not be in WHThe "=" in the ERE clause is left on a function, arithmetic, or other expression operation, or the index may not be used correctly by the system. 20. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible. 21. Do not write meaningless queries, such as the need to generate an empty table structure: Select Col1,col2 into #t from T where 1=0 such code will not return any result set, but will consume system resources, should be changed to this: CREATE TABLE #t (... 22. It is a good choice to replace in with exists in many cases: Select num from a where num in (select num from B) is replaced with the following statement: Select Num from a where exists (SE Lect 1 from B where num=a.num) 23. Not all indexes are valid for queries, SQL is query-optimized based on the data in the table, and when there is a large amount of data duplication in the index columns, SQL queries may not take advantage of the index, as there are fields in the table Sex,male, Female almost every half of it, even building an index on sex does not work for query efficiency. 24. The index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary. 25. Use numeric fields as much as possible, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of queries and connections and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once. 26. Use varchar instead of char as much as possible, because the first variable-length field has a small storage space and can save storage space, and secondly, in a relatively small field, search efficiency is obviously higher for queries. 27. Do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available. 28. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources. 29. When creating a temporary table, if you insert a large amount of data at one time, you can use the SELECT INTOInstead of CREATE TABLE, avoid creating a large number of logs to speed up, and if the amount of data is small, create a table and insert it in order to mitigate the resources of the system tables. 30. Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data. 31. Before using a cursor-based method or temporal table method, you should first look for a set-based solution to solve the problem, and the set-based approach is generally more efficient. 32. Try to avoid large transaction operation and improve the system concurrency ability. 33. Try to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable. What is a stored procedure? A stored procedure is a process written by flow control and SQL statements that are compiled and optimized to be stored in the database server as long as the application is invoked. In Oracle, several linked processes can be grouped together to form a package. Stored procedure Advantage 1. Fast speed. Stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, and the general SQL statements are compiled once per execution, so using stored procedures increases the speed of the database execution. 2. Simple writing procedure. Calling a class with a stored procedure calls any stored procedure as long as 1-2 lines of code. 3. Easy to upgrade and maintain. Stored procedures make maintenance easier because it is often easier to modify a stored procedure than to modify the SQL statement in an already published component; 4. Can alleviate network bandwidth. Because you can execute SQL statements in batches instead of sending requests for hyper-payloads from the client. Especially for more complex logic, it reduces the consumption of network traffic. 5. The security and integrity of the data can be guaranteed. Stored procedures enable users who do not have permissions to access the database indirectly under control, thus guaranteeing the security of the data. Stored procedures allow related actions to occur together to maintain the integrity of the database. 6. Enhanced security. A) They can provide access to specific data by granting users access to stored procedures, rather than tables. b) Increased code security to prevent SQL injection (but not completely resolved, for example,--dml the data manipulation language, attaching to input parameters). c) The SqlParameter class specifies the data type of the stored procedure parameter, which, as part of a deep defensive strategy, validates the user-supplied value type (but is not foolproof or should be passed to the database for additional validation). Stored procedure disadvantage 1. Portability is poor. Because stored procedures bind applications to SQL Server, using stored procedures to encapsulate the business logic limits the portability of the application. If the portability of your application is important in your environment, encapsulating the business logic in an RDBMS-neutral middle tier might be a better choice. 2. Poor code readability, very difficult to maintain. 3. Cluster 4 is not supported. Large-volume adoption of stored procedures for business logic development The fatal disadvantage is that many stored procedures do not support object-oriented design and cannot encapsulate business logic in an object-oriented manner, thus creating a common, reusable business logic framework. 5. If a large number of use of stored procedures in a program system, to the time of application delivery as the user needs increase will lead to changes in data structure, then the system related problems, and finally if users want to maintain the system can be said to be difficult, and the cost is unprecedented, maintenance more troublesome. Use principle 1. It is appropriate to use stored procedures when a business is working on multiple tables at the same time. 2. Complex data processing is more appropriate for stored procedures, such as some report processing. 3. Multi-conditional multi-table joint query, and do paging processing, with stored procedures are also more suitable. 4. Use stored procedures, such as demand changes, need to maintain the time, the trouble comes. Should not be used on a large scale. 5. Proper use of stored procedures can improve the performance of our SQL queries to improve our productivity. Talk about MySQL and Oracle's paging query. How are they implemented? Mysql:1. MySQL database implementation paging is relatively simple, providing the limit function. It is generally only necessary to write directly to the SQL statement immediately after the line. 2. The limit clause can be used to limit the number of data returned by the SELECT statement, which has one or two parameters, if two arguments are given, the first parameter specifies the position of the first row returned in all data, starting with 0 (note not 1), and the second parameter specifies the maximum number of rows to return. For example: SELECT * FROM Table WHERE ... LIMIT 10; #返回前10行select * FROM table WHERE ... LIMIT 0, 10; #返回前10行select * FROM table WHERE ... LIMIT 10, 20; #返回第10-20 rows Oracle: There is no limit keyword in Oracle, but there is a rownum field, and RowNum is a pseudo-column, which is the number assigned to each line that the Oracle system automatically returns results for the query, the first behavior 1, the second behavior 2, and so on ....                   1. The first type of code is as follows: SELECT * FROM (select A.*, ROWNUM RN from (SELECT * FROM table_name) A where ROWNUM <=) where RN >= 21l one of the mostThe inner query SELECT * FROM table_name represents the original query statement without paging. ROWNUM <= 40 and RN >= 21 control the range of pages per page for paged queries. The paging query statement given above is highly efficient in most cases. The purpose of paging is to control the output result set size and return the results as soon as possible. In the above paged query statement, this consideration is mainly reflected in the where ROWNUM <= 40 sentence. L Select the 21st to 40th record there are two methods, one is shown in the above example in the second layer of the query through the rownum <= 40来 control The maximum value, at the outermost level of the query control minimum. The other way is to remove the ROWNUM <= 40 statement that queries the second layer, controlling the minimum and maximum paging values at the outermost level of the query. 2. The second type of code is as follows: SELECT * FROM (select E.*,rownum r from (SELECT * from emp ORDER BY sal Desc) e) e1where e1.r& Gt;21 and E1.r<=40;l Red parts: Sort by salary descending and query all information. L Brown part: Get the value of the Red Department query, and query the rownum of the system and specify the alias. This sentence is more critical, plays a role in the transition, first of all to calculate the rownum to the red part of the specified number, you can also be used for the blue outer part of the variable. Specifies the number of start records on the query and the criteria for ending the record. L Blue section: Specifies the record from the beginning of the first to the end of the first, remove the value of the Brown department as a variable summary of the query criteria: In most cases, the first query is much more efficient than the second one. Oracle if you want to implement a primary key self-increment. How does MySQL go about it? MySQL: It is very easy to automatically increment the primary key. You only need to add auto_increment after the primary key definition, oracle:1. Create a table, create TABLE account (aid INTEGER not NULL, Atype C HAR () not NULL, hostname CHAR (a) not NULL, adDress char ($) NOT NULL, undertaker char (20) Not NULL, underdate DATE is not NULL, constraint Pk_account primary ke Y (aid)); 2.       Create a SEQUENCE sequence: Create SEQUENCE book_sequenceincrement by 1--add several start with 1 at a time-counting from 1 to Nomaxvalue         --does not set the maximum value nocycle; --keep accumulating, not looping 3. Create a trigger: Create TRIGGER book_increase BeforeInsert on account for each rowbeginselect book_sequence.nextval Into:New.aid f Rom dual;end; database optimization: In the process of designing a commodity table in an e-commerce project, there are four fields in the Product ID, the price of the commodity, the count of the goods, the total amount of the goods, say why this design. 1. Data segmentation: E-commerce projects generally have three main business: inquiry, order and payment, which the next single involves the product ID, commodity price, commodity quantity and the amount of goods, the four attributes to create a separate table, you can reduce the impact of the query and the next business, if necessary, You can also split the query and place the order into two separate systems. 2. Data security: The amount of merchandise is calculated to save to the database, in use without going to the interface to obtain, to prevent malicious tampering with the interface caused by the low amount of the problem

　　

SQL Summary (3)---compare all