SQL数据库基本操作学习

数据查询操作

1、读取最新的10条数据

select top 10 * from [database].[dbo].[tablename] order by columnname desc

2、删除数据表，删除数据较慢，可以恢复

Delete from [basename].[dbo].[tablename]

3、删除数据表的所有数据，删除数据速度快，数据不可恢复

truncate table [basename].[dbo].[tablename]

4、查询满足条件的数据

select columnname from [basename].[dbo].[tablename] where columnname = ‘’

5、数据库操作语句比较

delete from tablename where olunmn =' '(直接删除)

delete from tablename where Id in (select top 100 Id from tablename) and  colunmn =' '（通过主键查询范围再删除）

6、获取SQL语句执行的时间

declare @start_date datetime
declare @end_date datetime
select @start_date = getdate()
DELETE FROM [DB_JPFBGAcquire].[dbo].[SensorData]
      WHERE  Id  in (select top  300 Id  from [DB_JPFBGAcquire].[dbo].[SensorData]) and  Tag =1

select @end_date = getdate()
select[花费的时间]=datediff(ms,@start_date,@end_date

7、查询当前数据表的行数

SELECT COUNT(*)    FROM [SLDataConvertor].[dbo].SensorData where Tag=0

8、创建简单索引和创建唯一索引

create index 索引名称 on tablename (colunmname)//创建普通索引
create unique index 索引名称 on tablename (columnname)//创建唯一索引