By hmimcu 
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, which can sometimes 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.
(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 clause
Usage 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 ‘_虹%’
–张% 表示姓张的信息.
–_红%和_虹%分别代表名字的第二个字是”红”或”虹”的信息
select *from Student
where Sname LIKE ‘Zhang%’ or Sname LIKE ‘_Red%’ or Sname LIKE ‘_Rain%’
–Zhang% indicates the information of the surname Zhang.
–_红% and _红% respectively represent the information that the second character of the name is “red” or “hong”
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’/’–表示让’/’字符后的字符为转义字符
4. Use the TOP keyword query
Supplementary knowledge:
Usage of the TOP keyword:
1) Return a certain number of records
语法格式:SELECT TOP n <列表名>FROM<表名>[查询条件]
For example: retrieve the information of the first 3 courses from the course selection table.
Select Top 3 *
From Course
2) Returns the number of records with a specified percentage in the result set
语法格式:SELECT TOP n PERCENT<列表名>FROM<表名>[查询条件]
For example: retrieve the information of the first 3 courses from the course selection table.
Syntax format: SELECT TOP n PERCENT<list name>FROM<table name>[query condition]
select top 20 percent *from Course
5… Eliminate duplicate rows (distinct)
The keyword distinct is used to return uniquely distinct values.
格式:
1.作用于单列 select distinct name from A
2.作用于多列 select distinct name, id from A
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
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() –取当前时间
Method Two:
print getdate()–注意:在SQL语句中,打印是print与c语言中的printf不同.
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 姓名 ,year (getdate())-year(Birth) as 年龄 from Student
Supplementary knowledge:
select year(getdate())–表示获取年份 2022
,month(getdate())–表示获取月份 12
,day(getdate())–表示日期天数 11
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–表示按班号排列,班号相同则按学号排列
(2) Query the names and ages of all students, and arrange them in descending order of age.
select Sname as 姓名 ,year (getdate())-year(Birth) as 年龄 from Student
order by ‘年龄’desc–表示按降序排列
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 学生总人数 from Student–distinct表示计算不同学号的行数,即学生总人数.
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 平均分,max(Grade) as 最高分,min(Grade) as 最低分 from sc
where Cno=’002′
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 班级,COUNT(*)as 学生人数
from Student
group by classno–按班级分类
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: filter qualified rows,
having: Filter the groups that meet the conditions.
select Sno as 学号,sum (Grade) as 总成绩
from sc
group by Sno
having sum (Grade)>150
(3) Query the corresponding number of course candidates for each course number.
select Cno as 课程号,count(*) as 学生人数
from SC
group by Cno–按课程号分类
operation result:
