关系数据库标准语言SQL

jinm127

3.1 SQL概述

SQL(Structured Query Language)语言是1974年由Boyce和Chamberlin提出的．1975年～1979年IBM公司 San Jose Research Laboratory 研制了著名的关系数据库管理系统原型System R并实现了这种语言. SQL语言最终发展成为关系数据库的标准语言和数据库领域中一个主流语言．
SQL语言的特点：
1．综合统一

SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体.
2．高度非过程化
用户只要提出”做什么”，而无须指明“怎么做”,因此无需了解存取路径，存取路径的选择以及SQL语言的操作过程由系统自动完成．不但大大减轻了用户负担，而且有利于提高数据的独立性.
3．面向集合的操作方式
查找结果、插入、删除、更新操作的对象可以是元组的集合。
4．自含式和嵌入式两种使用方式
5．语言简洁，易学易用

3．2 数据定义

SQL的数据定义功能包括定义表、定义视图和定义索引。
3．2．1定义、删除与修改基本表

1.定义基本表
格式: CREATE TABLE <表名>
（＜列名＞ ＜数据类型> [列级完整性约束条件]

…

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


例1
建立一个”学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成.其中学号不能为空，值是唯一的，并且姓名取值也唯一．

CREATE TABLE Student(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20) UNIQUE,
Ssex CHAR(1),
Sage INT,
Sdept CHAR(15));
二、修改基本表

ALTER
TABLE ＜表名＞

［ADD ＜新列名＞
＜数据类型＞ [完整性约束]］

[DROP ＜完整性约束名＞]

［MODIFY ＜列名＞ ＜数据类型>］；
例2 向Student表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE Student ADD Scome
DATE；
不论基本表中原来是否已有数据，新增加的列一律为空值。
例3
将年龄的数据类型改为半字长整数。

ALTER TABLE Student
MODIFY Sage
SMALLINT;
例4删除学生姓名必须取唯一值的约束。

ALTER TABLE Student DROP UNIQUE（Sname）；

SQL不提供修改视图定义和索引定义的操作．只能先将它们删除掉，然后再重建．
三、删除基本表
格式： DRO P
TABLE ＜表名＞
例5
删除Student表

DROP
TABLE
Student；
基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。
3．2．2建立与删除索引
一、建立索引
CREATE
[UNIQUE] [CLUSTER] INDEX ＜索引名＞
ON ＜表名＞ （＜列名>
[＜次序>]…）；
ASC（升序）或DESC（降序），缺省值为ASC。
CLUSTER聚簇索引。指索引项的顺序与表中记录的物理顺序一致的索引组织。例如：
CREATE CLUSTER INDEX Stusname ON Student（Sname）；
将会在 Student表的 Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放。
在一个基本表上最多只能建立一个聚簇索引。对于经常更新的列不宜建立聚簇索引。
例 6
为学生-课程数据库中的 Student，Course， SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。
CREATE
UNIQUE
INDEX
Stusno
ON Student（Sno）；
CREATE
UNIQUE
INDEX
Coucno
ON Couse（Cno）；
CREATE
UNIQUE
INDEX
Scno
ON
SC（Sno ASC，Cno DESC）；
二、删除索引
DROP INDEX ＜索引名＞；
例7
删除Student表的Stusname索引。

DROP INDEX
Stusname；
删除索引时，系统会同时从数据字典中删去有关该索引的描述。

3．3查询

例：
SELECT
Sname，
Sage

FROM
student
WHERE Sage＜ 20;
格式：
SELECT
[ALL | DISTINCT]＜目标列表达式＞…
FROM ＜表名或视图名> …

[WHERE ＜条件表达式＞]
[GROU P
BY＜列名1＞［HAVING ＜条件表达式＞]]
[ORDER BY＜列名2＞[ASC| DESC]];
含义：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按＜列名1＞的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按＜列名2＞的值的升序或降序排序。
既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。
学生-课程数据库中包括三个表。
学生表：Student（Sno,Sname,Ssex,Sage,Sdept）
课程表： Course（Cno， Cname,Cpno， Ccredit）
学生选课表：SC（Sno，Cno，Grade）
3．3．l单表查询（仅涉及一个表）
一 、选择表中的若干列
1．查询指定列
例1
查询全体学生的学号与姓名。
SELECT
Sno，Sname
FROM
Student；
例2
查询全体学生的姓名、学号、所在系。

SELECT
Sname，Sno，Sdept
FROM Student；
可根据应用的需要改变列的显示顺序。
2．查询全部列
例3
查询全体学生的详细记录。

SELECT
*
FROM
Student；
3．查询经过计算的值
例4
查全体学生的姓名及其出生年份。

SELECT Sname，2000-Sage
FROM
Student；

输出的结果为：

Sname	2000-Sage
李勇	1976
刘晨	1977
王敏	1978
张立	1977

＜目标列表达式＞还可以是字符串常量、函数等。
例5
查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。

SELECT
Sname，’ Year of Birth:’, 2000－Sage，ISLOWER（Sdept）FROM Student；

