黑色行动僵尸模式:在asp中如何为一个表添加一个字段

set cn=server.CreateObject("adodb.connection")
cnstr="driver={microsoft access driver (*.mdb)};dbq="&server.MapPath("test.mdb")
cn.connectionstring=cnstr
cn.open
sql="ALTER TABLE test_tab add pass char[(字段最大长度)]"
'字长可选，默认值为255
cn.Execute sql
'
完整语法:
ALTER TABLE table {ADD {COLUMN field type [(size)] [NOT NULL]
[CONSTRAINT index] | CONSTRAINT multifieldindex} | DROP {COLUMN
field I CONSTRAINT indexname} }
其它自便吧~~~

INSERT INTO 表名 ("字段") VALUES ("值")
------------
简单数据操纵语言：

SELECT
INSERT DELETE UPDATE

*******************************************************************/

/*******************************************************************
简单查询:

SELECT语句的基本用法
*
定义列名
IDENTITYCOL

TOP
WITH TIES

WHERE 子句
比较操作符:
= <> != < > >= <= !> !<
布尔操作符:
NOT AND OR
谓词：
IN 、 BETWEEN AND
谓词往往不是必需的，它们可以由一般的运算组合代替，但能使语意更清晰
DISTINCT
DISTINCT 必需在所有字段前
作用于所有列，DISTINCT将显示所有列的组合各不相同的行
IS NULL ISNULL()
LIKE % _ [] ^ ESCAPE-->'[_]' = '!_'ESCAPE'!'

ORDER BY 子句
ASC DESC

聚集函数：MIN、MAX、SUM、AVG、COUNT

GROUP BY 子句
分类汇总
出现在查询的SELECT列表中的每一列都必须同时出现在GROUP BY的子句中，对于常量和作为聚集函数一部分的列来说，不受次限制约束。
GROUP BY子句中的表名序列不需要与SELECT列表中的表名序列相对应

HAVING 子句
与WHERE类似，但搭配的对象不同
聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中
HAVING子句与WHERE子句的区别：三条.....

小结：
WHERE子句用于对每一条记录做筛选，GROUP BY子句用于多条同类记录作统计。

COMPUTE 子句
COMPUTE 与 COMPUTE BY
COMPUTE 子句中的所有列都必须出现在SELECT 列表中
COMPUTE BY 子句中的每一列都必须出现在ORDER BY 子句中
COMPUTE 和 ORDER BY 子句中列的顺序必须一致

计算列
用来创建计算列的列必须是属于同一张表中的
不能将DEFAULT约束附加给计算列
计算列中不能包含子查询

SELECT INTO 建表
不允许和COMPUTE 子句一起使用，因为COMPUTE 子句的结果不是一张表。

补充：临时表
临时表存储在系统数据库tempdb中
临时表会被系统隐式地丢弃

*******************************************************************/
SELECT location ,department_name FROM department

SELECT * FROM department

SELECT department_name AS '部门名称',location '地点' FROM department

-----------------------------------------------------------------
SELECT TOP 3 * FROM department

-----------------------------------------------------------------
SELECT location FROM department
WHERE department_name='学术部'

SELECT * FROM employee
WHERE age<25

--查询斯瑞年轻的员工（女的25以下算年轻，男的30以下算年轻）
SELECT * FROM employee
WHERE (age<25 AND sex='女')
OR (age<30 AND sex='男')--注意运算顺序:NOT>AND>OR

SELECT * FROM employee
WHERE department_id IN('002','005')--NOT IN

SELECT * FROM employee
WHERE age BETWEEN 20 AND 40--NOT BETWEEN

--查询斯瑞有哪些部门（不包括暂时没有员工的部门）
SELECT DISTINCT department_id FROM employee

SELECT * FROM employee
WHERE department_id IS NULL--IS NOT NULL

SELECT employee_id,ISNULL(department_id,'department unknown') AS dep FROM employee --注意ISNULL中参数的类型

-----------------------------------------------------------------
SELECT * FROM employee
ORDER BY age DESC --默认ASC

