关系数据库标准语言SQL
之后的笔记戳这里,虽然实际项目中用不到这么多 sql 的知识点,但是记录一下,了解一下也OK。
【数据库系统设计】关系数据库标准语言SQL(2)
【数据库系统设计】关系数据库标准语言SQL(3)
SQL介绍
SQL(Structured Query Language)
结构化查询语言,是关系数据库的标准语言
SQL的特点
综合统一
- 由三部分组成:
数据定义语言(DDL)
数据操纵语言(DML)
数据控制语言(DCL) - 可以独立完成数据库生命周期中的全部活动
高度非过程化
- 非关系数据模型的数据操纵语言“面向过程”,必须指定存取路径
- SQL只要提出“做什么”,无须了解存取路径
- 存取路径的选择以及 SQL 的操作过程由系统自动完成
面向集合的操作方式
- 非关系数据模型采用面向记录的操作方式,操作对象是一条记录
- SQL 采用集合操作方式
- 操作对象、查找结果可以是元组的集合
- 一次插入、删除、更新操作的对象可以是元组的集合
以同一种语法结构提供多种使用方式
- SQL是独立的语言,能够独立地用于联机交互的使用方式
- SQL又是嵌入式语言
SQL能够嵌入到高级语言(例如C,C++,Java)程序中,供程序员设计程序时使用
语言简洁 ,易学易用
SQL功能极强,完成核心功能只用了 9 个动词
数据查询:SELECT
数据定义:CREATE
,DROP
,ALTER
数据操纵:INSERT
,UPDATE
,DELETE
数据控制:GRANT
,REVOKE
SQL中基本概念
SQL支持关系数据库三级模式结构
基本表
- 本身独立存在的表
- SQL中一个关系就对应一个基本表
- 一个(或多个)基本表对应一个存储文件
- 一个表可以带若干索引
存储文件
- 逻辑结构组成了关系数据库的内模式
- 物理结构对用户是隐蔽的
视图
- 从一个或几个基本表导出的表
- 数据库中只存放视图的定义而不存放视图对应的数据
- 视图是一个虚表
- 用户可以在视图上再定义视图
示例:学生-课程 数据库
学生表:Student(Sno, Sname, Ssex, Sage, Sdept)
课程表:Course(Cno, Cname, Cpno, Ccredit)
学生选课表:SC(Sno, Cno, Grade)
Student 表:
Course 表:
SC 表:
数据定义
数据库中的各种“对象”
现代关系数据库管理系统提供了一个层次化的数据库对象命名机制:
- 一个数据库中可以建立多个模式
- 一个模式下通常包括多个表、视图和索引等数据库对象
SQL的数据定义功能:定义各种数据库的“对象”
- 模式定义
- 表定义
- 视图定义
- 索引定义
SQL 的数据定义语句
SCHEMA定义
注:和前面的,数据库的模式概念区别开
模式(Schema),来自于“ISOSQL标准”协议
- Schema 在 SQL数据库中相当于一个容器
- 数据库所有的对象如表、视图、索引、用户、存储过程、触发器等都位于容器内
- 创建SQL模式,就是定义一个存储空间
- 在商业DBMS中,大多都使用Database代替Schema
SQL模式的创建和撤销
创建
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
撤销
DROP SCHEMA <模式名> <CASCADE|RESTRICT>
CASCADE(级联)
删除模式的同时把该模式中所有的数据库对象全部删除RESTRICT(限制)
如果该模式中定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行
仅当该模式中没有任何下属的对象时才能执行
基本表定义(重点)
注:<> 代表必写项,[] 代表可以省略的项。
定义基本表(关系模式)
CREATE TABLE <表名> /* 基本表的名称 */
(<列名> <数据类型>[ <列级完整性约束条件> ] /*组成该表的列*/
[,<列名> <数据类型>[ <列级完整性约束条件>] ]
…
[,<表级完整性约束条件> ]);
<列级完整性约束条件>:涉及相应属性列的完整性约束条件
NOT NULL / [NULL]
:列值是否可以为空UNIQUE
:列值唯一,不得重复DEFAULT
:列值空缺时,由系统填写默认值
<表级完整性约束条件>:涉及一个或多个属性列的完整性约束条件
-
PRIMARY KEY ( <列名表> )
作用:提供实体完整性约束的说明
主码为单属性时,可直接在属性后的列级完整性约束条件中,使用PRIMARY KEY
定义主码 -
Foreign key [外码名] (<列名表1>) references <主表名> [(列名表2)]
提供参照完整性约束的说明
通过CHECK (约束表达式)
对某元组某属性取值的约束说明
如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上
数据类型
- 定义表的属性时需要指明其数据类型及长度
CREATE TABLE Student( Sno CHAR(9) );
中的CHAR(9)
- 不同的关系数据库管理系统支持的数据类型不完全相同
例如 mysql 与 Oracle 数据库的数据类型有区别
定义基本表示例
建立“学生表” Student。学号是主码,姓名取值唯一。
Student(Sno, Sname, Ssex, Sage, Sdept)
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY, /*列级完整性约束条件,Sno是主码*/
Sname CHAR(20) UNIQUE, /*UNIQUE约束,Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
建立一个“课程”表 Course。
Course(Cno, Cname, Cpno, Ccredit)
CREATE TABLE Course(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
/*Cpno是外码,被参照表是Course,被参照列是Cno*/
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
建立一个学生选课表 SC。
SC(Sno, Cno, Grade)
CREATE TABLE SC(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
/*主码由两个属性构成,必须作为表级完整性进行定义*/
PRIMARY KEY (Sno,Cno),
/*表级完整性约束条件,Sno是外码,被参照表是Student*/
FOREIGN KEY (Sno) REFERENCES Student(Sno),
/*表级完整性约束条件,Cno是外码,被参照表是Course*/
FOREIGN KEY (Cno)REFERENCES Course(Cno),
/*检测输入成绩只能在0~100之间*/
Check ( Grade Between 0 And 100)
);
修改基本表
ALTER TABLE <表名> /*<表名>是要修改的基本表*/
[ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ADD <表级完整性约束>]
[DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
ADD
子句:
- 增加新列
- 新的列级完整性约束条件
- 新的表级完整性约束条件
DROP COLUMN
子句:用于删除表中的列
- 如果指定了
CASCADE
短语
则自动删除引用了该列的其他对象 - 如果指定了
RESTRICT
短语
则如果该列被其他对象引用,关系数据库管理系统将拒绝删除该列
DROP CONSTRAINT
子句:
- 用于删除指定的完整性约束条件
ALTER COLUMN
子句:
- 用于修改原有的列定义,包括修改列名和数据类型
修改基本表示例
Student 表中,增加“入学时间(S_entrance )”列,数据类型为日期型(DATE)。
/*不管基本表中原来是否已有数据,新增加的列一律为空值*/
ALTER TABLE Student ADD S_entrance DATE;
Student 表中,将年龄(Sage)的数据类型(假设原来是字符型)改为整数(INT)。
ALTER TABLE Student ALTER COLUMN Sage INT;
Course 表中,增加课程名称(Cname)必须取唯一值(UNIQUE)的约束条件。
ALTER TABLE Course ADD UNIQUE(Cname);
删除基本表
DROP TABLE <表名>[RESTRICT| CASCADE];
RESTRICT
:删除表是有限制的。
- 欲删除的基本表不能被其他表的约束所引用
- 如果存在依赖该表的对象,则此表不能被删除
CASCADE
:删除该表没有限制。
- 在删除基本表的同时,相关的依赖对象一起删除
删除基本表示例
删除 Student 表。
/*由于使用了CASCADE*/
/*基本表定义被删除,数据被删除*/
/*表上建立的索引、视图、触发器等一般也将被删除*/
DROP TABLE Student CASCADE;
不同RDBMS的处理策略比较
DROP TABLE
时,SQL2011 与 3个 RDBMS 的处理策略比较
索引的定义
建立索引的目的:加快查询速度
关系数据库管理系统中常见索引:
- 顺序文件上的索引
- B+树索引
- 散列(hash)索引
- 位图索引
概念理解:
- 属于内模式范畴
- RDBMS通常在主码上自动建立索引
- 查询、更新时自动起作用(适当建立索引会提高查询速度)
- 索引属性值和相应的元组指针
数据库管理员 或 表的属主(即建立表的人)可以建立索引。
索引的维护由关系数据库管理系统自动完成。
关系数据库管理系统自动选择合适的索引作为存取路径,用户不必也不能显式地选择索引。
建立索引与示例
/*<表名>:要建索引的基本表的名字*/
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
/*索引:可以建立在该表的一列或多列上,各列名之间用逗号分隔*/
ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);
<次序>:指定索引值的排列次序
升序:ASC
,降序:DESC
。缺省值 :ASC
UNIQUE
:此索引的每一个索引值只对应唯一的数据记录
CLUSTER
:表示要建立的索引是聚簇索引 — 会改变数据记录的物理顺序使之与索引项值的排列顺序相同,一个表只能建立一个聚簇索引。
示例:
为学生-课程数据库中的 Student,Course,SC 三个表建立索引。Student 表按学号升序建唯一索引,Course 表按课程号升序建唯一索引,SC 表按学号升序和课程号降序建唯一索引 。
/*UNIQUE可省略*/
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
修改索引与示例
ALTER INDEX <旧索引名> RENAME TO <新索引名>
将 SC 表的 SCno 索引名改为 SCSno。
ALTER INDEX SCno RENAME TO SCSno;
删除索引与示例
删除索引时,系统会从数据字典中删去有关该索引的描述。
DROP INDEX <索引名>;
例:删除 Student 表的 Stusname索引
DROP INDEX Stusname;
数据字典
数据字典是关系数据库管理系统内部的一组系统表,它记录了数据库中所有定义信息:
- 关系模式定义
- 视图定义
- 索引定义
- 完整性约束定义
- 各类用户对数据库的操作权限
- 统计信息等
关系数据库管理系统在执行 SQL 的数据定义语句时,实际上就是在更新数据字典表中的相应信息。
数据查询(重点中的重点)
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]|(SELECT 语句) [AS] <别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
SELECT
:指定要显示的属性列
FROM
:指定查询对象(基本表或视图)
WHERE
:指定查询条件
GROUP BY
:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING
:只有满足指定条件的组才予以输出
ORDER BY
:对查询结果表按指定列值的升序或降序排序
数据查询示例
不带条件的查询(没有WHERE字句)
Ex:Student 表中,查询全体学生的学号(Sno)与姓名(Sname)。
SELECT Sno,Sname FROM Student;
Ex:Student 表中,查询全体学生的详细记录。
SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student;
/*上下两种写法都可以*/
SELECT * FROM Student; /* *表示所有信息的*/
Ex:Student 表中,查询全体学生的姓名(Sname)及其出生年份。
出生年份即当前年份减去年龄:2014-Sage,所以 sql 查询可以写表达式。
SELECT Sname, 2014-Sage FROM Student;
Ex:Student 表中,查询全体学生的姓名(Sname)、出生年份和所在的院系(Sdept),要求用小写字母表示系名。
SELECT Sname, 'Year of Birth: ', 2014-Sage, LOWER(Sdept)
FROM Student;
Ex:使用别名改变上一个查询结果的列标题。
SELECT
Sname NAME,
'Year of Birth:' BIRTH,
2014-Sage BIRTHDAY,
LOWER(Sdept) DEPARTMENT
FROM Student;
Ex:查询选修了课程的学生学号(Sno)。
如果没有指定DISTINCT
关键词,则缺省为ALL
SELECT Sno FROM SC;
通过指定DISTINCT
消除取值重复的行。
SELECT DISTINCT Sno FROM SC;
带条件的查询(有WHERE字句)
常用的查询条件:
- 比较:
=
,>
,<
,>=
,<=
,!=
,<>
,!>
,!<
;NOT+上述比较运算符
- 确定范围:
BETWEEN AND
,NOT BETWEEN AND
- 确定集合:
IN
,NOT IN
- 字符匹配:
LIKE
,NOT LIKE
- 空值:
IS NULL
,IS NOT NULL
- 多重条件(逻辑运算):
AND
,OR
,NOT
比较大小 >
、<
、=
Ex:查询计算机科学系全体学生的名单。
SELECT Sname FROM Student WHERE Sdept='CS';
Ex:查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname, Sage
From Student
WHERE Sage<20;
Ex:查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno From SC WHERE Grade<60;
确定范围 BETWEEN AND
BETWEEN … AND …
NOT BETWEEN … AND …
Ex:查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 30;
Ex:查询年龄不在20~23岁之间的学生姓名、系别和年龄。
SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
确定集合 IN
、NOT IN
IN <值表>, NOT IN <值表>
Ex:查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS', 'MA', 'IS');
Ex:查询既不是计算机科学系、数学系,也不是信息系的学生的姓名和性别。
SELECT Sname, SSex
FROM Student
WHERE Sdept NOT IN ('CS', 'MA', 'IS');
字符匹配 LIKE
、NOT LIKE
[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
<匹配串>可以是一个完整的字符串,也可以含有通配符%
和_
%
代表任意长度(长度可以为0)的字符串。
例如 a%b 表示以 a 开头,以 b 结尾的任意长度的字符串_
代表任意单个字符。
例如 a_b 表示以 a 开头,以 b 结尾的长度为3的任意字符串
Ex:查询学号为201215121的学生的详细情况。
SELECT * FROM Student
WHERE Sno LIKE ‘201215121';
上下两种写法是等价的。
SELECT * FROM Student
WHERE Sno='201215121';
Ex:查询所有姓刘学生的姓名、学号和性别。
SELECT Sname, Sno, Ssex FROM Student
WHERE Sname LIKE '刘%';
Ex:查询姓 “欧阳” 且全名为三个汉字的学生的姓名。
SELECT Sname FROM Student
WHERE Sname LIKE '欧阳__';
Ex:查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT Sname, Sno FROM Student
WHERE Sname LIKE '_阳%';
Ex:查询所有不姓刘的学生姓名、学号和性别。
SELECT Sname, Sno, SSex FROM Student
WHERE Sname NOT LIKE '刘%';
Ex:查询 DB_Design 课程的课程号和学分。
使用换码字符将通配符_
转义为普通字符。
ESCAPE ‘\’ 表示 “\” 为换码字符
SELECT Cno, Ccredit FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
Ex:查询以 “DB_” 开头,且倒数第3个字符为 i 的课程的详细情况。
SELECT * FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';
判断空值 IS NULL
、IS NOT NULL
Ex:某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
IS
不能用 =
代替
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
Ex:查所有有成绩的学生学号和课程号。
SELECT Sno, Cno
FROM SC
WHERE GRADE IS NOT NULL;
逻辑运算符:AND
和OR
来连接多个查询条件
AND
的优先级高于OR
,可以用括号改变运算顺序。
Ex:查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname From Student
WHERE Sdept IN ('SC') AND Sage < 20;
/*两种写法等价*/
SELECT Sname From Student
WHERE Sdept = 'CS' AND Sage < 20;
Ex:查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。
以下两种写法等价。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS')
SELECT Sname, Ssex
FROM Student
WHERE Sdept= 'CS' OR Sdept= 'MA' OR Sdept= 'IS';
ORDER BY
子句
可以按一个或多个属性列排序
升序:ASC
;
降序:DESC
;
默认为升序ASC
;
Ex:查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECT Sno, Grade FROM SC
WHERE Cno = '3'
ORDER BY Grade DESC;
Ex:查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT * FROM Student
ORDER BY Sdept ASC, Sage DESC;
/*ASC可以省略,默认为ASC*/
SELECT * FROM Student
ORDER BY Sdept, Sage DESC;
聚集查询
COUNT(*)
:统计元组个数
COUNT([DISTINCT|ALL] <列名>)
:统计一列中值的个数
SUM([DISTINCT|ALL] <列名>)
:计算一列值的总和(此列必须为数值型)
AVG([DISTINCT|ALL] <列名>)
:计算一列值的平均值(此列必须为数值型)
MAX([DISTINCT|ALL] <列名>)
:计算一列中的最大值
MIN([DISTINCT|ALL] <列名>)
:计算一列中的最小值
Ex:查询学生总人数。
SELECT COUNT(*) FROM Student;
Ex:查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno) FROM SC;
Ex:计算1号课程的学生平均成绩。
SELECT AVG(Grade) FROM SC WHERE Cno = 1;
Ex:查询选修1号课程的学生最高分数。
SELECT MAX(Grade) FROM SC WHERE Cno = '1';
Ex:查询学号为 201215121 的学生,选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC, Course
WHERE Sno = '201215121' AND SC.Cno=Course.Cno;
GROUP BY
子句、HAVING
短语
GROUP BY
子句分组:细化聚集函数的作用对象
- 如果未对查询结果分组,聚集函数将作用于整个查询结果
- 对查询结果分组后,聚集函数将分别作用于每个组
- 按指定的一列或多列值分组,值相等的为一组
先来看一段 sql:
SELECT Cno, COUNT(Sno) FROM SC;
这段 sql 的意思是,查询出课程号,以及总人数。
????,很明显这是个没什么卵用的需求,再看看下面这个例子。
Ex:求各个课程号及相应的选课人数。
SELECT Cno, COUNT(Sno) FROM SC
GROUP BY Cno;
通过GROUP BY
将 Cno 分组以后,再查询人数后,即可达到效果。
Ex:查询选修了2门以上课程的学生学号。
SELECT Sno FROM SC
GROUP BY Sno HAVING COUNT(*) >= 2;
Ex:查询平均成绩大于等于80分的学生学号和平均成绩。
以下语句是错误的:
SELECT Sno, AVG(Grade)
FROM SC
WHERE AVG(Grade) > 80
GROUP BY Sno;
甚至无法执行,会报错
因为WHERE
子句中是不能用聚集函数作为条件表达式
正确的查询语句应该是:
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 80;
HAVING
短语与WHERE
子句的区别:
- 作用对象不同
WHERE
子句作用于基表或视图,从中选择满足条件的元组HAVING
短语作用于组,从中选择满足条件的组
应用示例
有以下四张表:
C(CNO,CNAME,Credit,CreditHours,CPNO,TNO)
S(SNO,SNAME,AGE,SEX,NativePlace)
T(TNO,TNAME,TITLE,SEX)
SC(SNO,CNO,Grade)
(1) 统计每门课程的学生选修人数,要求显示课程号、课程名和学生人数
SELECT C.CNO, CNAME, COUNT(Sno) as 学生人数
FROM S, SC
WHERE C.Cno = SC.Cno
GROUP BY C.Cno, Cname;
(2) 按教师号统计每位教师每门课程的学生选修人数,要求:
– 1)仅显示选修人数在3人(>=3)以上的信息
– 2)显示 TNO、CNO 和选修人数
– 3)显示时,查询结果按选修人数降序排列,人数相同按TNO升序、 CNO降序排列
Select Tno, C.Cno, COUNT(Sno) as 选修人数
From C, SC Where C.Cno = SC.Cno
Group By Tno, C.Cno
Having COUNT(*)>=3
Order By 3 DESC, Tno, C.Cno DESC