SQL language in SQL Server 2000 (2)

Data Control Language DCL)

Data Control Language (DCL) is a statement used to set or change database user or role permissions. These statements include GRANT, DENY, REVOKE, and other statements. By default, only members of sysadmin, dbcreator, db_owner, db_securityadmin, and other roles have the right to execute the data control language.

GRANT statement
A grant statement is an authorization statement that grants statement or object permissions to other users and roles.
Syntax:
GRANT {ALL | statement [,... n]} TO security_account [,... n]

The syntax for granting object permissions is as follows:
GRANT {ALL [PRIVILEGES] | permission [,... n]} {[(column [,... n])] ON {table | view} | ON {table | view} [(column [,... n])] | ON {stored_procedure | extended_procedure} | ON {user_defined_function} TO security_account [,... n] [with grant option] [AS {group | role}]

2. DENY statement

The DENY statement is used to DENY permissions to users or roles in the current database and prevent users or roles from inheriting permissions through their groups or roles. Syntax:
DENY {ALL | statement [,... n]} TO security_account [,... n]

Syntax:
DENY {ALL [PRIVILEGES] | permission [,... n]} {[(column [,... n])] ON {table | view} | ON {table | view} [(column [,... n])] | ON {stored_procedure | extended_procedure} | ON {user_defined_function} TO security_account [,... n] [CASCADE]

3. REVOKE statement

A revoke statement is the opposite of a GRANT statement. It can delete permissions granted or denied by users or roles in the current database, however, this statement does not affect the permissions that the user or role inherits from other roles as members.
Syntax:
REVOKE {ALL | statement [,... n]} FROM security_account [,... n]

Syntax:
REVOKE [grant option for] {ALL [PRIVILEGES] | permission [,... n]} {[(column [,... n])] ON {table | view} | ON {table | view} [(column [,... n])] | ON {stored_procedure | extended_procedure} | ON {user_defined_function }}{ TO | FROM} security_account [,... n] [CASCADE] [AS {group | role}]

System stored procedures

The system stored procedure is a stored procedure created by the SQL Server system. It aims to conveniently query information from the system table, you can also complete management tasks or other system management tasks related to updating database tables. The system stored procedure can be executed in any database. The system stored procedure is created and stored in the system database master, and the name starts with sp _ or xp.

Some examples of system stored procedures are as follows:
Sp_addtype: defines a user-defined data type.
Sp_configure: used to manage server configuration option settings.
Xp_sendmail: used to send email or paging information.
Sp_stored_procedures: Used to return the list of stored procedures in the current database.
Sp_help: used to display the parameter list and its data type.
Sp_depends: displays objects based on stored procedures or objects based on stored procedures.
Sp_helptext: displays the definition text of a stored procedure.
Sp_rename: used to modify the name of the user object in the current database.

Other language elements

Note
Annotation is a text string that is not executed in the program code, also known as annotation ). In SQL Server, two types of annotation characters can be used: one is the ANSI standard annotator "--", which is used for single line annotation; the other is the same program annotation symbol as the C language, that is, "/**/".

Variable
A variable is an essential part of a language. There are two types of variables in the Transact-SQL language: local variables defined by the user and global variables provided by the system.

Local variable
A local variable is an object that can have a specific data type. It is only effective within the program. A local variable can be used as a counter to calculate the number of cycles or control the number of times a loop is executed. In addition, local variables can be used to save data values for test by control flow statements and data values returned by stored procedures. When a local variable is referenced, it must be marked with "@" before its name, and must be defined by the DECLARE command before it can be used.

Global Variables
Global variables are used in the SQL Server system. They are not limited to a specific program, but can be called by any program at any time. Global variables usually store configuration settings and statistical data for some SQL servers. You can use global variables in the program to test the system setting value or the status value after the execution of the Transact-SQL command.

Note the following when using global variables:
① Global variables are not defined by the user program. They are defined at the server level.
② You can only use predefined global variables.
③ When referencing a global variable, it must start with the tag.
④ The local variable name cannot be the same as the global variable name; otherwise, unpredictable results will appear in the application.

Operator
Operators are symbols used to perform arithmetic operations, String concatenation, assignment, and comparison between fields, constants, and variables. In SQL Server 2000, operators are classified into the following types: Arithmetic Operators, value assignment operators, bitwise operators, comparison operators, logical operators, and string Concatenation Operators.

Arithmetic Operators
Arithmetic Operators can perform mathematical operations on two expressions, which can be any data type of digital data type classification. Arithmetic Operators include addition +), subtraction-), multiplication *), Division/), and modulo % ).

