Shulou(Shulou.com)05/31 Report--

This article mainly explains the "commonly used SQL query sentence Daquan", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "commonly used SQL query sentence Daquan" bar!

I. Foundation

1. Description: create a database

CREATE DATABASE database-name

2. Description: delete the database

Drop database dbname

3. Description: backup sql server

-create a device for backing up 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],..)

Create a new table based on 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 the new table

Drop table tabname

6. Description: add a column

Alter table tabname add column col type

Note: columns cannot be deleted after they are added. The data type cannot be changed after the column is added in DB2, and the only thing that can change is to increase 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: create an index: create [unique] index idxname on tabname (col … (.)

Delete index: drop index idxname

Note: the index is immutable and must be deleted and rebuilt if you want to change it.

9. Description: create view: create view viewname as select statement

Delete View: drop view viewname

10. Description: a few simple basic sql statements

Select: select * from table1 where range

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

Deleting: delete from table1 where ran

Updating: update table1 set field1=value1 where ran

Look up: select * from table1 where field1 like'% value1%'-like's syntax is very exquisite, look up 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

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 lines 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. When ALL is used with EXCEPT (EXCEPT 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 rows that use operands must be consistent.

12. Description: use external connections

A, left (outer) join:

Left outer join (left join): the result set includes the matching rows of the join table, as well as 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 the matching join rows of the join table and all rows of the right join table.

C:full/cross (outer) join:

Full outer join: includes not only the matching rows of the symbolic join table, but also all records in the two join tables.

12. Group: Group by:

A table, once the grouping is completed, only the group-related information can be obtained after the query.

Group related information: (statistics) criteria for count,sum,max,min,avg grouping)

When grouping in SQLServer: fields of type text,ntext,image cannot be used as grouping basis

Fields in selecte statistics functions cannot be put together with ordinary fields

13. Operate the database:

Detach database: sp_detach_db; attach database: sp_attach_db followed by indicates that attaching requires a full pathname

14. How to modify the name of a database:

Sp_renamedb 'old_name',' new_name'

II. Promotion

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

Law 1: select * into b from a where 11 (for SQlServer only)

Method 2: select top 0 * into b from a

2. Description: copy table (copy data, source table name: a target table name: B) (available for Access)

Insert into b (a, b, c) select d from b

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

Insert into b (a, b, c) select djiggy e from b in 'specific database' where condition

Example:.. from b in'"& Server.MapPath (". ") &"\ data.mdb "&" 'where.. "

4. Description: sub-query (table name 1VLA, table name 2RB)

Select a dagger bjorc from a where an IN (select d from b) or: select a recital b recital c from a where an IN (1m 2pm 3)

5. Description: displays the article, author and last reply time

Select a.title adddate from table where table.title=a.title a.username from table a b.adddate, (select max (title) adddate from table where table.title=a.title) b

6. Description: external join query (table name 1VR a, table name 2RB)

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 1pora)

Select * from (SELECT a FROM c FROM a) T where t.a > 1

8. Description: the use of between. Boundary values are included when between limits the scope of query data, while not between does not include

Select * from table1 where time between time1 and time2

Select a dint bpenc, from table1 where a not between numeric 1 and numeric 2

9. Description: how to use in

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

10. Description: two associated tables to delete information that has not been found in the secondary table in the primary table

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

11. Description: joint check of the four tables:

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. Note: five minutes in advance of the schedule

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

13. Description: a sql statement completes 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 int,@end int

@ sql nvarchar

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 this is the only way to deal with it in practice. Rid is an identity column, which is very beneficial if there are specific fields after top. Because this can avoid the inconsistency in the actual table after the result of the query if the field of top is logically indexed (the data in the logical index may be inconsistent with that in the data table, and if the field in the query is in the index, the index will be queried first)

14. Description: the first 10 records

Select top 10 * form table1 where range

15. Description: select all the information corresponding to the largest record of an in each set of data with the same b value (similar usage can be used for forum monthly rankings, monthly best-selling product analysis, ranking by subject scores, etc.).

Select a dint bjorn c from tablename ta where a = (select max (a) from tablename tb where tb.b=ta.b)

Description: derives a result table by including all rows in TableA but not in TableB and TableC and eliminating all duplicate rows

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

17. Description: 10 pieces of data are randomly selected

Select top 10 * from tablename order by newid ()

18. Description: randomly select 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 a large amount of data, which is not suitable for large capacity but data operation.

