Favorite common SQL statements & lt; 2 & gt; and SQL statements

Favorite common SQL statements <2> and SQL statements
Create Table below

Create table dbo. class_bak (Name nvarchar (25) not null default 'abc' primary key, studentnumber int DEFAULT 0); insert. class_bak (studentnumber) values (23): the default value of col1 inserted is abc. copy the structure of a table to a new table. select * into class_bak from class where 1 = 2: copy a table as another table. If you do not export data, set the condition to 1 = 2. displays the table creation statement of a table: ORACLE: desc table_nameINFORMIX: dbschmea SQLSERVER:

Modify:

Alter table class_bak add monitor varchar (10);: add an Alter table class_bak alter COLUMN montior nvarchar (10): Modify an Alter table class_bak drop COLUMN monitor ;: drop table class_bak

Create an index

It is used to speed up data query. create UNIQUE index idx1_class on class creates a UNIQUE index on the field class, that is, this field cannot be repeated during insertion. the name is idx_classinsert into class (class, teacher) values ('gao', 'lisi'); the second execution of this insert statement will fail. delete index: the drop index idx1_class on class index is re-generated and re-organized. When a table is modified, inserted, or deleted multiple times, the index needs to be re-created to improve query efficiency in the future. however, the reconstruction process consumes resources. on busy machines such as SCP, You must select the appropriate time period to re-build the index. alter index idx_class on class REBUILD re-build INDEX will delete and re-create INDEX. This will delete fragments, reclaim disk space, and re-Sort index rows on consecutive pages based on specified or existing fill factor settings. Alter index idx_class on class REORGANIZE reorganizes indexes with minimal system resources.

Indexing skills and tuning:

-In a query statement, string fields must be enclosed by single quotation marks. Do not use quotation marks for integer fields. For date fields, it is currently inconclusive, whether it will affect the performance. further tracking. -Try not to use the in clause. in this case, indexes cannot be used, resulting in low query efficiency. You can use UNION to connect multiple queries. this improves efficiency. -For Multiple query conditions, the conditions for filtering multiple indexed fields should be put in front. -Try to use equal signs in the query conditions. Do not use equal signs greater than or less than equal signs. In this case, indexes may not be used. -When all results are queried, the condition 1 = 1 is required to avoid using indexes and improve efficiency.
Create a stored procedure

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoCREATE PROCEDURE [dbo].[get_avage_age] -- Add the parameters for the stored procedure here@class_name nchar(10),@CheckDate [datetime]ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;select avg(age) from student where class=@class_name and born<@CheckDate    -- Insert statements for procedure hereEND

Run:

USE [gaotest] GODECLARE @ return_value intEXEC @ return_value = [dbo]. [get_avage_age] @ class_name = 'gaosan', @ CheckDate = '2017-01-01 'select 'Return value' = @ return_valueGO: Drop procedure get_avage_age;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.