SQL Server 2008 from getting started to mastering--20180710

Directory

• 1. Programming with the Transact-SQL language
  • 1.1. Data Definition Language DDL
  • 1.2. Data manipulation language DML
  • 1.3. Data Control Language DCL
  • 1.4.transact-sql Language Basics
• 2. Operators
  • 2.1. Arithmetic operators
  • 2.2. Assignment operators
  • 2.3. Bitwise operators
  • 2.4. Comparison operators
  • 2.5. Logical operators
  • 2.6. Connection operators
  • 2.7. Unary operators
  • 2.8. Precedence of operators
• 3. Control statements
  • 3.1.BEGIN End Statement Block
  • 3.2.IF Else Statement block
  • 3.3.CASE Branch Statements
  • 3.4.WHILE statements
  • 3.5.WAITFOR Delay Statement
  • 3.6.RETURN Unconditional Exit statement
  • 3.7.GOTO Jump Statement
  • 3.8.TRY Catch error Handling statement
• 4. Common functions
  • 4.1. Data type Conversion functions

1. Programming with the Transact-SQL language

Although SQL Server 2008 provides a graphical interface, only one Transact-SQL language can interact directly with the database engine. The Transact-SQL language can be divided into 3 categories according to the execution features: Data definition language DDL, Data manipulation language DML, Data Control Language DCL.

1.1. Data Definition Language DDL

Is the most basic type of Transact-SQL language that is used to create databases and create, modify, and delete various objects in the database, providing objects for the operation of other languages. For example, databases, tables, triggers, stored procedures, views, functions, indexes, types, and users are all objects in the database. Common DDL statements include

CREATE TABLE--创建表DROP TABLE--删除表ALTER TABLE--修改表

1.2. Data manipulation language DML

is a statement that manipulates data in tables and views, such as querying data (SELECT), inserting data (insert), updating data (update), deleting data (Delete), and so on.

1.3. Data Control Language DCL

Languages that involve rights management are called data control languages and are primarily used to perform operations related to security management. Grant permissions (Grant), revoke permissions (REVOKE), deny principal permissions, and prevent principals from inheriting permissions (DENY) through group or role members

1.4.transact-sql Language Foundation 1.4.1. Constants and variables

Constants do not say much. In SQL Server 2008, there are two types of variables. One is a system-defined and maintained global variable, and one is a local variable that the user defines to hold intermediate results.

1.4.1.1. System Global Variables

System global variables fall into two main categories, one of which is the global variables associated with the SQL Server connection or the current processing, such as the @ @Rowcount represents the number of rows affected by the most recent statement. The @ @error represents the error state in which the most recent operation was saved. A class is a global variable related to the entire SQL Server system, such as @ @Version represents the version information of the current SQL Server.

SELECT @@VERSION AS 当前版本;--查看当前SQL Server的版本信息

Results

1.4.1.2. Local Variables

Local variables can have specific data types, have a certain scope, and are typically used to act as counters to calculate or control the number of loop executions, or to hold data values. There are only 1 @ characters before local variables, and local variables are declared with a Declare statement.

USE testDECLARE @StudentId varchar(20)SET @StudentId=(SELECT Student.stu_noFROM StudentWHERE stu_enter_score=‘603‘)SELECT @StudentId AS 入学分数为603的学生学号GO

Results

2. Operator 2.1. Arithmetic operators

In SQL Server 2008, arithmetic operations include plus (+) minus (-) multiplication (*) in addition to (/) modulo (%). To give a simple example.
Example 1: Add a column to the student table with the column named Stu_age, calculate the stu_age column and insert the data according to the Stu_birthday column of the student table. (Demonstrates how to insert an entire column of data)
Student table Data

Execute the following statement

ALTER TABLE StudentADD stu_age int;--在Student表中添加stu_age列CREATE TABLE #agetemp(stu_no varchar(8),age int);--新建一个临时表INSERT INTO #agetemp(stu_no,age)--在临时表中插入学号和计算出来的年龄SELECT Student.stu_no,YEAR(GETDATE())-YEAR(stu_birthday)--利用函数和运算符计算年龄FROM Student;UPDATE StudentSET Student.stu_age=#agetemp.age--将临时表中的age列数据整个复制到Student表的stu_age列FROM #agetempWHERE Student.stu_no=#agetemp.stu_no--条件是两个表的stu_no列值相等GOSELECT * FROM Student