--思考：查找年龄最大的员工
SELECT TOP 1 employee_name FROM employee
ORDER BY age DESC

SELECT * FROM teach
ORDER BY employee_id ,course_id DESC

SELECT * FROM teach
ORDER BY 1,2 DESC

-----------------------------------------------------------------
--最小员工年龄
SELECT MIN(age) as min_age FROM employee --注意别名

--每个部门的人数
SELECT department_id,COUNT(*)dep_count FROM employee
GROUP BY department_id

--哪些部门只有男生或女生
SELECT department_id,COUNT(DISTINCT sex)dep_count FROM employee --消除重复
GROUP BY department_id

--每个部门的平均年龄
SELECT department_id,AVG(age)min_age FROM employee
GROUP BY department_id

--每位老师所带的课程数
SELECT employee_id,COUNT(*)teach_count FROM teach
GROUP BY employee_id

--每门课有几个老师在带
SELECT course_id,COUNT(distinct employee_id)teach_count FROM teach
GROUP BY course_id

--每位老师带过几个班同一个课
SELECT employee_id,course_id,count(class) FROM teach
GROUP BY employee_id,course_id

-----------------------------------------------------------------
--学术部的平均年龄
SELECT department_id,AVG(age)min_age FROM employee
GROUP BY department_id
HAVING department_id='005'

--平均年龄小于30岁的有哪几个部门
SELECT department_id,AVG(age)min_age FROM employee --HAVING与WHERE 用处的区别
GROUP BY department_id
HAVING AVG(age)<30

--SEA02 有几个老师在带
SELECT course_id,COUNT(*)teach_count FROM teach
GROUP BY course_id
HAVING course_id='SEA02'

--第一学期的几门课程有几个老师在带
SELECT course_id,COUNT(*)teach_count FROM teach
GROUP BY course_id
HAVING course_id LIKE 'SEA__'

-----------------------------------------------------------------
--查询每个员工的信息 并计算员工的平均工资
SELECT AVG(pay) FROM employee

SELECT * FROM employee
COMPUTE AVG(pay)

--查询每个员工的信息 并计算每个部门员工的平均工资
SELECT department_id,avg(pay) FROM employee
GROUP BY department_id

SELECT * FROM employee
ORDER BY department_id
COMPUTE AVG(pay) BY department_id

--思考：查询每个员工的信息 并计算每个部门的平均年龄
--扩展：一个SELECT 中可以包含多个COMPUTE 子句，一个COMPUTE 子句中又可以有多个聚集函数
SELECT * FROM employee
ORDER BY department_id
COMPUTE AVG(pay),SUM(pay) ,AVG(age) BY department_id
COMPUTE AVG(pay)

--注意：COMPUTE使用中需要注意的几点
--查询每位老师所带的课程，并计算出相应的课程数
SELECT employee_id,COUNT(*)teach_count FROM teach
GROUP BY employee_id

SELECT * FROM teach
ORDER BY employee_id
COMPUTE COUNT(course_id) BY employee_id

--ORDER BY 子句中列是有顺序的，COMPUTE 和 ORDER BY 子句中列的顺序必须一致
SELECT * FROM teach
ORDER BY employee_id,course_id
COMPUTE count(class) BY employee_id,course_id

-----------------------------------------------------------------
CREATE TABLE product
( product_id INT NOT NULL IDENTITY,
product_name CHAR(10) NOT NULL,
price MONEY,
quantity INT,
orderdate DATETIME,
total AS price*quantity,
shippeddate AS DATEADD(DAY,7,orderdate)
)
INSERT INTO product VALUES('苹果',2.5,50,'12.12.2005')
SELECT * FROM product

-----------------------------------------------------------------
CREATE TABLE #employee_temp
( id CHAR(18),
name CHAR(10) NOT NULL,
age INT
)

SELECT id ,employee_name,age INTO #employee_temp FROM employee

SELECT * FROM #employee_temp

--思考：不同数据库之间，数据的复制
--思考：使用SELECT INTO 修改表名、列名

