[SQL]SQL server 查询方法代码汇总

首先第一件事就是

use 数据库名
go

打开需要查询的数据库
然后
biao=表名
ziduan=字段名

--检索表所有信息
select * from biao

--检索表中某字段
select ziduan from biao

--检索表中某字段并以其他字段标题显示
select ziduan1 AS 字段1,ziduan2 AS 字段2 from biao

--检索前n条 或前10%
select top n * from biao
select top 10 percent * from biao

--检索表中字段消除重复数据
select distinct ziduan from biao

--字段排序，升序-asc，降序-desc
select ziduan1,ziduan2 from biao order by ziduan asc

--检索1000-1500之间的记录
 select ziduan1,ziduan2 from biao where ziduan2 between 1000 and 1500

--检索多个指定id编号的记录，或者多个不需要的记录not in 
 select ziduan1,ziduan2 from biao where ziduan2 in (id1,id2,id3)

--检索信息为空的字段，或不为空的not null
 select * from biao where ziduan is null

 --查询表中包含广州的字段
 select * from biao where ziduan like '%广州%'

 --查询字段第一个字不等于'猫'且长度是二个字符的字段
 select * from biao where ziduan like '[^黑]_'

 --统计在200到3500之间的记录数和ziduan的总和
 select count(*) AS 记录数,sum(ziduan) AS 总和 from biao where biao between 200 and 3500
 

 --分类统计并进一步分类大于1500的总数
 select lieID AS 类别 , sum(lie1) AS 总数 from biao GROUP BY lieID HAVING sum(lie1)>1500

 --将查询信息放入新的表中
 select lieID,lie1,lie2 INTO xinbiao from biao

多表查询，交叉连接查询

--方法1
select biao1.lie1,biao2.lie2 from biao1 CROSS JOIN biao2
--方法2
select AA.lie1,BB.lie2 from biao1 as AA CROSS JOIN biao2 as BB
--方法3
 select AA.lie1,BB.lie2 from biao1 as AA,biao2 as BB

多表查询，内连接

--biao=b ， ziduan=zhubiao、
--方法1
select b1.z1,b2.z2 FROM b1 INNER JOIN b2 on b1.z1 = b2.z1

 --方法2
 select b1.z1,b2.z2,b3.z3 FROM b1 INNER JOIN b2 on b1.z1 = b2.z1 INNER JOIN b3 on b1.z1 = b3.z3 where z3>1500

 --简化
 select b1.z1,b2.z2,b3.z3 FROM b1,b2,b3 where b1.z1 = b2.z1 and b1.z1 = b3.z3 and z3>1500

多表查询，左外连接

  SELECT * FROM b1 LEFT OUTER JOIN b2 on b1.z1 = b2.z2

–多表查询，右外连接

 SELECT * FROM b1 RIGHT OUTER JOIN b2 on b1.z1 = b2.z2

–多表查询，完全连接

 SELECT * FROM b1 FULL OUTER JOIN b2 on b1.z1 = b2.z2

–子查询

 select * from b1 where z1=z2 and age>( select max(age) from b1 where z1='01');

打赏

微信扫一扫，打赏作者吧～