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