SQL Server Common Syntax statement operations

SQL Server statement Operations

--1, getting the table's primary key field
Select name from syscolumns where id=object_id (' table name ') and colid= (select top 1 colid from Sysindexkeys where id=object_id (' table Name '))

Select A.column_name
From INFORMATION_SCHEMA. Constraint_column_usage A Join
(SELECT * from sysobjects where xtype=n ' PK ') B
On object_id (a.constraint_name) =b.id where a.table_name= ' table name '

The first method uses only one primary key in the table and does not fit with the multi-field federated primary key.
The second method, both of which are suitable.

--2, gets the field name, type, length of the table
Select Dbo.sysobjects.name as TABLE_NAME,
Dbo.syscolumns.name as column_name,
Dbo.systypes.name as Type_name,
Dbo.systypes.length as Type_length,
ColumnProperty (dbo.syscolumns.id,dbo.syscolumns.name, ' precision ') as Column_lengh
From Dbo.syscolumns inner JOIN dbo.sysobjects
On dbo.syscolumns.id = Dbo.sysobjects.id
Left JOIN Dbo.systypes
On dbo.syscolumns.xtype = Dbo.systypes.xusertype
where dbo.sysobjects.name = ' denglu '--table name
and (Dbo.sysobjects.xtype = ' u ')
and (Not (Dbo.sysobjects.name like ' dtproperties '))

--1, creating a database
CREATE DATABASE database_name

--2, deleting a database
DROP DATABASE database_name

--4, creating a new table
CREATE TABLE table_name
(
   -Create column
    col1 type1 [NOT NULL] [ Primary key],
    col2 type2 [NOT NULL]
     
   --instance
    Name varchar (+) NOT null primary key,
    Age Int NOT NULL
)
--Create a new table from an existing table
 create table new_table like old_table--Create a new table with the old table
 create table New_table as select col1,col2,col3 from ole_table definition only--definition is not data inserted at the time of Create Table. That keyword means "define no data only"

--5, deleting a new table
DROP TABLE table_name

--6, adding a column
Alter Table table_name Add column col type--col to add name, type
--Note: The column will not be deleted after the addition of the DB2 column after the data type can not be changed, the only thing that can change is the length of the varchar.

--7, adding a primary key
Alter table table_name Add primary key (COL)--col as column name

--8, delete primary key
Alter table table_name Drop PRIMARY KEY (COL)--col as column name

--9, creating an index
Create [unique] index idxname on TableName (col1,col2)

--10, deleting an index
Drop INDEX Idxname---Note: The index is immutable and you want to change the newly created

--11, creating views
CREATE View view_name as select COL1,COL2,COL3 from table_name

--12, deleting views
Drop View View_name

--13, a few simple basic SQL statements
SELECT * FROM table_name WHERE condition
Insert into table_name (FIELD1,FIELD2) VALUES (values1,values2)
Delete from table_name where range
UPDATE table_name set FIELD1=VALUES1,FIELD2=VALUES2 where condition
SELECT * FROM table_name where field1 like '%values1% '--like syntax is very subtle, please check the information
SELECT * FROM table_name ORDER BY field1,field2 Desc--desc is a flashback arrangement, ASC is a positive order
Select count as ' total number of names ' from table_name
Select SUM (field1) as ' Sum ' from table_name
Select AVG (field2) as ' average ' from table_name
Select Max (field3) as ' maximum ' from table_name
Select min (filed4) as ' minimum ' from table_name

----14, several advanced query operation words
A:union operator
The Union operator derives a result table by combining the other two result tables (for example, Table_name1 and table_name2) 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 by including all in Table1 but not in the
Table2 rows in a row and clears all duplicate rows to derive a result table, and when all is used with Except (Except all), duplicate rows are not cleared.

C:intersect operator
The Intersect operator derives a result table by including only rows in Table1 and Table2 and clears all duplicate rows, and when all is used with Intersect (Intersect all), duplicate rows are not cleared
Note: Several query result rows that use an operation word must be consistent.

--15, using external connections
A, left (outer) join:
Left OUTER join (left JOIN): The result set includes a matching row for the join table, and also includes all rows of the left join table.

Sql:select a.a,a.b,b.a,b.b from a left off join B on a.a=b.b

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/cross (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.

--16, grouping: GROUP by
A table, once the group is complete, the query can only get group-related information,
Group-related information: (Statistical information) Count,sum,max,min,avg

Grouping criteria when grouping in SQL Server: You cannot group by a field of type Text,ntext,image.
In the Select Statistics function to get the field, not to drink ordinary fields put together;

--17, operate the database:
Detach Database: sp_detach_db database_name path name
Additional databases: sp_attach_db database_name path name

--18, modify the name of the database
Sp_renamedb ' old_databasename ' new_databasename '

--1, copy table (copy structure only, source table name: A, new table name: B) (Access available)
Method One: SELECT * to B from a where 1<>1 (SQL Server only)
Method Two: Select top 0 * into B from a

--2, copy table (copy data, source table name: A, new table name: B) (Access available)
Insert into B (a,b,c) select D,e,f from A;

--3, copies of tables across databases (using absolute paths for specific data) (Access available)
Insert into B (a,b,c) Select D,e,f from a in ' specific database ' where condition

--4, sub-query (table name 1:ta, table name 2:TB)
Select A,b,c from Ta where a in (select D from TB)
Select A,b,c from Ta where a in (all-in-a-

--5, show article, author, and last reply time
Select A.title,a.username,b.adddate from Ta, (select Max (adddate) adddate from TB where ta,title=tb.title) b

--6, outer join query (table name 1:ta, table name 2:TB)
Select Ta.a,ta.b,ta.c,tb.c,tb.f from TA left off join TB on TA.A=TB.C

--7, online view query
SELECT * FROM (select A,b,c from TA) T where t.a>1

--8, between usage, between limits the bounds of the query data range, not between does not include
SELECT * FROM table1 where Timea between Time1 and Time2
Select A,b,c from table1 where a is not between value 1 and value 2

How to use--9 and in
SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 3 ', ' Value 4 ')

--10, two associated tables, deleting information that is not already in the secondary table in the main table
Delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1)

The problem of--11 and four-table joint investigation
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 ' condition '

--12, schedule five minutes advance reminder
SELECT * FROM Schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

--13, 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
Specific implementation:
About Database paging:
declare @start int, @end int
declare @sql nvarchar (600)

Set @sql = ' Select Top ' +str (@[email protected]+1) + ' * from UserInfo where UserID not in (select Top ' +str (@start-1) + ' UserID From UserInfo where userid>-1) '
EXEC sp_executesql @sql

Note: After top cannot directly follow a variable, So in practical applications only such special processing. UserID is an identity column, which is advantageous if there is a specific field on top, because it avoids the fact that the top field is logically indexed and the result of the query is inconsistent in the actual table ( The data in the logical index is likely to be inconsistent with the data table, and the index is queried first if it is in the index.

--14, Top 10 records
Select Top Ten * from table1 where ' condition '

--15, selects all the information of a largest record in the same data of each group 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)

--16, including all rows in TableA but not in TableB and TableC, and eliminating all duplicate rows to derive a result row
(select a from TableA) except (select a from TableB) except (select a from TableC)

--17, randomly remove 10 data
Select TOP * FROM table_name ORDER by NEWID ()

--18, random selection record
Select NEWID ()

--19, deleting duplicate records
Delete from table_name where ID not in (the Select Max (ID) from table_name GROUP by COL1,COL2)

SELECT DISTINCT * to TEMP from table_name
Delete from table_name
Insert into table_name SELECT * FROM TEMP
--Evaluation: This operation is implicated in the movement of large amounts of data, which is not suitable for large-capacity data operations

--Import data in an external table, for some reason only part of the first import, but it is difficult to read the exact location, so that only the next time all imports, so that will produce a lot of duplicate fields, how to delete duplicate fields
ALTER TABLE TABLE_NAME
--Add a self-increment column
Add Column_b int identity (+)

Delete from table_name where Column_b not in (select Max (COLUMN_B) from table_name GROUP by COLUMN1,COLUMN2)

--Delete Increment column
ALTER TABLE table_name DROP column Column_b

--20, list all table names in the database
Select name from sysobjects where type= ' sa '--sa for the user

--21, list all column names in table
Select name from syscolumns where ID=OBJECT_ID (' table_name ')

--22, listing type, Vender, and 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 whe N ' B ' then PCs else 0 end) from table_name GROUP by type

--23, initializing tables table
TRUNCATE TABLE table_name

--24, select records from 10 to 15
Select Top 5 * FROM (SELECT top to TABLE_NAME ORDER by ID ASC) table_new ORDER BY id DESC

The third floor is superb.

Use of--1, 1=1 1=2, more in combination of SQL statements
Where 1=1 is the choice of all,
Where 1=2 is to indicate that all is not selected
Such as:
If @strWhere! = "
Begin
Set @strSQL = ' SELECT count (*) as total from [' [email protected]+ '] where '[email protected]
End
Else
Begin
Set @strSQL = ' SELECT count (*) as total from [' [email protected]+ '] '
End

--2, shrinking databases
--Rebuilding the index
DBCC REINDEX
DBCC INDEXDEFRAG
--Shrinking data and logs
DBCC SHRINKDB
DBCC SHRINKFILE

--3, compressing the database
DBCC SHRINKDATABASE (database_name)

--4, transferring the database to a new user already exists user rights
exec sp_change_users_login ' update_one ', ' newname ', ' oldname '
Go

--5, checking backup sets
Restore VERIFYONLY from disk= ' path '

--6, repairing the database
ALTER DATABASE [Dvbbs]set Single_user
Go
DBCC CHECKDB (' Dvbbs ', repair_allow_data_loss) with Tablock
Go
ALTER DATABASE [Dvbbs]set Multl_user
Go

Classic
--1, sort by last name stroke
SELECT * FROM table_name ORDER BY CustomerName Collate Chinese_prc_stroke_ci_as-from less to more

