MySQL Tutorial SQL basic Query

In fact, in the database most often used as a query operation

Basic syntax

SELECT    [All | DISTINCT | Distinctrow]    field list as field alias    [from table name    WHERE conditional expression    GROUP by field name | expression      [ASC | DESC], ... [with ROLLUP]]    [having conditional expression]    [ORDER by field name | expression      [ASC | DESC], ...]    [LIMIT {[offset,] row_count | row_count offset Offset}]  

All clauses that are used must be strictly ordered in the order shown in the syntax description. For example, a HAVING clause must precede the GROUP BY clause and precede the ORDER BY clause.

The all, distinct, and distinctrow options Specify whether duplicate rows should be returned, if not specified, the default value is all (return all matching rows), distinct and distinctrow represent deduplication (if you want to delete duplicate rows, Then all fields need to be the same)

Data preparation

CREATE TABLE IF not EXISTS score (    ID INT,--Student ID    name VARCHAR (10),--course name    score NUMERIC (4, 1));--Fractional insert Into score values (1, ' Language ', ' n '), insert into score values (1, ' math ', ' on '), insert into score values (1, ' English ', 98); INSERT INTO SC Ore values (2, ' language ', score), insert into the values (2, ' Math ', "n"), insert into score values (2, ' English ', ' n '), insert INTO score VALU ES (3, ' languages ', "); INSERT into score values (3, ' math ', +); INSERT into score values (3, ' English ', 98);

field aliases : When the data is queried, sometimes the fields of the data table do not meet our needs (multiple table queries, there may be fields with the same name), you need to rename the field

Note: Using column aliases in a WHERE clause is not allowed because the column values may not have been determined when the WHERE clause is executed.

Mysql> SELECT name, score from score; --Not using aliases +------+-------+| name | Score |+------+-------+| language | | | | mathematics | | | |    English | 98    | | chinese | |    | | mathematics | | | english | | | | chinese | | | |    Mathematics | | |   | english | 9 8    |+------+-------+9 rows in setmysql> SELECT name as ' course name ', score ' score ' from score;--using aliases, the Score field uses the AS keyword +------ ----+------+| Course Name | Score |+----------+------+| Chinese     | | |   Mathematics | |   | English     | 98   | | chinese | | |     | mathematics | | | |   English     | 90
   
    || language | | | |   Mathematics     | |  English     | 98   |+----------+------+9 rows in Set
   

Using as to explicitly specify the alias of a column, as a habit, is a good practice.

conditional filtering where

In a SELECT statement, the data is filtered according to the search criteria specified in the WHERE clause, and there are comparison and logical operators in the search condition that determine the condition, where

Comparison operators are: >,<,>=,<=,!=,<>,like,between And,in/not in

Logical operators have:&& (and), | | (OR),! (not)

When SQL executes to the WHERE clause, it is first determined from the disk according to the search criteria, if it is saved in memory, otherwise skipped.

NOTE: The WHERE clause returns a result of only 0 or 1 (either true or not), where 0 represents false,1.

Mysql> SELECT * FROM score WHERE id = 1; --Find student information with ID 1 +----+------+-------+| ID | name | Score |+----+------+-------+|  1 | language |    | |  1 | Math |    | |  1 | English | 98    |+----+------+-------+3 rows in setmysql> SELECT * FROM score WHERE id = 1 or id = 2;--Find student information with ID 1 or ID 2 +----+ ------+-------+| ID | name | Score |+----+------+-------+|  1 | language |    | |  1 | Math |    | |  1 | English | 98    | |  2 | language | |    |  2 | Math |    | |  2 | English |    |+----+------+-------+6 rows in setmysql> SELECT * from score where score between and 98;--find course scores between 95 and 98 Student Information +----+------+-------+| ID | name | Score |+----+------+-------+|  1 | Math |    | |  1 | English | 98    | |  3 | language |    | |  3 | English | 98    |+----+------+-------+4 rows in Set

grouping function GROUP BY

Group by semantics above means grouping by the field name or expression followed by, so-called grouping is to divide the data of select into several groups, the same set of groups, usually grouped for statistical analysis, so often with aggregation (statistical) function to use

The commonly used aggregation (statistics) functions are:

COUNT (): Returns the number of non-null values in the row retrieved by the SELECT statement, and if no matching rows are found, COUNT () returns 0,count (*) with a nonzero value

SUM (): Counts the total number of data per set, rows with null columns in the table do not participate in the calculation, or null if no matching rows are found

AVG (): Counts the average of each set of data, rows with null columns in the table do not participate in the calculation, and null if no matching rows are found

Max (): Counts the maximum value in each group and returns null if there is only a null value in the column of statistics

MIN (): Counts the minimum value in each group and returns null if there are only null values in the column of statistics

Features of aggregate functions: only one return value

mysql> SELECT name, AVG (score), SUM (score) from score GROUP by name; --statistics of the average scores and total score of each department +------+------------+------------+| name | AVG (Score) | SUM (Score) |+------+------------+------------+| Math | 94.33333 |   283.0 |      | english | 95.33333   | 286.0      | | chinese | 92.66667   | 278.0      |+------+------------+------ ------+3 rows in Set

Grouping is sorted by default on the grouped fields, where sorting refers to sorting the result set of each group, rather than each record in the grouping, and each group is actually a record after grouping.

Now that there is a requirement that you want to summarize the above results again, you might consider a federated query, but it's easy to do this with the WITH Roolup keyword provided in MySQL

mysql> SELECT name, AVG (score), SUM (score) from score GROUP by name with rollup;+------+------------+------------+| name | AVG (Score) | SUM (Score) |+------+------------+------------+| Math | 94.33333 |   283.0 |      | english | 95.33333   | 286.0      | | chinese | 92.66667   | 278.0      | | NULL | 94.11111   | 847.0      |+------+------------+------------+4 rows in Set

Compared to group by, the last line of the query has more than a summary of the average and total score. A summary of a single dimension does not reflect the benefits of rollup, and the following summarizes the ID and name

mysql> SELECT ID, name, AVG (score), SUM (score) from score GROUP by ID, name with rollup;+------+------+------------+--- ---------+| ID   | name | AVG (Score) | SUM (Score) |+------+------+------------+------------+|    1 | Math |         95.0 |       |    1 | English | 98         | 98.0 |       |    1 | language |         90.0 |       |    1 | NULL | 94.33333   | 283.0 |      |    2 | Math |         88.0 |       |    2 | English |         90.0 |       |    2 | language |         92.0 |       |    2 | NULL |         270.0 |      |    3 | Math |        100.0 |      |    3 | English | 98         | 98.0 |       |    3 | language |         96.0 |       |    3 | NULL | 98         | 294.0 |      | NULL | NULL | 94.11111   | 847.0      |+------+------+------------+------------+13 rows in Set

where (null, NULL) is similar to group by name with Rollup, which represents a summary of the final data

(ID, NULL) represents the aggregated result of a group of students, which represents a summary of each student's accomplishments

(ID, name) represents the aggregated result of a group of students and subjects, which represents a summary of each student's scores

MySQL extends the use of group BY, so you can use columns or operations that do not appear in the group BY statement in the select list. For example

mysql> SELECT ID, name, AVG (score), SUM (score) from score GROUP by id;+----+------+------------+------------+| ID | name | AVG (Score) | SUM (Score) |+----+------+------------+------------+|  1 | language | 94.33333   | 283.0 |      |  2 | language |         270.0 |      |  3 | language | 98         | 294.0      |+----+------+------------+------------+3 rows in Set

The characteristics of the grouping function can be seen from the above results : The return value is the first record in the group

In standard SQL, you must add the name to the GROUP by clause. If the column you omitted from the group by section is not unique in the set, do not use this feature! You will get non-predictive results. For example, the first record value in each group is based on the course name of the student who is checking the highest score, which is not what we want.

mysql> SELECT ID, name, AVG (score), MAX (score) from score GROUP by id;+----+------+------------+------------+| ID | name | AVG (Score) | MAX (Score) |+----+------+------------+------------+|  1 | language | 94.33333   | 98 |         |  2 | language | |         |  3 | language | 98         |        |+----+------+------------+------------+3 rows in Set

If you need to display each student's scores in a row, you can use the Group_concat function, which is usually used with group by, and if there is no group BY, all values in the column are returned

mysql> SELECT ID, Group_concat (score) from score GROUP by id;+----+---------------------+| ID | Group_concat (Score) |+----+---------------------+|  1 | 90.0,95.0,98.0      | |  2 | 92.0,88.0,90.0      | |  3 | 96.0,100.0,98.0     |+----+---------------------+3 rows in setmysql> SELECT ID, Group_concat (score) from score;+--- -+-----------------------------------------------+| ID | Group_concat (Score)                           |+----+-----------------------------------------------+|  1 | 90.0,95.0,98.0,92.0,88.0,90.0,96.0,100.0,98.0 |+----+-----------------------------------------------+1 row in Set

Filter group having

Having is used to filter data after grouping, for example, to query student information with an average score of less than 95, when using having, the data is already in memory.

Mysql> SELECT ID, AVG (score) from score GROUP by ID has AVG (score) < 95;+----+------------+| ID | AVG (Score) |+----+------------+|  1 | 94.33333   | |  2 |         |+----+------------+2 rows in Set

sort order by

Sort Ascending (default) or descending according to a field, depending on the proofing set

Mysql> SELECT * FROM score WHERE id = 1 ORDER by score DESC; ---Check the students ' scores by 1 and sort by high to low score +----+------+-------+| ID | name | Score |+----+------+-------+|  1 | English | 98    | |  1 | Math |    | |  1 | language |    |+----+------+-------+3 rows in Set

Quantity Limit Limit

Two modes of Use

1. Limit row_count:row_count indicates quantity, as

Mysql> SELECT * from score LIMIT 2; --Find list top two data +----+------+-------+| ID | name | Score |+----+------+-------+|  1 | language |    | |  1 | Math |    |+----+------+-------+2 rows in Set

2, LIMIT Begin,offset:begin is the starting position, offset represents the number

Mysql> SELECT * FROM Score LIMIT 2, 3; --Starting from the second, remove three data, usually for pagination +----+------+-------+| ID | name | Score |+----+------+-------+|  1 | English | 98    | |  2 | language | |    |  2 | Math |    |+----+------+-------+3 rows in Set

MySQL Tutorial SQL basic Query