复制成功
  • 图案背景
  • 纯色背景
  •   |  注册
  • /
  • 批注本地保存成功,开通会员云端永久保存 去开通
  • 第二章 关系数据库语言【精品PPT】

    下载积分:2000

    内容提示: 第2章S Q L (S tru ctu red   Q u ery   L a n g u a g e ) 是结构化查询语言, 利用S Q L 语言可以实现对数据库的访问。 T ra n sa ct- S Q L 是M icro so f t公司对S Q L 标准的一个实现, 简称T - S Q L 。关系数据库语言 SQL概述 SQL Server数据库的基本管理与设置数据库原理与设计 SQL Server数据库的基本管理与设置 数 据 定 义 数 据 查 询 数 据 更 新 2. 1 SQL概述S Q L 简单易学、 风格统一, 利用简单的几条命令就可以完成所有的功能。SQL有以下主要特点。 1. 综合统一高度非过程化 3. 面向集合的操作方式数据库原理与设计 2. 高度非过程化 4. 以同一种语法结构提供两种使用方式 5. 语言简洁, 易学易用 2. 1. 2 S...

    亚博足球app下载格式:PPT| 浏览次数:11| 上传日期:2015-06-17 17:34:32| 亚博足球app下载星级:
    第2章S Q L (S tru ctu red   Q u ery   L a n g u a g e ) 是结构化查询语言, 利用S Q L 语言可以实现对数据库的访问。 T ra n sa ct- S Q L 是M icro so f t公司对S Q L 标准的一个实现, 简称T - S Q L 。关系数据库语言 SQL概述 SQL Server数据库的基本管理与设置数据库原理与设计 SQL Server数据库的基本管理与设置 数 据 定 义 数 据 查 询 数 据 更 新 2. 1 SQL概述S Q L 简单易学、 风格统一, 利用简单的几条命令就可以完成所有的功能。SQL有以下主要特点。 1. 综合统一高度非过程化 3. 面向集合的操作方式数据库原理与设计 2. 高度非过程化 4. 以同一种语法结构提供两种使用方式 5. 语言简洁, 易学易用 2. 1. 2 SQL数据库的体系结构视图(View)----类似于表----虚表(窗口)----使数据管理更方便和安全数据库原理与设计 2. 1. 3 SQL语句的分类 在SQL2标准中, SQL语句可大体分为以下三类 ① 数据定义语言(Data Definition Language, DDL) :用于定义SQL模式、 基本表、 视图、 索引等结构。 ② 数据操纵语言(Data Manipulation ② 数据操纵语言(Language, DML) :数据库原理与设计p数据操作分成数据查询和数据更新两类。 ③ 数据控制语言(Data Control Language,DCL) :包括事务管理功能和数据保护功能 SQL 语言的动词 SQL 功 能 数 据 定 义 动 词 CREATE, DROP, ALTERSELECT 数 据 操 纵 数据库原理与设计INSERT, UPDATE DELETE 数 据 控 制 GRANT, REVOKE 2. 1. 4 SQL的语法约定 1. 标识符 2. 标识符的类型 3. SQL语句的语法数据库原理与设计 1.了 解数据库的存储结构2.掌握数据库的创建和维护的方法2.2 数据库的基本管理与设置数据库原理与设计 2. 2 数据库的基本管理与设置 1. 数据库的存储结构分两种: 逻辑存储结构和物理存储结构。 数据库的逻辑存储结构: SQL Server的数据库是由诸如表、 视图等不同的数据库对象所组成的。数据库原理与设计 数据库的物理存储结构: 指的是数据库文件是如何在磁盘上存储的。 SQL Server 2000中, 数据存储在页(Page) 中, 页是数据存储的最小单位 数据库操作系统文件分为三类: 主数据文件、二级数据文件和日志文件.  ( 1 ) 主数据文件: 扩展名“ .mdf”, 每个数据库有且仅有一个主数据文件, 用于存储数据库的启动信息、 数据和数据库对象。 是数据库的起点, 指向数据库中文件的其他部分。数据库原理与设计(2) 二级数据文件: 扩展名为“.ndf” 。 主要在一个数据库跨多个硬盘驱动器时使用。  (3) 日志文件: 扩展名为“.ldf”。 每个数据库至少拥有一个日 志文件。 用于存放对数据库进行操作的事务日志。 数据库的最精简装备: 一个数据库至少应该包含一个主数据文件和一个事务日志文件。数据库原理与设计 数据库较完备装备: 多个数据文件, 三个日 志文件; 日 志文件与数据文件不放在同一块物理磁盘上, 以备数据文件磁盘损坏时可以利用日志文件恢复。 日志文件数据库原理与设计 数据库的创建与管理数据库原理与设计 SQL Server的系统数据库与示例数据库 SQL Server的数据库可以分为两大类: 系统数据库和用户数据库。 系统数据库不能随便更改或删除。 用户数据库一般由用户创建 用户数据库数据库原理与设计般由用户创建。 SQL Server安 装 时 建 立 了 6 个 数 据 库 , 其 中master, tempdb, model和msdb是4个系统数据库; pubs和Northwind是两个用户数据库, 用以示例。 2. 创建数据库默认情况下, 只有系统管理员和数据库拥有者可创建数据库。 创建数据库的用户自动成为该数据库的所有者。创建数据库的过程实际上就是为数据库设计名称、 设计所占用的存储空间和存放文件位置的过程。1. 企业管理器创建数据库2. 用T-SQL语言创建数据库:Create Database 数据库名3. 用向导创建数据库数据库原理与设计一个数据库至少应该包含一个数据库文件和一个事务日志文件。 使用T-SQL语言创建数据库, 语法如下:Create Database 数据库名[ON [PRI MARY] [<文件格式> [, …n] ] ][LOG ON {<文件格式> [, …n] } ] <文件格式>: : =([NAME=逻辑文件名, ] FI LENAME=‘操作系统下的物理路径和文件名’[, SI ZE=文件初始大小][, MAXSI ZE={文件最大大小| UNLI MI TED} ] [, FI LEGROWTH=增量值] [, …n]数据库原理与设计 Create Database Company ON PRIMARY (NAME=Company_data, FILENAME=' C:\SQL\Company.mdf', SIZE=10, MAXSIZE=unlimited, FILEGROWTH=10%)FILEGROWTH 10%) LOG ON (NAME=Company_log, FILENAME='C:\SQL\Company.ldf',SIZE=1, MAXSIZE=5, FILEGROWTH=1)创建一个Company数据库主数据文件逻辑名称物理文件名初始大小最大尺寸为无限大增长速度增长速度数据库原理与设计日志文件逻辑名称物理文件名 2. 2. 3 修改数据库ALTER DATABASE包括增、 删数据文件和事务日志文件, 修改数据文件和事务日志文件的初始容量、 最大容量、 增长方式等。Alter Database 数据库名{ADD FILE <文件格式>[,…n] |ADD LOG FILE <文件格式>[,…n] |REMOVE FILE <日志文件名> [with delete] 数据库原理与设计|MODIFY FILE <文件格式> |MODIFY NAME=新数据库名 2. 2. 4 删除数据库 删除数据库时也删除了 该数据库的全部对象。 T-SQL语言删除数据库:DROP DATABASE 数据库名 [ ,...n ]数据库原理与设计无法除去系统数据库(msdb、 model、 master和tempdb)有用户使用此数据库时以下情况不能删除:数据库正在被恢复时数据库正在参与复制时 2. 2. 5 设置数据库选项sp_dboption系统存储过程设置数据库选项。 基本语法如下:sp_dboption [‘数据库名’ ] [‘配置选项名’ ][‘设置值’ ]]数据库原理与设计将Company数据库设为只读将Company数据库设为可写 数据库操作总结创建数据库:Create Database 数据库名修改数据库:Alter Database 数据库名{ADD FILE <文件格式>[{ADD FILE <文件格式>[,…n] |REMOVE FILE <日志文件名> |MODIFY FILE <文件格式> 删除数据库:Drop Database 数据库名 [ ,...n ]]数据库原理与设计 2.3.1 表的创建与管理数据库原理与设计2.3 数 据 定 义 表操作之一表操作之一- -表的建立表的建立一个表由表结构、 表记录构成。 定义表的结构, 就是定义表的字段个数、 字段名称、 类型、 宽度及小数位数等。 企业管理器中创建表数据库原理与设计 使用Create命令创建Create Table 表名 (字段名1 类型(长度), 字段名2 类型(长度) …) 数据库原理与设计 数据类型注解注解: 因为: 因为char是会用空格填充使之达到一是会用空格填充使之达到一个固定宽度, 在表中占用很个固定宽度, 在表中占用很大的存储空间。大的存储空间。char类型总类型总数据库原理与设计 关系模型关系模型 stu d en t(stu d en t( sn oco u rse(co u rse( cn oo ,cterm )o ,cterm )o ,cterm )o ,cterm )sc(sc( sn osn o , ,cn osn o ,cln o ,sn a m e,ssex ,sbir,scred it),cln o ,sn a m e,ssex ,sbir,scred it)cn o ,ctn o ,cn a m e,cin f o ,ccred its,cttim e,cp n,ctn o ,cn a m e,cin f o ,ccred its,cttim e,cp nE E- -R R模型模型coursestudentsnoclnosnamessexsbirscreditsccnocnamectnocinfoccreditscttimecpnoctermscoreNM数据库原理与设计数据分类数据分类cn o ,sco re),sco re) 完整性约束----建表中的完整性问题名称含义实现实体完整性要求表中的每条记录都是唯一的用主码来实现Primary key参照完整性确保表与表之间的引用关系用外码来实现Foreign key用户自定义完整性确保表中数据满足用户的特定要求例如:数据必须唯一数据不能为空数据满足某些条件定义数据默认值UniqueNot nullCheck(Ssex in (‘男’ ,’女’ ))Default 完整性约束----建表中的完整性问题数据库原理与设计 完整性约束----建表中的完整性问题数据库原理与设计 完整性约束----建表中的完整性问题数据库原理与设计 完整性约束使用命令定义完整性约束条件:需要在定义完字段的数据类型和长度之后, 定义该字段上的约束类型: 1.主码完整性约束 ------如:sno char(5) primary key  2.Foreign Key约束-----如:hsno char(5) foreign key references student(sno)( ) fikfd()Create Table 表名 (字段名1类型(长度)类型(长度)约束,约束字段名2…)数据库原理与设计 3.check完整性约束-----如:age int check(age between 18 and 30) 4.唯一完整性约束 unique  5.DEFAULT完整性约束scredit numeric(3,0) default 4 6.非空完整性约束sname char(6) not null 例: 使用命令的方式创建student,course,sc三个表。CCreate table student( Sno char(5) primary key,Clno char(5) not null,Sname varchar(8) not null,Ssex char(2) check (ssex in (‘男’,’女’)) ,Sbir datetime,Scredit numeric(4,0))bld数据库原理与设计 Create table course( Cno char(5) primary key,(( ) pCname varchar(50) unique,Ctno char(2),Cinfo varchar(50),Ccredits numeric(2,0) default 4,Cttime numeric(3,0),Cpno char(5),Cterm numeric(1,0))数据库原理与设计yy, Create table sc( Sno char(5) foreign key(sno) references student(sno),Cno char(5) foreign ke (cno) references co rse(cno)Cno char(5) foreign key(cno) references course(cno),Score numeric(3,1),Primary key(sno,cno)) 数据库原理与设计 引用完整性约束(外码约束)内容补充 先建主表, 后建从表 当定义外码时候, 子表中引用的必须是主表的主码 。 默认情况下创建了 外码约束, 当删除或修改主表记录时若子表中有对应记录记录时, 若子表中有对应记录, 不能修改或者删除。不能修改或者删数据库原理与设计 如果用户需要, 删除或者修改主表的记录的同时,系统自动删除或者修改子表中相关的记录, 则需要在建外码的同时指定on delete cascade 级联删除。  命名约束定义命名的完整性约束CREATE TABLE tablename (CREATE TABLE tablename (字段名字段名 类型类型[ [长度长度] ] [CONSTRAINT [CONSTRAINT constraint_nameconstraint_name] ]…………Not null Not null Unique Unique Primary key Primary key Foreign key(Foreign key(字段主表(主键)主表(主键)条件条件Check Check 条件条件) )D ef a u ltD ef a u lt字段) references ) references 数据库原理与设计 练习创建表和完整性约束 创建货物表goods和订单表orders, 表结构如下。 所有的约束采用命名约束。 创建外键约束,级联删除.数据库原理与设计 表操作之二-表的修改 修改表对象ALTER TABLE 表名ADD 列名 数据类型 [完整性约束条件] ALTER COLUMN 列名 数据类型DROP COLUMN 列名DROP COLUMN 列名ADD CONSTRAINT 约束名 约束DROP CONSTRAINT 约束名数据库原理与设计  例2-12 向表Student中增加一列address。Alter table Student add address varchar(40)  例2-14 将Student表中address列的最大长度修改为50, 并允许为空。ALTER Table StudentALTER Column address varchar(50) NULL数据库原理与设计 例2-15 删除Student表中的address列。ALTER Table StudentDROP Column address  向表Student中增加列Email, 且要求输入的电子邮件地址必须包括“@”字符。ALTER TABLE Student ADD Email VARCHAR(40) CONSTRAINT ck_ea CHECK(Email like '%@%')数据库原理与设计()  例2-13 在Student表中SName列上设置唯一约束。ALTER TABLE Student ADD CONSTRAINT u_name Unique(SName)数据库原理与设计 例2-16 删除Student表中SName列上的唯一约束。ALTER TABLE StudentDROP CONSTRAINT u_name 完整性约束的作用时间:在ALTER TABLE语句中使用(1)nocheck constraint子句(2)check constraint子句来完成使约束无效和重新有效的设置。数据库原理与设计ALTER TABLE student nocheck constraint ck_ageage int constraint ck_agecheck(age between 18 and 60) 表操作之三-表的删除DROP TABLE 表名例如: DROP TABLE eg1注意: DROP TABLE语句不能删除系统表。数据库原理与设计删除表时, 系统会删去该表及其索引表上的视图往往仍然保留,无法引用 SQL的数据定义语句操 作 方 式 操作 对象 表 视图 索引 创 建 删 除 修改 表CREATE T blCREATE TableDROPT blDROP Table ALTERALTER TableT bl数据库原理与设计CREATE View DROP View CREATE Index DROP Index 查询数据库原理与设计 2.3.2 索引的创建与管理数据库原理与设计2.3 数 据 定 义 索引的分类索引分为聚簇索引与非聚簇索引。 聚簇索引(Clustered Index) 叶结点中存储的是实际的数据。数据库原理与设计 非聚簇索引(Nonclustered Index) 叶结点存储组成索引的关键字值和指针 3. 创建索引(1) 选择创建索引的列• 表的主键列;• 连接中频繁使用的列;• 频繁搜索的列。插入和更新数据时, 维护索引要花费时间和空间插更新数据时维护索引要花费时数据库原理与设计(2) 创建索引的语法格式CREATE [unique] [clustered| nonclustered]INDEX 索引名 ON 表名 (列名[次序] [, 列名[次序] ] …)次序:指定索引值的排列次序:ASC和DESC两种  例2-18 在Course表上创建一个名cs_index的非聚簇复合索引, 索引关键字为Cno和CTno,非聚簇复合索引, 索引关键字为Cno和CTno,升序排列。CREATE nonclusteredINDEX cs_index ON Course (CNo ASC, CTno ASC)CREATE [unique] [clustered|nonclustered] INDEX 索引名 ON 表名 (列名[次序] [, 列名[次序] ] …)数据库原理与设计 在Course表CNo上创建一个唯一性的聚簇索引,索引排列顺序为降序。CREATE unique clustered INDEX IX_CNoON Course(CNo DESC ) 唯一性索引数据库原理与设计对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引 后, 插入新记录时DBMS会自动检查新记录在该列上是否取了 重复值。 这相当于增加了 一个UNIQUE约束  在Course表CName上创建一个非唯一性的非聚簇索引CREATE nonclustered INDEX IX_CName ON Course(CName) WITH DROP_EXISTING数据库原理与设计WITH子句中可包含索引的创建选项,DROP_EXISTING是指如果表中已存在同名的索引则将其删除, 重建索引。 建立索引 在一个基本表上最多只能建立一个聚簇索引聚簇索引可以提高某些类型的查询效率数据库原理与设计 聚簇索引可以提高某些类型的查询效率 聚簇索引的适用范围 很少对基表进行增删操作 4. 管理索引(1) 查看索引sp_helpindex可以查看特定表上的索引信息例2-19 查看Course表的索引信息。EXECUTE sp_helpindex Course数据库原理与设计(2) 删除索引DROP语句将其删除:DROP INDEX <表名>.<索引名>DROP INDEX Course. IX_CName drop index SC.pk_SC在表中指定主键时, 将自动创建一个名为“PK_”且后跟表名的主键索引,主键索引是唯一索引的特殊类型唯一索引的特殊类型ALTER TABLE SC DROP CONSTRAINT pk_SC数据库原理与设计 注意: 如果索引是在CREATE TABLE语句中创建的,只能用ALTER TABLE语句删除索引; 如果索引是CREATE INDEX创建的, 可用DROP INDEX删除。DROP INDEX删除。数据库原理与设计 DROP INDEX语句删除多个索引, 索引之间需用逗号隔开 被删除的索引一定要以<表名>.<索引名>”的形式给出 SELECT 目标列1,目标列2...FROM 表名或视图名WHERE 条件表达式列名GROUP BY 列名1HAVING 条件表达式ORDER BY 列名2数据查询Data Query languageSQL的核心是查询select查询, 就是用SELECT语句实现对表中的数据的请求。 该语句的功能是从中的数据的请求。 该语句的功能是从表或视图中查询满足条件的记录 1.输出表中某些列例:查询Student的SNo、 SName和Ssex。SELECT SNo SName SSexSELECT SNo, SName, SSexFROM Student2.输出表中全部列3.为列指定别名4 .添加派生列5.消除重复记录6 .限制返回行数SELECT -----FROM----WHERE结构的使用例:查询Student表中的所有信息例:从Student选取SNo、 SName输出,字段名显示为中文(成绩) 一列统一增加20分作为输出, 消去结果集中重复的行SELECT DISTINCT SName,SSexFROM StudentFROM Student例: 查询Student表中的所有字段SELECT *FROM StudentSELECT 学号=SNo, 姓名=SNameFROM StudentScore= Score+20FROM SCSELECT SNo AS 学号例:查询SC表所有信息, 将Score例: 从Student选取SName和SSex例: 查询Student表中的所有字段,在结果集中显示表中的前3条记录SELECT SNo, CNo, SELECT TOP 3 * 1. 语法格式SELECT 目标列表达式…INTO 新表名FROM 表名或视图名2.创建新表并添加记录例: 查询学生的学号和姓名写入到自动创建的表Student2中SELECT SNo, SNameINTO Student2FROM Student 1. 比较运算符包括: >、 <、 =、 >=、 <=、 <>和!=、 !>、 !<例: 查询SC表中的所有字段, 在结集中绩结果集中显示成绩不及格的记录2.范围运算符3.列表运算符4.模式匹配运算符4.模式匹配运算符5.空值运算符6.逻辑运算符格的SELECT *FROM SCWHERE Score<60指定范围内NOT BETWEEN—测试表达式的值不包含在指定范围内例: 查询SC表中的所有字段, 在结果集中显示成绩在70为“赵丽”或“王华”或“李刚”的记录②匹配任何单个字符例 查询St dt表中的所有字段例:查询Teacher表中的所有字段BETWEEN—测试表达式的值包含在IN—测试表达式的值等于列表中的某一个值;NOT IN—测试表达式的值不等于列表中的任何一个值。例: 查询Student所有字段, 显示姓名① %: 匹配任意类型和长度的字符串WHERE SName LIKE '刘%'SELECT *NOT—运算符右侧的表达式结果取反90的记录LIKE—列取值与指定的字符通配格式相符NOT LIKE—列取值不与指定的字符通配格式相符FROM Student示班级编号为空的学生记录式结果即成立例:查询Student中的所有字段, 在结果集中显示姓氏为“刘”的学生记录SELECT *例: 查询Student表中的所有字段, 显OR—运算符两端有一个成立时, 表达显显IS NULL—列值为空IS NOT NULL—列值不为空式结果才为成立AND—运算符两端同时成立时, 表达 1. ORDER BY子句ORDER BY 列名1 ASC|DESC[,列名2…]2.默认顺序为升序3.两个字段排序4.排序列的选择5.是查询最后一步例: 查询SC表中所有及格学生的记录, 并按学号降序排序的记录, 按姓名降序排序, 如姓名相同则按照教师编号升序排序SELECT *FROM Teacher排序子句可包括未出现在选择列SELECT *FROM SCWHERE Score>=60例:查询Teacher表中全部男教师可将排序列指定为列名或列别名和表达式, 或位置作, 是即将输出之前的处理所以排序子句一般也出现在查询语句的最后SELECT score 成绩 FROM sc ORDER BY 成绩排序应是查询语句的最后一步工 设有一个SPJ数据库, 包括S, P, J, SPJ四个关系模式:S( SNO, SNAME, STATUS, CITY);P(PNO, PNAME, COLOR, WEIGHT);J(JNO, JNAME, CITY);SPJ( SNO, PNO, JNO, QTY);备注备注:select ---from ---where---结构练习供应商表S由供应商代码(SNO) 、 供应商姓名(SNAME) 、 供应商状态(STATUS) 、 供应商所在城市(CITY) 组成;零件表P由零件代码(PNO) 、 零件名(PNAME) 、 颜色(COLOR) 、 重量(WEIGHT) 组成;工程项目表J由工程项目代码(JNO) 、 工程项目名(JNAME) 、工程项目所在城市(CITY) 组成; 供应情况表SPJ由供应商代码(SNO) 、 零件代码(PNO) 、 工程项目代码(JNO) 、 供应数量(QTY) 组成, 表示某供应商供应某种零件给某工程项目的数量为QTY。 供应商表s零件表pp供应信息表spjselect ---from ---where---结构练习工程表j •查询所有的供应商号。查询北京的供应商号。查询蓝色的零件号。查询供应项目j1零件p3的供应商号及相应数量。查询供应项目j1零件p3的供应商号及相应数量, 并按数量多少升序排列。••select ---from ---where---结构练习•• 集合函数集合函数用来将结果集中的记录进行汇总计算, 并将结果生成一条新的记录。 SQL中常用集的集合函数有5种。1. AVG函数2. SUM函数3. MAX函数4. MIN函数5. COUNT函数6. 习题SELECT -----FROM----WHEREGROUP BY -----HAVING----结构用于统计一个数值型字段的平均值:AVG ( [ALL | DISTINCT ] 列名)SUM ( [ALL | DISTINCT ] 列名)MAX ( [ALL | DISTINCT ] <列名>)MIN ( [ALL | DISTINCT ] <列名>)例: 查询选修了 00005课程的最低成绩。SELECT MIN(Score)FROM SC例: 查询所有学生的平均成绩例: 查询SC表, 在结果集中显示所有00005课程及格学生的成绩总和SELECT MAX(Score)FROM SCFROM StudentSELECT AVG(Score)FROM SCSELECT SUM(Score)用于统计一个数值型字段的总和:用于统计一个数值型字段的最大值:用于统计一个数值型字段的最小值:统计结果集记录的个数:COUNT ( [*|ALL | DISTINCT ] 列名)•计算2号课程的学生平均成绩例: 查询00005课程成绩最高的记录。例: 查询学生的总人数。SELECT COUNT(*)•查询学习了 2号课程的学生的最高分•查询选修了 课程的学生人数 1. 语法格式GROUP BY按列名所指定的列的取值进行分组, 取值相同的记录分成一组, 对每一组进行记录分成组, 对每汇总计算。 每一组生成一条记录 :GROUP BY 列名例:查询每个课程号及相应选课人数Select Cno,COUNT(Sno)From SCGroup by Cno组进行2.细化集函数 1. 语法格式用于对分组以后的结果进行筛选:GROUP BY 列名HAVING 条件表达式HAVING 条件表达式2.必须和GROUP BY 一起用3.HAVING和WHERE区别例: 查询选了 3门以上课的学生学号HAVING 只应用于组而 WHERE 子句应用于基本表SELECT SNo FROM Sc Group By Sno HAVING count(*)>3Group By ssex having count(*)>2SELECT ssex,count(*) FROM student where clno='030001' 1. COMPUTE子句与GROUP BY的区别是:除显示统计结果外, 还显示参加统计的各组数据的详细信息:ORDER BY 列名COMPUTE 集合函数 BY 列名例:查询各班学生人数和该班级的所有学生记录。SELECT *FROM StudentORDER BY CLno COMPUTEORDER BY用于将数据按分组所参照的列值进行排序2.示例 设有一个SPJ数据库, 包括S, P, J, SPJ四个关系模式:S( SNO, SNAME, STATUS, CITY);P(PNO, PNAME, COLOR, WEIGHT);J(JNO, JNAME, CITY);SPJ(SNO, PNO, JNO, QTY);备注备注:单表操作练习供应商表S由供应商代码(SNO) 、 供应商姓名(SNAME) 、 供应商状态(STATUS) 、 供应商所在城市(CITY) 组成;零件表P由零件代码(PNO) 、 零件名(PNAME) 、 颜色(COLOR) 、 重量(WEIGHT) 组成;工程项目表J由工程项目代码(JNO) 、 工程项目名(JNAME) 、工程项目所在城市(CITY) 组成;供应情况表SPJ由供应商代码(SNO) 、 零件代码(PNO) 、 工程项目代码(JNO) 、 供应数量(QTY) 组成, 表示某供应商供应某种零件给某工程项目的数量为QTY。 单表操作练习供应商表s零件表pp供应信息表spj工程表j 查询供应商S1总共供应几个项目。查询每个供应商供应项目的项目数。查询供应项目多于2个的供应商号。统计项目j1由几个供应商提供零件。按照项目来统计其对应的供应商数量按照项目来统计其对应的供应商数量。统计供应商多余3个的项目号。查询最轻的零件的质量是多少。查询质量最轻的零件号。查询质量最重的零件号。统计汇总操作练习编写分类统计SQL的关键:(1) 明确按照“什么” 来统计(分类字段)(2) 明确要统计的内容(统计字段) 统计汇总操作练习 查询固定资产表中有多少个不同的部门。 查询表中有多少种物资。查询表中是否有重名的物资 查询表中是否有重名的物资。 要查询每个使用部门到底使用了 多少种物资。 2.4.2连接相关表中的数据连接查询(多表) 指从多个有关联的表中查询连接查询(多表) 指从多个有关联的表中查询数据, 其基本语法跟单表查询类似。 一般来说,数据, 其基本语法跟单表查询类似。 一般来说, 多表查询要在表查询要在wherewhere条件中指定表连接的条件条件中指定表连接的条件, 如果没有连接条件, 则查询结果是这多个查询表的笛卡没有连接条件, 则查询结果是这多个查询表的笛卡儿积儿积儿积。儿积。建立两表之间关系的最简单的运算符是等号建立两表之间关系的最简单的运算符是等号( (= =) 。 如果使用等号两个表连接起来。 这种类型) 。 如果使用等号两个表连接起来。 这种类型的连接把来自两个表的在指定列中具有相等值的行的连接把来自两个表的在指定列中具有相等值的行连接起来。连接起来。多, 如果 2.4.2连接相关表中的数据 相等连接(内连接相等连接(内连接、 、 外连接)外连接) 自连接自连接 不相等连接不相等连接 优化连接查询优化连接查询 1 . 显示成绩单, 包括学生姓名、 课程名称、 成绩。 多表等值连接查询的注意事项:1. 在from语句中指明数据所涉及的表, 在where条件中须指明表和表之间的连接条件。select sname, cname, scorefrom student,course,scwhere student.sno=sc.snowhere student.sno sc.snoand course.cno=sc.cno2.由于多个表中可能存在相同列, 所以返回列的时候需要指明具体为哪个表中的列。3.在SQL语句中, 使用表别名可以简化代码, 增强可读性。连接之一--- 等值连接数据库原理与设计  例:查询选修了 “数学”课的学生学号。SELECT SNo FROM SC a, Course bWHERE a.CNo=b.CNo AND CName= '数学'例:学生的学号、 姓名及成绩)查询“数学”课不及格的学生名单(输出 SELECT a.SNo, SName, ScoreFROM Student a, SC b, Course cWHERE a.SNo=b.SnoAND b.CNo=c.CNoAND CName= '数学' AND Score<60 查询的结果来自两张表, 而决定查询结果的因素涉及三张表的信息。 连接之一----等值连接练习供应商表s零件表pp供应信息表spj工程表j 1.查询为天津的项目提供零件的供应商号及其城市。2.查询为天津的项目提供红色零件的供应商号及其城市。连接之一----等值连接练习3.查询为“三建” 工程提供零件的供应商名称。4.查询供应以“三” 开头的工程的供应商号及其城市 2. 等值连接的分类------内连接和外连接内连接和外连接---内连接(简单连接) , 用于返回满足连接条件的记录, 废弃两个表中不匹配的行。---外连接是简单连接的扩展, 它不仅会返回满足连接条件的所有行, 而且还会返回不满足连接条件的某张表的所有行左外连接的某一张表的所有行。 左外连接、 右外连接和完全外连接右外连接和完全数据库原理与设计 语法一:语法一: 使用连接操作符(使用连接操作符(= = 、 、 * * = = 、 、 = *= * ) 来实现) 来实现1 1) 内连接) 内连接SELECT table1.col,table2.col FROM table1,table2SELECT table1.col,table2.col FROM table1,table2WHERE table1.col = table2.colWHERE table1.col = table2.col2 2) 左外连接) 左外连接SELECT table1.col,table2.col FROM table1,table2SELECT table1.col,table2.col FROM table1,table23、 等值连接的语法WHERE table1.col WHERE table1.col * =* = table2.coltable2.col3 3) 右外连接) 右外连接SELECT table1.col,table2.col FROM table1,table2SELECT table1.col,table2.col FROM table1,table2WHERE table1.col WHERE table1.col =* =* table2.coltable2.col 语法二: 在FROM子句中指明连接类型, 在ON子句中指明连接条件SELECT table1.col,table2.colFROM table1 [INNER | LEFT | RIGHT | FULL] JOINtable2ON t bl 1l t bl 2lON table1.col=table2.col3、 等值连接的语法 4、 外连接的应用例如: 查找学生的基本情况(学号、 姓名、年龄) , 及其选课情况(包括课程号、 成绩)例如例如: 查找学生表中哪些学生没有选修过课程。查找学生表中哪些学生没有选修过课select student.sno,sname,sage,cno,scorefrom student, scwhere student.sno*=sc.sno数据库原理与设计 在teacher表中, 查询职工“王刚” 的上级领导姓名连接之二----自连接数据库原理与设计处理的方法: 将物理上独立存在的一张表, 逻辑上看作两张表来处理。实现的技巧: 通过为表起不同的别名来实现。 将Teacher表看作t1(普通教师)将Teacher表看作t2(直接领导)t1和t2的关系: t1.mgr=t2.tnoSQL命令:select t2.tnamefrom teacher t1,teacher t2where t1.mgr=t2.tno EMPEMP表别名为表别名为workerworkerEMPEMP表别名为表别名为managermanagerSQL>select manager.ename from emp worker,emp managerSQL>select manager.ename from emp worker,emp managerwhere worker.mgr=manager.empnowhere worker.mgr=manager.empnoand worker.ename='BLAKE';and worker.ename='BLAKE'; 一张表的两个副本之间连接, 可以将同一张表的不同行连接起来。 必须为表指定两个不同的别名.SELECT ...FROM 表1 新表名1,表1 新表名2,WHERE 新表名1.列名=新表名2.列名连接之二----自连接数据库原理与设计例: 在Student中查询姓名相同的学生信息.例: 和李刚同一个班的学生信息--自身连接 数据库原理与设计例如:SELECT *FROM Student ST1, Student ST2WHERE ST1.SName=ST2.SNameAND ST1.SNo<>ST2.Sno在Student中查询姓名相同的学生信息 例如: 和李刚同一个班的学生信息--自身连接Select S2.SName From Student S1, Student S2Where S1. CLno= S2. CLnoAND S1.SName=‘李刚’数据库原理与设计 连接之三----不等值连接 不等值连接多表操作的连接条件中不为“=”运算符的查询叫不等值连接。关系R关系S关系代数表示:关系代数表示小于小于RSB<DT-SQL命令表示:SELECT *FROM R , SWHERE R.B < S.D 连接之三----不等值连接例: 显示教师的基本信息(包括教师的编号,姓名、 性别, 出生日期, 年龄, 工资, 及工资的级别) 多表连接的性能低下多数是因为过滤操作的次序不合理与SQL书写不规范造成的。优化连接查询(1) 过滤操作次序问题1、 系统执行的效率:先过滤数据, 后进行连接。2书写角度2、 书写角度: 先书写表连接, 后书写过滤条件。先书写数据库原理与设计(2) 书写规则问题3、 编写规则的SQL语句, 不仅可以提高SQL命令的可读性, 而且在网络数据库环境下有利于在服务器内存中命中, 从而提高SQL执行的效率 优化连接查询(1) 过滤操作次序问题SQL SERVER数据库采用自下而上的顺序来解析WHERE子句, SQL SERVER建议表之间的连接应该写在其他WHERE条件之前。SELECT a.SNo, SName, ScoreFROM Student a, SC b, Course cWHERE a.SNo=b.SnoAND b.CNo=c.CNoAND CName= '数学' AND Score<60 SELECT a.SNo, SName, ScoreFROM Student a, SC b, Course cWHERE CName= '数学'AND Score<60AND a.SNo=b.SnoAND b.CNo=c.CNo? ? ? 哪种书写方式更好呢? 优化连接查询(2) 书写规则问题实例实例SGASGAShared poolShared poolLibrary cacheLibrary cacheLibrary cacheLibrary cachesql1 sql2内存内存结构结构用户用户进程进程服务器服务器服务器服务器进程进程Redo logRedo logBuffer cacheBuffer cacheBuffer cacheBuffer cacheDatabaseDatabaseB ffB ffBuffer cacheBuffer cachehhData DictionaryData DictionarycachecachePMONPMONSMONSMONDBWRDBWRLGWRLGWRCKPTCKPTOthersOthers后台后台进程进程PGAPGA用户用户进程进程服务器服务器进程进程PGAPGA重做日志文件重做日志文件数据文件数据文件sqln… SQLSQL语句的命中率越高, 共享缓冲区的利用率越高, 数语句的命中率越高, 共享缓冲区的利用率越高, 数据库的性能就越好。据库的性能就越好。优化策略:优化策略: ------------制定制定SQLSQL语句编码标准。语句编码标准。※※优化连接查询(2) 书写规则问题数据库原理与设计编写完全相同的编写完全相同的SQL 语句文本相同语句文本相同 大小写相同大小写相同 赋值变量相同赋值变量相同SQL语句语句: :SELECTSELECT * SELECT * FROM student WHERE Sno=‘03001’;SELECT * FROM student WHERE Sno=‘03001’;* FROMFROM studentstudent WHEREWHERE SnoSno=‘03001’;=‘03001’;? 2.4.4 集合查询(合并查询)(1) 什么是集合(合并) 查询(2) 集合(合并) 查询使用规则(3) 集合(合并) 查询的优劣势分析数据库原理与设计 (1) 什么是集合查询(合并查询)为了合并多个为了合并多个SELECTSELECT语句的查询结果, 可以使用集合运算符语句的查询结果, 可以使用集合运算符SQL Server只支持UNION 和UNION ALL操作,不支持INTERSECT和MINUS( (set operatorset operator) 进行合并查询。) 进行合并查询。------其基本语法:其基本语法:SELECT SELECT 语句语句1……1……[ [UNIONUNION||UNION ALLUNION ALL| | INTERSECTINTERSECT||MINUSMINUS] ]数据库原理与设计例: 查选修了 00001 或00005课程的学生Select sno from sc where cno=‘00001’SELECT SELECT 语句语句2……2……Select sno from sc where cno=‘00005’UNION (2) 合并查询使用规则:(1) Select 语句必须选择同样数量的列。(2) 相应的列必须具有相同的类型与长度。Select sno,score from sc where cno=‘00001’数据库原理与设计Select sno,score from sc where cno=‘00005’UNION(3) 在合并查询的结果集合中, 自动以第一个查询的字段名作为列名, 因此, 排序只能按照第一个查询的列名或者为序进行排序。 分析一下查询实现哪个更好呢?例, 查询选修了 00001号课程或者00005号课程的学生学号SELECT sno FROM sc WHERE cno='00001'unionSELECT sno FROM sc WHERE cno='00005'等价与SELECT sno FROM sc WHERE cno='00001' or cno='00005' order by sno 分析一下查询实现哪个更好呢?例, 查询雇员表EMP中, 工资大于2500 并且岗位为MANAGERSELECT ename,sal,job FROM emp WHERE sal>2500intersectSELECT ename,sal,job FROM emp WHERE job='MANAGER';等价于SELECT ename,sal,job FROM emp WHERE sal>2500 AND job='MANAGER';ORDER BY ename (3) 合并查询的优劣势分析劣势劣势a. a.非合并查询比合并查询的效率高非合并查询比合并查询的效率高b. b.书写上来看非合并查询比合并查询书写简洁书写上来看非合并查询比合并查询书写简洁优势优势合并查询的可读性强, 比较容易实现。合并查询的可读性强, 比较容易实现。 2. 4. 3 嵌套查询在在SQLSQL中, 一个中, 一个SELECTSELECT——FROMFROM——WHEREWHERE语句称为一语句称为一个个查询块查询块, , 将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块的SelectSelect语句、语句、FromFrom语句、语句、 WhereWhere条件或是条件或是HavingHaving条件中的查询称为嵌套查条件中的查询称为嵌套查询或子查询询或子查询。 前者为。 前者为子查询(内查询)子查询(内查询) , 后者为, 后者为父查询(外查父查询(外查询)询) 。 。格式一:格式一:格式一:格式一:SELECT…… SELECT…… ( (SELECT …… FROM …… WHERE ……SELECT …… FROM …… WHERE ……) ) a aFROM ……FROM ……WHERE …WHERE …格式二格式二: :SELECT……SELECT……FROM ……FROM ……WHERE …WHERE …( (SELECT ……SELECT ……FROM ……FROM ……WHERE ……WHERE ……) )格式三格式三: :SELECT……SELECT……FROM ……FROM ……( (SELECT ……SELECT ……FROM ……FROM ……WHERE ……WHERE ……) ) b bWHERE ……WHERE …… 嵌套查询的分类( (4 4) ) 带有( (4 4) ) 带有带有EXISTSEXISTS谓词的子查询谓词的子查询带有EXISTSEXISTS谓词的子查询谓词的子查询( (1 1) ) 带有带有比较运算符比较运算符的子查询行外层查询.内层查询执行的结果是外层查询执行的条件的子查询( (2 2) ) 带有带有in in关键词关键词的子查询的子查询( (3 3) ) 带有带有anyany或或allall关键词关键词的子查询的子查询--------不相关子查询不相关子查询相关子查询相关子查询----相关子查询相关子查询不相关子查询的特点:先执行内层查询,后执( (5 5) ) 带有带有NOTNOT EXISTSEXISTS谓词的子查询谓词的子查询----相关子查询的特点:父查询所查阅的当条记录, 是否满足子查询中提供的条件, 如果满足, 子查询返回逻辑真, 否则返回逻辑假。 嵌套查询嵌套查询--------( (1 1) ) 带有比较运算符的子查询带有比较运算符的子查询特点:特点: 子查询的返回结果是一个值子查询的返回结果是一个值。 。 子查询返回的结果作为父查询的条件为父查询的条件( (先执行子查询先执行子查询, ,后执行父查询子查询返回的结果作后执行父查询) )。 。例如,查找和李刚同一个班的其他学生信息Select * from student hCL(lt CLwhere CLno =(select CLno from Studentwhere SName=‘李刚’ )fSt dt 嵌套查询嵌套查询--------( (1 1) ) 带有比较运算符的子查询带有比较运算符的子查询例: 查询“计算机0301班” 的学生姓名 ---利用连接查询来实现SELECT Sname FROM student , ClassWHERE Class . CLno= student. CLno 例: 查询“计算机0301班” 的学生姓名---利用嵌套查询来实现SELECT Sname FROM studentWHERE CLno= (SELECT CLno FROM Class WHERE CLname=‘计算机0301班’ )AND CLname=‘计算机0301班’ 嵌套查询嵌套查询--------( (2 2) ) 带有带有inin关键字的子查询关键字的子查询特点:特点: 子查询的返回结果是一个集合子查询的返回结果是一个集合。 子查询返回的结果作为父查询的条件为父查询的条件( (先执行子查询先执行子查询, ,后执行父查询后执行父查询) )。 。。 子查询返回的结果作例: 查询选修了 “数学” 课的学生名单。----利用连接查询来实现select snamefrom student a course b sc cfrom student a,course b,sc cwhere a.sno=c.snoand b.cno=c.cnoand cname=‘数学’----利用嵌套查询来实现select sname from studentwhere sno in (select sno from scwhere cno=(select cno from coursewhere cname='数学')) 例: 查体育课不及格的男生名单:SELECT SnameFROM StudentWHERE Ssex=‘男’ AND Sno IN(S(SELECT Sno FROM SCCSOSC数据库原理与设计WHERE Score<60 AND cno=(SELECT Cno FROM CourseWHERE Cname=‘体育’ )) Select sno from scwhere cno=‘00002’Select sno from sc where cno=‘00001’and cno=‘00002’where cno=‘00001’)例如, 查询同时选修00001 和00002两门课的学生学号Select sno from sc 数据库原理与设计and sno in (select sno from sc SELECT cno,cname FROM courseWHERE cno NOT IN (SELECT cno FROM sc )例如, 查询从未被选修的课程。 嵌套查询嵌套查询----( (3 3) ) 带有带有anyany或或allall关键词的子查询关键词的子查询>all>all 大于子查询结果中的所有值大于子查询结果中的所有值<all<all小于子查询结果中的所有值小于子查询结果中的所有值>any 大于子查询结果中的某个值大于子查询结果中的某个值>any<any<any 小于子查询结果中的某个值小于子查询结果中的某个值y y 小于子查询结果中的某个值小于子查询结果中的某个值> > SelectSelect min(min( ) )< < SelectSelect max(max( ) )> > SelectSelect max(max( ) )< < SelectSelect min(min( ) )例, 查询其它班中比030002班所有学生年龄都大的学生名单。select sname from studentwhere clno<>'030002'and sage>all(select sage from student where clno='030002')( ( ) ) 查询其它班中比030002班所有学生学分都少的学查询其它班中比030002班所有学生学分都少的学生姓名SELECT SName FROM StudentWHERE CLno<>'030002' AND Scredits< ALL(SELECT Scredits FROM Student WHERE CLno='030002')数据库原理与设计 嵌套查询----不相关子查询练习供应商表s零件表pp供应信息表spj工程表j 1.查询为天津的项目提供零件的供应商号及其城市。2.查询为天津的项目提供红色零件的供应商号及其城市。3.查询为“三建” 工程提供零件的供应商名称。询为建程提供零件的供应商名称嵌套查询----不相关子查询练习 嵌套查询----不相关子查询练习1.检索”联华公司”中低于本公司平均工资的职工工号和姓名.(10题2小题)(1)查找联华公司职工的平均工资.(涉及到works,comp)(2)查找联华公司工资低于(1)的职工工号和姓名(涉及到emp,works,comp)检索职工人数最多的公司的编号和名称(10题4小题).检索数多的的编名称小2.(1) 按照公司来统计公司的人数(涉及到works)(2) 从(1)的结果中查找人数最多的公司的人数(3) 根据(2)查询的结果,从(1)的结果中查找人数最多的公司的编号.(4) 已知公司编号,在COMP表中查找公司编号和名称 嵌套查询----不相关子查询练习3. 检索平均工资高于联华公司平均工资的公司编号和名称.(10题的6小题)(1) 查找联华公司职工的平均工资.(涉及到works,comp)(2) 统计各公司的平均工资.(3) 在(2)的结果集中查找满足条件(1)的公司编号(3) 在(2)的结果集中查找满足条件(1)的公司编号.(4) 已知公司编号在COMP中查找公司编号和名称. Exists 表示“存在” 的意思, 表示父查询所查阅的当条记查询的SELECT语句也不需要指明具体的列(仅用一个*就可以) , 因为内层查询不需要向外层查询传递具体的值。(2) 在内层查询中必须指明与外层查询的连接条件, 确保内外两层查询SELECT sno,snameSELECT sno,snameFROM student WHERE sno in (SELECT sno FROM scWHERE cno='00001' );录, 是否满足子查询中提供的条件, 如果满足, 子查询返回逻辑真, 否则返回逻辑假。例: 查询选修了 课程号为‘00001’的学生的学号和姓名。SELECT sno,sname嵌套查询嵌套查询----( (4 4) ) 带有带有EXISTSEXISTS关键词的子查询关键词的子查询使用exists连接子查询注意事项:(1) exists前不需要加列名, 内层数据库原理与设计FROM student aWHERE EXISTS(SELECT * FROM sc bWHERE cno='00001' AND a.sno=b.sno );相关联。 数据库原理与设计查询所有选了 00008课程的学生姓名SELECT SName FROM StudentWHERE EXISTS(SELECT * FROM SC WHERE SNo= Student.SNo AND CNo='00008')03001 数据库原理与设计2.查询从未被选修的课程号和课程名SELECT cno,cnameFROM course aWHERE NOT EXISTS(SELECT * FROM sc bWHERE b.cno=a.cno) 2.5 数 据 更 新数据库原理与设计包括插入数据, 修改数据和删除数据 2.5.1 插入数据1. 插入单个记录例2-84 将学号为“06001”、 姓名为“王立” 、 性别为“男” 的学生记录插入Student表中。INSERT INTO Student (SNo,SName,SSex)VALUES ('06001', '王立', '男')2. 插入子查询结果例2-86 把Student表中所有男生的记录插入到表ss中。INSERT INTO ss SELECT * FROM Student WHERE SSex='男'数据库原理与设计 2.5.2 修改数据UPDATE 表名SET 列名=表达式 [, 列名=表达式][WHERE 条件 ] ...

    关注我们

  • 新浪微博
  • 关注微信公众号

  • 打印亚博足球app下载
  • 复制文本
  • 下载第二章 关系数据库语言【精品PPT】.XDF
  • 您选择了以下内容