Study notes for basic query statements in MySQL

  • 2020-12-18 01:57:20
  • OfStack

1. Basic query statement
select property list from table name and view list [where conditional expression 1] [group by property name 1 [having conditional expression 2]] [order by property name 2 [asc|desc]]
2. Single table query
1) Query all fields with *


select * from  The name of the table. 

2) Query specified fields


select id . name from product ; 

Use the above example to query a specified field

3) Query specified records
where conditional expression
Example:


select *from employee where id = 1002;

The where clause is commonly used for query conditions

Comparison: =, < , < =, > , > =,! =, < > ,! > ,! <
Specified scope: between and, not between and
Specify collections: in, not in
Matching characters: like, not like
Null value: is null, is not null
Multi-conditional query: and or
4) Query with in keyword
The in keyword determines whether the value of a field is in the specified collection.

in (element 1, element 2... , element n)
Example:


select * from employee where id in (1001,1002);

If the elements in the collection are characters, single quotes are required.

5) Range query with between and
[not] between is 1, and is 2
Value 1 is the starting value and value 2 is the ending value
Example:


select * from employee where age 
bewteen 15 and 20;

6) String matching query with like
[not] like 'string';
The value of a 'string' can be a complete string, a wildcard character with a percent sign (%) or a sliding line (_).

The "%" can represent a string of any length, which can be 0.
"_" can only represent a single character.
like is equivalent to "=" for full characters.
Example:


select * from employee where homeaddr like  'Beijing %' ; 

Query all homeaddr fields with "Beijing"
The beginning of the record.


select * from employee where name like "ar_c";

Query the record for all name field values of length 4, with the first two letters "ar" and the last letter "c".
Concatenated strings can be in single or double quotes.
The wildcard character "" can be used multiple times, as in" zhao _ ".

7) Check for vacancy
is [not] null
Example:


select * from work where info is null;

Query the work table for records where the info field is empty.

8) and and or multi-conditional queries
Conditional expression 1 and conditional expression 2 [...and conditional expression n]
and means that records that meet all conditions simultaneously will be queried, and or means that records that meet only one of them will be queried.

9) The query results are not repeated
select distinct attribute name
Example:


select distinct age department_id employee;

10) Query result sorting
order by attribute name [asc|desc]
Default asc sort.
If you encounter a record with a null value in a field, note that the null value sort can be understood as the minimum value for that field.
In mysql you can specify sorting by multiple fields.
Example:


select * from employee order by id asc , age desc;


3.limit limits the number of query results
1) No starting position is specified
limit record number
If the number of records exceeds the query result, all records will be displayed without error

2) Specify the starting position
limit starting position, number of records
The starting position of the record starts at position 0.

2. Use collection function queries
The set functions include count (), sum (), avg (), max () and min ().
1) count () function
Count the number of records
Example:


select id . name from product ; 
0

With group by1


select id . name from product ; 
1

The above statements are grouped first and then counted.

2) sum () function
The sum () function is the sum function
Example:


select id . name from product ; 
2

sum () can only compute numeric type fields.
3) avg () function
The avg () function is the average function.
Example:


select avg(age) from employee;

select course,avg(score) from group by course;

4) max (), min () function
Find the maximum and the minimum.
Example:


select id . name from product ; 
4

For the maximum value of a string, the max () function is calculated using the ascii code corresponding to the character.

4. Merge query results
Use the union and union all keywords.
union merges the results of the query to 1 and removes identical records, union all simply merges to 1.

Statement 1 union|union all
2 union|union all...
n select statements;
PS: Aliases a table or field
Table alias syntax:

Table name An alias for a table


select id . name from product ; 
5

Field alias syntax:

Attribute name [as] alias
as is dispensable.


select id . name from product ; 
6


Related articles: