Database basic----SQL statement Daquan

Learn the database is very useful d~~ record some common SQL statements ... There is an increase in the introduction has not seen the ... Good whole ... Collection of ...
In fact, the general use is to query, insert, delete and other statements just .... But learning about stored procedures is a good thing ... In the future, the data will not be in the program to engage in. and the program and the database as long as a one-back communication can take care of all the data operation ....

First, the basic

1. Description: Create Database
Create DATABASE Database-name
2. Description: Delete Database
Drop Database dbname
3. Description: Back up SQL Server
---to create a device that backs up data
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
---start Backup
BACKUP DATABASE pubs to Testback
4. Description: 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:create table tab_new like Tab_old (create new table with old table)
B:create table tab_new as Select Col1,col2 ... from tab_old definition only
5. Description: Delete new table
drop table TabName
6. Description: 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.
7. Description: Add primary key: Alter table TabName Add primary key (COL)
Description: Delete primary key: Alter table tabname drop primary key (COL)
8. Description: Create INDEX: [unique] index idxname on tabname (col ...)
Drop INDEX: Idxname
Note: The index is immutable and you must remove the rebuild if you want to change it.
9. Description: Creating view: Create VIEW viewname AS SELECT statement
Delete view: Drop View ViewName
10, Description: 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% '---the syntax of like 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
11. Description: Several advanced query operation 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.
12. Description: Use external connection
A, LEFT OUTER join:
Left OUTER join (left JOIN): The result set includes a matching row for 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.

Second, promote

1. Description: Copy table (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
2. Description: 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;

3. Description: Copy of table 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."

4, Description: 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 (-)

5, Description: Display the article, the author and the 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

6, Description: 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 off JOIN b on a.a = B.C

7, Description: Online view query (table name 1:a
SELECT * FROM (select A,b,c from a) T where t.a > 1;

8, Description: Between usage, between limits the query data range includes the boundary value, 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

9. Description: How to use
SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table is already in the secondary table does not have information
Delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1

11, Description: Four table linked to check the 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 ...

12, Description: Schedule five minutes before the reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

13, Description: 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

14, Description: The first 10 records
Select Top Ten * form table1 where range

15, Description: Select in each group B value the same data corresponding to a maximum record of all information (similar to the usage can be used in the forum monthly leaderboard, monthly hot product analysis, ranked by the subject score, and so on.)
Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b)

16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a result table
(select a From TableA except (select a from TableB) except (select a from TableC)

17, Description: Randomly remove 10 data
Select Top * FROM tablename ORDER by NEWID ()

18, Description: Random selection of records
Select NEWID ()

19. Description: Delete duplicate records
Delete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...)

20, Description: List all the table names in the database
Select name from sysobjects where type= ' U '

21, Description: List of all the
Select name from syscolumns where id=object_id (' TableName ')

22, Description: List the type, Vender, PCs fields, arranged in the Type field, case can easily implement multiple choices, 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

23. Description: Initialize table table1

TRUNCATE TABLE table1

24. Description: Select records from 10 to 15
Select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by id DESC

Third, skills

1, the use of 1=1,1=2, in the combination of SQL statements used more

"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 diazepam ' + @strWhere

2. Shrinking the Database
--Rebuilding the index
DBCC REINDEX
DBCC Indexdefrag
--Shrinking data and logs
DBCC SHRINKDB
DBCC Shrinkfile

3. Compress the Database
DBCC SHRINKDATABASE (dbname)

4. Transfer the database to a new user with existing user rights
exec sp_change_users_login ' update_one ', ' newname ', ' oldname '
Go

5. Check the backup set
RESTORE verifyonly from disk= ' E:\dvbbs.bak '

6. Repair 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

7. 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 = ten,--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 had 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

8. Description: Change a table
EXEC sp_changeobjectowner ' tablename ', ' dbo '

9. Storage Change 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


10. Write data directly in SQL Server
DECLARE @i int
Set @i=1
While @i<30
Begin
INSERT INTO Test (userid) VALUES (@i)
Set @[email protected]+1
End

Database basic----SQL statement Daquan