关系数据库标准语言SQL语言基础.ppt

SQL语言基础 本章学习目标SQL语言是数据库的标准语言 只有理解了SQL才能真正理解关系数据库 本章将介绍怎样使用 结构化查询语言 SQL 来操作数据库 第三章SQL语言基础 本章内容安排 3 1SQL简介 3 2SQL基本语法 3 3数据查询语言 DQL 3 4数据操纵语言 DML 3 5数据定义语言 DDL 3 6数据控制语言 DCL 3 7常用函数 3 1 2SQL的优点 3 1 3操作界面 3 1 1发展历史 3 1SQL简介 SQL语言是数据库的核心语言 全称是 结构化查询语言 StructuredQueryLanguage 最早的是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEMR开发的一种查询语言 它的前身是SQUARE语言 1 非过程化语言SQL是一个非过程化的语言 因为它一次处理一个记录 对数据提供自动导航 SQL允许用户在高层的数据结构上工作 而不对单个记录进行操作 可操作记录集 所有SQL语句接受集合作为输入 返回集合作为输出 SQL的集合特性允许一条SQL语句的结果作为另一条SQL语句的输入 SQL不要求用户指定对数据的存放方法 这种特性使用户更易集中精力于要得到的结果 3 1 2SQL的优点 3 1 2SQL的优点 2 统一的语言SQL可用于所有用户的DB活动模型 包括系统管理员 数据库管理员 应用程序员 决策支持系统人员及许多其它类型的终端用户 SQL为许多任务提供了语句 包括 查询数据在表中插入 修改和删除记录建立 修改和删除数据对象控制对数据和数据对象的存取保证数据库一致性和完整性 3 1 2SQL的优点 3 是所有关系数据库的公共语言由于所有主要的关系数据库管理系统都支持SQL语言 用户可将使用SQL的技能从一个RDBMS转到另一个 所有用SQL编写的程序都是可以移植的 1 SQL PLUS界面 1 登录输入SQLPLUS 再回车 接着输入正确的Oracle用户名并回车 输入用户口令并回车 会显示提示符 SQL 2 退出输入EXIT即可 2 语句的编辑与运行语句的编辑与运行可以在语句提示符后输入SQL语句并运行 执行单条语句 以分号结束输入 执行程序块以斜杠结束输入 或者以空行结束输入 也可以利用SQL缓冲区进行PL SQL块的编辑和运行 或利用语句文件进行PL SQL块的编辑和运行 3 1 3操作界面 3 2 2SQL语言的语法结构 3 2 1SQL语言的主要功能 3 2SQL基本语法 通过SQL语句 程序员或数据库管理员 DBA 可以做如下的主要工作 1 建立数据库的表格 包括设置表格所可以使用之空间 2 改变数据库系统环境设置 3 针对某个数据库或表格 授予用户存取权限 4 对数据库表格建立索引值 5 修改数据库表格结构 新建 删除或是修改表格字段 6 对数据库进行数据的新建 7 对数据库进行数据的删除 8 对数据库进行数据的修改 9 对数据库进行数据的查询 3 2 2SQL语言的语法结构 SQL语句是针对关系型数据库所建立出来的语法叙述 所以SQL在这类数据库中所发挥的功能非常的强 SQL的语句不多 而且其语法也相对较简单 归纳起来 共有六大类 1 第一类 属性词 Predicates 2 第二类 声明 Declaration 3 第三类 条件子句 Clause 4 第四类 运算符 Operator 与操作数 Operation 5 第五类 函数 Function 6 第六类 SQL语句 Statement 3 3 2复合查询 3 3 1基本查询 3 3数据查询语言 DQL 3 3 3集合函数 1 DQL的基本结构2 SELECT语句的格式SELECT predicate table table field table field2 ASalias1 alias2 FROMtableexpression WHERE GROUPBY HAVING ORDERBY WITHOWNERACCESSOPTION 3 3 1基本查询 3 SELECT中的条件语句 1 FROM条件子句SELECTfieldlistFROMtableexpression 2 WHERE条件子句SELECTfieldlistFROMtableexpressionWHEREcriteria 3 DISTINCT属性词用法SELECT DISTINCT FROMtable 4 ORDERBY条件子句SELECTfieldlistFROMtableWHEREselectcriteriaORDERBYfield ASC DESC field2 ASC DESC 5 GROUPBY条件子句SELECTfieldlistFROMtableWHEREcriteriaGROUPBYgroupfieldlist 6 HAVING条件子句SELECTfieldlistFROMtableWHEREselectcriteriaGROUPBYgroupfieldlistHAVINGgroupcriteria 7 BETWEEN AND运算符expr Not BETWEENvalue1ANDvalue2 8 LIKE操作数expressionLIKE pattern expression 4 注意事项 1 字段名之间可以进行算术运算 例如 字段名1 字段名2 3 2 查询语句可以嵌套 例如 SELECT FROM表名1WHERE字段1in SELECT FROM表名1 表名2 WHERE条件1WHERE条件2 3 查询结果集的排序操作 默认的排序是升序ASC 降序是DESC 4 每个表都有一个隐含的字段ROWID 它标记着记录的唯一性 1 连接查询连接可以在SELECT语句的FROM子句或WHERE子句中建立 在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来 所以 在Transact SQL中推荐使用这种方法 1 内连接内连接查询操作列出与连接条件匹配的数据行 它使用比较运算符比较被连接列的列值 内连接分三种 等值连接 在连接条件中使用等于号 运算符比较被连接列的列值 其查询结果中列出被连接表中的所有列 包括其中的重复列 不等连接 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值 这些运算符包括 自然连接 在连接条件中使用等于 运算符比较被连接列的列值 但它使用选择列表指出查询结果集合中所包括的列 并删除连接表中的重复列 3 3 2复合查询 2 外连接外连接分为左外连接 右外连接和全外连接三种 与内连接不同的是 外连接不只列出与连接条件相匹配的行 而是列出左表 左外连接时 右表 右外连接时 或两个表 全外连接时 中所有符合搜索条件的数据行 在ORACLE中 外连接可以通过在WHERE子句中 的使用来使用 例如 表A与表B的左连接A FIELD1 B FIELD1 右连接A FIELD1 B FIELD1 3 交叉连接交叉连接不带WHERE子句 它返回被连接的两个表所有数据行的笛卡尔积 返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数 2 联合查询UNION运算符可以将两个或两个以上SELECT语句的查询结果集合合并成一个结果集合显示 即执行联合查询 UNION的语法格式为 select statementUNION ALL selectstatement UNION ALL selectstatement n 1 统计字段值的数目函数COUNT 用来统计一个表中有多少条记录 注意 函数COUNT 没有指定任何字段 这个语句计算表中所有记录所数目 包括有空值的记录 因此 不需要指定要被计算的特定字段 2 计算字段的平均值函数AVG 可以返回一个字段中所有值的平均值 注意 函数AVG 只能对数值型字段使用 这个函数在计算平均值时也忽略空值 3 3 3集合函数 3 计算字段值的和计算字段值的和使用函数SUM 注意 函数SUM 的返回值代表字段purchase amount中所有值的总和 4 返回最大值或最小值函数MAX 和函数MIN 分别用于返回最大值和最小值 注意 函数MIN 返回一个字段的所有值中的最小值 如果字段是空的 函数MIN 返回空值 3 4 2DELETE语句 3 4 1INSERT语句 3 4数据操纵语言 DML 3 4 3UPDATE语句 1 INSERT语句格式INSERT语句用于往数据表里插入记录 1 同时插入多条记录的语句格式为 INSERTINTO target field1 target field2 SELECT source field1 source field2 FROMtableexpression 2 插入单条记录的语句格式为 INSERTINTO target field1 target field2 VALUES value1 value2 3 4 1INSERT语句 2 注意事项 1 字符串类型的字段值必须用单引号括起来 例如 GOODDAY 2 如果字段值里包含单引号需要进行字符串转换 把它替换成两个单引号 3 字符串类型的字段值超过定义的长度会出错 最好在插入前进行长度校验 4 日期字段的字段值可以用当前数据库的系统时间SYSDATE 精确到秒 5 INSERT时如果要用到从1开始自动增长的序列号 应该先建立一个序列号 1 DELETE语句格式DELETE语句删除数据表里的记录 用DELETE语句删除的记录 无法再复原 所以条件设置一定要正确 DELETE语句格式为 DELETE table FROMtableexpressionWHEREcriteria 3 4 2DELETE语句 2 注意事项 1 删除记录并不能释放Oracle里被占用的数据块表空间 它只把那些被删除的数据块标成unused 2 如果确实要删除一个大表里的全部记录 可以用TRUNCATE语句 它可以释放占用的数据块表空间 其语句格式为 TRUNCATETABLE表名 3 此操作不可回退 UPDATE语句通过条件的限制来修改特定的数据 UPDATE语句格式为 UPDATEtableSETnewvalueWHEREcriteria DML语句对表都加上了行级锁 确认完成后 必须加上事物处理结束的语句COMMIT才能正式生效 否则改变不一定写入数据库里 如果想撤回这些操作 可以用语句ROLLBACK复原 3 4 3UPDATE语句 3 5 2ALTER语句 3 5 1CREATE语句 3 5数据定义语言 DDL 3 5 3DROP语句 3 5 4TRUNCATE语句 1 表的建立表是存储用户数据的基本结构 建立表主要指定义下列信息 列定义完整性约束表所在表空间存储特性可选择的聚集 3 5 1CREATE语句 1 建立一个新表的语句格式可以利用CREATETABLE语句 来建立一个全新的表 但前提是 数据库必须已经存在 语句格式为 CREATETABLEtable field1type size index1 field2type size index2 nultifieldindex 2 注意事项 创建表时要把较小的不为空的字段放在前面 可能为空的字段放在后面 创建表时可以用中文的字段名 但最好还是用英文的字段名 创建表时可以给字段加上默认值 例如DEFAULTSYSDATE 这样每次插入和修改时 不用程序操作这个字段都能得到动作的时间 创建表时可以给字段加上约束条件 例如不允许重复UNIQUE 关键字PRIMARYKEY 2 表索引的建立索引是一种数据库对象 对于在表或聚集的索引列上的每一值将包含一项 为行提供直接的快速存取 在下列情况下 Oracle可利用索引改进性能 按指定的索引列的值查找行 按索引列的顺序存取表 表索引的建立主要采用的是CREATEINDEX语句 这个命令是对一个已存在的表建立索引 语句格式为 CREATE UNIQUE INDEXindexONtable field1 ASC DESC field2 ASC DESC WITH PRIMARY DISALLOWNULL IGNORENULL CONSTRAINT条件子句的功能也是类似索引 INDEX 的 虽然CONSTRAINT也可以建立表之间的关联性 如 1 单一字段索引语句格式 CONSTRAINTname PRIMARYKEY UNIQUE REFERENCESforeigntable foreignfield1 foreignfield2 2 多字段索引语句格式 CONSTRAINTname PRIMARYKEY primary1 primary2 UNIQUE unique1 unique2 FOREIGNKEY ref1 ref2 REFERENCESforeigntable foreignfield1 foreignfield2 3 视图的建立视图是一个逻辑表 它允许操作者从其它表或视图存取数据 视图本身不包含数据 视图所基于的表称为基表 引入视图有下列作用 提供附加的表安全级 限制存取基表的行或 和列集合 隐藏数据复杂性 为数据提供另一种观点 促使Oracle的某些操作在包含视图的数据库上执行 而不在另一个数据库上执行 建立视图的语句格式为 CREATEVIEWviewASSELECTtable1 field1 table2 field1 FROMtable1 table2 4 同义词的建立同义词为表 视图 序列 存储函数 包 快照或其它同义词的另一个名字 使用同义词为了安全和方便 对某一对象建立同义词可有下列好处 引用对象不需指出对象的持有者 引用对象不需指出它所位于的数据库 为对象提供另一个名字 建立同义词使用的语句格式为 CREATESYNONYMsymnon nameFOR username tablename 5 用户的建立建立用户使用的语句格式为 CREATEUSERusernameIDENTIFIEDBYpassword 1 ALTER语句格式用ALTER语句 可以修改表 索引 或对视图的字段重新设计 语句格式为 ALTERTABLEtable ADD COLUMNfieldtype size CONSTRAINTindex CONSTRAINTmultifiedindex DROP COLUMNfield CONSTRAINTindexname 3 5 2ALTER语句 2 ALTER语句的具体用法 1 改变表的名称 语句格式为 ALTERTABLEtable1TOtable2 2 在表的后面增加一个字段 语句格式为 ALTERTABLEtableADDFIELDDESCRIPTION 3 修改表里字段的定义描述 语句格式为 ALTERTABLEtableMODIFYFIELDDESCRIPTION 4 给表里的字段加上约束条件 语句格式为 ALTERTABLEtableADDCONSTRAINTcons namePRIMARYKEY field ALTERTABLEtableADDCONSTRAINTcons nameUNIQUE field 5 把表放在或取出数据库的内存区 语句格式为 ALTERTABLEtableCACHE ALTERTABLEtableNOCACHE 使用DROP语句 可以删除表 索引 视图 同义词 过程 函数 数据库链接等 DROP语句的格式为 DROP TABLEtable INDEXindexONtable 3 5 3DROP语句 使用TRUNCATE语句 可以清空表里的所有记录 保留表的结构 TRUNCATE语句的格式为 TRUNCATEtable 3 5 4TRUNCATE语句 2 REVOKE语句 1 GRANT语句 3 6数据控制语言 DCL 3 COMMIT语句 4 ROLLBACK语句 1 GRANT语句GRANT语句的作用是赋与用户权限 常用的数据对象权限有以下六个 1 ALLON数据对象名 2 SELECTON数据对象名 3 UPDATEON数据对象名 4 DELETEON数据对象名 5 INSERTON数据对象名 6 ALTERON数据对象名 2 REVOKE语句REVOKE语句是回收权限语句 回收以上所赋权限的相应语句为 REVOKECONNECTFROMUSER1 REVOKESELECTONTABLE2FROMUSER2 REVOKESELECT INSERT DELETEONTABLE3FROMUSER1 USER2 3 COMMIT语句COMMIT WORK 提交事务 提交数据有三种类型 显式提交 隐式提交及自动提交 下面分别说明这三种类型 1 显式提交用COMMIT语句直接完成的提交为显式提交 其格式为 SQL COMMIT 2 隐式提交用SQL语句间接完成的提交为隐式提交 这些命令是 ALTER CONNECT CREATE DISCONNECT DROP EXIT GRANT QUIT REVOKE 3 自动提交若把AUTOCOMMIT设置为ON 则在插入 修改 删除语句执行后 系统将自动进行提交 这就是自动提交 其格式为 SQL SETAUTOCOMMITON 4 ROLLBACK语句ROLLBACK WORK TO SAVEPOINT 回退到某一点 回滚语句使数据库状态回到上次最后提交事务的状态 3 7常用函数 一个函数类似于一个算符 它操作数据项 返回一个结果 函数在格式上不同于算符 它具有参数 可操作0个 一个 二个或多个参数 函数的形式为 函数名 参数1 参数2 函数具有下列一般类形 单行函数分组函数