(ebook pdf) Teach Yourself SQL in 21 Days

Podstrony

[ Pobierz całość w formacie PDF ]
.If the WHERE clause of the DELETE command returnedfive rows, five rows would be deleted! Also note that the rowcount variable can bereset repeatedly.Therefore, from within the loop, you can query the database for someadditional information by simply resetting rowcount to 1 before continuing with theloop.Transact-SQL Wildcard OperatorsThe concept of using wildcard conditions in SQL was introduced on Day 3, "Expressions,Conditions, and Operators." The LIKE operator enables you to use wildcard conditionsin your SQL statements.Transact-SQL extends the flexibility of wildcard conditions.Asummary of Transact-SQL's wildcard operators follows.The underscore character (_)represents any one individual character.Forexample, _MITH tells the query to look for a five-character string ending withMITH. The percent sign (%) represents any one or multiple characters.For example,WILL% returns the value WILLIAMS if it exists.WILL% returns the value WILL.Brackets ([ ]) allow a query to search for characters that are contained withinthe brackets.For example, [ABC] tells the query to search for strings containingthe letters A, B, or C.The ^ character used within the brackets tells a query to look for anycharacters that are not listed within the brackets.For example, [^ABC] tells thequery to search for strings that do not contain the letters A, B, or C.Creating Summarized Reports Using COMPUTETransact-SQL also has a mechanism for creating summarized database reports.Thecommand, COMPUTE, has very similar syntax to its counterpart in SQL*Plus.(See Day 20,"SQL*Plus.")The following query produces a report showing all batters, the number of home runs hitby each batter, and the total number of home runs hit by all batters:INPUT:select name, homerunsfrom batterscompute sum(homeruns)ANALYSIS:In the previous example, COMPUTE alone performs computations on the report as a whole,whereas COMPUTE BY performs computations on specified groups and the entire report,as the following example shows:SYNTAX:COMPUTE FUNCTION(expression) [BY expression]where the FUNCTION might include SUM, MAX, MIN, etc.andEXPRESSION is usually a column name or alias.Date ConversionsSybase and Microsoft's SQL Server can insert dates into a table in various formats; theycan also extract dates in several different types of formats.This section shows you howto use SQL Server's CONVERT command to manipulate the way a date is displayed.SYNTAX:CONVERT (datatype [(length)], expression, format)The following date formats are available with SQL Server when using the CONVERTfunction:Format code Format picture100 mon dd yyyy hh:miAM/PM101 mm/dd/yy102 yy.mm.dd103 dd/mm/yy104 dd.mm.yy105 dd-mm-yy106 dd mon yy107 mon dd, yy108 hh:mi:ss109 mon dd, yyyy hh:mi:ss:mmmAM/PM110 mm-dd-yy111 yy/mm/dd112 yymmddINPUT:select "PayDate" = convert(char(15), paydate, 107)from payment_tablewhere customer_id = 012845OUTPUT:PayDate---------------May 1, 1997ANALYSIS:The preceding example uses the format code 107 with the CONVERT function.Accordingto the date format table, code 107 will display the date in the format mon dd, yy.SQL Server Diagnostic Tools--SET CommandsTransact-SQL provides a list of SET commands that enable you to turn on variousoptions that help you analyze Transact-SQL statements.Here are some of the popularSET commands:SET STATISTICS IO ON tells the server to return the number of logical andphysical page requests.SET STATISTICS TIME ON tells the server to display the execution time of anSQL statement.SET SHOWPLAN ON tells the server to show the execution plan for thedesignated query.SET NOEXEC ON tells the server to parse the designated query, but not toexecute it.SET PARSONLY ON tells the server to check for syntax for the designated query,but not to execute it.Transact-SQL also has the following commands that help to control what is displayedas part of the output from your queries:SET ROWCOUNT n tells the server to display only the first n records retrievedfrom a query.SET NOCOUNT ON tells the server not to report the number of rows returned bya query [ Pobierz całość w formacie PDF ]

zanotowane.pl

doc.pisz.pl

pdf.pisz.pl

bezglutenowo.pev.pl