Results

2.2. Assignment operators

An equal sign (=) that assigns the value of an expression to another variable. To give a simple example.
Example 2: Calculate the average student enrolment score for the student table and print.
Student table data, stu_enter_score column holds student's entrance record

Execute the following statement

DECLARE @average int--声明@average变量SET @average=(--将计算出的平均值赋值给@averageSELECT AVG(stu_enter_score)FROM Student)PRINT @average--打印@average的值

Results

2.3. Bitwise operators

Bitwise operators are included with operations (&), or Operations (|), and XOR (^), which can be bit-manipulated on two expressions, which can be integer or binary data. Transact-SQL first converts integer data to binary data and then bitwise operations. Give a simple example.
Example 3: declare 2 int variable @num1, @num2, assign to these two and do with or differ or operate.
Execute the following statement

DECLARE @num1 int,@num2 intSET @num1=5 SET @num2=6SELECT @num1&@num2 AS 与,@num1|@num2 AS 或,@num1^@num2 AS 异或

Results

Extension Example 4: Write a decimal conversion to a binary function

CREATE FUNCTION Bin_con_dec(@dec int)--定义十进制转换为二进制函数RETURNS varchar(20)ASBEGINDECLARE @quo int,@remainder varchar(20),@quo1 intSET @[email protected]SET @remainder=‘‘WHILE @quo<>0BEGINSET @[email protected]/2SET @remainder=CAST(@quo%2 AS varchar(20))[email protected]SET @[email protected]ENDRETURN @remainderEND

After executing the above function, run the following statement to verify the function correctness

PRINT dbo.Bin_con_dec(42)

The result is 101010, and the function is defined correctly.

2.4. Comparison operators

Also called relational operators, which are used to compare two values of a relationship, common have equals (=), greater than (>), less than (<), greater than or equal (>=), less than or equal (<=), not equal to (<> or!). =
Example 5: Search for student information from the student table with an average score above
Data for student tables

Execute the following statement

DECLARE @ave intSET @ave=(SELECT AVG(stu_enter_score) FROM Student)SELECT *FROM StudentWHERE stu_enter_score>[email protected];

The result is as shown

Note: You cannot write the code directly into the following form

SELECT * FROM StudentWHERE stu_enter_score>=AVG(stu_enter_score)

MSG 147, Level 15, State 1, line 2nd
Aggregations should not appear in the WHERE clause unless the aggregation is in a subquery contained in a HAVING clause or select list, and the column to be aggregated is an external reference.

Because AVG is an aggregate function.

2.5. Logical operators

The function of a logical operator is to test the condition. All,and,any,between,exists,in,like,not,all,some. Here's an example with some. The some function is true if some of them are true in a set of comparisons.
Example 6: Query the student table for students with a higher than average enrollment score, if present, output true, output false does not exist.
Student table's Stu_enter_score column (entrance record) data

Execute the following statement

USE testIF (SELECT AVG(stu_enter_score) FROM Student)<=SOME(SELECT stu_enter_score FROM Student)PRINT ‘true‘ELSEPRINT ‘false‘GO

Results

2.6. Connection operators

The plus sign (+) is a string concatenation operator that you can concatenate strings together, with a plus sign in the decimal-to-binary function of Example 4.
Example 7: stu_name columns and Stu_enter_score columns in the student table are displayed in the same column, with the column named score
Data for student tables

Execute the following statement

SELECT stu_name+CAST(stu_enter_score AS VARCHAR(3)) AS score FROM Student

Execution results

Note: The Stu_enter_score column data type is int and the plus sign is valid only for string type data, so you use the CAST function to convert the Stu_enter_score data type to varchar (3) so that string concatenation can be implemented.

2.7. Unary operators

