Introduction and basic usage of "SQL Server" SQL Server programming language T-SQL

I. T-SQL overview

The programming language that SQL Server uses to manipulate the database is Transaction-sql, or T-SQL. T-SQL differs from PL/SQL, and there is no fixed program structure.
T-SQL consists of the following 4 sections:

DDL: Define and manage databases and their objects, such as Create, alter, drop, and so on.
DML: Implements operations on objects in a database table, such as INSERT, update, and so on.
DCL: Data Control Language, the implementation of database security management and Rights management control, such as Grant, REVOKE, deny and so on.
The additional language element. Additional language elements of T-SQL, including variables, operators, functions, annotations, and process control statements.

In T-SQL, the writing of commands and statements is case insensitive.

Two. T-SQL Programming Fundamentals 1, identifiers

①t-sql Rule Identifiers

• Consists of letters, numbers, underscores, @, #, $ symbols, where the letters can be a-Z or a-Z, or they can be alphabetic characters from other languages.
• The first character cannot be numeric and $.
• Identifiers are not allowed to be T-SQL reserved words.
• Spaces and special characters are not allowed in identifiers
• Length less than 128

② Defining identifiers
For identifiers that do not conform to the rules of the identifier, enclose the identifiers using the delimited characters brackets ([]) or double quotation marks (""). Spaces and reserved word select are used, respectively, in identifiers [My Table], select.

2. Data type

A variety of system data types are available in SQL Server. In addition to the system data type, you can customize the data type.

① System data types

(1) Exact numeric data type

• Int
  Stores an integer value that stores a value range of -231~231-1.
• bigint
  BigInt can store a larger value than int, and store a value range of -263~263-1.
• smallint
  The range value of the data type is smaller than int, between -215~215-1. Be careful when defining this type of data, to make sure that the stored data does not exceed the range of values that smallint can store.
• tinyint
  The range value of the data type is smaller than smallint and stores integer data from 0 to 255.
• Decimal/numeric
  decimal[(P,s)] and numeric[(p,s)] Both data types are used to store data of the same precision and range (the same number of numeric digits stored on the left and right sides of the decimal point), and the range of values that can be stored is -1038+1~1038-1.
  P indicates the maximum number of decimal digits that can be stored to the left and right of the decimal point, and S specifies the number of decimals. [(P,s)] The range is 1≤p≤38,0≤s≤p. If S is omitted, the default is 0, if P and s are not attached, then numeric represents numeric (18) and can only represent integers

(2) Approximate numeric data types

• Float
  Stores the numeric range of numeric values that are not fixed by the decimal point. For -1.79e+308~1.79e+308.
• Real
  Very similar to float, the storage value range is -3.40e+38~3.40e+38.

(3) Currency data type

• Money
• SmallMoney

(4) Character data type

• Char
  A fixed length of up to 8,000 characters can be defined. If a column is defined as char (n), then n characters are stored. When the input is less than the defined number of characters, the remaining length is filled with the space on the right.
• NChar
  Similar to the char type, but can define up to 4,000 characters, using Unicode encoding.
• varchar
  As with char, it is used to store alphanumeric data with a maximum of 8,000 characters. The difference is that each row of varchar can have a different number of characters, and the maximum number of characters does not define the maximum length. For example, a column is defined as varchar (50), the column data can be up to 50 characters in length. However, if only 3 characters of string are stored in a column, only 3-character storage is used. If a column is defined without a specified size, or varchar (), its length defaults to 1.
  In particular, varchar (max) can define a string data type of more than 8,000 characters, up to 1,073,741,824 characters. "Note not varchar (n)"
• nvarchar
  The definition is similar to varchar, except that nvarchar uses Unicode format to store characters.

(3) Date and time data type

• Date
  It is used only to store dates, ranging from January 1, 01 to December 31, 9999. The format of the date data type is YYYY-MM-DD.
• Time
  Only the time based on the 24-hour system is stored in the format hh:mm:ss[.nnnnnnn]. Like the date data type, to provide accurate data types for the data to be stored in the column, you can store data with an accuracy of up to 100 nanoseconds.
• Datetime
  Used to store any date and time from January 1, 1753 to December 31, 9999. DateTime not only stores the date, but also stores the time next to the date. If the date is only saved as a column defined as datetime, the default time of 12:00:00 is added to the stored date.
• DateTime2
  Similar to datetime, DateTime2 is used to store dates and times. The difference is that the DATATIME2 data type has a higher precision in the number of seconds of the decimal part. In addition, the data type can store dates from January 1, 01 to December 31, 9999. The format is YYYY-MM-DD hh:mm:ss[.nnnnnnn].
