SQL Study Notes III various forms of select statements

Copy codeThe Code is as follows:
Select * from T_Employee
Select FName, FAge from T_Employee
Select FName from T_Employee where FSalary <5000
Select FName as name, FAge as age, FSalary as monthly salary from T_Employee where FSalary <5000
Select FName as name, FAge as age, FSalary as monthly salary, getdate () as current time from T_Employee


3.3.1 SELECT command format and basic usage
Data Query is the most common operation in a database.
The SQL language provides SELECT statements to obtain the required information through the query operation.
The general format of SELECT statements is:
SELECT <column Name> [{, <column Name>}]
FROM <Table name or view Name> [{, <Table name or view Name>}]
[WHERE <Search Condition>]
[Group by <column name 1> [HAVING <conditional expression>]
[Order by <column name 2> [ASC | DESC];
Ø the query result is still a table.
The execution process of the SELECT statement is:
Ø based on the search conditions of the WHERE clause, SELECT the tuples that meet the conditions FROM the basic table or view specified by the FROM clause, and then project the results table according to the columns specified in the SELECT clause.
If a GROUP clause exists, the query results are grouped according to the same value of <column name 1>.
If the GROUP clause has a HAVING phrase, only the tuples meeting the HAVING condition are output.
If an ORDER clause exists, the query results must be sorted by the value of <column name 2>.
Section 3.21 describes the student ID, name, and age of all students.
Select sno, SN, AGE FROM S
Ø example 3.22 query all the student information.
SELECT * FROM S
Ø use '*' to indicate all column names of the S table, instead of listing them one by one.
Ø example 3.23 query the student number of the course selected.
SELECT DISTINCT SNO FROM SC
The duplicate rows in the query results are removed.
Ø all the preceding queries are unconditional queries without the WHERE clause, also known as projection queries.
In addition, projection queries can be used to control the order of column names, and the names of Column Titles in query results can be changed by specifying aliases.
Example 3.24 query the name, student ID, and age of all students.
Select sname name, SNO, AGE FROM S
Ø the NAME is the alias of SNAME.
3.3.2 conditional Query
If you want to find rows that meet certain conditions in the table, you must use the WHERE clause to specify the query conditions.
In the WHERE clause, conditions are generally described in three parts:
1. Column name;
2. Comparison operators;
3. Column name and constant.








3.3.2.1 compare the size
Example 3.25 query the student ID and score of the elective course number 'c1.
Select sno, score from SC WHERE CNO = 'c1' Ø in case 3.26, the student's student ID, course number, and SCORE with scores higher than 85 are queried.
Select sno, CNO, score from SC WHERE SCORE> 85
Ø 3. 3.2.2 multi-condition Query
Ø when the WHERE clause needs to specify more than one query condition, it is necessary to use the logical operators AND, OR, and not to link them into a composite logical expression.
The priority ranges from high to low: NOT, AND, OR. You can use parentheses to change the priority.
In Example 3.27, the student ID, course number, and score of the students who take the course C1 or C2 and whose score is greater than or equal to 85 are queried.
Select sno, CNO, SCORE
FROM SC
WHERE (CNO = 'c1 'or cno = 'c2') and score> = 85
3.3.2.3 determine the scope
For example, 3.28 check the instructor number, name, and title of the teacher whose salary is between 1000 and 1500.
Select tno, TN, PROF
FROM T
Where sal between 1000 AND 1500
It is equivalent
Select tno, TN, PROF
FROM T
Where sal> = 1000 and sal <= 1500
Ø in example 3.29, check the instructor ID, name, and title of a teacher whose salary is not between 1000 and 1500.
Select tno, TN, PROF
From twhere sal not between 1000 AND 1500
3.2.2.4 determine the set
The "IN" operation can be used to query the tuples whose property values belong to the specified set.
Example 3.30 query the student ID, course number, and score of the student who takes the C1 or C2 course.
Select sno, CNO, SCORE
FROM SC
Where cno in ('c1', 'c2 ')
This statement can also be implemented using the logical operator "OR.
Select sno, CNO, SCORE
FROM SC
Where cno = 'c1' or cno = 'c2 'Ø you can use "not in" to query tuples outside the specified set.
In Example 3.31, the student ID, course number, and score of the student who does not select C1 or C2.
Select sno, CNO, SCORE
FROM SC
Where cno not in ('c1', 'c2 ')
It is equivalent:
Select sno, CNO, SCORE
FROM SC
Where cno! = 'C1' and cno! = 'C2 'Ø
3.3.2.5 partial matching Query
All the above examples are completely matched queries. If you do NOT know the exact condition, you can also use LIKE or not like for partial matching queries (also called fuzzy queries ).
The general format defined by LIKE is:
<Property name> LIKE <String constant>
The attribute name must be character type. The character of a String constant can contain the following two special characters:
Ø %: indicates any string with a known length;
_: Represents any single character.
Example 3.32 query the instructor ID and name of all instructors surnamed Zhang.
Select tno, TN
FROM T
Where tn like 'sheet %'
In Example 3.33, the second Chinese Character in the name is the instructor ID and name of "force.
Select tno, TN
FROM T
Where tn like '_ force %' Ø Note: one Chinese Character occupies two characters.
3.3.2.6 null Query
The NULL value of a field ).
Ø when a column is not input, the column value is null.
The null value is different from zero and space, and does not occupy any storage space.
For example, some students do not take the test after the course is selected, and there is a Course Selection Record, but there is no test score, and the test score is null, which is different from taking the test, the score is zero.

Example 3.34 query the student's student ID and the corresponding course number without examination scores.
Select sno, CNO
FROM SC
WHERE SCORE IS NULL
Note: The NULL condition here is null and cannot be written as SCORE = NULL.