数据库简单操作练习



---数据库的简单操作
--查询表数据
select * from dbo.Table_V1
--去除表列中的重复项(DISTINCT)
SELECT DISTINCT companyname FROM Table_V1
--where 条件 <> !=
select * from Table_V1 where companyid='11'
select * from Table_V1 where companyid!='33'
select * from Table_V1 where paymentamount>111
select * from Table_V1 where paymentamount<444
--and  or
select * from Table_V1 where companyid='11' and companyname=N'公司名'
select * from Table_V1 where companyid='11' or companyname=N'公司名称'
--order by (asc 升序 desc 倒叙)
select companyid,companyname from Table_V1 order by companyid  desc
--添加数据（1）
insert into Table_V1
values(1,'666','245','2017-12-12','2018-11-11','4256561987656X','33')
--添加数据（2）
insert into Table_V1 (ID,CompanyID,CompanyName,starttime,endtime,idnumber,paymentamount)
values(98,'888','zi','2017-12-12','2018-11-11','4256561987656X','33')
---修改表数据
select * from Table_V1
UPDATE Table_V1 SET ID = '5' WHERE ID = '98'
update Table_V1 set id='1' where ID='6' and CompanyID='11'

update Table_V1 set CompanyName=N'上海一大' ,paymentamount=N'888' where ID='5' and CompanyID='888'

---删除表数据
select * from Table_V4
delete from dbo.Table_V4

--查询表中的前2行数据 50%数据
select top 2 * from  dbo.Table_V1
select top 50 PERCENT  * from Table_V1
--模糊查询
select * from Table_V1 where  CompanyName  like '2%'
select * from Table_V1 where  CompanyName  like '%5'
select * from Table_V1 where  CompanyName  like N'公司_'
select * from Table_V1 where  CompanyName  like N'[公]%'
select * from Table_V1 where  CompanyName  like N'[!公]%'
--选取CompanyID 为’11‘和’12‘的数据
select * from Table_V1 where  CompanyID  in('12','11')

详见http://www.w3school.com.cn/sql/sql_between.asp