MySQL Query statement

Basic Query

Removal of duplicate records >select DISTINCT vend_id from the products;

Paging >select * from the products LIMIT 5;

>select * from the products LIMIT 0, 5;

>select * from the Products LIMIT 5, 5;

Sort (Descending) >select * from the products order by Prod_price DESC;

Sort (Ascending) >select * from the products ORDER by Prod_price [ASC];

Multi-column Sort >select * from the products order by Prod_price Asc,prod_name ASC;

Filter query Query product prices between 2 and 10 products

>select * FROM Products WHERE Prod_price >= 2 and Prod_price <= 10;

>select * FROM Products WHERE Prod_price between 2 and 10;

Find all products with a product price not equal to 2.5

>select * FROM Products WHERE Prod_price <> 2.5; >select * FROM Products WHERE prod_price! = 2.5;

Query for customers who do not have e-mail messages

>select * FROM Customers WHERE Cust_email is NULL;

Inquire about customers who have e-mail messages

>select * FROM Customers WHERE Cust_email are not NULL;

Filter Query

Search for products manufactured by suppliers 1001 and 1003 and priced at more than 10 yuan

>select * FROM products WHERE vend_id = ' 1001 ' OR vend_id = ' 1003 ' and Prod_price > 10;

>select * from the products WHERE (vend_id = ' 1001 ' OR vend_id = ' 1003 ') and Prod_price > 10;

>select * FROM Products WHERE vend_id in (' 1001 ', ' 1003 ') and Prod_price > 10;

Queries are not manufactured by suppliers 1001 and 1003

> SELECT * from the products WHERE vend_id isn't in (' 1001 ', ' 1003 ');

Fuzzy query

The "_" wildcard represents a character "%" wildcard character for 0 or one or more characters query product names that start with Jet

> SELECT * FROM Products WHERE prod_name like ' jet% ';

Query _ Ton Anvil Products

> SELECT * FROM Products WHERE prod_name like ' _ Ton Anvil '

? Do not overuse the like wildcard character, and use other operators if other operators can complete

? Wildcard search takes longer than other searches

? If you do need to use wildcards, do not place wildcards at the beginning of the WHERE clause unless absolutely necessary, and place wildcards at the beginning of the search pattern, which is the slowest search.

More basic queries

Alias for column

> SELECT vend_id as ' supplier number ' from product;

Arithmetic operations

>select quantity,item_price,quantity * Item_price as ' Total price ' from OrderItems;

Text Processing functions

Left () returns the character of the length specified

> SELECT prod_name,left (prod_name,2) from the products;

Right () returns the character of the length specified

>select Prod_name,right (prod_name,5) from the products;

Length () returns the lengths of the strings

> SELECT prod_name,length (prod_name) from the products;

Lower () converts a string to lowercase

> SELECT prod_name,lower (prod_name) from the products;

Upper () converts a string to uppercase

> SELECT prod_name,upper (prod_name) from the products;

Text Processing functions

LTrim () Remove the space to the left of the string

> SELECT Prod_name,ltrim (prod_name) from the products;

RTrim () Remove the space to the right of the string

>select Prod_name,rtrim (Prod_name) from the products;

Trim () remove left and right spaces

>select Prod_name,trim (Prod_name) from the products;

String connection

>select CONCAT (' I love ', cust_name) as ' Message ' from customers;

Date-time functions

Function	Use	Function	Use
Curdate ()	Returns the current date	Curtime ()	Return Current time
Now ()	Returns the current date and time	Date ()	Returns the date part of the DateTime
Time ()	Return the time portion of a datetime	Day ()	Returns the number of days part of a date
DayOfWeek ()	Returns a date that corresponds to the day of the week	Hour ()	Returns the hour part of the time
Minute ()	Returns the minute part of a time	Month ()	Returns the month portion of a date
Second ()	Returns the seconds part of a time	Year ()	Returns the year part of a date
DateDiff ()	Calculates the difference between two dates	Adddate ()	Add a date (in days)

Date and Time functions

Get orders for the day of 2005-9-1

>select * FROM orders WHERE order_date = ' 2005-09-01 ';

>select * FROM Orders WHERE DATE (order_date) = ' 2005-09-01 ';

Get orders for September 2005

>select * FROM orders WHERE order_date >= ' 2005-09-01 ' and order_date <= ' 2005-09-30 ';

