Some useful SQL statements to organize recommended collections _mssql

1, Description: Create a database
CREATE DATABASE Database-name
2, Note: Delete the database
Drop Database dbname
3, Description: Backup 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
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 a new table using the 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: The column will not be deleted after it has increased. The data type can not be changed when the column in the DB2 is added, and the only change is 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: Creating index: Create [unique] index idxname on tabname (col ...)
Deleting indexes: Drop INDEX Idxname
Note: The index is not to be changed and you want to change the rebuild must be deleted.
9. Description: Create VIEW: Created view viewname as SELECT statement
Delete view: Drop View ViewName
10, Description: A few simple basic SQL statements
Selection: SELECT * FROM table1 where
Inserting: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where scope
Update: UPDATE table1 set field1=value1 where scope
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, look for information!
Sort: SELECT * from table1 ordered 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
Max: 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 (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with 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 that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated. 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 the rows in TABLE1 and TABLE2 and eliminates all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operator must be consistent.
12, Description: Use of external connections
A, LEFT OUTER join:
Left OUTER join (left connection): The result set includes a matching row for the join table and all rows of the left-attached 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 connection): The result set includes both matching connection rows for the join table and all rows of the right join table.
C:full outer join:
All outer joins include not only matching rows for symbolic join tables, but also all records in two connected tables.
Second, let's look at some good SQL statements
1, Description: Replication table (only copy structure, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1<>1
Method Two: 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, note: cross-database copy of the table (specific data using absolute path) (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: Subquery (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 where a (1,2,3)
5. Description: Display article, author and final 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-out 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 use, between limit 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 numeric 1 and value 2
9, Description: In the use of methods
SELECT * FROM table1 where a [isn't] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')
10, Description: Two related tables, delete the main table has not been in the secondary table of information
Delete from table1 where does exists (SELECT * from table2 where table1.field1=table2.field1)
11, note: Four table joint Inquiry question:
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 ....
12. Description: Schedule five minutes advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5
13, Description: A SQL statement to handle the 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 fields
14, Description: The first 10 records
Select top * form table1 where scope
15, Description: Select in each group B value of the same data in the corresponding a the largest record of all information (similar to the use can be used for the forum monthly rankings, monthly hot product analysis, ranking by subject results, etc.)
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 and derive a result table
(select a from TableA) except (select a-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, Note: Delete duplicate records
Delete from TableName where ID. (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: Listed type, Vender, PCs fields, sorted by Type field, case can easily implement multiple choices, similar to the case in select.
Select Type,sum (Case vender when ' A ' then pcs else 0), sum (case vender when ' C ' then pcs else 0), sum (case vender WH En ' B ' then PCs else 0 end] from tablename GROUP By type
Show Results:
Type Vender pcs
Computer A 1
Computer A 1
CD B 2
CD A 2
Mobile B 3
Mobile C 3
23, Description: Initialization of the table table1
TRUNCATE TABLE table1
24, Description: Select from 10 to 15 records
Select Top 5 * "from" (select top * to table ORDER by ID ASC) Table_ alias ORDER by id DESC

Ext
1. View the version of the database
SELECT @ @version
Several common SQL Server patched version numbers:
8.00.194 Microsoft SQL Server 2000
8.00.384 Microsoft SQL Server SP1
8.00.532 Microsoft SQL Server SP2
8.00.760 Microsoft SQL Server SP3
8.00.818 Microsoft SQL Server SP3 w/cumulative Patch ms03-031
8.00.2039 Microsoft SQL Server SP4
2. View the machine operating system parameters of the database
EXEC master.. xp_msver
3. View Database Startup Parameters
sp_configure
4. View Database Startup time
Select CONVERT (varchar, login_time,120) from master. sysprocesses where spid=1
To view the database server name and instance name
print ' Server Name ... ...: ' + CONVERT (varchar (), @ @SERVERNAME), ".
print ' Instance ....: ' + CONVERT (varchar), @ @SERVICENAME. @)., d..
5. View all database names and sizes
sp_helpdb
To rename SQL for a database
Sp_renamedb ' Old_dbname ', ' new_dbname '
6. View all database user login information
Sp_helplogins
View the role information that all database users belong to
Sp_helpsrvrolemember
Fix_orphan_user scripts or Loneuser procedures that can be used when repairing orphaned users when migrating servers
Change the user owner of a data object
sp_changeobjectowner [@objectname =] ' object ', [@newowner =] ' owner '
Note: Changing any part of an object name can corrupt scripts and stored procedures.
To back up the database user login information on a single server, you can use Add_login_to_aserver script
View Object-level user permissions under a database
Sp_helprotect
7. View Linked Servers
Sp_helplinkedsrvlogin
View Remote database user logon information
Sp_helpremotelogin
8. View the size of a data object under a database
sp_spaceused @objname
You can also see the largest n (default 50) table with the Sp_toptables procedure
View index information for a data object under a database
Sp_helpindex @objname
You can also use the Sp_nchelpindex procedure to view more detailed indexing
Sp_nchelpindex @objname
Clustered index is the physical order of records, the index occupies less space.
Tables with very frequent key-value DML operations I recommend that you use a clustered index and a constraint, and the FILLFACTOR parameter will have a default value.
To view the constraint information for a data object under a database
Sp_helpconstraint @objname
9. View all stored procedures and functions in the database
Use @database_name
Sp_stored_procedures
To view the source code for stored procedures and functions
Sp_helptext ' @procedure_name '
View the name of a data object that contains a string @str
SELECT DISTINCT object_name (ID) from syscomments where text like '% @str% '
Create an encrypted stored procedure or function with the with encryption parameter in front of AS
Decryption of encrypted stored procedures and functions can be done using the Sp_decrypt procedure
10. View information about users and processes in the database
sp_who
View information about active users and processes in the SQL Server database
sp_who ' Active '
To view the locks in the SQL Server database
Sp_lock
The process number 1--50 is used internally within the SQL Server system, and the process number greater than 50 is the user's connection process.
The SPID is the process number, dbid is the database number, ObjID is the data object number
To view the SQL statement that the process is executing
DBCC INPUTBUFFER ()
We recommend that you use the improved SP_WHO3 process to see directly the SQL statements that the process runs
Sp_who3
Check for deadlocks with the Sp_who_lock process
Sp_who_lock
11. How to view and shrink database log files
View all database log file sizes
DBCC SQLPERF (LOGSPACE)
If some log files are large, shrink the simple recovery mode database log, and the @database_name_log size unit is M
Backup LOG @database_name with NO_LOG
DBCC SHRINKFILE (@database_name_log, 5)
12. Methods for analyzing SQL Server SQL statements:
Set STATISTICS Time {on | off}
Set STATISTICS IO {on | off}
Graphical display of query execution plans
In Query Analyzer-> Query-> show estimated evaluation plan (D)-ctrl-l or click on the graphics in the toolbar
Text mode display Query execution plan
Set SHOWPLAN_ALL {on | off}
SET SHOWPLAN_TEXT {on | off}
Set STATISTICS Profile {on | off}

13. When inconsistent error, NT Event Viewer out of error No. 3624, repair the database method
Comment out the tables referenced in the application for inconsistencies and then restore and repair them on the backup or other machines first
ALTER DATABASE [@error_database_name] set Single_user
To fix a table that appears inconsistent errors
DBCC CHECKTABLE (' @error_table_name ', Repair_allow_data_loss)
Or, unfortunately, choose to fix a small database name with inconsistent errors
DBCC CHECKDB (' @error_database_name ', Repair_allow_data_loss)
ALTER DATABASE [@error_database_name] set Multi_user
CHECKDB has 3 parameters:
Repair_allow_data_loss includes assigning and unassign rows and pages to correct allocation errors, structure rows, or pages.
and deleting corrupted text objects, these fixes can result in some loss of data.
A repair operation can be completed under a user transaction to allow the user to roll back the changes.
If you roll back the repair, the database will still contain errors and should be recovered from the backup.
If an incorrect fix is omitted due to the level of repair provided, any fixes that depend on the repair will be omitted.
When the repair is complete, back up your database.
Repair_fast for small, time-consuming fixes, such as fixing additional keys in a nonclustered index.
These fixes can be done quickly, and there is no risk of losing data.
Repair_rebuild performs all the repairs performed by Repair_fast, including the need for a longer period of repair, such as rebuilding an index.
There is no risk of losing data when performing these repairs.

SQL statement Instance
1 examples
=======================================
Select Id,age,fullname from Tableone a
Where a.id!= (select Max (id) from Tableone b where a.age=b.age and A.fullname=b.fullname)
=========================================
Delete FROM dbo. Schedule where
roomid=29 and Starttime> ' 2005-08-08 ' and endtime< ' 2006-09-01 ' and remark like ' preset ' and userid=107
and (
(scheduleid>=3177 and scheduleid<=3202)
or (scheduleid>=3229 and scheduleid<=3254)
or (scheduleid>=3307 and scheduleid<=3332)
=========================================
Delete Tableone
Where tableone.id!= (select Max (id) from Tableone b where tableone.age=b.age and tableone.fullname=b.fullname);
==========================================
Dataclient 12/23/2005 5:03:38 PM
Select Top 5
Doc_main. Current_version_no as VERSION, Doc_main. Modify_date as Modifydt, Doc_main. SUMMARY as SUMMARY, Doc_main. Author_employee_name as AuthorName, Doc_main. Title as title, Doc_main. document_id as DocumentID, attribute.attribute_id as AttributeID, attribute.catalog_id as Catalogid, DOC_STATISTIC. Visite_times as Visitetimes, doc_statistic. document_id as DocumentID2
From Doc_main Doc_main
Inner join Catalog_self_attribute ATTRIBUTE on Doc_main. catalog_id=attribute.catalog_id
Left join Doc_statistic doc_statistic on Doc_main. Document_id=doc_statistic. document_id
Where (Doc_main. author_employee_id = 1) and (attribute.attribute_id = 11)
ORDER BY Visitetimes DESC
====================================
Select top 1 Document_id,employee_name,comment_date,comment_value
FROM dbo. Doc_comment
where document_id=19 and comment_date = (select Max (comment_date) from doc_comment where document_id=19)
====================================

Select TITLE, (select top 1 employee_name
FROM dbo. Doc_comment where document_id=19) Commentman,
(SELECT top 1 comment_date
FROM dbo. Doc_comment where document_id=19) comment_date
From Doc_main where document_id=19
======================================
Alter VIEW Expertdoctopcomment
As

Select document_id, Max (order_number) as Lastednum
FROM dbo. Doc_comment
GROUP BY document_id

Go
Alter VIEW Expertdocview
As
Select TITLE, a.author_employee_id, C.employee_name, c.comment_date
FROM dbo. Doc_main A
Left Join
Expertdoctopcomment b

On
a.document_id = b.document_id

INNER JOIN
Doc_comment C
On
b.document_id = c.document_id and
B.lastednum = c. order_number
======================================
Select a.ID, A.windowsusername,
0, 1,
A.email,

Case B.enfirstname as null then A.username else B.enfirstname end,
Case B.enlastname as null then A.username else B.enlastname end
From UUMS_KM.dbo.UUMS_User A
Left Join
UUMS_KM.dbo.HR_Employee b
On
A. Hr_employeeid = b.ID
=====================================
List the IDs of the five people who uploaded the documents
Select Author_employee_id,count (author_employee_id)
FROM dbo. Doc_main
GROUP BY author_employee_id
Order by Count (author_employee_id)
2719 2
4 {
12 30
1 116
List information for five people uploading documents
Select DISTINCT author_employee_id, author_employee_name
FROM dbo. Doc_main
where author_employee_id
In (
Select Top 5 author_employee_id
FROM dbo. Doc_main
GROUP BY author_employee_id
Order by Count (author_employee_id)
)