Sname	‘Year of Birth:’	2000-Sage	ISLOWER(Sdept)
李勇	Year of Birth:	1976	cs
刘晨	Year of Birth:	1977	is
王敏	Year of Birth:	1978	ma
张立	Year of Birth:	1977	is

可以通过指定别名来改变查询结果的列标题．对于上例：
SELECT
Sname
NAME，’ Year of Birth:’ BIRTH, 2000－Sage
BIRTHDAY，ISLOWER（Sdept）DEPARTMENT
FROM Student；

NAME	BIRTH	BIRTHDAY	DEPARTMENT
李勇	Year of Birth:	1976	cs
刘晨	Year of Birth:	1977	is
王敏	Year of Birth:	1978	ma
张立	Year of Birth:	1977	is

jinm127

二、选择表中的若干元组

1. 消除取值重复的行

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

例6 查询选修了课程的学生学号．

      SELECT  Sno  FROM  SC

假设SC表中有下列数据

Sno
Cno
Grade

95001
1
92

95001
2
85

95001
3
88

95002
2
90

95002
3
80


执行上面的SELECT语句后，结果为:

如果想去掉结果表中的重复行，必须指定DISTINCT短语：

SELECT  DISTINCT  Sno  FROM  SC;

        执行结果为：

Sno

95001

95002


                                       

如果没有指定DISTINCT短语，则缺省为ALL，即保留结果表中取值重复的行．

2．查询满足条件的元组

表3.3 常用的查询条件

查询条件
谓词

比较
=, > , <, <=, >=, <>, !>,!<,

NOT+上述比较运算符

确定范围
BETWEEN AND,  NOT BETWEEN AND

确定集合
IN , NOT IN

字符匹配
LIKE, NOT LIKE

空值
IS NULL, IS NOT NULL

多重条件
AND, OR


(1)比较大小

  =   ＞  ＜   ＞=   ＜=   !=或＜＞

   逻辑符NOT可与比较运算符同用，对条件求非。

例7  查询计算机系全体学生的名单。

        SELECT   Sname   

FROM Student  

WHERE Sdept＝’CS’;

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

        SELECT  Sname，  Sage   

FROM  student

WHERE Sage＜ 20;

或

              SELECT  Sname，  Sage   

FROM  student

WHERE  NOT Sage>= 20;

例9  查询考试成绩有不及格的学生的学号。

          SELECT  DISTINCT  Sno

FROM  SC   WHERE  Grade＜ 60;

(2)确定范围

    BETWEEN…AND…,     NOT BETWEEN…AND…

例10 查询年龄在20－23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECT  Sname， Sdept， Sage

FROM    Student

WHERE   Sage  BETWEEN  20  AND  23；

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

SELECT Sname， Sdept，Sage

FROM Student

WHERE  Sage  NOT  BETWEEN  20  AND 23；

（3）确定集合

     IN   , NOT IN

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

SELECT Sname，Ssex  

FROM    Student

WHERE  Sdept  IN （‘IS’，’MA’，’CS’）;

例13 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

SELECT  Sname，Ssex

FROM    Student

WHERE   Sdept  NOT  IN （‘IS’，’MA’，’CS’）;

（4）字符匹配

[ NOT]  LIKE  ‘＜匹配串＞’［ESCAPE ‘＜换码字符＞’］

  查找指定的属性列值与＜匹配串＞相匹配的元组。＜匹配串＞可以含有通配符％和_

  ％（百分号）代表任意长度（可以为0）的字符串。

例如  a％b

  _（下横线）代表任意单个字符。

例如 a_ b

例14查询学号为95001的学生的详细情况。

              SELECT  *  

FROM  STUDENT  

WHERE  SNO LIKE  ‘95001’;

等价于          SELECT *

FROM  STUDENT

WHERE SNO= ‘95001’;

   如果LIKE后面的匹配串中不含通配符，则可以用=（等于）运算符取代LIKE谓词，用!=或＜＞（不等于）运算符取代NOT LIKE谓词。

例15  查询所有姓刘的学生的姓名、学号和性别。

          SELECT  Sname，  Sno，  Ssex

          FROM    Student

          WHERE   Sname   LIKE  ‘刘％’;

例16查询姓“欧阳”且全名为三个汉字的学生的姓名。

              SELECT  Sname

              FROM    Student

              WHERE   Sname  LIKE  ’欧阳_ _’；

例17 查询名字中第2个字为“阳”字的学生的姓名和学号。

              SELECT  Sname，  Sno

              FROM    Student

              WHERE   Sname   LIKE  ‘_ _阳％’；

例18  查询所有不姓刘的学生姓名。

          SELECT  Sname

          FROM    Student

          WHERE   Sname   NOT   LIKE   ‘刘％’;

    如果用户要查询的字符串本身就含有％或_，这时就要使用ESCAPE ’＜换码字符＞’ 短语对通配符进行转义。

例19 查询 DB_Design课程的课程号和学分。

     SELECT  Cno，Ccredit

     FROM    Course

WHERE Cname  LIKE  ‘DB\_Design’ ESCAPE  ‘＼’