3), for example, if you import data in an external table, for some reasons, only part of the data is imported for the first time, but it is difficult to determine the specific location, so it is only the next time all of them are imported, which results in a lot of duplicate fields. How to delete duplicate fields

Alter table tablename

-add a self-incrementing column

Add column_b int identity (1 dint 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

Description: list all the table names in the database

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

21. Description: list all the column names in the table

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

22, description: type, vender, pcs fields are listed and arranged in type fields. Case can easily achieve multiple choices, 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

Display the results:

Type vender pcs

Computer A1

Computer A1

CD B 2

CD A 2

Mobile phone B3

Mobile phone C 3

23. Description: initialization 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_ alias order by id desc

III. Skills

1. The use of SQL sentences is more frequently used in the combination of 1Q sentences.

"where 1" means to select all "where 1" and none of them.

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 it directly.

Mistake! No catalog entries were found.

Set @ strSQL = 'select count (*) as Total from [' + @ tblName +'] where 1 stable 1 + @ strWhere 2, shrink the database

-- rebuild the index

DBCC REINDEX

DBCC INDEXDEFRAG

-shrinking data and logs

DBCC SHRINKDB

DBCC SHRINKFILE

3. Compress the database

Dbcc shrinkdatabase (dbname)

4. Transfer the database to the 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 cleanup

SET NOCOUNT ON

DECLARE @ LogicalFileName sysname

@ MaxMinutes INT

@ NewSize INT

USE tablename-the name of the database to be operated 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、说明：更改某个表 exec sp_changeobjectowner 'tablename','dbo' 9、存储更改全部表 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、SQL SERVER中直接循环写入数据 declare @i int set @i=1 while @i=0 10：获取某一个表的所有字段 select name from syscolumns where id=object_id('表名') select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名') 两种方式的效果相同 11：查看与某一个表相关的视图、存储过程、函数 select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%' 12：查看当前数据库中所有存储过程 select name as 存储过程名称 from sysobjects where xtype='P' 13：查询用户创建的所有数据库 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 14：查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = '表名' 15：不同服务器数据库之间的数据操作 --创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例 select * from ITSV.数据库名.dbo.表名 --导入示例 select * into 表 from ITSV.数据库名.dbo.表名 --以后不再使用时删除链接服务器 exec sp_dropserver 'ITSV ', 'droplogins ' --连接远程/局域网数据(openrowset/openquery/opendatasource) --1、openrowset --查询示例 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --生成本地表 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --把本地表导入远程表 insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) select *from 本地表 --更新本地表 update b set b.列A=a.列A from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b on a.column1=b.column1 --openquery用法需要创建一个连接 --首先创建一个连接创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' --查询 select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') --把本地表导入远程表 insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') select * from 本地表 --更新本地表 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 --把本地表导入远程表 insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名 select * from 本地表 SQL Server基本函数 SQL Server基本函数 1.字符串函数 长度与分析用 1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格 2,substring(expression,start,length) 取子串，字符串的下标是从"1"，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度 3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用left于之相反 4,isnull( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类 5,Sp_addtype 自定義數據類型 例如：EXEC sp_addtype birthday, datetime, 'NULL' 6,set nocount {on|off} 使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。 SET NOCOUNT 为 OFF 时，返回计数 常识 在SQL查询中：from后最多可以跟多少张表或视图：256 在SQL语句中出现 Order by,查询时，先排序，后取 在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。 SQLServer2000同步复制技术实现步骤 一、 预备工作 1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户 --管理工具 --计算机管理 --用户和组 --右键用户 --新建用户 --建立一个隶属于administrator组的登陆windows的用户（SynUser） 2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作: 我的电脑--D:\ 新建一个目录,名为: PUB --右键这个新建的目录 --属性--共享 --选择"共享该文件夹" --通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限 --确定 3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置) 开始--程序--管理工具--服务 --右键SQLSERVERAGENT --属性--登陆--选择"此账户" --输入或者选择第一步中创建的windows登录用户名（SynUser） --"密码"中输入该用户的密码 4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置) 企业管理器 --右键SQL实例--属性 --安全性--身份验证 --选择"SQL Server 和 Windows" --确定 5.在发布服务器和订阅服务器上互相注册 企业管理器 --右键SQL Server组 --新建SQL Server注册... --下一步--可用的服务器中,输入你要注册的远程服务器名 --添加 --下一步--连接使用,选择第二个"SQL Server身份验证" --下一步--输入用户名和密码（SynUser） --下一步--选择SQL Server组,也可以创建一个新组 --下一步--完成 6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到） (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP) 开始--程序--Microsoft SQL Server--客户端网络实用工具 --别名--添加 --网络库选择"tcp/ip"--服务器别名输入SQL服务器名 --连接参数--服务器名称中输入SQL服务器ip地址 --如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号 二、 正式配置 1、配置发布服务器 打开企业管理器，在发布服务器（B、C、D）上执行以下步骤: (1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导 (2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己） (3) [下一步] 设置快照文件夹 采用默认\\servername\Pub (4) [下一步] 自定义配置 可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置 否,使用下列默认设置（推荐） (5) [下一步] 设置分发数据库名称和位置 采用默认值 (6) [下一步] 启用发布服务器 选择作为发布的服务器 (7) [下一步] 选择需要发布的数据库和发布类型 (8) [下一步] 选择注册订阅服务器 (9) [下一步] 完成配置 2、创建出版物 发布服务器B、C、D上 (1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令 (2)选择要创建出版物的数据库，然后单击[创建发布] (3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助) (4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型, SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。 但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器 (5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表 注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表 (6)选择发布名称和描述 (7)自定义发布属性 向导提供的选择: 是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性 否 根据指定方式创建发布 （建议采用自定义的方式） (8)[下一步] 选择筛选发布的方式 (9)[下一步] 可以选择是否允许匿名订阅 1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器 方法: [工具]->

