『数据库系统概论』学习笔记之三

『数据库系统概论』学习笔记（三）

第三章　　關係數據庫標准語言SQL

本章重點：

        全面掌握和熟練運用SQL的DQL、DDL、DML、DCL進行相關的數據庫操作。

前言 SQL發展歷程 

        SQL的定義：Structured Query Language(結構化查詢語言)，名為查詢，但其功能不僅僅是查詢。 
        SQL的提出：時間(1974年)、人物(Boyce、Chamberlin) 
        SQL應用的起源：時間(1975-1979)、人物(IBM的San Jose Research Laboratory)、作品(System r) 
        SQL標准的歷程：SQL-86、SQL-89、SQL-92、SQL-99(醞釀中)

3.1        SQL概述

3.1.1        特點 
        是一個綜合的、功能極強同時又簡捷易學的、集數據查詢(Data Query)，數據操縱(Data Manipulation) ，數據定義(Data definition)和數據控制(Data Control)功能於一體的關係數據庫語言。
一、綜合統一 
        集DDL、DML、DCL的功能於一體，語言風格統一，可以獨立完成數據庫生命周期中的全部活動。
二、高度非過程化 
        講究“做什麼”，而無須知道“怎麼做”。
三、面向集合的操作方式 
        除了操作對象、查找結果是元組的集合，一次插入、刪除、更新操作的對象也可以是元組的集合。
四、以同一種語法結構提供兩種使用方式 
        既是自含式語言，又是嵌入式語言。
五、語言簡捷，易學易用 
        完成核心功能只用9個動詞，如下：數據查詢(Select)、數據定義(Create，Drop，Alter)、數據操縱(Insert，Update，Delete)、數據控制(Grant，Revoke)

3.1.2         SQL語言的基本概念 
        基本表：本身獨立存在的表。 
        視圖：從一個或幾個基本表導出的表，本身並不獨立存儲在數據庫中，是一個虛表。

3.2 數據定義       

        包括：定義表(Create Table，Drop Table，Alter Table)、定義視圖(Create View，Drop View)、定義引(Create Index，Drop Index) 。 
        從上面可以看出，標准SQL沒有提供修改視圖和修改索引定義的操作(而很多成熟的關係數據庫產品都對標准SQL進行了擴充，提供了視圖和索引定義的修改語句，如SQL Server所用的Transact-SQL)。

3.2.1 定義、刪除與修改基本表

 

下文都建立在如下關係模式之上： 
職員表：Employees(Eid,Ename,Esex,Eage,Edept)，主碼為Sno； 
子程序表：programs(Pid,Pname,PDescription)，主碼為Pid； 
職員權限表：Authority(Eid,Pid,ADefinition,ADescription)，主碼為(Eid，Pid)。 

說明： 
Eid：惟一標識職員表中每個職員的編號(統一6位字符)，同時一個職員可有多個子程序的不同權限； 
Ename：職員姓名，最長20個字符； 
Esex：職員性別，一個字符，0表示男，1表示女； 
Pid：惟一標識子程序表中的每個子程序的編號(統一8位字符)，同時一個子程序可以由多個職員操作； 
Pname：子程序名稱，最長40個字符； 
Pdescription：子程序描述，最長80個字符，允許為空； 
Eage：職員的年齡(整型，取值范圍為18到90)； 
Edefinition：權限定義，是否有權限(為0或1，1表示有權限)，默認為0； 
刪除職員表中職員時同時刪除職員權限表的相關記錄。 

 

一、建立基本表

Create Table Employees(  --建立職員表 
Eno varChar(6) NOT NULL Primary Key, --主碼 
Ename varchar(20) Unique,  --Unique條件約束 
Esex varChar(1) NOT NULL,  
Eage Int, 
Edept varChar(20) NULL, 
Constraint pk_Eage CHECK (Eage>=18 and Eage<=90)  --建立年齡的條件約束 
) 

Create Table Programs( 
Pid varchar(8) Not Null Primary Key, 
Pname varchar(40) Not Null Unique, --建立unique條件約束 
Pdescription varchar(80) NULL  --允許為空 
) 

Create Table Authority( 
Eid varchar(6) Not NULL, 
Pid varchar(8) Not Null, 
Adefinition bit constraint df_pdefinition default (0), --默認為0 
Adescription varchar(80) Null, 
constraint pk_auth_Eid primary key clustered(Eid,Pid), 
constraint fk_auth_Eid foreign key(Eid) Reference Employees(Eid) On Delete Cascade, --同步刪除 
Constraint fk_auth_Pid Foreign Key(Pid) Reference Employees(Pid), 
) 

二、修改基本表

--向Employees中增加“入職時間”列，其數據類型為日期型:  
        Alter Table Employees Add Ecome DateTime 
--將年齡的數據類型改為半字長整數：  
        Alter Table Employees Modify Eage Smallint 
