Typical examples of SQL statements

Section 1. Data Table query (select)

Select field list [as Alias], * from data table name

[Where Condition Statement]

[Group by field]

[Order by sort Field List DESC]

[Limit startrow, rownumber]

1. Select field list from data table

Example: ① Select ID, gsmc, add, tel from haf (* indicates all fields in the data table)

② Select unit price, quantity, unit price * quantity as total amount from haf (as sets the field alias)

2. Select... From... Where filter Condition

Filter condition: ①. String data: Select * From transcript where name = 'lilim'

② Million character: Select * From transcript where name like 'Lee %'

Select * From transcript where name like '% Li %'

Select * From transcript where name like '% Li _'

③ Special condition:

(1) =/>/<>/> =/<=

(2) and (logical and) or (logical or) Not (logical not)

(3) Where field name in (value 1, value 2)

(4) Where field name is null/where field name is not null

3. Select... From... Group by field

SQL functions:

Select sex, count (ID) as women from 'user' group by 'sex ';

Function Name Description

AVG average count

Max Min

Sum

4. Select... From... Order by Field List DESC (inverted, if directly written in order)

5. Select... From... Limit ". $ start_rowno.", ". ($ pagesize + 1)

Section 2 SQL statement instance application

Database description:

Student (student table ):

Stdid int (11) ID

Son char (5) Student ID

Sname char (20) Name

Ssex tinyint (1) Gender

Sage char (3) Age

System of sdept char (20)

Course (course schedule ):

CouId int (11) ID

CNO char (5) course No.

Cname char (20) Course name

Cpno char (6) electives No.

Ccredit char (50) Credits

SC (student selection table ):

SCID int (11) ID

CNO char (5) course No.

Grade float score

Sno char (5) Student ID

Single Table query:

1. select several fields in the table:

Query a specified column:

1. query the student ID and name of all students;

Select son, sname from student

2. query the name, student ID, and Department of all students;

Select sname, son, sdept from student

3. query detailed records of all students;

Select * from student

Query calculated values:

4. Check the names of all students and their birthyears.

Select sname, year (now ()-sage as 'Year of birth 'from student

5. query the names, year of birth, and all departments of all students. Use uppercase/lowercase letters to indicate all department names.

Select sname as 'name', 'birthday', year (now ()-sage as 'birthyear', upper (sdept) as 'bin' from student

Select sname as 'name', 'birthday', year (now ()-sage as 'birthyear', lower (sdept) as 'bin' from student

2. select several records in the table:

Remove rows with duplicate values:

6. query the student ID of the Selected Course

Select distinct SnO from SC

Query records that meet the conditions:

Compare size:

7. query the list of all students on the computer

Select sname from student where sdept = 'cs'

8. query the names and ages of all students under the age of 20.

Select sname, sage from student where sage <20

9. query the student ID of the student whose score is less than 90

Select distinct SnO from SC where Grade <90

Scope:

10. query the name, department, and age of a student between 18 and 20 years old.

Select sname, sdept, sage from student where sage between 18 and 20

11. query the name, department, and age of a student between the ages of 19-20.

Select sname, sdept, sage from student where Sage not between 19 and 20

Confirm the set:

12. query the names and gender of students in the Information Department (is), Mathematics Department (MA), and Computer Science Department (CS.

Select sname, ssex from student where sdept in ('is, 'M', 'cs ')

13. query the name, department, and age of a student who is not in the Information Department (is) or mathematics department (MA.

Select sname, ssex from student where sdept not in ('is, 'Ma ')

Character matching (like '<matching string>' % represents a string of any length (the length can be 0); _ represents any single character, and the Chinese character must use two "__"):

14. query the details of students whose student ID is 95001

Select * from student where son like '123'

15. query the names, student IDs, and gender of all students named Li.

Select sname, son, ssex from student where sname like 'Lee %'

16. The query name contains the Student name, student ID, and gender.

Select sname, son, ssex from student where sname like '____'

17. query the names of all students not surnamed Li.

Select sname from student where sname not like 'Lee __'

Query involving null values:

18. Some students do not take the test after taking the course, so they have a Course Selection Record, but have no test scores. query the student's student ID and the corresponding course number.

Select SnO, CNO from SC where grade is null

19. query all student ID and course number with scores.

Select SnO, CNO from SC where grade is not null

Multi-condition query (and or ):

20. query the names of students aged 20 in the computer department.

Select sname from student where sdept = 'cs 'and sage = 20

21. query the names and gender of students in the Information Department (is), Mathematics Department (MA), and Computer Science Department (CS.

Select sname, ssex from student where sdept = 'is' or sdept = 'M' or sdept = 'cs'

Iii. Sorting query results:

22. query the student ID and score of the course No. 3 selected. The query results are sorted in descending order of scores.

Select SnO, grade from SC where CNO = '3' order by grade DESC

23. query the status of all the students. The query results are listed in ascending order of the Department number, and the students in the same department are listed in descending order of age.

Select * from student order by sdept, sage DESC

4. Use the set function:

24. query the total number of students.

Select count (*) as 'total number' from student

25. query the number of students who have selected the course.

Select count (distinct SnO) as 'count 'from SC

26. Calculate the average score of students of course 1

Select format (AVG (grade), 2) As 'average score 'from SC where CNO = '1'

27. query the highest score of students who take the No. 1 course.

Select max (grade) from SC where CNO = '1'

5. query result grouping:

28. Find the course number and the number of students.

Select CNO as 'course No. ', count (SNO) as' count 'from SC group by CNO

29. query the student ID that has taken more than three courses.

Select SnO from SC group by SnO having count (*)> 2

Note: The difference between a where clause and a having phrase is that the where clause acts on different objects. A where clause acts on a basic table or view and selects records that meet the conditions. A having clause acts on a group, select a group that meets the conditions.

Multi-Table query

Query more than two tables at the same time, called join query.

Equivalent join: When the join operator is =, It is equivalent join.

1. query each student and their optional courses (equivalent connection ).

Select student. *, SC. * from student, SC where student. Son = SC. SnO

Natural join: Remove duplicate attribute columns in the target column from the equijoin.

2. query the status of each student and their optional courses (natural connection ).

Select student. Son, sname, ssex, sage, sdept, CNO, grade from student, SC where student. Son = SC. SnO

Self-join: The join operation can be performed between two tables, or between a table and itself.

3. query the indirect preference of each course.

Select a. CNO, B. cpno, A. cname from course A, course B where a. cpno = B. CNO

Composite condition connection:

4. query all students who have taken course 2 and scored more than 90 points.

Select a. Son, sname from student A, SC B where a. Son = B. SnO and B. CNO = '2' and B. Grade> 90

5. query the student ID, name, and name and score of the selected course.

Select a. Son, sname, cname, grade from student A, SC B, course c Where a. Son = B. SnO and B. CNO = C. CNO

Google_protectandrun ("ads_core.google_render_ad", google_handleerror, google_render_ad );