SELECT, DELETE, UPDATE, INSERT statement method:
Function Description:
SELECT: Mainly performs a collection of information that satisfies certain conditions from one or more tables from the database.
Delete: Mainly performing information from the table from the database that meets a certain condition.
Update: Update the information in the database table in accordance with certain conditions.
INSERT: Insert a data to the database table.
1, SELECT statement:
SELECT SELECT_LIST
From Table_Source
[Where search_condition]
[Group by group_BY_EXPIPRESSION]
[Having Search_Condition]
[Order by Order_ExpResion [ASC | DESC]]]
(1) The most basic SELECT statement is:
SELECT SELECT_LIST
From Table_Source
Among them, select_list specifies the name of the column to be included in the result set, and has a comma between multiple columns:
Table_source is the table name to query.
For example: a. If the information in the table is queried, use "*" instead of column name.
Select * from MCHK
b. Specify an alias in the result set
SELECT SPBH AS drug number, HSHSJ AS Tax Price from SPKFK
c. Specify an alias for the table name.
SELECT SPBH, HSHSJ from SPKFK A
d. Eliminate the rows of repeated results in the result.
Select Distinct SPID from SPLSK
e. Return to limited results.
Top n [percent]
SELECT TOP 10 DJBH, HSJE
From cwk
Returns the top 10 records.
SELECT TOP 10 Percent Djbh, HSJE
From cwk
Returns 10% of the query results.
(2) Select the query.
a. Inquiry based on comparison conditions
Tax amount equal to 20,000
Select * from cwk where hsje = 20000
Tax amount is greater than 5
Select * from cwk where hsje> 20000
Conditions: =,>,> =, <, <=, <>
b. Suites based on scope conditions
BetWeen Keyword Between and
Query between '2002/04/1' and '2002/05/22'
Select * from cwk where rq between '2002-04-01' and '2002-05-22'
c. Based on list condition query
Key words
Select * from spkfjc where kcshl in (100, 1000, 10000)
d. Query based on string matching conditions.
format:
SELECT SELECT_LIST
From Table_Source
Where expression like 'string'
A wildcard can be included in the string:
1.%: Represents any of the characters, A% indicates the string beginning with A.
% A indicates a string ending with A,
% A% means a string of A in the middle.
Inquiry purchase
Select * from cwk where djbh like 'jha%'
2 ._ (Underline): Represents a single character.
Query Wuhan merchant information according to the number
Select * from mchk where danwbh like 'WH00_'
3. []: Represents a single character within the specified range, [] can be a single character ([ASD]), or
It is a character range ([A-H]) querying the pin agent information in the SPKFK.
SELECT * from SPFK Where SPBH Like '[Z]%'
4. [^]: The representative is not in a single character within the specified range, [] can be a single character ([asd]), too
Can be a range of characters ([A-H])
Query the non-needle information in the SPKFK.
SELECT * from SPFK Where SPBH Like '[^ z]%'
e. Inquiry based on unknown value (NULL).
Null values actually refer to an unknown, non-existent, non-applicable information, usually represented by null.
Query price is empty information collection
Select * from spkfk where spid is null
f. Select query results based on multiple conditions.
In the WHERE statement, you can use a logical operator to connect multiple conditions to form a complex condition.
Inquire. There are three logical operators:
1 and: It connects two conditions, if both conditions are set up, the combined condition is established.
2 OR: It connects two conditions if one of the conditions is true, and the combination conditions are set.
3 NOT: It draws a condition that takes the value of the condition.
The query did not happen to the data between '2002-04-01' and '2002-05-22'
Select * from cwk where rq NOT BETWEEN '2002-04-01' and '2002-05-22' and djbh like 'xsa%'
Query between '2002-04-01' and '2002-05-22'
Select * from cwk where rq betWeen '2002-04-01' and '2002-05-22' and djbh like 'xsa%'
The query occurred in less than or equal to '2002-04-01' and more information from '2002-05-22'
Select * from CWK
WHERE RQ <= '2002-04-01' or RQ> = '2002-05-22'
g. Sort by the query results
format:
SELECT SELECT_LIST
From talbe_source
Where search_condition
Order by Order_Expression [ASC | DESC]
Among them, ORDER_EXPRESSION is the column name of sorting, and there can be multiple column names. ASC
Preface, DESC is sorted in descending order, default is ASC
Query information that meets WHERE conditions is arranged in descending order.
Select * from cwk where rq betWeen '2002-04-01' and '2002-05-22' and djbh like 'xsa%'
Order by RQ DESC
(3) Data group (Northwind database)
GROUP BY
Use Group By to group the results of the queries to packet with a certain condition, then the number of each group
According to the calculation statistics.
Format: SELECT SELECT_LIST
From Table_Source
Where search_condition
Group by group_by_expression
For example, check the total number of goods sold.
SELECT SPID, SUM (SHL) from Ywmxk
GROUP BY SPID
B.having
The Having clause is used to add filter guidelines to the query using the group By clause, syntax and WHERE
Like, but where where WHER is for a single line, Having is for a group. Difference: 1. When queries, first filter out records that do not meet the conditions in WHERE, and the having clause is divided
After use.
2. Having can include the aggregate function in the clause, but WHER is not.
For example: check the total number of goods sold, the number of solders is greater than 30.
SELECT SPID, SUM (SHL) AS SHL from YWMXK
GROUP BY SPID
Having Sum (SHL)> 30
C.Compute with compute by
The Compute clause can be used to calculate the summary information.
For example: Viewing Date 1994/09/13 After selling the total number of goods,
SELECT SPID, SHL AS SHL from Ywmxk Where Djbh Like 'JHA%'
Compute SUM (SHL)
Compute BY can group query results in a given condition, and calculate summaries for each group
SELECT SPID, SHL from Ywmxk Where Djbh Like 'JHA%'
Order by SPID
Compute Sum (shl) by SPID
If compute B is used, you must use the order by, and the compute BY appears.
The order of the columns must be the same as the order of ORDER BY.
(4) Subproof in t_sql
There are two types of subquery: simple and associated.
Subquerry refers to the SELECT clause in another T-SQL statement, in general, child check
Inquire about WHERE or HAVING in another SELECT, INSERT, UPDATE or DELETE statement
Phrase.
a. Simple subquery
For example: query sales outbound information of sales of sales goods
Select DJBH, HSJE from CWK Where DJBH in
(Select DJBH AS SHL from Ywmxk Where DJBH LIKE 'XSA% "
Group by Djbh Having Sum (SHL)> 100)))
b. Joint and subquery
For example: query sales document information is also displayed at the same time
Select a.djbh, a.hsje, b.dwmch from cwk a join mchk b
ON A.dwbh = B.dwbh
Where a.djbh like 'xsa%'
In most cases, the linkage in the SELECT statement is easier than one equivalent, high efficiency, I
They should use the link to replace the child query as much as possible to achieve better performance.
c. Why sometimes you must use a child query.
For example: SELECT A.DJBH, A. HSJE, B.DWMCH from CWK a Join MCHK B
ON A.dwbh = B.dwbh where a.djbh like 'XSA%'
And a.hsje <(SELECT AVG (HSJE) from cwk where a.djbh like 'xsa%')
Because we cannot compare the polymer value -avg (hsje), and non-polymerizable value hsje.
(6) Outer joint, cross-linking and self-linking.
A Outline: Execution of the vast majority of queries, when the user retrieves the information during multiple tables, the standard internal
The link is sufficient. But some case is that when the user wants to retrieve, there is no public in two tables.
When the total information is used, the use of an external joint and crosslinking is useful.
Type of external linkage: left link, right link, all-out.
The difference between the left link and the right link is only different from the order of the surfactory in the FROM phrase.
The entire outer linkage is simultaneous use of left and right links.
Example 1 Select all items and its sales using the left link. If we don't sell, we also want to see it. SELECT A.SPID, A.SPMCH, ISNULL (SUM (B.SHL), 0) AS SHL
From spkfk a left join ywmxk b on a.spid = B.SPID
Where b.djbh like 'XSA%'
Group by a.spid, a.spmch
If the information of the item that is not sold in the inner connection will not be displayed,
The purpose of using the isnull function: turn NULL to 0. Isnull (Check_Expression,
Replacement_Value).
Example 2 Only the name of the product that is not sold.
SELECT A.SPID, A.SPMCH, ISNULL (SUM (B.SHL), 0) AS SHL
From spkfk a left join ywmxk b on a.spid = B.SPID
WHERE B.DJBH LIKE 'XSA%' and B.SPID Is Null
Group by a.spid, a.spmch
B cross-link.
All rows of all tables perform Cartesol.
For example: Generate a list between all suppliers and all items.
Select a.dwmch, b.spmch
From mchk a cross Join SPKFK B
c. Self-link.
Self-linking: is not a special connection, it is actually inner joint and outer links in the same table
.
For example: Select the list of the same tax price.
Select Distinct a.hshsj, a.spmch
From SPKFK A
Join SPKFK B on a.hshsj = b.hshsj
And A.SPID <> B.SPID
(1) SPID does not say that the record is not associated with yourself.
(2) There is a duplicate information in Distinct.
(7) Merge:
Union is a method that combines two or more queries to a result set, and the result of the merger contains
All queries in the parallel group.
