Mysqlshu Database Learning----Query

The query statement is the most used statement in the MySQL database.

Query statements are divided into several

1. Single-Table Query
2. Aggregate function Query
3. Connection Query
4. Sub-query
5. Merging queries
6. Regular expression Query

One: single-table query

SELECT property from table name [WHERE query condition ][Group By property name 1 [havinggrouping condition ]][ORDER by property 2 [ asc/desc]][ LIMIT];

WHERE Query criteria

Query criteria	Symbol or keyword	Example
Comparison	=, <, <=, >, >=,! =, <>,!>,!<	WHERE ID = 1
Specify range	Between and, not between and	WHERE ID between 2 and 3
Specify the collection	In, not in	WHERE ID in (All-in-a-
Match character	Like, not like	WHERE ID like ' 1% '
is a null value	Is null, is not NULL	WHERE ID is NULL
Multiple criteria Query	And, or	WHERE ID > 1 and ID <3

Here is the like, if it is where the id ' ID ' here is similar to ' = ' is where id = ' ID '

'% ' can represent any length string: Where ID like ' i% ', this is the match for any length string starting with ' I '

' _ ' can represent a character: Whrer ID like ' i_ ', here is a string that matches 2 lengths beginning with ' I '

Group by Property 1 Grouping

Grouped by attributes, the attributes are divided into groups, but only the first record of each group is displayed, so a separate grouping is meaningless, and using the aggregate function will be better.

Use the Group_concat (property) function: Lists the attributes specified in the group to

SELECT Group_concat (ID) from the DEMO GROUP by SEX;

Use the Count (property) function: Calculates the number of attributes specified in the grouping

SELECT COUNT (ID) from the DEMO GROUP by SEX;

Group by can also be grouped by multiple attributes

GROUP by property 1, Property 2, ... : Group BY attribute 1 First, if there are duplicate records in attribute 1 and then grouped by attribute 2, and so on

Having group conditions

Having and where are different, where conditional expressions are in effect with tables and views, and having conditional expressions are data after grouping to select groups that meet the criteria

ORDER by property 2

Sort the results of the query by property 2, by default ascending sort

Asc/desc

Order by can be set by the Sort method after

ASC: Sort in ascending order, this is the default

DESC: Descending sort

LIMIT

Limit the number of query results displayed

LIMIT 2: Show only the first 2 records in the query results

LIMIT 2, 4: Starts with the 3rd record found in the query results, displays the next 4 records, and the result set subscript starts at 0.

Two: aggregate function query

SELECT [[COUNT ()] [SUM ()] [AVG ()] [MIN ()] [MAX ()]] from table name

count (property): Calculates the number of attribute records

SUM (attribute): Calculates the sum of the value of the attribute

AVG (attribute): Calculates the average of a property's value

min (attribute): Gets the minimum value of the property

max (attribute): Gets the maximum value of the property

Three: Connection query

Internal connection query:

If 2 tables have a field that represents the same meaning, the result set of the field in 2 tables can be obtained through an inner join query

Table 1

Id

USER

Pwd

Table 2

USER

Time

SELECT ID, table 1. User,pwd,time from table 1, table 2 WHERE table 1. USER = table 2. USER

Get a result set similar

Id

USER

Pwd

Time

Outer JOIN query:

is divided into the left connection query and the right connection query, the general use of relatively few, here do not introduce

Four: Sub-query

A subquery is a query statement that is nested inside a query statement, and the result of the inner query statement can be used as the query condition of the outer query.

The subquery contains the comparison operator and some keywords in, notin, any, all,EXISTS, notEXISTS , etc.

in: a field in the result of the inner query statement that contains the condition of the outer query statement

SELECT * FROM table 1 WHERE field 1 in (SELECT Field 1 from table 2);

not in In contrast, this is not much of a description .

Any: indicates that any one of these conditions is satisfied. An outer query statement can be executed by this condition as long as any one of the results returned by the inner query statement

Select field 1 from Table 1 WHERE field 1 [[<][=][>][<=][>=][<>][!=]] Any (SELECT field 1 from table 2);

In fact, the IN keyword and the Any keyword are the same in some cases:

SELECT * FROM table 1 WHERE field 1 in (SELECT Field 1 from table 2);

SELECT * FROM table 1 WHERE Field 1 = any (SELECT field 1 from table 2);

All: indicates that all of these conditions are met. The outer query statement can be executed only if all the conditions in the results returned by the Inner query statement are satisfied.

Select field 1 from Table 1 WHERE field 1 [[[<][=][>][<=][>=][<>][!=]] All (SELECT field 1 from table 2);

The Any keyword and the all keyword mean the opposite, any is as long as one of the conditions is met, the all keyword satisfies all conditions.

EXISTS: indicates existence, when using the EXISTS keyword, the inner query statement does not return a query record, but returns a true or False value, if True (true) executes the outer query statement, and if False (false) does not execute the outer query statement.

SELECT * FROM table 1 where EXISTS (SELECT * from WHERE ...);

Not EXISTS the keyword and exists keyword mean the opposite, there is not much to do description.

Five: Regular expression query

Cond...

VI: Merge Query results

Sometimes we want to combine the results of the two SELECT statement queries, we use the union and Union all keyword

UNION: merges two SELECT statement query results and removes duplicate records.

UNIONAll: Merges the results of the two SELECT statements query.

SELECT ... [[Union] [Unionall]] SELECT ...

Seven: Alias tables and fields

In the actual use of some table name and field names are relatively long, select statements written to be a bit bloated or not easy to read, we can give table and field alias to solve the problem.

alias to table : alias of table name table

Alias Field : Alias for field name [as] field

SELECT * from TABLE1 T1 WHERE t1.id = 1;

SELECT ID as T1id from TABLE1 WHERE t1id = 1;

Mysqlshu Database Learning----Query