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

时间:2020-04-26作者:klpeng分类:IT综合浏览:1380评论:0

首先第一件事就是

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');
打赏
文章版权声明:除非注明,否则均为彭超的博客原创文章,转载或复制请以超链接形式并注明出处。
相关推荐

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

猜你喜欢