T-SQL Programming
T-SQL programming is similar to the C language, except that the syntax is slightly different, and the general idea remains unchanged. Many will not say, or start from the variable.
Variables are also divided into global variables and local variables, which are represented in slightly different ways.
Local variables:
Local variables must be prefixed with the @ tag, such as @age
The use of local variables is also declared first, and then assigned the value
Global variables:
Global variables must be prefixed with @ @, such as @ @version
Global variables are defined and maintained by the system, we can only read and cannot modify the values of global variables
Local variables
The Declaration local variable syntax is: DECLARE @ variable name data type
int declare @name nvarchar (10)
The syntax for the assignment is:
SET @ variable name = value or SELECT @ Variable name = value
== stuname from Stu where stuid= ' 101 '
The use of the Select method to assign a value should be noted: to ensure that the filtered data is only one
Take a look at an example:
1 /*-- find information about a pot of sake--*/2DECLARE @name varchar (8)--Student Name3Set @name = ' A pot of sake '--assigning values using Set4SELECT * from stuinfo where stuname =@name5 /*-- Find a pot of sake around the table--*/6DECLARE @seatint--Seat number7Select @seat =stuseat from Stuinfo--Assigning values using select8where stuname=@name9SELECT *From StuinfoTenWHERE (stuseat = @seat + 1) or (stuseat = @seat-1) OneGo
Global variables
Declare all variable syntax as: Declare @@ 变量 name data type
Common global variables are shown in the following table:
Variable |
Meaning |
@ @ERROR |
last T-SQL Wrong number of error |
@ @IDENTITY |
Last-inserted identity value |
@ @LANGUAGE |
The name of the currently used language |
@ @MAX_CONNECTIONS |
Maximum number of simultaneous connections that can be created |
@ @ROWCOUNT |
subject to the previous SQL number of rows affected by the statement |
@ @SERVERNAME |
Name of the local server |
@ @TRANSCOUNT |
Number of transactions opened by the current connection |
@ @VERSION |
SQL Server The version information |
Let's look at an example:
1print ' version of SQL Server ' +@ @VERSION2print ' Server name: ' +@ @SERVERNAME3 INSERT INTO Stuinfo (stuname,stuno,stusex,stuage)4Values (' Wu Song ', ' s25328 ', ' Male ', ' 23 ')5--if greater than 0 indicates that the previous statement was executed with an error6print ' current error number ' +convert (varchar (5), @ @ERROR)7print ' The student who just enrolled, the seat number is: '8+convert (varchar (5), @ @IDENTITY)9Update Stuinfo Set stuage=85TenWhere Stuname= ' a pot of sake ' Oneprint ' current error number ' +convert (varchar (5), @ @ERROR) AGo
Error refers to the wrong number, returned as 0 table correct, no error
Logical control Statements
In the C language, logical control statements are if-else,switch,for,while, and in SQL programming, the logical control statements here are: If-else,while,case-end statements
If-else statements
The syntax format is:
if (condition) begin Statement 1 statement 2 endelse begin Statement 1 statement 2 End
Note: Begin...end is equivalent to curly braces in the C language, with multiple statements using Begin...end, which is equivalent to a statement block between Begin...end
See an example to make it easier to understand, for example:
float Select @myavg=+ CONVERT (varchar (5), @myavg)if(@myavg >70) begin print ' top three ' score for ' 3 * from stumake ORDER by Writexam Dese endelse begin print ' after three results for ' 3 * from stumake ORDER by writexam ASC End
When you use T-SQL programming, you can switch views to see results, Tools > Options > Query Results > General > Show results by default > show results in text format, see better performance
While Loop statement
The syntax format is:
while (condition) begin Statement 1 statement 2 break End
Note: Break is the end loop, similar to the auxiliary control statement break,continue in the C language
See an example:
int where (1=1)- conditions always set begin Select @n=count (*) fromstumarks where Writtenexam <60 -- statistics on the number of failed if (@n>0) update stumarks-- 2 points per person set Writtenexam=writtenexam+2 elsebreak -- exit loop the results after the bonus score are as follows: ' * from Stumarks
Case-end Multi-Branch statements
The syntax format is:
Case When condition 1 then result 1 when condition 2 then result 2 else other result end
It is clear that you can see an example of a grade:
Print ' ABCDE level five shows the following results: 'select Stuno, score case when writtenexam<60 Then ' E ' And then ' D ' , and then ' C ' , and then ' B ' else
c19> ' A ' endfrom stumarks
Note: Before the go in the code, the batch flag is go, batch is to put some logic-related business operations statements in the same batch, while the operation
Look at one more example:
According to the following rules on the test scores are repeated, until the average score of more than 85 points. Please write a T-SQL statement implementation.
90 +: no extra points
80-89 points: Add 1 points
70-79 points: Add 2 points
60-69 points: Add 3 points
60 points below: plus 5 points
1SELECT * FROM Stumarks--Original Results2DECLARE @labAvgint--declaring variables, the user temporarily holds average points3 while(1=1)--conditions have been established4 begin5 Update Stumarks6Set labexam= Case7When labexam<60 then labexam+5--set up conditions for the addition of8When Labexam between and labexam+39When Labexam between and labexam+2TenWhen Labexam between and labexam+1 One ElseLabexam A End -Select @labAvg =avg (labexam) from Stumarks--get the current average score and judge whether it is necessary to add points - if@labAvg >=85 the Break--Break If it is already greater than or equal to 85, jump out of the loop - End -SELECT * FROM Stumarks-Score after addition
Safe Mode
In the SQL Server Learning (a) post, there is a reference to security mode (set permissions), but it is manual operation, now speak of a code implementation of the way
There are two ways to login authentication:
SQL authentication: For users who are not Windows platforms or for Internet users, you need to provide an account and password
Windows authentication: Suitable for Windows platform users, no password required, and Windows integrated authentication
There are two types of login accounts: SQL account and Windows account
Create Login
Add a Windows logon account
exec sp_grantlogin ' jbtraining\ww123456 ' – Domain name \ user Name
Add a SQL login account
exec sp_addlogin ' yihuqingjiu ', ' 123456 ' -username, password
exec represents a call to a stored procedure, a stored procedure that resembles a C-language function, which is discussed later in this article
Create a database user
The user who creates the database needs to call the system stored procedure sp_grantdbaccess, which uses:
EXEC sp_grantdbaccess ' login account name ', ' Database user name '
Where database user is an optional parameter, the default is the login account, which means that the database user defaults to the same name as the login account.
Add two users to a database
exec sp_grantdbaccess ' jbtraining\ww123456 ', ' ww123dbuser' yihuqingjiu ', ' Yidbuser '
Database users built into the system
dbo User and Guest user
DBO User: Represents the owner of the database (DB owner) and cannot delete the dbo user, which is always present in each database
Guest Guest User: For login account without database user access, each data can also be deleted, guest users need administrator authorization, otherwise unable to manipulate the database
The syntax for authorization
Grant permissions [on table name] to database user
Cases:
/* --Assign a SELECT, INSERT, UPDATE permission for Yidbuser to the table Stuinfo-- */ Grant SELECT, INSERT, update on stuinfo to yidbuser /*-- Assign Ww123dbuser permissions to a table--*/grant CREATE table to ww123dbuser
It's a lot easier to do than the previous method.
This article is for the author's opinion only, the author at the temperature of a pot of sake published. Reprint Please specify source: http://www.cnblogs.com/hong-fithing/
SQL Server Learning (iii) T-SQL programming, logical control statements, and Safe mode