--2, database encryption
Select Enctypt (' original password ')
Select Pwdencrypt (' original password ')
Select Pwdcompare (' original password ', ' encrypted password ') =1--the same, otherwise different encrype (' original password ')

Select Pwdencrypt (' original password ')
Select Pwdcompare (' original password ', ' encrypted password ') =1--the same;

--3, retrieving fields from a table
DECLARE @list varchar (1000)
declare @sql nvarchar (1000)
Select @[email protected]+ ', ' +b.name from sysobjects a,syscolumns b where a.id=b.id and a.name= ' Table A '
Set @sql = ' select ' +right (@list, Len (@list)-1) + ' from Table A '
EXEC (@sql)

--4, viewing hard disk partitions
Exec Master. Xp_fixeddrives

--5, compare A, B table is equal
if (select Checksum_agg (binary_checksum (*)) from Table A)
= (select Checksum_agg (binary_checksum (*)) from table B)
print ' equal '
Else
print ' Not Equal '

--6, kill all Profiler processes:
Declare hcforeach cursor Global for select ' Kill ' +rtrim (spid) from master.dbo.sysprocesses
where program_name in (' SQL Profiler ', ' SQL Profiler ')

--7, Record Search
--Start to N Records
Select Top N * FROM table

--n to M record (with primary index ID)
Select Top M-n * from table where ID in (select top M-ID from table) ORDER BY ID Desc

--n to end record
Select Top N * from ORDER BY ID Desc

Case
For example 1: A table with more than 10,000 records, the first field of the table RecId is the self-growth field, write an SQL statement, find the table of the 31st to DI40 Records
Select Top RecId from a where recid not in (select top of recid from a)
Parsing: If this writes a certain problem, if RecId has a logical index in the table.
Select Top Ten recid from A where ... Is looked up from the index, and the subsequent select top recid from A is looked up in the data table, so that the order in the index is likely to be inconsistent with the data table, which results in the query is not the original data to be obtained.
Solution:
1. With ORDER BY:
Select top RecId from A order by ricid if the field is not self-growing, the problem occurs.
2, in that sub-query also add conditions:
Select top RecId from A where recid>-1

Example 2: Querying the last few records in a table, and not knowing how much data and table structure the table has
Set @s= ' select top 1 from t where PID not in (select Top ' +str (@count-1) + ' pid from T ') '
Print @s
EXEC sp_executesql @s

--8, get all the user tables in the current database
Select Name from sysobjects where type= ' u ' and status>=0

Select Name from sysobjects where xtype= ' u ' and status>=0

--9, getting all the fields of a table
Select name from syscolumns where id=object_id (' Table name ')

Select name from syscolumns where ID in (select id from sysobjects where type= ' u ' and name= ' table name ')

--10, viewing an attempt, stored procedure, function, related to a table
Select a.* from sysobjects a,syscomments b where a.id=b.id and b.text like '% table name% '

--11, view all stored procedures in the current database
Select name as ' stored procedure name ' from sysobjects where xtype= ' P '

--12, querying all databases created by the user
SELECT * FROM Master. sysdatabases D where Sid not in (select Sid from Master: syslogins where name= ' sa ')

Select Dbid,name as db_name from master. sysdatabases where sid<>0x01

--13, querying the fields and data types of a table
Select Column_name,data_type from Information_schema.columns
where table_name= ' userinfo '--userinfo table name

--14, data manipulation between different server databases
--Create a connection server

exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '

exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username ', ' password '

--query Example
SELECT * from ITSV. Database name. dbo. Table name

--import Example
SELECT * into table from ITSV. Database name. dbo. Table name

--Remove the connection server when no longer in use
exec sp_dropserver ' itsv ', ' droplogins '

--Connect remote/LAN data (Openrowset/openquery/opendatasource)

--1, OPENROWSET

--query Example
SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; Username ', ' password ', ' database name. dbo. Table name ')

--Raw cost surface
SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Import local tables to remote tables
Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; User name '; ' Password ', database name. dbo. Table name) SELECT * FROM local surface

--Update the local surface
Update b set B. Column a=a. Column A from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; User name '; ' Password ', database name. dbo. Table name) as a inner join local table B
On A.column1=b.column1

--2 OPENQUERY usage need to create a connection

--First create a connection to create a connection server
exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '

--Query
SELECT * FROM OPENQUERY (ITSV, ' select * from database. dbo. Table name ')

--Import local tables to remote tables
Insert OpenQuery (ITSV, ' select * from database. dbo. Table name ') SELECT * from local surface

--Update the local surface
Update b set B. column b=a. Column b from (ITSV, ' select * from database. dbo. Table name ') as a inner join local table B on a. Column a=b. Column A

--3 Opendatasource/openrowset
SELECT * from OpenDataSource (' SQLOlEDB ', ' Data source=ip/servername; User id= login name; password= password '). Text.dbo.roy_ta

--Local Table import remote table
Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name SELECT * FROM local surface

SQL Server Common Syntax statement operations