Value assignment operator
In Transact-SQL, there is only one value assignment operator, that is, equal sign = ). The value assignment operator allows us to assign data values to specific objects. You can also use the value assignment operator to establish a relationship between the column title and the expression of the column-defined value.

Bitwise operators
Bitwise operator allows us to perform bitwise operations between integer data or binary data image data types. In addition, the operands on both sides of the bitwise operator cannot be both binary data.

Comparison Operators
The comparison operator is used to compare the values of the two expressions. The comparison result is a Boolean value, that is, TRUE indicates that the expression result is TRUE), FALSE indicates that the expression result is FALSE), and UNKNOWN. Except for expressions of the text, ntext, or image data type, comparison operators can be used for all expressions.

Logical operators
Logical operators can connect multiple logical expressions. Logical operators include AND, OR, and not. Like comparison operators, logical operators return boolean data types with TRUE or FALSE values.

String concatenation operator
The String concatenation operator can concatenate strings using the plus sign (+). This plus sign is called the String concatenation operator. For example, for the SELECT 'abc' + 'def 'statement, the result is abcdef.

The priority levels of operators are as follows:
Parentheses :);
Multiplication, division, and modulus operators: *,/, and %;
Addition and subtraction operators: + ,-;
Comparison operators: =,>, <, >=, <=, <> ,! =,!> ,! <;
Bit operators: ^, &, |;
Logical OPERATOR: NOT;
Logical OPERATOR: AND;
Logical OPERATOR: OR.

Function
In the Transact-SQL language, functions are used to execute some special operations to support standard SQL Server commands. The Transact-SQL programming language provides three functions:
(I) Row set function: a row set function can be referenced as a table in a Transact-SQL statement.
(Ii) Aggregate functions: Aggregate functions are used to calculate a group of values and return a single value.
(Iii) scalar function: a scalar function is used to process and compute one or more parameter values passed to it and return a single value.

The most common functions in SQL Server

String Functions
String functions can perform different operations on binary data, strings, and expressions. Most string functions can only be used for char and varchar data types and explicitly converted to char and varchar data types, A few string functions can also be used for binary and varbinary data types. In addition, some string functions can process data of the text, ntext, and image types.

Classification of string functions:
Basic string functions: UPPER, LOWER, SPACE, REPLICATE, STUFF, REVERSE, LTRIM, and RTRIM.
String search functions: CHARINDEX and PATINDEX.
Length and analysis functions: DATALENGTH, SUBSTRING, and RIGHT.
Conversion functions: ASCH, CHAR, STR, SOUNDEX, and DIFFERENCE.

Date and Time Functions
The date and time functions are used to perform various processing and operations on date and time data, and return a string, numeric value, or Date and Time Value. In SQL Server 2000, the types of date and time functions are shown in Table 2-5. In addition, table 2-6 lists the names, abbreviations, and acceptable values of date types.

Mathematical functions
Mathematical functions are used to perform mathematical operations on numeric expressions and return calculation results. Mathematical functions can process the numeric data decimal, integer, float, real, money, smallmoney, smallint, and tinyint provided by SQL Server.

Conversion functions
Generally, SQL Server automatically converts data types. For example, if you compare char and datetime expressions, smallint and int expressions, or char expressions of different lengths, SQL Server can automatically convert them. This conversion is called implicit conversion. However, if the results cannot be automatically converted by SQL Server or the results of the automatic conversion by SQL Server do not meet the expected results, you need to use the Conversion Function for display conversion. There are two conversion functions: CONVERT and CAST.

