Mysql SQL statement Review 1

Retrieves a list that does not already have the same value, plus the DISTINCT keyword

SELECT DISTINCT vend_id from Products;

You can limit the number of returned results by using the limit clause

SELECT Prod_name from Products LIMIT 5;

The limit clause also has the function of offset, which is the setting from the first few data to get

SELECT Prod_name from Products LIMIT 5, 5;

Represents five data starting from the fifth data

Although the limit has the function of offset, MySQL provides the offset clause

SELECT Prod_name from Products LIMIT 5 OFFSET 5;

MySQL's index value is starting from 0.

You can sort the data by using the ORDER BY clause, which takes the name of one or more columns and sorts the output accordingly

SELECT prod_name FORM Products ORDER by Prod_name;

This clause sorts data in columns that are not retrieved

The ORDER BY clause can optionally be sorted using multiple columns, separated by commas between the selected columns.

After the ORDER BY clause is to mimic the WHERE clause

The ORDER BY clause can specify the direction of the sort (ascending and descending), the default is ascending, and if you want to change to descending you need to add desc at the end of the ORDER BY clause

The DESC keyword is applied only to the column name immediately preceding it, and can be queried using the following method

SELECT prod_name,prod_price,prod_id from the products ORDER by Prod_name Desc,prod_name;

The WHERE clause in the action symbol for equality use = Judge unequal use of <> symbols, and the programming language slightly different

Scope value checking by using the Between keyword

SELECT Prod_name,prod_price from Products WHERE Prod_price between 5 and 10;

NULL check using is NULL

SELECT Prod_name from Products WHERE Prod_price is NULL

This statement returns product data with no price

There are several logical operators in MySQL, namely the following

and OR in not

SELECT Prod_id,prod_price,prod_name from Products WHERE vend_id=1003 and prod_price<=10; Take intersection

SELECT Prod_name,prod_price from Products WHERE vend_id=1002 OR vend_id=1003;

SELECT Prod_name,prod_price from Products WHERE vend_id in (1002,1003) ORDER by Prod_name; Take intersection

SELECT Prod_name,prod_price from Products WHERE vend_id not in (1002,1003) ORDER by Prod_name; The complement of the fetch set

You can use parentheses to mark the order of calculations

The% wildcard indicates that any character appears in any order, using the LIKE operator

SELECT Prod_id,prod_name from Products WHERE prod_name like ' jet% ';

The query statement queries all prod_name fields that begin with Jet

_ Wildcard matches a single arbitrary character

SELECT Prod_id,prod_name from Products WHERE prod_name like ' _ Ton anvil ';

The query statement queries any row that ends with a ton anvil and has only one character before it

Do not overuse wildcards, and if other operations can achieve the same purpose, use a different operator.

When you do need wildcards, do not use them at the beginning of the search pattern unless absolutely necessary, so it is the slowest.

Mysql SQL statement Review 1