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

It is the most basic to understand and master the sentence operation of adding, deleting, changing and querying SQL. In the actual production environment, we will also use some more advanced data processing and queries, including indexes, views, stored procedures and triggers. This blog mainly focuses on how to better realize the operation, diagnosis and optimization of the database.

Blog outline:

I. Index

II. View

III. Stored procedures

Flip-flop

V. Affairs

I. Index

The index provides pointers to the data values stored in the specified column in the table, then arranges the pointers in the specified order, and then follows the pointer to the column that contains the value.

1. What is an index?

The index in the database is similar to the catalog in the book. In a book, you do not need to read the whole book, you can use the catalogue to quickly find the information you need. In a database, an index enables a database program to find the data it needs without scanning the entire table. The catalogue in the book is a list of words with the page number containing each word. An index in a database is a collection of one or more column values in a table, as well as a list of logical pointers that physically represent these worthy data industries.

Indexing is an internal method for SQL Server to orchestrate data, which provides a way for SQL Server to route query data.

An index page is a data page that stores an index in a database. The index page stores the keyword page that retrieves the data row and the address pointer to the data row. By using the index, the retrieval speed of the database can be greatly improved and the performance of the database can be improved.

two。 Index classification

In SQL Server, the commonly used indexes are:

(1) unique index

A unique index does not allow two rows to have the same index value.

If there are duplicate key values in existing data, most databases generally do not allow unique indexes to be created. When the new data duplicates the key values in the table, the database also refuses to receive the data.

When a unique constraint is created, a unique index is automatically created. Although unique indexes help you find information, it is recommended that you use primary key constraints for best performance.

(2) Primary key index

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index.

The primary index requires that each value in the primary key be unique. It also allows quick access to data when a query uses a primary key index.

(3) clustered index

In a clustered index, the physical order of the rows in the table is the same as the logical (index) order of the key values.

Only one clustered index can be contained in a table.

(4) nonclustered index

The nonclustered index is built on the index page, and the location of the record can be found in the index when querying the data.

A nonclustered index makes the physical order of the rows of data in the table mismatch with the logical order of the key values. Clustered indexes have faster data access than nonclustered indexes.

In SQL Server, only one clustered index can be created for a table, but there can be multiple nonclustered indexes. Set a column primary key, and the column defaults to the clustered index.

(5) compound index

When you create an index, you can not only create an index on one of the columns, but like a primary key, you can combine multiple columns as an index, which is called a composite index.

Note: the composite index is used only when the first column or the entire composite index column is used as a condition to complete the data query.

(6) full-text index

Full-text index is a special type of tag-based functional index, which is created and maintained by the full-text engine service in SQL Server.

Full-text indexing is mainly used to search for strings in a large amount of text, and the efficiency of using full-text indexing will be much higher than that of using T-SQL 's LIKE keyword.

3. Create and use indexes

There are two ways to create an index: using SSMS and T-SQL statements.

Create an index using SSMS, as follows:

Index creation using SSMS is complete!

Select * from chengjiWITH (INDEX=IX_chengji) where name LIKE 'Sun%' / / query data using the created index

Although you can specify which index SQL Server will query for data, you generally do not need to specify it manually. SQL Server will automatically optimize the query based on the index created.

Using indexes can speed up data retrieval, but it is not necessary to index each column. Because the index itself needs to be maintained and takes up some resources, the indexed columns can be selected according to the following criteria.

Columns that are searched frequently; columns that are often used to query selections; columns that are often sorted and grouped; columns that are often used to join (primary / foreign keys)

Do not create an index using the following columns:

A column with only a few different values; a table with only a few rows

Work experience:

Reduce the use of "*" to return all columns and not unwanted columns when querying; indexes should be as small as possible, and indexes should be built on columns with small bytes; expressions containing index columns should be placed before other conditional expressions when there are multiple conditional expressions in the where clause; avoid using expressions in the order by clause; periodically rebuild or reorganize the index to defragment according to the frequency of business data occurrence; second, view

A view is an select query saved in a database. Therefore, most of the operations specified on the query can also be done on the view.

The reasons for using views are:

(1) for security reasons, users do not have to see the whole database structure, but hide part of the data; (2) it accords with the daily business logic of users, making it easier to understand the data; 1. What is a view?

A view is another way to view data in one or more tables in a database. A view is a virtual table, usually created as a subset of rows or columns from one or more tables. Of course, the view can also contain all rows and columns. However, a view is not a collection of data values stored in the database, its rows and columns come from the tables referenced in the query. At execution time, the view displays data directly from the table.

The view acts as a filter for the table specified in the query. Queries that define views can be based on one or more tables, other views, the current database, or other databases.

Views are typically used for the following three operations:

Filter rows in the table; prevent unauthorized users from accessing sensitive information; abstract multiple physical data tables into a logical data table. (1) benefits of using views:

(1) benefits to end users:

The result is easier to understand.

It's easier to get data.

(2) benefits for developers:

It is easier to restrict data retrieval

It is more convenient to maintain the application

(2) create a view

Syntax:

Create view aaasSELECT dbo. Table of basic information. Student number, dbo. A transcript. Student number AS Expr1, dbo. Table of basic information. Name, dbo. A transcript. Score FROM dbo. Basic information table INNER JOIN dbo. Transcript ON dbo. Table of basic information. Student number = dbo. A transcript. School number

View view

Select * from aa (3) considerations for using views

