[Zhuantang learning notes] basic SQL query statements (single-Table query and multi-Table query)
SQLQueryBasic1. Single Table query
It is also called projection.
Basic query statement Structure
Select column from table
* Not all other columns
Query all data
Example: SELECT * FROM t_studen
Perform a relatively fine operation and add conditional Filtering: Query student information with id 2
SELECT * FROM t_student WHERE id = 2;
Filter execution steps
Example: SELECT * FROM t_student WHERE id = 2;
SELECT * (3) after filtering, the final projection result is the filtered result. Not all rows are displayed, and the row data is filtered.
FROM t_student (1) Add the entire table to the memory, locate the entire table, and add it to the memory.
WHERE id = 2 (2) filtering because the data in a table needs to be filtered and does not want to be displayed in each row. This makes no sense, so a conditional filtering is required.
Only projects certain Columns
SELECT id, s_name FROM t_student; only project the result, instead of changing the table data. Many people will be confused. Will other columns disappear?
The query results are displayed in Chinese. For example, s_name is displayed as the student name.
ASAdd an alias (column alias)
SELECT id, s_name AS "Student name" FROM t_student;
Table alias. When there are several tables, the table name is long and an alias is added to the table.
SELECT t_student.s_name FROM t_student -- but writing t_student.s_name in a table does not make any sense.
SELECT a. s_name FROM t_student a this query a represents that t_student can be a. s_name
1.1,
Filter out duplicate data
DISTINCT
Select distinct s_name FROM t_student;
Select distinct s_name, s_classid FROM t_student; the data of the duplicate column (s_name, s_classid) is exactly the same
1.2,
Select Operation: Write filtering condition method:
AndIt can also be written &&
SELECT * FROM t_student WHERE id = 2 AND s_name = 'lily' AND s_classid = 'class1'
SELECT * FROM t_student WHERE id = 2 AND s_name = 'lily' & s_classid = 'class1'
OrIt can also be written as |
SELECT * FROM t_student WHERE s_classid = 'second ban' OR id = 1
SELECT * FROM t_student WHERE s_classid = 'second ban' | id = 1
Range operation ...... AND ......(Include)
Syntax: select column from table name where column name between value and value;
SELECT * FROM t_student WHERE age> = 18 AND age <= 25
SELECT * FROM t_student WHERE age BETWEEN 18 AND 25 -- equivalent to above greater than or equal
Less than or equal to 18 and 1 class, greater than or equal to 25 and 1 class
SELECT * FROM t_student WHERE age <= 18 OR age> = 25 AND s_classid = 'class1 ';
The result of executing this statement is all data smaller than or equal to 18 and data larger than or equal to 25 in a class.
It is because and has a higher priority than or.
So we should write it like this:
SELECT * FROM t_student WHERE(Age <= 18 OR age> = 25)AND s_classid = 'class1';
Less than or equal to 18 and a class, greater than or equal to 25 and a class and priority higher than or, so it is generally enclosed in parentheses
Query if the student age is 18, 25, or 28
SELECT * FROM t_student WHERE age = 18 OR age = 25 OR age = 28
In operation
Simplify the above case where column in (value 1, value 2, value 3)
SELECT * FROM t_student WHERE age IN (18, 25, 30)
Not in Operation
The value is not 25.
SELECT * FROM t_student WHERE age not in (25)
Returns the limit number of rows.Limit
Limit number = number of rows starting from the first row
Limit number n. The number m starts from the nth row of data (same as the array subscript calculation method). The number m row is similar to the string substr.
SELECT * FROM t_student LIMIT 2
SELECT * FROM t_student LIMIT 2, 2
Returns the value of the second parameter of all rows after the second row. If the value of the second parameter is large enough, it is returned only until the last row.
SELECT * FROM t_student LIMIT 2,100
How many rows are returned for application scenarios? When some websites have a large amount of data, pagination is required.
1.3,
Fuzzy search
% Unlimited characters
Syntax: select * from table name where field like'X %';
Find all students surnamed Zhang
SELECT * FROM t_student WHERE s_name = 'zhang san' = full match will not help you automatically perform fuzzy search
SELECT * FROM t_student WHERE s_name LIKE 'Zhang %' like % fuzzy query with no limit on characters (0-n characters)
SELECT * FROM t_student WHERE s_name LIKE '% sheets %' like %. You can find including sheets, such as * sheets *
SELECT * FROM t_student WHERE s_name LIKE '% sheets like % can be found, ending with a sheet, but not a sheet **
Placeholder _ (underline)An underline represents a character
Find all students with the surname Zhang, and the name can be 3 characters long
SELECT * FROM t_student WHERE s_name LIKE 'zhang _ 'like _ fuzzy query of the maximum number of characters an underline represents a character
Query the student information of an empty age.
SELECT * FROM t_student WHERE age = "" ''" indicates a blank value.
Null is null
SELECT * FROM t_student WHERE age is null indicates that the NULL value has no input data.
Not emptyIs not null
SELECT * FROM t_student WHERE age IS NOT NULL
The preceding operations are projected in the same order as the data in our table (input order ).
SortOrderAscending ASC descending desc
Age size sorting
SELECT * FROM t_student order by age -- the default value is ascending.
SELECT * FROM t_student order by age DESC -- descending ORDER
SELECT * FROM t_student order by age ASC -- Ascending
Sort by more than one condition
SELECT * FROM t_student order by age ASC, id ASC will first sort age,
Then, if there are two identical age conditions, and then the id sorts the age, the second condition is generally not considered.
1.4. Aggregate functions
Count (column)
Syntax: select count (column) as 'Total' from table name whereCondition------- Generally, an alias is given during counting.
Count statistics do not contain items with no value
Calculates the number of people aged 20.
Example: select count (*) FROM t_student WHERE age = 20
Sum (column)
Syntax: select sum (column) from Table Name
Example: select sum (age) FROM t_student
Average avg
Syntax: (1) select sun (column)/count (*) from Table Name
Example: select sum (age)/COUNT (*) FROM t_student
(2) select avg (column) from table name ------- null is not included by default
Example: select avg (age) FROM t_student
(3) Select avg (ifnull (column, 0) from table name ----- if it is null, fill in 0 to calculate the average
Example: select avg (IFNULL (age, 0) FROM t_student
Maximum and minimum
Syntax: select max (column), min (column) from Table Name
Example: select max (age), MIN (age) FROM t_student
Group
Syntax: select column name, count (*) from table name group by column name to be grouped(It must be associated with the previous column)
Select column name, count (*) from table name group by the having condition for the column name to be grouped
Example: query the number of people in each class
SELECT s_classid, COUNT (*) FROM t_student group by s_classid;
Displays a data entry of the class with the number of students greater than 2 at the age of 10 (where ).
SELECT s_classid, COUNT (*) FROM t_student WHERE age = 10 group by s_classid HAVING (COUNT (*)> 2 );
Procedure:
SELECT s_classid, COUNT (*) -- 4 projection
FROM t_student -- 1 load the table to the memory
WHERE age = 10 -- 2 Filter
Group by s_classid -- GROUP 3
HAVING (COUNT (*)> 2) -- 5 Secondary Filtering. You must obtain the first result before filtering.
-- Both having and where are filtering conditions. where is before grouping, because no aggregate function is executed before grouping.
-- Having is used only for filtering Aggregate functions After grouping.
The number of students aged 10 (where) is greater than 1 and sorted by the number of students
SELECT s_classid, COUNT (*) FROM t_student WHERE age = 10 group by s_classid HAVING (COUNT (*)> = 1) order by count (*) limit 1
Procedure:
SELECT s_classid, COUNT (*) -- 4 projection
FROM t_student -- 1 load the table to the memory
WHERE age = 10 -- 2 Filter
Group by s_classid -- GROUP 3
HAVING (COUNT (*)> = 1) -- 5 Secondary Filtering: You must obtain the first result before filtering.
Order by count (*) DESC -- 6 secondary grouping
LIMIT 1 -- 7 LIMIT the number of rows must be the number of rows in the final result after your execution.
-- The seven steps must not be wrong.
2. Multi-Table query
2.1 related subqueries
The subquery must depend on the primary query. If the subquery runs independently, an error is returned, depending on the results of the primary query.
Select nestingOnly single row single column can be returned
If the primary query is executed once, the subquery is executed once, and N + 1 is finally executed, the efficiency is low. If the primary query does not provide data, the subquery cannot be executed.
Syntax: select column name 1, column name 2 (select column name from table name where condition) from Table Name
Example: SELECT s_name, (SELECT c_name FROM t_class WHERE c_id = s_classid) FROM t_student
2.2 Non-correlated subqueries
From nestingYou must create an alias for the nested subquery table to return multiple rows and multiple columns of data.
The subquery does not depend on the primary query. The subquery is executed only once and only from. It has high performance and can run independently. It only provides the condition value for the primary query.
Syntax: select column name 1, column name 2,... from (select column name from table name [where condition]) [where condition]
For example, if the gender is female and the name is zhangsan
SELECT * FROM (SELECT * FROM t_student WHERE s_sex = 'femal') AS t1
WHERE t1.s _ name = 'zhangsan ';
Where nestingRun the subquery twice. The subquery can run independently and does not rely on the primary query. It only provides the condition value for the primary query.
Syntax: select column name 1, column name 2,... from table name where column name relational operator (select columns from table name where condition)
For example, query the students in the first class.
SELECT * FROM t_student WHERE s_classid = (SELECT c_id FROM t_class WHERE c_name = 'class1 ')
Multiple in values
For example, query the students in the first class.
Syntax: select column name 1, column name 2,... from table name where column name in (select columns from table name where condition)
SELECT * FROM t_student WHERE s_classid in (SELECT c_id FROM t_class WHERE c_name = 'class1 ')
Any and all must be used separately with subqueries.
Any value of all
Syntax: select column name 1, column name 2,... from table name where column name relational operator all (select columns from table name where condition)
For example, to query all students whose scores are higher than any other student, use all.
SELECT * FROM t_student WHERE s_score> ALL (SELECT s_score FROM t_student WHERE s_name = 'zhangsan ')
Any
Syntax: select column name 1, column name 2,... from table name where column name relational operator any (select columns from table name where condition)
For example, all students whose query scores are higher than any of the three
SELECT * FROM t_student WHERE s_score> ANY (SELECT s_score FROM t_student WHERE s_name = 'zhangsan ');
You can also use MIN
SELECT * FROM t_student WHERE s_score> (select min (s_score) FROM t_student WHERE s_name = 'zhang san ');
No matter how the subquery changes, there are only three types. One nested in the where clause, one form, and one select clause