What are the MySql query methods?

New

insert into B select * from A；//将A表的信息通过查询新增到B表中去

##Aggregation query

count；//返回到查询的数据总和

sum；//返回到查询的数据总和（只对数字有意义）

Only Meaningful for numbers

avg/max/min;//返回查询数据的平均值/最大值/最小值(只对数字有意义）

Group query

select * from 表名 group by 分组条件；

Here is the first Perform grouping, and then perform the aggregate function of each group based on the grouping.

Conditional query

having;

Having can be used to conditionally filter the results grouped by group by. where is executed before grouping. If you want to conditionally filter the results after grouping, you need to use having (used with group by).

For example: Find the average salary of each role, except Wu Jiu. This sentence can be rewritten as: "Specify the conditions before grouping-remove Wu Jiu, and then calculate the average salary.".

Use the having clause to filter the salaries of various roles whose average salary is less than 10,000: SELECT role, AVG(salary) AS avg_salary FROM salaries GROUP BY role HAVING AVG(salary)

Union query

The first way of writing: select * from table name 1, table name 2;

The second way of writing: select * from table name 1 join table name 2 on conditions;

joint query (more important) is a multi-table query, and the previous queries are all single-table queries. The core operation in multi-table query---Cartesian product.

The Cartesian product operation is to combine each record of the two tables to obtain a new set of records.

The above records are not all the results we want. We can get the results we want through filtering.

So what’s the difference between joining on followed by conditions and using where with conditions?

The writing method of where from multiple tables is called "inner join".

Use join on to express both inner and outer connections.

select column name from table 1 inner join table 2 on condition; inner join means "inner join" where inner can be omitted.

select column name from table 1 left join table 2 on condition; left outer join.

Select column from table 1 right join table 2 on condition; right outer join.

Self-join

Self-join means connecting itself to the same table for query. A rewritten version is: list all score information, in which "Chinese" scores are higher than "Mathematics". You must first find the course numbers (course_id) of the two courses, Chinese and Mathematics, and then proceed to the next step. Then compare them.

select s1.student_id,s1.score,s2.score from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1 and s1.score>s2.score;

Merge query

union；//这个可自动去重
union all；//这个不可自动去重

This operator is used to obtain the union of two result sets.

For example: Query courses with ID less than 3, or courses named "English".

select * from course where id<3 union select * from course where name=&#39;英文&#39;;

Or use or to achieve

select * from course where id<3 or name=&#39;英文&#39;；

The above is the detailed content of What are the MySql query methods?. For more information, please follow other related articles on the PHP Chinese website!