Single Table QueryI. Basic Query (Unconditional Query)

1, query the student number, name and age of all the students

select Student Number (SNo), Name (SN), Age (Age)

from table name (S);

1

2

2, query all the information about the student

select *

from table name;

1

2

3

" * " means to display all the information

The above queries are unconditional query without where clause, also known as the projection query

The second conditional query

When you want to use the where clause to query a student, you need to use the where clause. strong>

When you want to find out in the table to meet some of the conditions of the rows, you need to use the where clause to specify the query conditions

where

1, query the elective course number for the 'C1' number and grades

select 学号,成绩

from table name

where 课程号='C1'

;

1

2

3

4

2, query the student number, course number, and grade of students whose grades are higher than 85

select 学号,课程号,成绩

from table name

where Grade>85

;

1

2

3

4

3. Query the student numbers, course numbers and grades of students who have taken C1 or C2 and scored more than or equal to 85 points

select student number,course,grade

from table name

where(course number='C1'or course number='C2')and( grades>=85)

;

1

2

3

4

in

4, query the student number, course number and grades of the students who did not take C1, or C2

select student number, course number, grades

from table name

where (course number = 'C1' or course number = 'C2') and('C2') and('Grades>=85)

;

1

2

3

4

in

4. where course number not in('C1','C2')

1

2

3

like

5, query the teacher number and name of all teachers surnamed Zhang

select Teacher number,Name

from table name

where Name like 'Zhang%'

;

1

2

3

4

order by

sorting, often at the end of the statement, desc for descending, asc for ascending

group by & having &. count

having is often used in conjunction with group by, filtering the results of group by

6, the query elective more than two (including two) courses of the student's number and the number of elective courses

select number, count (*)

from table name

group by number< /p> having (count(*)>=2)

;

1

2

3

4

5

7, for more than three courses (including three) and each course passes the students and their total grades, the query results in descending order by the total grades

select student number, sum(grade)

from table name

where by student number, count(*)

group by student number<

To determine the number of students and the number of classes they have taken

, select student number and the number of classes they have passed, select student number and the total grade of each course. from table name

where(grade>=60)

group by student number

having (count(*)>=3)

order by sum(grade) desc

;

1

2

3

4

5

6

7

Program execution process:

from table name, take out the whole table

where filter out the grade >=60

group by will be selected, grouped by the number of students

having filtered out the selection of more than three groups

select in the remaining groups to extract the number of students and the total grades

order by will be selected the results of the order by