SQL 5 for interview preparation-transactions, indexes, and views

1. What is a transaction?

A transaction is a mechanism and a sequence of operations. It contains a set of database operation commands, and all commands are submitted to the system as a whole.

Or cancel the operation request, that is, this group of databases are either executed or not executed. It is especially suitable for database systems operated by multiple users at the same time.

 

A transaction is a series of operations performed as a single logical unit of work.

A logical unit must have four attributes:

Atomicity: A transaction is a complete operation, and each element of the transaction cannot be further divided. All elements must be submitted or rolled back as a whole.

Consistency: When the transaction is completed, the data must be consistent.

Isolation: When data is modified, all concurrent transactions are isolated from each other.

Durability: After the transaction is completed, the impact on the system is permanent.

 

2. Create a transaction

Start transaction: Begin transaction

Commit transaction: commit transaction

Rollback (UNDO) Transaction: rollback transaction

Code

Use Studb
Go

If   Exists ( Select   *   From Sysobjects Where Name =   ' Bank ' )
Drop   Table Bank
Create   Table Bank
(
Customername Char ( 10 ), -- CUSTOMER NAME
Currentmoney Money   -- Balance
)
Go
-- Add check constraint account balance cannot be less than 1
Alter   Table Bank
Add   Constraint Ck_currentmoney Check (Currentmoney > =   1 )
Go
Insert   Into Bank Values ( ' Zhang San ' , 1000 )
Insert   Into Bank Values ( ' Li Si ' , 1 )

Select * FromBank

-- --------------------------------------------------------
-- ------------- *** Things ****----------------
-- --------------------------------------------------------
Use Studb
Go
Set Nocount On   -- The affected number of rows is not displayed.
Print   ' Data before the transaction: '
Select   *   From Bank
Go

Begin Transaction 
Declare @ Errorsum Int
Set @ Errorsum=0

Update Bank Set Currentmoney = Currentmoney - 1000   Where Customername = ' Zhang San '
Update Bank Set Currentmoney = Currentmoney + 1000   Where Customername = ' Li Si '

Set   @ Errorsum   =   @ Errorsum   +   1
 
Print   ' Data in the transaction: '
Select   *   From Bank

If   @ Errorsum   <>   0
Begin
Print   ' Transaction Failed, rollback transaction '
Rollback   Transaction
End
Else
Begin
Print   ' Transaction successful, transaction committed, hard disk written, permanent storage '
Commit   Transaction  
End
Go
Print   ' Transaction data: '
Select   *   From Bank

 

----------------------------------------------------------
--------------- ***** ****----------------
----------------------------------------------------------
-- Index: it is an internal method for SQL Server to orchestrate data.
-- Indexes can be divided into the following three types;
-- Unique index: two rows cannot have the same index value
-- Primary key index: a special type of primary key index that automatically creates a unique index when a table defines a primary key. Each value in the primary key must be unique.
-- Clustered index: the physical order of each row in the table is the same as the logic (INDEX) of the key value. The table can only contain one clustered index (which can be understood as a dictionary pinyin ).
-- Non-clustered index: the data and index contain physical order of each row in the table pointing to the corresponding location of the data storage and the logical order of the key value do not match. (MAP)
-- Clustered index is faster than non-clustered Index
-- A table can have only one clustered index, but multiple non-clustered indexes. If a column is set as the primary key, the column is the clustered index by default.
-- The table can have no index, and the primary key index is not necessarily a clustered index.

--Where can I use indexes?
-- This column is frequently searched and used to sort data
-- There are only a few different values for the bad type, and there is no need to use indexes if there are just a few rows of data in the table.

 

-- Syntax
-- Create [unique] [clustered | nonclustered] index index_name on table_name (column_name [, column_name]...)
--[
-- With fillfactor = x -- fill factor X is 0 ~ Value between 100
--]

 

Code

Use Studb
Go

If   Exists ( Select   [ Name ]   From Sysindexes Where   [ Name ] = ' Ix_stumarks_writtenexam ' )
Drop   Index Stumarks. ix_stumarks_writtenexam -- Check whether the index already exists. Delete the index if it exists.
Create   Nonclustered   Index Ix_stumarks_writtenexam On Stumarks (writtenexam)
With   Fillfactor = 30   -- Fill Factor reserved space
Go
-- Query
Select   *   From Stumarks ( Index = Ix_stumarks_writtenexam)
-- The following error occurs: A syntax error is returned near 'index. If it is to be part of the table prompt, it must have the With keyword and parentheses, such:
Select   *   From Stumarks With ( Index = Ix_stumarks_writtenexam)
Select   *   From Stumarks With ( Index = Ix_stumarks_writtenexam) Where Writtenexam Between   60   And   90

 

----------------------------------------------------------
--------------- ***** View the ** figure ****----------------
----------------------------------------------------------

View: a virtual table that is used to query data from one or more tables.

General purpose: Filter rows in a table, prevent unauthorized users from accessing sensitive data, and abstract multiple physical data tables into one logical data table.

-- Syntax:
-- Create view view_name
--
-- <SELECT statement>

Code

Use Studb
Go

If   Exists ( Select   *   From Sysobjects Where Name = ' View_stuinfo_stumarks ' )
Drop   View View_stuinfo_stumarks
Go
Create   View View_stuinfo_stumarks
As
Select Name = Stuname, student ID = Stuinfo. stuno, test score = Writtenexam, machine test score = Labexam,
Average score = (Writtenexam + Labexam) / 2   From Stuinfo Left   Join Stumarks On Stuinfo. stuno = Stumarks. stuno
Go