• smalldatetime
  Very similar to datetime, except that the smalldatetime can be stored in a range of values from January 1, 1900 to June 6, 2079. The ending date of the range is not the end of the month.

(4) Binary data type

• Binary
  Stores a fixed-size binary ten data that can store up to 8000 bytes.
  This data type is primarily used to store data as a combination of tags or tags. For example, store tags about the customer. You need to know if the customer is active (value 1), whether there is a consumption record for the last one months (value 2), if the final one-month consumption exceeds 1000 yuan (a value of 4), or if it is offs on time (value 8). This adds 4 columns of data to the database. However, if a binary value is used, if the customer has a binary value of 1101, the customer has a value of 1+4+8, which indicates that the customer is active. The last one months of spending more than 1000 yuan and on time offs.
• varbinary
  Very similar to binary, but the physical column size for each row of varbinary varies with the stored value. varbinary (max) can store data longer than 8,000 characters and can store up to 2GB, which can be used to store data like images.

(5) Private data type

• Bit
  The data type stores a value of 0 or 1. Usually used to determine the true or False value.
• uniqueidentifier
  Used to store a 16-bit globally unique identifier (UUID).
• Xml

② data types in a program
(1) Cursor
Data can be stored in memory-resident state. cursors, similar to tables, have data rows and columns, but their similarities are limited to this. The difference is that the cursor is not indexed. A data set is created by using cursors to process one row of data at a time.

(2) Table
Table data types are somewhat similar to cursors and tables. This data type is used to store row and column data, but cannot be indexed on the data. At this point, the system can "process one data set at a time" data and want to work with a standard table.

(3) Sql_varint
You can change the data type based on the stored data, that is, to store some different types of data types. However, it is strongly deprecated to use this data type.

3. Expression

Expressions are often referred to as meaningful formulas that are connected by certain rules by means of constants, variables, functions, and so on.

1. Variables
T-SQL variables are divided into local variables and global variables.
(1) Local variables
Local variables are user-defined and generally appear in batches, stored procedures, and triggers, and are scoped only within the program.
Local variables must be declared before they are used. T-SQL also provides assignment statements for local variables.
The ①declare variable declares the statement in the syntax format:

declare @变量1 [as] datatype,@变量2 [as] datatype...

• Local variable names must begin with @
• As can be omitted
• Assign an initial value of NULL

There are three ways to assign a local variable:
① is assigned a value when the variable is defined:

declare @变量1 [as] datatype = value,@变量2 [as] datatype = value...

②select an assignment statement with the syntax format:

select @变量1 = 表达式1,@变量2 = 表达式2...

• Use the Select command to assign values to multiple variables at once
• An expression can be a normal value, or it can be a query result
• When an expression is a column name for a table, the form is similar to using a column alias in a normal query. You can use a subquery to return more than one value at a time from a table. If the result of the query is more than one row, only the corresponding column value of the last row is assigned to the variable, unlike PL/SQL, which does not allow multiple rows of query results to be assigned to the variable

③set an assignment statement with the syntax format:

set @变量 = 表达式

• The basic usage is the same as SELECT, except that a set assignment statement can assign a value to only one variable, and a SELECT statement may assign values to multiple variables

Example

declare @sumsal as numeric(10,2),@dno as tinyintselect @dno = deptno,@sumsal = sum(sal) from emp where deptno = 10group by deptnoprint cast(@dno as varchar)+‘：‘+cast(@sumsal as varchar)

(2) Global variables
Global variables are defined by the SQL Server system and are typically used to track information about server-wide and specific sessions, and cannot be explicitly defined and assigned by the user. You can access global variables to understand some of the system's current state information.
The global variable name begins with @@ 开头 Some common global variables are given below.

Global Variables	Description
@ @error	Previous SQL statement reported an error number
@ @nestlevel	/tr>
The number of rows processed by an SQL statement
@ @servername	local server name
@ @identity	last inserted identity value
@ @spid
@ @fetch_status
@ @cpu_busy	sql server time state since last startup

2. Functions
A function is a set of T-SQL statements that are used to complete a particular function and return the result of processing, which becomes the "return value" and the process becomes the "body of the function."
Functions are also divided into system built-in functions and users automatically with functions. SQL Server provides a number of system built-in functions that can be divided into the following categories: Mathematical functions, String functions, date functions, convert functions, aggregate functions.
(1) Mathematical functions
The usual mathematical functions provided in T-SQL are as follows:

