MySQL Query detailed

MySQL Query detailed

--The following excerpt from the Marco Education Classroom

SELECT

Select query process

Client SELECT statement <==> query Cache--cache miss--interpreter--preprocessor--parse tree (multiple access paths)

--query optimizer (preferred path)--Query execution plan--query execution engine--API call the storage engine--data--the query execution engine returns results and uses hashes to store the query cache

Select Now (), which does not require a query cache, queries the current cache

Multiple MySQL servers, how to increase the cache hit ratio of MySQL

The front-end program can use a consistent hashing algorithm to enable the same SELECT statement to be sent to the same MySQL server

Third-party caches can also be introduced to cache the execution results of a select rather than using MySQL's own cache. The program can indicate whether the SELECT statement has a cached result in the first-to-memcached query, and no interaction with the MySQL server. When introducing multiple memcached, it is also necessary to use a consistent hashing algorithm to ensure that the SELECT statement is hit

If the scale of business continues to grow, you can use the following architecture

--Cache server

Application--mysql read/write Detach server (self-developed, using consistent hashing)--mysql a master multiple slave server architecture

SQL statement Format

SELECT ... From ... ORDER by ...

SELECT ... From ... GROUP by ... Having ...

SELECT ... From ... WHERE ...

SELECT ... From ... Having ...

Select statement Full execution process

SELECT ... From ... WHERE ...  GROUP by ... Having ... ORDER by ... SELECT Field LIMIT ...

Where is the selection, the "select field" is the projection

===

MySQL single-table query--select detailed

Help SELECT

DISTINCT: Data deduplication

SELECT DISTINCT Gender from students;

Sql_cache: Display specified store query results in cache

Sql_no_cache: Display specified store query results are not cached

The result of a select is cached if it is cached, otherwise it is not cached

Query cache has related parameters

SHOW GLOBAL VARIABLES like ' query% ';

Query_cache_type on: Query caching feature opens

Query_cache_type DEMAND: Specified in the command whether to cache, Sql_cache will not cache

Query_cache_size 16M: By default, you can introduce memcached

Cache Hit Ratio: query hit count/total number of queries

SHOW GLOBAL STATUS like ' qcache% '; query hit count

Qcache_hits 2;

SHOW GLOBAL STATUS ' com_se% '; Total number of queries

Com_select 42;

Query cache needs to be warmed up to increase hit ratio

Field display aliases: Col1 as ALIAS1, modified only when displayed

SELECT Name as stuname from students;

Where clause, indicating the filter condition to implement the selected function; Boolean type

Arithmetic operators: +,-, *,/,%

comparison operator:=,!=,<>,<=>; null value security comparison >,>=,<,<=

SELECT name,age from students WHERE age+30 > 50;

Between lower and upper limit

In (element 1, Element 2)

SELECT Name,age from Students WHERE age in (18,100)

is null/is not NULL: Determines whether the value is empty

SELECT Name,classid from students WHERE ClassID is NULL;

SELECT Name,classid from students WHERE ClassID = NULL; (Error)

Like: Fuzzy match, you can use the wildcard character% to represent any number of characters, _ any single character

Rlike: Not Proficient

REGEXP: Unskilled no, match string can use regular expression writing mode

Logical operator: not, and, OR, XOR

Group: "group" Query results according to specified criteria for "aggregation" operations

AVG (), Max (), Min (), count (), SUM ()

SELECT avg (age), Gender from students GROUP by Gender;

Grouped by sex, then there are several people in each group, calculating the average age

SELECT avg (age) as Aage,gender from students GROUP by Gender have aage>20;

Show average age >20 group only

SELECT count (Stuid) as Numberofstu from students GROUP by ClassID;

SELECT count (Stuid) as Numberofstu,classid from students GROUP by ClassID;

Count the number of classmates in each class

SELECT count (Stuid) as Numberofstu,classid from students GROUP by ClassID have numberofstu>2;

Count each class how many students, and only show the class number of more than 2

Having: Specify the filter condition for the result of grouping aggregation operation;

ORDER BY: Sorts the results of the query according to the specified field;

