Summary of common SQL statement Daquan

Source: http://www.cnblogs.com/0351jiazhuang/p/4530366.html

SQL is (structured query Language) Structured Query language abbreviation, the following Zhao yiming essay blog from the Basic knowledge, judgment object and application skills, introduced the application of SQL method.

Basis

Create a database

Determine if the database exists before you create it
if exists (SELECT * from sysdatabases where name= ' DatabaseName ')
Drop Database DatabaseName
Go
Create DATABASE Database-name

Deleting a database

Drop Database dbname

Backing up SQL Server

-Device to create backup data
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
-Start Backup
BACKUP DATABASE pubs to Testback

Create a new table

CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)
To create a new table from an existing table:
A:go
Use original database name
Go
SELECT * into destination database name. dbo. destination table name from the original table name (Create a new table using the old table)
B:create table tab_new as Select Col1,col2 ... from tab_old definition only

Create a sequence

Create sequence Simon_sequence
MinValue 1-min value
MaxValue 999999999999999999999999999 Maximum Value
Start with 1 starting value
Increment by 1 each time you add a few
Cache 20;

Delete a new table

drop table TabName

Add a column

Alter table tabname Add column col type
Note: Columns cannot be deleted after they are added. DB2 the column plus the data type can not be changed, the only change is to increase the length of the varchar type.

Add primary Key

Alter table TabName Add primary key (COL)
Description: Delete primary key: Alter table tabname drop primary key (COL)

Create an index

Create [unique] index idxname on tabname (col ...)
Remove index: Drop index idxname on tabname
Note: The index is immutable and you must remove the rebuild if you want to change it.

Create a View

CREATE VIEW viewname AS SELECT statement
Delete view: Drop View ViewName

A few simple basic SQL statements

Select: SELECT * FROM table1 where range
Insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where range
Updated: Update table1 set field1=value1 where range
Find: SELECT * FROM table1 where field1 like '%value1% ' (All strings containing ' value1 ' this pattern)-like syntax is very subtle, check the information!
Sort: SELECT * FROM table1 ORDER by FIELD1,FIELD2 [DESC]
Total: Select COUNT (*) as TotalCount from table1
Sum: Select SUM (field1) as Sumvalue from table1
Average: Select AVG (field1) as Avgvalue from table1
Maximum: Select Max (field1) as MaxValue from table1
Min: select min (field1) as MinValue from Table1[separator]

Several advanced query arithmetic words

A:union operator
The UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operation word must be consistent.

Using an outer JOIN

A, LEFT OUTER join:
Left OUTER join (left JOIN): The result set includes both the matching row of the join table and all rows of the left join table.
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C
B:right outer join:
Right outer join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table.
C:full outer join:
Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

Determine if an object exists

Determine if the database exists

if exists (SELECT * from sys.databases WHERE name = ' database name ')
drop database [DB name]

Determine if a table exists

If not EXISTS (SELECT * from sysobjects where [name] = ' table name ' and xtype= ' U ')
Begin
– Create a table here
End

Determine if a stored procedure exists

if exists (select * from sysobjects where id = object_id (n ' [Stored procedure name] ') and OBJECTPROPERTY (ID, n ' isprocedure ') = 1)
drop procedure [Stored procedure name]

Determine if a temporary table exists

If object_id (' tempdb.. #临时表名 ') is not null
drop table #临时表名

To determine whether a view exists

–sql Server 2000
IF EXISTS (SELECT * from sysviews WHERE object_id = ' [dbo].[ View name] '
–sql Server 2005
IF EXISTS (SELECT * from sys.views WHERE object_id = ' [dbo].[ View name] '

Determine if a function exists

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Function name] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [function name]

Get user-created object information

SELECT [name],[id],crdate from sysobjects where xtype= ' U '
/*
The xtype represents the parameter type, usually including the following C = CHECK constraint D = default value or defaults constraint F = FOREIGN KEY constraint L = log FN = scalar function IF = inline table function P = stored procedure PK = PRIMA RY KEY constraint (type is k) RF = copy Filter stored procedure S = system table TF = table function TR = trigger U = User Table UQ = UNIQUE constraint (type k) V = view X = Extended stored procedure */

Determine if a column exists

if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')
ALTER TABLE table name drop column name

To determine whether a column is self-increment

If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-increment column '
Else
print ' is not self-adding column '
SELECT * from Sys.columns WHERE object_id=object_id (' Table name ')
and Is_identity=1

Determine if an index exists in the table

if exists (SELECT * from sysindexes where id=object_id (' table name ') and name= ' index name ')
print ' exists '
Else
print ' does not exist

viewing objects in a database

SELECT * from sys.sysobjects WHERE name= ' object name '

Improve

Copying tables

(copy structure only, source table name: A new table name: b) (Access available)
Law one: SELECT * into B from a where 1<>1
Law II: SELECT top 0 * into B from a

Copy table

(Copy data, source table name: A target table name: b) (Access available)
Insert into B (A, B, c) select d,e,f from B;

Copies of tables across databases

(Use absolute path for specific data) (Access available)
Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition
Example:.. From B in ' "&server.mappath (". ") & "\data.mdb" & "' Where."

Sub-query

(Table name 1:a table name 2:b)
Select A,b,c from a where a in (select D from B or: Select A,b,c from a where a in (-)

Show article, author, and last reply time

Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b

Outer JOIN query

(Table name 1:a table name 2:b)
Select A.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C

Online View Query

(Table name 1:a
SELECT * FROM (select A,b,c from a) T where t.a > 1;

The use of between

Between includes boundary values when querying data ranges, not between does not include
SELECT * FROM table1 where time between time1 and time2
Select A,b,c, from table1 where a is not between value 1 and value 2

in how to use

SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

Delete information that is not already in the secondary table in the primary table

Two association tables delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1

Four-table joint-search problem

SELECT * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ...

Schedule five minutes Advance reminder

Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

A SQL statement to take care of database paging

Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort field

Top 10 Records

Select Top Ten * form table1 where range

Select Rank

Select all the information for the a largest record in each group of data with the same B value (similar usage can be used for forum monthly leaderboard, monthly hot product analysis, ranking by subject score, etc.)
Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b)

Derived results table

Includes all rows in TableA but not in TableB and TableC and eliminates all duplicate rows to derive a result table
(select a From TableA except (select a from TableB) except (select a from TableC)

Randomly remove 10 data

Select Top * FROM tablename ORDER by NEWID ()

Random selection of records

Select NEWID ()

Delete duplicate records

Delete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...)

List all table names in the database

Select name from sysobjects where type= ' U '

List all of the tables

Select name from syscolumns where id=object_id (' TableName ')

List arrangement

The type, vender, and PCs fields are listed in the Type field, which makes it easy to implement multiple selections, similar to case in select.
Select Type,sum (case vender if ' A ' then the PCs else 0 end), sum (case vender if ' C ' then PCs else 0 end), sum (case vender WH En ' B ' then PCs else 0 end) from tablename GROUP By type
Show Results:
Type Vender pcs
PC A 1
PC A 1
Disc B 2
Disc A 2
Mobile B 3
Mobile C 3

Initialize table table1

TRUNCATE TABLE table1

Select a record from 10 to 15

Select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by id DESC

Data type conversions

DECLARE @numid int
DECLARE @id varchar (50)
Set @numid =2005
Set @id =convert (varchar, @numid)
The above statement completes the data type int converted to varchar, the other conversions are similar, see the CONVERT function

Skills

Use of 1=1,1=2

More when combined with SQL statements
"Where 1=1" is the choice of all "where 1=2" is not selected,
Such as:
If @strWhere! = '
Begin
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] where ' + @strWhere
End
Else
Begin
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] '
End
We can write it directly.
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] where 1=1 and ' + @strWhere

