1. Table addition
1) Keep only one row of union repeated rows
Table addition refers to aggregating data from two tables (same number of columns, same data type). Example: Summarize the course1 and Course2 tables
SELECT course id from course union SELECT course ID from course1Copy the code2) Union all keeps duplicate lines
With union, duplicate rows in two tables are counted as only one row; If you want to preserve duplicate rows, you can use union all
SELECT course id, course name from course union all SELECT course id, course name from course1Copy the code2. Join tables
Sample data used
1) Cross join (Cartesian product)
Merge each row in table 1 with each row in table 2, so that the number of rows resulting from the merge is the product of the number of rows in the two tables. Note: the cross connection in the practical application is less, because the result line number is more, the calculation is relatively large, the actual use value is not big example: poker 13 grades and 4 suits of the cross connection
2) inner join
Inner join is used to find outAt the same time there isData in both tables.
For example:
Select a. student ID, A. name, B. course id, B. grade from student1 as a inner join score1 as b on a Student number =b. Student Number - identifies two numbers that indicate that the field "student number" is connectedCopy the code
3) left join
The left join fetches all the data in the table to the left of the FROM clause and matches it with the right. If there is no match in the right table, the value is Null.
Select a. student ID, A. name, B. course id, B. grade from student1 as a inner join score1 as b on a Student ID = B. Student IDCopy the code
4) Right join
The right join fetches all the data in the table to the right of the FROM clause and matches it with the left. If there is no match in the left table, the value is Null.
Select a. student ID, A. name, B. course ID, B. grade from student1 as a right join score1 as B on a Student ID = B. Student IDCopy the code
5) The two tables partially overlap, only to one side of the table unique
1. Select only the left table
2. Select only the right table
6) Full join
A full join returns all rows in the left and right tables, and if there is a match between the two tables, the join is performed; Otherwise, the unmatched parts are represented by null values.
Note: mysql does not support full joins
7) Summary diagram of different types of connections
8) Application examples
Question 1: Query all students’ student id, name, number of courses, total score
Select a, b,count(b),sum(b) As from student1 as a left join score1 as B on A Student ID = B. Student ID group by Student IDCopy the code
Query the student id, name and gpa of all students whose gpa is greater than 85
SELECT a. Student ID, A. Name, AVG (b. Grade) from student1 as a left join score1 as b on a Student id =b. Student ID GROUP BY student ID having avg(b. Grade) > 85Copy the code
Question 3: Query student’s course selection information, including student id, name, course id and course name
Select a, B, C from student as a inner join score as b on a. Inner join course as C on B. Course number = C. Course numberCopy the code
Case expression
A function that acts as a condition judge to determine whether each row satisfies a condition; If so, the then clause continues to run, and then terminates. If the condition is not met, the run continues until a statement that meets the condition is found or is finally terminated.
Case expressions are used when using conditional judgments.
Note: (1) else clause can be omitted, but for the sake of writing norms, write as much as possible. (2) The end of the book. (3) Case expressions can be placed in the select clause, as well as in any SQL clause
The case clause is part of the SELECT clause, so the comma before it cannot be ignored
SELECT * from score, (case when score >=60 then 'f' when score <60 then 'F' else null end) as' F 'from scoreCopy the code
Example 2: Find the number of students who passed and failed in each course
Select course id, sum(case when score >=60 THEN 1 else 0 end) as 数, Sum (case when score <60 then 1 else 0 end) as from score GROUP BY course id;Copy the code
Example 3: Use sections [100-85],[85-70],[70-60],[<60] to count the scores of each subject, including the number of students in each section, course number and course name
Select A. course name, B. course name, sum(case when a) Score BETWEEN 85 and 100 then 1 else 0 end) as '[100-85]', sum(case when a) Then 1 else 0 end) as '[85-70]', sum(case when a. As '[70-60]', sum(case when a <70 and A. Score >=60 then 1 else 0 end) as '[70-60]' Score <60 then 1 else 0 end) as '[<60]' from score as a RIGHT JOIN course as B on A Course id =b. Course id GROUP BY A. course ID, BCopy the codeColumns in [100-85] with identical values count as a group by (group by)
