Basic query statements and statements

Basic query statements and statements

Query format:
Select [distinct] * (all) | field name... from table name [where condition filtering]
Queries the specified field information pname price
Select pname, price from products;

Query all fields in the table
Select * from products;

Remove records with repeated amounts
Select distinct price from products;

The as keyword used for alias query can be omitted.
Select pname as name, price as price from products;
Select pname, price from products;

In SQL statements, we can directly perform Column Operations.
Select (1 + 1 );
Select (1, 5/2 );

Query the prices of all products and increase the price by 20 RMB.
Select pname, price + 20 from products;

Conditional query statement
Format:
Select [distinct] * (all) | field name,... from table name [where condition filter]
Comparison Operators
><>=<=<> (! =)
Is null?
Logical operators
And
Or
Not
Fuzzy search
Like
%: Any number of characters
_: Single Character
Query product information called "Playboy"
Select * from products where pname = 'player ';
Select * from products where pname in ('player ');

Query products with a price of 800
Select X from products where price = 800;

Query all products whose prices are not 800
Select * form products where price! = 800;
Select * from products where price <> 800;
Seelct * from products where price not in (800 );
Select * from products where not price in (800 );

Query Information about all products whose prices are greater than 60.
Select * from products where price> 60;

Query all products whose prices range from 200 to 1000.
Select * from products where price> = 200 and <= 1000;
Using between and for Transformation
A small value must be written in front to query the date.
Select * from products where price between 200 and 1000;

Query all products whose prices are 200 or 800
SELECT * FROM products WHERE price = 200 OR price = 800;

Using in (multiple fields) for Transformation
SELECT * FROM products WHERE price IN (200,800 );

Query all products whose names start with 'xiang'
SELECT * FROM products WHERE pname = 'xiang'; -- no result
SELECT * FROM products WHERE pname LIKE 'incense %'

Query all products whose names end with 'BA'
SELECT * FROM products WHERE pname LIKE '% Ba'

Query all products whose names contain the word 'BA'
SELECT * FROM products WHERE pname LIKE '% overlord %'

Query all products with five characters in name
SELECT * FROM products WHERE pname LIKE '_____';

Query all the items whose name is the second word "think"
SELECT * FROM products WHERE pname LIKE '_ % ';

Query the null value of the product name
SELECT * FROM products WHERE pname is null;

The query item name is not a null value.
SELECT * FROM products WHERE pname is not null; -- is not empty
SELECT * FROM products where not (pname is null );

 

Sort Query
Format:
Select FIELD | * from table name [where condition filtering] [order by field [ASC] [DESC]
Ascending: ASC is ascending by default.
Descending order: DESC
Note:
Order by should be written at the end of select statement

1. sort by price (ascending)
SELECT * FROM products order by price ASC;
SELECT * FROM products order by price;

2. sort by price (in descending order)
SELECT * FROM products order by price DESC;

3. display the prices of items (de-duplicated) and sort them (in descending order)
Select distinct price FROM products order by price DESC;

4. display all information about products whose prices exceed 1000, and sort the information in descending order)
SELECT * FROM products WHERE price> 1000 order by price DESC;

5. sort by product name sort by encoding table by default
SELECT * FROM products order by pname DESC;

 

Aggregate functions:
Operate on the column. The returned result is a single value, ignoring null values.
Count: count the number of records in a column not NULL;
Sum: calculates the value and value of the specified column. If the specified column type is not a value type, the calculation result is 0;
Max: calculates the maximum value of a specified column. If the specified column is of the string type, the string sorting operation is used;
Min: calculates the minimum value of a specified column. If the specified column is of the string type, the string sorting operation is used;
Avg: calculates the average value of a specified column. If the specified column type is not a numerical value, the calculation result is 0;
Format:
Select aggregate function (field) from Table Name

1. query the total number of items.
Select count (*) FROM products; -- 13
Select count (pname) FROM products; -- ignore null value 12
Select count (price) FROM products; -- 13

2. query the total number of items with a price greater than 200
Select count (*) FROM products WHERE price> 200;

3. query the commodity table and calculate the sum of all amounts
Select sum (price) FROM products;
The result of summation of non-numeric fields is 0.
Select sum (pname) FROM products;

4. query the commodity table and calculate the average value for all amounts
Select avg (price) FROM products;

5. Calculate the maximum and minimum prices in the commodity table.
Select max (price), MIN (price) FROM products;
Select max (price) maximum value, MIN (price) Minimum value FROM products;

SELECT * FROM products;

 

Grouping query statement:
Select group field from table name group by field [having field]
Note:
Fields to be grouped must be written after select

Requirements:
Groups the data based on the same product name and sums the data of each group.

SELECT pname, SUM (price) FROM products group by pname;

-- Requirement: filter the result of grouping summation, and only display products whose summation result is greater than 1000.
/*
Where: filter results only in the query process.
Having: filter the query results of the group data again.
*/
-- First query the products whose prices are greater than 1000, and then group and sum up the products whose prices are greater than 1000.
SELECT pname, SUM (price) s FROM products WHERE price> 1000 group by pname;

SELECT pname, SUM (price) s FROM products group by pname
HAVING s> 1000;

SELECT pname, SUM (price) s FROM products group by pname
Having sum (price)> 1000;

Paging Query
You can use the keyword limit m, n
M: The page numbers 1, 2, 3, and 4 can be changed.
N: fixed number of unchanged page shards (5 entries per page)
The database data starts from 0.

-- Only the first five data entries are required.
SELECT * FROM products LIMIT 5;

-- Data starting from 0 to ending at 5 (data on the first page)
-- 5 data records starting from 0
SELECT * FROM products LIMIT 0, 5;

-- Data from 6 to 10 (data on the first page)
-- 5 data records starting from 6
SELECT * FROM products LIMIT 5, 5;

-- Data starting from 11 to 15 (data on the first page)
-- 5 data records starting from 10
SELECT * FROM products LIMIT 10, 5;