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:

1. The number of columns and data types of query results for collection operations should be the same
2. 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