DQL language (2)
select name,boyName from beauty,boys;
Cartesian assembly occurs under the following conditions
– omit connection conditions - invalid connection condition – all rows in all tables are connected to each other • to avoid Cartesian sets, you can add valid join conditions to WHERE.
Querying data in multiple tables using joins
• write the join condition in the WHERE clause. • prefix the column name with the same column in the table
Distinguish duplicate column names
• columns with the same column name in different tables can be distinguished by table aliases. • if you use a table alias, you need to use a table alias instead of a table name in the select statement • table aliases can be up to 32 characters long, but less is recommended
Alias of table
• use aliases to simplify queries. • using table name prefixes can improve execution efficiency.
Connect multiple tables
Connecting n tables requires at least n-1 connection conditions. For example, to join three tables, you need at least two join conditions.
Connection query case
/* Meaning: also known as multi table query. When the query field comes from multiple tables, it will use join query Cartesian product phenomenon: Table 1 has m rows, table 2 has n rows, result = m*n rows Cause: no valid connection conditions How to avoid: adding valid connection conditions Classification: By age: sql92 Standard: only internal connection is supported sql99 Standard [recommended]: support inner connection + outer connection (left outer and right outer) + cross connection By function: Internal connection: Equivalent connection Non equivalent connection Self connect External connection: Left outer connection Right outer connection Total external connection Cross connect */ SELECT surface1column,surface2column FROM surface1,surface2 WHERE surface2.column=surface1.id; #1, sql92 standard SELECT column,column FROM surface1，surface2 WHERE surface2.'column'=surface1.'column'; #Three meter connection #Case: query employee name, department name and city SELECT last_name,department_name,city FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` AND city LIKE 's%' ORDER BY department_name DESC;
– inner join on – external connection • left [outer] join on right [outer] join on
Create a connection using the ON Clause
• columns with the same name are the connection conditions in the natural connection. • you can use the ON clause to specify additional connection conditions. • this connection condition is separate from other conditions. • the ON clause makes the statement more readable.
#2, sql99 syntax select column from table 1 alias connection type join table 2 alias on connection condition where filter criteria group by group column having screening criteria order by sort list
select query list from table 1 alias inner join table 2 alias on connection conditions;
Application scenario: used to query records in one table and not in another
1. Query results of external connections are all records in the main table
If there is a match from the table, the match value will be displayed; otherwise, null will be displayed
2. Left outer join, left join the main table on the left
right join the right outer join the right main table
3. Total external connection = internal connection result + no table 2 in Table 1 + no table 2 in Table 1
#Left outer SELECT b.*,bo.* FROM boys bo LEFT OUTER JOIN beauty b ON b.boyfriend_id = bo.id WHERE b.id IS NULL; #Right outer SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN t_mysql_departments d ON d.department_id = e.department_id WHERE e.employee_id IS NULL; #Total external SELECT b.*,bo.* FROM beauty b FULL OUTER JOIN boys bo ON b.boyfriend_id= bo.id; #Cross connect SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;
By subquery location: After select: Only scalar subqueries are supported from behind: Support table subquery where or having: ★ Scalar subquery (single line) √ Column subquery (multiple lines) √ Row subquery After exists (related subqueries) Table subquery Depending on the number of rows and columns in the result set: Scalar subquery (result set has only one row and one column) Column subquery (result set has only one column and multiple rows) Row subquery (result set has one row and multiple columns) Table subquery (result set is generally multi row and multi column)
#Behind where or having SELECT last_name,job_id,salary FROM t_mysql_employees WHERE job_id = ( SELECT job_id FROM t_mysql_employees WHERE employee_id = 141 ) AND salary>( SELECT salary FROM t_mysql_employees WHERE employee_id = 143 ); #from behind SELECT AVG(salary),department_id FROM t_mysql_employees GROUP BY department_id HAVING AVG(salary)=( SELECT MIN(ag) FROM ( SELECT AVG(salary) ag,department_id FROM t_mysql_employees GROUP BY department_id ) ag_dep );
select query list from table join type join table 2 on connection conditions where filter criteria group by group field Screening after having grouping order by sorted fields limit start subscript, display the number of entries;
① Put the limit statement at the end of the query statement ② Formula Number of pages to display page, number of entries per page size select query list from table limit (page-1)*size,size;