MySQL syntax collation (3)

Continue with the previous blog post , Today, I finally want to talk about the more complex multi table query ！

in： Query whether the elements of a table are in another table . Sounds like it's easy , Let's take an example

/* I want to find satisfaction semester='Fall' AND YEAR = 2009 also semester='Spring' AND YEAR=2010 Curriculum id
 here , We will think of the set intersection operation mentioned in the last blog , here in The same thing can be done */
SELECT DISTINCT course_id
 FROM section
 WHERE semester='Fall' AND YEAR = 2009 AND
 course_id IN 
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR=2010);

Of course , Operation similar to set intersection , We can also use another way to realize

/* This is another way of writing , First construct two tables , Then judge course_id Whether it is equal or not */
SELECT DISTINCT A.course_id
FROM (SELECT DISTINCT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009
	) AS A ,
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR=2010
	) AS B
WHERE A.course_id = B.course_id;

not in： Query whether the elements of a table are not in another table . And in similar , But on the contrary .

/*not in  and  in  similar , Return when you are away true*/
SELECT DISTINCT course_id
 FROM section
 WHERE semester='Fall' AND YEAR=2009 AND
 course_id NOT IN (
 SELECT course_id
FROM section
WHERE semester='Spring' AND YEAR=2010);

/* Here's another way , More complicated . First, put 2009 Autumn and 2010 Find out the courses that also open in spring ,
 Then I'm putting 2009 The course in the fall of 2007 is not in 2009 In the autumn ,2010 Spring courses */
SELECT B.course_id
FROM(
	SELECT DISTINCT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009 AND
	course_id IN (SELECT course_id
	FROM section
	WHERE semester = 'Spring' AND YEAR = 2010)
	) AS A , section AS B
WHERE B.semester='Fall' AND B.YEAR = 2009 AND B.course_id != A.course_id;

Just now we were just a key to in perhaps not in operation , If multiple key values are in progress , Just put a parenthesis .

/* This is a comparison of multiple values , For example, when the primary key is multiple keys , That's it */
/* The query is id by '10101' The number of courses taught by our teachers */
SELECT COUNT(DISTINCT ID)
 FROM takes
 WHERE (course_id,sec_id,semester,YEAR) IN --  Here, when the four key values are equal, the record will be put into the result table 
	(SELECT course_id,sec_id,semester,YEAR
	FROM teaches
	WHERE teaches.ID='10101');

some As long as one is true , amount to or. For example, compare two tables , as long as A A record of the table is for any one B The records of the table meet the conditions , Then we will A Save this record in the result table .

/* Here is to find out the name of a teacher who has a higher minimum wage than the computer department */
SELECT NAME
 FROM instructor
 WHERE salary > SOME --  Because the minimum wage teacher's salary will not meet more than the conditions , So it will be excluded 
	(SELECT salary
	FROM instructor
	WHERE dept_name='Comp. Sci.');

all： amount to and, Do it all .

/* This means to find out the names of teachers who earn more than all teachers in the computer department */
SELECT NAME
 FROM instructor
 WHERE salary > ALL 
	(SELECT salary
	FROM instructor
	WHERE dept_name='Comp. Sci.');

We can combine the groups we learned before group by and having To make some combined queries . Here are a few examples ：

/* Here we need to find out which department has the highest average salary */
/* Here, due to the comparison of various departments , So we need group by, It is worth noting that ,>=  It can't be replaced by  >
 Because suppose a system is the largest , The biggest can't be bigger than yourself ！*/
SELECT dept_name
 FROM instructor
 GROUP BY dept_name
 HAVING AVG(salary) >= ALL 
	(SELECT AVG(salary)
	FROM instructor
	GROUP BY dept_name);

Review just a few queries , We found that internal queries do not rely on external queries , Internal queries are independent , That is, the execution order is from inside to outside . Next, the query statement we are going to use will depend on external queries , Its execution order is from outside to inside .

exists： And in similar , But the difference is ,exists It's not an independent query , Rely on external queries .

/* Here I want to find out that is in 2009 In the autumn , Also in the 2010 Courses starting in the spring of */
SELECT course_id
 FROM section AS S
 WHERE semester='Fall' AND YEAR=2009 AND
 EXISTS (SELECT *
	FROM section AS T
	WHERE semester='Spring' AND YEAR=2010 AND
	S.course_id = T.course_id); --  This step depends on external S surface 

Next, let's give you a difficult example , Go and feel it exists and not exists Specific usage of .

/* Division in Relational Algebra ： Given two tables A（1,2,3）,B（1,2）, because B The elements in 
A There are , therefore A except B For real . To put it bluntly ,A Set contains B aggregate , that A except B It's true 
 conversely ,B except A For false , because B Not included A All elements of the collection */
SELECT S.ID,S.name
 FROM student AS S --  Here first student Change the name of the watch into S
 WHERE NOT EXISTS
	(SELECT *
	FROM course C
	WHERE dept_name='Biology' --  Let's pick out the biology course first 
	AND NOT EXISTS -- exits The role of can be seen as C Put the records in one by one below to judge 
		(SELECT *
		FROM takes AS T 
		WHERE S.ID = T.ID --  Come here and select all the courses selected by a student 
		AND C.course_id = T.course_id)--  This is to determine whether all biology classes are selected by students 
		);

/* The number of courses can be less than or equal to 1 Find out 2009 Courses that don't open or only open once a year */
SELECT T.course_id
 FROM course AS T
 WHERE 1 >= (SELECT COUNT(R.course_id)
	FROM section AS R
	WHERE T.course_id = R.course_id AND
	R.year=2009); 

/* You need to rename the subquery results , You can use it directly without reference */
SELECT dept_name,avg_salary
 FROM (SELECT dept_name,AVG(salary)-- as avg_salary
	FROM instructor
	GROUP BY dept_name)
	AS dept_avg(dept_name,avg_salary)
 WHERE avg_salary > 42000;

with： Create a temporary table . This one is very common , It can avoid the reduction of readability caused by multi-layer query .

 /*with Operator , Create a temporary table , Format with  Table name （ attribute 1 ,  attribute 2 ...） as （ Query statement ）*/
 WITH max_budget(VALUE) AS --  The assignment operation 
	(SELECT MAX(budget)
	FROM department  --  Attention format 
	)
 SELECT budget
 FROM department,max_budget
 WHERE department.budget = max_budget.value;

/* This query statement queries the Department whose average salary is higher than that of the Department , And list */
WITH dept_total(dept_name,VALUE) AS --  The first temporary table 
	(SELECT dept_name,SUM(salary)
	FROM instructor
	GROUP BY dept_name) , --  Attention format 
	dept_total_avg(VALUE) AS --  The second temporary table 
	(SELECT AVG(VALUE)
	FROM dept_total)
SELECT dept_name
FROM dept_total,dept_total_avg --  Query on two temporary tables 
WHERE dept_total.value >= dept_total_avg.value;

Let's talk about subscript quantum query ： Put the query statement into select In the sentence . In fact, I don't think it's meaningful .

/* Scalar subquery , characteristic select The query record of clause must be equal to the total select Number of records 
 What is common is the total select The element as select clause where The key words of the statement */
SELECT dept_name,
	(SELECT COUNT(*) -- select Clause as an attribute 
	FROM instructor
	WHERE department.dept_name = instructor.dept_name)
	AS num_instructors
FROM department;