MySQL query sorting
This note is the learning content of Alibaba cloud Tianchi Longzhu plan SQL training camp. The link is: https://tianchi.aliyun.com/specials/promotion/aicampsql ；
1.1 use of select
Use format
select Listing as 'alias', Listing ,.....from Table name where query criteria;
Relevant rules
An asterisk () means all columns.
Line breaks can be used in SQL at will, which does not affect the execution of statements (but empty lines cannot be inserted).
When setting Chinese aliases, you need to use double quotation marks (").
Use DISTINCT in a SELECT statement to remove duplicate rows.
Comments are used to identify descriptions or precautions in SQL statements. They are divided into 1-line comments "--" and multi line comments "/ * /.
operator
Arithmetic operator
Meaning operator
Addition+
Subtraction-
Multiplication*
Division/
Comparison operator
=Greater than or equal to
greater than
< = less than or equal to
< less than
Operator meaning
=Equal to
< > and ~ are not equal
Constants or expressions can be used in the SELECT clause.
When using comparison operators, be sure to pay attention to the position of unequal sign and equal sign.
In principle, the data of string type shall be sorted according to the dictionary order and shall not be confused with the size order of numbers.
When you want to select NULL records, you need to use the IS NULL operator in the conditional expression. When you want to select records that are not NULL, you need to use the IS NOT NULL operator in the conditional expression.
-- SQL Operational expressions can also be used in statements
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
FROM product;
-- WHERE Clause can also be used in conditional expressions
SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price-purchase_price >= 500;
/* Use unequal sign on string
First create chars and insert data
SELECT a SELECT statement greater than '2'*/
-- DDL: Create table
CREATE TABLE chars
(chr CHAR(3)NOT NULL，
PRIMARY KEY(chr));
-- Select greater than'2'Of data SELECT sentence('2'As string)
SELECT chr
FROM chars
WHERE chr > '2';
-- selection NULL Record of
SELECT product_name，purchase_price
FROM product
WHERE purchase_price IS NULL;
-- Select not as NULL Record of
SELECT product_name，purchase_price
FROM product
WHERE purchase_price IS NOT NULL;
Logical operator
3.1 NOT operator
When you want to express "NOT...", in addition to the < > operator mentioned above, there is another operator that represents negation and is more widely used: NOT.
NOT cannot be used alone, for example:
-- Select a record with a sales unit price greater than or equal to 1000 yen SELECT product_name, product_type, sale_price FROM product WHERE sale_price >= 1000; -- To code listing 2-30 Add to query criteria NOT operator SELECT product_name, product_type, sale_price FROM product WHERE NOT sale_price >= 1000;
3.2 AND operator and OR operator
When you want to use multiple query criteria at the same time, you can use the AND OR operator.
AND is equivalent to "AND", similar to the intersection in mathematics;
OR is equivalent to "OR", similar to the union set in mathematics.
Note: the AND operator takes precedence over the OR operator (without parentheses to increase the priority)
For SQL, there are three logical operation results
True or false unknown
The results of logical operations on and NULL are unknown
Part I exercises
2.1 write an SQL statement and select the goods with "registration date (register after April 28, 2009)" from the product table. The query result should include two columns: product_name and register_date.
SELECT product_name, regist_date from product where regist < = '2009-04-28';
2.2 please state the returned results when executing the following three SELECT statements on the product table.
SELECT * FROM product WHERE purchase_price = NULL; --The result is product In the table purchase_price by NULL Data for all fields of the. SELECT * FROM product WHERE purchase_price <> NULL; --The result is product In the table purchase_price Not for NULL Data for all fields of the. SELECT * FROM product WHERE product_name > NULL; --It is empty. Because it cannot be determined, each data is not satisfied where Conditions.
Aggregate query
The five most commonly used aggregate functions
COUNT: calculate the number of records (rows) in the table
SUM: calculate the total value of the data in the value column in the table
AVG: calculate the average value of the data in the value column in the table
MAX: find the maximum value of data in any column in the table
MIN: find the minimum value of data in any column in the table
In addition, there are many functions. You can refer to the rookie tutorial.
Common rules
The result of the COUNT function varies according to the parameters. COUNT() will get the number of data rows containing NULL, while COUNT (< column name >) will get the number of data rows other than NULL.
The aggregate function excludes NULL. The exception to COUNT() does not exclude NULL.
The MAX/MIN function is applicable to almost all data type columns. The SUM/AVG function is only applicable to numeric type columns.
When you want to calculate the type of value, you can use DISTINCT in the parameters of the COUNT function.
You can remove duplicate data by using DISTINCT in the parameters of the aggregate function.
GROUP BY statement on table
Use group by to group fields
SELECT <Column name 1>,<Column name 2>, <Column name 3>, ...... FROM <Table name> GROUP BY <Column name 1>, <Column name 2>, <Column name 3>, ......; --Group by column name 1 first, then by 2 and 3
When a value of an aggregated column is null, it is counted as a specific group. At this time, null will be processed as a group of special data
GROUP BY writing position
There are strict requirements for the clause writing order of GROUP BY. Failure to comply with the requirements will lead to the failure of normal SQL execution. The current clause writing * * * * order is:
1.SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
The first three items are used to filter data, and GROUP BY processes the filtered data
HAVING
The usage of HAVING is similar to where. The feature of HAVING is that the HAVING clause is used to filter groups. You can use numbers, aggregate functions and column names (aggregate keys) specified in GROUP BY.
HAVING can realize the function of where, but where cannot realize the filtering of groups after HAVING GROUP BY.
-- number SELECT product_type, COUNT(*) FROM product GROUP BY product_type HAVING COUNT(*) = 2; -- Wrong form (because product_name Not included in GROUP BY Aggregate key (in) SELECT product_type, COUNT(*) FROM product GROUP BY product_type HAVING product_name = 'ball pen';
ORDER BY sort
SELECT <Column name 1>, <Column name 2>, <Column name 3>, ...... FROM <Table name> ORDER BY <Sort base column 1>, <Sort base column 2>, ......
By default, the fields are sorted in ascending order (ASC) and DESC in descending order
-- Descending order SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price DESC; -- Multiple sort keys SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price, product_id;--Arrange according to the first field first. If equal, arrange according to the second field -- When the column name used for sorting contains NULL When, NULL It will be summarized at the beginning or end. SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY purchase_price;
Use of aliases
The alias defined in the SELECT clause cannot be used in the GROUP BY clause, but it can be used in the ORDER BY clause. Why not in GROUP BY and in ORDER BY?
This is because when SQL uses the HAVING clause, the execution * * * * order of the SELECT statement is:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
The execution order of SELECT is after the GROUP BY clause and before the ORDER BY clause. That is, when using an alias in ORDER BY, you already know the existence of the alias set by SELECT, but you don't know the existence of the alias when using an alias in GROUP BY, so you can use an alias in ORDER BY, but you can't use an alias * * * * in GROUP BY.
exercises
2.2.1 errors in select
SELECT product_id, SUM(product_name) --book SELECT There is an error in the statement. FROM product GROUP BY product_type WHERE regist_date > '2009-09-01'; --1.sum The aggregated field is a numeric type, and product_name Obviously not. --2.where Should be group by before --3.group by Fields and select The difference should be product_id.
2.2.2 please write a SELECT statement to find the commodity type whose total value of sales unit price (sales_price column) is 1.5 times greater than the total value of purchase unit price (purchase_price column). The execution results are as follows.
select product_type,sum(sale_price),sum(purchase_price) from product group by product_type having sum(sale_price) > 1.5 *sum(purchase_price);
2.2.3
SELECT * FROM product order by regist_date DESC,sale_price;