数据库基本原理和应用.pptx

数据库原理及应用数据库原理及应用主要内容数据库基本概念数据库基本概念数据库数据库管理系统数据查询数据操作表和视图基本概念数据数据库和数据和数据库管理系管理系统数据库是在计算机上组织、存储和共享数据的方法，数据库系统是由普通的文件系统发展而来的。数据库系统具有较高的数据独立性，即不依赖于特定的数据库应用程序；数据库系统的数据冗余小，可以节省数据的存储空间；另外数据库系统还很容易实现多个用户的数据共享。数据库系统成熟的标志就是数据库管理系统的出现。数据库管理系统(DataBase Managerment System，简称DBMS)是对数据库的一种完整和统一的管理和控制机制。数据库管理系统不仅让我们能够实现对数据的快快速速检检索索和和维维护护，还还为为数数据据的的安安全全性性、完完整整性性、并并发发控控制制和和数数据据恢恢复复提提供供了了保保证证。数据库管理系统的核心是一个用来存储大量数据的数据库。基本概念一个真正的数据库系统由硬件和软件两个方面构成。比如我们要使用Oracle数据库，需要安装Oracle公司提供的数据库服务器软件和一台用于安装数据库管理系统的高性能的计算机服务器。数据库系统的发展经历了层次模型、网状模型及关系模型几个阶段。当今应用最普遍的是关系型数据库管理系统关系型数据库管理系统。目前，市场上流行的几种大型数据库，如Oracle、DB2、Sybase、MS SQL Server等都是关系型数据库管理系统。Oracle数据库是一种面向对象的关系型数据库管理系统(ORDBMS)，是基于标准SQL语言的数据库产品。目录数据库基本概念数据查询数据查询基本语法基本语法排序查询条件查询高级查询数据操作表和视图SQL内置函数数据库查询语言SQL主要特点：SQL语言可以在Oracle数据库中创建、存储、更新、检索和维护数据，其中主要的功能是实现数据的查询和数据的插入、删除、修改等操作。SQL语言在书写上类似于英文，简洁清晰，易于理解。它由关键字、表名、字段名，表达式等部分构成。分类：SQL语言按功能可分为DDL语言、DML语言、DCL语言和数据库事务处理语言四个类别。SQL语言的主要关键字有：ALTER、DROP、REVOKE、AUDIT、GRANT、ROLLBACK、COMMIT、INSERT、SELECT、COMMENT、LOCK、UPDATE、CREATE、NOAUDIT、VALIDATE、DELETE、RENAME等。SQL语言的分类按照SQL语言的不同功用，可以进一步对SQL语言进行划分。下表给出了SQL语言的分类和功能简介。由主句主句和若干个从句从句组成，主句和从句都由关键字引导。主句表示该语句的主要功能从句表示一些条件或限定，有些从句是可以省略的。在语句中会引用到列名、表名或表达式。另外还有如下一些说明：关键字、字段名、表名等之间都要用空格或逗号等进行必要的分隔。语句的大小写不敏感(查询的内容除外)。语句可以写在一行或多行。语句中的关键字不能略写和分开写在两行。要在每条SQL语句的结束处添加“;”号。为了提高可读性，可以使用缩进。从句一般写在另一行的开始处。SQL基本语法查询语句是最常见的SQL语句，它从给定的表中，把满足条件的内容检索出来。以下是最基本的SELECT语句语法。SELECT(字段名列表字段名列表|*)FROM 表名表名 WHERE 条件条件;SELECT为查询语句的关键字，该关键字不能省略。字段名列表代表要查询的字段。FROM 也是查询语句关键字，后面跟要查询的表名，该关键字不能省略。WHERE条件限定检索特定的记录，满足“条件”的记录被显示出来，不满足条件的被过滤掉。语句查询的结果往往是表的一部分行和列。如果字段名列表使用*，将检索全部的字段。如果省略WHERE条件，将检索全部的记录。SELECT*FROM emp WHERE deptno=10;SQL查询语句基本查询语句1基本查询 select*from dept;2显示行号 ROWNUM oracle特有的每个表都有一个虚列ROWNUM，它用来显示结果中记录的行号。我们在查询中也可以显示这个列。SELECT rownum,ename FROM emp;select*from(select rownum no,id,name from student)where no2;select*from(select rownum no,id,name from student where rownum=2;where rownumXXX不能输出结果，因为where 每次去select 时遇到不符合的就直接删除，所以num一直不能满足条件。但是可以先将rownum搜出来后形成一个虚表，再调用。3显示计算列在查询语句中可以有算术表达式，它将形成一个新列，用于显示计算的结果，通常称为计算列。表达式中可以包含列名、算术运算符和括号。括号用来改变运算的优先次序。常用的算术运算符包括：+：加法运算符。?：减法运算符。*：乘法运算符。/：除法运算符。基本查询语句4 连接运算符在前面，我们使用到了包含数值运算的计算列，显示结果也是数值型的。我们也可以使用字符型的计算列，方法是在查询中使用连接运算。连接运算符是双竖线“|”。通过连接运算可以将两个字符串连接在一起。5 使用别名 as我们可以为表的列起一个别名，它的好处是，可以改变表头的显示。特别是对于计算列，可以为它起一个简单的列别名以代替计算表达式在表头的显示。说明：表头显示的是列别名，转换为汉字显示。在列名和别名之间要用AS分隔，如ename和它的别名“名称”之间用AS隔开。AS也可以省略，如sal和它的别名“工资”之间用空格分割。注意：如果用空格分割，要区别好列名和别名，前面为列名，后面是别名。别名如果含有空格或特殊字符或大小写敏感，需要使用双引号将它引起来。基本查询语句6消除重复行如果在显示结果中存在重复行，可以使用的关键字DISTINCT消除重复显示。SELECT DISTINCT job FROM emp;7.查询结果的排序查询结果的排序SELECT 字段列表 FROM 表名 WHERE 条件ORDER BY 字字段段名名1 ASC|DESC,字段名字段名 ASC|DESC.;ASC 升序（默认）升序（默认）|DESC 降序降序SELECT ename,sal FROM emp ORDER BY sal;可以按多列进行排序，先按第一列，然后按第二列如果要对计算列排序，可以为计算列指定别名，然后按别名排序。Eg：select*from emp order by mgr asc,hiredate desc;条件查询 简单条件查询要对显示的行进行限定，可在FROM从句后使用WHERE从句，在WHERE从句中给出限定的条件，因为限定条件是一个表达式，所以称为条件表达式。条件表达式中可以包含比较运算，表达式的值为真的记录将被显示。例子：条件查询复合条件查询可以用逻辑运算符构成复合的条件查询，即把两个或多个条件，用逻辑运算符连接成一个条件。有3个逻辑运算符，如下表所示。运算的优先顺序是NOT，AND，OR。如果要改变优先顺序，可以使用括号。特殊条件查询Between.andInIs Not NullLIKE使用LIKE操作符可完成按通配符查找字符串的查询操作，该操作符适合于对数据进行模糊查询。其语句法为：NOT LIKE 匹配模式匹配模式中除了可以包含固定的字符之外，还可以包含以下的通配符：%：代表0个或多个任意字符。_：代表一个任意字符。函数-数值型函数函数字符型函数函数日期型函数函数类型转换函数1自动类型转换Oracle可以自动根据具体情况进行如下的转换：*字符串到数值。*字符串到日期。*数值到字符串。*日期到字符串。2日期类型转换将日期型转换成字符串时，可以按新的格式显示。如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。日期类型转换日期类型转换2数值转换符其他常用函数目录数据库基本概念数据查询数据查询基本语法排序查询条件查询高级查询高级查询数据操作表和视图高级查询多表联合查询通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句中用比较运算符指明连接的条件。忘记说明表的连接条件是常见的一种错误，这时查询将会产生表连接的笛卡尔积(即一个表中的每条记录与另一个表中的每条记录作连接产生的结果)。一般N个表进行连接，需要至少N-1个连接条件，才能够正确连接。两个表连接是最常见的情况，只需要说明一个连接条件。相等连接。不等连接。外连接。自连接。统计查询 通常需要对数据进行统计，汇总出数据库的统计信息。这个功能可以由统计查询完成。Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。统计查询分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。使用GROUP BY 从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。如果不使用分组，将对整个表或满足条件的记录应用组函数。在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT 表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。对对分分组组查查询询的的结结果果进进行行过过滤滤，要要使使用用HAVING从从句句。HAVING从从句句过过滤滤分分组组后后的的结结果果，它它只只能能出出现现在在GROUP BY从从句句之之后后，而而WHERE从从句句要要出现在出现在GROUP BY从句之前。从句之前。子查询我们可能会提出这样的问题，在雇员中谁的工资比SCOTT高？第一步查询雇员SCOTT的工资第二步查询工资高于SCOTT的雇员。通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。集合运算多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该一样。目录数据库基本概念数据查询数据操作数据操作表和视图数据库操作语句DML数据库操作语句插入数据可以使用INSERT命令，向已经存在的表插入数据，语法格式如下：INSERT INTO 表 名 (字 段 列 表)VALUES(表 达 式 1,表 达 式2,.)|QUERY语句；修改数据修改数据的语句UPDATE对表中指定字段的数据进行修改，一般需要通过添加WHERE条件来限定要进行修改的行，如果不添加WHERE条件，将对所有的行进行修改。(1)修改数据的语句UPDATE的基本语法如下：UPDATE 表名 SET 字段名1=表达式1,字段名2=表达式2,.WHERE 条件;(2)UPDATE语句的另外一种用法：UPDATE 表名 SET(字段名1,字段名2,.)=SELECT(字段名1,字段名2,.)FROM 另外的表名WHERE条件;删除数据DELETE FROM表名 WHERE 条件;目录数据库基本概念数据查询数据操作表和视图表和视图表的创建和操作表的创建和操作数据完整和约束条件数据完整和约束条件SQL内置函数Oracle数据库对象表的创建CREATE TABLE 表名(列名 数据类型(宽度)DEFAULT 表达式COLUMN CONSTRAINT,.TABLE CONSTRAINTTABLE_PARTITION_CLAUSE );由此可见，创建表最主要的是要说明表名、列名、列的数据类型和宽度，多列之间用“,”分隔。可以是用中文或英文作为表名和列名。表名最大长度为30个字符。在同一个用户下，表不能重名，但不同用户表的名称可以相重。另外，表的名称不能使用Oracle的保留字。在一张表中最多可以包含2000列。该语法中的其他部分根据需要添加，作用如下：DEFAULT 表达式：用来定义列的默认值。COLUMN CONSTRAINT：用来定义列级的约束条件。TABLE CONSTRAINT：用来定义表级的约束条件。TABLE_PARTITION_CLAUSE：定义表的分区子句。create table TEST(ID NUMBER(6,2)not null ,NAME VARCHAR2(20)default mao not null,AGE NUMBER,DEPTNO NUMBER not null,COMM VARCHAR2(50)not null,primary key(id),foreign key(DEPTNO)references DEPT(DEPTNO),check(age0 and age150);表的创建和删除通过子查询创建表如果要创建一个同已有的表结构相同或部分相同的表，可以采用以下的语法：CREATE TABLE 表名(列名.)AS SQL查询语句;该语法既可以复制表的结构，也可以复制表的内容，并可以为新表命名新的列名。新的列名在表名后的括号中给出，如果省略将采用原来表的列名。复制的内容由查询语句的WHERE条件决定。删除已创建的表DROP TABLE 表名CASCADE CONSTRAINTS;表的删除者必须是表的创建者或具有DROP ANY TABLE权限。CASCADE CONSTRAINTS表示当要删除的表被其他表参照时，删除参照此表的约束条件。有关内容请参考下一节。表的操作表的重命名RENAME 旧表名 TO 新表名;清空表TRUNCATE TABLE 表名；DDL清空表可删除表的全部数据并释放占用的存储空间。有关训练请参照DELETE(DML)语句部分，注意两者的区别。添加注释(1)为表添加注释：COMMENT ON TABLE 表名 IS.;(2)为列添加注释：COMMENT ON COLUMN 表名.列名 IS.查看表可以通过对数据字典USER_OBJECTS的查询，显示当前模式用户的所 有 表。SELECT object_name FROM user_objects WHERE object_type=TABLE;目录数据库基本概念数据查询数据操作表和视图表和视图表的创建和操作数据完整和约束条件数据完整和约束条件视图的创建和操作数据完整性和约束条件表的数据有一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。完整性包括数据完整性和参照完整性数据完整性定义表数据的约束条件:主键(PRIMARY KEY)非空(NOT NULL)惟一(UNIQUE)检查(CHECK)约束条件定义参照完整性定义数据之间的约束条件。参照完整性由外键(FOREIGN KEY)约束条件定义。外键约束第一种语法，如果子记录存在，则不允许删除主记录：CONSTRANT 约 束 名 FOREIGN KEY(列 名 1,列 名2,.)REFERENCES 表名(列名1,列名2,.)第二种语法，如果子记录存在，则删除主记录时，级联删除子记录：CONSTRANT 约 束 名 FOREIGN KEY(列 名 1,列 名2,.)REFERENCES 表名(列名1,列名2,.)on delete cascade 第三种语法，如果子记录存在，则删除主记录时，将子记录置成空：CONSTRANT 约 束 名 FOREIGN KEY(列 名 1,列 名2,.)REFERENCES 表名(列名1,列名2,.)on delete set null其中的表名为要参照的表名。目录数据库基本概念数据查询数据操作表和视图表和视图表的创建和操作数据完整和约束条件视图的创建和操作视图的创建和操作视图的概念视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表，视图本身不包含任何数据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个查询语句。视图的数据都来自于某些表，这些表被称为基表。通过视图来查看表，就像是从不同的角度来观察一个(或多个)表。可以提高数据访问的安全性，通过视图往往只可以访问数据库中表的特定部分，限制了用户访问表的全部行和列。简化了对数据的查询，隐藏了查询的复杂性。视图的数据来自一个复杂的查询，用户对视图的检索却很简单。一个视图可以检索多张表的数据，因此用户通过访问一个视图，可完成对多个表的访问。视图是相同数据的不同表示，通过为不同的用户创建同一个表的不同视图，使用户可分别访问同一个表的不同部分。视图可以在表能够使用的任何地方使用，但在对视图的操作上同表相比有些限制，特别是插入和修改操作。对视图的操作将传递到基表，所以在表上定义的约束条件和触发器在视图上将同样起作用。视图的创建CREATE OR REPLACE VIEW 视图名(别名1，别名2.)AS 子查询 WITH CHECK OPTION CONSTRAINT 约束名 WITH READ ONLYOR REPLACE 表示替代已经存在的视图。别名是为子查询中选中的列新定义的名字，替代查询表中原有的列名。子查询是一个用于定义视图的SELECT查询语句，可以包含连接、分组及子查询。WITH CHECK OPTION表示进行视图插入或修改时必须满足子查询的约束条件。后面的约束名是该约束条件的名字。WITH READ ONLY 表示视图是只读的。删除视图的语法如下：DROP VIEW 视图名;SQL练习假设有两张表，一张项目表item（itemID,itemName,inv），一张单项工程表prj(prjID,itemID,prjType，inv)。1.查询“项目一”下所有的单项工程信息2.查询“项目一”下所有“变电”类型的单项信息3.查询所有“变电”类型单项工程有两个以上的项目的名称以及“变电”单项个数。(分组查询)4.对所有“线路”类型单项的总投资（inv）从高到低排序5.查询“线路”类型单项的最大总投资，最小总投资，以及平均总投资。The End谢谢