SQL常用命令使用方法

SQL常用命令使用方法：
(1) 数据记录筛选：
sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
sql="select * from 数据表 where 字段名 like %字段值% order by 字段名 [desc]"
sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]"
sql="select * from 数据表 where 字段名 in (值1,值2,值3)"
sql="select * from 数据表 where 字段名 between 值1 and 值2"
(2) 更新数据记录：
sql="update 数据表 set 字段名=字段值 where 条件表达式"
sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"
(3) 删除数据记录：
sql="delete from 数据表 where 条件表达式"
sql="delete from 数据表" (将数据表所有记录删除)
(4) 添加数据记录：
sql="insert into 数据表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …)"
sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到目标数据表)
(5) 数据记录统计函数：
AVG(字段名) 得出一个表格栏平均值
COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名) 取得一个表格栏最大的值
MIN(字段名) 取得一个表格栏最小的值
SUM(字段名) 把数据栏的值相加
引用以上函数的方法：
sql="select sum(字段名) as 别名 from 数据表 where 条件表达式"
set rs=conn.excute(sql)
用 rs("别名") 获取统的计值，其它函数运用同上。
(5) 数据表的建立和删除：
CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… )
例：CREATE TABLE tab01(name varchar(50),datetime default now())
DROP TABLE 数据表名称 (永久性删除一个数据表)

在ASP编程时，下面这些语句是必须知道的：
1.连接数据库
a. ASP与Access数据库连接：
<%@ language=VBs cript%>
<%
dim conn,mdbfile
mdbfile=server.mappath("数据库名称.mdb")
set conn=server.createobject("adodb.connection")
conn.open "driver={microsoft access driver (*.mdb)};uid=admin;pwd=数据库密码;dbq="&mdbfile
%>
b. ASP与SQL数据库连接：
<%@ language=VBs cript%>
<%
dim conn
set conn=server.createobject("ADODB.connection")
con.open "PROVIDER=SQLOLEDB;DATA SOURCE=SQL服务器名称或IP地址;UID=sa;PWD=数据库密码;DATABASE=数据库名称
%>
建立记录集对象：
set rs=server.createobject("adodb.recordset")
rs.open SQL语句,conn,3,2
2. 记录集对象的方法：
rs.movenext 将记录指针从当前的位置向下移一行
rs.moveprevious 将记录指针从当前的位置向上移一行
rs.movefirst 将记录指针移到数据表第一行
rs.movelast 将记录指针移到数据表最后一行
rs.absoluteposition=N 将记录指针移到数据表第N行
rs.absolutepage=N 将记录指针移到第N页的第一行
rs.pagesize=N 设置每页为N条记录
rs.pagecount 根据 pagesize 的设置返回总页数
rs.recordcount 返回记录总数
rs.bof 返回记录指针是否超出数据表首端，true表示是，false为否
rs.eof 返回记录指针是否超出数据表末端，true表示是，false为否
rs.delete 删除当前记录，但记录指针不会向下移动
rs.addnew 添加记录到数据表末端
rs.update 更新数据表记录
附：
常数 常数值 说明
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
adLockReadOnly 1 缺省值，Recordset对象以只读方式启动，无法运行AddNew、Update及Delete等方法
adLockPrssimistic 2 当数据源正在更新时，系统会暂时锁住其他用户的动作，以保持数据一致性。
adLockOptimistic 3 当数据源正在更新时，系统并不会锁住其他用户的动作，其他用户可以对数据进行增、删、改的操作。
adLockBatchOptimistic 4 当数据源正在更新时，其他用户必须将CursorLocation属性改为adUdeClientBatch才能对数据进行增、删、改的操作。
--------------------------------------------------------------------------------
郑重声明：
本贴仅代表【连连】个人观点，与【耒水小鲢鱼论坛】无关。
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

3.2.1 定义、删除与修改基本表
一、定义基本表
SQL语言使用CREATE TABLE语句定义基本表，其一般格式如下：
CREATE TABLE <表名>(<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]……
[，<表级完整性约束条件>])；


例1 建立一个“学生”表student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。
CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE, /*列级完整性约束条件，Sno取值唯一，不许取空值*/
Sname CHAR(20) UNIQUE,
Ssex CHAR(1),
Sage INT,
Sdept CHAR(15));
定义表的各个属性时需要指明其数据类型及长度。
SMALLINT 半字长二进制整数。
INTEGER或INT 全字长二进制整数。
DECIMAL(p[,q]) 压缩十进制数，共P位，其中小数后有q
或DEC(p[,q]) 位。0<=q<=p<=15,q=0时可以省略不写。
FLOAT 双字长浮点数。
CHARTER(n)或CHAR(n) 长度为n的定长字符串。
VARCHAR(n) 最大长度为n的变长字符串。
GRAPHIC(n) 长度为n的定长图形字符串。
VARGRAPHIC(n) 最大长度为n的变长图形字符串。
DATE 日期型，格式为YYYY-MM-DD。
TIME 时间型，格式为HH.MM.SS。
TIMESTAMP 日期加时间。
二、修改基本表
SQL语言使用ALTER TABLE 语句修改基本表，其一般格式如下：
ALTER TABLE <表名>
[ADD<新列名><数据类型>[完整性约束]]
[DROP<完整性约束名>]
[MODIFY<列名><数据类型>]；


