Foreword
All EXAMPLE performs through the SQL2000 query analyzer (Microsoft SQL Server / 80 / Tools / Binn / IsqLw.exe). (* The following EXAMPLE is submitted to EX)))
* Append file branch includes a list of function types, a list of data types.
Create 2004/12/02 14:11:23
First, basic DML
Basic DML Add Data (INSERT), Change Data (Update), Delete, Query Four Models:
1. Add data:
· INSERT INTO TABLE_NAME (Column1, Column2, ...) VALUES II, Complex Query
1. Combined inquiry:
Combined query means that the source of data is not only a single table, but a combination of more than one table can be obtained.
SELECT *
From Table1, Table2
Where table1.colum1 = Table2.column1
Description:
1. Query data in the same value in both tables
2. Of course, the two forms are compared to each other, and their data must be the same.
3. A complex query that it uses the form that it might be many
EX)
Select * from Employees A, Employeterritories B Where a.employeeiD = B.employeeid
Select Distinct Lastname from Employees A, Employeeterritories B Where a.employeeiD = B.Employeeid (did not find DistINCTIS???
2. Integration query:
·
SELECT Count (*)
From table_name
Where colorn_name = xxx
Description:
Query the number of qualified data
EX)
SELECT Count (*) from Employees
· Contains a polymerization function:
Open an object browser in the SQL query analyzer to see the list of functions
The following is a commonly used polymeric function
SELECT SUM (Column1)
From table_name
Description:
1. Calculate the sum, the selected column must be a number of digital forms
2. There are also AVG () to calculate average, max (), min ()
Calculate integrated query for maximum minimum
Select Column1, AVG (Column2)
From table_name
Group by column1
Having Avg (Column2)> XXX
Description:
EX)
SELECT SUM (Unitprice) from [Order Details] *** Separate
Group by: Use Column1 to calculate the average of COLUMN2, which must be used with the keywords of the integrated query of AVG, SUM.
Select Productid, AVG (Unitprice) from [Order Details] Group by ProductID
Having: HAVING clause usually uses the use of Group By clauses as integrated limits
For example, the following WHERE clauses are limited to orders that are sold above $ 100, and the Having clause further limits the results to only more than 100 pieces of orders:
Select ORDD1.ORDERID AS ORDERID,
SUM (OrDD1.quantity) AS "Units Sold", Sum (OrDD1.Unitprice * ORDD1.QUANTITY) AS Revenue
From [Order Details] as Ordd1
WHERE ORDD1.Orderid in (Select Distinct ORDD2.Orderid)
From [Order Details] as Ordd2
Where ordd2.Unitprice> $ 100)
Group by Ordd1.Orderid
Having Sum (Ordd1.quantity)> 100
3. Complex query
SELECT *
From table_name1
WHERE EXISTS (Select * from table_name2 where conditions)
Description:
Where's consitions can be another Query
1.Exists here refers to whether there is or not
SELECT *
From table_name1
Where column1 in (Select Column1 from Table_Name2 Where Conditions)
Description
In the back, it is a collection, indicating that the data of the select exists in Column1 must comply with Column1.
EX)
Select ORDD1.ORDERID AS ORDERID,
Sum (OrDD1.quantity) AS "Units Sold",
Sum (OrDD1.Unitprice * ORDD1.QUANTITY) AS REVENUE
From [Order Details] as Ordd1
Where not exists (Select Distinct ORDD2.Order)
From [Order Details] as Ordd2
Where ordd2.Unitprice> $ 100)
Group by Ordd1.Orderid
Having Sum (Ordd1.quantity)> 100
4. Other inquiry (Like, Between, Top N)
SELECT *
From table_name1 where colorn1 like 'x%'
Description:
1.Like must and the back 'X%' Elementary indicates the string starting with X
Select * from table_name1 where column1 betWeen XX and yy
Description
1. Between indicates that the value of Column1 is between XX and YY
· About Like
Wildcard description example
% The arbitrary string containing zero or more characters. Where title Like '% computer%' will find all the book names that contain the word computer in any of the book name.
_ (Underline) any single character. WHERE AU_FNAME LIKE '_ean' will look for all 4 letters ending with EAN (Dean, Sean, etc.).
[] Specify the range ([A-F]) or any single character in the collection ([ABCDEF]). WHERE AU_LNAME LIKE '[C-P] Arsen' will look up with ARSEN's last name, for example, caSen, larsen, karsen, etc.
[^] Does not belong to any individual characters of the specified range ([A-F]) or a collection ([Abcdef]). WHERE AU_LNAME LIKE 'DE [^ l]%' will look for all the authors of DE and after the letters of L.
Select CompanyName, ContactName, Phone, FaxFrom Customers
WHERE COMPANYNAME LIKE N '% SNABBBK? P' '%'
ORDER BY CompanyName ASC, ContactName ASC; ===> *** N role ???
· About BETWEEN
SELECT TITLE_ID, YTD_SALES
From titles
Where ytd_sales not betWeen 4095 and 12000
· About Top N
Select Top 5 Orderid, ProductId, Quantity from [Order Details] Order by Quantity DESC (Value1, Value2, ...)
Description:
1. If you do not specify a column system, the data is filled in the column order in the table.
2. The data form of the column and the filled data must be consistent
3.Table_name can also be a landscape view_name
· INSERT INTO TABLE_NAME (Column1, Column2, ...) Select Columnx, Column, ... from another_table
Explanation: You can also fill in the data of other forms after a subquery.
EX)
INSERT ORDERS
(ORDERDATE, REQUIREDDATE, FREIGHT, SHIPNAME)
Values
(Convert (DateTime, '2004/12/02 1: 1: 1'), NULL, 99, 'GEC'
* This statement contains inputs for dates, numbers, characters, and null values. Among them, the date type is a recessive conversion. If comparing CHAR and DATETIME expressions SQL Server can automatically convert them.
ORDERDATE "=== '2004/12/02 1: 1: 1'
2. Change the data:
· Update Table_name Set Column1 = Value1, Column2 = Value2 ... Where Conditoins
Description:
1. Change a column settings to 'xxx'
2. Conditions is the condition you have to match. If there is no WHER, the entire table will be changed.
EX)
Update Orders
Set ORDERDATE = '
2004-1-1 1: 1
: 1 ', FREIGHT = 99, ShipName =' gecheng '
Where orderid = 10248
3. Delete the data:
· Delete from table_name where conditions
Description: Delete eligible data
EX)
Delete [Order Details]
From Suppliers, Products
Where products.supplierid = Suppliers.supplierID
And support ccompanyname = 'lyngbysild'
AND [Order Details] .productid = Products.ProductID
* [ORDER DETAILS] is a space-separated table name
4. Query data:
Basic query
· Select Column1, Columns2, ... from table_name
Explanation: All of the specific columns of Table_name are listed all.
SELECT * from home_name
Where column1 = xxx
[And color] [or column3 <> zzz]
Description:
1. '*' Means all columns are listed
2.where is after the conditional style, list the qualified data
Select Column1, Column2
From table_name
Order by column2 [desc]
Description:
ORDER BY is specified to be sorted with a column, [dec] means from big to small arrangement, without indicating [ASC], it is from small to large
EX)
Select * from Employees Where Employeeid> 3 or EmployeeiD = 1 Order by Employeeid ASC;
Combine query, integrated query, contain aggregate functions, composite queries, other queries (Like, Between .... ...) follow-up
Second, complex inquiry
1. Combined inquiry:
Combined query means that the source of data is not only a single table, but a combination of more than one table can be obtained.
SELECT *
From Table1, Table2
Where table1.colum1 = Table2.column1
Description:
1. Query data in the same value in both tables
2. Of course, the two forms are compared to each other, and their data must be the same.
3. A complex query that it uses the form that it might be many
EX)
Select * from Employees A, Employeterritories B Where a.employeeiD = B.employeeid
Select Distinct Lastname from Employees A, Employeeterritories B Where a.employeeiD = B.employeeID
(Distinguished DistINCTROW ???)
2. Integration query:
·
SELECT Count (*)
From table_name
Where colorn_name = xxx
Description:
Query the number of qualified data
EX)
SELECT Count (*) from Employees
· Contains a polymerization function:
Open an object browser in the SQL query analyzer to see the list of functions
The following is a commonly used polymeric function
SELECT SUM (Column1)
From table_name
Description:
1. Calculate the sum, the selected column must be a number of digital forms
2. There are also AVG () to calculate average, max (), min ()
Calculate integrated query for maximum minimum
Select Column1, AVG (Column2)
From table_name
Group by column1
Having Avg (Column2)> XXX
Description:
EX)
SELECT SUM (Unitprice) from [Order Details] *** Separate
Group by: Use Column1 to calculate the average of COLUMN2, which must be used with the keywords of the integrated query of AVG, SUM.
Select Productid, AVG (Unitprice) from [Order Details] Group by ProductID
Having: HAVING clause usually uses the use of Group By clauses as integrated limits
For example, the following WHERE clauses are limited to orders that are sold above $ 100, and the Having clause further limits the results to only 100 pieces of orders: select ordd1.orderid as OrderID,
Sum (OrDD1.quantity) AS "Units Sold",
Sum (OrDD1.Unitprice * ORDD1.QUANTITY) AS REVENUE
From [Order Details] as Ordd1
WHERE ORDD1.Orderid in (Select Distinct ORDD2.Orderid)
From [Order Details] as Ordd2
Where ordd2.Unitprice> $ 100)
Group by Ordd1.Orderid
Having Sum (Ordd1.quantity)> 100
3. Complex query
SELECT *
From table_name1
WHERE EXISTS (Select * from table_name2 where conditions)
Description:
Where's consitions can be another Query
1.Exists here refers to whether there is or not
SELECT *
From table_name1
Where column1 in (Select Column1 from Table_Name2 Where Conditions)
Description
In the back, it is a collection, indicating that the data of the select exists in Column1 must comply with Column1.
EX)
Select ORDD1.ORDERID AS ORDERID,
Sum (OrDD1.quantity) AS "Units Sold",
Sum (OrDD1.Unitprice * ORDD1.QUANTITY) AS REVENUE
From [Order Details] as Ordd1
Where not exists (Select Distinct ORDD2.Order)
From [Order Details] as Ordd2
Where ordd2.Unitprice> $ 100)
Group by Ordd1.Orderid
Having Sum (Ordd1.quantity)> 100
4. Other inquiry (Like, Between, Top N)
SELECT *
From table_name1 where colorn1 like 'x%'
Description:
1.Like must and the back 'X%' Elementary indicates the string starting with X
Select * from table_name1 where column1 betWeen XX and yy
Description
1. Between indicates that the value of Column1 is between XX and YY
· About Like
Wildcard description example
% The arbitrary string containing zero or more characters. Where title Like '% computer%' will find all the book names that contain the word computer in any of the book name.
_ (Underline) any single character. WHERE AU_FNAME LIKE '_ean' will look for all 4 letters ending with EAN (Dean, Sean, etc.).
[] Specify the range ([A-F]) or any single character in the collection ([ABCDEF]). WHERE AU_LNAME LIKE '[C-P] Arsen' will look up with ARSEN's last name, for example, caSen, larsen, karsen, etc. [^] Does not belong to any individual characters of the specified range ([A-F]) or a collection ([Abcdef]). WHERE AU_LNAME LIKE 'DE [^ l]%' will look for all the authors of DE and after the letters of L.
Select CompanyName, ContactName, Phone, Fax
From customer
WHERE COMPANYNAME LIKE N '% SNABBBK? P' '%'
ORDER BY CompanyName ASC, ContactName ASC; ===> *** N role ???
· About BETWEEN
SELECT TITLE_ID, YTD_SALES
From titles
Where ytd_sales not betWeen 4095 and 12000
· About Top N
Select Top 5 Orderid, ProductId, Quantity from [Order Details] Order by Quantity DESC
Third, the use of external joints
The inner linkage returns only when there is at least one of the row of lines belonging to the two tables. Inner connected eliminates the rows that do not match the other table.
The outer joint returns all the lines of at least one table or view mentioned in the FROM clause, as long as these rows meet any WHERE or HAVING search criteria. All rows of the left table referenced by the left outward joint reference will be retrieved, and all rows of the right to the right of the right outwardly. All rows of the two tables in the complete external join will return.
Microsoft SQL Server 2000 uses the following SQL-92 keywords specified in the FROM clause:
LEFT OUTER JOIN or LEFT JOIN
Right Outer Join or Right Join
Full Outer Join or Full Join
SQL Server supports SQL-92 external joint synthesis, and the old grammar specified in the WHERE clause of the * = and = * operators specified. Since SQL-92 syntax is not easy to generate ambiguity, and old TRANSACT-SQL external joints sometimes generate ambiguity, so it is recommended to use SQL-92 syntax.
· Use left to outline
Assume that the AUTHORS table and the Publishers table are coupled to the City column. The results are only displayed in the city where the publisher is located (this example is Abraham Bennet and CHERYL CARSON).
To include all authors in the results, regardless of whether the publisher live in the same city, use SQL-92 left outwardly. Below is the query and result of the TRANSACT-SQL left outward join:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
From authors a left outer Join Publishers P
ON A.CITY = P.city
ORDER BY P.PUB_NAME ASC, A.AU_LNAME ASC, A.AU_FNAME ASC
Regardless of whether it is matched to the city column in the Publishers table, Left Outer Join contains all rows of the Authors table in the results. Note: Most authors listed in the results do not match data, so the PUB_NAME columns of these rows contain null values.
· Use right outward joint
Assume that the AUTHORS table and the Publishers table are coupled to the City column. The results are only displayed in the city where the publisher is located (this example is Abraham Bennet and CHERYL CARSON). SQL-92 Right Out Outer Dip Operator Right Outer Join Indicates: Regardless of whether there is a match in the first table, the result will contain all rows in the second table. To include all publishers in the results, regardless of whether there are publishers in the city living in the city, please use SQL-92-to-right outwardly. Below is the query and result of the TRANSACT-SQL right outwardly:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
From Authors As a Right Outer Join Publishers AS P
ON A.CITY = P.city
ORDER BY P.PUB_NAME ASC, A.AU_LNAME ASC, A.AU_FNAME ASC
· Use the complete external connection
To keep mismatch information by including a row in the coupling result, use the complete external connection. Microsoft? SQL Server? 2000 provides complete external join operators Full Outer Join, regardless of whether another table has a match, this operator includes all rows in two tables.
Assume that the AUTHORS table and the Publishers table are coupled to the City column. The results are only displayed in the city where the publisher is located (this example is Abraham Bennet and CHERYL CARSON). SQL-92 FULL OTER JOIN operators indicate: Regardless of whether there is a matching data in the table, the results will include all rows in two tables.
To include all authors and publishers in the results, regardless of whether there is publisher or publisher living in the city, please use a complete external join. Below is the query and result of Transact-SQL complete external join:
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
From Authors a Full Outer Join Public Publishers P
ON A.CITY = P.city
ORDER BY P.PUB_NAME ASC, A.AU_LNAME ASC, A.AU_FNAME ASC
Fourth, string functions
These scalar functions perform an action on a string input value, return a string or a numeric value.
ASCII
Returns the ASCII code value of the left-end character of the character expression.
Nchar
The Unicode Character is returned with a given fixed number code according to the definition of the Unicode Standard.
Replace
Replace all the second giveholding string expressions that appear in the first string expression with the third expression.
grammar
Replace ('string_expression1', 'string_expression2', 'String_Expression3')
Str
The digital data is converted to character data.
Returns the character of the specified number from the left and right.
Left right
Left (character_expression, integer_expression)
Space
Returns a string consisting of repeated spaces.
grammar
Space (Integer_Expression)
Replicate
Repeat the character expression with the specified number of times.
grammar
Replicate (character_expression, integer_expression)
* To include spaces in Unicode data, use Replicate instead of SPACE.
Substring
Returns part of the character, binary, text, or image expression. For more information on valid Microsoft® SQL ServerTM data types that can be used with this function, see the data type. grammar
Substring (Expression, Start, Length)
Len
Returns a given string expression of characters (instead of bytes), which do not contain tail space.
grammar
Len (string_expression)
Returns a character expression that converts the uppercase uppercase character data.
Lower Upper
Return to a string after going to the space
LTRIM RTRIM
...
V. Date and time functions
Function determinism
----------------
DateAdd is determined
Dateadd (DatePart, Number, Date)
Select Dateadd (Day, 21, Pubdate) AS TimeFrame
From titles
DATEPART is the parameter that specifies which part to return to the date. The following table lists the date and abbreviations identified by Microsoft? SQL Server?.
Date part abbreviation
Year Yy, YYYY
Quarter QQ, Q
Month mm, m
Dayofyear Dy, Y
Day dd, d
Week WK, WW
Hour hh
Minute Mi, N
Second ss, s
Millisecond MS
Datediff has certain determinism
Returns the number of date and time boundaries across two specified dates.
Datediff (datepart, startdate, enddate)
DateName does not have certain determinism
Returns a string representing the specified date part of the specified date.
Datename (DatePart, Date)
DATEPART has certain determinism in addition to DATEPART (DW, date). DW is the date part of the working day, depending on the value set by Set DateFirst set by setting a weekly week.
Set DateFirst 7
Select datepart (weekday, getdate ()) AS 'Word Day'
Day is certain
MONTH has certain determinism
Year has a certainty
GetDate does not have certain determinism
Press the DateTime Value's Microsoft? SQL Server? Standard internal format returns the current system date and time.
SELECT getDate ()
2004-12-02 00: 31: 30.840
Getcdate does not have certain determinism
Returns the datetime value indicating the current UTC time (world time coordinate or GMT). The current UTC time is available from the current local time and time zone settings in the computer operating system running SQL Server.
2004-12-01 16: 33: 12.107
Sixth, conversion functions
When Microsoft? SQL Server 2000 does not automate expression conversion of different data types, the conversion function CAST and CONVERT can be converted. These conversion functions can also be used to get a variety of special data formats. These two conversion functions can be used to select a list, where clause, and anywhere allowing the use of expressions.
If you want the Transact-SQL program code to follow SQL-92 standards, use CAST instead of using Convert. If you want to use the style features in Convert, use CONVERT instead of using CAST.
When using CAST or Convert, you need two information:
The expression to be converted (eg, the sales report requires sales data to convert from the currency data into character data).
To convert the data type to which the expression is converted, for example, any other data type provided by VARCHAR or SQL Server.
The conversion is only valid within the action of the CAST or Convert function unless the value of the converted value is stored. In the example below, in the first SELECT statement, use CAST, and the second SELECT statement uses Convert, convert the title column to the CHAR (50) column to make the result more readable:
SELECT CAST (Title As Char (50)), YTD_SALES
From titles
WHERE TYPE = 'TRAD_COOK'
or
Select Convert (CHAR (50), Title, YTD_Sales
From titles
WHERE TYPE = 'TRAD_COOK'
Here is the result set: (for any one)
YTD_Sales
------------------------------------- -
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in
BUCKINGHAM
Palace
Kitchens 15096
Sushi, Anyone? 4095
(3 row (s) affected)
In the following example, the int type YTD_SAles column is converted to a CHAR (20) column, so that the LIKE predicate can be used for the column:
SELECT TITLE, YTD_SALES
From titles
WHERE CAST (YTD_SALES AS CHAR (20)) Like '15% '
And type = 'TRAD_COOK'
The following is the result set:
Title Ytd_sales
-------------------------------------
Fifty Years in
BUCKINGHAM
Palace
Kitchens 15096
(1 row (s) affected)
SQL Server automatically handles some data types. For example, if compared to CHAR and DateTime expressions, Smallint and int expression, or different lengths of CHAR expressions, SQL Server can automatically convert them. This conversion is called the implicit conversion. These conversions do not have to use the CAST function. However, it is acceptable to use CAST in the following situations:
The data types of two expressions are identical.
Two expressions can be reimmitted.
The data type must be explicitly converted.
If an impossible conversion is tried (for example, the SQL Server will display an error message.
If the length of the data type is not specified, the SQL Server is automatically provided with a length of 30.
When converting to DateTime or SmallDateTime, SQL Server will reject all values that cannot be identified as a date (including date before January 1, 1753). The DateTime value can be converted to SmallDateTime if the date is in the appropriate range (January 6, 2079, 2079). The time value is rounded to the closest number of minutes.
Convert to Bit will change any non-zero value to 1.
When converting to MONEY or SMALLMONEY, the monetary unit will be added after the integer. For example, the integer value 4 is converted to a currency (for the default language US_ENGLISH). The fractional part of the floating point value will be rounded to four decons for the Money value. In the expression of a char or VARCHAR data type to be converted to an integer data type, only numbers and optional plus numbers or minus ( or -) are included. The front guide felt will be ignored. To convert the expression of a Money's char or VARCHAR data type, you can also include an optional decimal point and a leading US dollar symbol ($). CHAR or VARCHAR type expressions to be converted to FLOAT or REAL data types can also selectively include an index symbol (e or E, which is optional or - symbol, and then the number).
When converting character expressions to other sizes, the value of the new data type is too long will be truncated, and SQL Server will display an asterisk (*) in the OSQL utility and SQL query analyzer. If the digital expression is too long for the new data type, the value will be truncated. Below is an example of a character truncation:
Select Substring (Title, 1, 25) AS Title, Convert (Char (2), YTD_SALES)
From titles
WHERE TYPE = 'TRAD_COOK'
The following is the result set:
Title
-------------------------
Onions, Leeks, And Garlic *
Fifty Years in buckingham *
Sushi, Anyone? *
(3 row (s) affected)
When the data type conversion is performed, if the decimal number of the target data type is smaller than the number of small digits of the source data type, the value will be truncated. For example, the result of CAST (10.3496 as Money) is $ 10.35.
Text data can be explicitly converted to char or varchar, explicitly convert Image to binary or varbinary. Since these data types are limited to 8,000 characters, the conversion is also limited to the maximum length of the Character and the Binary data type, namely 8,000 characters. NTEXT data can be explicitly converted to nchar or NVARCHAR, but the maximum length is 4,000 characters. If no length is specified, the default length of the converted value is 30 characters. Implicit conversion is not supported.
style parameter
When converting the DateTime data to Char or VARCHAR data, Convert's style parameters provide various date display formats. The value provided for the Style parameter determines how the DateTime data is displayed. The year can be displayed as two or four digits. By default, SQL Server will display the year as a two digits. To display four-digit year (YYY) including century (even if the year data is stored), please add 100 to the STYLE value to get the four-digit year.
The following example shows the conversion with the style parameter:
Select Convert (25), Getdate (), 111) '' Convert (Char (8), Getdate (), 108)
This statement converts the current date to a style 111, YYYY / MM / DD HH: mm: SS
2004/12/02
13:46:42
See
CAST and CONVERT
function
In the table, the two columns on the left indicate the Style value that converts DATETIME or SMALLDATETIME to character data. Add 100 to the Style value to get four years (YYY), including a century number. Do not take a few (YY) taking a century (YYY)
standard
input Output**
- 0 or 100 (*) Default MON DD YYYY HH: MIAM (or PM)
1 101 United States MM / DD / YYYY
2 102 ANSI YY.MM.DD
3 103 United Kingdom / French DD / MM / YY
4 104 German DD.MM.YY
5 105 Italy DD-MM-YY
6 106 - DD MON YY
7 107 - MON DD, YY
8 108 - HH: mm: ss
- 9 or 109 (*) Default milliseconds MON DD YYYY HH: MI: SS: MMMAM (or PM)
10 110 US MM-DD-YY
11 111 Japan YY / MM / DD
12 112 ISO YYMMDD
- 13 or 113 (*) European default value milliseconds DD MON YYYY HH: mm: SS: MMM (24h)
14 114 - HH: MI: SS: MMM (24h)
- 20 or 120 (*) ODBC Specification YYYY-MM-DD HH: mm: ss [.fff]
- 21 or 121 (*) ODBC specification (with milliseconds) YYYY-MM-DD HH: mm: ss [.fff]
- 126 (***) ISO8601 YYYY-MM-DD THH: mm: SS: MMM (excluding space)
- 130 * Kuwait DD MON YYYYY HH: MI: SS: mmmam
- 131 * Kuwait DD / MM / YY HH: MI: SS: MMMAM
* The default value (Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21 or 121) always returns to the century number (YYYY).
