Basic query statements in Mysql learn notes _mysql

1. Basic Query Statement
Select Property List from table name and view list [where Condition expression 1] [Group BY property name 1 [having conditional expression 2]] [ORDER BY property name 2 [Asc|desc]]
2. Single-table query
1) Use * query all fields

SELECT * from table name;

2) Query specified fields

Select Id,name from Product;

Use the example above to query the specified field

3) query specified record
Where Condition expression
Instance:

Select *from Employee WHERE id = 1002;

WHERE clause common query criteria

Comparison: =, <, <=, >, >=,!=, <>,!>,!<
Specified range: Between and, not between and
Specify collection: In, not in
Matching characters: like, not like
Null value: Is NULL, is not NULL
Multi-condition query: And OR
4 Query with in keyword
The In keyword can determine whether a field's value is in the specified collection.

[NOT] in (element 1, Element 2, ..., element N)
Instance:

SELECT * FROM employee where ID in (1001,1002);

If the elements in the collection are characters, you need to enclose the single quotation mark.

5 range query with between and
[NOT] between takes a value of 1 and takes a value of 2
The value 1 is the starting value, and the value 2 is the ending value
Instance:

SELECT * FROM employee where age 
bewteen and 20;

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

"%" can represent arbitrary length strings, which can be 0 in length.
' _ ' can only represent a single character.
The full character is like the equivalent of "=".
Instance:

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 for all Name field values of 4, the first two letters are "AR" last Letter "C" record.
Wildcard strings can be in single or double quotes.
Wildcard characters "" can be used many times, such as "Zhao _".

7) Enquiries are vacant
is [NOT] null
Instance:

SELECT * from work where info is null;

Query work records with a blank table info field.

8) and and or multiple condition queries
Conditional expression 1 and conditional expression 2 [... and conditional expression N]
And the record that satisfies all the conditions is queried, or indicates that only one of the records that satisfies it is queried.

9) Query results are not duplicated
Select DISTINCT property name
Instance:

Select distinct age department_id employee;

10 Query Results sorted
Order BY property name [Asc|desc]
The default ASC sort.
If you encounter a record in which a field has a null value, you need to be aware that a null-value sort can be interpreted as the minimum value for that field.
In MySQL, you can specify that you want to sort by multiple fields.
Instance:

SELECT * FROM employee-ID ASC, age desc;

3.limit Limit Query Result bar count
1) Do not specify the starting position
Number of Limit Records
The number of records exceeds the query results, all records are 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 aggregate function query
Aggregate functions include count (), SUM (), AVG (), Max (), and Min ().
1) count () function
Number of statistical record bars
Instance:

Select COUNT (*) from employee;

Use with GROUP by

Select D_id,count (*) from the employee group by D_ID;

The above statements are grouped before statistics.

2) sum () function
SUM () function is the SUM function
Instance:

Select Num,sum (score) from grade where num= 1001;

Select Num,sum (Score) from grade GROUP by NUM;

SUM () can only calculate numeric type fields.
3 avg () function
The AVG () function is the average function.
Instance:

Select AVG (age) from employee;

Select Course,avg (Score) from group by course;

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

Select Max (age) from employee;
Select Num,course,max (Score) from grade group by course;

For the maximum problem of a string, the max () function is computed using the ASCII code corresponding to the character.

4. Merge Query Results
Use Union and UNION ALL keywords.
Union merges the results of the query together and removes the same records, union all simply merges together.

SELECT statement 1 union|union All
SELECT statement 2 union|union all ...
SELECT statement N;
PS: Alias a table or field
table alias Syntax:

Alias of table Name table

SELECT * FROM Department d where d.d_id = 1001;

Field alias Syntax:

property name [as] Alias
As dispensable.

Select d_id as department_id,d_name as Department_name from department;