Ascending: ASC, descending: DESC

SELECT count (Stuid) as Numberofstu,classid from students GROUP by ClassID have numberofstu>2 ORDER by Numberofstu [DE SC];

limit [[Offset,]row_count]: Limits the number of rows to the displayed result

SELECT Name,age from students ORDER by age DESC LIMIT 10;

SELECT Name,age from students ORDER by age DESC LIMIT 9, 10; Remove the first 9 and then take 10

For UPDATE: "Write lock", "exclusive lock" is applied to data in query results, other users cannot read and write

Lock for SHARE MODE: "read lock" is applied to the data in the query results, other users can read only

Exercise: 54min

Multi-Table Query

Cross join: Cartesian product, the most resource-consuming. Usually the inter-table connection is connected between the two tables in relation to the specified field

SELECT * from Students,teachers;

Students number of table rows *teachers table rows

Equivalent connections: The most common way

The connection of the product of Descartes is of little significance, so how is it meaningful to connect? The TID in the Teacherid and teachers tables in the students table represents the teacher ID, which is the same meaning, so you can make table connections based on this field

SELECT * from Students,teachers WHERE students. Teacherid=teachers. TID;

At this point, you can only display a specific field to form a corresponding relationship, find a classmate corresponding teacher

SELECT S.name as stuname,t.name as teaname from students as s,teachers as T WHERE S.teacherid=t.tid; Table can also take aliases

EXPLAIN SELECT S.name as stuname,t.name as teaname from students as s,teachers as T WHERE s.teacherid=t.tid\g

SELECT * from students;

SELECT * from classes; both tables have classid fields, which combine the two tables to query the correspondence between students and class names

SELECT S.name,c.class from students as s,classes as C WHERE s.classid=c.classid;

In addition, there is no equivalent connection, natural connection. The above two plus the equivalent connection three are called Inner joins

External connection

Left OUTER join: When the left table is equivalent to the right table, if the left table row data corresponds to the Right table field is empty, then also display the row data of the left table

SELECT S.name,c.class from students as s,classes as C WHERE S.classid=c.classid, even if a student does not have a class, the name is displayed instead of the

From TB1 left JOIN TB2 on Tb1.col=tb2.col

SELECT S.name,c.class from students as s left JOIN classes as C WHERE S.classid=c.classid; The final result contains

Null

Null

Right outer join: When the left table is equivalent to the right table, if the right table row data corresponds to the left table field is empty, then also display the row data of the right table

From TB1 left JOIN TB2 on Tb1.col=tb2.col

SELECT S.name,c.class from students as s right JOIN classes as C WHERE s.classid=c.classid;

NULL * * * *

NULL * * * *

Full outer joins: combined left and right outer connections

NULL * * * *

NULL * * * *

Null

Null

Self-linking, establishing a connection with an equivalent relationship between a field in a table and another field

SELECT S.name,t.name from students as s,students as T WHERE S.teacherid=t.stuid;

Subquery: Query results based on a statement are queried again, similar to the view

Not recommended because MySQL subqueries are poorly implemented

=

Subquery used in the WH ERE clause

(1) Subqueries for comparing expressions: subqueries can only return a single value;

Query for students greater than average age

Select Name,age from Students WHERE age> (select Avg. from students;)

EXPLAIN Select Name,age from students WHERE age> (SELECT avg (age) from students;) \g

(2) subqueries used in in: subqueries should return one or more value-forming lists;

The teacher's age is the same as the student's age

Select Name,age from Students WHERE-age-In (select-age from Teachers);

(3) for exists

=

For subqueries in the FROM clause, this is similar to the view, where the view is to store the query sentence and the from subquery is not stored

Use format: SELECT tb_alias.col1,... From (SELECT clause) as Tb_alias WHERE clause;

Select Aage,classid from (select AVG (age) as Aage,classid from students WHERE ClassID are not NULL GROUP by ClassID) as S W Here s.aage>30;

===

Federated queries

Select Name,age from students UNION SELECT name,age from teachers;

MySQL Query detailed