在asp中如何为一个表添加一个字段

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

1、先建立一个MDB数据库，名称为：date.mdb

2、在数据库里建立一个表，名称是：gsec

3、将以下代码保存为2.asp

4、执行该页面即可添加了。

【提供一个附件给你参考】

<%

Dim connstr

connstr="Provider=Microsoft.Jet.OLEDB.4.0Data Source="&Server.MapPath("date.mdb") '数据库名称

Set conn = Server.Createobject("ADODB.Connection")

conn.Open connstr

%>

<%

sql="alter table gsec add column gsec CHAR(255)"

set rs=server.Createobject ("adodb.recordset")

rs.open sql,conn,1,3

%>

<%

sql="alter table gsec add column zauthorit CHAR(255)"

set rs=server.Createobject ("adodb.recordset")

rs.open sql,conn,1,3

%>

<%

sql="alter table gsec add column iden CHAR(255)"

set rs=server.Createobject ("adodb.recordset")

rs.open sql,conn,1,3

%>

<script language="vbscript">

msgbox "添加成功！"

</script>

conn.Execute "alter table 表名 add column 字段名 类型"

字段名 autoincrement(25,1) //设定为自动编号，初始值25，步长1

primary key //设定为主键

not null //设定为必填字段

"SmallInt", // 整型

"Int", // 长整型

"Real", // 单精度型

"Float", // 双精度型

"Money",// 货币

"DateTime",// 日期时间

"Bit", // 是否

"TinyInt", // 字节

"Decimal", // 小数

"VarChar", // 文本

"Memo", // 备注

"Binary", // 二进制

"Image" // OLE 对象

---