Learning notes for basic query statements in MySQL _ MySQL

This article describes the basic query statement learning notes in MySQL, including how to use limit to limit the number of query results and merge query results. For more information, see Learning notes for basic query statements in MySQL _ MySQL

1. basic query statement
Select attribute list from table name and view list [where condition expression 1] [group by attribute name 1 [having condition expression 2] [order by attribute name 2 [asc | desc]
2. single table query
1) use * to query all fields

Select * from table name;

2) query specified fields

select id，name from product；

You can use the preceding example to query a specified field.

3) query a specified record
Where condition expression
Instance:

select *from employee where id = 1002;

Where clause common query conditions

Comparison: =, <,

[Not] in (element 1, element 2,..., element n)
Instance:

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

If the element in the set is a character, single quotation marks are required.

5) query the range with between and
[Not] between value 1 and value 2
Value 1 is the start value and value 2 is the end value.
Instance:

select * from employee where age bewteen 15 and 20;

6) query string matching with like
[Not] like 'string ';
The value of 'string' can be a complete string, or a wildcard character containing the percent sign (%) or underscore.

"%" Can be a string of any length, and the length can be 0.
"_" Can only represent a single character.
When the character is complete, like is equivalent to "= ".
Instance:

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

Query all homeaddr fields in Beijing"
.

select * from employee where name like "ar_c";

Query records where the value of all name fields is 4 and the first two letters are "ar" and the last letter is "c.
A unified string can be enclosed in single or double quotation marks.
The wildcard "" can be used multiple times, such as "zhao _".

7) vacant query
Is [not] null
Instance:

select * from work where info is null;

Query records in the work table where the info field is null.

8) and or multi-condition query
Conditional expression 1 and conditional expression 2 [... and conditional expression n]
And indicates that records that meet all the conditions will be queried. or indicates that records that meet one of the conditions will be queried.

9) the query results are not repeated.
Select distinct attribute name
Instance:

select distinct age department_id employee;

10) sort query results
Order by attribute name [asc | desc]
By default, asc is sorted.
If a field contains a record with a null value, note that the sorting of null values can be considered as the minimum value of the field.
In mysql, you can specify multiple fields for sorting.
Instance:

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

3. limit limits the number of query results
1) do not specify the starting position
Number of limit Records
If the number of records exceeds the query result, all records are displayed and no error is reported.

2) specify the start position
Start position of limit, number of records
The starting position of the record starts from 0.

2. query using a set function
Aggregate functions include count (), sum (), avg (), max (), and min ().
1) count () function
Number of statistical records
Instance:

select count(*) from employee;

Used with group

select d_id,count(*) from employee group by d_id;

The preceding statements are grouped before statistics.

2) sum () function
The 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 fields.
3) avg () function
Avg () is an average function.
Instance:

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

4) max (), min () functions
Calculate the maximum and minimum values.
Instance:

select max(age) from employee;select num,course,max(score) from grade group by course;

For the maximum value of a string, the max () function uses the ascii code corresponding to the character for calculation.

4. merge query results
Use the union and union all keywords.
Union merges the query results and removes the same records. union all is simply merged.

Select statement 1 union | union all
Select statement 2 union | union all...
Select statement n;
PS: alias for a table or field
Alias syntax for table startup:

Table name table alias

select * from department d where d.d_id =1001;

Field alias syntax:

Attribute name [as] Alias
As is optional.

select d_id as department_id,d_name as department_name from department;

The above is the content of the basic query statement learning NOTE _ MySQL in MySQL. For more information, see PHP Chinese network (www.php1.cn )!