SQL语句

全部是在SQL Server 2000中测试!

0

查询数据库时间

SELECT getdate() AS sj FROM userinfo

 

1

判断数据库中的表是否存在

select count(*) from sysobjects where [name]='result'

2

查询某表中的字段

 select * from syscolumns where id=(select id from sysobjects where [name]='userinfo') 

或

select * from syscolumns as a join sysobjects as b on a.id=b.id where b.[name]='userinfo'

 

3

我想把edepartGeneral的username和department设置成userinfo中的username和department,edepartGeneral中的operator和userinfo中的name是对应的.
查询语句

select a.username,a.department from edepartGeneral as a join userinfo as b on b.[name]=a.operator

设置语句

update a set a.username=b.username,a.department=b.departmentfrom edepartGeneral as ainner join userinfoas bon a.operator=b.[name]

4

 UNION和ORDER BY共存

SELECT * FROM (SELECT ndate, username FROM edepartGeneral GROUP BY ndate, username UNION SELECT ndate, username FROM edepartDigit GROUP BY ndate, username )as a ORDER BY username

其中as a非常重要,没有则报错;而且Group by 中的项要和select一一对应

5

1.GROUP BY: 以column1 为一组计算column2 的平均值必须和AVG、SUM 等整合性查询的关键字
一起使用。
2.HAVING : 必须和GROUP BY 一起使用作为整合性的限制。

 

 SELECT department AS 部门,fType AS 作业类型,SUM(ncount) AS 数量  FROM edepartGeneral where ndate between '2011-08-01' and '2011-08-31' GROUP BY department,fType

//一下having的用法摘自W3school

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SQL HAVING 语法

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value

SQL HAVING 实例

我们拥有下面这个 "Orders" 表：

O_IdOrderDateOrderPriceCustomer12008/12/291000Bush22008/11/231600Carter32008/10/05700Bush42008/09/28300Bush52008/08/062000Adams62008/07/21100Carter

现在，我们希望查找订单总金额少于 2000 的客户。

我们使用如下 SQL 语句：

SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerHAVING SUM(OrderPrice)<2000

结果集类似：

CustomerSUM(OrderPrice)Carter1700

现在我们希望查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。

我们在 SQL 语句中增加了一个普通的 WHERE 子句：

SELECT Customer,SUM(OrderPrice) FROM OrdersWHERE Customer='Bush' OR Customer='Adams'GROUP BY CustomerHAVING SUM(OrderPrice)>1500

结果集：

CustomerSUM(OrderPrice)Bush2000Adams2000

 

6

EXISTS 与IN的区别

in只能单一的查询一个条件,如果要查询多个就要另加in语句

单个条件

----------------------

exists

select  * from edepartGeneral as a where exists (select * from edepartdigit as b where a.operator=b.operator)

in

select  * from edepartGeneral where operator in (select operator from edepartdigit )

多个条件

----------------------

exists

select  * from edepartGeneral as a where exists (select * from edepartdigit as b where a.operator=b.operator and a.ndate=b.ndate)

in

select  * from edepartGeneral where operator in (select operator from edepartdigit ) and ndate in (select ndate from edepartdigit)

 

(未完待续...)