This article learned from Dataware SQL tutorial
SELECT & WHERE
SELECT: SELECT the necessary data from the table
Query: the process of matching query / query and selecting necessary data through SELECT
SELECT "Field name" FROM "Table name";
WHERE: selectively extract necessary data
SELECT "Field name" FROM "Table name" WHERE "condition";
SELECT sale_price FROM product WHERE product_name='trousers';
Some SQL rules
1, An asterisk (*) represents all columns
SELECT * FROM <Table name>；
2, Line breaks can be used freely in SQL without affecting statement execution (but empty lines cannot be inserted)
3, DISTINCT can be used in a SELECT statement to remove duplicate rows
SELECT DISTINCT product_type FROM product;
4, Single line notes: –; Multiline comment: / **/
5, Use IS NULL operator when selecting NULL record; The IS NOT NULL operator is used when selecting records that are not NULL
-- 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;
6, SQL statements can use the AS keyword to set aliases for columns (double quotes are required when using Chinese)
SELECT product_id As id, product_name As name, purchase_price AS "Purchase unit price" FROM product;
7, SQL statements can use arithmetic expressions
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2" FROM product; SELECT product_name, sale_price, purchase_price FROM product WHERE sale_price-purchase_price >= 500;
8, You can use AND, OR, NOT operators
-- By using parentheses OR Operator before AND Operator execution SELECT product_name, product_type, regist_date FROM product WHERE product_type = 'Office Supplies' AND ( regist_date = '2009-09-11' OR regist_date = '2009-09-20');
9, Note that because of the existence of NULL in SQL, its logical operation is called ternary logic. In short, no matter AND OR, if there is an uncertainty, it is uncertain.
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_date >= '2009-4-28';
Note: in SQL, you can use >, <, > =, < = to compare and select dates,
2, Please state the returned results when executing the following three SELECT statements on the product table.
-- 1 SELECT * FROM product WHERE purchase_price = NULL; -- 2 SELECT * FROM product WHERE purchase_price <> NULL; -- 3 SELECT * FROM product WHERE product_name > NULL;
- Return all the NULL purchase_price columns in the product table;
- Returns all columns in the product table whose purchase_price column is not NULL;
- Error is reported, NULL cannot be compared;
3, Please write a SELECT statement and SELECT the records from the product table that meet the conditions of "office supplies and kitchenware with a profit of more than 100 yen after 10% discount on the sales unit price". The query results should include the product_name column, the product_type column and the profit after 10% discount on the sales unit price (the alias is set to profit).
Tip: a 10% discount on the sales unit price can be obtained by multiplying the value of saleprice column by 0.9, and the profit can be obtained by subtracting the value of purchase_price column from this value.
SELECT product_name, product_type, sale_price * 0.9 - purchase_price AS profit FROM product WHERE sale_price * 0.9 > 100 AND (product_type='Office Supplies' OR product_type='kitchenware');
Aggregate functions in SQL
The functions used for summary in SQL are called aggregate functions. The following five are the 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
-- Calculate the number of rows of all data (including NULL) SELECT COUNT(*) FROM product; -- calculation NULL Rows of data other than SELECT COUNT(purchase_price) FROM product; -- Calculate the total value of sales unit price and purchase unit price SELECT SUM(sale_price), SUM(purchase_price) FROM product; -- Calculate the average value of sales unit price and purchase unit price SELECT AVG(sale_price), AVG(purchase_price) FROM product; -- MAX and MIN It can also be used for non numeric data SELECT MAX(regist_date), MIN(regist_date) FROM product; -- Calculate the number of data rows after removing duplicate data SELECT COUNT(DISTINCT product_type) FROM product;
Some 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.
Group summary and sorting
GROUP BY: used for group summary
For example, we know the sales of all stores in a shopping mall (calculated by SUM). If we want to know the individual sales of each store, we need to use GROUP BY, as follows
SELECT Store_Name, SUM(Sales) FROM Store_Information GROUP BY Store_Name;
The returned result is the total sales of each store.
Writing position of GROUP BY:
- SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
The first three items are used to filter data. GROUP BY processes the filtered data, for example:
SELECT purchase_price, COUNT(*) FROM product WHERE product_type = 'clothes' GROUP BY purchase_price;
The above code is used to count the number of clothes with different purchase prices. I understand that the statistical result will contain NULL
When using aggregate functions and GROUP BY clauses, common errors are:
Columns other than the aggregate key are written in the SELECT clause of the aggregate function
When using aggregate functions such as COUNT, if a column name appears in the SELECT clause, it can only be the column name specified in the GROUP BY Clause (that is, the aggregate key).
Use the alias of the column in the GROUP BY clause
Aliases can be specified in the SELECT clause through AS, but they cannot be used in GROUP BY. In DBMS, the SELECT clause is executed after the GROUP BY clause.
Use aggregate functions in WHERE
The reason is that the aggregate function is used on the premise that the result set has been determined and WHERE is still in the process of determining the result set, so contradictions will cause errors. If you want to specify conditions, you can use the aggregate function in the SELECT, HAVING and ORDER BY clauses.
HAVING: filter groups
SELECT product_type, COUNT(*) FROM product GROUP BY product_type HAVING COUNT(*) = 2;
The following is an example of a typical error:
-- 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 the results in order
-- In descending order DESC SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price DESC; -- Ascending sort is the default SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price, product_id;
Note that when the column name used for sorting contains NULL, NULL will be summarized at the beginning or end.
When too many SQL commands are used, the order of command execution becomes particularly important. When using the HAVING clause, the order of SELECT statements is:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
The sequence is logical and not difficult to understand
1, Point out grammatical errors
SELECT product_id, SUM(product_name) FROM product GROUP BY product_type WHERE regist_date > '2009-09-01';
Columns other than aggregate keys are written in the SELECT clause of the aggregate function, and product_type is not a column in the SELECT clause;
In addition, the order of GROUP BY and WHERE is wrong.
2, Please write a SELECT statement to find the product category whose total sales unit price (sales_price column) is 1.5 times greater than the total purchase unit price (purchase_price column). The execution results are as follows.
product_type | sum | sum -------------+------+------ clothes | 5000 | 3300 Office Supplies | 600 | 320
SELECT product_type, SUM(sale_price), SUM(purchace_price) FROM product WHERE sale_price > purchace_price*1.5;
3, Previously, we used the SELECT statement to SELECT all records in the product table. At that time, we used the order by clause to specify the sorting order, but now we can't remember how to specify it. Please think about the contents of the order by clause according to the following execution results.
Answer: order by register_date;