System Functions
System functions are used to return information about SQL Server systems, users, databases, and database objects. System functions allow users to use conditional statements to perform different operations based on the returned information after obtaining information. Like other functions, you can use system functions in the SELECT and WHERE clauses and expressions of SELECT statements.

Aggregate functions
An aggregate function can return the aggregate data of the entire or several columns or one column. It is often used to calculate the statistical value queried by the SELECT statement. Aggregate functions are often used together with the group by clause of SELECT statements.

Process control statement

Flow Control statements are commands used to control program execution and process branches. in SQL Server 2000, flow control statements are mainly used to control the execution processes of SQL statements, statement blocks, or stored procedures.

IF... ELSE statement
IF... The ELSE statement is a condition judgment statement. The ELSE clause is optional. The simplest IF statement does not contain the ELSE clause. IF... The ELSE statement is used to determine that a program is executed when a condition is set, and another program is executed when the condition is not met. SQL Server allows nested use of IF... ELSE statement, and there is no limit on the number of nested layers.

IF... Syntax form of ELSE statements
IF Boolean_expression {SQL _statement | statement_block} [ELSE {SQL _statement | statement_block}]

BEGIN... END statement
BEGIN... END statements can combine multiple Transact-SQL statements into one block and treat them as one unit. In condition statements and loop control flow statements, when two or more statements meet the specific conditions, you need to use BEGIN... END statement in the Syntax:
BEGIN {SQL _statement | statement_block} END

CASE Function
The CASE function can calculate multiple conditional expressions and return one of the result expressions that meet the condition. The CASE function can be divided into simple CASE functions and search for CASE functions according to different forms of use.

Syntax form of the CASE Function
CASE input_expressionWHEN when_expression THEN result_expression [... n] [ELSE else_result_expressionEND

Syntax form of CASE function search
Case when Boolean_expression THEN result_expression [... n] [ELSE else_result_expression END

WHILE... CONTINUE... BREAK statement
WHILE... CONTINUE... The BREAK statement is used to set conditions for repeated execution of SQL statements or statement blocks. If the specified condition is true, the statement is executed repeatedly. The CONTINUE statement allows the program to skip the statement after the CONTINUE statement and return to the first line of the WHILE loop. The BREAK statement completely jumps out of the loop and ends the WHILE statement execution.

Syntax:
WHILE Boolean_expression {SQL _statement | statement_block} [BREAK] {SQL _statement | statement_block} [CONTINUE]

GOTO statement
The GOTO statement allows the program to directly jump to the specified location marked with an identifier for further execution. The program located between the GOTO statement and the identifier will not be executed. GOTO statements and identifiers can be used in statement blocks, batch processing, and stored procedures. identifiers can be combinations of numbers and characters, but must end.

Syntax form of the GOTO statement
GOTO label
......
Label:
Program list 2-50: Use the GOTO statement to find the sum from 1 to 5.
Declare @ sum int, @ count int
Select @ sum = 0, @ count = 1
Label_1:
Select @ sum = @ sum + @ count
Select @ count = @ count + 1
If @ count <= 5
Goto label_1
Select @ count @ sum

WAITFOR statement

WAITFOR statements are used to temporarily stop execution of SQL Statements, Statement blocks, or stored procedures until the set time has elapsed or the set time has elapsed. The syntax of the WAITFOR statement is as follows:
WAITFOR {DELAY 'time' | time 'time '}
Here, DELAY is used to specify the TIME interval. TIME is used to specify a TIME point. The data type is datetime and the format is 'hh: mm: ss '.

RETURN Statement

The RETURN statement is used to terminate a query, stored procedure, or batch process unconditionally. At this time, the program after the RETURN statement will not be executed. The syntax of the RETURN statement is as follows:
RETURN [integer_expression]
The integer_expression parameter is the returned integer value. Stored Procedures can return an integer value to the calling process or application.

Edit note: this series is not complete and is to be continued)

(