例20查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况。

    SELECT  *

    FROM   Course

    WHERE  Cname LIKE  ‘DB＼_％i_ _’  ESCAPE ‘＼’;（5）涉及空值的查询

例21 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

              SELECT SNO,CNO

FROM    SC

WHERE GRADE  IS  NULL

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

SELECT SNO,CNO

FROM  SC  

WHERE  GRADE  IS  NOT NULL

（6）多重条件查询

     AND   OR

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

            SELECT  SNAME

            FROM   Student

WHERE  Sdept=’CS’  AND  Sage＜20;

jinm127

三、对查询结果排序
例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

SELECT
Sno，Grade

FROM
SC

WHERE
Cno=’ 3’

ORDER
BY
Grade
DESC;
例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。


SELECT
*

FROM
Student

ORDER
BY
Sdept，Sage
DESC;
四、使用集函数
COUNT（*）统计元组个数
COUNT（［DISTINCT | ALL］ ＜列名＞）统计一列中值的个数
SUM（［DISTINCT | ALL］＜列名＞）计算一列值的总和（此列必须是数值型）
AVG （［DISTINCT | ALL］＜列名＞）计算一列值的平均值此列必须是数值型）
MAX（［DISTINCT | ALL］＜列名＞）求一列值中的最大值
MIN（［DISTINCT | ALL］＜列名＞）求一列值中的最小值
例26查询学生总人数。

SELECT
COUNT
(*)

FROM
Student;
例27查询选修了课程的学生人数。
SELECT
COUNT(DISTINCT SNO)
FROM
SC;
例28
计算1号课程的学生平均成绩。

SELECT
AVG（Grade）

FROM
SC

WHERE
Cno=’l’;
例29
查询选修1号课程的学生最高分数。


SELECT
MAX（Grade）
FROM
SC

WHERE
Cno=’ 1’;
五、对查询结果分组
目的：细化集函数的作用对象。
如果未对查询结果分组，集函数将作用于整个查询结果．
分组后集函数将作用于每一个组，即每一组都有一个函数值。
例30求各个课程号及相应的选课人数。

SELECT Cno，COUNT（Sno）

FROM
SC

GROUP BY Cno；
求上例选修人数超过30人的课号及相应人数:
SELECT
Cno，COUNT（Sno）

FROM
SC

GROUP
BY
Cno
HAVING
COUNT(Sno)>30
例31查询选修了3门以上课程的学生学号。

SELECT
Sno

FROM
SC

GROUP
BY
Sno

HAVING
COUN T（*）＞3；
WHERE与HAVING 的区别：

作用对象不同。WHERE作用于基本表或视图，从中选择满足条件的元组。HAVING作用于组，从中选择满足条件的组。
3．3．2连接查询
关系数据库中最主要的查询，包括等值连接、自然连接、非等值连接、自身连接、外连接和复合条件连接查询。
一、等值与非等值连接查询
例32查询每个学生及其选修课程的情况。

SELECT
Student.* ，SC.*

FROM
Student，
SC
WHERE
Student.Sno＝SC.Sno；


假设Student表、SC表分别有下列数据：

Sno	Sname	Ssex	Sage	Sdept
95001	李勇	男	20	CS
95002	刘晨	女	19	IS
95003	王敏	女	18	MA
95004	张立	男	19	IS

Sno	Cno	Grade
95001	1	92
95001	2	85
95001	3	88
95002	2	90
95002	3	80

该查询的执行结果为：

Student.Sno	Sname	Ssex	Sage	Sdept	Sno	Cno	Grade
95001	李勇	男	20	CS	95001	1	92
95001	李勇	男	20	CS	95001	2	85
95001	李勇	男	20	CS	95001	3	88
95002	刘晨	女	19	IS	95002	2	90
95002	刘晨	女	19	IS	95002	3	80

两种特殊情况：自然连接，广义笛卡尔积（连接）.

两个表的广义笛卡尔积即是两表中元组的交叉乘积，结果会产生一些没有意义的元组，实际很少使用．

若在等值连接中把目标列中重复的属性列去掉则为自然连接.
例33 对例32用自然连接完成．
SELECT
Student.Sno，Sname, Ssex,Sage,Sdept, Cno,Grade

FROM
Student，
SC
WHERE
Student.Sno=SC.Sno；
二、自身连接
连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接，称为表的自身连接。

例34 查询每一门课的间接先修课（即先修课的先修课）

为COURSE 表取两个别名，FIRST、SECOND。
FIRST表

CNO	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4

SECOND表

CNO	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4

完成该查询的SQL语句为:
SELECT
FIRST.Cno,
SECOND.Cpno
FROM
Course
FIRST,
Course
SECOND
WHERE
FIRST.Cpno=SECOND.Cno;
结果为:

CNO	Cpno
1	7
3	5
5	6

piaoxue2002

好文章，怎么没有人欣赏呢。

cdg510

留个脚印 慢慢欣赏

coolqoo

好文章.顶一下.支持楼主.

宝坻洋货市场

好好学习天天向上