Complete SQL statements. Here is the SQL you need

Basics

Create database
```
CREATE DATABASE database-name
```
Delete database
```
drop database dbname
```

Backup sql server

//device for creating backup 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 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

Delete new table
```
drop table tabname
```

Add a column

Alter table tabname add column col type
//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 varchar type.

Add primary key

Alter table tabname add primary key(col) 
//Note: delete primary key: * * Alter table tabname drop primary key(col)

Create index

create [unique] index idxname on tabname(col....) 
//Delete index
drop index idxname
//The index cannot be changed. If you want to change it, you must delete it and rebuild it.

Create view

create view viewname as select statement 
//Delete view
drop view viewname

Several simple basic sql statements

//choice:
select * from table1 where Range
//Insert:
insert into table1(field1,field2) values(value1,value2)
//Delete:
delete from table1 where Range**
//to update:
update table1 set field1=value1 where Range
//Find:
select * from table1 where field1 like '%value1%' ---like Your grammar is very exquisite. Check the information!
//Sort:
select * from table1 order by field1,field2 [desc]
//total:
select count as totalcount from table1
//Summation:
select sum(field1) as sumvalue from table1
//average:
select avg(field1) as avgvalue from table1
//maximum:
select max(field1) as maxvalue from table1
//minimum:
select min(field1) as minvalue from table1