Shrinking a Database

– Rebuilding the Index
DBCC REINDEX
DBCC Indexdefrag
– Shrinking data and logs
DBCC SHRINKDB
DBCC Shrinkfile

Compress database

DBCC SHRINKDATABASE (dbname)
Transfer the database to a new user with existing user rights
exec sp_change_users_login ' update_one ', ' newname ', ' oldname '
Go

Check the backup set

RESTORE verifyonly from disk= ' E:\dvbbs.bak '

Repairing the database

Alter DATABASE [Dvbbs] SET Single_user
GO
DBCC CHECKDB (' Dvbbs ', repair_allow_data_loss) with TABLOCK
GO
Alter DATABASE [Dvbbs] SET Multi_user
GO

Log cleanup

SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
Use tablename-the name of the database to manipulate
Select @LogicalFileName = ' tablename_log ',-log file name
@MaxMinutes = 10,-limit on time allowed to wrap log.
@NewSize = 1-the size of the log file you want to set (M)
-setup/initialize
DECLARE @OriginalSize int
Select @OriginalSize = size
From Sysfiles
Where name = @LogicalFileName
Select ' Original Size of ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR), @OriginalSize) + ' 8K pages or ' +
CONVERT (VARCHAR (+), (@OriginalSize *8/1024)) + ' MB '
From Sysfiles
Where name = @LogicalFileName
Create TABLE Dummytrans
(Dummycolumn char (8000) NOT NULL)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR (255)
Select @StartTime = GETDATE (),
@TruncLog = ' BACKUP LOG ' + db_name () + ' with Truncate_only '
DBCC shrinkfile (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-wrap the log if necessary.
While @MaxMinutes > DATEDIFF (MI, @StartTime, GETDATE ())-time have not expired
and @OriginalSize = (Select size from sysfiles Where name = @LogicalFileName)
and (@OriginalSize * 8/1024) > @NewSize
Begin-outer Loop.
Select @Counter = 0
while ((@Counter < @OriginalSize/16) and (@Counter < 50000))
Begin-update
Insert Dummytrans VALUES (' Fill Log ')
Delete Dummytrans
Select @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
Select ' Final Size of ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR (), size) + ' 8K pages or ' +
CONVERT (VARCHAR (+), (size*8/1024)) + ' MB '
From Sysfiles
Where name = @LogicalFileName
Drop TABLE Dummytrans
SET NOCOUNT OFF

Change a table

EXEC sp_changeobjectowner ' tablename ', ' dbo '

Store changes All tables

Create PROCEDURE dbo. User_changeobjectownerbatch
@OldOwner as NVARCHAR (128),
@NewOwner as NVARCHAR (128)
As
DECLARE @Name as NVARCHAR (128)
DECLARE @Owner as NVARCHAR (128)
DECLARE @OwnerName as NVARCHAR (128)
DECLARE Curobject CURSOR for
Select ' Name ' = name,
' Owner ' = user_name (UID)
From sysobjects
where USER_NAME (UID) [email protected]
Order BY name
OPEN Curobject
FETCH NEXT from Curobject to @Name, @Owner
while (@ @FETCH_STATUS =0)
BEGIN
If @[email protected]
Begin
Set @OwnerName = @OldOwner + '. ' + RTrim (@Name)
EXEC sp_changeobjectowner @OwnerName, @NewOwner
End–select @name, @NewOwner, @OldOwner
FETCH NEXT from Curobject to @Name, @Owner
END
Close Curobject
Deallocate Curobject
GO

Direct loop write data in SQL Server

DECLARE @i int
Set @i=1
While @i<30
Begin
INSERT INTO Test (userid) VALUES (@i)
Set @[email protected]+1
End

Summary of common SQL statement Daquan