[copy]-> [configure properties for publication, subscriber and distribution]-> add in [subscriber]

Otherwise, the prompt that appears when requesting a subscription at the subscriber: changing the publication does not allow anonymous subscription.

If anonymous subscriptions are still required, use the following solutions

[enterprise Manager]-> [copy]-> [publication]-> [Properties]-> [subscription options] Select to allow anonymous request subscriptions

2) if you choose anonymous subscription, the above prompt will not appear when configuring the subscriber

(10) [next] set Snapshot Agent schedule

(11) [next] complete the configuration

When the publication is created, the database that creates the publication becomes a shared database.

Have data

Srv1. Library name.. author has a field: id,name,phone

Srv2. Library name.. author has a field: id,name,telphone,adress

Request:

Srv1. If the record is added to the library name... author, srv1. Library name... author record increased

Srv1. If the phone field of the library name.. author is updated, then srv1. Update the corresponding field telphone of the library name.. author

-- * /

-- rough processing steps

-1. Create a connection server on srv1 to operate srv2 in srv1 for synchronization

Sql instance name or ip' of exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2

Exec sp_addlinkedsrvlogin 'srv2','false',null,' username', 'password'

Go

-- 2. On both srv1 and srv2 computers, start msdtc (distributed transaction service) and set it to start automatically

. My computer-- Control Panel-- Administrative tools-- Services-- right-click the Distributed Transaction Coordinator-- property-- start-- and set the startup type to start automatically.

Go

-- and then create a job to call the above synchronous processing stored procedure.

Enterprise Manager

-- Management

-- SQL Server Agent

-- right-click the assignment

-- New Job

-- enter the job name in the "General" item

-- "steps" item

-- New

-- enter a step name in the step name

-- Select "Transact-SQL script (TSQL)" in "Type"

-- "Database" selects the database to execute the command

Enter the statement to be executed in the "Command": exec p_process

-- OK

-- scheduling item

-- New schedule

-- enter the scheduling name in the name

-- Select your job execution schedule in "scheduling Type"

-- if you choose "repeat"

Click "change" to set your schedule

Then start the SQL Agent service and set it to start automatically, otherwise your job will not be executed

Setting method:

My computer-- Control Panel-- Management tools-- Services-- right SQLSERVERAGENT-- properties-- Startup type-- Select "Auto start"-- OK.

-3. Method 2 for realizing synchronization processing, timing synchronization

Create the following synchronized stored procedure in srv1

Create proc p_process

As

-- updating modified data

Update b set name=i.name,telphone=i.telphone

From srv2. Library name. Dbo.author bjorn author I

Where b.id=i.id and

(b.name i.name or b.telphone i.telphone)

-- insert new data

Insert srv2. Library name .dbo.author (id,name,telphone)

Select id,name,telphone from author i

Where not exists (

Select * from srv2. Library name. Dbo.author where id=i.id)

-- Delete deleted data (if necessary)

Delete b

From srv2. Library name. Dbo.author b

Where not exists (

Select * from author where id=b.id)

Go