(1) multiple tables can be used per view

(2) similar to a query, one view can nest another view, preferably no more than three layers

(3) the select statement in the view definition cannot contain the following:

Order by clause, except for sub-ah, there is also a TOP clause in the select list of select statements; into keyword; references to temporary tables or table variables; III. Stored procedures

SQL Server uses stored procedures to avoid the security risks of sending and executing SQL code remotely.

1. Why stored procedures are needed

Today's software is mostly used in the network, while the data used by general applications is saved in the database. In database applications that do not use stored procedures, most users send requests written by SQL code to the server from the local extreme and the client through the network. The server compiles the received SQL code and executes it, and sends the specified results back to the client, which is then processed by the application software of the client and output. If the developer does not consider the security of the server comprehensively, it will provide the illegal person with the opportunity to steal data. As shown in the figure:

The unauthorized illegal person intercepts the SQL code sent by the user to the server in the network, and the rewritten malicious SQL code is submitted to the server for compilation and execution. finally, the illegal person can easily get the data he needs.

From the figure, we can see that the process performed by the application is not secure, mainly in the following aspects:

Data insecurity: the network transmits SQL code, which is easy to be intercepted by unauthorized users; each submission of SQL code has to be compiled by syntax and then executed, affecting the running performance of the application; network traffic is large: the same SQL code executed repeatedly will be transmitted on the network many times, increasing network transmission traffic.

In order to solve these problems, we can use stored procedures to precompile the SQL code for database operations and save them on the server. Users only need to input the name of the stored procedure to be executed and the necessary data on the local computer to directly call the stored procedure to complete the operation of the row management. Like this. It not only reduces the network traffic, but also ensures the running performance of the application, but also prevents unauthorized people from trying to intercept SQL code.

two。 What is a stored procedure?

Stored procedures are precompiled collections of SQL statements and control statements, saved in the database, can be called and executed by applications, and allow users to declare variables, logical control statements and other powerful programming functions.

Advantages of using stored procedures:

1. Modular programming; 2. Fast execution speed and high efficiency; reduce network traffic; 3. Reduce network traffic; 4. It has good security; 3. System stored procedure

SQL Server provides system stored procedures, which are a set of precompiled T-SQL statements. System stored procedures provide a mechanism for managing databases and updating tables, and act as a shortcut to retrieve information from system tables

(1) commonly used system stored procedures

The name of the system stored procedure for SQL Server begins with "sp-" and is stored in the Resource database. As shown in the figure:

For example:

Exec sp_databases# lists the statement text of the exec sp_helptext aa# view of the database in the current system

If xp_cmdshell is turned off as part of the server security configuration, enable it using the following statement:

Exec sp_configure 'show advanced options',1# displays advanced configuration information goreconfigure# reconfigures goexec sp_configure' xp_cmdshell',1# opens the xp_cmdshell option goreconfigure# reconfigures go (2) commonly used extended stored procedures

For example, use these statements to create some files in the system:

Exec xp_cmdshell'md c:\ bank',no_output# create folder c:\ bankexec xp_cmdshell 'dir c:\' # list the files under disk c, such as create proc ooasselect name, SUM (grade) as total score from basic information table left join score table on basic information table. Student number = grade sheet. Student ID group by name # create stored procedure qqexec qq# view stored procedure qqcreate proc ww@shuo varchar (10) asselect name, SUM (grade) as total score from basic information table left join score table on basic information table. Student number = grade sheet. Student ID group by name having name = @ shuo# create a record for each student to view exec ww Zhang San # View ww stored procedures but only look at Zhang San Si, trigger

Triggers are divided into the following types

INSERT trigger: automatically executes the SQL statement defined by the trigger when data is inserted into the table

UPDATE trigger: triggered when updating a column or multiple columns in a table, automatically executing the SQL statement defined by the trigger

DELETE trigger: triggered when a record in the table is deleted and automatically executes the SQL statement defined by the trigger.

Two special tables are managed by the system:

The statement that creates the trigger:

First kind

Create trigger deletes on chart for deleteasbegindelete from score sheet end# deletes the data from the table will not be synchronized

The second kind

Create trigger automatically synchronizes scores on chart after deleteasbegindelete from score sheet where subjects id= (select subject id from deleted) end# automatically synchronizes scores after deletion

The third kind

Create trigger forbids deletion of on basic information table for deleteasprint 'forbids deletion' rollback transaction# forbids deletion of data, rollback and recall operations are performed if data is deleted. 5. Transaction

Transaction: guarantee the atomicity, consistency, isolation and persistence of the database, referred to as ACID.

A small example

Begin transactiondeclare @ errorsum intset @ errorsum=0# defines an internal variable, which is used to store the execution result of the previous article. If the execution is successful, it is 0. If the execution is not successful, it will be transferred. Zhang San's account is 1000 less and Li Si's account is 1000 yuan more-- * / update bank set currentmoney=currentmoney-1000where name='zhangsan'set @ errorsum=@errorsum+@@ERROR update bank set currentmoney=currentmoney+1000where name='lisi'set @ errorsum=@errorsum+@@errorprint. The transaction failed to check the balance in the transfer transaction 'select * from bankif @ errorsum0 begin print'. Rollback transaction 'rollback transaction endelse begin print' transaction successfully, commit transaction, write to hard disk, permanently save 'commit transaction end go print' to view the balance after transfer transaction 'select * from bank go

-this is the end of this article. Thank you for reading-

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.