SQL Server Learning (iii) T-SQL programming, logical control statements, and Safe mode

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