Transact-SQL programming, transactsql

Transact-SQL programming, transactsql
Transact-SQL programming
Transact-SQL
(1) Data Definition Language (DDL)
Create tabl/* create Table */
Alter table/* Modify table */
Drop table/* Delete table */
(2) data manipulation language (DML)
Select/* retrieve data from or from a table */
Delete/* delete data from the table */
Insert/* add data to a table */
Update/* modify existing data in the table */
(3) Data Control Statement (DCL)
Grant/* grant permissions */
Remove/* revoke permissions */
Deny/* reject permissions */


(1) variables and constants
A constant is a fixed data value that never changes during the running of the program. It is defined in Transact-SQL.
The constant format depends on the Data Type of the value it represents.
String constant
Binary constant
Decimal constant
Hexadecimal constant
Date constant
Real Constants
Currency constant
Variable:
(1) global variables (global variables provided by SQL server are divided into two types: global variables related to the current SQL Server connection and global variables related to the entire SQL Server System)
Select @ VERSION as [current SQL server VERSION]
Select @ SERVERNAME as [name of the current server]
Select @ ROWCOUNT as [number of rows processed by the latest statement]
Select @ CONNECTIONS as [returns the number of accepted CONNECTIONS or attempted CONNECTIONS]
@ CURSORROWS specifies the number of rows in the cursor after the returned cursor is opened
@ ERROR specifies the number of errors returned by the last SQL statement executed.
(2) local variables
// Create a local variable named @ sno. The data type is char (10)
Declare @ sno char (10)
You can use the set or select statement to assign values to local variables after defining local variables.
Set @ sno = '20140901 ';
Select @ sno
Use Student Achievement Management System
Go
Declare @ gender nchar (10)
Set @ gender = 'female'
Select * from student information
Where gender = @ gender


(2) Expression
Simple expression
Use Student Achievement Management System
Select *
From instructor Information
Where age> 30
Complex expressions
Use Student Achievement Management System
Select Course number, score as average score, student ID + name + gender + class as student Basic Information
From score information, student information
Where score information. Student ID = student information. Student ID
Order by average score desc


Note
-- Open the database of the Student Achievement Management System
Use Student Achievement Management System
/*
View student ID. name, gender, class, home address
And the gender is the student information of the female.


*/
Select student ID, name, gender, class, home address
From student information
Where gender = "female"


(3) Operators
Arithmetic Operators
Comparison Operators
Value assignment operator
Bitwise operators
Logical operators
String concatenation operator
Unary operator
Operator priority
(4) control statement
Begin end statement Block
If Condition Statement
Case branch statement
While loop statement
Try error handling statement
(5) Functions
Aggregate functions
Mathematical functions
Configuration Functions
String Functions
Data Type Conversion Function
Date and Time Functions'
Text and image Functions
User-Defined Functions
(6) transactions and locks
Transactions, locks