Execution sequence

from:Which datasheet do you need to retrieve data from?
where:Conditions of data in filter tables
group by:How to group the filtered data above
having:Conditions for filtering data grouped above
select:View which column in the result set, or the result of the column calculation
order by :In what order do you view the returned data?
asc Specified columns are arranged in ascending order. desc Specified columns are arranged in descending order

Select

select from
select distinct Returns different values
SELECT * FROM XXX LIMIT X; Returns the specified number
select into The queried data is added to a new table
insert into select Query and insert into existing tables

Where: Used to extract records that meet the criteria

where XXX = ''; Usually used at the end
where xxx like 'x%'with X Start
where xxx like '%x'with X Ending
where xxx NOT like '%x%'with X Not included at the beginning x
Where XXX IN (value1,value2,…); Multiple characters can be filtered
WHERE xxx BETWEEN value1 AND value2;These values can be numeric, textual, or date

LIKE Expansion:

'_a_'    //Three and the middle letter is a

'_a'    //Two and the ending letter is a

'a_'    //Two and the beginning letter is a.

Update is used to update records in tables

delect is used to delete rows in tables

delect from Table name
where XXX ;

DROP,TRUNCATE, DELETE The difference.

DROP:
DROP test;
//Delete the table test and free up the space to delete the test completely.

TRUNCATE:
TRUNCATE test;
//Delete the contents of the table test and free up space, but do not delete the definition of the table. The structure of the table is still there.

DELETE:
1,Delete specified data
//Delete data in table test that is 30 years old and country US
DELETE FROM test WHERE age=30 AND country='US';
2,Delete the entire table
//Delete only all contents in the table test, retain the definition of the table, and do not release space.
DELETE FROM test perhaps DELETE FROM test;
DELETE * FROM test perhaps DELETE * FROM test;

AS alias

join

The JOIN clause is used to combine rows from two or more tables based on common fields between these tables.
JOIN access_log
ON Websites.id=access_log.site_id (connection condition)
On only supports equivalence connection, and can be used after on, does not support <>!= etc.
Always small tables (result sets) drive large tables (result sets)

    INNER JOIN: If there is at least one match in the table, the row is returned (one must be left and right)
    LEFT JOIN: Even if there is no match in the right table, all rows are returned from the left table
    RIGHT JOIN: Even if there is no match in the left table, all rows are returned from the right table
    FULL JOIN: Returns rows as long as a match exists in one of the tables(Left or right data)

CREATE

create database;
create tables(
values,type,
);

PRIMARY KEY Primary Key

Drop

• DROP TABLE
• DROP DATABASE
• TRUNCATE TABLE Empty Table Contents

Utility function

AVG() function
AVG() Function returns the average value of a numerical column

COUNT() function
COUNT() Function returns the number of rows that match the specified condition.
//Often added to * (representing superposition) column list data

COUNT(DISTINCT column_name) 

FIRST() function
FIRST() Function returns the value of the first record in the specified column.

LAST() function
LAST() Function returns the value of the last record in the specified column.

MAX() function
MAX() Function returns the maximum value of the specified column

MIN() function
MIN() Function returns the minimum value of the specified column

SUM() function
SUM() Function returns the total number of numeric columns.

GROUP BY Sentence
GROUP BY Statements are used to group result sets according to one or more columns in conjunction with aggregation functions.

HAVING clause
//The reason for adding HAVING clause in SQL is that WHERE keywords cannot be used with aggregation functions.
HAVING The clause allows us to filter the grouped data.

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200; 

Aggregation functions cannot be used in where statements, and aggregation functions can be used in having statements

having It can be followed by aggregation functions. where Only with ordinary functions

union: Merge one or more result sets, and the merge will de-duplicate.
union all: Merge one or more result sets without duplication. (Duplicating data works)

explain to see the details