首页 > 代码库 > T-SQL简单查询语句(模糊查询)

T-SQL简单查询语句(模糊查询)

 1  T-SQL简单查询语句
 2 
 3 简单查询:
 4 
 5 1.最简单查询(查所有数据)
 6 select * from 表名; 注:* 代表所有列
 7 select * from info
 8 
 9 2.查询指定列
10 select code,name from info
11 
12 3.修改结果集的列名
13 select code as ‘代号‘,name as ‘姓名‘ from info
14 
15 4.条件查询
16 select * from info where code=‘p003‘
17 
18 5.多条件查询
19 查询info表中code为p003或者nation为n001的所有数据
20 select * from info where code=‘p003‘ or nation=‘n001‘
21 查询info表中code为p004并且nation为n001的数据
22 select * from info where code=‘p004‘ and nation=‘n001‘
23 
24 6.范围查询
25 select * from car where price>=40 and price<=60
26 select * from car where price between 40 and 60
27 
28 7.离散查询
29 查询汽车价格在(10,20,30,40,50,60)中出现的汽车信息
30 select * from car where price=10 or price=20 or price=30 or price=40 or price=50 or price=60
31 
32 select * from car where price in(10,20,30,40,50,60)
33 select * from car where price not in(10,20,30,40,50,60)
34 
35 8.模糊查询(关键字查询)
36 查询car表里面名称包含奥迪的
37 select * from car where name like ‘%奥迪%‘ %任意n个字符
38 查询car中名称第二个字符为‘马’的汽车
39 select * from car where name like ‘_马%‘ _任意一个字符

技术分享

技术分享

9.排序查询
select * from car order by price asc asc升序(省略)
select * from car order by oil desc desc降序

先按照brand升序排,再按照price降序排
select * from car order by brand,price desc

技术分享

技术分享

 1 10.去重查询
 2 select distinct brand from car
 3 
 4 11.分页查询
 5 一页显示10条 当前是第3页
 6 select * from chinastates limit 20,10
 7 
 8 一页显示m条 当前是第n页
 9 limit (n-1)*m,m
10 
11 12.聚合函数(统计函数)
12 select count(areacode) from chinastates #查询数据总条数
13 select sum(price) from car #求和
14 select avg(price) from car #求平均
15 select max(price) from car #求最大值
16 select min(price) from car #求最小值 

技术分享

技术分享

13.分组查询
查询汽车表中每个系列下有多少个汽车
select brand,count(*) from car group by brand

技术分享

 1 查询汽车表中卖的汽车数量大于1的系列 2 select brand from car group by brand having count(*)>1 

技术分享

 

T-SQL简单查询语句(模糊查询)