MySQL basic query statement learning notes, MySQL statement learning notes

MySQL basic query statement learning notes, MySQL statement learning notes

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: =, <, <=,>,> = ,! =, <>,!> ,! <
Specified range: between and, not between and
Set: in, not in
Matching characters: like, not like
Whether it is null: is null, is not null
Multi-condition query: and or
4) query with the in keyword
The in keyword can be used to determine whether the value of a field is in a specified set.

[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;

Articles you may be interested in:

• Tips for enabling slow log query in MYSQL5.7.9
• How to solve the problem that PHP uses mysql_query to query ultra-memory large result sets
• Php mysql implements login and fuzzy query Functions
• Query duplicate records using distinct statements in MySQL and Performance Discussion
• How to use the general query log in MySQL to find the statement with the most queries
• How to query logs and slow query logs in MySQL
• Five practical tips for MySQL Query Optimization
• How to Use the FROM and EXISTS clauses in MySQL subqueries
• Tutorial on using subqueries and scalar queries in MySQL
• Tutorial on column-subquery and row-subquery operations in MySQL
• How to sort query results and limit the number of returned results in MySQL
• Basic tutorial on adding and querying MySQL Data with a PHP script