[Zhuantang learning notes] basic SQL query statements (single-Table query and multi-Table query)

[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