MySQL Learning 14: Working with records in data sheets (ii)

four querying records

The most common use of records in a data table in a MySQL database is a recorded query, and the records in the Operation data table are mostly recorded queries. Find

The syntax structure of the record is:

SELECT select_expr [, select_expr ...]

        [

From Table_refereneces

[WHERE Where_condition]

[GROUP by {col_name | position} [ASC | DESC],...]

[Having where_condition]

[ORDER by {col_name | expr | position} [ASC | DESC],...]

[LIMIT {[offset,] row_count | row_count offset Offset}]

];

(1) The simplest query command

1) Display the current server version

SELECT VERSION ();

2) display the current date time

SELECT now ();

3) Show Current user

SELECT USER ();

4) Display the current database

SELECT databae ();

Look at two examples:

(2) query expression parsing

For query expression select_expr, each expression represents the desired column and must have at least one. Multiple columns are separated by commas.

Star Number (*) indicates all columns. Table_name.* can represent all the columns of a named table. A query expression can use [as] alias_name to give it a column name. Don't

name can be used to GROUP By,order by or HAVING clause.

Example:

1) View records for all columns in the data table

SELECT * from users;

SELECT * from Users3;

2) View the records for the columns specified in the data table

Look at the ID field in the datasheet users3 and the two column records of the Username field, where the front and back position of the query expression you wrote is not the same, and the query

The result is not the same. Here's an example:

SELECT id,username from Users3;

SELECT Username,id from Users3;

3) Use table_name.* to represent all columns of a named table

SELECT users3.id,users3.username from Users3;

The above example does not reflect the benefits of such a query, in the future we learn the connection between multiple tables query, so the benefits of writing will immediately reflect

Out, about the connection between the multi-table query we will follow up in-depth study.

4) The query expression can be given a column name using [as] alias_name

SELECT ID as userid,username as uname from Users3;

4) where statement for conditional query

the expression of a conditional expression means that filters the records and displays all records if no WHERE clause is specified. In the Where table up-

, you can use the functions or operators supported by MySQL. Here do not do too much introduction, and then into the line of detailed analysis.

SELECT id,username from Users3 WHERE id% 2 = 0;

5) GROUP BY statement groups query results

[GROUP by {col_name | position} [ASC | DESC],...]

GROUP BY Statement grouping displays you can also specify the name of the column and the location of the column. ASC indicates that the displayed query results are sorted in ascending order by default

column, desc indicates descending row column.

SELECT * from Users3;

GROUP BY Gender:

SELECT sex from Users3 GROUP by sex;

Or select sex from the Users3 GROUP by 1;

6) Having a statement sets the grouping condition

Group conditions: [Having where_condition]

SELECT sex,age from Users3 GROUP by 1 have age >;

SELECT sex from Users3 GROUP by 1 have count (ID) > 12;

SELECT sex from Users3 GROUP by 1 have count (id) > 1;

7) Order BY statement sort query Results

[ORDER by {col_name | expr | position} [ASC | DESC],...] The ORDER by statement can also specify the name of the column by grouping the display

the position of the expression and the column. ASC Indicates the default ascending order, and DESC indicates descending order.

Example:

SELECT * from Users3;

SELECT * from USERS3 ORDER by ID DESC;

Two fields to sort

SELECT * from Users3 ORDER by Age,id DESC;

The first choice is to sort in ascending order in the Age field, and if there are duplicate numbers, start with the descending order of the ID field.

Out of the results above. It is important to note that if the first specified field cannot be sorted, only the second field will be arranged;

The first field you specify can be sorted, and the second field is subject to the situation.

8) Limit statement limits the number of queries

Limit the number of query results returned: [Limit {[offset,] row_count | row_count offset Offset}] The default is to return all records because

The number of returned query results needs to be specified by us. The most important thing to note here is that records are recorded starting at index 0.

Example:

1 Specifies a number that returns the number of query results, of course, starting at index 0.

SELECT * from Users3 LIMIT 2;

2 Specifies two digits, the first number represents the start index where the query's demerit is returned, and the second number represents the number of results that are queried.

SELECT * from Users3 LIMIT 3,2;

3GROUP by and limit compound statements

SELECT * from USERS3 ORDER by ID DESC;

SELECT * from USERS3 ORDER by ID DESC LIMIT 2, 2;

The result here is that the result of a grouped query is either a positive or a reverse order, and the index position of the limit restriction statement is the bit from index 0.

Start , the index in this case is the index position of the record, that is, the initial position of the record begins.

MySQL Learning 14: Working with records in data sheets (ii)