A unary operator performs an operation on only one expression, which can be any one of the data types in a numeric data type. SQL Server 2008 provides a unary operator that contains a positive (+), a negative (-), and a bit inverse (~).
Example 8: Declare an int data type variable @num and assign a value to do positive and negative action against the variable.
Execute the following statement

DECLARE @num INTSET @num=45SELECT [email protected] AS 正,[email protected] AS 负,[email protected] AS 位反GO

Results

Note: The bitwise inverse operator is used to take a number of complements, which can only be used for integers.

2.8. Precedence of operators

Priority Level	operator
1	~ (bit counter)
2	* (multiply),/(except),% (modulo)
3	+ (positive),-(negative), + (plus), + (connection),-(minus),& (bits and)
4	=,>,<,>=,<=,<>,!=,!>,!< (comparison operator)
5	^ (Bit XOR), bit or (symbol not out, front, self turn)
6	not
7	and
8	all,any,between,in,like,all,som E
9	= (Assignment)

When operators in an expression have the same precedence, the unary operator is left-to-right and the two-tuple operator (the operator that acts on two expressions) is left-to-right as they are positioned in the expression.
Example 9: Verifying operator precedence
Execute the following statement

DECLARE @result INT,@num INTSET @num=45SET @[email protected]+([email protected])*[email protected]/([email protected])SELECT @result AS resultGO

Results

To calculate an expression in code
@[email protected]+ ([email protected])[email protected]/(email protected])
[Email protected]+ ( -46)[email protected]/(-46)
=45+ ( -46)4-45/(-46)
=45+ ( -46)4
=-139

3. Control Statement 3.1.BEGIN END statement block

BEGIN end can define SQL Server statement blocks, which are executed as a set of statements, allowing statements to be nested. For examples, see example 4

3.2.IF Else Statement block

Used to specify the execution condition of the T-SQL statement, and if the condition is true, the statement following the conditional expression is executed, and if the condition is false, you can try the Else keyword to specify the T-SQL statement to execute. See Example 4for an example.

3.3.CASE Branch Statements

Example: The student table of students, sex and the origin of printing out, the requirement of native place can only show the province, outside the province or the autonomous region.
Data for student tables

Execute the following statement

SELECT stu_name AS 姓名,stu_sex AS 性别,(CASE stu_native_placeWHEN ‘浙江‘ THEN ‘省内‘WHEN ‘内蒙古‘ THEN ‘自治区‘WHEN ‘西藏‘ THEN ‘自治区‘WHEN ‘宁夏‘ THEN ‘自治区‘WHEN ‘新疆‘ THEN ‘自治区‘WHEN ‘广西‘ THEN ‘自治区‘ELSE ‘省外‘END) AS 籍贯 FROM Student

Results

3.4.WHILE statements

Used to set conditions for repeating a T-SQL statement or block of statements.
Exampleone: Use "*" to output a diamond with a width of 9 on the screen.
Execute the following statement

DECLARE @width int,@j intSET @[email protected]为菱形的最大宽度SET @[email protected]表示每行打印的“*”符号的个数WHILE @j<[email protected]BEGINPRINT SPACE((@[email protected])/2)+REPLICATE(‘*‘,@j)--SPACE函数打印n个空字符，REPLICATE打印n个特定字符串SET @[email protected]+2ENDSET @[email protected]WHILE @j>0BEGINPRINT SPACE((@[email protected])/2)+REPLICATE(‘*‘,@j)SET @[email protected]END

Results

3.5.WAITFOR Delay Statement

The WAITFOR DELAY statement allows the statement after it to be executed at a specified time or interval, suspending the execution of a batch, stored procedure, or transaction.
Example: Query for student information at a point in time with student number 20180101

BEGINWAITFOR TIME ‘15:03‘--在15点03分查询SELECT * FROM StudentWHERE stu_no=‘20180101‘END

example: After 3 minutes, query the student information of student 20180102

BEGINWAITFOR DELAY ‘00:03‘--在3分钟后查询SELECT * FROM StudentWHERE stu_no=‘20180102‘END

3.6.RETURN Unconditional Exit statement

