目录
SQL应用情况:市面上大多数数据库都是用SQL语言,有些数据库有扩展
SQL支持三级模式:外模式是自己看到的,模式是表,内模式为存储文件
在PostgreSQL服务器中,执行SQL语句创建数据库关系表Course
在PostgreSQL中,执行SQL语句创建数据库关系表Plan
7.表约束定义代理键:代理键,没有实际意义的键,只是一个序号
在PostgreSQL中,执行SQL语句创建数据库关系表Plan
在PostgreSQL中,执行SQL语句创建数据库关系表Register。
2.主要语句类型:ADD增,DROP删,RENAME改名,ALTER COLUMN改类型
第3章 结构化查询语言---SQL语言:目录
1、了解SQL语言的特点
2、理解SQL语言的功能
3、掌握SQL语言创建数据库、表、视图、索引
4、掌握SQL语言的数据操纵语言
5、掌握SQL语言的查询语句
6、掌握SQL语言控制管理数据库对象
7、在PostgreSQL环境下进行项目实践
SQL简介:结构化查询语言
SQL:Structured Query Language,
结构化查询语言,它是一种对数据库进行访问与操作的数据子语言。
SQL可嵌入到编程语言(如JAVA、C#等)中执行或在DBMS工具中执行。
SQL不仅仅是一个查询工具,还用于控制DBMS提供给用户的所有功能,包括:
- 数据定义(Data definition)
- 数据检索(Data retrieval)
- 数据操纵(Data manipulation)
- 存取控制(Access control)
- 数据共享(Data sharing):
- 数据完整性(Data integrity)
SQL发展过程:了解即可
20世纪70年代由IBM公司开发
1986 ANSI的数据库委员会批准了SQL作为关系型数据库语言的美国标准。
1987年国际标准化组织(ISO)将其采纳为国际标准SQL86。
1989年更新成为国际标准SQL-89
1992年更新成为国际标准SQL-92(或称SQL2)
1999年更新成为国际标准SQL-99(或称SQL3)
2008年更新成为国际标准SQL2008
SQL应用情况:市面上大多数数据库都是用SQL语言,有些数据库有扩展
绝大多数主流的关系型数据库管理系统采用了SQL语言标准
- Oracle
- Sybase
- DB2
- Microsoft SQL Server
- Access
- MySql
- …….
其中一些数据库管理系统对SQL语句进行了再开发和扩展,
如Sybase、Microsoft SQL Server将操作语言扩展为Transaction-SQL;
Oracle将操作语言扩展为PL/SQL。
SQL能做什么?:增删查改,建新库表等
- SQL 可从数据库取回数据
- SQL 可在数据库中插入新的纪录
- SQL 可更新数据库中的数据
- SQL 可从数据库删除记录
- SQL 可创建新数据库
- SQL 可在数据库中创建新表
- SQL 可在数据库中创建存储过程
- SQL 可在数据库中创建视图
- SQL 可以设置表、存储过程和视图的权限
SQL语句类别:增删查改和其它
1. 数据操纵语言:增删查改
(Data Manipulation Language,DML)是SQL语言中用于查询和增删修改表数据的语句
- SELECT - 从数据库表中获取数据
- UPDATE - 更新数据库表中的数据
- DELETE - 从数据库表中删除数据
- INSERT - 向数据库表中插入数据
2. 数据定义语言:创删表等
(Data Definition Language,DDL)是SQL语言中用于创建或删除表。也可以定义索引(键),规定表之间间的约束。
- CREATE DATABASE - 创建新数据库
- ALTER DATABASE - 修改数据库
- CREATE TABLE - 创建新表
- ALTER TABLE - 变更(改变)数据库表
- DROP TABLE - 删除表
- CREATE INDEX - 创建索引(搜索键)
- DROP INDEX - 删除索引
3. 数据控制语言:权限
(Data Control Language,DCL)是SQL语言中用于对数据访问
权进行控制的语句。
- GRANT – 授予用户对数据对象的权限
- DENY – 拒绝授予用户对数据对象的权限
- REVOKE – 撤消用户对数据对象的权限
4. 事务处理语言:事物
(Transaction Process Language,TPL)是SQL语言中用于数据库内部事务处理的语句。
BEGIN TRANSACTION – 开始事务
COMMIT – 提交事务
ROLLBACK – 回滚事务
5. 游标控制语言:游标
(Cursor Control Language,CCL)是SQL语言中用于数据库
游标操作的语句。
DECLARE CURSOR – 定义游标
FETCH INTO – 提交游标数据
CLOSE CURSOR– 关闭游标
SQL支持三级模式:外模式是自己看到的,模式是表,内模式为存储文件
SQL特点:同意,非过程,集合,简单
综合统一:统一不同类型的SQL语句
① 集DDL、DML、DCL的功能于一体
② 可以在运行后根据需要随时修改模式
③ 数据操作符统一
高度非过程化:高层不考虑低层
① 只需提出“做什么”,而无需指明“怎么做”。
② 无需了解存取路径,存取路径的选择以及SQL语句的操作过程由系统自动完成
面向集合的操作方式:集合化
操作对象和结果均为集合
语言简洁,易学易用,核心功能只需9个动词:简单易用
① DDL:CREATE、DROP、ALTER
② DML: SELECT、INSERT、UPDATE、DELETE
③ DCL:GRANT、REVOKE
PostgreSQL的数据类型
(1)数字数据类型用于指定表中的数字数据
名称 | 描述 | 存储大小 | 范围 |
smallint | 存储整数,小范围 | 2字节 | -32768 至 +32767 |
integer | 存储整数。使用这个类型可存 储典型的整数 | 4字节 | -2147483648 至 +2147483647 |
bigint | 存储整数,大范围。 | 8字节 | -9223372036854775808 至 9223372036854775807 |
decimal | 用户指定的精度,精确 | 变量 | 小数点前最多为131072个数字; 小数点后 最多为16383个数字。 |
numeric | 用户指定的精度,精确 | 变量 | 小数点前最多为131072个数字; 小数点后 最多为16383个数字。 |
real | 可变精度,不精确 | 4字节 | 6位数字精度 |
double | 可变精度,不精确 | 8字节 | 15位数字精度 |
serial | 自动递增整数 | 4字节 | 1 至 2147483647 |
bigserial | 大的自动递增整数 | 8字节 | 1 至 9223372036854775807 |
(2)字符串数据类型
数据类型 | 描述 |
char(size) | size是要存储的字符数。固定长度字符串,右边的空格填充到相等大小的字符。 |
character(size) | size是要存储的字符数。 固定长度字符串。 右边的空格填充到相等大小的字符。 |
varchar(size) | 这里size是要存储的字符数。 可变长度字符串。 |
character varying(size) | 这里size是要存储的字符数。 可变长度字符串。 |
text | 可变长度字符串。 |
(3)日期/时间数据类型
名称 | 描述 | 存储大小 | 最小值 | 最大值 | 精度 |
timestamp [ (p) ] | 日期和时间, | 8字节 | 4713 BC | 294276 AD | 1微秒 |
date | 日期 | 4字节 | 4713 BC | 5874897 AD | 1天 |
time [ (p) ] | 时间(无日期) | 8字节 | 0:00:00 | 24:00:00 | 1微秒 |
interval [ fields ] [ (p) ] | 时间间隔 | 12字节 | -178000000年 | 178000000年 | 1微秒 |
(4)其他数据类型
名称 | 描述 | 存储大小 |
money | 货币金额 | 8字节 |
boolean | 它指定true或false的状态。 | 1字节 |
一、创建数据库
PostgreSQL 创建数据库可以用以下三种方式:
1、使用 CREATE DATABASE SQL 语句来创建。
2、使用 createdb 命令来创建。
3、使用 pgAdmin 工具。
一、数据库创建SQL语句
1. 语句基本格式
CREATE DATABASE <数据库名>;
例 执行SQL语句创建一个选课管理数据库CourseDB。
CREATE DATABASE CourseDB;
2.语句执行
在PostgreSQL服务器中,执行SQL语句创建数据库CourseDB。
查看新建的数据库
3、使用 pgAdmin 工具的图形化界面
二、数据库修改SQL语句
1.语句基本格式:ALTER修改
ALTER DATABASE <数据库名> <修改内容>;
例 将选课管理数据库CourseDB名称修改为 CourseManageDB
ALTER DATABASE CourseDB RENAME TO CourseManageDB;
2.语句执行
在PostgreSQL服务器中,执行SQL语句修改数据库CourseDB名称。
三、数据库删除SQL语句
1. 语句基本格式:DROP删除
DROP DATABASE <数据库名>;
例 删除前面创建的选课管理数据库CourseManageDB
DROP DATABASE CourseManageDB;
2.语句执行
在PostgreSQL服务器中,执行SQL语句删除数据库CourseManageDB。
二、数据库表创建SQL语句
1.语句基本格式:CREATE TABLE创建表
CREATE TABLE <表名>
(<列名1> <数据类型> [列完整性约束],
<列名2> <数据类型> [列完整性约束],
<列名3> <数据类型> [列完整性约束],
…
);
2. 列完整性约束
列完整性约束关键词
- PRIMARY KEY——主键
- NOT NULL——非空值
- NULL——空值
- UNIQUE——值唯一:作为候选键,选完主键以后,要求唯一的候选键
- CHECK——有效性检查:避免低级错误
- DEFAULT——缺省值
3. SQL语句应用
例 选课管理数据库的学生表Student设计需求
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 |
学号 | StudentID | 文本 | 13 | 是 | 主键 |
姓名 | StudentName | 文本 | 10 | 是 | 否 |
性别 | StudentGender | 文本 | 2 | 否 | 否 |
出生日期 | BirthDay | 日期 | 短日期 | 否 | 否 |
专业 | Major | 文本 | 30 | 否 | 否 |
手机号 | StudentPhone | 文本 | 11 | 否 | 否 |
创建学生表Student的SQL语句
CREATE TABLE Student
(StudentID char(13) PRIMARY KEY,
StudentName varchar(10) NOT NULL,
StudentGender char(2) NULL,
BirthDay date NULL,
Major varchar(30) NULL,
StudentPhone char(11) NULL
);
在PostgreSQL服务器中,执行SQL语句创建数据库关系表Student
5.其它的列完整性约束应用
在关系表创建SQL语句中,除了使用主键约束、是否空值约束外。有时还会使用如下约束。
- UNIQUE——值唯一
- CHECK——有效性检查
- DEFAULT——缺省值 :没指定的情况下,默认为缺省值
例 选课管理系统数据库的课程信息表Course设计需求
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 | 备注 |
课程编号 | CourseID | 文本 | 4 | 是 | 主键 | |
课程名 | CourseName | 文本 | 20 | 是 | 否 | 取值唯一 |
课程类别 | CourseType | 文本 | 10 | 否 | 否 | “基础 课”、 “专业 课”、 “选修课” |
学分 | CourseCredit | 数字 | 短整型 | 否 | 否 | |
学时 | CoursePeriod | 数字 | 短整型 | 否 | 否 | |
考核方式 | TestMethod | 文本 | 10 | 否 | 否 | 缺省值 “闭卷考 试” |
创建课程信息表Course的SQL语句
CREATE TABLE Course
(CourseID char(4) PRIMARY Key,
CourseName varchar(20) NOT NULL UNIQUE,
CourseType varchar(10) NULL CHECK(CourseType IN('基础课','专业课','选修课')),
CourseCredit smallint NULL,
CoursePeriod smallint NULL,
TestMethod char(4) NOT NULL DEFAULT '闭卷考试'
);
在PostgreSQL服务器中,执行SQL语句创建数据库关系表Course
6.表约束定义主键:复合主键,多个主键,约束名自己起名
使用列 约束关 键词PRIMARY KEY定 义 表 的 主 键 列 只 能 定 义 单 列 主 键,
若要定义由多个列构成的复合主键,则需要使用表约束方式来定义。
CREATE TABLE <表名>
( <列名1> <数据类型> [列完整性约束],
<列名2> <数据类型> [列完整性约束],
<列名3> <数据类型> [列完整性约束],
…
CONSTRAINT <约束名> PRIMARY Key(主键列)
);
例:选课管理系统数据库的开课计划表Plan设计需求
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 |
课程编号 | CourseID | 文本 | 4 | 是 | 主键 |
教师编号 | TeacherID | 文本 | 4 | 是 | 主键 |
地点 | CourseRoom | 文本 | 30 | 否 | 否 |
时间 | CourseTime | 文本 | 30 | 否 | 否 |
备注 | Note | 文本 | 50 | 否 | 否 |
创建开课计划表Plan的SQL语句
CREATE TABLE Plan
(
CourseID char(4) NOT NULL,
TeacherID char(4) NOT NULL,
CourseRoom varchar(30),
CourseTime varchar(30),
Note varchar(50),
CONSTRAINT CoursePlan_PK PRIMARY Key(CourseID,TeacherID)
);
在PostgreSQL中,执行SQL语句创建数据库关系表Plan
使用表约束定义主键的优点:
- 便于定义复合主键
- 可命名主键约束
- 便于定义代理键
7.表约束定义代理键:代理键,没有实际意义的键,只是一个序号
在一些关系表中,为了方便数据处理,可以使用代理键去替代复合主键。
在SQL语句中,关系表的代理键采用表约束方式来定义。
CREATE TABLE <表名>
( <代理键列名> <Serial数据类型> NOT NULL,
<列名2> <数据类型> [列完整性约束],
<列名3> <数据类型> [列完整性约束],
…
CONSTRAINT <约束名> PRIMARY Key(代理键列名)
);
例 选课管理系统数据库的开课计划表Plan设计需求
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 |
开课编号 | CoursePlanID | 自动编号 | 长整型 | 是 | 代理键 |
课程编号 | CourseID | 文本 | 4 | 是 | 否 |
教师编号 | TeacherID | 文本 | 4 | 是 | 否 |
地点 | CourseRoom | 文本 | 30 | 否 | 否 |
时间 | CourseTime | 文本 | 30 | 否 | 否 |
备注 | Note | 文本 | 50 | 否 | 否 |
创建开课计划表Plan的SQL语句
CREATE TABLE Plan
( CoursePlanID serial NOT NULL,
CourseID char(4) NOT NULL,
TeacherID char(4) NOT NULL,
CourseRoom varchar(30),
CourseTime varchar(30),
Note varchar(50),
CONSTRAINT CoursePlan_PK PRIMARY Key(CoursePlanID)
);
在PostgreSQL中,执行SQL语句创建数据库关系表Plan
8.表约束定义外键:外键,其它表的主键
在数据库中,一些关系表之间存在关联。在一个表中作为主键的列,在另外的关联表中则作为外键。
CREATE TABLE <表名>
( <列名1> <数据类型> [列完整性约束],
<列名2> <数据类型> [列完整性约束],
<列名3> <数据类型> [列完整性约束],
…
CONSTRAINT <约束名> FOREIGN Key(外键列)
);
例 选课管理系统数据库的注册表Register设计需求
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 |
注册编号 | CourseRegID | 自动编号 | 长整型 | 是 | 代理键 |
开课编号 | CoursePlanID | 数字 | 长整型 | 是 | 外键 |
学号 | StudentID | 文本 | 13 | 是 | 外键 |
备注 | Note | 文本 | 30 | 否 | 否 |
创建注册表Register的SQL语句
CREATE TABLE Register
( CourseRegID serial NOT NULL,
CoursePlanID Int NOT NULL,
StudentID char(13),
Note varchar(30),
CONSTRAINT CourseRegID_PK PRIMARY Key(CourseRegID),
CONSTRAINT CoursePlanID_FK FOREIGN Key(CoursePlanID)
EFERENCES Plan(CoursePlanID) --当前表CoursePlanID参照Plan表的CoursePlanID
ON DELETE CASCADE, --级联删除,当在开课表中删除,注册表中相关信息一并删除
CONSTRAINT StudentID_FK FOREIGN KEY(StudentID)
REFERENCES Student(StudentID)
ON DELETE CASCADE
);
在PostgreSQL中,执行SQL语句创建数据库关系表Register。
![](https://i-blog.csdnimg.cn/blog_migrate/824386a58dbdbcc7b35530cec739d55c.png)
二、修改表结构SQL语句:ALTER TABLE修改
1.语句基本格式:ALTER TABLE修改
ALTER TABLE <表名> <修改方式>;
2.主要语句类型:ADD增,DROP删,RENAME改名,ALTER COLUMN改类型
1)ADD修改方式,用于增加新列或列完整性约束
ALTER TABLE <表名> ADD <新列名称><数据类型>|[完整性约束]
2)DROP修改方式,用于删除指定列或列的完整性约束条件
ALTER TABLE<表名> DROP COLUMN <列名>;
ALTER TABLE<表名> DROP CONSTRAINT<完整性约束名>;
3)RENAME修改方式,用于修改表名称、列名称
ALTER TABLE <表名> RENAME TO <新表名>;
ALTER TABLE <表名> RENAME <原列名> TO <新列名>;
4)ALTER修改方式,用于修改列的数据类型
ALTER TABLE <表名> ALTER COLUMN <列名> TYPE<新的数据类型>;
3.表修改举例
学生表Student原有结构及数据
例 在学生表Student中,新增一个“email”列
学生表Student修改后结构及数据
三、删除表结构SQL语句:DROP TABLE删除
1.语句基本格式:DROP TABLE删除
DROP TABLE <表名>;
注意: 该语句将删除该表的所有数据及其结构
2.表删除举例
例: 删除注册表Register表及其数据,可以使用如下语句。
DROP TABLE Register;
例 执行SQL语句删除注册表Register
3.2 数据定义SQL语句:增删查改
——数据库索引创建、修改、删除
一、什么是索引
索 引 (Index )是一种按照关系表中指定列的取值顺序组织元组数据存储的数据结构,使用它可以加快表中数据的查询访问。
二、索引作用及特点
索引作用:支持对数据库表中数据快速查找,其机理类似图书目录可以快速定位章节内容。
索引优点:
• 提高数据检索速度
• 可快速连接关联表
• 减少分组和排序时间
索引开销:
• 创建和维护索引都需要较大开销
• 索引会占用额外存储空间
• 数据操纵因维护索引带来系统性能开销
三、索引创建SQL语句
1.语句基本格式
CREATE INDEX<索引名>ON <表名><(列名)>;
2.索引创建实例
例 在学生信息表Student中,为出生日期Birthday列创建索引,以便支持按出生日期快速查询学生信息。
CREATE INDEX Birthday_Idx ON STUDENT (Birthday);
3.索引创建SQL执行
四、索引修改SQL语句
1.语句基本格式
ALTER INDEX <索引名> <修改项>;
例如,索引名称修改语句格式如下:
ALTER INDEX <索引名> RENAME TO <新索引名>;
2.索引修改实例
例 在学生信息表Student中,将原索引Birthday_Idx更名为Bday_Idx,其索引修
改SQL语句如下:
ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;
3.索引修改SQL执行
五、索引删除SQL语句
1.语句基本格式
DROP INDEX <索引名> ;
2.索引删除实例
例 在学生信息表Student中,删除bday_idx索引,其索引删除SQL语句如下:
DROP INDEX bday_idx;
3.索引删除SQL执行
3.3数据操纵SQL语句
本节学习目标
掌握SQL语言的数据插入语句
掌握SQL语言的数据修改语句
掌握SQL语言的数据删除语句
一、数据插入SQL语句
1.语句基本格式
INSERT INTO <表名|视图名>[<列名表>] VALUES (列值表);
2.数据插入实例
例 在学生信息表Student中,插入一个新的学生数据,如:
“2017220101105”,“柳因”,“女”,“1999-04-23”,“软件工程”,“liuyin@163.com”。
INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程', 'liuyin@163.com');
3.数据插入SQL语句执行
4.多条数据插入
在数据库表插入操作中,还可以执行一组SQL数据插入语句,实现在表中多行数据插入。
例 在学生信息表STUDENT中,一次插入多个学生数据,其插入数据SQL
语句如下:
INSERT INTO Student VALUES('2017220101106',' 张 亮 ',' 男 ','1999-11-21',' 软 件 工 程','zhangl@163.com');
INSERT INTO Student VALUES('2017220101107',' 谢 云 ',' 男 ','1999-08-12',' 软 件 工 程','xiey@163.com');
INSERT INTO Student VALUES('2017220101108','刘亚','女','1999-06-20','软件工程',NULL);
5.多条数据插入SQL语句执行
6.数据插入结果查看
二、数据更新SQL语句
1.语句基本格式
UPDATE <表名|视图名>
SET <列名1>=<表达式1> [,<列名2>=<表达式2>...]
[WHERE <条件表达式>];
2.数据更新实例
例 在学生信息表Student中,学生“赵东”的原有Email数据为空,现需要修改为“zhaodong@163.com”。
UPDATE Student
SET Email='zhaodong@163.com'
WHERE StudentName='赵东';
3.数据更新SQL语句执行
4.数据更新结果查看
三、数据删除SQL语句
1.语句基本格式
DELETE
FROM <表名|视图名>
[WHERE <条件表达式>];
2.数据删除实例
例 在学生信息表STUDENT中,删除姓名为 “张亮”的学生数据,其数据删除的SQL语句如下:
DELETE
FROM STUDENT
WHERE StudentName='张亮';
3.数据删除SQL语句执行
4.数据删除结果查看
3.4数据查询SQL语句-单表数据查询
一、 数据查询SQL语句格式
SELECT [ALL|DISTINCT] <目标列>[,<目标列>…]
[ INTO <新表> ]
FROM <表名|视图名>[,<表名|视图名>…]
[ WHERE <条件表达式> ]
[ GROUP BY <列名> [HAVING <条件表达式> ]]
[ ORDER BY <列名> [ ASC | DESC ] ];
二、从单个表读取指定列
在关系数据库中,最简单的数据查询操作就是从单个关系表中读取指定列的数据,即关系的投影操作。
1.语句基本格式
SELECT <目标列>[,<目标列>…]
FROM <关系表>;
2.数据查询实例
例 学生信息表Student原始数据如下图所示。
要求:从Student表中读取学生的学号、姓名、专业列数据输出。
3.数据查询SQL语句及执行结果
若希望从Student表查询所有列数据,其数据查询SQL语句及执行结果如下:
若希望从Student表中查询专业列(Major)数据,其数据查询SQL语句及执行结果如下:
为了在结果集中过滤重复数据,可以在查询语句的输出列前加入DISTINCT关键字,其执行结果如下。
三、从单个表读取指定行
SQL查询语句也可以从一个关系表中读取满足条件的指定行数据,即完成关系数据的元组选择操作。
1.语句基本格式
SELECT *
FROM <关系表>
WHERE <条件表达式>;
2.数据查询实例
例 学生信息表Student原始数据如下图所示。
要求:从Student表中查询男生数据
3.数据查询SQL语句及执行结果
四、从单个表读取指定行和列
在SQL查询语句中,还可以从一个关系表中读取指定行与指定列范围内的数据。既完成关系的行选择,又完成关系的列投影操作。
1.语句基本格式
SELECT <目标列>[,<目标列>…]
FROM <关系表>
WHERE <条件表达式>;
2.数据查询实例
例 学生信息表Student原始数据如下图所示
要求:从Student表中查询性别为“男”的学生学号、学生姓名、性别、专业数据
3.数据查询SQL语句及执行结果
五、Where条件子句
在WHERE子句中可以使用如下方式,指定范围数据。
1 ) 使 用 BETWEEN .. AND 关 键 词 来 限 定 列 值 范 围 , 还 可 以 使 用 关 键 词LIKE与通配符来限定查询条件。
2)使用通配符来限定字符串数据范围。下划线(_)通配符用于代表一个未指定的字符。百分号(%)通配符用于代表一个或多个未指定的字符。
例1 若要从STUDENT表中查询出生日期在“2000-01-01”到“2000-12-30”的学生数据。其数据查询SQL语句如下:
SELECT *
FROM STUDENT
WHERE BirthDay BETWEEN '2000-01-01' AND '2000-12-30';
数据查询SQL语句及执行结果
例2 若要从STUDENT表中查询邮箱域名为“@163.com”的学生数据。其数据
查询SQL语句如下:
SELECT *
FROM STUDENT
WHERE Email LIKE '%@163.com';
数据查询SQL语句及执行结果
在SQL查询Where子句中,还可以使用多个条件表达式,
并通过逻辑运算符(AND、OR、NOT)连接操作,以及使用IN或NOT IN关键词,进一步限定结果集的数据范围。
例3 从STUDENT表中查询性别为“男”,并且专业为“软件工程”的学生数据,其数据查询SQL语句如下。
SELECT StudentID, StudentName, StudentGender, Major
FROM STUDENT
WHERE Major='软件工程' AND StudentGender='男';
数据查询SQL语句及执行结果
例4 在STUDENT表查询时,使用IN关键字限定范围”计算机应用”专业的学生。其SQL语句如下所示。
SELECT StudentID, StudentName, StudentGender, Major
FROM STUDENT
WHERE Major IN ('计算机应用');
数据查询SQL语句及执行结果
六、对结果集进行排序
在SELECT查询语句返回的结果集中,行的顺序是任意的。如果需要结果集排序,可以在SELECT语句中加入ORDER BY关键字。
例 若要从STUDENT表中按学生出生日期降序输出学生数据,其数据查询SQL语句如下。
SELECT *
FROM STUDENT
ORDER BY Birthday DESC;
在默认情况下,SQL查询的结果集是按指定列值的升序排列。可以使用关键词ASC和DESC选定排序是升序或降序。
数据查询SQL语句及执行结果
如果结果集需要按多个列排序,可以分别加入关键字ASC或DESC改变。
例 若要将STUDENT表查询数据,首先按出生日期降序排列,然后按姓名升序排列,其数据查询SQL语句如下
SELECT *
FROM STUDENT
ORDER BY Birthday DESC , StudentName ASC;
数据查询SQL语句及执行结果
3.4数据查询SQL语句—内置函数、分组统计
一、SQL内置函数类型
SQL语言提供了大量内置函数,支持对SELECT查询结果数据进行处理。
典型SQL内置函数类型如下:
- 聚合函数
- 算术函数
- 字符串函数
- 日期时间函数
- 数据类型转换函数
二、SQL聚合函数
聚合函数是一些对关系表中数值属性列进行计算并返回一个结果数值的函数。
聚合函数 | 功能 |
AVG() | 计算结果集指定列数据的平均值 |
COUNT() | 计算结果集行数 |
MIN() | 找出结果集指定列数据的最小值 |
MAX() | 找出结果集指定列数据的最大值 |
SUM() | 计算结果集指定列数据的总和 |
例 学生信息表Student原始数据如下图所示。
例1 若要统计Student表中的学生人数,在SELECT语句中可以使用COUNT()函数来计算,其查询SQL语句如下:
SELECT COUNT(*) AS 学生人数
FROM Student;
例2 找出STUDENT表中年龄最大和年龄最小的学生出生日期,其查询SQL语句如下:
SELECT Min(Birthday) AS 最大年龄,Max(Birthday) AS 最小年龄
三、SQL内置函数与分组统计
在SQL语言中,可使用内置函数对查询结果集进行分组数据统计。
这是通过在SELECT语句中加入Group By子语句来实现。
分组统计SQL语句基本格式:
SELECT 统计函数(目标列)
FROM <表名>
[WHERE 条件]
GROUP BY <目标列>
[Having 条件];
例 学生信息表Student原始数据如下图所示。
例1 若要分专业统计Student表中的学生人数。在SELECT语句中可以使用GROUP BY分组子句完成统计,其查询SQL语句如下:
SELECT Major AS 专业, COUNT(StudentID) AS 学生人数
FROM Student
GROUP BY Major;
例2 若要分专业统计STUDENT表中男生人数,但限定只显示人数大于2的人数,其查询SQL语句如下:
SELECT Major AS 专业, COUNT(StudentID) AS 学生人数
FROM Student
WHERE StudentGender='男'
GROUP BY Major
HAVING COUNT(*)>2;
3.4 数据查询SQL语句-多表关联查询
一、子查询与多表关联
在实际应用中,通常需要关联多表才能获得所需的信息。在SELECT查询语句中,可使用子查询方式实现多表关联查询。
子查询SQL语句基本格式:
SELECT <目标列>[,<目标列>…]
FROM <表名>
WHERE <条件中嵌套另一关系表的SELECT 查询结果集>
例 在选课管理系统数据库中,希望能检索出“计算机学院”的教师名单。
该操作需要关联教师信息表Teacher和学院信息表College,才能获得这些数据。
这里可采用子查询方法实现两表关联查询,其查询SQL
语句如下:
SELECT TeacherID, TeacherName, TeacherTitle
FROM Teacher
WHERE CollegeID IN
(SELECT CollegeID
FROM College
WHERE CollegeName='计算机学院');
例 在选课管理系统数据库中,希望能检索出“计算机学院”的教师名单。
该操作需要关联教师信息表Teacher和学院信息表College,才能获得这些数据。
这里可采用子查询方法实现两表关联查询,其查询SQL语句如下:
SELECT TeacherID, TeacherName, TeacherTitle
FROM Teacher T, College C
WHERE T.CollegeID=C.CollegeID and C.CollegeName='计算机学院');
二、使用连接关联多表查询
在使用多个表查询时,子查询只有在结果数据均来自一个表的情况下才有用。
但如果需要从两个或多个表中获取结果数据,就不能使用子查询,而需要采用连接关联多表查询。
连接关联多表查询SQL语句基本格式:
SELECT <目标列>[,<目标列>…]
FROM <表名1>,<表名2>,…, <表名n>,
WHERE <关系表之间的连接关联条件>
例 在选课管理系统数据库中,希望获得各个学院的教师信息列表,
包括学院名称、教师编号、教师姓名、教师性别、职称等信息。
要求按学院名称、教师编号分别排序输出,其查询SQL语句如下:
SELECT B.College Name AS 学 院 名 称 , A.TeacherID AS 编号 ,
A.TeacherName AS 姓名, A.TeacherGender AS 性别, A. TeacherTitle AS 职称
FROM Teacher AS A,College AS B
WHERE A.CollegeID=B.CollegeID
ORDER BY B.CollegeName, A.TeacherID;
三、SQL JOIN …ON连接查询语句
在SQL语言中,实现多表连接关联查询还可以使用JOIN…ON关键词的语句格式。
其中两表连接关联查询的JOIN…ON语句格式如下:
SELECT <目标列>[,<目标列>…]
FROM <表名1> JOIN <表名2> ON <连接条件>;
例 在选课管理系统数据库中,希望获得各个学院的教师信息,
包括学院名称、教师编号、教师姓名、教师性别、职称等信息。
要求按学院名称、教师编号分别排序输出,其查询SQL语句如下:
SELECT B.College Name AS 学 院 名 称 , A.TeacherID AS 编号 ,
A.TeacherName AS 姓名, A.TeacherGender AS 性别, A. TeacherTitle AS 职称
FROM TEACHER AS A JOIN COLLEGE AS B
ON A.CollegeID=B.CollegeID
ORDER BY B.CollegeName, A.TeacherID;
四、外部连接
前节介绍的多表连接方式在SELECT查询语句称为内部连接。
在一些特殊情况下,如关联表中一些行的主键与外键不匹配,查询结果集就会丢失部分数据
例 在选课管理数据库中,希望查询所有开设课程的学生选课情况,
包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、教师信息表TEACHER、开课计划表PLAN、选课注册信息表REGISTER。
其连接查询的SQL语句如下:
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师,
COUNT (R.CoursePlanID) AS 选课人数
FROM COURSE AS C JOIN PLAN AS P ON C.CourseID=P.CourseID
JOIN TEACHER AS T ON P.TeacherID=T.TeacherID
JOIN REGISTER AS R ON P.CoursePlanID=R.CoursePlanID
GROUP BY C.CourseName, T.TeacherName;
问题:在上面的内连接查询中,只能找出有学生注册的课程名称和选课人数,但不能找出没有学生注册的课程名称。
在SQL 应用中,有时候也希望输出那些不满足连接条件的元组数据。这时,
可使用JOIN…ON外连接方式实现。其实现方式有三种形式,具体如下:
- LEFT JOIN: 左外连接,即使没有与右表关联列值匹配,也从左表返回所有的行。
- RIGHT JOIN: 右外连接,即使没有与左表关联列值匹配,也从右表返回所有的行。
- FULL JOIN: 全外连接,同时进行左连接和右连接,就返回所有行。
例 在选课管理系统数据库中,希望能查询所有开设课程的学生选课情况,
包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、开课计划表CPLAN、教师信息表TEACHER、选课注册信息表REGISTER。若使用左外连接查询,
该JOIN…ON连接查询的SQL语句如下:
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师,
COUNT (R.CoursePlanID) AS 选课人数
FROM COURSE AS C JOIN PLAN AS P
ON C.CourseID=P.CourseID
JOIN TEACHER AS T ON P.TeacherID=T.TeacherID
LEFT JOIN REGISTER AS R ON P.CoursePlanID=R.CoursePlanID
GROUP BY C.CourseName, T.TeacherName;
3.5数据控制SQL语句
一、什么是数据控制SQL语句:权限控制:授权,收权,拒绝
在SQL语言中,数据控制SQL语句是一种可对用户数据访问权进行控制的操作语句,
它可以控制特定用户或角色对数据表、视图、存储过程、触发器等数据库对象的访问权限。
主要有如下语句:
• GRANT授权语句
• REVOKE收权语句
- • DENY拒绝权限语句
二、GRANT权限授予语句
GRANT语句是一种由数据库对象创建者或管理员执行的权限授予语句,它可以把访问数据库对象权限授予给其他用户或角色。
GRANT <权限列表> ON <数据库对象> TO <用户或角色>
[ WITH GRANT OPTION ];
例 在选课管理系统数据库中,将课程注册表REGISTER的数据插入、数据修改、数据删除、数据查询访问权限赋予学生角色RoleS
GRANT SELECT, INSERT, UPDATE, DELETE ON REGISTER TO RoleS;
三、REVOKE权限收回语句
REVOKE语句是一种由数据库对象创建者或管理员将赋予其它用户或角色的权限进行收回语句,它可以收回原授予给其他用户或角色的权限。
REVOKE <权限列表> ON <数据库对象> FROM <用户或角色> ;
例 在 选 课 管 理 系 统 数 据 库 中 , 收 回 学 生 角 色RoleS 在 课 程 注 册 表REGISTER的数据删除访问权限。
REVOKE DELETE ON REGISTER FROM RoleS;
四、DENY权限拒绝语句
DENY语句用于拒绝给当前数据库内的用户或者角色授予权限,
并防止用户或角色通过其组或角色成员继承权限。
DENY <权限列表> ON <数据库对象> TO <用户或角色> ;
例 在 选 课 管 理 系 统 数 据 库 中 , 若 拒 绝 教 师 角 色RoleT 对 教 师 表TEACHER的数据删除访问权限。
DENY DELETE ON TEACHER TO RoleT;
3.6 视图SQL语句
一、什么是视图
视图——是一种通过基础表或其它视图构建的虚拟表。
它本身没有自己的数据,而是使用了存储在基础表中的数据。
二、视图创建SQL语句
1.语句基本格式
CREATE VIEW <视图名>[(列名1),(列名2),…] AS <SELECT查询>;
2.视图创建实例
例 在选课管理系统数据库中,若需要建立一个查看基础课数据的视图BasicCourseView,其创建SQL语句如下
CREATE VIEW BasicCourseView AS
SELECT CourseName,CourseCredit,CoursePeriod,TestMethod
FROM COURSE
WHERE CourseType='基础课';
当视图在数据库中创建后,用户可以像访问关系表一样去操作访问视图。
例 使用SELECT语句查询该视图数据,并按课程名称排序输出,其SQL语句如下:
SELECT *
FROM BasicCourseView
ORDER BY CourseName;
DROP VIEW BasicCourseView;
三、视图删除
当数据库不再需要某视图时,可以在数据库中删除该视图。
1.语句基本格式
DROP VIEW <视图名>;
其中DROP VIEW 为删除视图语句的关键词。<视图名>为将被删除的视图名称。
2.视图删除实例
例 在数据库中,若需要删除名称为BasicCourseView的视图对象,其删除该视图的SQL语句如下:
DROP VIEW BasicCourseView;
四、SQL视图应用
1.使用视图简化复杂SQL查询操作
数据库开发人员可以将复杂的SQL查询语句封装在视图内,
外部程序只需要使用简单的视图访问方式,便可获取所需要的数据。
例 在选课管理系统数据库中,查询选修“数据库系统原理与开发”课程的学生名单。
这需要关联课程信息表COURSE、开课计划表PLAN、选课注册信息表REGISTER、学生信息表STUDENT,其查询SQL语句如下:
SELECT C.CourseName AS 课程名称, S.StudentID AS 学号, S.StudentName AS 姓名
FROM COURSE AS C,PLAN AS P, REGISTER AS R, STUDENT AS S
WHERE C.CourseID=P.CourseID AND C.CourseName=’数据库系统原理与开发’AND
P.CoursePlanID=R.CoursePlanID AND R.StudentID=S.StudentID;
上面这个SQL语句是较复杂和冗长,为了让外部程序简单地实现该信息查询,可以先定义一个名称为DatabaseCourseView视图,其创建SQL语句如下:
CREATE VIEW DatabaseCourseView AS
SELECT C.CourseName AS 课程名称, S.StudentID AS 学号, S.StudentName AS 姓名
FROM COURSE AS C,PLAN AS P, REGISTER AS R, STUDENT AS S
WHERE C.CourseID=P.CourseID AND C. CourseName='数据库系统原理与开发' AND P.CoursePlanID=R.CoursePlanID AND R.StudentID=S. StudentID;
当DatabaseCourseView视图被创建完成后,外部程序就可以通过一个简单
的SELECT语句查询视图数据,其操作语句如下:
SELECT * FROM DatabaseCourseView;
2.使用视图提高数据访问安全性
通过视图可以将数据表中敏感数据隐藏起来,外部用户无法得知数据表的完整数据,降低数据库被攻击的风险。
此外,还可以保护用户隐私数据。
例 在选课管理系统数据库中,除管理部门用户外,其他用户只能浏览教师基本信息,如教师编号、教师姓名、性别、职称、所属学院。教师其他信息需要被隐藏,可通过视图来处理,其视图创建SQL语句如下:
CREATE VIEW BasicTeacherInfoView AS
SELECT T.TeacherID AS 编号, T.TeacherName AS 教师姓名, T.TeacherGender
AS 性别, T. TeacherTitle AS 职称, C.CollegeName AS 所属学院
FROM TEACHER AS T, COLLEGE AS C
WHERE T.CollegeID=C. CollegeID;
当BasicTeacherInfoView视图被创建完成后,
外部程序就可以通过一个简单的SELECT语句查询视图数据,其操作语句如下:
SELECT * FROM BasicTeacherInfoView
ORDER BY 所属学院 , 教师姓名;
3. 提供一定程度的数据逻辑独立性
通过视图,可提供一定程度的数据逻辑独立性。当数据表结构发生改变,只要视图结构不变,应用程序可以不作修改。
4. 集中展示用户所感兴趣的特定数据
通过视图,可以将部分用户不关心的数据进行过滤,仅仅提供他们所感兴趣的数据。
3.7 PostgreSQL数据库SQL实践
一、项目案例——工程项目管理系统
在 某 工 程 项 目 管 理 系 统 中 , 将 使 用PostgreSQL 数 据 库 管 理 系 统 工 具pgAdmin4创建一个工程项目数据库ProjectDB。
在该数据库中,包含部门表(Department)、员工表(Employee)、项目表(Project)和任务表(Assignment)。各个数据库表结构设计如下。
部门表(Department)
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 |
部门编号 | DepartmentCode | Char | 3 | 是 | 主键 |
部门名称 | DepartmentName | Varchar | 30 | 是 | 否 |
部门简介 | DepartmentIntro | Varchar | 200 | 否 | 否 |
部门地点 | DepartmentAddr | Varchar | 50 | 否 | 否 |
部门电话 | DepartmentTel | Varchar | 20 | 否 | 否 |
员工表(Employee)
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 |
员工编号 | EmployeeID | Serial | 是 | 主键 | |
员工姓名 | EmployeeName | Varchar | 10 | 是 | 否 |
性别 | Gender | Char | 2 | 否 | 默认值‘男’ |
所属部门 | Department | Char | 3 | 否 | 外键 |
学历 | Degree | Char | 4 | 否 | (‘本科’、‘研 究生’、‘其它’) |
出生日期 | Birthday | Date | 否 | ||
联系电话 | Phone | Char | 11 | 否 | |
邮箱 | | Varchar | 20 | 否 | 取值唯一 |
项目表(Project)
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 |
项目编号 | ProjectID | Serial | 是 | 代理键 | |
项目名称 | ProjectName | Varchar | 50 | 是 | |
所属部门 | Department | Char | 30 | 是 | 外键 |
估算工时 | EstimateHours | Int | 否 | ||
开始日期 | StartDate | Date | 否 | ||
结束日期 | EndDate | Date | 否 |
任务表(Assignment)
字段名称 | 字段编码 | 数据类型 | 字段大小 | 必填字段 | 是否为键 |
项目编号 | ProjectID | int | 是 | 主键,外键 | |
员工编号 | EmployeeID | int | 是 | 主键,外键 | |
完成工时 | FishedHours | int | 否 | 否 | |
工时成本 | Cost | int | 否 | 否 |
二、执行SQL语句创建ProjectDB数据库
三、执行SQL语句创建ProjectDB数据库的关系表
四、执行SQL语句插入数据
Department表数据
Employee表数据
Project表数据
Assignment表数据
五、执行SQL语句实现多表关联查询
例:在工程项目管理系统数据库ProjectDB中,管理部门希望了解各个项目参与员工的任务工时列表。
实现该信息查询处理,需要关联Employee表、Project表和Assignment表。查询输出内容应包含(“项目名称”、“员工姓名”、“实际工时”)数据。
SELECT ProjectName AS 项目名称, EmployeeName AS 员工姓名,
FishedHours AS 实际工时
FROM Employee AS E, Project AS P, Assignment AS A
WHERE E.EmployeeID =A.EmployeeID AND P.ProjectID=A.ProjectID
ORDER BY P.ProjectID, A.EmployeeID;
六、执行SQL语句实现视图创建操作
例:在工程项目管理系统数据库ProjectDB中,管理部门希望能查询输出员工通信录。
为了保护员工的一些隐私信息,可以采用视图方式查询输出,
其输出内容包含(“员工编号”、“员工姓名”、“手机”、“邮箱”)基本数据。
该视图的创建SQL语句如下:
CREATE VIEW ContactView AS
SELECT EmployeeID AS 员工编号, EmployeeName AS 员工姓名,
Phone AS 电话, Email AS 邮箱
FROM Employee;
视图查询SQL语句执行
SELECT * FROM ContactView ORDER BY 员工编号;