1. Query all the information in the data table
?Query all the information of all students in the student table (student) (equivalent to querying the entire table)
select* from Student
operation result:
Supplementary knowledge:
‘ * ‘ (character asterisk) means all .
2. Query some attributes in the data table
?Query the student number (Sno) and name (sname) of all students (some attributes in the query table)
select Sno,sname–表示需要查找的属性
from Student–属性所属表格
3. Display the properties that need to be queried in Chinese.
Query the student’s name (Sname), student number (Sno), home address information (home_addr), and display it in Chinese prompts.
select Sname as 学生姓名,Sno as 学号,home_addr as 家庭地址
from Student
operation result:
Supplementary knowledge:
as is an alias keyword, sometimes it can be omitted. It is used for attribute interpretation.
General usage:
select 字段 as 别名 from 表名
?Condition query
Of course, in real life, we not only need to query all the values or a few values in the table, but we often need to query the values in the table that meet a certain condition. This type of query is called conditional query.
For example:
(1) Query the student number (Sno), course number (Cno) and grade (grade) of students with scores > 80
select Sno as 学号,Cno as 课程号,grade as 成绩
from sc
where Grade>80–只有满足条件的数据才会被显示出来
(2) Query the student numbers of students who have taken the course number “002” and whose grades are greater than 80.
select Sno as 学号
from sc
where Cno=’002’and Grade>80-
-and相当于c语言中的逻辑与(&&),只有左右两边条件都满足才行.
–or相当于c语言中的的逻辑或(||),只要左右两边条件满足任何一个或者都满足都会执行.
(3) Query the student number and corresponding course number of the students who have taken the course but did not take the exam.
select Sno as 学号,Cno as 姓名
from SC
where Grade is null–null表示为空
1. Data query based on IN clauseUsage of IN — the query matches multiple fields (or a single one), allowing us to specify multiple values in the WHERE clause.
Query a single field
– Check out all the information about advanced mathematics and C language programming from the curriculum.
Tip: Cname indicates the course name
select *from Course
where Cname in (‘C语言程序设计’ ,’高数’)–表示课程名可以为(里面的数据)
operation result:
2. Data query based on BETWEEN…AND clause
BETWEEN A AND B : Indicates to query the data between A and B, that is, [A, B].
For example:
– Query the student number, course number and grades of students whose grades are between 75 and 80 (closed interval)
select Sno as 学号,Cno as 课程号,grade as 成绩
from sc
where Grade between 75 and 80
3. Query based on LIKE clause
(1) From the student table, retrieve the information of all students whose surname is Zhang or the information of all students whose second character is “hong” or “hong”.
Supplementary knowledge:
Wildcards in SQL:
①: ‘%’ Any string containing zero or more characters:
②: ‘_’ (underscore) any single character:
select *from Student
where Sname LIke ‘张%’ or Sname LIKE ‘_红%’ or Sname LIKE ‘_虹%’
–张% 表示姓张的信息.
–_红%和_虹%分别代表名字的第二个字是”红”或”虹”的信息
(2) Query the credits of the course named Visual_Basic.
Since ”_” (underscore) is a wildcard character, it is necessary to escape ” (underscore) to an ordinary character when searching
ESCAPE represents an escape character, making the following wildcard ” (underscore) an ordinary character
select Credit from Course
where Cname LIKE ‘Visual/_Basic’ ESCAPE ‘/’–Indicates that the character after the ‘/’ character is an escape character
4. Use the TOP keyword query
Supplementary knowledge:
Usage of the TOP keyword:
1) Return a certain number of records
Syntax format: SELECT TOP n <list name>FROM<table name>[query condition]
1
For example: retrieve the information of the first 3 courses from the course selection table.
Select Top 3 *
From Course
1
2
2) Returns the number of records in the specified percentage of the result set
Syntax format: SELECT TOP n PERCENT<list name>FROM<table name>[query condition]
1
For example: Retrieve the top 20% course information from the class schedule.
select top 20 percent *from Course
1
5… Eliminate duplicate rows (distinct)
The keyword distinct is used to return uniquely distinct values.
Format:
1. Act on a single column select distinct name from A
2. Act on multiple columns select distinct name, id from A
1
2
3
Retrieves the course number of the course that the student has selected, and requires that the displayed courses are not repeated.
select distinct Cno from Course
1
6. Time function: getdate().
The getdate() function obtains date/time information, which can be used to obtain the current time without parameters.
method one:
select getdate() — get the current time
1
Method Two:
print getdate()–Note: In SQL statements, print is different from printf in c language.
1
Result 2:
Example:
Query the names and ages of all students
There is no age attribute in our table, but we have the student’s date of birth, so we can get the age by calculating (current date – date of birth).
select Sname as Name ,year (getdate())-year(Birth) as Age from Student
1
Supplementary knowledge:
select year(getdate())–Indicates the acquisition year 2022
,month(getdate())– means to get the month 12
,day(getdate())–Indicates the number of days in the date 11
1
2
3
7. Use the ORDER BY statement to sort the results of the query.
Supplementary knowledge:
1.desc means descending, asc means ascending,
2. ORDER BY means sorting
3. getdate() means to get the current time, year (time) means accurate to ‘year’
(1) Display the basic information of all students’ names, arranged by class number, and arrange by student number if the class number is the same.
select *from Student
order by Classno, Sno– means to arrange by class number, if the class number is the same, arrange by student number
1
2
(2) Query the names and ages of all students, and arrange them in descending order of age.
select Sname as Name ,year (getdate())-year(Birth) as Age from Student
order by ‘age’ desc– means sorted in descending order
1
2
operation result:
?Using aggregate functions
count function
Supplementary knowledge:
The COUNT() function returns the number of rows in the table. The COUNT() function allows you to count all rows in a table that match a certain criteria.
The COUNT() function returns the number of rows in the result set returned by the SELECT statement. The COUNT() function counts rows that contain NULL and non-NULL values, that is, counts all rows.
(1) Query the total number of students.
select count (distinct Sno) as the total number of students from Student–distinct means to calculate the number of rows of different student numbers, that is, the total number of students.
1
(2) Calculate the average score, the highest score, and the lowest score of the students in the “002” course.
The avg() function is used to calculate the average
The max() function is used to calculate the maximum value
The min() function is used to calculate the minimum value
select avg(Grade) as average score, max(Grade) as highest score, min(Grade) as lowest score from sc
where Cno=’002′
1
2
operation result:
Queries using the Group clause
group, as the literal meaning, means grouping, and group by means grouping according to a certain rule. Divide a data set into several small areas.
For example:
(1) Query the total number of students in each class.
Tip: sort by class
select Classno as class, COUNT(*)as number of students
from Student
group by classno–classification by class
1
2
3
operation result:
(2) Summarize the student numbers and total scores of students with a total score greater than 150.
Supplementary knowledge:
having is similar to where, the difference is
where: Delete the rows that meet the conditions,
having: Delete the groups that meet the conditions.
select Sno as student number, sum (Grade) as total grade
from sc
group by Sno
having sum (Grade)>150
1
2
3
4
(3) Query the corresponding number of course candidates for each course number.
select Cno as course number, count(*) as number of students
from SC
group by Cno–classification by course number
1
2
3
operation result:
