Classic SQL Statement Encyclopedia

SQL statement reference, including access, MySQL, and SQL Server

Base Creation Database

Deleting a database

drop database dbname

Backing up SQL Server

1. To create a device that backs up data

    USE masterEXEC sp_addumpdevice ‘disk‘, ‘testBack‘, ‘c:\mssql7backup\MyNwind_1.dat‘

2. Start Backup

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 (使用旧表创建新表)B：create table tab_new as select col1,col2… from tab_old definition only

Delete a new table

Add a column

Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

Add primary Key

Alter table tabname add primary key(col)

Delete primary key

Alter table tabname drop primary key(col)

Create an index

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

Delete Index

drop index idxname注：索引是不可更改的，想更改必须删除重新建。

Create a View

create view viewname as select statement

Delete a view

drop view viewname

A few simple basic SQL statements

选择：select * from table1 where 范围插入：insert into table1(field1,field2) values(value1,value2)删除：delete from table1 where 范围更新：update table1 set field1=value1 where 范围查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!排序：select * from table1 order by field1,field2 [desc]总数：select count as totalcount from table1求和：select sum(field1) as sumvalue from table1平均：select avg(field1) as avgvalue from table1最大：select max(field1) as maxvalue from table1最小：select min(field1) as minvalue from table1

Several advanced query Operation words UNION operator

EXCEPT operator

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

INTERSECT operator

Note: Several query result rows that use an operation word must be consistent.

Use outer join 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 OUT JOIN b ON a.a = b.c

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.

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.

Group: GROUP BY

A table, once the grouping is complete, you can only get group-related information after the query.
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
The fields in the SELECTE statistic function cannot be combined with ordinary fields;

Working with a database

Separate database: sp_detach_db;
Additional database: sp_attach_db followed by indicates that the append requires a full path name

How to modify the name of a database

sp_renamedb ‘old_name‘, ‘new_name‘

Promote replicated tables (copy structure only, source table name: A new table name: B) (Access available)

• SELECT * to B from a where 1<>1 (SQL Server only)
• Select top 0 * into B from a

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;

Copies of tables across databases (using absolute paths for specific data) (Access available)

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

Subquery (table name 1:a table name 2:b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

Show article, author, 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

Outer JOIN 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

Online view query (table name 1:a)

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

Between usage, between limits the bounds when querying the data range, not between does not include

select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 数值1 and 数值2

in how to use

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

Two associated tables to delete 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 )

Four-table joint-search 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 .....

Schedule five minutes Advance reminder

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

A SQL statement to take care of database paging

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

• Specific implementation:

  • About Database paging:

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

    Note: You cannot follow a variable directly after top, so there is only such special handling in the actual application. The RID is an identity column, which is beneficial if there are specific fields after top. Because this avoids the top field if it is a logical index, 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)

Top 10 Records

select top 10 * form table1 where 范围

Select all the information for the a largest record in each group of data with the same 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)

Includes all rows in TableA but not in TableB and TableC and eliminates all duplicate rows to derive a result table

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

Randomly remove 10 data

select top 10 * from tablename order by newid()

Random selection of records

select newid()

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 tablenamedelete from tablenameinsert into tablename select * from temp评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量的数据操作3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段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

List all table names in the database

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

List all the column names in the table

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

The type, vender, and PCs fields are listed in the Type field, which makes it easy to implement multiple selections, similar to 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
  PC A 1
  PC A 1
  Disc B 2
  Disc A 2
  Mobile B 3
  Mobile C 3

Initialize table table1

TRUNCATE TABLE table1

Select a record from 10 to 15

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

The use of skill 1=1,1=2, more in combination with SQL statements

"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     endelse     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 and ‘+ @strWhere

Shrinking a Database

--重建索引    DBCC REINDEX    DBCC INDEXDEFRAG--收缩数据和日志    DBCC SHRINKDB    DBCC SHRINKFILE

Compress database

dbcc shrinkdatabase(dbname)

Transfer the database to a new user with existing user rights

exec sp_change_users_login ‘update_one‘,‘newname‘,‘oldname‘go

Check the backup set

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

Repairing the database

ALTER DATABASE [dvbbs] SET SINGLE_USERGODBCC CHECKDB(‘dvbbs‘,repair_allow_data_loss) WITH TABLOCKGOALTER DATABASE [dvbbs] SET MULTI_USERGO

Log cleanup

SET NOCOUNT ondeclare @LogicalFileName sysname, @MaxMinutes INT, @NewSize intuse tablename-the database name to manipulate select @LogicalFileN Ame = ' 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) Setu P/initializedeclare @OriginalSize intSELECT @OriginalSize = size from Sysfileswhere name = @LogicalFileNameSELECT ' Origi Nal Size of ' + db_name () + ' LOG is ' + CONVERT (varchar (), @OriginalSize) + ' 8K pages or ' + CONVERT (varchar), (@Orig inalsize*8/1024) + ' MB ' from sysfileswhere name = @LogicalFileNameCREATE 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 n    Ecessary. While @MaxMinutes > DATEDIFF (MI, @StartTime, GETDATE ())--time had not expiredand @OriginalSize = (SELECT size from S Ysfiles wherE name = @LogicalFileName) and (@OriginalSize * 8/1024) > @NewSize BEGIN--Outer loop. SELECT @Counter = 0WHILE ((@Counter < @OriginalSize/16) and (@Counter < 50000)) BEGIN--Updateinsert Dummytrans VALUES (' Fill Log ') DELETE dummytransselect @Counter = @Counter + 1ENDEXEC (@TruncLog) endselect ' Final Size of ' + Db_na Me () + ' LOG is ' +convert (varchar (+), size) + ' 8K pages or ' + CONVERT (varchar ($), (size*8/1024)) + ' MB ' from Sysfiles WH ERE name = @LogicalFileNameDROP TABLE dummytransset NOCOUNT OFF

Change a table

exec sp_changeobjectowner ‘tablename‘,‘dbo‘

Store changes All tables

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch@OldOwner as NVARCHAR(128),@NewOwner as NVARCHAR(128)ASDECLARE @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 sysobjectswhere user_name(uid)[email protected]order by nameOPEN   curObjectFETCH NEXT FROM curObject INTO @Name, @OwnerWHILE(@@FETCH_STATUS=0)BEGIN     if @[email protected] begin    set @OwnerName = @OldOwner + ‘.‘ + rtrim(@Name)    exec sp_changeobjectowner @OwnerName, @NewOwnerend-- select @name,@NewOwner,@OldOwnerFETCH NEXT FROM curObject INTO @Name, @OwnerENDclose curObjectdeallocate curObjectGO

Direct loop write data in SQL Server

declare @i intset @i=1while @i<30begininsert into test (userid) values(@i)set @[email protected]+1end

Case:
As in the table below, it is required that all the failed grades in the mounting, on the basis of each increase of 0.1, make them 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 sort by last name stroke

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

Database encryption

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

Retrieving fields from a table

declare @list varchar(1000),@sql nvarchar(1000) select @[email protected]+‘,‘+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)

View hard disk Partitions

EXEC master..xp_fixeddrives

Compare the equality of a/b table

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

Kill all the event explorer processes

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

Record Search

• Start to N Records

   Select Top N * From 表

• N to M record (with primary index ID)

  Select Top M-N * From 表 Where ID in (Select Top M ID From 表) 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 40th 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 back of select top RecId from A is looked up in the datasheet, so that the order in the index is likely to be inconsistent with the data table, which results in a query that is not the intended data.

Solution Solutions

1, with order by select top RecId from A order by ricid if the field is not self-growing, a problem occurs
2, also add condition in that subquery: Select top recid from A where recid>-1

Example 2: The last record in the query table does not know how much data the table has, and the table structure.
Set @s = ' SELECT top 1 * from T where PID isn't in (select top ' + str (@count-1) + ' pid from T ') '

Print @s exec sp_executesql @s

Get all user tables in the current database

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

Get all fields of 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 = ‘表名‘)

The effect is the same in both ways

View views, stored procedures, functions related to a table

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

View all stored procedures in the current database

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

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

Querying the fields and data types of a table

select column_name,data_type from information_schema.columnswhere table_name = ‘表名‘

Data operations between different server databases

--Create a linked server exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' Remote server name or IP address ' exec sp_addlinkedsrvlogin ' itsv ', ' Fals E ', 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--delete chain when no longer in use        Connect server exec sp_dropserver ' itsv ', ' droplogins '--connection to 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)--Raw cost surface select * into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name--Import the local table into the remote table Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name ';  ' Password ', database name. dbo. Table name) Select *from Local table--Update local table updated 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 need to create a connection--first create a connection create a linked service exec sp_addlinkedserver ' itsv ', ', ' SQloledb ', ' Remote server name or IP address '--query select * FROM OPENQUERY (ITSV, ' select * from database. dbo. Table name ')             --Import the local table into the remote table Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ') SELECT * from local table--Update native surface            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 the local table into the remote table Insert OpenDataSource (' SQLOLEDB ', ' Data Source=ip/serverna Me User id= login name; password= password '). database. dbo. Table name SELECT * FROM local surface

SQL Server Basic functions

SQL Server Basic functions

String functions

Length and analytical use

• DATALENGTH (char_expr) returns a string that contains the number of characters but does not contain the following spaces
• SUBSTRING (expression,start,length) takes a substring, the subscript of the string is from "1", start is the start position, length is the string length, the actual application of Len (expression) to obtain its length
• Right (char_expr,int_expr) returns the int_expr character to the left of the string, with the opposite
• IsNull (check_expression, Replacement_value) If check_expression is empty, returns the value of Replacement_value, not empty, returns Check_ Expression character Manipulation class
• Sp_addtype self-defining type
  Example: EXEC sp_addtype birthday, datetime, ' NULL '
• SET NOCOUNT {On|off}
  Causes the returned results to contain no information about the number of rows affected by the Transact-SQL statement. If some of the statements contained in a stored procedure do not return much of the actual data, this setting can significantly improve performance because of the significant reduction in network traffic. Set NOCOUNT settings are set at execution or runtime, not at parse time.
  When SET NOCOUNT is on, no count is returned (indicating the number of rows affected by the Transact-SQL statement).
  When SET NOCOUNT is OFF, the count is returned

Common sense

• In SQL queries: the maximum number of tables or views that can be followed from: 256
• An order by is present in the SQL statement, and the query is sorted first, followed by
• In SQL, the maximum capacity for a field is 8000, and for nvarchar (4000), because nvarchar is a Unicode code.

Transferred from: http://blog.csdn.net/znyyjk/article/details/52717336

Classic SQL statement Daquan (GO)