MySQL Learning Note (ii)

In MySQL learning note (ii), the basics of MySQL are described, and now talk about the core part of MySQL, query: SELECT statement.

1. query all fields: SELECT * from < table name >;

2. Query the specified field: SELECT <line1,line2,... > from < table name >;

3. Specify record query: SELECT <line1,line2,... > from < table name > WHERE < query conditions >;

The Where Condition judge has the following:

=,<>,!=,<,<=,>,=>,between

4. the query with in keyword is to use the IN keyword in the query condition.

For example:SELECT line1,line2 from Tab1 WHERE line1 in (100,200);

Of course, you can also add not to the opposite conditional query in front.

5. a range query with between and is the use of between and keywords in query conditions.

For example:SELECT line1,line2 from Tab1 WHERE line1 between and;

Similarly, you can add not in front of the between to make the opposite conditional query.

6. a character matching query with like: (1) matches any length of character using '% ', and of course includes 0 characters.

For example, you need to find all the line1,sql statements in TAB1 that begin with ' a ' as follows:

SELECT line1 from Tab1 WHERE line1 like ' a% ';

To find all the line1 records in Tab1 that contain ' B ', the SQL statements are as follows:

SELECT line1 from Tab1 WHERE line1 like '%b% ';

(2) match any character of a length, using the underscore ' _ ', for example:

SELECT line1 from Tab1 WHERE line1 like ' _c% ';

7. Null value query (is NULL or is isn't null):SELECT line1 from tab1 WHERE line1 is null;

8. Multi-conditional query.

(1) and keywords:SELECT <line1,line2,... > from < table name > WHERE < query conditions 1> and < query conditions 2> [and ...];

(2) or keywords:SELECT <line1,line2,... > from < table name > WHERE < query condition 1> OR < query condition 2> [and ...];

These two keywords can be used together, but the and priority is higher than or, make sure the logic is correct when used.

9. go to heavy conditions query:SELECT DISTINCT < field name > from < table name >;

Sort the results of the query: use the ORDER BY clause. For example:

(1) Sort the line1:SELECT line1 from Tab1 ORDER by line1;

(2) Sort by line1 first, then line2:SELECT * from Tab1 order by Line1,line2;

Alternatively, you can specify the sort, such as ASC (ascending, default sort), and (DESC) descending.

For example, to line1 descending, to line2 ascending:SELECT * from Tab1 ORDER by line1 DESC, line2;

One . group queries. Using the group BY keyword, the syntax is:[GROUP by field] [having < conditional expression;]

(1) When used with aggregate functions, such as Max (), MIN (), COUNT (), SUM (), AVG (), and so on. You can add the AS keyword to give these aggregate functions an alias, such as Max (line1) as Max.

For example, grouping and counting the number of groups in line1:SELECT line1,count (*) as total from Tab1 Group by line1;

If you need to display the names of the fields in the group in detail, you can use the Group_concat () keyword.

Use such as:SELECT line1,group_concat (line2), COUNT (*) as total from Tab1 GROUP by line1;

Of course, you can also use multiple field groupings, such as Group by Line1,line2;

(2) filter groups using having keywords.

Example:SELECT line1,count (*) as total from Tab1 GROUP by line1 have COUNT (line1) >100;

(3) using the WITH rollup statistic, it adds a record of the sum of records above the last column in the grouping.

For example:SELECT line1,count (*) as total from Tab1 GROUP by line1 with ROLLUP;

(4) Restrict query results with limit syntax: limit[position offset,] < rows >;

For example, the first 3 records are returned:SELECT * from Tab1 LIMIT 3;

From the 5th record, return to the next 2 records: SELECT * from Tab1 LIMIT 4,2;

Sub - query. A subquery is a query that is nested inside another query statement within a query statement. In the SELECT statement, the result of the clause is queried as the filter condition for the outer query. The commonly used operators are any (SOME), all, in, EXISTS. Of course, a subquery can be added to a select, UPDATE, DELETE statement.

(1) Subqueries with any, some keywords (that is, any of these conditions):

Select line1 from Tab1 WHERE line1 > A (select Line2 from TAB2);

(2) Subqueries with all keywords (satisfying all the conditions in the inner layer):

Select line1 from Tab1 WHERE line1 > All (SELECT line2 from TAB2);

(3) with (not) exists keyword (the system queries the subquery to determine whether the row is returned, exists is a Boolean value, and if False it is no longer queried by the outer-layer statement).

For example:select * from Tab1 where EXISTS (select Lin1 from tab2 where lin1>10);

(4) Sub-query with (not) in keyword:

Select Line1 from Tab1 where line2 in (SELECT lin1 from tab2 where lin2> 10);

(5) Subqueries using comparison operators:

Select Line1 from Tab1 where line1= (select Lin1 from tab2 where lin2= ' one ');

The results of the Union merge query. The premise of merging is that the columns of these tables must be the same as the data type, and the rows returned are unique and non-repeatable. When union All is used, duplicate results are allowed, and their query efficiency is relatively high.

Syntax: SELECT line1,...  From Tab1 UNION [all] SELECT line1,... from Tab1;

use regular expression queries. MySQL uses the RegExp keyword to specify the matching pattern of characters. For regular expressions, see the use of regular expressions in another blog post, which shows only a few examples:

(1) query for a specific character (here is the example of ' s ') that starts with a record:

SELECT * from Tab1 WHERE line1 REGEXP ' ^s ';

(2) to query for characters other than the specified character (here is an example of a character other than the query ' A-o '):

SELECT * from Tab1 WHERE line1 REGEXP ' [^a-o] ';

MySQL Learning Note (ii)