SQL Server语法学习笔记

一、基础的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作为一个表来使用