Several advanced query operators
A: UNION operator**
The UNION operator derives a result table by combining two other result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When ALL is used with UNION (i.e. UNION ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B: Excel operator
The excel 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 Excel (excel ALL), duplicate lines are not eliminated.
C: INTERSECT operator
The INTERSECT operator derives a result table by including only the rows in both TABLE1 and TABLE2 and eliminating ALL duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate lines are not eliminated.
Note: several query result lines using operation words must be consistent.
Use external connection
A,left (outer) join:
Left outer join (left join): the result set includes several 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 both matching join rows of the join table and all rows of the right join table.
C: full/cross (outer) join:
All outer join: includes not only the matching rows of the symbolic join table, but also all records in the two join tables.
Group by:
Once a table is grouped, only group related information can be obtained after query.
Group related information: (statistical information) count,sum,max,min,avg * * grouping criteria
When grouping in SQL Server: fields of type text, ntext and image cannot be used as grouping basis
The fields in the select statistics function cannot be put together with ordinary fields;

14. Operate the database:
Detach database: sp_detach_db; Attach database: sp_attach_db followed by indicates that the full pathname is required for the attachment

//Modify the name of the database:
sp_renamedb 'old_name', 'new_name'

2, Promotion

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

//FA Yi
select * into b from a where 1<>1(Only for SQlServer)
//Method 2:
select top 0 * into b from a

2. Copy table (copy data, source table name: a, destination table name: b) (Access available)

insert into b(a, b, c) select d,e,f from b;

3. Copy of tables across databases (absolute path is used for specific data) (Access is 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. Sub query (table name 1: atable 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)

5. Show article, submitter and 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. External connection query (table name 1: atable 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. Online view query (table name 1: a)

select * from (SELECT a,b,c FROM a) T where t.a > 1;

8. Usage of between. When between limits the range of query data, boundary values are included. not between does not include boundary values

select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between Value 1 and Value 2

9. Use of in

select * from table1 where a [not] in ('Value 1','Value 2','Value 4','Value 6')

10. Two associated tables, delete the information in the primary table that is not in the secondary table

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

11. Four table joint query questions:

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. Remind the schedule five minutes in advance

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

13. A * sql * statement handles Database Paging

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**Specific implementation:**About Database Paging:
  declare [@start](https://learnku.com/users/7236) int,@end int
  @sql  nvarchar(600)
  set @sql='select top'+str(@end-[@start](https://learnku.com/users/7236)+1)+'+from T where rid not in(select top'+str(@str-1)+'Rid from T where Rid>-1)'
  exec sp_executesql @sql

Note: a variable cannot be directly followed after top, so this is the only one for special processing in practical application. Rid is an identification column. If there are specific fields after top, it is very beneficial. This can avoid inconsistency in the actual table after the query result if the * * Top * * field is logically indexed (the data in the logical index may be inconsistent with that in the data table, and if it is in the index during the query, query the index first)**

14. Top 10 records

select top 10 * form table1 where Range

15. Select all the information corresponding to the largest a record in each group of data with the same b value (similar usage can be used for forum monthly ranking, monthly hot selling 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. 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. Take out 10 pieces of data at random

select top 10 * from tablename order by **newid()**

18. Random selection of records
select newid()

19. 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

Note: this operation involves the movement of a large amount of data. This method is not suitable for high-capacity data operation 3). For example, when importing data in an external table, only part of the data is imported for the first time for some reasons, but it is difficult to judge the specific location. In this way, only all the data is imported next time, which will produce a lot of duplicate fields. How to delete duplicate fields
alter table tablename
– add a self incrementing column
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. List all table names in the database

select name from sysobjects where type='U' // U represents the user

21. List all the column names in the table

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

22. List the type, vender and pcs fields and arrange them in the type field. Case can easily realize multiple selection, 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

//Display results:
type vender pcs
**computer A 1
 computer A 1
 CD B 2
 CD A 2
 mobile phone B 3
 mobile phone C 3

23. Initialization table table1

TRUNCATE TABLE table1

24. 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

3, Skill

1 = 1 and 1 = 2 are often used in SQL statement combination
"where 1=1" means to select all and "where 1=2" means not to select all
For example:
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

2. Shrink database

--Rebuild index
DBCC REINDEX
DBCC INDEXDEFRAG
--Shrink data and logs
DBCC SHRINKDB
DBCC SHRINKFILE

3. Compress database

dbcc shrinkdatabase(dbname)

4. Transfer the database to the new user to the existing user permissions

exec sp_change_users_login 'update_one','newname','oldname'
go

5. Check the backup set

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

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

7. Log clearing

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
 @MaxMinutes INT,
 @NewSize INT
USE tablename -- Database name to operate 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

8. Change a table

exec sp_changeobjectowner 'tablename','dbo'

9. Store and change all tables

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS

DECLARE [@Name](https://learnku.com/users/31010)    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](https://learnku.com/users/31010), @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN     
if @Owner=@OldOwner 
begin
   set @OwnerName = @OldOwner + '.' + rtrim([@Name](https://learnku.com/users/31010))
   exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO [@Name](https://learnku.com/users/31010), @Owner
END

close curObject
deallocate curObject
GO

10. Write data directly and circularly 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 * *:
As shown in the following table, it is required that all failing grades in the mounting should be increased by 0.1 each time to make them just pass:

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

Data development - Classic

1. Sort by stroke of last name:

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //From less to more

2. Database encryption:

select encrypt('Original password')
select pwdencrypt('Original password')
select pwdcompare('Original password','Encrypted password') = 1--Same; Otherwise, it is different encrypt('Original password')
select pwdencrypt('Original password')
select pwdcompare('Original password','Encrypted password') = 1--Same; Otherwise, it is different

3. Retrieve 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 = 'table A'
set @sql='select '+right(@list,len(@list )-1) + 'from table A'
exec (@sql)

4. View the hard disk partition:

EXEC master..xp_fixeddrives**

5. Compare whether tables a and B are equal:

if (select checksum_agg(binary_checksum(*)) from A)
     =
    (select checksum_agg(binary_checksum(*)) from B)
print 'equal'
else
print 'Unequal'

6. Kill all event detector processes:

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

7. Record search:

//From the beginning to N records
Select Top N * From surface
//N to M records (with primary index ID)
Select Top M-N * From surface Where ID in (Select Top M ID From surface) Order by ID   Desc
//N to end record
Select Top N * From surface Order by ID Desc

Case 1:
A table has more than 10000 records. The first field RecID of the table is a self growing field. Write an SQL statement to 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: if you write this, some problems will arise. If the recid has a logical index in the table.
select top 10 recid from A where... Is to search from the index, while the following select top 30 recid from A is to search in the data table. In this way, the order in the index may be inconsistent with that in the data table, resulting in that the data queried is not the original data.

Solution
1. Use order by select top 30 record from a order by ricid. If the field is not self growing, there will be a problem

2. Add a condition in the sub query: select top 30 recid from a where recid > - 1

9. Get all user tables in the current database

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

10: Get all 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')
//The two 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 '%Table name%'

12. View all stored procedures in the current database

select name as Stored procedure name from sysobjects where xtype='P'

13. Query all databases created by users

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
//perhaps
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

14. Query the fields and data types of a table

ect column_name,data_type from information_schema.columns
where table_name = 'Table name'

15. Data operation with server database

//Link server
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', 'Remote server name or ip address '
exec sp_addlinkedsrvlogin  'ITSV ', 'false ',null, 'user name ', 'password '
--Query example

select * from ITSV.Database name.dbo.Table name

--Import example

select * into surface from ITSV.Database name.dbo.Table name

**--****Delete linked 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 '; 'user name '; 'password ',Database name.dbo.Table name)

--Generate local table

select * into surface from openrowset( 'SQLOLEDB ', 'sql server name '; 'user name '; 'password ',Database name.dbo.Table name)

--Import local table into remote table

insert openrowset( 'SQLOLEDB ', 'sql server name '; 'user name '; 'password ',Database name.dbo.Table name)

select *from Local table

--Update local table

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

--openquery Usage requires creating a connection

--First create a connection and create a linked 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 table into remote table

insert openquery(ITSV,  'SELECT *  FROM database.dbo.Table name ')

select * from Local table

--Update local table

update b

set b.column B=a.column B

FROM openquery(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 ' ).test.dbo.roy_ta

--Import local table into remote table

insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=Login name;Password=password ').database.dbo.Table name

select * from Local table

Source: www.cnblogs.com/cangqiongbingchen/...

Posted by scotte on Tue, 12 Oct 2021 19:11:16 -0700

Programmer Group

Complete SQL statements. Here is the SQL you need

Basics

2, Promotion

3, Skill

Data development - Classic

Hot Keywords