Database principle-data single table query

Format: SELECT [ALL|DISTINCT] < target column expression >,[target column] ...

From < table name or view name >[,< table name or view name >| ( Seslect statement) [as]< alias;]

[where < condition name;]

[Group by< column name 1>[having < conditional expression;]]

[Order by< column name 1>[asc|desc]]

A single-table query selects several columns in a table 1. Query all columns SELECT * from student2. Query computed column Select 2012-age from student//2012-age is not a column name, but an expression that calculates the year of birth of the student 

Select several tuples in a table

1. Cancel duplicate rows

SELECT DISTINCT SNO from SC//Remove duplicate rows in table must have a phrase distinct, if not specified, the default value is all

2. Querying a tuple that satisfies a condition

• • Compare size

Select Sname age from student where age<20

• • Determine scope

Find the names of students aged 20-23 years (including 20 and 23 years)

Select Sname,sdept from student where age between and 23

Find the names of students who are not 20-23 years of age (including 20 and 23 years old)

Select Sname,sdept from student where age not between and 23

• • Determining the Collection

Select Sname,sex from student where sdept in (' Is ', ' math ')//predicate in used to find the attribute value belonging to the specified set

Select Sname,sex from student where sdept not in (' is ', ' math ')//predicate in used to find the attribute value belongs to the specified set

• • Character matching

Format: Like ' Match string '

The matching string can be either a complete string or a wildcard character% and _

% (Percent semicolon) represents a string of any length (can be 0)

_ (lower line) represents any single character

Information for students who have a student number of 001

SELECT * FROM student where sno= ' 001 '//Match string does not contain wildcard characters, you can use = instead of like, with! = or >,< instead of not-like

Find information for all students surnamed Liu

SELECT * FROM student where sname like ' Liu '

Check the names of students surnamed Zhang with a full name of 3 characters

Select sname from student where sname like ' Zhang _ _ _ '//A Chinese character occupies two characters in position

• • Null value query

Select Sno,cno from SC where grade is NULL//Note that the is cannot be substituted with equals =

• • Multiple criteria Query

And and or can be linked to multiple query conditions, and the priority of and is higher than or, the user can change the priority with parentheses

Check the name of a computer department student under the age of 20 years

Select sname from student where sdept= ' CS ' and age<20

ORDER BY

Select Sno,grade from SC where cno= ' 3 ' ORDER BY grade DESC

For null values, sorted in ascending order, tuples with null values will be displayed last, sorted in descending order, tuples with null values will be displayed first

Aggregation FunctionsCount Distinct/all * Calculates the number of tuples count distinct/all column name counts the number of values in a column sum distinct/all column names calculate the sum of a column of values that must be numeric avg Distinc T/all column names calculate the average of a column of values, which must be numeric max distinct/all column names calculate the maximum value of a column min distinct/all column name calculates the minimum value of a column value

If you specify distinct, the calculation cancels the duplicate value, which defaults to all, indicating that duplicate values are not canceled

Total number of students queried

Select Count * from student

Check the number of students who have enrolled in the course

Select COUNT (Distinct sno) from SC

Calculate the average student score for 1th courses

Select AVG (grade) from SC where cno= ' 1 '

Maximum number of students who are enrolled in the 1th course

Select Max (grade) from SC where cno= ' 1 '

Group by sentence for each course number and the corresponding number of courses select Cno,count (SNO) from the SC group by CNO inquires the number of students enrolled in more than 3 courses select Sno from SC GROUP by Sno Having a count (*) >3//having phrase Specifies the criteria for selecting a group, and only groups that meet the criteria are chosen to distinguish between the WHERE clause and the having phrase, which are different from the acting object. The WHERE clause acts on the base table or view from which you select a tuple that satisfies the criteria. The having phrase acts on the group from which the group that satisfies the condition is selected.

Database principle-data single table query