1.1 arithmetic operators
1.2 comparison operators
|<>||And ~ are not equal|
|>=||Greater than or equal to|
|<=||Less than or equal to|
2 exercise part 1 - query
2.1 writing the product table
The SQL code is as follows:
CREATE TABLE product (product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER DEFAULT 0, purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id)); INSERT INTO product VALUES('0001', 'T shirt', 'clothes', 1000, 500, '2009-09-20'); INSERT INTO product VALUES('0002', 'Punch', 'Office Supplies', 500, 320, '2009-09-11'); INSERT INTO product VALUES('0003', 'motion T Shirt', 'clothes', 4000, 2800, NULL); INSERT INTO product VALUES('0004', 'kitchen knife', 'kitchenware', 3000, 2800, '2009-09-20'); INSERT INTO product VALUES('0005', 'pressure cooker', 'kitchenware', 6800, 5000, '2009-01-15'); INSERT INTO product VALUES('0006', 'Fork', 'kitchenware', 500, NULL, '2009-09-20'); INSERT INTO product VALUES('0007', 'Dish cleaning board', 'kitchenware', 880, 790, '2008-04-28'); INSERT INTO product VALUES('0008', 'ball pen', 'Office Supplies', 100, NULL, '2009-11-11');
The results are as follows:
2.2 query statement
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 not regist_date = '2009-01-15' and not regist_date = '2008-04-28';
2.3 several incorrect query formats of null
Please state the results returned when the following three SELECT statements are executed on the product table
SELECT * FROM product WHERE purchase_price = NULL; SELECT * FROM product WHERE purchase_price <> NULL; SELECT * FROM product WHERE product_name > NULL;
None of the above three query results can get any records. The results are as follows:
When selecting 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.
2.4 conditional query statement + operator
The SELECT statement can take out the goods whose "sales price is more than 500 yen higher than purchase price" from the product table. Please write two SELECT statements that can get the same result. The execution results are as follows.
The SQL statement is as follows:
SELECT product_name, sale_price, purchase_price FROM product WHERE sale_price-purchase_price >= 500; SELECT product_name, sale_price, purchase_price FROM product WHERE sale_price-500 >= purchase_price; SELECT product_name, sale_price, purchase_price FROM product WHERE sale_price >= purchase_price +500;
2.5 condition query + query results are set to another column
SELECT product_name, product_type,sale_price*0.9-purchase_price as profit FROM product WHERE sale_price*0.9-purchase_price>100 and (product_type='Office Supplies' or product_type='kitchenware');
The results are as follows:
3 aggregation & sorting
3.1 aggregate function
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
3.2 GROUP BY statement under where condition
SELECT purchase_price, COUNT(*) FROM product WHERE product_type = 'clothes' GROUP BY purchase_price;
There are strict requirements for the writing order of GROUP BY clauses. Failure to comply with the requirements will lead to the failure of normal SQL execution. The current order of clauses 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
3.3 using HAVING to get a specific group
-- number SELECT product_type, COUNT(*) FROM product GROUP BY product_type HAVING COUNT(*) = 2;
3.4 ORDER BY sorting
-- 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; -- 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;
Question: 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 can't it be used in GROUP BY and can it be used in ORDER BY?
This is because when SQL uses the HAVING clause, the order of SELECT statements 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 it is in ORDER
When using an alias in BY, you already know the existence of the alias set in SELECT, but you don't know the existence of the alias when using an alias in GROUP BY, so you can't use it in ORDER
Aliases can be used in BY, but not in GROUP BY
4 exercise part 2 - aggregation & sorting
4.1 please point out all syntax errors in the following SELECT statement.
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';
- SUM is used to calculate the total value of the data in the numerical column in the table. The product_name column is not numerical column data.
- Punctuation marks, such as brackets, commas, semicolons, use full angle, and half angle should be used.
- The order of GROUP BY and WHERE is reversed, and a semicolon is not required after the WHERE statement.
- 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).
4.2 HAVING filters aggregation groups
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.
SELECT product_type,sum(sale_price),sum(purchase_price) FROM product GROUP BY product_type HAVING sum(sale_price) > 1.5*sum(purchase_price);
4.3 ORDER BY sorting
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.
SELECT * FROM product ORDER BY if(isnull(regist_date),0,1),regist_date DESC, sale_price;
reference resources: DataWhale SQL team learning