Mysql-3 retrieval data (I), mysql-3 retrieval data

Mysql-3 retrieval data (I), mysql-3 retrieval data
SELECT statement

SELECT to retrieve table data, at least two pieces of information must be provided -------- what to SELECT and where to SELECT.

Retrieve a column

SELECT prod_name FROM products;

The preceding statement uses the SELECT statement to retrieve a column named prod_name from the products table.

Retrieve multiple columns

SELECT prod_id, prod_name, prod_price FROM products;

Retrieve all columns

SELECT * FROM products;

Retrieve different rows

SELECT vend_id FROM products;

Select returns 14 rows, but there are only four suppliers in the Table. Because the products table lists 14 products, how can we retrieve the list with different values?

Select distinct vend_id FROM products;

Limit results

SELECT prod_name FROM products LIMIT 5;

SELECT prod_name FROM products LIMIT 5, 5;

Output 5 rows starting from the fifth line

The start position of the first digit, and the second number is the number of retrieved rows.

 

SELECT prod_name FROM products LIMIT 4 OFFSET 3;

Output four rows starting from the third row

 

Use a fully qualified output table name

SELECT products. prod_name FROM products;

 

Sort and retrieve data sort data

SELECT prod_name FROM products order by prod_name;

Mysql sorts the prod_name columns alphabetically.

Sort by multiple

SELECT prod_id, prod_price, prod_name FROM products order by prod_price, prod_name;

First, sort by price and name. when the price is the same, it will be sorted by name.

If all data values in the prod_price column are unique, they are not sorted by prod_name.

Set sorting direction

BY default, order by is arranged in ascending ORDER. If ORDER is arranged in descending ORDER, DESC must be marked;

SELECT prod_id, prod_price, prod_name FROM products order by prod_price DESC;

SELECT prod_id, prod_price, prod_name FROM products order by prod_price DESC, prod_name;

Sort the prices in ascending order.

Unlike DESC, ASC does not have much effect, because ASC is sorted in ascending order by default.

 

SELECT prod_price FROM products order by prod_price desc limit 2;

Find out the values of the two most expensive items

 

Use the where clause to filter data

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

Query the names and prices of products with prices equal to 2.50

Operator	Description
=	Equal
<>	Not equal
! =	Not equal
<	Less
>	Greater
<=	Less than or equal
> =	Greater than or equal
BETWEEN	Between the specified two values

Check single value

SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses ';

SELECT prod_name, prod_price FROM products WHERE prod_price <10;

SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

Query all products not manufactured by 1003 suppliers

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

Query the names and prices of products with prices between 5.

 

Where clause of data filtering combination

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1002 OR vend_id = 1003;

Computing order

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price> = 10;

From the above results, there are two results with less than 10 values. Obviously, the returned results are not filtered as expected. Why? The reason is that the order of calculation, before processing the OR operator, SQL first processes the AND operator. When SQL sees the where clause above, it is understood that any price made by 1003 of the supplier is more than 10 USD, including 10 USD, or 1002 of the products provided by the supplier, regardless of the price. In other words, operators are incorrectly combined because of the higher priority in the AND calculation order. The correct operation method is as follows:

SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price> = 10;

 

IN Operator

The IN operator is used to specify the condition range. Each condition IN the range can be matched.

SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) order by prod_name;

The usage of IN and OR is a bit similar. The above can also be written as follows:

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 order by prod_name;

 

NOT Operator

There is only one use of the not operator in the where clause, that is, all the conditions after the where clause are denied.

SELECT prod_name, prod_price FROM products WHERE vend_id not in (1002,1003) order by prod_name;

 

 

All the query commands used above

SELECT prod_name FROM products;SELECT prod_id,prod_name,prod_price FROM products;SELECT * FROM products;SELECT vend_id FROM products;SELECT DISTINCT vend_id FROM products;SELECT prod_name FROM products LIMIT 5;SELECT prod_name FROM products LIMIT 5 , 5;SELECT prod_name FROM products LIMIT 1 , 1;SELECT prod_name FROM products LIMIT 4 OFFSET 3;SELECT products.prod_name FROM products;SELECT prod_name FROM products ORDER BY prod_name;SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price , prod_name;SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC;SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC, prod_name;SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 2;SELECT prod_name ,prod_price FROM products WHERE prod_price = 2.50;SELECT prod_name ,prod_price FROM products WHERE prod_name = 'fuses';SELECT prod_name,prod_price FROM products WHERE prod_price <10;SELECT vend_id , prod_name FROM products WHERE vend_id <> 1003;SELECT prod_name , prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1003 AND prod_price <=10;SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1002 OR vend_id = 1003;SELECT prod_name , prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >=10;SELECT prod_name , prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10;SELECT prod_name , prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;SELECT prod_name , prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 ORDER BY prod_name;SELECT prod_name , prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;