学步园

全部是在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

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

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)

(未完待续...)