数据库应用最基础知识大全

数据库应用
An Introduction to Database System
华东师范大学
East China Normal University
什么是数据库
数据库(DB)是长期存储在计算机内,有组织的,统一管理的相关数据的集合.
DB能为各种用户共享,具有较小的冗余度,数据间联系紧密而又有较高的数据独立性等特点.
数据库技术的产生和发展
数据库这个名词起源于20世纪50年代初期,当时美国为了战争的需要,把各种情报集中在一起,存储在计算机里,称为DATABASE.
四个阶段:
人工管理阶段
文件管理阶段
数据库阶段
高级数据库阶段
关系模型的基本概念
关系模型:用二维表格表示实体集,用关键码进行数据导航的数据模型称为关系模型.这里数据导航是指从已知数据查找未知数据的过程和方法.
关键码(Key,简称键):由一个或多个属性组成.在实际使用中,有下列几种键:
超键(Super Key):在关系中能唯一标识元组的属性集称为关系模式的超键.
候选键(Candidate Key):不含有多余属性的超键称为候选键.
主键(Primary Key):用户选作元组标识的候选键称为主键.一般如不加说明,键是指主键.
外键(Foreign Key):如果模式R中属性K是其他模式的主键,那么K在模式R中称为外键.
值域(Domain):关系中每一个属性都有一个取值范围,称为属性的值域.属性A的取值范围用DOM(A)表示.每一个属性对应一个值域,不同的属性可对应于同一值域.
工号
姓名
年龄
性别
工资
4001
Zhang
50
M
2000
4002
Li
40
F
1500
4124
Liu
35
M
2000
5018
Wang
25
M
1000
一般术语
关系模型术语
字段,数据项
记录类型
记录1
记录2
记录3
记录4
字段值
属性
关系模式
元组1
元组2
元组3
元组4
属性值
文件
关系
(实例)
例如:有下列一张职工登记表:
在关系模型中,字段称为属性,字段值称为属性值,记录类型称为关系模式,上例中,关系模式名是R.记录称为元组,元组的集合称为关系或实例.有时也习惯称关系为表或表格,元组为行,属性为列.关系中属性个数称为元数,元组个数为基数.
上图中,(工号,姓名)是模式的一个超键,但不是候选键,而(工号)是候选键.在实际使用中,如果选择(工号)作为删除或查找的标志,那么称(工号)是主键.
R:
数据库技术在学校中的应用:
1,学生管理
2,教务管理
3,课程与成绩管理
4,新闻管理
5,图书管理
……
王老师是《英语》课程的任课老师,有67名学生选修这门课程;《数学》课程的任课老师是石老师,有57名学生选修这门课程;《语文》课程的任课老师是邵老师,有60名学生选修这门课程;……
67
王老师
英语
57
石老师
数学
60
邵老师
语文
选课人数
任课教师
课程名称
课程信息表
属性
属性值
元组
如何构建一个学生成绩管理系统的数据库
设计数据库应该尽量避免冗余!
学生成绩管理系统
学生
课程
成绩
学生表
课程表
成绩表
(学号,姓名,性别,年龄,系别…)
(课程编号,课程名称,任课教师,学分…)
(学生学号,课程编号,成绩…)
学生表用student表示,学号,姓名,性别,年龄,系别五个字段分别用Sno,Sname,Ssex,Sage,Sdept表示;
课程表用Course表示,课程编号,课程名称,任课教师和学分分别用Cno,Cname, Cteacher,Ccredit表示 ;
选课表用SC表示,学生学号,课程编号,成绩分别用Sno,Cno,Grade 表示.
因此,我们有:
学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
课程表:Course(Cno,Cname, Cteacher,Ccredit)
学生选课表:SC(Sno,Cno,Grade)
Sno
Sname
Ssex
Sage
Sdept
95001
李勇
男
20
CS
95002
刘晨
女
19
IS
95003
王敏
女
18
MA
95004
张立
男
19
IS
Student表:
cno
cname
cteacher
ccredit
1
数据库
张华
4
2
数学
李力
2
3
信息系统
吴小明
4
4
操作系统
王平
3
5
数据结构
赵婷婷
4
6
数据处理
赵婷婷
2
7
PASCAL语言
胡刚
4
course表:
SC表:
sno
cno
grade
95001
1
92
95001
2
85
95001
3
88
95002
2
90
95003
3
80
一个重要问题:
如何实现这些表
通过数据库管理系统(DBMS)来实现
SQL SERVER 2000
SQL Server是由Microsoft开发和推广的关系数据库管理系统(DBMS),它最初是由Microsoft,Sybase和Ashton-Tate三家公司共同开发的,并于1988年推出了第一个OS/2版本. SQL Server近年来不断更新版本,1996年,Microsoft 推出了SQL Server 6.5版本;1998年,SQL Server 7.0版本和用户见面;SQL Server 2000是Microsoft公司于2000年推出的最新版本.
一,SQL SERVER 的安装和配置
1. SQL Server 环境需求
(1)SQL Server 2000常见的版本
企业版(Enterprise Edition)
标准版(Standard Edition)
个人版(Personal Edition)
开发者版(Developer Edition)
(2)安装,运行SQL Server 2000的硬件需求
计算机:Intel及其兼容计算机,Pentium 166Mz或者更高处理器.
内存(RAM):企业版最少64MB内存,其他版本最少需要32MB内存,建议使用更多的内存.
硬盘空间:完全安装(Full)需要180MB的空间,典型安装(Typical)需要170MB的空间,最小安装(Minimum)需要65MB的空间.
1. SQL Server 环境需求(续)
(3)安装,运行SQL Server 2000的软件需求
SQL Server 2000企业版必须运行于安装Windows NT Server Enterprise Edition 4.0或者Windows2000 Advanced Server以及更高版本的操作系统下.
SQL Server 2000标准版必须运行于安装Windows NT Server Enterprise Edition 4.0,Windows NT Server 4.0,Windows 2000 Server以及更高版本的操作系统下.
SQL Server 2000 个人版可在多种操作系统下运行,如可运行于Windows 9x, Windows NT 4.0或Windows 2000的服务器版或工作站版的操作系统下.
SQL Server 2000 开发版可运行于上述Windows 9x以外的所有操作系统下.
2. SQL Server 2000的安装
SQL Server 2000的安装过程与其它Microsoft Windows系列产品类似. 用户可根据向导提示,选择需要的选项一步一步地完成.
3 . SQL Server 2000的升级
如果计算机中安装有旧的SQL Server版本,那么安装SQL Server 2000时需要做以下工作,以确保系统能退回到升级前的状态.
1. 检查操作系统是否满足安装要求.
2. 检查磁盘空间是否足够安装新的SQL Server 2000版本.
3. 备份包括系统数据库在内的所有数据库.
4. 备份操作系统的注册表,用于恢复原有的SQL Server.
5. 关闭任何与旧的SQL Server相关的应用程序.
6. 安装SQL Server 2000数据库管理系统.
4. SQL Server 2000的系统数据库简介
SQL Server 2000有6个系统数据库:
(1). Master数据库
(2). Model数据库
(3). Msdb数据库
(4). Tempdb数据库
(5). Pubs和Northwind数据库
这些数据库记录了SQL Server系统的所有系统信息.这些系统信息包括所有的登录信息,系统设置信息,SQL Server的初始化信息和其它系统数据库及用户数据库的相关信息,以及临时数据库和实例数据库等.
5. SQL Server 系统表简介
(1). Sysobjects表
(2). Syscolumns表
(3). Sysindexes表
(4). Sysusers表
(5). Sysdatabases表
(6). Sysdepends表
(7). Sysconstraints表
……
这些表记录了数据库用户表的相关信息,数据库的角色,表间的依赖关系等等.
二,启动运行SQL SERVER 2000
1. 服务管理器(Service Manager):
SQL Server 服务管理器是在服务器端实际工作时最有用的实用程序,其界面如图1-21所示.服务管理器用来启动,暂停,继续和停止数据库服务器的实时服务.
2. 企业管理器:
企业管理器是基于一种新的被称为微软管理控制台(Microsoft Management Console)的公共服务器管理环境,它是SQL Server中最重要的一个管理工具.企业管理器不仅能够配置系统环境和管理SQL Server,而且由于它能够以层叠列表的形式来显示所有的SQL Server对象,因而所有SQL Server对象的建立与管理都可以通过它来完成.
利用企业管理器可以完成的操作有:管理SQL Server 服务器;建立与管理数据库;建立与管理表,视图,存储过程,触发程序,角色,规则,默认值等数据库对象,以及用户定义的数据类型;备份数据库和事务日志,恢复数据库;复制数据库;设置任务调度;设置警报;提供跨服务器的拖放控制操作;管理用户帐户;建立Transact-SQL命令语句以及管理和控制SQL Mail.
图1-20 SQL Server 企业管理器(Enterprise Manager)界面
3. 查询分析器(Query Analyzer) :
SQL Server 2000新的图形化查询分析器用于输入和执行Transaction-SQL语句,并且迅速查看这些语句的结果,以分析和处理数据库中的数据.这是一个非常实用的工具,对掌握SQL语言,深入理解SQL Server的管理工作有很大帮助.
4.联机帮助文档(Books Online)
SQL Server 2000提供了大量的联机文档,它具有索引和全文搜索能力,可根据关键词来快速查找用户所需信息.
联机帮助文档(Books Online)图例:
三,数据库对象的操作
表操作
数据类型
在SQL Server 2000中,每个数据库中最多可以创建200万个表,用户创建数据库表时,最多可以定义1024列,也就是可以定义1024个字段.
SQL Server 2000利用企业管理器(Enterprise Manager)创建表,修改表,删除表
系统数据类型
1.整型数据类型
2.浮点数据类型
3.字符数据类型
4.日期和时间数据类型
5.文本和图形数据类型
6.货币数据类型
7.位数据类型
8.二进制数据类型
9.特殊数据类型
10.新增数据类型
自定义数据类型
1. 整型数据类型
整型数据类型是最常用的数据类型之一,它主要用来存储数值,可以直接进行数据运算,而不必使用函数转换.
int(integer):int(或integer)数据类型可以存储从-231(-2,147,483,648)到231-1(2,147,483,647)范围之间的所有正负整数.
Smallint:可以存储从-215(-32,768)到215-1范围之间的所有正负整数 .
Tinyint:可以存储从0到255范围之间的所有正整数.
2. 浮点数据类型
浮点数据类型用于存储十进制小数.浮点数值的数据在SQL Server中采用只入不舍的方式进行存储 .
Real:可以存储正的或者负的十进制数值,最大可以有7位精确位数.
Float:可以精确到第15位小数,其范围从-1.79E-308到1.79E+308.
Decimal和numeric:Decimal数据类型和numeric数据类型完全相同,它们可以提供小数所需要的实际存储空间,但也有一定的限制,可以用2到17个字节来存储从-1038-1到1038-1之间的数值.
3. 字符数据类型
字符数据类型可以用来存储各种字母,数字符号和特殊符号.
Char:其定义形式为char(n),每个字符和符号占用一个字节的存储空间.
Varchar:其定义形式为varchar(n).用char数据类型可以存储长达255个字符的可变长度字符串 .
Nchar:其定义形式为nchar(n).
Nvarchar:其定义形式为nvarchar(n).
4. 日期和时间数据类型
Datetime:用于存储日期和时间的结合体 .它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间 .
Smalldatetime:与datetime数据类型类似,但其日期时间范围较小,它存储从1900年1月1日到2079年6月6日内的日期.
5. 文本和图形数据类型
Text:用于存储大量文本数据,其容量理论上为1到231-1(2,147,483,647)个字节,但实际应用时要根据硬盘的存储空间而定.
Ntext:与text数据类型类似,存储在其中的数据通常是直接能输出到显示设备上的字符,显示设备可以是显示器,窗口或者打印机.
Image:用于存储照片,目录图片或者图画,其理论容量为231-1(2,147,483,647)个字节.
6. 货币数据类型
Money:用于存储货币值,存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中,存储范围为-922337213685477.5808到922337213685477.5808,精度为货币单位的万分之一.
Smallmoney:与money数据类型类似,但其存储的货币值范围比money数据类型小,其存储范围为-214748.3468到214748.3467.
7. 位数据类型
Bit:称为位数据类型,其数据有两种取值:0和1,长度为1字节.
8. 二进制数据类型
Binary:其定义形式为binary(n),数据的存储长度是固定的,即n+4字节,当输入的二进制数据长度小于n时,余下部分填充0.
Varbinary:其定义形式为varbinary(n),数据的存储长度是变化的,它为实际所输入数据的长度加上4字节.其它含义同binary.
9. 特殊数据类型
Timestamp:亦称时间戳数据类型,它提供数据库范围内的唯一值,反应数据库中数据修改的相对顺序,相当于一个单调上升的计数器. Uniqueidentifier:用于存储一个16字节长的二进制数据类型,它是SQL Server根据计算机网络适配器地址和CPU时钟产生的唯一号码而生成的全局唯一标识符代码(Globally Unique Identifier,简写为GUID).
10. 新增数据类型
Bigint:用于存储从-263(-9,223,372,036,854,775,807)到263-1(9,223,372,036,854,775,807)之间的所有正负整数.
sql_variant:用于存储除文本,图形数据和timestamp类型数据外的其它任何合法的SQL Server数据.
table:用于存储对表或者视图处理后的结果集.这种新的数据类型使得变量可以存储一个表,从而使函数或过程返回查询结果更加方便,快捷.
四, SQL Server权限管理
安全帐户认证是用来确认登录SQL Server的用户的登录帐号和密码的正确性,由此来验证其是否具有连接SQL Server的权限.
SQL Server权限管理――安全帐户认证
SQL Server权限管理――安全帐户认证
SQL Server 2000提供了两种确认用户的认证模式:
(一)Windows 认证模式.
(二)混合认证模式.
(一)Windows NT认证模式
SQL Server数据库系统通常运行在Windows NT服务器平台上,而NT作为网络操作系统,本身就具备管理登录,验证用户合法性的能力,因此Windows NT认证模式正是利用了这一用户安全性和帐号管理的机制,允许SQL Server也可以使用NT的用户名和口令.在这种模式下,用户只需要通过Windows NT的认证,就可以连接到SQL Server,而SQL Server本身也就不需要管理一套登录数据.
(二)混合认证模式
混合认证模式允许用户使用Windows NT安全性或SQL Server安全性连接到SQL Server,这就意味着用户可以使用他的帐号登录到Windows NT,或者使用他的登录名登录到SQL Server系统.NT的用户既可以使用NT认证,也可以使用SQL Server认证.
SQL Server权限管理――安全帐户认证
利用企业管理器进行认证模式的设置:
其主要过程如下:
1.打开企业管理器,用右键单击要设置认证模式的服务器,从快捷菜单中选择"属性(properties)"选项,则出现SQL Server属性对话框.
2.在SQL Server属性对话框中选择安全性选项.
3.在安全性选项栏中,身份验证中可以选择要设置的认证模式,同时审核级别中还可以选择跟踪记录用户登录时的哪种信息,例如登录成功或登录失败的信息等.
4.在启动服务帐户中设置当启动并运行SQL Server时默认的登录者中哪一位用户.
但是通过认证阶段并不代表用户能够访问SQL Server中的数据,同时他还必须通过许可确认.用户只有在具有访问数据库的权限之后,才能够对服务器上的数据库进行权限许可下的各种操作,这种用户访问数据库权限的设置是通过用户帐号来实现的.
SQL Server权限管理――访问许可确认
1. SQL Server服务器登录管理
SQL Server有三个默认的用户登录帐号:即sa,administrators/builtin和guest.
2. 用户帐号管理
(1).利用企业管理器创建,管理SQL Server登录帐号
其具体执行步骤如下:
打开企业管理器,单击需要登录的服务器左边的"+"号,然后展开安全性文件夹.
用右键单击登录(login)图标,从快捷菜单中选择新建登录(new login)选项,则出现SQL Server登录属性―新建登录对话框所示.
在名称编辑框中输入登录名,在身份验证选项栏中选择新建的用户帐号是Windows NT认证模式,还是SQL Server认证模式.
SQL Server权限管理
――服务器登录帐号和用户帐号管理
2.角色管理
角色是SQL Server 7.0版本引进的新概念,它代替了以前版本中组的概念.利用角色,SQL Server管理者可以将某些用户设置为某一角色,这样只对角色进行权限设置便可以实现对所有用户权限的设置,大大减少了管理员的工作量.SQL Server提供了用户通常管理工作的预定义服务器角色和数据库角色.
SQL Server权限管理――许可(权限)管理
1,服务器角色
服务器角色是指根据SQL Server的管理任务,以及这些任务相对的重要性等级来把具有SQL Server管理职能的用户划分为不同的用户组,每一组所具有的管理SQL Server的权限都是SQL Server内置的,即不能对其进行添加,修改和删除,只能向其中加入用户或者其他角色.
SQL Server权限管理――许可(权限)管理
七种常用的固定服务器角色
系统管理员:拥有SQL Server所有的权限许可.
服务器管理员:管理SQL Server服务器端的设置.
磁盘管理员:管理磁盘文件.
进程管理员:管理SQL Server系统进程.
安全管理员:管理和审核SQL Server系统登录.
安装管理员:增加,删除连接服务器,建立数据库复制以及管理扩展存储过程.
数据库创建者:创建数据库,并对数据库进行修改.
SQL Server权限管理――许可(权限)管理
2,数据库角色
数据库角色是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限,这些权限是数据库专有的,并且还可以使一个用户具有属于同一数据库的多个角色.SQL Server提供了两种类型的数据库角色:即固定的数据库角色和用户自定义的数据库角色.
(1)固定的数据库角色
public:维护全部默认许可.
db_owner:数据库的所有者,可以对所拥有的数据库执行任何操作.
db_accessadmin:可以增加或者删除数据库用户,工作组和角色
SQL Server权限管理――许可(权限)管理
db_addladmin:可以增加,删除和修改数据库中的任何对象.
db_securityadmin:执行语句许可和对象许可.
db_backupoperator:可以备份和恢复数据库.
db_datareader:能且仅能对数据库中的任何表执行select操作,从而读取所有表的信息.
db_datawriter:能够增加,修改和删除表中的数据,但不能进行select操作.
db_denydatareader:不能读取数据库中任何表中的数据.
db_denydatawriter:不能对数据库中的任何表执行增加,修改和删除数据操作.
SQL Server权限管理――许可(权限)管理
(2)用户自定义角色
创建用户定义的数据库角色就是创建一组用户,这些用户具有相同的一组许可.如果一组用户需要执行在SQL Server中指定的一组操作并且不存在对应的Windows NT组,或者没有管理Windows NT用户帐号的许可,就可以在数据库中建立一个用户自定义的数据库角色.用户自定义的数据库角色有两种类型:即标准角色和应用程序角色.
SQL Server权限管理――许可(权限)管理
3.标准角色和应用程序角色
标准角色通过对用户权限等级的认定而将用户划分为不用的用户组,使用户总是相对于一个或多个角色,从而实现管理的安全性.
应用程序角色是一种比较特殊的角色.当我们打算让某些用户只能通过特定的应用程序间接地存取数据库中的数据而不是直接地存取数据库数据时,就应该考虑使用应用程序角色.当某一用户使用了应用程序角色时,他便放弃了已被赋予的所有数据库专有权限,他所拥有的只是应用程序角色被设置的角色.
SQL Server权限管理――许可(权限)管理
使用企业管理器管理角色
管理服务器角色
打开企业管理器,展开指定的服务器,单击安全性文件夹,然后单击服务器角色图标,选择需要的选项,根据提示操作.
管理数据库角色
在企业管理器中,展开指定的服务器以及指定的数据库,然后用右键单击角色图标,从快捷菜单中选择新建数据库角色选项,则出现数据库角色属性―新建角色对话框,根据提示即可新建角色.
SQL Server权限管理――许可(权限)管理
五, SQL Server数据转换
数据的导入和导出
数据的备份和还原
六,SQL语句
SQL的数据查询
SQL的数据更新
SELECT-FROM-WHERE句型:
SELECT A1,… An
FROM R1,… Rm
WHERE F
在WHERE子句的条件表达式F中中可使用下列运算符:
算术比较运算符=,>,<,或!=,=;
逻辑运算符AND,OR,NOT;
集合成员资格运算符:IN,NOT IN;
聚合函数:
COUNT(*)计算元组的个数
COUNT(列名)对一列中的值计算个数
SUM(列名)求一列中值的总和(此列的值必须是数值型)
AVG(列名)求一列中值的平均值(此列的值必须是数值型)
MAX(列名)求一列中值的最大值
MIN(列名)求一列中值的最小值
SELECT语句的完整语法:
SELECT 目标表的列名或列表达式序列
FROM 基本表名
[ WHERE ]
[ ORDER BY 列名 [ ASC|DESC ],… ];
其中[]表示该项可选.
SELECT子句:指定要显示的属性列
FROM子句:指定查询对象(基本表或视图)
WHERE子句:指定查询条件,称为行条件子句
ORDER BY子句:对查询结果表按指定列值的升序或降序排序,称为排序子句
实例数据库:学生-课程-成绩数据库
学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
课程表:Course(Cno,Cname, Cteacher,Ccredit)
学生选课表:SC(Sno,Cno,Grade)
单表查询查询仅涉及一个表,是一种最简单的查询操作,包括如下内容:
1,选择表中的若干列
2,选择表中的若干元组
3,对查询结果排序
4,使用集函数
一,选择表中的若干列:
[例1] 查询全体学生的学号与姓名.(查询指定列)
SELECT Sno,Sname
FROM Student;;_
[例2] 查询全体学生的姓名,学号,所在系.(查询指定列)
SELECT Sname,Sno,Sdept
FROM Student;
1,SQL的数据查询――单表查询
[例3] 查询全体学生的详细记录.(查询全部列)
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
或
SELECT *
FROM Student;
[例4]查全体学生的姓名及其出生年份(查询经过计算的值)
SELECT Sname,2004-Sage
FROM Student;
输出结果:
Sname 2004-Sage
--------- -------------
李勇 1984
刘晨 1985
王名 1986
张立 1985
1,SQL的数据查询――单表查询
[例5] 查询全体学生的姓名,出生年份和所有系. (查询经过计算的值)
SELECT Sname,'Year of Birth:',2004-Sage,Sdept
FROM Student;
输出结果:
Sname 'Year of Birth:' 2004-Sage Sdept
------- ---------------- --------- --------------
李勇 Year of Birth: 1984 cs
刘晨 Year of Birth: 1985 is
王名 Year of Birth: 1986 ma
张立 Year of Birth: 1985 is
1,SQL的数据查询――单表查询
[例6]使用列别名改变查询结果的列标题
SELECT Sname NAME,'Year of Birth: ' BIRTH,
2004-Sage BIRTHDAY,Sdept DEPARTMENT
FROM Student;
输出结果:
NAME BIRTH BIRTHDAY DEPARTMENT
------- ---------------- ------------- ------------------
李勇 Year of Birth: 1984 cs
刘晨 Year of Birth: 1985 is
王名 Year of Birth: 1986 ma
张立 Year of Birth: 1985 is
1,SQL的数据查询――单表查询
二,选择表中的若干元组
1. 消除取值重复的行
在SELECT子句中使用DISTINCT短语
假设SC表中有下列数据 :
Sno Cno Grade
------- ------- -------
95001 1 92
95001 2 85
95001 3 88
95002 2 90
95002 3 80
1,SQL的数据查询――单表查询
[例7] 查询选修了课程的学生学号.
4.3 SQL的数据查询――单表查询
SELECT Sno
FROM SC;
或
SELECT all Sno
FROM SC;
结果: Sno
-------
95001
95001
95001
95002
95003
SELECT distinct Sno
FROM SC;
结果: Sno
-------
95001
95002
95003
注意 DISTINCT短语的作用范围是所有目标列
例:查询选修课程的各种成绩
错误的写法
SELECT DISTINCT Cno,DISTINCT Grade
FROM SC;
正确的写法
SELECT DISTINCT Cno,Grade
FROM SC;
1,SQL的数据查询――单表查询
查询满足条件的元组
(1)比较大小
在WHERE子句的中使用:
比较运算符=,>,=,<=,!= 或 ,!>,!<,
逻辑运算符NOT + 比较运算符
[例8] 查询所有年龄在20岁以下的学生姓名及其年龄.
SELECT Sname,Sage
FROM Student
WHERE Sage = 20;
1,SQL的数据查询――单表查询
查询满足条件的元组
(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;
1,SQL的数据查询――单表查询
查询满足条件的元组
(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' );
1,SQL的数据查询――单表查询
查询满足条件的元组
(4)字符串匹配
[NOT] LIKE '' [ESCAPE ' ']
:指定匹配模板
匹配模板:固定字符串或含通配符的字符串
当匹配模板为固定字符串时, 可以用 = 运算符取代 LIKE 谓词, 用 != 或 运算符取代 NOT LIKE 谓词
通配符
% (百分号) 代表任意长度(长度可以为0)的字符串
例:a%b表示以a开头,以b结尾的任意长度的字符串.如acb,addgb,ab 等都满足该匹配串
_ (下横线) 代表任意单个字符
例:a_b表示以a开头,以b结尾的长度为3的任意字符串.如acb,afb等都满足该匹配串
当用户要查询的字符串本身就含有 % 或 _ 时,要使用ESCAPE '' 短语对通配符进行转义.
1,SQL的数据查询――单表查询
例题:
1)匹配模板为固定字符串
[例14] 查询学号为95001的学生的详细情况.
SELECT *
FROM Student
WHERE Sno LIKE '95001';
等价于:
SELECT *
FROM Student
WHERE Sno = '95001';
2) 匹配模板为含通配符的字符串
[例15] 查询所有姓刘学生的姓名,学号和性别.
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%';
1,SQL的数据查询――单表查询
2) 匹配模板为含通配符的字符串(续)
[例16] 查询姓"欧阳"且全名为三个汉字的学生的姓名.
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__';
[例17] 查询名字中第2个字为"阳"字的学生的姓名和学号.
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '__阳%';
[例18] 查询所有不姓刘的学生姓名.
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';
1,SQL的数据查询――单表查询
3) 使用换码字符将通配符转义为普通字符
[例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 '/' ;
1,SQL的数据查询――单表查询
查询满足条件的元组
(5) 涉及空值的查询
使用谓词 IS NULL 或 IS NOT NULL
"IS NULL" 不能用 "= NULL" 代替
[例21] 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩.查询缺少成绩的学生的学号和相应的课程号.
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
[例22] 查所有有成绩的学生学号和课程号.
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
1,SQL的数据查询――单表查询
查询满足条件的元组
(6) 多重条件查询
用逻辑运算符AND和 OR来联结多个查询条件
AND的优先级高于OR
可以用括号改变优先级
[例23] 查询计算机系年龄在20岁以下的学生姓名.
SELECT Sname
FROM Student
WHERE Sdept= 'CS' AND Sage=20 AND Sage<=23;
1,SQL的数据查询――单表查询
三,对查询结果排序
使用ORDER BY子句
可以按一个或多个属性列排序
升序:ASC;降序:DESC;缺省值为升序
当排序列含空值时
ASC:排序列为空值的元组最后显示
DESC:排序列为空值的元组最先显示
[例24] 查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列.
SELECT Sno,Grade
FROM SC
WHERE Cno= '3'
ORDER BY Grade DESC;
1,SQL的数据查询――单表查询
对查询结果排序(续)
查询结果:
Sno Grade
------- -------
95007 92
95003 82
95010 82
95009 75
95014 61
95002 55
1,SQL的数据查询――单表查询
对查询结果排序(续):
[例25] 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列.
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;
1,SQL的数据查询――单表查询
四,使用集函数:5类主要集函数
计数
COUNT([DISTINCT|ALL] *)
COUNT([DISTINCT|ALL] )
计算总和
SUM([DISTINCT|ALL] )
计算平均值
AVG([DISTINCT|ALL] )
求最大值
MAX([DISTINCT|ALL] )
求小大值
MIN([DISTINCT|ALL] )
DISTINCT短语:在计算时要取消指定列中的重复值
ALL短语:不取消重复值
ALL为缺省值
1,SQL的数据查询――单表查询
四,使用集函数(续)
[例26] 查询学生总人数.
SELECT COUNT(*)
FROM Student;
[例27] 查询选修了课程的学生人数.
SELECT COUNT(DISTINCT Sno)
FROM SC;
注:用DISTINCT以避免重复计算学生人数
[例28] 计算1号课程的学生平均成绩.
SELECT AVG(Grade)
FROM SC
WHERE Cno= '1';
[例29] 查询选修1号课程的学生最高分数.
SELECT MAX(Grade)
FROM SC
WHER Cno= '1';
1,SQL的数据查询――单表查询
连接查询:同时涉及多个表的查询称为连接查询
用来连接两个表的条件称为连接条件或连接谓词
一般格式:
[.] [.]
比较运算符:=,>,=,<=,!=
[.] BETWEEN [.] AND [.]
连接字段
连接谓词中的列名称为连接字段
连接条件中的各连接字段类型必须是可比的,但不必是相同的
2,SQL的数据查询――连接查询
[例31]
SELECT Student.* , SC.*
FROM Student, SC
[例32] 查询每个学生及其选修课程的情况.
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno = SC.Sno;
2,SQL的数据查询――连接查询
1,连接运算符为 = 的连接操作
[.] = [.]
任何子句中引用表1和表2中同名属性时,都必须加表名前缀.引用唯一属性名时可以加也可以省略表名前缀.
例如:假设Student表,SC表分别有下列数据:
Student表: SC表:
2,SQL的数据查询――连接查询
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
95002
95002
3
2
3
88
90
80
结果表:
Student.Sno Sname Ssex Sage Sdept SC.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
2,SQL的数据查询――连接查询
2,连接的一种特殊情况,把目标列中重复的属性列去掉.
[例33] :对[例32]用自然连接完成.
SELECT Student.Sno,Sname,Ssex,Sage,
Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;
2,SQL的数据查询――连接查询
3,连接运算符 不是 = 的连接操作
[.][.]
比较运算符:>,=,<=,!=
[.] BETWEEN [.] AND [.]
2,SQL的数据查询――连接查询
4,复合条件连接
WHERE子句中含多个连接条件时,称为复合条件连接
[例35]查询选修2号课程且成绩在90分以上的所有学生的学号,姓名
SELECT Student.Sno, student.Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno
AND SC.Cno= '2'
AND SC.Grade > 90;
2,SQL的数据查询――连接查询
5,多表连接:
[例36]查询每个学生的学号,姓名,选修的课程名及成绩.
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno
and SC.Cno = Course.Cno;
_结果:
Student.Sno Sname Cname Grade
95001 李勇 数据库 92
95001 李勇 数学 85
95001 李勇 信息系统 88
95002 刘晨 数学 90
95002 刘晨 信息系统 80
2,SQL的数据查询――连接查询
3,SQL的数据更新――插入数据
语句格式
INSERT INTO [([,…)] VALUES ( [,] … )
功能:将新元组插入指定表中
[例37] 将一个新学生记录
(学号:95020;姓名:陈冬;性别:男;所在系:IS;年龄:18岁)插入到Student表中.
INSERT
INTO Student
VALUES ('95020', '陈冬','男', '18', 'is');
[例38] 插入一条选课记录( '95020','1 ').
INSERT
INTO SC(Sno,Cno)
VALUES (' 95020 ',' 1 ');
新插入的记录在Grade列上取空值
3,SQL的数据更新――插入数据
INTO子句
指定要插入数据的表名及属性列
属性列的顺序可与表定义中的顺序不一致
没有指定属性列:表示要插入的是一条完整的元组,且属性列属性与表定义中的顺序一致
指定部分属性列:插入的元组在其余属性列上取空值
VALUES子句
提供的值必须与INTO子句匹配
值的个数
值的类型
3,SQL的数据更新――插入数据
语句格式:
UPDATE
SET =[,=]…
[WHERE ];
功能:修改指定表中满足WHERE子句条件的元组
4,SQL的数据更新――数据修改
SET子句:指定修改方式
要修改的列
修改后取值
WHERE子句:指定要修改的元组
缺省表示要修改表中的所有元组
4,SQL的数据更新――数据修改
1. 修改某一个元组的值
[例39] 将学生95001的年龄改为22岁.
UPDATE Student
SET Sage=22
WHERE Sno='95001';
2. 修改多个元组的值
[例40] 将所有学生的年龄增加1岁.
UPDATE Student
SET Sage= Sage+1;
[例41] 将信息系所有学生的年龄增加1岁.
UPDATE Student
SET Sage= Sage+1
WHERE Sdept='IS';
4,SQL的数据更新――数据修改
语句格式:DELETE
FROM
[WHERE ];
功能:删除指定表中满足WHERE子句条件的元组
WHERE子句
指定要删除的元组
缺省表示要修改表中的所有元组
5,SQL的数据更新――数据删除
1. 删除某一个元组的值
[例42] 删除学号为95019的学生记录.
DELETE
FROM Student
WHERE Sno='95019';
2. 删除多个元组的值
[例43] 删除2号课程的所有选课记录.
DELETE
FROM SC;
WHERE Cno='2';
[例44] 删除所有的学生选课记录.
DELETE
FROM SC;
5, SQL的数据更新――数据删除
七,并发控制
Why
――并发
三个并发问题:
丢失更新问题
未提交依赖问题
不一致分析问题
(1)丢失更新问题
Transaction A's update is lost at time t4.
(2)未提交依赖问题
Transaction A becomes dependent on an uncommitted change at time t2
Transaction A becomes dependent on an uncommitted change at time t2
Transaction A time Transaction B
t1
t2
t3
Update t
Update t
Rollback
(3)不一致分析问题
Transaction A performs an inconsistent analysis
如何解决这些问题
――锁
锁:
1.两种锁:
排他锁:Exclusive locks (X locks) / write locks
共享锁:Shared locks (S locks) / read locks

锁相容矩阵:
No update is lost, but deadlock occurs at time t4
(1)丢失更新问题
重提那三个典型问题:
Transaction A is prevented from seeing an uncommitted change at time t2.
(2)未提交依赖问题
Transaction A is prevented from updating an uncommitted change at time t2
Transaction A time Transaction B
t1
t2
t3
Update t
(Acquires x lock on t)
Commit/Rollback
(release x lock on t)
Update t
(requires x lock on t)
Wait
Wait
Wait
Resume:Update t
(acquire x lock on t)
t4
Inconsistent analysis is prevented,but deadlock occurs at time t7
(3)不一致分析问题
死锁:
An example of deadlock

本文地址：http://www.45fan.com/dnjc/73393.html