20130819-SQL语言基本操作格式及实例

20130819-SQL语言基本操作格式及实例

摘自电子书<SQL语言操作速查手册>

1.  定义删除修改基本表

1.1    定义基本表

格式：

CREATE TABLE <表名>(<列名><数据类型>[列级完整性约束条件]

[,<列名><数据类型>[列级完整性约束条件]]……

[，<表级完整性约束条件>])；

 

例子：

Create table Student(

Sno char(5) not null,unique

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       日期加时间。

1.2    修改基本表

格式：

SQL语言使用ALTER TABLE 语句修改基本表，其一般格式如下：

ALTER TABLE <表名>

[ADD<新列名><数据类型>[完整性约束]]

[DROP<完整性约束名>]

[MODIFY<列名><数据类型>]；

 

例子：

向student表新增入学时间列，其数据类型为日期型

Alter table Student

Add scome date;

将年龄数据列改为半字长整数

Alter table Student

Modify sage smallint;

将student表中的sname的unique属性去掉

ALTER TABLE <表名>

[DROP<完整性约束名>]

1.3   删除基本表

格式：

DROP TABLE <表名>;

例子：

Drop table Student;

2.  建立与删除索引

2.1 建立索引

格式

SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为：

CREATE[UNIOUE][CLUSTER]INDEX<索引名>

ON <表名>(<列名>[<次序>][，<列名>[<次序>]]……)；

UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。

CLUSTER 表示要建立的索引是聚簇索引，所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。

 

例子

将会在Student 表的Sname(姓名)列上建立一个聚簇索引，而且Student 表中的记录将按照Sname值的升序存放

Create cluster index student_name on Student(sname);

例为学生-课程数据库中的Student，Course,SC三个表建立索引。其中Student 表按学号升序建唯一索引，表Course按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

Create unique index index_sno on Stdudent(sno);

Create unique index index_cno on Student(cno);

Create unique index index_sno_cno on SC(sno asc,cno desc);

 

2.2 删除索引

格式

SQL语言中，删除索引使用DROP INDEX语句，其一般格式为：

DROP INDEX <索引名>

例子

删除Student表的Stusname索引

Drop index student_name;

 

3.  查询

查询的一般格式为：

SELECT[ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]……

FROM<表名或视图名>[,<表名或视图名>]……

[WHERE <条件表达式>]

[GROUP BY<列名1>[HAVING<条件表达式>]]

[ORDER BY<列名2>[ASC|DESC]]；

 

2     

3     

3.1    单表查询

3.1.1   单表查询

3.1.1.1  查询指定列

例7：查询全体学生的学号与姓名。

Select sno,sname from Student;

3.1.1.2  查询全部列

例8 查询全体学生的详细记录。

Select * from Student;

等价于select sno,sname,sage,ssex,sdept from Student;

3.1.1.3  查询经过计算的值

例9 查全体学生的姓名及其出生年份

Select sname,1996-sage from Student;

3.1.2   查询满足条件的元组

1.消除取值重复的行

两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行了。

例 10 查询选修了课程的学生学号。

Select sno from sc;

该查询结果里包含了许多重复的行。如果想去掉结果表中的重复项，必须指定DISTINCT短语:

Select distinct sno from sc;

3.1.3   选择表中的若干元组

3.1.3.1  比较大小

查询满足条件的元组可以通过WHERE子句实现。

用与进行比较的运算符一般包括：

=(等于)，>(大于),<(小于),>=(大于等于),<=(小于等于),!=或<>(不等于)。

还包括：!>(不大于),!<(不小于)。

例11 查询所有年龄在20岁以下的学生姓名及年龄。

Select sname,sage

from student

where sage <20

3.1.3.2  确定范围

谓词BETWEEN……AND……和NOT BETWEEN……AND……可以用来查询属性值在（或不在）指定范围内的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。

例12 查询年龄不在20 ~23岁之间的学生姓名、系别和年龄。

Select sname,sdept,sage

From student

Where sage between 20 and 23;

3.1.3.3  确定集合

谓词IN可以用来查找属性值属于指定集合的元组。

例13 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

Select sname,ssex

From student

Where sdept in (IS,MA,CS);

3.1.3.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’\’

3.1.3.5  涉及空值的查询

例17 查询所有有成绩的学生学号和课程号。

Select sno,cno

From sc

Where grade is not null;

3.1.3.6  多重条件的查询

逻辑运算符AND和OR可用来联结多个查询条件。AND的优先级高于OR，但用户可以用括号改变优先级。

例18 查询计算机系年龄在20岁以下的学生姓名

Select sname

From Stduent

Where sage <20 and sdept = ‘SC’

3.1.4   对查询结果排序

查询的一般格式为：

SELECT[ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]……

FROM<表名或视图名>[,<表名或视图名>]……

[WHERE <条件表达式>]

[GROUP BY<列名1>

[HAVING<条件表达式>]]

[ORDER BY<列名2>[ASC|DESC]]；

思考：where having group by order by 的顺序及其原因?

 

用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或

降序（DESC）排列，缺省值为升序。

例：查询选修了3毫课程的学生的学号及成绩，查询结果按分数的降序排列。

Select  sno,grade

From sc

Where cno =’3’

Order by grade desc;

3.1.5   使用集函数

SQL提供的集函数主要有：

COUNT([DISTINCT|ALL]*) 统计元组个数

COUNT([DISTINCT|ALL]<列名>) 统计一列中值的个数（空值不计）

SUM ([DISTINCT|ALL]<列名>) 计算一列值的总和（此列必须是数值型）

AVG([DISTINCT|ALL]<列名>) 计算一列值的平均值（此列必须是数值型）

MAX([DISTINCT|ALL]<列名>) 求一列值中的最大值

MIN([DISTINCT|ALL]<列名>) 求一列值中的最小值

例：查询学生总人数 select count(*) from student ;

例：计算1号课程的学生平均成绩 select avg(grade) from sc where cno = ‘1’;

3.1.6   对查询结果分组

GROUP BY子句将查询结果表按某一列或多列值分组，值相等的为一组。如果

分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的

组，则可以使用HAVING短语指定筛选条件。

例：求各个课程号及相应的选课人数，且只输出超过3人的课号

Select cno,sum(sno)

From sc

Group by cno

Having sum(sno)>3;

3.2    其他查询

3.2.1   连接查询

若一个查询同时涉及两个以上的表，则称之为连接查询。

3.2.1.1  等值与非等值连接

当连接运算符为＝时，称为等值连接。

若在等值连接中把目标列中重复的属性列去掉则为自然连接。

例：查询每个学生及其选修课程的情况

Select s.*,c.*

From student as s ,cource as c

Where s.sno = c.sno;

3.2.1.2  外连接

例：select sno,sname,cno,cname

From  student left join cource

Where student.sno = cource.sno

思考：内连接，外连接，左连接，右连接都是用来做什么的，又是怎么规定及命名的？

3.2.1.3  复合条件连接

WHERE子句中可以有多个连接条件，称为复合条件连接。

例：查询选修2号课程且成绩在90分以上的所有学生

Select s.sno,s.sname

From student s ,CS cs

Where s.sno = cs.sno and cs.grade >90 and sc.cno = ‘2’;

3.2.2   嵌套查询一

嵌套查询

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询

称为嵌套查询。

3.2.2.1  带有IN谓词的子查询

例：查询与刘晨在同一个系学习的学生

Select sno,sname

From student

Where dept in(

    Select dept from student where sname = ‘刘晨’);

3.2.2.2  带有比较运算符的子查询

例：

select sno,sname,sdept

from student

where dept = (

    select dept from student where sname = ‘刘晨’’);

3.2.3   嵌套查询二

3.2.3.1  带有ANY或ALL谓词的子查询

例：查询其他系中比信息系某一学生年龄小的学生姓名和年龄

Select sname,sage

from student

where sage < any(select age from student where sdept = ‘IS’)

3.2.3.2  带有EXISTS谓词的子查询

EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻

辑真值“true”或逻辑假值“false”。

 

 

例：查询所有选修了1号课程的学生姓名

Select sname

From student

Where exsits(

    Select * from sc where sc.sno = student.sno and cno = 1

);

3.2.4   集合查询

多个SELECT语句的结果可进行集合操作。集合操作主要包括UNION、INTERSECT、MINUS。

例45 查询计算机科学系的学生及年龄不大于19的学生。

Select * from student where dept = ‘sc’

Union

(select * from student where sage <19)

4.  数据更新

插入单个元组的INSERT语句的格式为：

INSERT

INTO<表名>[<属性列1>[,<属性列2>...)]

VALUES(<常量1>[,<常量2>]...)

4  

5  

4.1 插入数据

4.1.1   插入数据

插入子查询结果的INSERT语句的格式为：

INSERT

INTO<表名>[<属性列1>[,<属性列2>...)]

子查询；

例1 插入一条选课记录('95020','1')

Insert into sc(sno,cno)

Values('95020','1')

新插入的记录在Grade列上取空值

4.1.2   插入子查询结果

例3 对每一个系，求学生的平均年龄，并把结果存入数据库。

CREATE TABLE Deptage

(Sdept CHAR(15)

Avgage SMALLNT);

然后对表按系分组求平均年龄,再把系名和平均年龄存入新表中。

Insert into Deptage(sdept,avage) values

Select sdept,avg(sage) from student ;

4.2 修改数据

修改操作语句的一般格式为:

UPDATE <表名>

SET<列名>=<表达式>[,<列名>=<表达式>]...

[WHERE<条件>];

4.2.1    修改某一个元组的值

例4 将学生95001的年龄改为22岁。

Update student

Set sage = ‘22’

Where sno = ‘95001’;

4.2.2    修改多个元组的值

例5 将所有学生的年龄增加1岁。

Update student

Set sage = sage +1;

4.2.3    带子查询的修改语句

子查询也可以嵌套在UPDATE语句中。

例6 将计算机科学系全体学生的成绩置零。

Update sc

Set grade = 0

Where ‘sc’ = (

Select dept

From student

Where student.sno = sc.sno

)

4.3 删除数据

删除语句的一般格式为:

DELETE

FROM<表名>

[WHERE<条件>];

4.3.1   删除某一个元组的值

例7 删除学号为95019的学生记录:delete from student where sno = ‘95019’

4.3.2   除多个元组的值

例8 删除所有的学生的选课记录:delete from sc;

4.3.3   带子查询的删除语句

例9 删除计算机科学所有学生的选课记录:delete from sc where exsit(select sno from student where dept = ‘cs’)

4.3.4   更新操作与数据库的一致性

5.  视图

6. 

5.1定义视图

 

 

5.1.1建立视图

SQL语言用CREATE VIEW命令建立视图，其一般格式为：

CREATE VIEW[<列名>[,<列名>]...)]

