1. What is a transaction?
A transaction is a mechanism, an operation sequence, which 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, either all of this group of databases will be executed, or none of them will be 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 of work must have 4 attributes:
Atomicity: A transaction is a complete operation, and the elements of the transaction cannot be further divided. All elements must be committed or rolled back as a whole.
Consistency: When a transaction is completed, the data must be in a consistent state.
Isolation: All concurrent transactions are isolated from each other when modifying data.
Durability: After the transaction is completed, the impact on the system is permanent.
2. Create a transaction
Begin 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 that the 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('李思',1)
select * from bank
-------------------------------------------------- --------
--------------- * * * * 事* * 事* * * * -------------
-------------------------------------------------- --------
use studb
go
set nocount on --Do not display affected row number information
print 'Data before 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='李思'
set @errorSum = @errorSum + 1
print 'Data in transaction:'
select * from bank
if @errorSum <> 0
begin
print 'Transaction failed, rollback transaction'
rollback transaction
end
else
begin
print 'Transaction successful, commit transaction, write to hard disk, save permanently'
commit transaction
end
go
print 'Data after transaction:'
select * from bank
-------------------------------------------------- --------
--------------- * * * * Citation* * Citation* * * * -------------
-------------------------------------------------- --------
--Index: It is the internal method of SQL Server to organize data
--Indexes can be divided into the following three types;
--Unique index: two rows are not allowed to have the same index value
--Primary key index: A special type of primary key index that automatically creates a unique index when defining a primary key for a table. Each value in the primary key is required to be unique.
--Clustered index: The physical order of the rows in the table is the same as the logical (index) order of the key values. The table can only contain one clustered index (can be understood as the pinyin of the dictionary).
--Nonclustered index: The data and index contain points to the corresponding locations where the data is stored. The physical order of the rows in the table does not match the logical order of the key values. (can be understood as MAP)
--Clustered indexes are faster than non-clustered indexes
--There can only be one clustered index in a table, but there can be multiple non-clustered indexes. If you set a column as the primary key, the column will be a clustered index by default.
--The table can have no index, and the primary key index is not necessarily a clustered index.
--Where is the index used?
--This column is frequently searched and used to sort data
--The bad type only has a few different values and there are only a few rows of data in the table, so there is no need to use an index.
--grammar
--create [unique][clustered|nonclustered] index index_name on table_name (column_name[,column_name]...)
--[
-- with fillfactor = x --The fill factor x is a value between 0 and 100
--]
code
use studb
go
if exists (select [name] from sysindexes where [name]='IX_stuMarks_writtenExam')
drop index stuMarks.IX_stuMarks_writtenExam --query whether the index already exists and delete it 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)
--An error will be reported: There is a syntax error near 'index'. If it is to be part of a table hint, it must have the WITH keyword and parentheses, such as:
select * from stumarks with(index=IX_stuMarks_writtenExam)
select * from stumarks with(index=IX_stuMarks_writtenExam) where writtenExam between 60 and 90
-------------------------------------------------- --------
--------------- * * * * View* * Picture* * * * ----------------
-------------------------------------------------- --------
View: It is a virtual table, a query method based on the data of one table or multiple tables.
General functions: filter rows in tables, prevent unauthorized users from accessing sensitive data, abstract multiple physical data tables into one logical data table
--grammar:
--create view view_name
--as
--<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 number=stuinfo.stuno, written test score=writtenexam, computer test score=labexam,
average score = (writtenexam+labexam)/2 from stuinfo left join stumarks on stuinfo.stuno = stumarks.stuno
go