Keyword method of SQL query statement

Distinct keyword

Displays a list of commodity names, prices and categories that are not duplicated.

Select distinct ware_name,price from t_ware

Use calculated columns

Inquire about the prices of all goods after a 20% increase.

Select ware_id,ware_name,price*1.2 from t_ware

Alias of the column

A) do not use as

Select ware_id,ware_name,price*1.2 as price_raise from t_ware

B) use as

Select ware_id,ware_name,price*1.2 price_raise from t_ware

Use logical expressions

A) not

Goods showing that the price of the commodity is not more than 100

Select ware_id,ware_name,price,category_id from t_ware where not price > 100

B) and

Displays the goods whose price is greater than 100 and the commodity category number is 5

Select ware_id,ware_name,price,category_id from t_ware where not price > 100

C) or

Display goods with category number 5 or 6 or 7

Select ware_id,ware_name,price,category_id from t_ware where category_id=5 or category_id=6 or category_id=7

Use the between keyword

Show that the price of goods is between 200 yuan and 1000 yuan (pay attention, is it a half-open range or a closed range? )

Select ware_id,ware_name,price,category_id from t_ware where price between 200 and 1000

Use the in keyword

It shows that the category of goods is 5, 6, 7 and the price is not less than 200 yuan.

Select ware_id,ware_name,price,category_id from t_ware where category_id in (5, 6, 7) and price > = 200

Fuzzy query using like clause

A)% (percent sign) represents 0 to n arbitrary characters

Select ware_id,ware_name,price,category_id from t_ware where ware_name like'% cotton%'

B) _ (underscore) represents a single arbitrary character

Select ware_id,ware_name,price,category_id from t_ware where ware_name like'% long sleeved T-shirt%'

The use of escape character escape

Select ware_id,ware_name,price,category_id from t_ware where ware_name like'%% 'escape'\'

Use order by to sort data

Select * from t_ware_category where parent_id=0 order by seq

Select * from t_ware_category where parent_id=0 order by seq asc

Select * from t_ware_category where parent_id=0 order by seq desc

Rownum

A) query the first 20 commodity records

Select ware_id,ware_name,price from t_ware where rownum20

The WHERE clause can include various conditional operators:

Comparison operators (size comparison): >, > =, =, = 10 AND age, > =, 20

The WHERE clause can include various conditional operators:

Comparison operators (size comparison): >, > =, =, = 10 AND age, > =, 70)-(begin-end is equivalent to {} in java)

Begin

Print 'the written examination results of this class are excellent. The top three scores are:'

Select top 3 * from stuMarks order by writtenExam desc

End

Else

Begin

Print 'the results of the written examination in this class are poor, and the scores of the last three are:'

Select top 3 * from stuMarks order by writtenExam [asc]

End

-while loop statement-

Declare @ n int

While (1x 1)-the condition is always true.

Begin

Select @ n=count (*) from stuMarks where writtenExam0)

Update stuMarks set writtenExam=writtenExam+2-2 points per person

Else

Break-exit the loop

End

The results of print 'plus points are as follows:'

Select * from stuMarks

-case multi-branch statement-

Select * from stuMarks-Raw data

The scores of print 'ABCDE five are as follows:'

Select stuNo, score = case

When writtenExam80)

Begin

Print: some people in this class scored more than 80 points in the written test, and each person only added 2 points. The scores after the extra points are as follows:'

Update stuMarks set writtenExam=writtenExam+2

Select * from stuMarks

End

Else

Begin

Print: the score of the unattended written examination in this class is higher than 80 points, and each person can add 5 points. The scores after the extra points are as follows:'

Update stuMarks set writtenExam=writtenExam+5

Select * from stuMarks

End

Go

Not's subquery:

If not exists (select * from stuMarks where writtenExam > 60 and labExam > 60)

Begin

Print: no one in this class has passed the exam, and the questions are too difficult. Each person adds 3 points. The results after adding points are as follows:

Update stuMarks set writtenExam=writtenExam+3,labExam=labExam+3

Select * from stuMarks

End

Else

Begin

Print's exam scores are average, each person only adds one point, and the scores after the extra points are as follows:'

Update stuMarks set writtenExam=writtenExam+1,labExam=labExam+1

Select * from stuMarks

End

Thank you for your reading, the above is the "commonly used SQL query sentence Daquan" content, after the study of this article, I believe you on the commonly used SQL query sentence Daquan this problem has a deeper understanding, the specific use of the need for you to practice and verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.