• ABS (): Return absolute value
• Round (numeric expression, length, [, type]): rounded to the specified length or precision. A type of 0, which is a rounding, and a type other than 0, indicates truncation
• Power (M,n): Returns the n-th power of M
• Trunc (): truncates a number to a specified number of digits
• %: For remainder, SQL Server does not have mod (m,n), and m%n instead

(2) String function

• LTrim (STR,SUBSTR)/rtrim (STR,SUBSTR): str represents the string to be manipulated, substr represents the substring to be clipped, and if you crop a space, you can omit
• SUBSTRING (str,position,length): Seeking substring
• Replace (STR,SEARCH_STR,REP_STR): Replaces a substring in a string. SEARCH_STR represents the substring to search, REP_STR represents the target string to be replaced
• Left (Str,n): Returns the specified number of characters starting at the beginning of the string
• Len (): Find string length

(3) Date and time functions
First, list the important parameters in the date-time function.

Date Time Element	Abbreviations	meaning
Year	Yy,yyyy	Years
Month	m,mm	Month
Day	D,dd	Day
DayOfYear	Dy	Days of the Year
Week	Wk	Days of the Week
Weekday	Dw	Day of the Week
Hour	hh	When
Minute	Mi	Score of
Quarter	Qq	Moment
Second	Ss	Seconds
Millisecond	Ms	Milliseconds

The following date functions are available in T-sql:

• GETDATE (): Returns the current date and time
• Year (date): Returns the integer of the years section of the specified date
• Month (date): Returns the integer of the month section of the specified date
• Day (date): Returns the integer of the days section of the specified date
• DatePart (date element, date): Returns the integer of the date part specified by the date element
• Datename (date element, date): A date name that returns a date element in the form of a string that specifies the time
• DateDiff (date element, date 1, date 2): Returns the difference between two days and converts it to the form of the specified date element
• DateAdd (date element, value, date): Returns the new date of date plus value by date unit given by date element

When a DateTime constant is involved, SQL Server recommends using a string format that is not related to DateFormat and locale settings, usually such string constants in the following two forms:

• No delimiter is used between dates, formatted as yyyymmdd[Hh:mi:[:ss][,mmm]], such as ' 20070703 ', ' 20070703 17:53:00.997 '.
• ISO 8601 in the form of yyyy-mm-ddthh:mi:ss[.mmm], the "-" delimiter is used between the date parts, the date and time parts are separated by T, and the time part cannot be omitted, such as ' 2007-07-03t17:53:10 '.

(4) Data type conversion function
There are two types of conversions: implicit and explicit conversions.
An implicit conversion is when SQL Server automatically converts data from one data type to another, and the user is not visible.
Explicit conversions Use the CONVERT function, which enables an expression of one data type to be cast to an expression of another data type. Both data types must be able to be converted, for example, a char value can be converted to binary, but cannot be converted to an image. The primary function of this function is to convert numeric or date data into strings, while strings that contain only numbers are converted to numeric data in general implicit conversions.
Format: CONVERT (data type (length), expression [, n])
The 4th parameter n of the function is optional and is used for datetime data types and character data type conversions. The parameter values are shown in the following table.

digits

not with the century	with century digits	format
1	101	Mm/dd/yyyy
2	102	Yy.mm.dd
3	103	Dd/mm/yyyy
4	104	Dd.mm.yy
5	105	Dd-mm-yy
8	108	Hh:mi:ss
	20 or 120	Yyyy-mm-dd Hh:mi::ss (24h)

Example

select ename+‘‘‘‘+‘s sal is‘+convert(char(7),sal) as ‘EMPLOYEE‘S SAL‘ from emp;

as ‘客户ID‘,cname as ‘客户名称‘   convert(char(10),cRegisterationDate,102) as ‘注册日期‘from customer;最终显示的日期格式就会如2006.12.02

3. Operators
Arithmetic operators: + 、-、 *,/,% (redundancy)
String operator: + (connection)
Comparison operators: =, >, >=, <, <=, <> (Not Equal),!> (not greater than),!< (not less than)
Logical operators: not, and, or, all, any (or some, either), between ... And, EXISTS (present), in (within range), like (match)
Bitwise-Operator:& (bit-to-), | (bit or), ^ (bitwise XOR)
Unary operators: + (positive),-(negative), ~ (bitwise inverse)
Assignment operator: = (equals)

Original link: T-SQL introduction and basic syntax

Introduction and basic usage of "SQL Server" SQL Server programming language T-SQL