asp如何创建字段,asp文件是什么编程语言写的
内容导航:
一、在asp中如何为一个表添加一个字段
INSERT INTO 表名 (“字段”) VALUES (“值”)————简单数据操纵语言: SELECT INSERT DELETE
UPDATE**********//*********简单查询:SELECT语句的基本用法
* 定义列名 IDENTITYCOLTOP WITH TIESWHERE 子句 比较操作符: = <> != < > >= <= !> !< 布尔操作符:
NOT AND OR 谓词: IN 、 BETWEEN AND 谓词往往不是必需的,它们可以由一般的运算组合代替,但能使语意更清晰 DISTINCT
DISTINCT 必需在所有字段前 作用于所有列,DISTINCT将显示所有列的组合各不相同的行 IS NULL ISNULL() LIKE % _ []
^ ESCAPE–>'[]’ = ‘!‘ESCAPE’!’ORDER BY 子句 ASC
DESC聚集函数:MIN、MAX、SUM、AVG、COUNTGROUP 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 departmentSELECT * FROM departmentSELECT
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 INSELECT * 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 NULLSELECT
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 DESCSELECT * FROM teach ORDER BY employee_id
,course_id DESCSELECT * 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_idSELECT * 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_idSELECT * FROM
teach ORDER BY employee_id COMPUTE COUNT(course_id) BY employee_id–ORDER BY
子句中列是有顺序的,COMPUTE 和 ORDER BY 子句中列的顺序必须一致SELECT * FROM teachORDER BY
employee_id,course_idCOMPUTE 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 employeeSELECT * FROM
employee_temp–思考:不同数据库之间,数据的复制–思考:使用SELECT INTO
修改表名、列名/**********复杂查询:–UNION两张表之间的联接方式
笛卡儿积 等值联接 自然联接 θ联接多于两张表的联接将表与自身联结子查询 子查询与其它联接查询的思维方式 多于两级的子查询
涉及聚集函数的子查询*********/——————————————————————-笛卡儿积SELECT
employee.,department. FROM employee,departmentSELECT * FROM
employee,department–问:产生多少条记录SELECT COUNT()AS Employee FROM employeeSELECT
COUNT()AS Department FROM departmentSELECT COUNT()AS EmployeeJoinDepartment
FROM employee,department–等值联接SELECT employee.,department. FROM
employee,department WHERE t_id=t_id–自然联接SELECT employee.,t_name, FROM
employee,department WHERE
t_id=t_id/-===============================================================小结:需要查哪些字段?
把需要的字段把在SELECT里这个查询需要用到哪些表呢? 把它们放在FROM里怎么联接各条记录?
把各表之间的联系用等值联接放在WHERE里================================================================/–多于两张表的联接
–思考:把teach表中的_id换成_nameSELECT * FROM teachSELECT
employee_name,course_name,class,start_time FROM employee,course,teach –没有歧义
WHERE _id=_id AND _id=_id
——————————————————————-将表与自身联结–
查找哪些房间里不只一个部门SELECT ,t_id,t_name FROM department AS t1 ,department t2 WHERE =
AND t_id<>t_id ORDER BY
——————————————————————-子查询–
查学术部的员工的姓名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 t_id=t_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 _id=_id)–使用EXISTS函数的方法SELECT employee_name
FROM employee WHERE EXISTS (SELECT * FROM teach WHERE _id=_id –这个条件不要掉了 AND
course_id=’SEA02’ –这里就是用到了上面两种方法的两个条件 )–使用联接的方法SELECT employee_name FROM
employee,teach WHERE _id=_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 _id =_id AND _id
=_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
二、在一个ASP页面添加字段
有一个ASP的后台页面,因需要,想添加几个字段,但不知道怎么添加,求助下高手,可以把页面QQ发送给你参考
数据库表里添加字段。
然后再程序中 insert into 表明 (,,,) values (,,,) 对应的添加就可以了。
你如果是想添加几个几条数据集进去就用楼上说的insert语句,你如果是想在数据库表当中再加入一些列,也就是新字段,你就打开后台的数据库表格修改表结构。
三、怎么用asp创建sql数据表及相关操作
答:首先,不知道你要连接的数据库是不是MSSQLSERVER,如果是的话,建表的语法都不正确,应该是(“createtableuser“)首先,表名由于与系统函数USER同名,因此必须用方括号括起来(建议最好换个表名);其次对于各列的声明,必须首先声明其数据类型,如果是自增序列,则加IDENTITY关键字,如果是主键,则再加PRIMARYKEY关键字。要在一个表中创建新的字段,参考如下示例:altertalbe[user]addcol1varchar(20)要在一个表中删除字段,参考如下示例:altertable[user]dropcolumncol1要清空表内容,参考如下示例:truncatetable[user]要删除表,参考如下示例:droptable[user]建议你先在查询分析器中测试所有SQL语句是否正确,然后再改写至ASP文件中。
四、aspaccess如何在已有表中创建新的字段
我想在原有的表(AAA)中创建一个新的字段,名字为:(BBB),数据类型为(文本),字段大小为(255),请问应如何写代码?sql=”ALTER Table
[aaa] ADD [bbb] nVarchar(50)”(sql)SQL:ALTER TABLE AAA ADD COLUMN BBB
text(255)再看看别人怎么说的。
SQL:ALTER TABLE AAA ADD COLUMN BBB text(255)
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,请发送邮件至 55@qq.com 举报,一经查实,本站将立刻删除。转转请注明出处:https://www.szhjjp.com/n/102479.html