SQL SERVER INSERT、UPDATE 、DELETE、SELECT增、改、删、查

川长思鸟来 2022-07-24 10:14 163阅读 0赞

1、insert插入一条记录：  
![这里写图片描述][20160519220211240]

2、insert插入多条记录：  
![这里写图片描述][20160519220230972]

3、update指定条件更新记录：  
![这里写图片描述][20160519220733305]

4、delete指定条件删除记录：

delete * from person where id=2

5、delete删除所有记录：

delete from person

6、select查询表中所有数据：

select * from person

7、select指定条件查询：

select * from person where id=2

8、select连接字符串并起别名：

select FirstName + ' ' + LastName as 姓名 from person where id=2

![这里写图片描述][20160519221733806]

9、order by 根据id降序desc排序：

select * from person order by id desc

![这里写图片描述][20160519222305673]

10、查询表中所有id不为1的记录：

select * from person where not id=1

![这里写图片描述][20160519222654078]

11、带AND的多条件查询：

select * from person where id=1 and FirstName='a1'

![这里写图片描述][20160519223032416]

12、带OR的多条件查询：

select * from person where id=1 or FirstName='a1'

![这里写图片描述][20160519223234424]

13、使用LIKE进行匹配查询：

%----零个或多个字符；               select * from person where FirstName like 'a%'
    _-----一个字符；                 select * from person where FirstName like 'a_'
    []----指定范围的任意单个字符；      select * from person where FirstName like 'a[a-e]'
                                    select * from person where FirstName like 'a[bcd]'
    [^]--不属于指定范围的任意单个字符；  select * from person where FirstName like 'a[^bcd]'
    []和%结合：  select * from person where FirstName like '[bcd]%'
                select * from person where FirstName like '[abcd]%'

![这里写图片描述][20160519225019194]

14、使用BETWEEN AND查询记录：

select * from person where id between 1 and 2

![这里写图片描述][20160519224646767]

[20160519220211240]: /images/20220722/f180e2c68c76486cb92fc96bd9b49d60.png
[20160519220230972]: /images/20220722/a3b70bc1a7ba45a8856c073ddc29a664.png
[20160519220733305]: /images/20220722/3a6154417a3a43af977e33b6386e90b4.png
[20160519221733806]: /images/20220722/1b2092e4d90144a1bd711fb6d2af0f88.png
[20160519222305673]: /images/20220722/bd92bf5120444bf0a1de42ab3c6a088b.png
[20160519222654078]: /images/20220722/b471190347fa4ab0bdd05db595b96b74.png
[20160519223032416]: /images/20220722/59fd97fa53fe42d5a6589156842b19de.png
[20160519223234424]: /images/20220722/c1f9e9ca9ab94b2caa54373eaa1d6752.png
[20160519225019194]: /images/20220722/915e386bcead446ebe636f69bcc12a2d.png
[20160519224646767]: /images/20220722/4a54762b9da34c8eabed29bef89a64a9.png