Left join, when two tables are connected, all rows in the left table will be returned, even if there is no matching record in the right table. Internal connection query（ select * from a join b on a.id = b.id）Query associated with (select * from a, b where a.id = b.id)Differences between However, this writing method does not conform to the specification and may only work for some databases, such as sqlserver。 It’s best not to write like this. Select * from a inner join b on a.id = b.id。 - That is, inner connection. However, if two tables are associated: select * from a,b where a.id = b.id The meaning changes, which is equivalent to: For example, if table a has 5 pieces of data and table B has 3 pieces of data, the final result will be 5 * 3 = 15 pieces of data. Note: simple select * from a,bIs the Cartesian product. This connection method id in the orders table_ If the P field cannot find a match in the persons table, it will not be listed. SELECT p.LastName, p.FirstName, o.OrderNo Inner joinInner connection is also called equivalent connection, left/right joinIs an external link. (the character sets of the two tables may be different).
For those STRINGType, and the same character set is required. And these fields used to join should be of the same type.įor example:If you want to join the decimal field with an int field, MySQL cannot use their index. In this way, MySQL will start the mechanism to optimize the SQL statement of join for you. In addition, if your application has many join queries, you should confirm that the join fields in the two tables have been indexed. The reason why join is more efficient is that MySQL does not need to create temporary tables in memory to complete this logically two-step query. If you use join to complete this work, the speed will be much faster, especially when the CustomerID is indexed in the salesinfo table, The query is as follows: This technique can use the select statement to create a query result of a single instance, and then use the result as a filter condition in another query.įor example, if we want to delete customers without any orders in the customer basic information table, we can use the sub query to first get the customer IDs of all orders from the sales information table, and then pass the results to the main query, As shown in the figure below: In this way, we can improve the performance of the database. Because in mysql, enum type is treated as numeric data, and numeric data is processed much faster than text type. If possible, try to set the field to NOT NULLIn this way, the database does not need to compare null values when executing queries in the future.įor some text fields, such as “province” or “gender”, we can define them as enum type.
Similarly, if we can, we should use MEDIUMINTinstead of BIGINTTo define the shaping field.
#Ilike mysql code#
Therefore, when creating a table, in order to obtain better performance, we can round down the width of the fields in the table as small as possible.įor example:When defining the postal code field, if it is set to char(255)，Obviously, it adds unnecessary space to the database, and even uses varcharThis type is also redundant because char(6)You can finish the task well. MysqlIt is a relational database, which can well support the storage of large amounts of data, but generally speaking, the smaller the table in the database, the faster the query executed on it. Select the most appropriate field attribute
0 kommentar(er)
