This is the 14th day of my participation in Gwen Challenge

Multi-table query

Cartesian product and union

  • Merge result set (row merge)

  1. What is a merged result set

    A merge result set combines the query results of two SELECT statements

  2. Two ways to merge result sets

    UNION: Duplicate records are removed while merging

    UNION ALL: Duplicate records are not removed during the merge

    select * from table1 union select * from table2;

  3. Matters needing attention

    Result of merging: The number and type of columns must be exactly the same

Application scenario: Messages and historical messages

Join query (column merge)

  1. Join queries, also known as cross-table queries, associate multiple tables to get a result set and merge multiple columns together

  2. Query two tables at the same time, default is cartesian product, [cross join]

    select * from table1,table2;

  3. The above results are basically not available, optimize

    Ensure that the primary key is consistent with the foreign key

    The primary key of the primary table is equal to the foreign key of the secondary table (referential integrity)

  4. According to the connection mode to get the best effect to meet the business

    Connection mode: internal connection, external connection, cross connection

  5. Join queries do not require the establishment of foreign keys, which are simply a means of wrapping referential integrity

Inner join on inner

Use the comparison operators (=,>,<,<>,>=,<=,! >,! <) perform the comparison operation between tables, query the data that matches the join condition, inner join only returns the data row that meets the join condition

Internal joins are divided into equivalent joins (using = joins), non-equivalent joins, and self-joins (two tables involved in a join query are the same table), depending on the comparison method used

Id = a2.pid select A1. name,a2.mobile from a as A1 inner join a as A2 on A1. id = a2.pid

Select name,a2.title from a1 join A2 on a1.id = a2.pid where 1=1;

select a1.name,a2.title from a1 , a2 where a1.id = a2.pid where 1=1;
Copy the code

Left,right,FULL [outer] JOIN

Natural join

Join is a transfer between two tables where you look for fields with the same data type and column names, and then automatically join them and return all results that match the criteria. Natural joins do not contain duplicate attributes.

select * from table1 natural join table2;

The subquery

  • A SELECT statement contains at least one complete SELECT

  • In select: displays the results of subqueries as columns

    Treat the query result as a new table

    After WHERE: Treat the result of a subquery as a condition in another SELECT

  • EXISTS is used to check whether a subquery returns at least one row of data. The subquery does not actually return any data, but returns true or false

https://blog.csdn.net/mhd2312/article/details/81037755
Copy the code

view

  • What is a view

    A view is a virtual table whose contents are generated by a query

    Just like a real table, it contains rows and columns

    The row and column data comes from the tables referenced by the query that defines the view and is generated dynamically when the view is referenced

    Simply put, a view is a table of select results

  • View feature

    No specific data is stored

    You can add, delete, change, and query the same as the basic table (add, delete, change is conditional).

    Views are generally used as queries

  • The view function

    Security: Create a view, define the data that the view operates on, and use authorization features

    Query performance improvement

    Improve data independence

    create view emp_view as select * from emp

    select * from emp_view

    Create view parameters

    ALGORITHM

    MERGE data in a real table TEMPTABLE: crystallized. Data is stored in a temporary table. Therefore, data cannot be updated.Copy the code

    View mechanism:

    Substitution: When operating on a view, the name of the view is directly replaced by the view definition. Substitution: mysql obtains the execution result of the view first, which forms an intermediate result and saves it in memory.Copy the code

    create ALGORITHM = merge view emp_view as select * from emp

  • WITH CHECK OPTION create view emp_view as select * from emp where sal < 5000 WITH CHECK OPTION

The view is not updatable

  1. Aggregation function
  2. The DISTINCT keyword
  3. The GROUP BY clause
  4. HAVING clause
  5. The UNION operator.
  6. The FROM clause contains multiple tables
  7. A non-updatable view is referenced in the SELECT statement
  8. As long as the pair data in the view is not from the base table, it cannot be modified directly