SQLServer常见查询

查询当前时间

SELECT GETDATE();

查询当前数据库

Select Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid);

查询前几条数据

/**
*查询前10条数据
*/
SELECT TOP 10 * FROM TABLE(表名);

去重查询

SELECT DISTINCT(字段) FROM TABLE(表名);

别名查询

/**
*AS可以省略
*/
SELECT  字段1,字段2 AS '新名字' FROM TABLE(表名)

查询不等于

SELECT * FROM TABLE WHERE 字段 !=值;
SELECT * FROM TABLE WHERE 字段 <>值;

查询在两个值之间数据

SELECT * FROM TABLE(表名) WHERE 字段>=值1 AND 字段<=值2;
select * FROM TABLE(表名) WHERE 字段 BETWEEN 值1 AND 值2;

查询条件或

SELECT * FROM TABLE(表名) WHERE 字段=值1 OR 字段=值2;
SELECT * FROM TABLE(表名) WHERE 字段 IN(值1,值2);

模糊匹配查询

SELECT * FROM TABLE WHERE 字段 LIKE '%值%';//包含
SELECT * FROM TABLE WHERE 字段 LIKE '值%';//以某个字符开始匹配
SELECT * FROM TABLE WHERE 字段 LIKE '_值%';//以第二个字符为开始匹配

查询为空

SELECT * FROM TABLE WHERE 字段 IS NULL;//为空
SELECT * FROM TABLE WHERE 字段 IS NOT NULL;//不为空

排序条件查询

SELECT * FROM TABLE ORDER BY 字段 ASC(默认);//升序
SELECT * FROM TABLE ORDER BY 字段 DESC;//降序
SELECT * FROM TABLE ORDER BY 字段1 DESC,字段2 DESC;//多字段排序

聚合查询

SELECT COUNT(*) FROM TABLE;//总行数
SELECT COUNT(字段) FROM TABLE;//字段不为NULL的行数
SELECT SUM(价格字段) FROM TABLE;//字段值总和
SELECT AVG(价格字段) FROM TABLE;//字段值平均值
SELECT MAX(价格字段) AS '最大价格' FROM TABLE;//字段最大值
SELECT MIN (价格字段) AS '最小价格' FROM TABLE;//字段最小值

分组查询

SELECT 字段1,COUNT(0) FROM TABLE WHERE 字段2>值(条件) GROUP BY 字段1;
SELECT 字段1,字段2... FROM TABLE GROUP BY 字段1,字段2... HAVING COUNT(字段)条件;