MySQL Basic query

1. Conditions
using the WHERE clause to filter the data in a table, the rows with the result of true appear in the result set, with the following syntax:
select * FROM table name where condition;
Example: SELECT * FROM students where id = 1;
The where is followed by a variety of conditional operators for conditional processing:
comparison operators;
logical operators;
fuzzy query;
scope query;
null judgment;
1.1. Comparison operators
equals: =
greater than: >
greater than or equal to: >=
less than: <
less than equals: <=
not equal to:! = or <>
1.2. Logical Operators
and or not
1.3. Fuzzy query like
% means any number of any characters
SELECT * from students where name is like "Shellfish%";
_ denotes an arbitrary character
SELECT * from students where name is like "beta-tower";
rlike "Str" indicates that there is a "str" character in the field
SELECT * from students where name Rlike "Kat";
1.4. Scope Query
In () range
= Any|some () any one, equivalent in
= All () equals all the
<> All () is not equal to any of these

2. Sort


select * from students where isdelete=0 order by id desc;

3. Aggregate functions
count () statistics, max () maximum, Min () Minimum value, sum () sum, avg () averages

select count (*) from students;

select Max (ages) from Students;

select min from Students;

select sum (age) from Students;

select avg (Ages) from Students;

4. Group
grouped by field, indicating that the same data in this field is grouped in a group, The grouped column is displayed in the result set,

select column 1, column 2, aggregation ... from table name Group BY column 1, column 2, column 3 ... having column 1,... Aggregation ...

select gender,count ( *) from students group by gender;

select gender,count (*) from Students GROUP by gender have gender= "male"; 　　

Where is the data filter for the table specified after from, and is the filter for the original data.
Having is filtering the results of group by.

5. Paging
Viewing data on one page is a very troublesome thing when the amount of data is too large
Syntax select * FROM table name limit Start,count
From start, get Count bar data, start index starting from 0
Example 1: Query the first two lines of the boy's information
SELECT * FROM students where gender= "male" limit 0, 2;
Example 2: Each page is known to show M data, the current display page n, page N to find data
SELECT * from students where isdelete=0 limit (n-1) *m,m;

6. Connection Query
when the columns of the query result are from multiple tables, you need to concatenate multiple tables into a large data set and select the appropriate columns to return
MySQL supports three types of connection queries, namely:
internal connection query inner join ... on: The result of the query matches two tables to the data
right JOIN query outer join ... On: Query results match two tables to data, right table-specific data with null padding for data not present in left table
left OUTER join ... on: Query results match two tables to data, left table-specific data with null padding for data not present in right table
Example 1: Querying Student and class tables using an internal connection
SELECT * FROM students inner joins classes on students.cls_id = classes.id;
Example 2: Using the right connection to query student and class tables
SELECT * from students as S right outer joins classes as C on s.cls_id=c.id;
Example 3: Querying Student and class tables with a left connection
SELECT * from students as s left outer joins classes as C on s.cls_id=c.id;

7. Subquery


subquery is embedded in the main query 　　
subquery is a secondary primary query, either acting as a condition, or acting as a data source


scalar subquery: The result returned by a subquery is a single row of data (one column)
column query: The result of the return is one row (one column more rows)
row subquery: The result returned is a row ( One row, multiple columns)
table-level subquery: The result returned is multiline multiple columns

select count (*) from students where > (select AVG ( Age) from students);

query in full format:
select select_expr [, select_expr,...] [
from Tb_name
[WHERE condition judgment]
[GROUP by {col_name | postion} [ASC | DESC], ...]
[having a WHERE condition judgment]
[ORDER by { Col_name|expr|postion} [ASC | DESC], ...]
[LIMIT {[Offset,]rowcount | row_count offset offset}]

The complete SELECT statement:
SELECT DISTINCT *
From table name
where ....
GROUP By ... having ...
ORDER BY ...
Limit Start,count

MySQL Basic query