Detailed SQL statements in full classification (packaged download)

May 16, 2021 SQL

1. Detailed SQL statements are fully classified

Detailed SQL statements are fully classified

Structured Query Language is a structured query language, short for SQL. S QL is a special purpose programming language, a database query and programming language for accessing data and querying, updating, and managing the relationship database system; Sql statements are also a lot of kinds and numbers, many of which are often used, the following common SQL statements are sorted, and can also be packaged and downloaded, hoping to help you.

Download address first: Point here to download SQL Statement Full

First, the basic article

1, Description: Create a database

CREATE DATABASE database-name

2, Description: Delete the database

drop database dbname

3, Description: Back up sql server

--- to create a device that backs up your data

USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'

--- to start the 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],..)

Create a new table from an existing table:
A: Create table tab_new like tab_old (create a new table with the old table.) )
B ： create table tab_new as select col1,col2... from tab_old definition only

5, Description: Delete the new table

drop table tabname

6, Description: Add a column

Alter table tabname add column col type

Note: Columns cannot be deleted when they are added. The data type cannot be changed by adding columns to DB2, the only thing that can be changed is to increase the length of the varchar type.

7, Description: Add the primary key

Alter table tabname add primary key(col)

Remove the primary key:

Alter table tabname drop primary key(col)

8, Description: Create an index

create [unique] index idxname on tabname(col….)

Delete the index:

drop index idxname

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

9, Description: Create a view

create view viewname as select statement

Delete the view:

drop view viewname

10, Description: A few simple basic sql statements

Choose:

```
select * from table1 where 范围
```

Insert:

insert into table1(field1,field2) values(value1,value2)

Delete:
```
delete from table1 where 范围
```

Update:

update table1 set field1=value1 where 范围

Find:
```
select * from table1 where field1 like ’%value1%’ 
```
--- like's grammar is very subtle, please 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

Biggest:

select max(field1) as maxvalue from table1

Minimum:

select min(field1) as minvalue from table1

11, Description: Several advanced query operators

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. W hen 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 in TABLE1 but not in TABLE2 and eliminating all duplicate rows. W hen ALL is used with EXCEPT (ALL ALL), duplicate rows are not eliminated.

C: InterSECT operator
The INTERSECT operator derives a result table by including only the rows that are available in TABLE1 and TABLE2 and eliminating all duplicate rows. W hen ALL is used with INTERSECT (INTERSECT ALL), duplicate rows are not eliminated.

Note: Several query result lines that use operators must be consistent.

12, Description: Use an external connection

A 、 left （ outer ） join ：
Left Outer Connection (Left Connection): The result set includes several matching rows of the connection table, as well as all rows of the left connection 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 Connection ( ) set includes both the matching connection rows of the connection table and all rows of the right connection table.

C ： full/cross （ outer ） join ：
Full external connection: Includes not only the matching rows of the symbolic connection table, but also all records in the two connection tables.

13, Group : Group by:

A table that, once the grouping is complete, only group-related information can be obtained after the query.
Group related information: (statistics) count, sum, max, min, avg grouping criteria )
When grouping in SQLServer: Fields of type text, ntext, image cannot be grouped
Fields in selecte statistical functions cannot be placed with normal fields;

14, to operate on the database

Detached database: sp_detach_db; Additional database: The sp_attach_db indicates that the attach requires a full path name

15, how to modify the name of the database

sp_renamedb 'old_name', 'new_name'

Second, the promotion of the article

1. Description: Copy table (copy structure only, source table name: a new table name: b) (Access available)

Method 1: (SQLServer only)

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 a;

3. Description: Copy of tables across databases (absolute path for specific data) (Access available)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

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 (1,2,3)

5. Note: The article, the author and the time of the final reply are shown

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: the use of between, between limit the scope of query data includes boundary values, not between does not include

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9, description: the use of in

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10, description: two associated tables, delete the main table has not been in the sub-table information

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

11, description: four tables to check the problem

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 in advance reminder

select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

13, description: a sql statement to do database pedding

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

Specific implementation:
About database pedding:

declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql

Note: You can't follow a variable directly after top, so in practice there is only this special treatment. R id is an identity column, which is very beneficial if there are specific fields after top. Because this prevents the top field from being logically indexed, the result of the query is inconsistent with the actual table (the data in the logical index may be inconsistent with the data table, and the index is queried first if the query is in the index)

14, Description: Top 10 records

select top 10 * form table1 where 范围

