SQL statement learning

SQL statement

Query statement select

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
  • 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
<,>,<>,=,!=,>=,<= That's what I mean
between ... and ... Between two numbers
in ... In a list
not in ... Not in a list
not between ... and ... Not between two numbers


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


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


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


Common statistical functions

count Count [count(*) counts the number of rows, count(column) counts the number of non NULL columns]
min Find the row with the smallest column
max Find the row with the largest column
avg Average all rows of column
sum Sum column s


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


SELECT country FROM Websites
SELECT country FROM apps
ORDER BY country; 
  • In this way, the of multiple select ions can be merged together


  • Output everything on one line
select group_concat(id) from movies

Tags: Database MySQL SQL

Posted by Sgarissta on Mon, 16 May 2022 03:42:58 +0300