例2 向Student表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE Student ADD Scome DATE；
例3 将年龄的数据类型改为半字长整数。
ALTER TABLE Student MODIFY Sage SMALLINT;
三、删除基本表
当某个基本表不再使用时，可以使用DROP TABLE语句删除它。其一般格式为：
DROP TABLE <表名>;
例4 删除Student表。
DROP TABLE Student;

3.2.2 建立与删除索引
一、建立索引
SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为：
CREATE[UNIOUE][CLUSTER]INDEX<索引名>
ON <表名>(<列名>[<次序>][，<列名>[<次序>]]……)；
UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER 表示要建立的索引是聚簇索引，所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。


例如： 执行下面的CREATE INDEX语句：
CREATE CLUSTER INDEX Stusname ON Student(Sname)；
将会在Student 表的Sname(姓名)列上建立一个聚簇索引，而且Student 表中的记录将按照Sname值的升序存放；


例5 为学生-课程数据库中的Student，Course,SC三个表建立索引。其中Student 表按学号升序建唯一索引，表Course按课程号升序建唯一 索引，SC表按学号升序和课程号降序建唯一索引。
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
3.2.2 建立与删除索引
二、删除索引
SQL语言中，删除索引使用DROP INDEX语句，其一般格式为：
DROP INDEX <索引名>


例6 删除Student表的Stusname索引
DROP INDEX Stusname；

3.3 查询
查询的一般格式为：
SELECT[ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]……
FROM<表名或视图名>[,<表名或视图名>]……
[WHERE <条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]]；
3.3.1 单表查询
一、选择表中若干列
1.查询指定列
例7：查询全体学生的学号与姓名。
SELECT Sno，Sname
FROM Student；
2.查询全部列
例8 查询全体学生的详细记录。
SELECT *
FROM Student； 等价于： SELECT Sno，Sname，Ssex,Sage,Sdept
FROM Student;
3.查询经过计算的值
例9 查全体学生的姓名及其出生年份
SELECT Sname，1996-Sage
FROM Student；
3.3 查询
查询的一般格式为：
SELECT[ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]……
FROM<表名或视图名>[,<表名或视图名>]……
[WHERE <条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]]；


二、选择表中的若干元组
1.消除取值重复的行
两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行了。
例 10 查询选修了课程的学生学号。
SELECT Sno
FROM SC;
该查询结果里包含了许多重复的行。如果想去掉结果表中的重复项，必须指定DISTINCT短语：
SELECT DISTINCT Sno
FROM SC；
如果没有指定DISTINCT短语，则缺省为ALL，即保留结果表中的重复的行。

3.3 查询
查询的一般格式为：
SELECT[ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]……
FROM<表名或视图名>[,<表名或视图名>]……
[WHERE <条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]]；


二、选择表中的若干元组

2.查询满足条件的元组。
(1)比较大小
查询满足条件的元组可以通过WHERE子句实现。
用与进行比较的运算符一般包括：
=(等于)，>(大于),<(小于),>=(大于等于),<=(小于等于),!=或<>(不等于)。
还包括：!>(不大于),!<(不小于)。
例11 查询所有年龄在20岁以下的学生姓名及年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage<20;
或 SELECT Sname,Sage
FROM Student
WHERE NOT Sage>=20;
(2)确定范围
谓词BETWEEN……AND……和NOT BETWEEN……AND……可以用来查询属性值在（或不在）指定范围内的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。
例12 查询年龄不在20 ~23岁之间的学生姓名、系别和年龄。
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
(3)确定集合
谓词IN可以用来查找属性值属于指定集合的元组。
例13 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN('IS'，'MA'，'CS')
(4)字符匹配
谓词LIKE可以进行字符串的匹配。其一般格式如下：
[NOT]LIKE’<匹配串>‘[ESCAPE’<换码字符>']
.%(百分号) 代表任意长度（长度可以为0）的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串。
._(下横线) 代表任意单个字符。
例14 查询学号为95001的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE'95001';
等价于：SELECT *
FROM Student
WHERE Sno ='95001';
例15 查询名字第二个字为“阳”字的学生的姓名和学号。
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE'_ _阳%';
如果用户查询的字符串的字符串本身就含有%或_ _,这时就要使用ESCAPE '<换码字符>'短语对通配符进行转义了。
例16 查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _'ESCAPE'\';
(5)涉及空值的查询
例17 查询所有有成绩的学生学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
(6)多重条件查询
逻辑运算符AND和OR可用来联结多个查询条件。AND的优先级高于OR，但用户可以用括号改变优先级。
例18 查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept='cs'AND Sage<20;

3.3 查询
查询的一般格式为：
SELECT[ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]……
FROM<表名或视图名>[,<表名或视图名>]……
[WHERE <条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]]；


3．3．1 单表查询
三、对查询结果排序
用户可以用ORDER BY子句对查询结果按照