15. Description: Select all the information corresponding to a maximum record in each set of data with the same b-value (usages like this can be used for forum monthly leaderboards, monthly hot product analysis, ranking by account score, 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 derive a result table by eliminating all duplicate rows

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

17, description: randomly take out 10 data

select top 10 * from tablename order by newid()

18, description: random selection of records

select newid()

19, Description: Delete duplicate records

1、

delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

2、

select distinct * into temp from tablename 
delete from tablename 
insert into tablename select * from temp

Evaluation: This operation involves the movement of large amounts of data, which is not suitable for large-capacity but data operations

3, for example: import data in an external table, for some reasons for the first time only a part of the import, but it is difficult to determine the specific location, so that only in the next all import, which will also produce a lot of duplicate fields, how to delete duplicate fields

alter table tablename 
--添加一个自增列 
add column_b int identity(1,1) 
delete from tablename where column_b not in( 
select max(column_b) from tablename group by column1,column2,...) 
alter table tablename drop column column_b

20, Description: List all the table names in the database

select name from sysobjects where type='U' // U代表用户

21. Description: List all the columns in the table

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

22, description: list type, vender, pcs fields, arranged in type fields, case can easily implement multiple selections, similar to the 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

Show results:

type vender pcs
电脑 A 1 
电脑 A 1 
光盘 B 2 
光盘 A 2 
手机 B 3 
手机 C 3

23, Description: Initialize table table1

TRUNCATE TABLE table1

24, Description: Select records from 10 to 15

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

Third, the skill article

The use of 1, 1, 1, 2 is used more when combining SQL statements

"Where 1 s 1" means selecting all "where 1 s 2" All 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 directly:

错误！未找到目录项。
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定'+ @strWhere

2, shrink the database

--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
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 purge

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(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

8, Description: Change a table

exec sp_changeobjectowner 'tablename','dbo'

9, store all tables that change

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

10, write data in a direct loop in SQL SERVER

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

Case:

THERE IS A THOSE WHO HAVE NOT The Requirements of The Requi To Meet The Requirements on The Base of 0.1 AT A Time Basiis, SO That They Can Meet The Requirements:

Name score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69
while((select min(score) from tb_table)<60) 
begin 
update tb_table set score =score*1.01 
where score<60 
if (select min(score) from tb_table)>60 
break 
else 
continue 
end

Fourth, data development

1. Sort by last name stroke

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多

2. Database encryption

select encrypt('原始密码') 
select pwdencrypt('原始密码') 
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码') 
select pwdencrypt('原始密码') 
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同

3. Get back the fields in the table

declare @list varchar(1000), 
@sql nvarchar(1000)  
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A' 
set @sql='select '+right(@list,len(@list)-1)+' from 表A'  
exec (@sql)

4. View the hard drive partition

EXEC master..xp_fixeddrives

5. Compare A to whether Table B is equal

if (select checksum_agg(binary_checksum(*)) from A) 
= 
(select checksum_agg(binary_checksum(*)) from B) 
print '相等' 
else 
print '不相等'

6. Kill all event probe processes

DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses 
WHERE program_name IN('SQL profiler',N'SQL 事件探查器') 
EXEC sp_msforeach_worker '?'

7. Record the search

Start with N records

Select Top N * From 表

N to M records (with primary index ID)

Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc

N to the end of the record

Select Top N * From 表 Order by ID Desc

Case

Example 1: A table has more than 10,000 records, and the first field of the table, RecID, is a self-growth field, write an SQL statement, and find the 31st to 40th records of the table.

select top 10 recid from A where recid not in(select top 30 recid from A)

Analysis: Writing like this can cause some problems if the recid has a logical index in the table.

select top 10 recid from A where...... is looked from the index, and then the select top 30 recid from A is looked up in the data table, so that because the order in the index may be inconsistent with the data table, this results in the query is not the original desired data.

Solution

1, with order by select top 30 recid from A order by ricid if the field is not self-growing, there will be a problem

2, in that sub-query also add conditions: select top 30 recid from where where recid;-1

Example 2: Query the last bar record in the table and don't know how much data the table has and the structure of the table.

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

9: Get all the user tables in the current database

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

10: Gets all the fields for a table

select name from syscolumns where id=object_id('表名')

select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')

Note: Both of these methods have the same effect

11: View views, stored procedures, and functions related to a table

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

12: View all stored procedures in the current database

select name as 存储过程名称 from sysobjects where xtype='P'

13: Query all databases created by the user

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

Or:

select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

14: Query the fields and data types of a table

select column_name,data_type from information_schema.columns 
where table_name = '表名'

15: Data operations between different server databases

--Create a link server

exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '

--An example query

select * from ITSV.数据库名.dbo.表名

--Import examples

select * into 表 from ITSV.数据库名.dbo.表名

-- Remove the link server when it is no longer in use

exec sp_dropserver 'ITSV ', 'droplogins '

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

--1、openrowset

--Example of a query:

select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--Build a local table:

select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--Import a local table into a remote table:

insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表

--Update the local table:

update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1

--Openquery usage requires creating a connection

-- Start by creating a connection to create a link server

exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

-- Query

select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

-- Import the local table into the remote table

insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表

--Update the local table

update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A

--3、opendatasource/openrowset

SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码' ).test.dbo.roy_ta