Mysql - DQL language

DQL language (2)

multi-table query

Cartesian set

Syntax:

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.

Equivalence / connection

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;

join link

classification

– 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.

Syntax:

#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

Internal connection

select query list from table 1 alias
 inner join table 2 alias
 on connection conditions;

External connection

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;

Common functions

Character function

Digital function

Date function


Subquery

classification

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)

code

#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
	);

Paging query

grammar

	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;

characteristic

① 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;

Tags: less

Posted on Thu, 18 Jun 2020 04:00:00 -0400 by rkm11