The statement indicates unconditional termination of the query, batch, or execution of the stored procedure. The statements after the stored procedure and the batch return statement are no longer executed. When you use the statement in a stored procedure, you can specify an integer value that is returned to the calling application, batch, or procedure. If return is not specified, the return value of the stored procedure is 0

3.7.GOTO Jump Statement

This statement jumps the execution of the T-SQL batch to the specified label. As the statement destroys the structure of the structured statement, use as little as possible
example: Goto as a branching mechanism
Execute the following statement

DECLARE @Counter int;  SET @Counter = 1;  WHILE @Counter < 10  BEGIN       SELECT @Counter      SET @Counter = @Counter + 1      IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.      IF @Counter = 5 GOTO Branch_Two  --This will never execute.  END  Branch_One:      SELECT ‘Jumping To Branch One.‘      GOTO Branch_Three; --This will prevent Branch_Two from executing.  Branch_Two:      SELECT ‘Jumping To Branch Two.‘  Branch_Three:  SELECT ‘Jumping To Branch Three.‘;

Results

When counter=4, executes the goto statement output Branch One, executes the statement and then breaks the while loop, then executes the Goto in the Branch_one statement, outputs branch three, and ends.

Note: Using Goto in a while loop breaks the loop.

example: Using the goto statement to print the diamond's function in example 11
Execute the following statement

DECLARE @width int,@j int,@i intSET @[email protected]为菱形的最大宽度SET @[email protected]表示每行打印的“*”符号的个数SET @[email protected]表示下一行打印第i行Set3:PRINT SPACE((@[email protected])/2)+REPLICATE(‘*‘,@j)--SPACE函数打印n个空字符，REPLICATE打印n个特定字符串SET @[email protected]+1IF @i<=(@width+1)/2GOTO Set1ELSEGOTO Set2Set1:SET @[email protected]+2GOTO Set3Set2:SET @[email protected]IF @j>=1GOTO Set3

Results

3.8.TRY Catch error Handling statement

If an error occurs inside a try block, control is passed to the statement group within the CATCH block. The TRY catch construct catches all errors that have a severity level greater than 10 but do not terminate the database connection.
Example: Example of a TRY catch
Execute the following statement

BEGIN TRYSELECT * FROM StudentSELECT 120/0 FROM StudentEND TRYBEGIN CATCHSELECT ERROR_MESSAGE() AS ‘错误信息‘END CATCH

Execution results

The 3 SELECT statements in the statement are all executed. If you put the error SELECT statement in front of the normal SELECT statement, can the normal SELECT statement be executed? Execute the following statement

BEGIN TRYSELECT 120/0 FROM StudentSELECT * FROM StudentEND TRYBEGIN CATCHSELECT ERROR_MESSAGE() AS ‘错误信息‘END CATCH

Results

The normal SELECT statement cannot be executed. The logic of the try Catch statement is that once the problem statement appears in the TRY statement block, it jumps immediately to the catch statement block, and the statement of the TRY statement block is no longer executed.

4. Common functions 4.1. Data type Conversion functions

By default, SQL Server automatically converts some data types, which are called implicit conversions. If you encounter an automatic conversion, you need to convert it with the cast () function and the Convert () function, which is called an explicit conversion. The cast () function and the convert () function are the same, and the cast function is easier to use, and the advantage of the CONVERT function is that you can specify a date and numeric format.
Example: Convert a number from a student table to a date format
The following two sentences are the same function, execute the following statement

SELECT stu_name,CAST(stu_no AS DATE) AS 学号转换成日期,stu_enter_score,stu_birthday FROM StudentSELECT stu_name,CONVERT(DATE,stu_no) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student

Results

example: Convert Stu_birthday to a date in the specified format with the CONVERT () function
Execute the following statement

SELECT stu_name,CONVERT(VARCHAR(20),stu_birthday,101) FROM Student--CONVERT函数将DATE类型的stu_birthday字段转化为字符串，并限定了样式，代码101

Results

Note: In the above code, CONVERT (date,stu_birthday,101) is useless to write this. 101 format codes are valid only for conversion of date formats to strings, and other formats that are converted to date formats are not valid.

Other commonly used functions are too simple to write on this side, slightly.

SQL Server 2008 from getting started to mastering--20180710