3.3 Data Query

Section Three data query

(i) format

SELECT [All|distinct] < target column expression >[,< target column expression >]...from < table name or view name >[,< table name or view name;] ... [where< conditional expression;] [Group by< column name 1>[having < conditional expression;]] [Order by< column name 2>[asc|desc]]

1. Target column expression */table name. */count (*)/Field name expression

2. Conditional expressions

(ii) Classification Introduction query command

Single-table queries, connection queries, nested queries, collection queries

One, single-table query

1. Select several columns in the table

1 Select sno,sname from student;2 select * from Student;3 select Sname,200-sage from Student;

2. Select several tuples in the table

1 Select Sname from Student where sdept= ' CS '; 2 Select Sname from S where Sage beteween and 30; 3 Select Sname from S where Sage in (' CS ', ' MA ', ' is '); 4 SELECT * from S where Sno like ' 200215121 '; 5 SELECT * from S where sno= ' 200215121 '; if the like is not followed by a wildcard, it can be equal to = 6 Select Sname from S where Sname ' Liu ';  restaurateur  Chenelle Ming 7 Select Sname from S where Sanme like ' Liu _ ';      Liu Beliuming 8 Select * from Course  where the Cname like ' db\_%i__ ' escape ' \ '; the query starts with Db_, and the last 3rd character is I course 9 Select Sno from SC where Gr Ade is NULL;  Is cannot be written as =10 and above or

3. Sort the query results

Select Sno from SC where cno= ' 3 ' ORDER by Grade Desc;

4. Using Library functions

Selsect Average (*) from SC;

5. Grouping Query Results

Select Sno from SC GROUP by Sno have Count (*) >3;

Second, connection query

1. Format:

1 [< table name 1>.] < column name 1>< comparison operator >[< table name 2>,]< column name 2>2 [< table name 1>.] < column name 1>between[< table name 2>.] < column name 2>and[< table name 3>.] < column name 3>

1. Equivalent and non-equivalent connections

Inquire about the situation of each student and his elective course selece student.*,sc.* from STHDENT,SC where STUDENT.SNO=SC. Sno;

2. Self-Connection

Course (CNO Course number, CNAME course name, Cpno lesson, Ccredit credits)

Because the course table has only direct first class, there is no indirect class, so we need to take two aliases for Corse, namely first and second, first (Cno, CNAME, Cpno, Ccredit) second (Cno, CNAME, cpno, Ccredit)

Find indirect electives for each course select first. Cno, second. Cpno from Course first,course second where first. Cpno=second. Cno;

3. Outer connection: Left outer connection, right outer connection

Outer joins: In a typical connection, a tuple that satisfies only the join condition can be output as a result. For example, there are 200215123 and 2,002,151,252 students in student who have no elective courses, and in the SC table, there is no corresponding selection record.

So when you need to list all of the students ' course selection information, they discard 200215123 and 2,002,151,252 students (left connection).

If a student does not choose a class, throw the discarded student tuple in the result relationship and fill in the null on the properties of the SC table, then you need to use an outer join.

Left outer JOIN lists all tuples that do the edge relationship, and the right outer join lists all tuples for the right relationship

List information for all students, including students who have not selected the course select Student.sno,sname,ssex,sage,sdept,cno,grade from Student left out JOIN SC on (student.sno= Sc. Sno);

4. Compound Condition Connection

There are multiple join conditions in the where

Select student.* from STUDENT,SC where STUDENT.SNO=SC for all students who have been on course 2nd and have scored more than 90 points. Sno and SC. Cno= ' 2 ' and SC. grade>90;

Three, nested query

A query that is nested in another query that is fast in a where statement or in the condition of having a phrase becomes a nested query

1. Query with In

Query with "Liu Chen" students studying in the same department select Sno,sname,sdept from Student where sdept in (select Sdept from Student where sname= "Liu Chen");

Of course, the subject can also be used in its own query to do:

Select S1. Sno,s1. Sname,s1. Sdept from Student S1, Student S2 where S1. Sdept=s2. Sdept and S2. Sname= "Liu Chen":

2. Subqueries with comparison operators

Similarly, suppose a student can only learn in one department, so the upper in can be substituted with =

Select Sno,sname,sdept from Student where sdept= (select Sdept from Student where sname= "Liu Chen");

3. Subqueries with predicates of any some all

Query the name and age of a student in another department who is younger than the computer science department select Sname,sage from Student where Sage<any (select Sage from Student where sdept= ' CS ') a nd sdept <> ' CS ';

Check the names and ages of students in other departments who are younger than all students in computer science Department select Sname,sage from Student where Sage<all (select Sage from Student where sdept= ' CS ') and sdept<> ' CS ';

4. Sub-query with EXISTS predicate

exists represents the existence of quantifiers, with exists not returning any data, but returning flase or True

Check the names of all students who have enrolled in course 1th select Sname from Student where sno= (select Sno from SC where cno= ' 1 '));

Select Sname from Student where exsts (select *from SC where Sno=student.sno and cno= ' 1 ');

Four, set query

The collection query mainly includes the union, the intersection operation intersect, the difference operation except. Attention! The number of columns for each query result that participates in the collection operation must be the same, and the data type of the corresponding item must be the same

Enquiry the name of the student who has enrolled in course 1th or 2nd, select Sname from Student where sno= (select Sno from SC where cno= ' 1 'or cno= ' 2 ');

inch (' 1 ', ' 2 '));

Union Select Sno from SC where cno= ' 2 ');

3.3 Data Query