MSSQL review 1

(1), T-SQL statement
1. Create a database:
Exec sp_configure 'show advanced options', 1
Go
Exec reconfigure
Go
Exec sp_configure 'xp _ Your shell', 1
Go
Exec xp_cmdshell 'mkdir filename'
Go
Constraints:
Alter table table name
Add constraints: Add constraint name Constraints
Delete constraint: Drop constraint name
Constraints:
Alter table student
Add constraint pk_stuno primary key (studentno)
Alter table student
Add constraint uq_stuid unique (identitycard)
Alter table student
Add constraint df_stuaddress default ('no details') for address
Alter table student
Add constraint ck_stuborndate check (borndate> = '2017-01-01 ')
Alter table result
Add constraint fk_stuno foreign key (stuno) References student (stuno)
2. SQL programming
Define variable: declare @ variable_name datatype
Assign a value to the variable: Set @ variable_name = value or select @ variable_name = Value
Set does not support assigning values to multiple variables at the same time. An error occurs when the expression returns multiple values. If no value is returned, the variable is assigned a null value.
Select allows you to assign values to multiple variables at the same time. When multiple values are returned by an expression, the last value is assigned to the variable. If no value is returned, the original value is retained.
Output:
Print local variables or strings
Select Local variable as Custom column name
Type conversion:
Cast (variable name as data type)
Convert (data type, variable name)
Logical control statement:
Begin-end statement
Begin
Statement Block
End
If-else Condition Statement
If (condition) statement or statement Block 1
Else statement or statement Block 2
While loop statement
While (condition)
Begin
Statement or statement Block
[Break | Continue]
End
Case multi-branch statement
Case
When condition 1 then result 1
When condition 2 then result 2
[Other else results]
End
Batch Processing
Go commands: each batch is independent of each other, marking the end of batch processing.
3. Advanced Query
Subquery: used in combination with the comparison operator. You must ensure that no more than one value is returned for a subquery, which is suitable for filtering queries.
Table join: Suitable for viewing multi-table data
= ^ = Generally, table connections can be completed using subqueries, but not necessarily = ^ =
The in subquery matches Multiple Single Column values returned by the primary query.
Exists keyword can be used to check whether data exists
Subquery considerations:
Select (subquery) from table name the subquery result is a single row single column, but you do not need to specify the column name
Select * from (subquery) the alias of The as table must be specified for the table
4. Transactions, views, and indexes
Transaction: a single unit of work
Start transaction: Begin transaction
Commit transaction: commit transaction
Roll back and cancel the transaction: rollback transaction
View:
Create:
Create view view_name
As
<SELECT statement>
Delete
Drop view view_name
Index:
Create an index:
Create [unique] [clustered | nonclustered] index index_name
On table_name (column_name [, column_name]...)
[With fillfactor = x]
Delete Index
Drop index table_name.index_name: delete non-clustered indexes first, and then delete clustered Indexes
Query
Select * From table_name
With (Index = index_name)
Where condition
5. Stored Procedure
Create a stored procedure:
Create proc [edure] stored procedure name
[{@ Parameter 1 Data Type} [= default value] Output]
As
SQL statement
Delete stored procedure:
Drop proc [edure] stored procedure name
Note: When using output parameters, You need to redefine and obtain output variables.
Handle error messages:
Raiserror statement
Raiserror ({msg_id | msg_str }{, severity, State} [with option])
Msg_id: Specifies the User-Defined error message in the sysmessages system table.
Msg_str: a user-defined message. The maximum length is 255 characters.
Severity: severity level defined by the user
State: indicates the error status, which is a value from 1.
Option: Indicates whether to record errors to the server error log.