>select * FROM Orders WHERE year (order_date) = ' 2005 ' and MONTH (order_date) = ' 9 ';

aggregate function? Min ()? Max ()? Count ()? SUM ()? The AVG () aggregate function is commonly used for statistical data when using aggregate function statistics to ignore records with a value of NULL

Aggregation functions

Find products with the highest prices

> SELECT MAX (prod_price) from the products;

Find products with the lowest price

> SELECT MIN (prod_price) from the products;

Query the sum of commodity prices

> SELECT SUM (prod_price) from the products;

Check the average price of an item

> SELECT AVG (prod_price) from the products;

Query the number of customers

>select COUNT (*) from customers;

>select COUNT (Cust_email) from customers;

Group statistics

Get the number of products each vendor offers

> SELECT vend_id,count (*) from the products GROUP by vend_id;

Get a supplier with a product quantity greater than 2

> SELECT vend_id,count (*) from the products GROUP by vend_id have COUNT (*) > 2;

Having statements used for group by filtering where to filter before grouping

Obtain a supplier with a product quantity greater than or equal to 2 and a product price greater than 10

> SELECT vend_id,count (*) from the products WHERE Prod_price > Ten GROUP by vend_id have COUNT (*) >= 2;

Query Statement Order 1. SELECT 2. From 3. WHERE 4. GROUP by 5. Having 6. ORDER by 7. LIMIT

Sub-query

Subqueries refer to queries that are nested within a query

Get the name of the customer who ordered the product number TNT2

1. Obtain the order number from the Order Details table:

> select Order_num from orderitems WHERE prod_id = "TNT2";

2. Obtain the customer ID of the order according to the order number:

> SELECT cust_id from the orders WHERE order_num in (' 20005 ', ' 20007 ');

3. Obtain the customer's name according to the customer ID:

> SELECT cust_name from Customers WHERE cust_id in (' 10001 ', ' 10004 ');

Sub-query

Select Cust_name from Customers where cust_id in (select cust_id from Orders WHERE order_num in (select Order_num from Orde Ritems WHERE prod_id = "TNT2"));

Sub-query

Get the number of orders per customer

> select Cust_id,cust_name, (select COUNT (*) from the orders WHERE orders.cust_id = customers.cust_id) from customers;

Equivalent query > SELECT ts.id as ' Stuid ', stu_name,tc.id as ' class_id ', class_name from T_student as ts,t_class as TC WHERE Ts.clas s_id = tc.id INNER JOIN query > SELECT ts.id as ' Stuid ', stu_name,tc.id as ' class_id ', class_name from t_student as TS INNER JOIN t_ Class as TC on ts.class_id = tc.id

Left (outer) Join query

> SELECT ts.id as ' Stuid ', stu_name,tc.id as ' class_id ', class_name from T_student as TS left joins T_class as TC on ts.cl ass_id = Tc.id

Right (outer) Join query

>select ts.id as ' Stuid ', stu_name,tc.id as ' class_id ', class_name from t_student as TS right joins T_class as TC on ts.cl ass_id = Tc.id

Combination Query

Query all users and companies, and display them in a result set

> select Id,name,createtime from T_user UNION SELECT id,name,createtime from T_company;

Combination Query

Query all users and companies and display them in a result set in descending order of creation time (Createtime)

> select Id,name,createtime from t_user Union select Id,name,createtime from T_company order by createtime Desc;

Combination Query

? Union must consist of two or more than two SELECT statements, with union split between statements

? Each query for union must contain the same columns, expressions, or aggregate functions

? The data type of the column must be compatible: the types do not have to be identical, but must be mutually convertible

? The union query automatically removes duplicate rows, and if this attribute is not required, you can use the Union ALL

? To sort the union result, the order BY statement must be after the last SELECT statement

>select vend_id from vendors UNION all SELECT vend_id from products;

Database engine:? InnoDB: A reliable transaction processing engine that does not support full-text search? MyISAM: is an extremely high performance engine that supports full-text search, but does not support transactional processing? Memory: function is equivalent to MyISAM engine, but because the data is stored in memory, so fast > CREATE TABLE XXX (...) Engine=innodb;

Add to table based on query records: >insert into T_tableb (val) select Val from T_tablea;

MySQL Query statement