simple query
## Direct query
Syntax: select field from table name;
For example, select name, age from student;
Ans: Query name and age from the student table.
## Conditional queries
Syntax: select field from table name where Condition;
For example, select name from student where age = 15;
Ans: Query the student table for name with age = 15.
## Fuzzy queries
Syntax: select field from table name where field like '%data%';
For example: select * from student where name like '% cheung %';
Explanation: Query the student table for all rows whose name contains 'Zhang'.
## Arithmetic operators
Syntax: > (greater than), < (less than), = (equal to), ! = (not equal), <> (not equal), >= (greater than or equal to), <= (less than or equal to)
For example, select * from student where age < 15;
Ans: Query the student table for all rows with age < 15.
## Logical operators
Grammar: and, or, not
For example, select * from student where age = 15 or sex = 'man';
Ans: Query the student table for all rows with age = 15 or sex = 'man'.
## in and not in operators
Syntax: select field from table name where field in(list)// or not in(list);.
For example: select * from student where age in(13, 14, 15);
Ans: Query the student table for all rows with age between (13, 14, 15).
## Sorting queries
Syntax: select field from table name order by field Sort by (ascending asc, descending desc).
Example: select * from student order by age asc
Ans: Query the student table for all rows and sort them in ascending order by age.
Advanced Search
## Range operations
Syntax: used to replace arithmetic operators
select field from table name where field between range 1 and range 2;
For example: select * from student where age between 13 and 15;
Ans: Query the student table for all rows with age >= 13 and age <= 15.
This is equivalent to select * from student where age >= 13 and age <= 15;
## Limit queries
Syntax: limit can limit the number of records to formulate the query result
select field from table name limit n, m;
Example: select * from student limit 3, 5;
Ans: Query the third to fifth rows of the student table, but note that 0 means the first row, which also starts from 0.
## Nested queries
Syntax: Nested queries are also called nested queries because they contain subqueries in the query statement, without a separate syntax, and nested subqueries are usually located after the conditions of the query statement.
Example: select name, age from student where name = (select name from engScore where score = 100)
Ans: Query the name and age records in the student table (name with score = 100 in the engScore table).
This means that you can get the name by querying the engScore table for the percentile, and then use the name as a condition to query the student table for the name and age.
## Multi-table lookup
Syntax: as with nested queries, a common field is required, and then multiple tables are joined together in a query to form an ensemble of eligible records
The following three types of connections are commonly used:
# Internal connections
Syntax: select fields from table1 inner join table2 on table1.fields = table2.fields;
Match based on fields common to both tables and then splice the eligible ensembles
The on is followed by the join condition, which is the common field
For example, select * from student inner join engScore on student.name = engScore.name;
Ans: Splice the student table with the engScore table by the same name, which is simply a merge of the two excels.
# Left connection
Syntax: select fields from table 1 left join table 2 on join condition;.
For example, select * from student left join engScore on student.name = engScore.name;
Ans: Same form as inner join, but the left table is the master table, the specified fields will be displayed, the right table is the slave table, no content will be displayed null
# Right connection
Syntax: select fields from table 1 right join table 2 on join condition;.
For example, select * from student right join engScore on student.name = engScore.name;
Ans: Same form as inner join, but the right table is the master table, the specified fields will be displayed, the left table is the slave table, no content will be displayed null
## Aggregation functions
You can implement some specific functions, such as finding the minimum value, finding the maximum value, summing, counting, etc.
# min()
Syntax: select min(field) from table name.
For example, select min(age) from student;
Ans: Find the smallest age from student
# max()
Syntax: select max(field) from table name.
For example, select max(age) from student;
Ans: Find the largest age from student
# sum()
Syntax: select sum(field) from table name.
For example, select sum(age) from student;
Ans: sum of all ages from student
# avg()
Syntax: select avg(field) from table name.
For example, select avg(age) from student;
Ans: Average all the ages from student.
# count()
Syntax: select count(field) from table name.
For example, select count(name) from student;
Ans: Query the number of records of name from student.
# as
Syntax: select Function(Field) as Alias from Table Name;.
For example, select count(name) as Number of records of names from student;
Ans: The number of records of name queried from student is given an alias 'number of records of name'.
## Case conversion
Syntax: select upper(field) from table name.
For example: select upper(sex) from student where name = 'Zhang SAN ';
Ans: If the original sex is defined as man, then running the sql statement will output MAN.
