SQL Serever Learning 13--Database programming language

Programming Basics Notes

Comments are named to describe some statements for later maintenance or for other users to understand, and comments will not be performed.

Single-line Comment

SELECT GETDATE ()--Query the current date

Multi-line comments

Variable

When the T-SQL executes the command, it can declare that the variable temporarily stores various data, declares that it is initialized to NULL, can only replace the numeric value, and cannot replace the object or keyword

Local variables

Must first be defined with the Declare statement, and the local variable is preceded by the name @

SELECT assignment Statement Yo, make sure the result is no more than one piece of data, otherwise the last data will be assigned to the variable

Global variables

The variables used within the SQL Server system, scoped to any program of the entire system, begin with @@ 开头 and the commonly used global variables are:

@ @ERROR, last T-SQL error code

@ @IDENTITY, the last inserted indicator value

@ @LANGUAGE, language currently in use

@ @ROWCOUNT, number of rows affected by the previous SQL statement

@ @SERVERNAME, local server name

@ @VERSION, SQL Server version information

PRINT @ @ERRORPRINT @ @IDENTITYPRINT @ @LANGUAGEPRINT @ @ROWCOUNTPRINT @ @SERVERNAMEPRINT @ @VERSION

Output to

Output statement

Print output using Chinese mode

SELECT output using tabular mode

Batch-processing statements

A batch is a set of T-SQL statements, and the statements in the collection are submitted to SQL Server for execution as a whole. Go is a batch flag.

SQL Server compiles batches into an executable unit (execution plan) to improve execution efficiency.

In general, some operations are placed in the same batch command, but if you are creating a statement for a database or other object, you must add go at the end to make it easier to execute separately from other commands.

There are 3 types of restrictions for batch processing:

• The CREATE statement cannot be combined with other statements
• If the batch command has a field name, it cannot be used in the same batch with a new name for the field
• If the EXECUTE statement is the first sentence of the batch, you can omit the Execute keyword, or you cannot omit the

IF ... Else statement

The average price of the statistic commodity, if it is more than 2000 yuan output "overall price is more expensive", and display the most expensive commodity information, if below 2000, show "Overall price is cheap", and display the cheapest commodity information.

DECLARE @avgjg float--Get the median price of select @avgjg =avg (input) from the Product list print ' The average value of all products is: ' +convert (VARCHAR (5), @avgjg)--To determine if (@avgjg  >=2000) BeginPrint ' overall price is more expensive, the most expensive product is: ' SELECT top 1 * from the ORDER by price of the list Descendelsebeginprint ' overall price is cheap, the cheapest product is: ' SELECT top 1 * From commodity table ORDER by price end

Note: If you need to display the number of tables and text data at the same time, you need to set: Tools-"Options-" Query results-"The default way to display results, choose to display the results in text format

While statement

Increase the sales price of all sales profit under 10% to 10 yuan, if there are still goods sales profit below 10%, in the increase of 10 yuan, until all commodity price profit is more than 10%, and finally show the increase in the largest number of goods added.

PRINT ' start to update the price of low-volume products ' DECLARE @cs intset @cs =0while ((SELECT COUNT (*) from commodity table WHERE Sales price/Price <1.1) >0) beginupdate Commodity table S ET Sales price = Sales price +10where Sales price/Price <1.1set @[email protected]+10if ((SELECT COUNT (*) from commodity table WHERE Sales price/input price <1.1) =0) Breakendprint ' increased the most items, added: ' +convert (VARCHAR (8), @cs) + ' meta ' GO

Multi-branch statement case

A brand of all goods classification, the price is higher than 3000 yuan for high-priced goods, 1000-3000 yuan for the medium-priced goods, 1000 below the low-priced goods

SELECT product name, model, input price =casewhen >3000 then ' high price commodity ' when input price between and then ' mid price commodity ' when price <1000 then ' low price commodity ' ENDFR OM Commodity Table

SQL Serever Learning 13--Database programming language