Select statement note

1. query system time

Select distinct getdate () from sysdatabases
// The distinct keyword is used here to display only one record (because there cannot be only one database in the system). You can also use top 1 for restrictions.
// For distinct, the null value is the same
// By default, select all sellist from yourtable // All displays all data.

2. query data of a specified percentage
Select Top 40 percent from Info // return the first 40% of data

3. Use an expression in the query list
Select p_id, cost, quantity, cost * quantity as total from product

4. The into statement saves the specified data in the table to another new table (it can be stored in different databases)
Select * into db1.dbo. newtable from db2.dbo. oldtable [where]
// If the calculation column (such as cost * quantity) is specified in the SELECT statement, the column corresponding to the new table is not a calculation column, but an actual column. The data is executed by the Select... Into Calculation Result
// Select... The into statement cannot be used with the compute statement.
// The target database can be a new table or a temporary table
Select * into tempdb. DBO. nettopic from test. DBO. Topic

5, inner join... On Connection query Conditions
Select employee data table. employee ID, position table. position from employee data table inner join position table on (employee data table. Position No. = position table. Position No)

6. Limit clause where
The where clause specifies the conditions for data retrieval to restrict rows of returned data.
Query conditions in the WHERE clause
Comparison operators: <, <=,>, >=, =,> ,! =,!> ,! <
Scope description:
Between A and B, not between A and B
List of optional values: In, not in
Pattern Matching: Like, not like
Null or not: Is null, is not null
Logical combination of the preceding conditions: And, or, not
// The Data Types of text, ntext, and image cannot form query conditions with comparison Operators

7. wildcard characters in the like keyword
% Any string containing zero or multiple characters.
Where title like '% Computer %' searches for all book titles containing the word "computer" at any position in the book title.
_ (Underline) any single character (two Chinese characters are required __)
Where au_fname like '_ EAN' searches for names of all four letters ending with EAN, such as Dean or Sean.
[] Is used to specify a range. For example, [A-F] indicates any single character in the range from A to F.
[^] Indicates the specified range. For example, [^ A-F] indicates any single character out of the range A to F.
// Escape the wildcard character
1. Like 'SQL # _ test' escape' # 'indicates the SQL _test string. Here, # can be used as escape characters with any uncommon symbols, however, remember to have the escape character later'
2. Use [] to describe like 'SQL [_] Test'

8. Summary function -- null
The summary function ignores NULL.
The count: Count function ignores NULL values in the object, while the count (*) function counts all records that meet the condition.
Sum: Ignore null values in the sum object
AVG: Ignore null values in the sum object
Max/min: Ignore null values in the sum object

9. subquery -- Multi-Value Comparison all
Multi-Value Comparison: multiple rows and one column
The comparison between the parent query and multi-value subquery requires the use of all for connection.
The scalar value S is greater than that in the return set R of the subquery, and S> All r is true.
All indicates all
> All, <all, <= All,> = All, <> All
<> All is equivalent to not in.
Example: Find the youngest student
Select * from students where age <= All (select age from students)
// Another method: Select top 1 from students order by age

10. subquery -- Multi-Value Comparison some/any (early use of any)
Some is required for the comparison between the parent query and multi-value subquery.
The scalar value S is larger than a value in the return set R of the subquery.
S> some r is true
> Some, <some, = Some, <> some,> = Some, <= some
= Some is equivalent to in, <> some is equivalent to not in
For example, find out the students not of the minimum age
Select * from students where age> some (select * from students)

11. subquery -- exists judgment
Exists + subquery is used to determine whether the subquery returns tuples.
If the result set of the subquery is not empty, exists is true.
If the result set of the subquery is null, the value of exists is false.
If you do not care about the specific content of the subquery, select *
Example: list the student ID and name of the c01 course.
Select SnO, sname from students where exists
(

Select * from SC

Where SC. Sno = students. SnO and CNO = 'c01'

)

// SC is the optional course schedule