--刪除職員姓名必須取唯一值的約束： 
        Alter Table Employees Drop Unique(Ename) 

 

三、刪除基本表  
       

        Drop Table Programs        
   /**//*說明：表一旦刪除，表中的數據、此表上建立的索引和視圖都將被刪除（但有的系統會保留視圖，如Oracle）。
      注意：如果此時Programs與Authority中存在關聯記錄的話，刪除programs會由於兩者間的條件約束而引起錯誤，不能成功；而*/ 
        Drop Table Employees  
   /**//*則不會引起這樣的問題，因為兩者間存在同步刪除的條件約束，所以會成功刪除Employees中的記錄並且會同步刪除Authority中的相關記錄。 */

 

3.2.2 建立與刪除索引  

建立索引有助於查詢速度的提高。索引的種類：  
        普通索引：Index；  
        聚集索引：Clustured Index，索引項的順序與表中記錄的物理順序一致的索引組織；  
        唯一索引：Unique Index，索引的每一個索引值只對應唯一的數據記錄 

一、建立索引 
        索引可建立在一個表的一列或多列上，各列名之間用逗號分隔。列名後可用ASC或DESC指定排列次序。

--在Employees表的Ename列上建立一個聚集索引（SQL Server中默認在Primary Key上建立聚集索引）：  
          Create Clustered Index EmpName ON Employees(Ename);  

--在Authority表中按職員號升序和子程序號子程序建立唯一索引：  
          Create Unique Index Auth_no ON Authority(Eid ASC,Pid DESC); 

 
二、刪除索引 
        Drop Index <索引名>  

--刪除Employees表的EmpName索引：  
            Drop Index EmpName; 

鑒於上面建立的三個表中我們還沒有輸入數據，這裡就先看看數據更新部分，看如果向表中插入、修改和刪除數據。

3.4 數據更新

3.4.1 插入數據（Insert Into）

一、插入單個元組
格式：Insert Into <表名> [(<列1>[,<列2>...)] values (<常量1>[,<常量2>]...);
說明：Into子句可以不指明任何列名，但values子句中必須為所有列指定值；   

--插入一個新職員記錄（職員號：SH0001；姓名：劉備；性別：男；年齡：36；所在系：蜀）到Employees中： 
        Insert Into Employees Values('SH0001','劉備','男',36,'蜀');   --Into子句後不帶列名  

--插入一條子程序記錄（'fmSys001','系統權限')到Programs表中：   
        Insert Into Programs(Pid,Pname) Values('fmSys001','系統權限');   --Into子句後帶列名，Pdescription列上取空值

二、插入子查詢結果
格式：Insert into <表名>[(<列1>(,<列2>...)] 子查詢；
說明：如果不帶列名，則表示為所有的列插入值；子查詢返回的列個數和類型必須與表名後的列名個數和類型一致。

--復制Programs表中的記錄到另一個表（假設表名為programs2，少了一個Pdescription列）：  
        Insert Into Programs2(Pid,Pname) Select Pid,Pname from programs; 

 

3.4.2 修改數據
格式：Update <表名> Set <列名>=<表達式>[,<列名>=表達式>]... [where <條件>];
說明：如果不帶where子句，將對表中所有元組根據SET子句中給出的表達式的值取代相應的列值。

--將職員SH0001的年齡改為38歲：  
        Update Employees Set Eage=38 where Eid='SH0001'     --只修改一個元組的值 

--將所有職員的年齡加1歲：  
        Update Employees Set Eage=Eage+1     --修改多個元組的值 

--將部門為“蜀”的所有職員的權限都改為1：  
        Update Authority Set Adefinition=1 where Eid=(select Eid from Employees where Edept='蜀' and Employees.Eid=Authority.Eid) --帶子查詢的修改語句

3.4.3 刪除數據

格式：Delete From <表名> [where <條件>];
說明：省略where子句時，將刪除表中的全部元組，但不會刪除表的定義。

/**//*刪除職員號為SH0001的職員記錄（依據上面表中的定義，刪除某一職員的記錄將會同步刪除Authority表的權限記錄：*/  
        Delete From Employees where Eid='SH0001'   --刪除某一個元組的值 

--刪除所有的職員權限記錄：  
        Delete From Authority    --刪除多個元組的值 

--刪除部門為“蜀”的所有職員的權限記錄：  
        Delete From Authority where Eid=(select Eid from Employees where Edept='蜀' and Employees.Eid=Authority.Eid)    --帶子查詢的刪除語句

注意：在進行數據的更新操作時，需要注意數據的參照完整性問題，比如如果不對職員表與職員權限表定

義同步刪除的參照完整性，在刪除職員記錄時可能會因為某些原因而未能刪除權限表中的記錄，從而引起

數據的不一致。

點擊這裡閱讀下一部分

 
版權聲明：此為原創，轉載請注明出處：www.cnblogs.com Bonny.wong(讓思想飛翔) 2005.2.24