Shulou(Shulou.com)06/01 Report--

We've looked briefly at the use of various queries, but there are some more advanced data processing and queries that will be used in actual development, including indexes, views, stored procedures, and triggers. Therefore, the operation, diagnosis and optimization of database can be better realized.

What is index? Index is SQL Server's internal method of arranging data. It provides SQL Server with a way to arrange the routing of query data, so as to improve the retrieval speed and improve database performance by using index.

The index is also divided into the following six categories:

Unique index: two rows are not allowed to have the same index value. If a unique constraint is created, a unique index will be automatically created.

2. Primary key index: It is a special type of unique index. When a table is defined as a primary key, a primary key index will be automatically created. It requires that each value in the primary key is unique.

Clustered index: In a clustered index, the physical order of rows in a table is the same as the logical index order of key values. (Note: A table can only contain one clustered index)

4. Non-clustered index: It is built on the index page. When querying data, you can find the location of records in the index. Clustered index has faster data access speed than non-clustered index.

Composite index: Multiple columns can be combined into an index.

Full-text indexing: It is a special type of tag-based functional index, mainly used to search for strings in large amounts of text.

Create a unique index: (no duplicate values)

create unique nonclustered index U_cardID on TStudent (cardID)

To view the index on the table:

Select * from sys.sysindexes where id=(select object_id from sys.all_objects where name='Tstudent')

Query by specified index:

SELECT * FROM xueyuan WITH (INDEX = IX_name) WHERE STUDENT NAME LIKE 'SUN %'

A view is a virtual table, usually created as a subset of rows or columns from one or more tables.

The function of the view is:

1. Filter the data in the table

Prevent unauthorized users from accessing sensitive data

Abstracting multiple physical data tables into one logical data table

The benefits to users are: results are easier to understand, data is easier to access

The benefits for developers are easier to restrict data retrieval and easier to maintain applications

Notes:

Multiple tables can be used in each view

2. Similar to query, one view can nest another view, preferably no more than three layers.

The select statement you are trying to define cannot include the following:

ORDER BY clause, unless there is also a TOP clause in the select list in the select statement The INTO keyword refers to a temporary table or table variable

Create view: create view netstudentselect Sname,sex,Class from dbo.TStudent where Class='netclass'.

Find data from a view:

select * from netstudent where sex ='male '

Create views, change column headers:

create view V_Tstudent1asselect StudentID student number,Sname name,sex sex,cardID ×× number,Birthday,Class from dbo.TStudentselect * from V_Tstudent1

Stored procedures are precompiled collections of SQL statements and control statements stored in a database and executed by application calls.

So why store procedures? Because sending SQL code from the client to the server over the network and executing it is inappropriate, resulting in unsafe data leaks, impressing application performance, and high network traffic.

The advantages of using stored procedures are:

1. Modular programming

2. Fast execution speed and high efficiency

Reduce network traffic

4, with good security

Stored procedures fall into two categories: system stored procedures and user-defined stored procedures

System Storage Process:

is a set of precompiled T-SQL statements that provide a mechanism for managing database update tables and act as a shortcut for retrieving information from system tables

Start with "sp" and store it in the Resource database. Common system stored procedures are as follows:

Syntax for executing stored procedures using T-SQL statement calls:

EXEC [UTE] stored procedure name [parameter value]EXEC is short for EXECUTE

Usage of common system stored procedures:

exec sp_databases --List databases in the current system exec sp_renamedb 'mybank','bank' --Change database name (single user access)use MySchoolgo exec sp_tables --List of queryable objects in the current database exec sp_columns student --View column information in table student exec sp_help student --View all information for table student exec sp_helpconstraint student --view table student table constraints exec sp_helptext view_student_result --View the view's statement text exec sp_stored_procedures --Returns a list of stored procedures in the current database

System stored procedures can be divided into different classes according to their different roles. Extended stored procedures are one of the various system stored procedures provided by SQL Server.

Allows creation of external stored procedures in other programming languages, such as C#, providing an interface from SQL Server instances to external programs

Start with "xp" and exist separately as DLL

A commonly used extended stored procedure is xp_cmdshell, which can perform some operations under DOS commands. Take it as an example.

The syntax is:

**EXEC xp_cmdshell DOS command [NO_OUTPUT]**

xp_cmdshell is normally turned off as part of the server security configuration and should be enabled with the following statement:

exec sp_configure 'show advanced options', 1 --Display advanced configuration options (only spaces in single quotes) go reconfigure --reconfigure go exec sp_configure 'xp_cmdshell', 1 --Open xp_cmdshell option goreconfigure --reconfiguration

When enabled, execute the following statement:

exec xp_cmdshell 'mkdir c:\bank', no_output --Create folder c:\bankexec xp_cmdshell 'dir c:\bank\' --View files

User-defined stored procedures:

A complete stored procedure includes

