preface
This paper briefly summarizes the data query statements and reviews what I wrote when reading
Basic grammar
Single line note:--
Multiline comment / **/
1, Single table query
1. Select several columns in the table
--Query specified column select sno, sname from student; --Query all columns select * from student; --Query calculated values select sname, 2014-sage from student; --Set alias select sname NAME, sage AGE from student;
2. Select several tuples in the table
--De duplicate lines (just not displayed, not deleted) select distinct sno from sc; --Query tuples that meet the criteria select sname from student where sdept='cs';
Here is a possible process of query execution:
Scan the whole table, take out a tuple and check whether the conditions are met. If satisfied, take out the value of sname column to form a new tuple output. If not, remove a tuple.
Why is this a possible process? Because specific operations may be optimized according to the optimizer, using indexes or according to some rules.
Refer to the following two articles for specific sql query operations:
SQL query operation processing flow
A select query process
--between select sname from student where sage between 20 and 30 --There are also not between, Left and right desirable --Determine set select name from student where sdept in('cs','ma','is'); --in and not in It can be used to find tuples whose attribute values belong to the specified set, that is, it essentially selects several tuples that meet the specified set --Character matching select sname from student where sno like '001'; select sname from student where sno like 'Chen%'; -- %Is a wildcard (length can be 0) -- _Is any single character --Escape character --query DB_Design Course number of the course select cno from course where cname like 'DB\_Design' escape '\'; --Null query select sno, cno from sc where grade is null; --Note that it cannot be used=replace --Multiple condition query --use and or or, actually in Just multiple or Abbreviation of
3. order by sentence
Sort the query results. ASC ascending or DESC descending
select sno, grade from sc where sno='3' order by grade desc;
4. Aggregation function
count, sum, avg, max, min
--Query the number of students who have taken courses select count(distinct sno) from sc; --Check the average score of student 1 select avg(grade) from sc where cno='1';
Aggregate function processing automatically skips null values
The aggregation function can only be used in select sentences and having sentences in group by
5. group by clause
--Each course number and corresponding number of students select cno, count(sno) from sc group by cno; --Query the student number of students who have taken more than three courses select sno from sc group by sno having count(*)>3;
2, Connection query
1. Equivalent and non equivalent connection query
The expression in where will be the join condition of the two tables
select student.*,sc.* from student, sc where student.sno=sc.sno; --Form a new tuple --Natural join, that is, there are no duplicate attribute columns
2. Self connection
In fact, it is to give the same table an alias
select first.cno, second.cpno from course first, course second where first.cpno=second.cno;
3. External connection
I think it is a bit like a full connection, that is, in the above connection, if the tuple on the connection is not successful (does not meet the limit in where), it will be discarded and will not be displayed. The external connection will display all tuples in the two tables. If any tuple does not meet the where limit, it will still be displayed, and the other half that is not matched will be filled with null.
Therefore, it is divided into left outer connection and right outer connection. If it is an outer connection, all tuples in the left relationship will be displayed.
select * from student left out join sc on(student.sno=sc.sno); --use on To limit --It can also be used using Remove duplicate results, from student left out join sc using (sno);
4. Multi meter connection
Is the connection of more than two tables
It's no different from two watches
3, Nested query
a key
Select from where is called a query block
1. Subquery with in predicate
In fact, the idea is a step-by-step query.
Query students studying in the same department as xx
First query the Department where xx is located, and the result is' CS'. In the query, the students whose major is equal to CS are limited
select sno, sname, sdept from student where sdept in( select sdept from student where sname='xx');
It is worth mentioning that this query process is not unique. One is outward processing. The child query is executed first, and the parent query is executed with the results. It may also be a self connection. Therefore, there are many ways to implement query requests.
This example is an unrelated sub query, that is, the query criteria do not depend on the parent query.
The following example is a related sub query.
2. Subquery with comparison operator
--Find out the course number of each student that exceeds his own elective course select sno, cno from sc x where grade >= ( /*Average score of a student*/ select avg(grade) from sc y where y.sno = x.sno);
In this sub query, those related to the parent query are called related sub queries
3. Subquery with any or all predicates
/*Query the names and ages of students younger than all students in the computer department in the non computer department*/ select sname, sage from student where sage < all ( select age from student where sdept='cs') and sdept <> 'cs';
4. Subquery with exists predicate
Subqueries with exists do not return data, but only logical true and false values
/*Query all students who have taken course No. 1*/ select sname from student where exisits( /*Judge whether the student has taken course No. 1*/ select * /*select here is meaningless. It is usually marked with an * sign */ from sc where sno = student.sno and cno='1');
The following is a classic query for students who have taken all courses
The idea is that there is no query. He didn't choose one course
Not exists means that the query returns true if it is empty
select sname from student where not exisits ( /*It doesn't exist. There's a case where you don't choose a course*/ select * from course where not exisit ( /*non-existent,*/ select * from sc where sno=student.sno and cno=course.sno));
I guess one of the internal situations is that we have selected a student, and now we do such things with the student's name in our hands.
Traverse the tables of all courses, take out the name of a course, and see if there is a match between the two in the sc table. There must be some found. For this student and this course, not exists returns false, and then continue to look at this student and the next course. In this way, if each student is found, it will be false after not exists, so it is empty. After the outermost not exists, it will return true because it is empty, The student is guaranteed to take all classes. Go on to the next round. If a student and a course are not found in the sc, not exist will return true. The consciousness is that the student does not choose a course, so it is wrong that there is no course selection in the outermost layer. Not exist will return false because of the true of the next layer, and the student will not be selected.
This is not a simple double negative table. You can't use exist for another layer. This is a bit like the idea of flag. You should keep a false state and finally return true, so as to ensure that you have traversed each course.
4, Collection query
union, intersect, except
/*Query students who have taken course 1 or course 2*/ select sno from sc where cno='1' union select sno from sc where cno='2';
be careful:
- The number of columns and data types of query results for collection operations should be the same
- Union will automatically remove duplicate tuples. To display them all, use union all
5, Query based on derived table
In fact, the sub query is placed in from, so the query table is the newly generated table, also known as temporary derived table
/*In the above example, find out the course number that each student exceeds the average score of their elective courses*/ select sno, cno from sc, ( select sno, avg(grade) from sc group by sno) as Avg_sc(avg_sno, avg_grade) where sc.sno = Avg_sc.avg_sno and sc.grade >= Avg_sc.avg_grade;
summary
This is to summarize the textbook content and your understanding when reviewing and consolidating data query. Data query is very basic and key. Next, we will do an experiment to see if there are any points that need to be supplemented.
Reference: introduction to database system (5th Edition), edited by Wang Shan, SA Shixuan