SQL statement

Query statement select

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• select is a query statement, followed by commas to separate multiple columns, and * indicates all columns
• Which form is it from after from
• where add conditional restrictions
• Limit limit the number of displays
• Offset indicates the backward offset. When used together with limit, it is equivalent to the restriction from the origin (for example, 1-5 is displayed, and 2-6 is displayed after offset 1)

as follows

SELECT * FROM movies limit 5 offset 1

where statement

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• Can read from multiple tables
• And and or can be used in where

as

SELECT * FROM movies where director=='John Lasseter' and year>2000

• Note that mysql is case insensitive. If you want to distinguish, you need to add the BINARY keyword

SELECT * FROM movies where BINARY director=='John Lasseter' and year>2000

• You can also use expressions

SELECT * FROM employees where building REGEXP 'w$'

like statement

• Fuzzy query

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• like can be followed by where
• Like is usually used with% (wildcard, like * in regular). If there is no wildcard, it is equivalent to=
• You can do it with and or

as

SELECT * FROM movies where year like "%5"

distinct statement

SELECT DISTINCT column, another_column, ...
FROM mytable
WHERE condition(s);

• distinct will eliminate the duplication, and the duplication will make each appear only once, and there will be no duplication

as

SELECT distinct year FROM movies;

Sort statement order

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][default ASC]], [field2...] [ASC [DESC][default ASC]]

• You can use ASC and DESC keywords to sort in ascending or descending order. The default is ascending order
• You can also add a where... like statement to add conditions

SELECT * FROM movies order by year DESC

join statement

• Joint multi table query

NULL value

• Nothing is NULL. For example, if there is no column name, it is NULL

where column is NULL

regular expression

• If you want to use regular expressions, you need the REGEXP operator

function

Common statistical functions

as

SELECT sum(building) FROM employees;

Grouping statistics

Statistics by grouping

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, ... 
FROM mytable 
WHERE constraint_expression
GROUP BY column;

SELECT sum(years_employed) FROM employees
group by building

union statement

• union usually connects multiple select statements, and multiple select statements will delete duplicate data

as

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country; 

• In this way, the of multiple select ions can be merged together

group_concat()

• Output everything on one line

select group_concat(id) from movies