Input and output parameters T-SQL statements executed in stored procedures Return values of stored procedures

Creating Stored Procedures with SSMS

A complete storage process consists of three parts:

1. Input and output parameters

T-SQL statements executed in stored procedures

3. Return value of stored procedure

The syntax for creating stored procedures using T-SQL statements is: CREATE PROC[EDURE] stored procedure name [ {@parameter1 data type} [= default] [OUTPUT], ……, {@ parameter n data type} [= default] [OUTPUT] ]AS SQL statement

The syntax for deleting stored procedures is:

DROP PROC[EDURE] Stored Procedure Name

For example, to query the average score of the most recent exam for this course:

use schoolDBgoif exists (select * from sysobjects where name='usp_getaverageresult')drop procedure usp_getaverageresultgocreate procedure usp_getaverageresultasdeclare @subjectid nvarchar(4)select @subjectid=subjectid from dbo.TSubject where subjectJectName ='network management 'declare @avg decimal (18, 2)select @avg=AVG(mark) from dbo.TScore where subJectID=@subjectidprint 'Network Administration Professional Average Score is:'+convert(varchar(5),@avg)go

After writing, execute: exec usp_getaveragesult

Trigger:

Is a stored procedure that automatically executes when adding, modifying, or deleting a table

Used to enforce business rules, you can define more complex constraints than check constraints

Executed by event triggering

Triggers fall into three categories:

INSERT trigger: Fires when data is inserted into a table

UPDATE trigger: triggered when updating a column or columns in a table

Delete trigger: Triggers when a record in a table is deleted

inserted and deleted tables

Managed by the system, stored in memory rather than in a database, so users are not allowed to modify it directly

Temporarily store modification information for data rows in a table

When triggers complete their work, they are also deleted

Trigger's function is: strengthen constraints, track changes, cascade operation

The syntax for creating a trigger is:

create trigger *triggername *on *tablename *[with encryption]for {[delete, insert, update]}as SQL statements

Example: Create a trigger to prohibit modification of data in admin table

create trigger remitteron adminfor updateasprint 'disable modification, contact DBA'rollback transactiongo

Then execute the statement to view the error message:

update Admin set LoginPwd='123' where LoginId='benet'select * from Admin

transaction (usually used in connection with a bank transaction, such as a transfer)

is an indivisible logical unit of work.

A set of commands, either all executed or none executed.

A transaction is a series of operations performed as a single logical unit of work. A logical unit must have four attributes: atomicity, consistency, isolation, and persistence. These attributes are often referred to simply as ACID.

For example, take a transfer.

First create a table named bank:

Check constraint for Currentmoney column:

Insert two pieces of data:

INSERT INTO bank(customerName,currentMoney) VALUES ('Zhang San', 1000)INSERT INTO bank(customerName,currentMoney) VALUES ('Li Si', 1)

Then enter the code transaction execution:

select customername,currentmoney as Balance before transfer transaction from bank --View balance before gobegin transaction --Start the transaction (specify that the transaction starts here and subsequent T-SQL statements are a whole) declare @errorsum int --Defines variables used to accumulate errors during transaction execution set @errorsum=0 --initialized to 0, i.e. no error update bank set currentmoney=currentmoney-1000 --transfer, Zhang San account less 1000 Li Si account more 1000 where customername='Zhang San'set @errorsum =@errorsum+@@ERROR --Accumulate whether there are errors update bank set currentmoney=currentmoney+1000where customername='Li Si'set @errorsum=@errorsum+@@ERROR --Cumulative error select customername,currentmoney as balance in transfer transaction process from bank --Check the balance during the transfer if@errorsum0--If there is an error begin print 'transaction fails, rollback transaction'rollback transaction elapse begin print 'transaction succeeds, commit transaction, write to hard disk, permanently save'commit transaction end goselect customername,currentmoney as balance after transfer transaction from bank --View balance after transfer

Transfer failed:

Transfer successful:

Lock:

Multiple users can manipulate the data in the same database at the same time, and data inconsistency will occur. Lock is able to ensure the integrity and consistency of data in a multi-user environment.

There are three types of lock:

Shared locks (S-locks): Used to read locks placed on resources.

Exclusive lock (X lock): Incompatible with other locks, including other exclusive locks.

Update lock (U lock): U lock can be seen as a combination of S lock and X lock, used to update data.

View lock:

Dynamic management views using sys.dm_tran_locks

Use Profiler to Capture Lock Information

deadlock

Deadlock is essentially a deadlock state caused by multiple agents competing for resources.

The conditions for deadlock formation are:

1. Mutual exclusion condition: the subject is exclusive to the resource

2. Request and waiting conditions

3. No deprivation conditions

4. Loop waiting conditions

Deadlock prevention:

breaking mutual exclusion condition

Destroy requests and wait conditions

violation of non-deprivation conditions

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.