Grouping and multi-table joins in MySQL
1. MySQL group by
Singleton function
| function | meaning |
|---|---|
| lower | Turn column content into lowercase |
| upper | Turn column content into capital |
| length() | length |
| trim | Remove spaces |
| round() | Adding a positive number afterwards is rounding for the decimal; adding a negative number afterwards is rounding for the integer |
| rand() | Generate a random number between 0-1 |
| concat(a,c) | Splice the contents of the two columns a and c, and output one column |
| substr(a,1,2) | Intercept the first and then two characters in the string in column a |
| ifnull(A,B) | Determine whether it is a null value. If A is a null value, it returns B. If A is not a null value, it returns the found value. |
Aggregation function
Aggregation function: The number of output columns is generally reduced, and it is generally used with gruop by, and nested use is generally not allowed.
| function | meaning |
|---|---|
| max() | Maximum value |
| min() | Minimum value |
| avg() | average value |
| sum() | Ask for sum |
| count() | count |
# Group the subjects and query the highest value of each subjectselect c_name,max(grade) from score_one group by c_name; # Note: Aggregation functions cannot be added after where, but singleton functions can be used, so having to use data filteringselect department,count(department) from student_one group by department having count(department)>2; # Implicit grouping distinct# Expression grouping addressselect substr(address,1,3),count(substr(address,1,3)) from student_one group by substr(address,1,3);
2. Multi-table connection (table connections will generate a lot of data garbage)
- Cartesian product connection (cross-connection) cross join: There is no difference between using, making connections. is the default connection in the connection.
- Inner join … on Join condition Filter condition: If two tables have the same column name, you can use using (consensual column name) to represent the connection when the condition is determined to be the same column name. The same column name is only displayed once, instead of on
- Simple multi-table connection: just join directly at the end
- Use the subquery results as a query table: name the query result a, and then use a to join with other tables
- Use the same table twice in a row: the same table specifies a different alias
Self-join: inner join, the foreign key of this table links this table, and the two references use different alias.
- Equal connection and unequal connection:
- Equal connection: Use an equal sign (=) connection after on;
- Unequal connection: Use >, <, != to connect
- External connection (left outer connection and right outer connection):
- Left outer join: left join … on …, the connection condition must be written, mainly the table on the left join
- Right external connection: right join … on …, the connection condition must be written, mainly the table on the right of join
- Full external connection: Use union to achieve union of left external connection and right external connection, or use union all (no duplicate values are deleted)
- Natural join
- When using natural joins, both tables must have the same column name
- If the same column name cannot be found, the natural connection will become a Cartesian product connection
This is the introduction to this article about grouping and multi-table connection in MySQL. For more related contents of mysql grouping and multi-table connection, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!