数据库系统课件第3章 SQL.pptx
《数据库系统课件第3章 SQL.pptx》由会员分享,可在线阅读,更多相关《数据库系统课件第3章 SQL.pptx(173页珍藏版)》请在三一文库上搜索。
1、目 录CONTENTSSQL语句产生发展SQL语句特点SQL语句功能1231.SQL语句产生发展 SQL(Structed Query Language,结构化查询语言)是操作关系数据库的通用语言。SQL名为查询语言,但不只支持数据库查询操作,其功能还包括数据定义、数据操纵、数据控制等 数据库管理系统尽管软件存在差异,但都有SQL软件或与SQL的接口软件。虽然各个数据库厂商附带的 SQL 软件产品对 SQL 语言支持很相似,但也存在一定的差异1.SQL语句产生发展 IBM 研究人员在 20 世纪 70 年代研究出 SQL 原型,命名为 SEQUEL 现在的 SQL 标准已经包括了:SQL 框架
2、、SQL 调用接口、SQL 永久存储模块、SQL 宿主语言绑定、SQL 外部数据管理、XML 相关规范等内容。2.SQL特点 SQL 是一个综合、功能强大又简单易学的语言,从数据库定义到数据库维护都提供了相应功能。其主要特点如下:p 一体化p 高度非过程化p 语言简洁p 多种使用方式3.SQL语言功能概述 SQL 的功能主要包括:数据定义、数据查询、数据操纵、数据控制 Database Principle and Application数据库原理及应用3.2.1 数据库定义及维护重庆理工大学 计算机科学与工程学院目 录CONTENTS数据库定义数据库维护121.数据库定义 数据库中的所有的数据
3、、对象和事务日志均以文件的形式保存。根据作用不同,这些文件可分为数据文件与事务日志文件。数据文件可根据数据存储需要进行组织,除了必须的一个主数据文件,还可以包括一个或多个次数据文件。1.数据库定义 主数据文件用于存储数据库的系统表,其文件扩展名为 mdf。次数据文件用于存储主数据文件中未存储的数据和数据对象。其文件扩展名为 ndf。事务日志文件用于记录对数据库的操作情况。其文件扩展名为 ldf。1.数据库定义数据库使用 CREATE DATABASE 语句实现,其一般格式如下:CREATE DATABASE database_name ON(NAME=logical_file_name,FIL
4、ENAME=os_file_name|filestream_path ,SIZE=size KB|MB|GB|TB ,MAXSIZE=maxsize KB|MB|GB|TB|UNLIMITED,FILEGROWTH=growth_incrementKB|MB|GB|TB|%)LOG ON(NAME=logical_file_name,FILENAME=os_file_name|filestream_path ,SIZE=size KB|MB|GB|TB ,MAXSIZE=maxsize KB|MB|GB|TB|UNLIMITED,FILEGROWTH=growth_incrementKB|MB
5、|GB|TB|%)1.数据库定义示例1:创建一个只设置名称的数据库,数据库名称为 dbtest。CREATE DATABASE dbtest2.数据库维护示例2:修改数据库 sjkDB 中数据文件的初始大小,将其初始大小改为 9MB,最大为120MB。ALTER DATABASE sjkDBMODIFY FILE(NAME=sjkDB_data,SIZE=9,MAXSIZE=120)2.数据库维护示例3:为数据库 sjkDB 添加新的日志文件,逻辑名称为 sjkDBlog1,存储路径为E:teaching,物理文件名为 sjkDBlog1.ldf,初始大小 3MB,增量 1MB,最大 20MB
6、。ALTER DATABASE sjkDBADD LOG FILE(NAME=sjkDBlog1,FILENAME=E:teachingsjkDBlog1.ldf,SIZE=3,MAXSIZE=20,FILEGROWTH=1)2.数据库维护示例4:将数据库 test 更名为 test_1示例5:使用 DROP DATABASE 语句删除数据库 dbtest。ALTER DATABASE testmodify name=test_1DROP DATABASE dbtest Database Principle and Application数据库原理及应用3.2.2 表定义及维护重庆理工大学 计
7、算机科学与工程学院目 录CONTENTS表定义数据类型12表维护3 1.表定义:所要定义的基本表的名字:组成该表的各个属性(列):涉及相应属性列的完整性约束条件:涉及一个或多个属性列的完整性约束条件 注:如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上,否则列级或表级都可以。CREATE TABLE(,);1.表定义例 1 建立“学生”表Student。学号是主码,姓名取值唯一。CREATE TABLE Student (SNO CHAR(9)PRIMARY KEY,/*列级完整性约束条件,Sno是主码*/SName CHAR(20)UNIQUE,/*Sname取唯一值*/Ssex
8、 CHAR(2),Sage SMALLINT,Sdept CHAR(20);命名格式:constraint constraint s_pkconstraint s_uk2.数据类型数据类型数据类型含义含义CHAR(n),CHARACTER(n)长度为长度为n的定长字符串的定长字符串VARCHAR(n),CHARACTERVARYING(n)最大长度为最大长度为n的变长字符串的变长字符串INT,INTEGER长整数(长整数(4字节)字节)SMALLINT短整数(短整数(2字节)字节)BIGINT大整数(大整数(8字节)字节)NUMERIC(p,d)定点数,由定点数,由p位数字(不包括符号、小数点
9、)组成,小数后面有位数字(不包括符号、小数点)组成,小数后面有d位数字位数字DECIMAL(p,d),DEC(p,d)同同NUMERICDATE日期,包含年、月、日,格式为日期,包含年、月、日,格式为YYYY-MM-DDTIME时间,包含一日的时、分、秒,格式为时间,包含一日的时、分、秒,格式为HH:MM:SS3.表维护-修改表ALTER TABLE ADD constaint /*增加列*/DROP COLUMN /*删除列*/ALTER COLUMN constaint/*修改列*/3.表维护-删除表【例】将例3-9的“商品种类”表添加一列,用以存放描述商品大类的数据,比如牙刷属于日用品类
10、。ALTER TABLE category ADD Cat_CategoryNO varchar(20)【例】将例3-8 的goods表的barcode列删除。ALTER TABLE goods DROP COLUMN barcode【例】将例3-10的Supplier表Suppliername列的数据类型修改为nvarchar(200),且不允许为空。ALTER TABLE Supplier ALTER COLUMN Suppliername nvarchar(200)not null 3.表维护-删除表DROP TABLE RESTRICT|CASCADE;RESTRICT:删除表是有限制
11、的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象,则此表不能被删除 CASCADE:删除该表没有限制。在删除基本表的同时,相关的依赖对象一起删除 3.表维护-删除表例 2 删除Student表 DROP TABLE Student CASCADE;基本表定义被删除,数据被删除表上建立的索引、视图、触发器等一般也将被删除 Database Principle and Application数据库原理及应用3.2.3 完整性定义及维护重庆理工大学 计算机科学与工程学院目 录CONTENTS完整性约束定义完整性约束维护121.完整性约束定义数据的完整性是指数据的正确性与相容性的要求。
12、完整性约束的作用范围可以分为:列级约束:某列的约束;比如,年龄 0元组约束:元组中各字段之间联系的约束;比如:结束日期=开始日期关系约束:关系之间联系的约束;比如:供货商表里没有的商家不能提供商品;SQL代码格式:ALTER TABLE ADD /*增加约束*/一、完整性约束示例4:为员工表添加主码 示例5:为薪资表的薪级名称列添加 UNIQUE 约束SQL代码:ALTER TABLE 员工表ADD CONSTRAINT pk_yg PRIMARY KEY(员工编号)/*pk_yg 约束名*/SQL代码:ALTER TABLE 薪资表ADD CONSTRAINT U_xinzname UNIQ
13、UE(薪级名称)/*U_xinzname 约束名*/1.完整性约束定义二、自定义完整性约束 示例6:薪资表的基础薪资列定义 DEFAULT 约束 示例7:薪资表的实发薪资列添加 CHECK 约束,使其值小于应发薪资列SQL代码:ALTER TABLE 薪资表ADD CONSTRAINT DF_jichu DEFAULT 2000 FOR 基础薪资SQL代码:ALTER TABLE 薪资表WITH NOCHECK /*表示该约束对旧数据不作用,对新数据约束 */ADD CONSTRAINT CK_shifa CHECK(实发薪资应发薪资)1.完整性约束定义三、参照完整性约束参照完整性属于表间规则
14、,是指一个表中的主码与另一个表外码之间的关系,保证两个表的相容性。若主码与外码来自同一个表,则称自参照完整性。只要外码值存在,主码表中的数据就不能任意修改与删除,除非设置了级联删除与修改。SQL 语句使用:FOREIGN KEY 实现参照完整性 示例8:员工表的薪级编码列添加外码约束,引用薪资表的薪级编号SQL代码:ALTER TABLE 员工表ADD CONSTRAINT FK_xinjiFOREIGN KEY(薪级编号)REFERENCES 薪资表(薪级编号)1.完整性约束定义2.约束条件维护三、参照完整性约束 示例8:员工表的薪级编码列添加外码约束,引用薪资表的薪级编号l 对参照表(外码
15、表)添加元组或修改外码属性列时,都有可能破坏参考完整性约束;l 对被参照表(主码表)删除元组或修改主码属性列时,也会破坏参考完整性约束;处理这些破坏完整性约束的操作,三种方式:拒绝、级联删除(修改)和设置为空值;SQL代码:ALTER TABLE 员工表ADD CONSTRAINT FK_xinjiFOREIGN KEY(薪级编号)REFERENCES 薪资表(薪级编号)/*员工表:外码表;薪资表:主码表*/三、参照完整性约束 如何能让参照约束实现联级删除(修改)?示例9:员工表的薪级编码列添加外码约束,引用薪资表的薪级编号。定义该完整性约束可以级联删除或修改。SQL代码:ALTER TABL
16、E 员工表ADD CONSTRAINT FK_xinjiFOREIGN KEY(薪级编号)REFERENCES 薪资表(薪级编号)ON DELETE CASCADE /*级联删除*/ON UPDATE CASCADE /*级联修改*/1.完整性约束定义四、多种约束合并添加 上述的三种的约束,可以在表定义的时候一并进行定义添加。SQL代码:CREATE TABLE 员工表(员工编号 char(7)primary key,姓名 nchar(5)not null,入职日期 smalldatetime not null,转正日期 smalldatetime,手机号码 char(11)unique CH
17、ECK(手机号码 LIKE 0-90-90-90-90-90-90-90-90-90-90-9),薪级编号 char(3),FOREIGN KEY(薪级编号)REFERENCES 薪资表(薪级编号)ON DELETE CASCADEON UPDATE CASCADE)1.完整性约束定义五、完整性约束删除 对完整性约束的维护主要是修改和删除约束。修改约束可以先删除约束,再添加同名约束。删除约束的语句如下:SQL代码:ALTER TABLE DROP /*删除约束*/2.完整性约束维护 Database Principle and Application数据库原理及应用3.2.4 索引定义及维护重
18、庆理工大学 计算机科学与工程学院目 录CONTENTS索引的定义索引的维护121.索引的定义索引的由来:在很多时候,由于表中的数据量比较大时,查询操作会变得非常耗时,索引就是用来提高查询速度的重要手段。索引与图书目录类似,查找书本内容,可以在目录中直接查看该内容在书本中的页数,而不需要查阅整本书。目录索引索引表页码可以帮助快速找到对应的内容1.索引的定义 索引可以加快查询速度,但索引表本身会占用用户数据库空间,在对数据进行插入、更新、删除时,维护索引也会增加时间成本。因此,我们在建立索引时,需要综合考虑;索引可分为:聚集索引,非聚集索引,唯一索引等种类聚集索引:是指数据表中的数据按照索引关键字
19、顺序存储,表设置主码后,就会建立一个主码上的聚集索引。因为一个表的数据只能按照一种物理顺序存储,所以一个表上只能有一个聚集索引1.索引的定义非聚集索引:则不要求数据表的数据按照索引关键字顺序排序,表的物理顺序与索引关键字顺序不同。一个表上可以有多个非聚集索引。唯一索引:索引关键字不允许重复。如果在Student表“SName”字段上建立了唯一索引,则SName的值不允许重复聚集索引与非聚集索引都可以是唯一索引。1.索引的定义索引的SQL定义格式:CREATE UNIQUECLUSTERED|NONCLUSTERED/*定义索引类型*/INDEX index_name/*定义索引名称*/ON t
20、able_name(columnASC|DESC,.n)/*定义索引属性列及次序,默认为升序*/1.索引的定义索引创建实例示例1:为“薪资表”中的“薪级名称”建立唯一非聚集升序索引示例2:按应发薪资升序和实发薪资降序建立唯一索引CREATE UNIQUE NONCLUSTEREDINDEX index_name1 ON 薪资表(薪级名称 ASC)CREATEUNIQUEINDEX index_name2ON 薪资表(应发薪资 ASC,实发薪资 DESC)1.索引的定义查看索引 如果我们要查看某张表上的索引,可以使用系统存储过程 Sp_helpindex 查看所建立索引,比如查看薪资表索引语句为
21、 通过上图我们可以发现,主码约束其实是一种索引,在数据库系统中描述为“clustered,unique,primary key”;UNIQUE约束默认为非聚集唯一索引,描述为“nonclustered,unique,unique key”Sp_helpindex 薪资表2.索引的维护索引维护 索引一经建立,就有数据库系统维护,无需用户参与。常见准则l避免在经常更新的表上建立过多索引,如果建立聚集索引,应设置较短索引建长度。l对经常用于查询中的谓词和连接条件的列建立非聚集索引。l在经常用作查询过滤的列上建立索引。l在查询中经常进行GROUP BY、ORDER BY的列上建立索引。l在不同值较少的
22、列上不必要建立索引,如性别字段。l对于经常存取的列避免建立索引。l在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定。l考虑对计算列建立索引。2.索引的维护删除索引 SQL基本格式如下示例3:删除薪资表上所有索引DROP INDEX table_name.index_name或者DROP INDEX index_name ON table_nameDROP INDEX 薪资表.index_name1,薪资表.index_name2或者DROP INDEX index_name1 on 薪资表,index_name2 on 薪资表 Database Princip
23、le and Application数据库原理及应用3.3.1 单表查询重庆理工大学 计算机科学与工程学院目 录CONTENTSSELECT语句格式 列操作12 行操作 排序34 聚合函数 分组统计561.SELECT语句格式SQL 使用 SELECT 语句 一般格式:SELECT ALL|DISTINCT,.FROM ,.WHERE GROUP BY ,COLUMN_name.HAVING ORDER BY ASC|DESC;2.列操作 单表查询是From子句后面的数据表只有一张的查询。选择列即关系代数中的投影运算。SELECT子句可以查询指定列、表达式。SELECT 目标列|表达式 FRO
24、M table1)查询指定列 查询指定列可以是部分列,也可以是全部列,列的显示顺序由 SELECT 子句目标列顺序决定。示例1:查询全体学生姓名、学号、专业SELECT SName,SNO,Major FROM Student 2.列操作1)查询指定列单表查询所有数据示例2:查询全体学生的详细信息 如果列的显示顺序与表中的列顺序一致,可以将目标列用*代替SELECT SNO,SName,BirthYear,Ssex,college,Major,WeiXin FROM StudentSELECT*FROM Student 2.列操作2)查询表达式的值 SELECT 子句中的表达式(Express
25、ion)可以是包含列的计算表达式,也可以是常量或函数。示例3:查询全体学生的学号、姓名、年龄其中 2019-BirthYear 是一个计算表达式,我们也可以使用系统函 数GETDATE(),读取当前时间,在用函数YEAR()读取年份。SELECT SNO,SName,2019-BirthYear FROM StudentSELECT SNO,SName,YEAR(GETDATE()-BirthYear FROM Student 2.列操作2)查询表达式的值 表达式的计算值被记录在结果集中,但没有列名,显示为“无列名”,可以使用 AS 子句为其添加别名记录其语义,AS 也可以省略。示例4:查询全
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库系统课件第3章 SQL 数据库 系统 课件
链接地址:https://www.31doc.com/p-21712635.html