一、基础的CRUD语句
1.插入
insert into dbo.new_product(new_name,new_price) values ('产品1',122)
2.查询
select new_name from dbo.new_contractBase where id = 1
3.修改
update dbo.new_contractBase set new_name = '合同1' where new_name = '合同壹'
4.删除
delete from dbo.new_contractBase where new_name = '合同3'
二、进阶查询语句
1.别名
---取别名,as 关键字可以省略,字符串的 单引号'' 也可以省略
select
new_name as 名称,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase
2.条件查询
---where 关键字
select
new_name as 名称,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase where new_name = '合同1'
3.范围查询
---关键字 and | or | between
举例1:select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase where new_lowestamount1 > 45500 and new_lowestamount1 < 55000
举例2:select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase where new_lowestamount1 < 45600 or new_lowestamount1 > 55000
举例3: --注意:使用between关键字,必须遵守”从小到大“的顺序
select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase where new_lowestamount1 between 45500 and 55500
4.null判断
select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase where new_fax1 is null -----这句才是重点
5.查询前多少行|按比例查询结果
--查询前多少行-- 使用 top 关键字,后面的数字代表:要查询的行数
select top 2
new_name as 名称,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase
--按比例查询结果-- 使用 top 和 percent 关键字,括号里面的数字代表要查询的比例
select top (100) percent
new_name as 名称,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase
6. case when查询
举例1:---指定 "范围"
select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号' ,
case when new_lowestamount1<45600 then '较低总金额' -- 区别处 case 和 when 之间没有字段
when new_lowestamount1>45600 and new_lowestamount1<46800 then '中等总金额'
else '高等总金额'
end as 分类
from dbo.new_contractBase
举例2:---指定 "具体值"
select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号' ,
case new_address when '上海市青浦区' then '带区的详细地址' -- 区别处 case 和 when 之间有字段
when '上海市' then '不带区的省略地址'
else '地址'
end as 地址分类
from dbo.new_contractBase order by 地址分类 desc
7.in查询(指定范围)
select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase where new_name in ('合同1','合同2')
8.like查询(模糊查询)
---注意1:必须和通配符 "%" 一起使用
---注意2:"%" 放在哪边,就代表哪边有值或无值;如果有值,必须加上 "%"
select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase where new_name like '%同%'
9.with关键字查询
--- 相当于 sql 查询语句中的 sql片段
with tt as (select
new_name as 名称,
new_lowestamount1 底价总金额,
new_address 地址,
new_fax2 '传真号分机号',
new_fax1 as '传真号'
from dbo.new_contractBase where new_name like '%同%')
select * from tt -- 将tt作为一个表来使用