AS <子查询>

[WITH CHECK OPTION];

子查询不允许含有ORDER BY 子句和DISTINCT短语。

例1 建立信息系学生的视图。

CREATE VIEW IS_Student

AS

SELECT Sno,Sname,Sage

FROM Student

WHERE Sdept='IS';本例中省略了视图view_student的列名,隐含了由子查询中SELECT子句中的三个列名组成。

5.1.2删除视图

该语句的格式为：

DROP VIEW <视图名>;

例8 删除视图IS_S1

Drop view is_s1

此语句后, IS_S1视图的定义将从数据字典中删除。

5.2查询视图

查询试图

视图定义后，用户就可以象对基本表一样对视图进行查询了。

例1 在信息系学生的视图中找出年龄小于20岁的学生。

SELECT Sno，Sage

FROM IS_Student

WHERE Sage<20；

DBMS执行对视图的查询时，首先进行有效性检查，检查查询的表、视图等是否存在。如果

存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等

价的对基本表的查询，然后再执行修正了的查询。这一转换过程为视图消解（View Resoluti

on）。

本例转换后的查询语句为：

SELECT Sno，Sage

FROM Student

WHERE Sdept＝'IS' AND Sage<20；

5.3更新视图

更新视图是指通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。

例2.向信息系学生视图IS_S中插入一个新的学生记录，其中学号为95029，姓名为赵新，年龄为20岁。

INSERT

INTO IS_Student

VALUES('95029','赵新',20);

转换为对基本表的更新：

INSERT

INTO Student（Sno，Sname，Sage，Sdept）

VALUES('95029','赵新',20,'IS');

这里系统自动将系名‘IS’放入VALUES子句中。

5.4视图的作用

视图最终是定义再基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。而

且对于非行列子集视图进行查询和更新时还有可能出现问题。既然如此，为什么还要定义视图

呢？这是因为合理使用视图能够带来许多好处。

1.视图能够简化用户的操作

2.视图使用户能以多种角度看待同一数据

3.视图对重构数据库提供了一定程度的逻辑独立性

4.视图能够对机密数据提供安全保护