Basics

Create a database

Determine whether the database exists before creating it

if exists (select * from sysdatabases where name='databaseName')
drop database databaseName
go
Create DATABASE database-name

Delete the database

drop database dbname

Backup sql server

Create a device for backing up 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],..)

Create new tables based on existing tables:

A: go
use Original database name
go
select * into Destination database name.dbo.Target Name from Original table name(Create new tables with old tables)
B: create table tab_new as select col1,col2... from tab_old definition only

Create Sequences

create sequence SIMON_SEQUENCE
minvalue 1 — minimum value
maxvalue 999999999999999999999999999 Maximum
start with 1 Beginning value
increment by 1 Add a few more at a time
cache 20;

Delete the new table

drop table tabname

Add a column

Alter table tabname add column col type

Note: Columns cannot be deleted after they are added. The data type cannot be changed after adding columns in DB2. The only change is to increase the length of the varchar type.

Adding primary keys

Alter table tabname add primary key(col)
Note: Delete the primary key: Alter table tabname drop primary key(col)

Create an index

create [unique] index idxname on tabname(col....)
//Delete the index:drop index idxname on tabname

Note: The index is immutable and must be deleted and rebuilt if you want to change it.

Create Views

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 table 1 (field 1, field 2) values (value 1, value 2)
Delete: delete from table1 where range
 Update: update table1 set field 1 = value1 where range
 Find: select * from table1 where field 1 like'% value 1%'(all strings containing the pattern of'value 1') - like grammar is exquisite, look up information!
Sort: select * from table1 order by field 1, field 2 [desc]
Total: select count (*) as total count from table1
 Summation: select sum (field 1) as sum value from table 1
 Average: select AVG (field 1) as AVG value from table 1
 Maximum: select max (field 1) as max value from table 1
 Minimum: select min (field 1) as min value from table 1 [separator]

Several Advanced Query Operators

Note: Several rows of query results using arithmetic words must be consistent.

Use external connections

A,left outer join: 
Left outer join (left join): The result set includes both matching rows 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 OUT JOIN b ON a.a = b.c
B: right outer join:
Right outer join (right join): The result set includes not only the matched join rows of the join table, but also all the rows of the right join table.
C: full outer join: 
External join: It includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

Judging whether an object exists or not

Judging whether the database exists or not

if exists (select * from sys.databases where name = database name)
drop database [database name]

Judging whether the table exists

if not exists (select * from sysobjects where [name] = 'Table name' and xtype='U')
begin
–Create tables here
end

Determine whether stored procedures exist

if exists (select * from sysobjects where id = object_id(N'[stored procedure name]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [stored procedure name]

Judging the existence of temporary tables

if object_id('tempdb..#Temporary table name') is not null
drop table #Temporary table name

Judging 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]'

Judging whether a function exists or not

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'
/*
xtype Representation parameter types, usually including the following C= CHECK Constraint D = default value or DEFAULT Constraint F= FOREIGN KEY Constraint L = log FN = scalar function IF = Embedded table function P = stored procedure PK= PRIMARY KEY Constraints (type K) RF = replication filter stored procedure S = system table TF = table function TR = trigger U = user table UQ= UNIQUE Constraints (type K) V = view X = extended stored procedures */

Determine whether a column exists

if exists(select * from syscolumns where id=object_id(`table name') and name ='column name')
alter table table table name drop column name

Determine whether columns are self-incremental

if columnproperty(object_id('table'),'col','IsIdentity')=1
print 'Self-adding'
else
print 'Not self-adding'
SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('Table name')
AND is_identity=1

Determine whether an index exists in a table

if exists(select * from sysindexes where id=object_id('Table name') and name='Index Name')
print 'existence'
else
print 'Non-existent

Viewing objects in the database

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

Promote

Replication table

(Copy structure only, source table name: a new table name: b) (Access available)

Method 1: select * into B from a where 1 <> 1
 Law 2: 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

(Specific data uses absolute paths) (Access is available)

Insert in B (a, b, c) select d, e, f from B in'specific database'where condition
 Ex amp le:... from b in'"& Server. MapPath (". "&" data.mdb"&"'where. ")

Subquery

(Table Name 1:a Table Name 2:b)

select a,b,c from a where a IN (select d from b perhaps: select a,b,c from a where a IN (1,2,3)

Display article, author and final response time

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

External Connection 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 OUT 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;

Use of between

Beween includes boundary values when restricting the range of query data, not between does not.

select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between numerical value1 and numerical value2

Use of in

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 main table

Two associated tables delete from table1 where not exists (select * from table2 where table1.field 1 = table2.field 1

Question of Joint Search of Four Tables

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

A five-minute reminder of the schedule

SQL: select * from calendar where datediff('minute', f start time, getdate ()> 5

A sql statement to paginate the database

select top 10 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

Selecttop 10 * form table1 where range

Choice Ranking

Choose all the information that corresponds to the largest record of a in each set of data with the same b value (similar usage can be used for forum monthly ranking, 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 Result Table

Including all rows in TableA but not in TableB and TableC and eliminating all duplicate rows to generate a result table

(select a from tableA except (select a from tableB) except (select a from tableC)

Random fetch of 10 data

select top 10 * from tablename order by newid()

Random Selection Record

select newid()

Delete duplicate records

Delete from tablename where id not in (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 items in the table

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

Listing and Arrangement

List the type, vender, pcs fields and arrange them in the type field. Case can easily realize multiple selection, similar to case in select.
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
 Display results:
type vender pcs
 Computer A 1
 Computer A 1
 CD-ROM B 2
 CD A 2
 Mobile B 3
 Mobile C 3

Initialization table table1

TRUNCATE TABLE table1

Select records from 10 to 15

select top 5 * from (select top 15 * from table order by id asc) table_alias order by id desc

Data Type Conversion

declare @numid int
declare @id varchar(50)
set @numid=2005
set @id=convert(varchar,@numid)

The data type Int is convert ed to varchar by the above statement. Other transformations are similar. See the conversion function.

Skill

Use of 1 = 1, 1 = 2

stay SQL Sentence combination is more often used
"where 1=1" It means all choices.“ where 1=2"None of them.
//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

Shrinkage database

Rebuilding index

DBCC REINDEX
DBCC INDEXDEFRAG
–Shrinkage data and logs
DBCC SHRINKDB
DBCC SHRINKFILE

Compressed database

dbcc shrinkdatabase(dbname)
Transfer database to new users with existing user privileges
exec sp_change_users_login 'update_one','newname','oldname'
go

Check backup set

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

Repair 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 Clearance

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename — The name of the database to be operated on
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(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@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 has 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(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(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 to 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)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end– select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO

SQL SERVER Write data in direct loop

declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end