/*******************************************************************
复杂查询:

--UNION
两张表之间的联接方式
笛卡儿积
等值联接
自然联接
θ联接
多于两张表的联接
将表与自身联结
子查询
子查询与其它联接查询的思维方式
多于两级的子查询
涉及聚集函数的子查询
*******************************************************************/

-----------------------------------------------------------------
--笛卡儿积
SELECT employee.*,department.* FROM employee,department
SELECT * FROM employee,department

--问:产生多少条记录
SELECT COUNT(*)AS Employee FROM employee
SELECT COUNT(*)AS Department FROM department
SELECT COUNT(*)AS EmployeeJoinDepartment FROM employee,department

--等值联接
SELECT employee.*,department.* FROM employee,department
WHERE employee.department_id=department.department_id

--自然联接
SELECT employee.*,department.department_name,department.location FROM employee,department
WHERE employee.department_id=department.department_id

/*-===============================================================
小结:

需要查哪些字段? 把需要的字段把在SELECT里
这个查询需要用到哪些表呢? 把它们放在FROM里
怎么联接各条记录? 把各表之间的联系用等值联接放在WHERE里

================================================================*/

--多于两张表的联接
--思考：把teach表中的*_id换成*_name
SELECT * FROM teach

SELECT employee_name,course_name,class,start_time FROM employee,course,teach --没有歧义
WHERE employee.employee_id=teach.employee_id
AND course.course_id=teach.course_id

-----------------------------------------------------------------
--将表与自身联结
--查找哪些房间里不只一个部门
SELECT t1.location,t1.department_id,t1.department_name
FROM department AS t1 ,department t2
WHERE t1.location=t2.location
AND t1.department_id<>t2.department_id
ORDER BY t1.location

-----------------------------------------------------------------
--子查询

--查学术部的员工的姓名
SELECT employee_name FROM employee
WHERE department_id =
(SELECT department_id FROM department
WHERE department_name='学术部')
/*-===============================================================
小结:

子查询的方法,首先由语意逻辑推出主要使用哪张表,再由给定数据引入相关表

================================================================*/
--用联接也可以做
SELECT employee_name,department_name FROM employee,department
WHERE employee.department_id=department.department_id
AND department_name='学术部'

--有哪几个老师在教SEA02
--逆序推理
SELECT employee_name FROM employee
WHERE employee_id IN --注意这里就不能再用=了
(SELECT employee_id FROM teach
WHERE course_id='SEA02')
--正序推理
SELECT employee_name FROM employee
WHERE 'SEA02' IN
(SELECT course_id FROM teach
WHERE employee.employee_id=teach.employee_id)
--使用EXISTS函数的方法
SELECT employee_name FROM employee
WHERE EXISTS
(SELECT * FROM teach
WHERE employee.employee_id=teach.employee_id --这个条件不要掉了
AND course_id='SEA02' --这里就是用到了上面两种方法的两个条件
)
--使用联接的方法
SELECT employee_name FROM employee,teach
WHERE employee.employee_id=teach.employee_id
AND course_id='SEA02'

/*-===============================================================
小结:

总结这种方法的思维方式
重点:逆序或正序推理、联接操作
================================================================*/

--多于两级的子查询
--有哪几个老师在教Dreamweaver 网页设计
SELECT employee_name FROM employee
WHERE employee_id IN
(SELECT employee_id FROM teach
WHERE course_id=
(SELECT course_id FROM course
WHERE course_name ='Dreamweaver 网页设计'
)
)
--用联接也可以做
SELECT employee_name,course_name FROM teach,course,employee
WHERE teach.employee_id =employee.employee_id
AND teach.course_id =course.course_id
AND course_name ='Dreamweaver 网页设计'

--涉及聚集函数的子查询
--最小年龄的员工姓名
SELECT employee_name,age FROM employee
WHERE age=(SELECT MIN(age)min_age FROM employee)

/*******************************************************************
总结：
SELECT FROM
WHERE
GROUP BY
HAVING
ORDER BY
COMPUTE BY

数据库都要先设计好的 编程这么久还没想过这样 好像也不行吧 要实现功能很多种方法 建议你变换下思维方式去实现你的功能