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

1. Index: a method to sort data and query data quickly.

Classification:

Unique index: the same value is not allowed

Primary key index: the index corresponding to the primary key that is created automatically. The command mode cannot be deleted.

Clustered index: the physical order is the same as the index order, and only one can be created

Nonclustered indexes: the physical order is inconsistent with the index order, and multiple indexes can be created

Composite indexes: composed of multiple columns

Full-text index: special function index

Command:

Create index: create [clustered | unique] index index name on table name (column name desc)

Use index: select * from table name with (index (index name))

Note: ① tries to index frequently used, frequently queried and sorted columns

② should not index tables with few column values or rows.

2. View: virtual table, from one or more tables

Function: ① filters data in the table

② masks sensitive data

③ abstracts multiple tables into a single table, which reduces the difficulty of operation.

Command: create view View name as SQL statement

Note: ① cannot have an order by clause unless there is a top clause

② cannot have into

③ cannot reference temporary tables

3. Stored procedure: a precompiled collection of SQL statements and control sentences, saved in the database (resource), which can be called and executed by the application

Advantages: ① modularization: once created, multiple calls

② has high speed and high efficiency.

③ reduces network traffic

④ has good security.

Classification: ① system stored procedures: start with sp_

Sp_databases: database information

Sp_tables: tables and views

Sphelptext: information about stored procedures, triggers, views

Extended stored procedures to execute commands outside of SQL, such as operating system commands, starting with xp

② user-defined stored procedures:

Command: create procedure stored procedure name as SQL statement

Execute: exec stored procedure name

Parameter values that can be added, input, and output

4. Trigger: a stored procedure that is automatically executed when inserting, updating, or deleting a table

Can implement more complex constraints than check constraints, triggered by events

Category: ① insert trigger: insert data trigger

② update trigger: update data trigger

③ delete trigger: delete data trigger

Table of trigger: stored in memory, deleted when trigger is completed

Inserted: saving new and updated information

Deleted table: stores records before they are deleted and updated

Command: create trigger trigger name on table name for [update,insert,delete] as SQL command

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.