MySQL syntax collation (2)

And then the last blog , Let's continue to learn MySQL The grammar of . This article focuses on multi table query , Including nested multi-level queries , Everybody be prepared ！

I don't say much nonsense , Let's start with the first sentence ,union： Merge the query results together and remove the duplicate . We learned about natural connection , Is the key to increase the table horizontally , So this union Is to add records in the result table vertically .

/* For example, I want to inquire 2009 Autumn and 2010 Classes began in the spring of course_id, And the room number of the class */
(
	SELECT course_id , room_number
	FROM section
	WHERE semester='Fall' AND YEAR = 2009
)
UNION -- union Will automatically help us to redo the results 
(
	SELECT course_id , room_number
	FROM section
	WHERE semester = 'Spring' AND YEAR = 2010
);

/* Merge the results together , But don't go heavy */
(
	SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR=2009
)
UNION ALL
(
	SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR=2010
);

union The query results are shown in the figure

Above we find 2009 Autumn and 2010 Courses starting in the spring of , In fact, it is actually the collection and operation of two query results . So if I want to find out now 2009 Classes begin in the autumn of , And at the same time 2010 What about the spring course in ？ You can find , This time I did the set intersection operation on the two query results . It is a pity that ,MySQL There is no word or statement to directly implement this operation , But we can pass where Conditional filtering to achieve

/* By comparing two tables where To realize the intersection of sets */
SELECT T.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE  semester='Fall' AND YEAR = 2009
	) AS T , -- T The table stores 2009 Classes began in the autumn of course_id
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010
	) AS S -- S The table stores 2010 Classes began in the spring of course_id
WHERE T.course_id = S.course_id;  --  there T in course_id One by one with S Medium course_id Compare , If equal, save to 
--  In the results table 

This is the intersection of sets , That's the same thing , The set difference operation is similar

/* adopt where in not in  Operation implementation set difference */
/* Here we want to find out only in 2009 Classes begin in the autumn of 2008 instead of 2010 Courses starting in the spring of */
SELECT T.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009) AS T
WHERE T.course_id NOT IN --  It's used here not in
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010);

/* Idea , The order of the above two tables is reversed , The result is different ！！ You can understand why it is different */
/* Here we want to find out only in 2010 Classes begin in the spring of 2008 instead of 2009 Courses that begin in the fall of */
SELECT S.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010) AS S
WHERE S.course_id NOT IN
	(SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009);

The query method is similar to the above , When T A record of the collection is in S There is no... In the collection , This will return to true , And save the record in the result table .

Next, let's learn some common functions , These also lay a good foundation for our follow-up study

/* utilize avg Or other function evaluation */
SELECT AVG(salary) AS avg_salary
FROM instructor
WHERE dept_name = 'Comp. Sci.';

/* This is usually used to find how many rows there are in the table */
SELECT COUNT(*) AS number
 FROM course;

/* And something like that. max() also min() You can practice the function by yourself , I won't give an example here */

What's the use of learning these functions ？ To a large extent, it paves the way for the commands we will learn next .group by： seeing the name of a thing one thinks of its function , Is to group the records of the table .

Next, let me give you an example , Let's talk about group by Have a better grasp of

 /* Statistics 2010 How many teachers in all departments started classes in the spring of */
 /*group by  It's followed by select The sentence syntax is strict , Need to be careful , Generally, it is connected with the key of function or group , Like here dept_name*/
SELECT dept_name , COUNT(DISTINCT instructor.ID) AS inst_count
FROM instructor , teaches
WHERE instructor.ID = teaches.ID AND --  first = Is to find out the record of a teacher's class 
	semester = 'Spring' AND  --  the second = It was in the last = On the basis of , Find out the spring classes , That is to find out what the teacher taught in spring 
	YEAR = 2010  --  Third = In the result above , Find out 2010 Classes in , You can find three = Actually, I've been doing set intersection 
GROUP BY dept_name; --  Be careful group by Is in where hinder , Group result tables 

This is the final query result , You can find , Each group , There's only one record , therefore select Finally, there must be a key representing this group

Here's a question for you ： Can I select Of these groups name key , As shown in the following code ：

SELECT dept_name , NAME --  What's the point of doing this ？
FROM instructor , teaches
WHERE instructor.ID = teaches.ID AND
	semester = 'Spring' AND
	YEAR = 2010
GROUP BY dept_name;

Actually, mine MySQL It's executable , But this statement is wrong , You can see , Last select It is the key to select different groups of tables , For example, I can select the table of each group dept_name, Or the average salary of each group table, etc . But choose what the teacher's name means ？ For example, there are three teachers in the computer department , Which teacher's name should I choose ？ It can be found that the query is meaningless , stay Oracle Or in other databases, this statement will report an error ！ therefore ：group by It's followed by select The sentence syntax is strict , Need to be careful , Generally, it is connected with the key of function or group , Like here dept_name.

We have learned from the above group by sentence , If I want to find out that the average salary of the Department is greater than 42000 What about your department ？ This is the time ,having Statement can help us solve this problem .having： Yes gruop by Then choose the result

SELECT dept_name,AVG(salary) AS avg_salary
 FROM instructor
 GROUP BY dept_name
 HAVING AVG(salary) > 42000; --  This is screening group by Later results 

After learning this, we can actually do a lot of queries , Let me give you a more complicated example .

 /* Find out 2009 The average credits of courses attended by more than two students in two semesters */
 SELECT course_id,semester,YEAR,sec_id,AVG(tot_cred)
-- FROM takes,student
-- WHERE takes.ID = student.ID AND YEAR = 2009
FROM takes NATURAL JOIN student --  above takes.ID = student.ID amount to NATURAL JOIN, Find out the record of the class the student has attended 
WHERE YEAR = 2009
/* Groups are grouped here in the following order */
GROUP BY course_id,semester,YEAR,sec_id --  For example, when course_id Press in group semester Divided into multiple groups , So continue to divide .
--  Let's think about it here , If grouped by primary key , What can we finally get , In fact, it is to get every record of the original table ！
HAVING COUNT(takes.ID)>=2;

The query results are shown in the figure

limit： Limit the number of records of query results , Usually with some functions .

/*limit Statement is used to filter a certain number of records , When the actual records are less than the limited records, it doesn't matter */
/* Here, find the names of the top two departments with average salary and the average salary of the Department */
SELECT dept_name,AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
ORDER BY avg_salary DESC
LIMIT 2; -- limit Is to limit the records of the result table , So it must be placed in where,group by Back 
/*
LIMIT 0,5 --  Indicates that the first to fifth records are displayed , Note that the record is from 0 The following table starts with .

group by + with rollup： It means to summarize and count each group again on the basis of grouping statistics

/* Summary is usually to find a sum*/
SELECT dept_name , SUM(salary) , COUNT(*)
FROM instructor
GROUP BY dept_name WITH ROLLUP;

The query record is shown in the figure , The last one is with rollup Field of , But it can be found that , For keys that cannot be counted dept_name It's a null value

This article has explained a lot , Including set intersection , Set difference and some functions and group by The order of . The next blog will continue to explain more syntax and commands of multi table query , Including nested use where and from.