數據庫 第3章 結構化查詢語言

1、1 電子科技大學數據庫原理及應用 了解結構化查詢語言SQL操作原理 學習數據庫SQL操作語句使用 掌握數據庫對象創(chuàng)建 掌握數據庫表訪問操縱 掌握多表關聯數據查詢 掌握數據庫對象修改與刪除 掌握數據庫視圖使用 掌握SQL Server數據庫管理工具使用2 電子科技大學數據庫原理及應用一、基本概念一、基本概念3.1 SQL簡介SQL（ Structured Query Language,結構化查詢語言 ）是一種對數據庫進行訪問的數據操作子語言。其SQL語句執(zhí)行命令完成對數據庫訪問操作。二、二、SQL發(fā)展發(fā)展w 20世紀70年代由IBM公司開發(fā)w 1979年ORACLE公司首先提供商用的SQLw 1

2、986 ANSI的數據庫委員會批準了SQL作為關系型數據庫語言的美國標準。w 1987年國際標準化組織(ISO)將其采納為國際標準SQL86。w 1989年更新成為國際標準SQL-89w 1992年更新成為國際標準SQL-92（或稱SQL2）w 1999年更新成為國際標準SQL-99（或稱SQL3）w 2008年更新成為國際標準SQL：20083 電子科技大學數據庫原理及應用三、三、SQL應用情況應用情況絕大多數主流的關系型數據庫管理系統(tǒng)采用了SQL標準語言實現數據庫操作wOraclewSybasewDB2wMicrosoft SQL ServerwAccesswMySqlw。 其中一些廠商數

3、據庫管理系統(tǒng)對SQL語句進行了擴展開發(fā)，如Sybase、 Microsoft SQL Server將操作語言擴展為Transaction-SQL;Oracle將操作語言擴展為PL/SQL。4 電子科技大學數據庫原理及應用四、四、SQL操作機理操作機理SQL主要操作功能：w SQL 從數據庫存取數據w SQL 從數據庫刪除數據w SQL 創(chuàng)建新數據庫w SQL 在數據庫中創(chuàng)建新表w SQL 在數據庫中創(chuàng)建存儲過程w SQL 在數據庫中創(chuàng)建視圖w SQL 可以設置數據庫對象的訪問權限 數據庫DBMS(執(zhí)行SQL)數據庫應用程序返回結果提交SQLI/O數據5 電子科技大學數據庫原理及應用五、五、SQ

4、L語言語句類別語言語句類別1. 數據操縱語言數據操縱語言（Data Manipulation Language，DML）是SQL語言中用于增添、修改、刪除數據的語句，主要語句如下：w INSERT - 向數據庫表中插入數據 w UPDATE - 更新數據庫表中的數據w DELETE - 從數據庫表中刪除數據 6 電子科技大學數據庫原理及應用2. 數據定義語言數據定義語言（Data Definition Language，DDL）是SQL語言中用于創(chuàng)建或刪除數據庫對象的語句。這類語句也可以定義數據表對象的主外鍵、索引、約束等要素。主要語句如下：w CREATE DATABASE - 創(chuàng)建新數據庫

5、w DROP DATABASE 刪除數據庫w ALTER DATABASE - 修改數據庫屬性 w CREATE TABLE - 創(chuàng)建新表 w ALTER TABLE 修改數據庫表結構w DROP TABLE - 刪除表w CREATE INDEX - 創(chuàng)建索引w DROP INDEX - 刪除索引 7 電子科技大學數據庫原理及應用3. 數據查詢語言數據查詢語言（Data Query Language，DQL）是SQL語言中用于對數據庫進行查詢的語句。該語句只有Select一個語句，但其變化范型較多。4. 數據控制語言數據控制語言（Data Control Language，DCL）是SQL語

6、言中用于對數據訪問權進行控制的語句。主要語句如下：w GRANT 授予用戶對數據對象的權限w DENY 拒絕授予用戶對數據對象的權限w REVOKE 撤消用戶對數據對象的權限8 電子科技大學數據庫原理及應用5. 事務處理語言事務處理語言（Transaction Process Language，TPL）是SQL語言中用于數據庫內部事務處理的語句。主要語句如下：w BEGIN TRANSACTION 開始事務w COMMIT 提交事務w ROLLBACK 回滾事務6. 游標控制語言游標控制語言（Cursor Control Language，CCL）是SQL語言中用于數據庫游標操作的語句。主要語

7、句如下：w DECLARE CURSOR 定義游標w FETCH INTO 提交游標數據w CLOSE CURSOR 關閉游標9 電子科技大學數據庫原理及應用六、六、SQL語言的數據類型語言的數據類型1. 基本數據類型w 字符：CHAR、VARCHAR、TEXTw 整數：SMALLINT、INTEGERw 浮點數：NUMBER(n,d)、FLOAT(n,d)w 日期：DATE、DATETIMEw 貨幣：MONEY2. 不同數據庫所支持數據類型Access的數據類型見表3-1所示SQL Server的數據類型見表3-2所示MySQL的數據類型見表3-3所示10 電子科技大學數據庫原理及應用部門信

8、息雇員信息項目信息任務信息工程項目管理數據庫數據庫系統(tǒng)任務:w 創(chuàng)建項目數據庫ProjectDBw 創(chuàng)建數據庫表對象及其約束w 完成數據庫表的數據插入、修改、刪除以及查詢處理某企業(yè)希望通過創(chuàng)建一個工程項目管理數據庫，實現對項目信息管理。3.2 數據定義語句DDL一、數據庫案例一、數據庫案例11 電子科技大學數據庫原理及應用列名類型鍵是否非空備注DepartmentNamechar(35)主鍵是部門名稱DepartIntroVarchar(200)否是部門簡介OfficeNumberchar(15)否是辦公室編號OfficePhonechar(12)否是辦公電話部門(DEPARTMENT)表結構

9、1. 數據庫表結構設計列名類型鍵是否非空備注EmployeeNumberInt主鍵是代理鍵，雇員編號NameVarchar(30)否是姓名Departmentchar(35)外鍵是部門Phonechar(12)否是電話EmailVarchar(100)否是郵件雇員(EMPLOYEE)表結構12 電子科技大學數據庫原理及應用列名類型鍵是否非空備注ProjectIDInt主鍵是項目編號NameVarchar(30)否是項目名稱Departmentchar(35)外鍵是部門MaxHoursNumber否是最長工時StartDateDateTime否否開始日期EndDateDateTime否否結束日期

10、項目(PROJECT)表結構13 電子科技大學數據庫原理及應用列名類型鍵是否非空備注ProjectIDInt主鍵,外鍵是復合鍵，項目編號EmployeeNumberInt主鍵,外鍵是復合鍵，雇員編號HoursWorkedNumeric(6,2)否否工作時數任務(ASSIGNMENT)表結構14 電子科技大學數據庫原理及應用列名類型鍵是否非空備注DepartmentNamechar(35)主鍵是部門名稱DepartIntroVarchar(200)否是部門簡介OfficeNumberchar(15)否是辦公室編號OfficePhonechar(12)否是辦公電話列名類型鍵是否非空備注Employ

11、eeNumberInt主鍵是代理鍵，雇員編號NameVarchar(30)否是姓名Departmentchar(35)外鍵是部門Phonechar(12)否是電話EmailVarchar(100)否是郵件參照完整性約束DEPARTMENT表EMPLOYEE表2. 數據庫表關聯設計15 電子科技大學數據庫原理及應用列名類型鍵是否非空備注DepartmentNamechar(35)主鍵是部門名稱DepartIntroVarchar(30)否是部門簡介OfficeNumberchar(15)否是辦公室編號OfficePhonechar(12)否是辦公電話DEPARTMENT表結構列名類型鍵是否非空備

12、注ProjectIDInt主鍵是項目編號NameVarchar(30)否是項目名稱Departmentchar(35)外鍵是部門MaxHoursNumeric(6,2)否是最長工期StartDateDateTime否否開始日期EndDateDateTime否否結束日期PROJECT表結構參照完整性約束16 電子科技大學數據庫原理及應用列名類型鍵是否非空備注ProjectIDInt主鍵是項目編號NameVarchar(30)否是項目名稱Departmentchar(35)外鍵是部門MaxHoursNumber否是最長工期StartDateDateTime否否開始日期EndDateDateTime

13、否否結束日期PROJECT表結構列名類型鍵是否非空備注ProjectIDInt主鍵,外鍵是復合鍵，項目編號EmployeeNumberInt主鍵,外鍵是復合鍵，雇員編號HoursWorkedNumeric(6,2)否否工作時數ASSIGNMENT表結構參照完整性約束17 電子科技大學數據庫原理及應用3. 業(yè)務規(guī)則設計w 如果要刪除EMPLOYEE中的某個行,若該行連接有ASSIGNMENT的外鍵值,則禁止對EMPLOYEE行執(zhí)行刪除操作。在創(chuàng)建表時定義參照完整性約束。w 如果要刪除PROJECT中的某個行,則ASSIGNMENT中連接到PROJECT的對應行數據也將隨之被刪除。在創(chuàng)建表時定義級

14、聯刪除約束。w 如果某雇員離職，則企業(yè)將刪除該雇員數據，但前提是必須先有人接替這個雇員工作，然后才允許刪除該雇員數據。w 如果企業(yè)取消某個項目，將刪除該項目數據。同時，也將刪除該項目所下屬的任務數據。數據庫中如何設計？問題：上述項目數據庫設計如何通過SQL實現?18 電子科技大學數據庫原理及應用二、數據庫創(chuàng)建語句二、數據庫創(chuàng)建語句1. 語句基本格式例 創(chuàng)建一個人力資源管理數據庫HR，其SQL語句如下：CREATE DATABASE HR;CREATE DATABASE 其中CREATE DATABASE 為創(chuàng)建數據庫語句的關鍵詞，為被創(chuàng)建數據庫的標識符名稱。19 電子科技大學數據庫原理及應用實

15、踐操作: 在SQL Server中，創(chuàng)建第一個數據庫HR。20 電子科技大學數據庫原理及應用2. 語句完整格式CREATE DATABASE on primary ( - 默認屬于primary文件組name=databasename_data，- 主數據文件的邏輯名稱filename=D：databasename_data.mdf， - 主數據文件的物理名稱size=數值MB, -主數據文件的初始大小maxsize=數值MB, - 主數據文件增長的最大值filegrowth=數值%-主數據文件的增長率）log on(/*-日志文件的具體描述,各參數含義同上-*/name=databasena

16、me_log, - 日志文件的邏輯名稱filename=所存目錄:databasename_log.ldf, - 日志文件的物理名稱size=數值MB, -日志文件的初始大小filegrowth=數值MB-日志文件的增長值）;21 電子科技大學數據庫原理及應用CREATE DATABASE ProjectDBon primary( - 默認屬于primary文件組name=Project_data,- 主數據文件的邏輯名稱filename=d:Project_data.mdf, - 主數據文件的物理名稱size=10MB, -主數據文件的初始大小maxsize=50MB, - 主數據文件增長的

17、最大值filegrowth=10%-主數據文件的增長率)log on(name=Project_log,- 日志文件的邏輯名稱filename=d:Project_log.ldf,- 日志文件的物理名稱size=10MB, -日志文件的初始大小filegrowth=1MB-日志文件的增長值)；例 創(chuàng)建一個項目信息管理數據庫ProjectDB，其SQL語句如下：22 電子科技大學數據庫原理及應用實踐操作: 采用完整參數SQL語句，創(chuàng)建數據庫ProjectDB。23 電子科技大學數據庫原理及應用三、數據庫刪除語句三、數據庫刪除語句1. 語句基本格式例 刪除前面創(chuàng)建的人力資源管理數據庫HR，其SQL

18、語句如下：DROP DATABASE HR；DROP DATABASE ;其中DROP DATABASE 為語句命令關鍵詞，為數據庫名稱。24 電子科技大學數據庫原理及應用實踐操作: 刪除數據庫HR。25 電子科技大學數據庫原理及應用實踐練習: 在SQL Server數據庫管理系統(tǒng)環(huán)境中，創(chuàng)建一個選課系統(tǒng)數據庫Catalog，完成后再刪除它。26 電子科技大學數據庫原理及應用四、數據庫表創(chuàng)建語句四、數據庫表創(chuàng)建語句1.語句基本格式CREATE TABLE （ 列完整性約束， 列完整性約束， 列完整性約束， )；其中CREATE TABLE 為創(chuàng)建表語句的關鍵詞，為將被創(chuàng)建的數據庫表名稱。27

19、電子科技大學數據庫原理及應用例 雇員數據庫表EMPLOYEE創(chuàng)建CREATE TABLE EMPLOYEE( EmployeeNumber Int, Name VarChar(30), Department Char(35), Phone Char(12), EmailVarChar(100) );列名類型鍵是否非空備注EmployeeNumberInt主鍵是NameVarChar(30)否是DepartmentChar(35)否是PhoneChar(12)否否EmailVarChar(100)否是28 電子科技大學數據庫原理及應用2. 列約束條件CREATE TABLE NewTableNa

20、me( ColumnName DataType OptionalConstraint, ColumnName DataType OptionalConstraint, ColumnName DataType OptionalConstraint, );列約束子句 w PRIMARY KEY主鍵w NOT NULL非空值w NULL空值w UNIQUE值唯一w CHECK有效性檢查w DEFAULT缺省值列約束子句29 電子科技大學數據庫原理及應用CREATE TABLE EMPLOYEE( EmployeeNumber Int PRIMARY KEY IDENTITY(1,1), Name V

21、arChar(30) NOT NULL, Department Char(35)NOT NULL DEFAULT 人力資源, Phone Char(12)NULL, EmailVarChar(100)NOT NULL UNIQUE );例 雇員數據庫表EMPLOYEE創(chuàng)建列名類型鍵是否非空備注EmployeeNumberInt主鍵是代理鍵NameVarChar(30)否是DepartmentChar(35)否是缺省值“人力資源”PhoneChar(12)否否EmailVarChar(100)否是要求取值唯一30 電子科技大學數據庫原理及應用說明：在Sybase或SQL Server，用INDE

22、NTITY標識代理鍵在Oracle數據庫中用SEQUENCE標識代理鍵在MySQL數據庫中，用AUTO_INCREMENT標識代理鍵31 電子科技大學數據庫原理及應用實踐操作: 在SQL Server中執(zhí)行SQL創(chuàng)建EMPLOYEE表32 電子科技大學數據庫原理及應用CREATE TABLE STUDENT( StudentID Char(10) PRIMARY KEY, StudentName VarChar(30)NOT NULL, StudentGender Char(2)NOT NULL CHECK (StudentGender IN(男,女), BirthDay Date NOT N

23、ULL, Major Varchar(30) NULL DEFAULT 軟件工程, StudentPhone Char(11) NULL );實踐練習: 課程目錄數據庫表STUDENT創(chuàng)建。列名類型鍵是否非空備注StudentIDChar(10)主鍵是StudentNameVarChar(20)否是StudentGenderChar(2)否是(男,女）BirthDayDate否是MajorVarchar(30)否否缺省值 “軟件工程”StudentPhoneChar(11)否否33 電子科技大學數據庫原理及應用3、使用表約束定義主鍵CREATE TABLE DEPARTMENT( Depart

24、mentName Char(35) NOT NULL, DepartIntro VarChar(200)NOT NULL, OfficeNumber Char(15)NOT NULL, OfficePhone Char(12)NOT NULL, CONSTRAINTDEPARTMENT_PKPRIMARY KEY(DepartmentName ) );使用表約束定義主鍵的優(yōu)點：w 便于定義復合主鍵w 可命名主鍵約束w 便于定義代理鍵34 電子科技大學數據庫原理及應用例 項目數據庫表PROJECT創(chuàng)建CREATE TABLE PROJECT( ProjectID Int NOT NULL IDE

25、NTITY(100,10), Name VarChar(30)NOT NULL, Department Char(35)NOT NULL, MaxHours Numeric(6,2) NOT NULL DEFAULT 60.30, StartDate DateTime NULL, EndDate DateTime NULL, CONSTRAINTPROJECT_PKPRIMARY KEY(ProjectID) );列名類型鍵是否非空備注ProjectIDInt主鍵是代理鍵100開始，增量10NameChar(30)否是DepartmentChar(35)否是MaxHoursNumeric(6,

26、2)否是缺省值 60.30StartDateDateTime否否EndDateDateTime否否35 電子科技大學數據庫原理及應用實踐操作: 在SQL SERVER中使用SQL表約束語句創(chuàng)建主鍵。36 電子科技大學數據庫原理及應用DEPARTMENT表結構列名類型鍵是否非空備注ProjectIDInt主鍵是項目編號NameVarChar(30)否是項目名稱DepartmentChar(35)外鍵是部門MaxHoursNumeric(6,2)否是最長工期StartDateDateTime否否開始日期EndDateDateTime否否結束日期PROJECT表結構參照完整性約束列名類型鍵是否非空備

27、注DepartmentNameText(35)主鍵是DepartIntroVarChar(200)否是OfficeNumberText(15)否是OfficePhoneText(12)否是4、使用表約束定義外鍵37 電子科技大學數據庫原理及應用CREATE TABLE PROJECT( ProjectID Int NOT NULL IDENTITY(1,1), Name VarChar(30)NOT NULL, Department Char(35)NOT NULL, MaxHours Numeric(6,2) NOT NULL DEFAULT 100, StartDate DateTime

28、NULL, EndDate DateTime NULL, CONSTRAINTPROJECT_PKPRIMARY KEY(ProjectID), CONSTRAINTPROJ_DEPART_FK FOREIGN KEY(Department)REFERENCES DEPARTMENT (DepartmentName) ON UPDATE CASCADE );例例 創(chuàng)建創(chuàng)建PROJECT表中外鍵及其參照完整性約束38 電子科技大學數據庫原理及應用實踐操作：在SQL Server中創(chuàng)建PROJECT表外鍵及其參照完整性約束。39 電子科技大學數據庫原理及應用列名類型鍵是否非空備注ProjectID

29、Int主鍵是NameVarChar(30)否是DepartmentChar(35)外鍵是MaxHoursNumber否是StartDateDateTime否否EndDateDateTime否否PROJECT表結構列名類型鍵是否非空備注ProjectIDInt主鍵,外鍵是EmployeeNumberInt主鍵,外鍵是HoursWorkedNumeric(6,2)否否ASSIGNMENT表結構參照完整性約束實踐練習: 創(chuàng)建ASSIGNMENT表中外鍵，及其參照PROJECT和EMPLOYEE表的完整性約束40 電子科技大學數據庫原理及應用列名類型鍵是否必需備注ProjectIDInt主鍵,外鍵是E

30、mployeeNumberInt主鍵,外鍵是HoursWorkedNumeric(6,2)否否ASSIGNMENT表結構參照完整性約束列名類型鍵是否必需備注EmployeeNumberInt主鍵是NameVarChar(30)否是DepartmentChar(35)外鍵是PhoneChar(12)否是EmailVarChar(120)否是EMPLOYEE表41 電子科技大學數據庫原理及應用CREATE TABLE ASSIGNMENT( ProjectID Int NOT NULL, EmployeeNumberIntNOT NULL, HoursWorked Numeric(6,2) NUL

31、L, CONSTRAINTASSIGNMENT_PK PRIMARY KEY(ProjectID,EmployeeNumber), CONSTRAINTASSIGN_PROJ_FK FOREIGN KEY(ProjectID)REFERENCES PROJECT(ProjectID)ON DELETE CASCADE,CONSTRAINTASSIGN_EMP_FK FOREIGN KEY(EmployeeNumber)REFERENCES EMPLOYEE(EmployeeNumber);42 電子科技大學數據庫原理及應用ALTER TABLE Constraint;ALTER TABLE 語

32、句可以用于添加、修改、刪除表結構的列和約束。例 刪除ASSIGNMENT表中外鍵ASSIGN_EMP_FK ，其SQL操作語句如下。ALTER TABLE ASSIGNMENT DROP CONSTRAINT ASSIGN_EMP_FK;五、修改表結構五、修改表結構SQL語句語句2、刪除表中外鍵1、語句基本格式43 電子科技大學數據庫原理及應用在ALT TABLE語句中，可以添加CHECK關鍵字實現數據范圍約束添加。例 在定義PROJECT表時，規(guī)定StartDate必須早于EndDate,則可通過如下語句實現。ALTER TABLE PROJECT ADD CONSTRAINT PROJEC

33、T_Check_DatesCHECK (StartDate EndDate);3、在表中添加約束44 電子科技大學數據庫原理及應用使用ALT TABLE語句，還可以在現有表結構中添加新的屬性列。例 在PROJECT表中添加一列CurrentTotalHours，以跟蹤在某項目上工作的實際小時數,則可通過如下語句實現。ALTER TABLE PROJECT ADD CurrentTotalHours Numeric(8,2) NULL;4、在表中添加列45 電子科技大學數據庫原理及應用實踐操作: 在PROJECT表中添加新列46 電子科技大學數據庫原理及應用例 在PROJECT表中刪除Curre

34、ntTotalHours列,則可通過如下語句實現。ALTER TABLE PROJECT DROP COLUMN CurrentTotalHours;5、在表中刪除列47 電子科技大學數據庫原理及應用六、刪除表結構六、刪除表結構SQL語句語句基本語句格式: DROP TABLE TableName;注意: 該語句將刪除該表的所有數據及其結構例 刪除EMPLOYEE表及其數據,可以使用如下語句。DROP TABLE EMPLOYEE;48 電子科技大學數據庫原理及應用注意: 如果某表包含了參照完整性約束，直接執(zhí)行DROP TABLE 語句，將會報錯。說明：刪除EMPLOYEE表前，必須先刪除AS

35、SIGNMENT表或至少刪除外鍵約束ASSIGN_EMP_FK，才能成功。49 電子科技大學數據庫原理及應用七、索引對象定義七、索引對象定義索引在數據庫中，索引是針對某個表中一列或多列建立的指向表中記錄數據頁的邏輯指針清單，其作用類似圖書目錄可支持對數據表的快速信息檢索。其中CREATE INDEX 為創(chuàng)建索引語句的關鍵詞。為在指定表中針對某列創(chuàng)建索引的名稱。該語句執(zhí)行后，在表中為指定列創(chuàng)建其列值的索引，使該列可實現快速查詢。1、索引創(chuàng)建SQL語句基本語句格式：CREATE INDEX ON ；50 電子科技大學數據庫原理及應用例 在學生信息表STUDENT中，為出生日期Birthday列創(chuàng)建

36、索引，以便可支持按出生日期快速查詢學生信息，其索引創(chuàng)建SQL語句如下。CREATE INDEX Birthday_Idx ON STUDENT （Birthday）；51 電子科技大學數據庫原理及應用索引優(yōu)點： 提高數據檢索速度 可快速連接關聯表 減少分組和排序時間索引開銷： 創(chuàng)建和維護索引都需要較大開銷 索引會占用額外存儲空間 數據操縱因維護索引帶來開銷52 電子科技大學數據庫原理及應用2、索引修改SQL語句其中ALTER INDEX 為索引對象修改語句的關鍵詞。為在指定表中針對某列創(chuàng)建索引的名稱。RENAME TO為索引換名關鍵詞。當該語句執(zhí)行后，原有索引被換名為新名稱。使用SQL語句可以

37、對索引進行修改操作，其語句格式分別如下：ALTER INDEX ON RENAME TO ；例 在學生信息表STUDENT中，將原索引Birthday_Idx更名Bday_Idx，其索引修改SQL語句如下：ALTER INDEX Birthday_Idx ON STUDENT RENAME TO Bday_Idx；53 電子科技大學數據庫原理及應用ALTER INDEX ON CLUSTERED | NONCLUSTERED；此外，索引對象修改SQL語句還可以更改索引的聚簇性質，例如修改為聚集索引或非聚集索引，其SQL語句格式如下：聚集索引與非聚集索引區(qū)別:在聚集索引中，鍵值的邏輯順序決定了表

38、中相應行的物理順序。在非聚集索引中，鍵值的邏輯順序可以與表中相應行的物理順序不同。54 電子科技大學數據庫原理及應用3、索引刪除SQL語句使用SQL語句可以對索引進行刪除操作，其語句格式分別如下：DROP INDEX ；其中DROP INDEX 為刪除索引語句的關鍵詞。為被指定的索引名稱。該語句執(zhí)行后，將從表中刪除該索引。例 在學生信息表STUDENT中，刪除Birthday_Idx索引，其索引刪除SQL語句如下：DROP INDEX Birthday_Idx；55 電子科技大學數據庫原理及應用學院信息、課程信息教師信息、學生信息開課信息、選課信息課程目錄數據庫數據庫系統(tǒng)任務:w 創(chuàng)建項目數據

39、庫Catalogw 創(chuàng)建數據庫表對象及其約束w 完成數據庫表的數據插入、修改、刪除以及查詢處理某高校希望通過創(chuàng)建一個課程目錄數據庫，實現學生選課管理。一、數據庫案例一、數據庫案例3.3 數據操縱SQL語句56 電子科技大學數據庫原理及應用學院信息表：COLLEGE（CollegeID，CollegeName，CollegeIntro，CollegeTel）課程表：COURSE（CourseID，CourseName，CourseType，CourseCredit，CoursePeriod，TestMethod）教師表：TEACHER（TeacherID，TeacherName，TeacherG

40、ender，TeacherTitle，CollegeID，TeacherPhone）學生表：STUDENT（StudentID，StudentName，StudentGender，BirthDay，Major，StudentPhone）開課計劃表：PLAN（CoursePlanID，CourseID，TeacherID，CourseRoom，CourseTime，Note）選課注冊表：REGISTER（CourseRegID，CoursePlanID，StudentID，Note）57 電子科技大學數據庫原理及應用二、單條數據插入二、單條數據插入例 在學生信息表STUDENT中，原有數據見下圖

41、所示INSERT INTO VALUES （列值表）；其中INSERT INTO 為插入語句的關鍵詞。為被插入數據的數據庫表。給出在表中插入哪些列。數據操縱SQL語句是一類對數據庫表中數據進行變動操作的語句集。它以INSERT、UPDATE、DELETE三種語句為核心，分別完成數據的插入、更新與刪除處理。58 電子科技大學數據庫原理及應用該語句執(zhí)行后，學生信息表STUDENT的數據見下圖所示。注意：在INSERT INTO插入數據語句中，所使用的Interger和Numeric等類型數值不使用引號標注，但Char、Varchar、Date和Datetime等值類型必須使用單引號。執(zhí)行如下插入數

42、據SQL語句：INSERT INTO STUDENT VALUES(201222010002,劉菲,女,1995-04-23,軟件工程,);59 電子科技大學數據庫原理及應用三、多條數據插入三、多條數據插入在數據庫表插入操作中，還可以一次執(zhí)行一組SQL數據插入語句，實現在表中多行數據插入。例 在學生信息表STUDENT中，一次插入多個學生數據，其插入數據SQL語句如下： INSERT INTO STUDENT VALUES(201222010003,張亮,男,1996-01-21,軟件工程,);INSERT INTO STUDENT VALUES(201222010004,謝云,男,1995-

43、08-12,軟件工程,);INSERT INTO STUDENT VALUES(201222010005,劉亞,女,1995-06-20,軟件工程,NULL);60 電子科技大學數據庫原理及應用實踐操作：在SQL SERVER中執(zhí)行上述插入語句。61 電子科技大學數據庫原理及應用這些語句執(zhí)行后，學生信息表STUDENT的數據見下圖所示。注意：在INSERT INTO插入數據語句中，若某些列的值不確定，可以在該列位置使用空值（NULL），但主鍵、非空列不允許使用NULL。此外，若表中主鍵為代理鍵，它不需要出現，因該值由DBMS自動提供62 電子科技大學數據庫原理及應用實踐練習: 在EMPLOYE

44、E表中插入多個員工信息數據列名類型鍵是否非空備注EmployeeNumberInt主鍵是代理鍵NameVarChar(30)否是DepartmentChar(35)否是缺省值“人力資源”PhoneChar(12)否否EmailVarChar(100)否是要求取值唯一63 電子科技大學數據庫原理及應用四、數據更新四、數據更新SQL語句語句數據更新SQL語句可以對數據表中指定數據進行修改處理，其語句基本格式如下：UPDATE SET = ，=.WHERE ；其中UPDATE 為數據更新語句的關鍵詞。為被更新數據的數據庫表。SET 關鍵詞指定對哪些列設定新值。WHERE 關鍵詞給出需要滿足的條件表達

45、式。64 電子科技大學數據庫原理及應用例 在學生信息表STUDENT中，學生“楊賓”的原有Email數據為空，現需要修改為“”。其數據修改的SQL語句如下：UPDATE STUDENTSET Email= WHERE StudentName=楊賓；65 電子科技大學數據庫原理及應用實踐操作：在SQL SERVER中執(zhí)行上述更新SQL語句。66 電子科技大學數據庫原理及應用這個語句執(zhí)行后，學生信息表STUDENT的數據下圖所示。67 電子科技大學數據庫原理及應用實踐練習: 在下面的STUDENT表中，使用SQL語句將“劉亞”的出生日期和Email分別修改為“1995-05-15”、。68 電子科

46、技大學數據庫原理及應用五、數據刪除五、數據刪除SQL語句語句數據刪除語句DELETE將從指定數據庫表中刪除滿足條件的數據行，其語句基本格式如下：DELETE FROM WHERE ；其中DELETE FROM為數據刪除語句的關鍵詞。為被刪除數據的數據庫表。WHERE 關鍵詞給出需要滿足的條件表達式。69 電子科技大學數據庫原理及應用例 在學生信息表STUDENT中，刪除姓名為 “張亮”的學生數據，其數據刪除的SQL語句如下：DELETE FROM STUDENTWHERE StudentName=張亮；70 電子科技大學數據庫原理及應用實踐練習: 在下面的STUDENT表中，刪除“張亮”信息。

47、71 電子科技大學數據庫原理及應用3.4 SQL關系查詢語句一、一、 語句基本格式語句基本格式在SQL語言中，數據查詢語言 (DQL) 是負責對數據表進行查詢訪問的語句，這是對數據庫訪問使用最多的一類SQL語句SELECT ALL|DISTINCT ， INTO FROM ， WHERE GROUP BY HAVING ORDER BY ASC | DESC ;72 電子科技大學數據庫原理及應用二、從單個表讀取指定列二、從單個表讀取指定列例 學生信息表STUDENT原始數據如下圖所示。在數據庫中，最簡單的數據查詢操作就是從單個數據表中讀取指定列的數據，其基本語句格式為：SELECT ，FROM

48、 ；73 電子科技大學數據庫原理及應用若要從STUDENT表中讀取學生的學號、姓名、專業(yè)等列數據，其數據查詢SQL語句如下：SELECT StudentID，StudentName，MajorFROM STUDENT;74 電子科技大學數據庫原理及應用如果希望從STUDENT表查詢所有列數據，其數據查詢SQL語句如下：SELECT * FROM STUDENT;75 電子科技大學數據庫原理及應用若希望從STUDENT表中查詢專業(yè)列（Major）數據，其數據查詢SQL語句執(zhí)行結果如下：76 電子科技大學數據庫原理及應用為了使讀取的數據過濾重復行,可以在查詢語句中加入DISTINCT關鍵字，其執(zhí)行

49、結果如下。77 電子科技大學數據庫原理及應用三、從單個表讀取指定行三、從單個表讀取指定行SQL查詢語句也可以從一個數據表中讀取滿足條件的指定行數據，即完成關系數據的元組選擇操作，其基本語句格式如下。SELECT *FROM WHERE ;例 從STUDENT表中查詢性別為“男”的學生數據，其數據查詢SQL語句如下。SELECT *FROM STUDENTWHERE StudentGender=男;78 電子科技大學數據庫原理及應用實踐操作：執(zhí)行上述SQL SELECT語句。79 電子科技大學數據庫原理及應用四、從單個表讀取指定行和列四、從單個表讀取指定行和列例 從STUDENT表中查詢性別為“

50、男”的學生學號、學生姓名、性別、專業(yè)數據，其數據查詢SQL語句如下。SELECT StudentID, StudentName, StudentGender, MajorFROM STUDENTWHERE StudentGender=男;在SQL查詢語句中，還可以從一個數據表中讀取指定行與指定列范圍內的數據，即同時完成關系數據的行列投影操作，其基本語句格式為：SELECT ，FROM WHERE ;80 電子科技大學數據庫原理及應用實踐操作：執(zhí)行上述SQL SELECT語句。81 電子科技大學數據庫原理及應用在WHERE子句中可以使用如下方式，指定范圍數據。1）使用BETWEEN.AND關鍵詞

51、來限定列值范圍，還可以使用關鍵詞LIKE與通配符來限定查詢條件。2）SQL查詢語言的常用通配符有下劃線（_）和百分號（%）。下劃線（_）通配符用于代表一個未指定的字符。百分號（%）通配符用于代表一個或多個未指定的字符。五、五、Where條件子句條件子句82 電子科技大學數據庫原理及應用例 若要從STUDENT表中查詢出生日期在“1995-04-01”到“1996-04-01”的學生數據。其查詢SQL語句如下：SELECT *FROM STUDENTWHERE BirthDay BETWEEN 1995-04-01 AND 1996-04-01;該語句執(zhí)行后，其查詢操作結果見下圖所示。83 電子

52、科技大學數據庫原理及應用例 若要從STUDENT表中查詢郵箱域名為“”的學生數據。其數據查詢SQL語句如下：SELECT *FROM STUDENTWHERE Email LIKE %;該語句執(zhí)行后，其查詢操作結果見下圖所示。84 電子科技大學數據庫原理及應用實踐練習: 從學生數據表STUDENT中查詢“劉”姓的學生信息。85 電子科技大學數據庫原理及應用說明： 在SQL查詢Where子句中，還可以使用多個條件表達式，并通過邏輯運算符（AND、OR、NOT）連接操作，進一步限定結果集的數據范圍。以及使用IN或NOT IN關鍵詞限定列值范圍。例 從STUDENT表中查詢性別為“男”，并且專業(yè)為“

53、軟件工程”的學生數據，其數據查詢SQL語句如下。SELECT StudentID, StudentName, StudentGender, MajorFROM STUDENTWHERE Major=軟件工程 AND StudentGender=男;86 電子科技大學數據庫原理及應用實踐操作：執(zhí)行上述SQL SELECT語句。87 電子科技大學數據庫原理及應用IN關鍵字使用: 從STUDENT表中查詢,使用IN關鍵字讀取”計算機應用”專業(yè)的學生。其SQL語句如下所示。SELECT StudentID, StudentName, StudentGender, MajorFROM STUDENTWH

54、ERE Major IN (計算機應用);88 電子科技大學數據庫原理及應用實踐操作：執(zhí)行上述SQL SELECT語句。89 電子科技大學數據庫原理及應用六、對結果進行排序六、對結果進行排序 在SELECT查詢語句返回的結果中,行的順序是任意的。如果需要結果集排序，可以在SELECT語句中加入ORDER BY關鍵字。例 若要從STUDENT表中按學生出生日期降序輸出學生數據，其數據查詢SQL語句如下。SELECT *FROM STUDENTORDER BY Birthday DESC;在默認情況下，SQL查詢的結果集是按指定列值的升序排列。可以使用關鍵詞ASC和DESC選定排序是升序或降序。9

55、0 電子科技大學數據庫原理及應用實踐操作：執(zhí)行上述SQL SELECT語句。91 電子科技大學數據庫原理及應用如果需要結果集按多個列排序，可以分別加入關鍵字ASC或DESC改變。例 若要將STUDENT表查詢數據，首先按出生日期降序排列，然后按姓名升序排列，其數據查詢SQL語句如下：SELECT *FROM STUDENTORDER BY Birthday DESC , StudentName ASC;92 電子科技大學數據庫原理及應用實踐練習: 從學生數據表STUDENT中查詢信息，分別按姓名、出生日期升序排列輸出。93 電子科技大學數據庫原理及應用七、七、SQL內置函數和計算內置函數和計算

56、在SQL語言中，可以使用函數方式對SELECT查詢結果集數據進行處理。這些函數可以是DBMS系統(tǒng)所提供的內置函數 ，也是用戶根據需要自定義函數。典型DBMS系統(tǒng)提供的內置函數主要有以下幾類：聚合函數算術函數字符串函數日期時間函數數據類型轉換函數94 電子科技大學數據庫原理及應用例 若要統(tǒng)計STUDENT表中的學生人數，在SELECT語句中可以使用COUNT（）函數來計算，其查詢SQL語句如下：SELECT COUNT（*） AS 學生人數FROM STUDENT;95 電子科技大學數據庫原理及應用例 若要找出STUDENT表中年齡最大和年齡最小的學生出生日期，其查詢SQL語句如下：SELECT

57、 Min（Birthday） AS 最大年齡，Max（Birthday） AS 最小年齡FROM STUDENT;96 電子科技大學數據庫原理及應用例 若要計算出STUDENT表中各個學生的Email字符串長度，其查詢SQL語句如下：SELECT StudentID, StudentName, Email, LEN( Email) AS 郵箱長度FROM STUDENT;97 電子科技大學數據庫原理及應用八、八、SQL內置函數和分組內置函數和分組在SQL語言中，可在查詢結果集進行分組數據統(tǒng)計。這是通過在SELECT語句中加入Group By子語句來實現。例 若要分專業(yè)統(tǒng)計STUDENT表中的學

58、生人數。在SELECT語句中可以使用GROUP BY分組子句完成統(tǒng)計，其查詢SQL語句如下：SELECT Major AS 專業(yè), COUNT（StudentID） AS 學生人數FROM STUDENTGROUP BY Major;98 電子科技大學數據庫原理及應用實踐操作：執(zhí)行上述SQL SELECT語句。99 電子科技大學數據庫原理及應用在分組統(tǒng)計SQL查詢語句中，還可以使用HAVING子句限定分組統(tǒng)計的條件。例 若要分專業(yè)統(tǒng)計STUDENT表中男生人數，但限定只顯示人數大于2的人數，其查詢SQL語句如下：SELECT Major AS 專業(yè), COUNT（StudentID） AS 學

59、生人數FROM STUDENTWHERE StudentGender=男GROUP BY MajorHAVING COUNT(*)2;100 電子科技大學數據庫原理及應用實踐操作：執(zhí)行上述SQL SELECT語句。101 電子科技大學數據庫原理及應用Group by與having理解group by 有一個原則,就是 select 后面的所有列中,沒有使用聚合函數的列,必須出現在 group by 后面或者包含在having 后的聚合函數里。where 子句的作用是在對查詢結果進行分組前將不符合where條件的行去掉，即在分組之前過濾數據條件中不能包含聚合函數。 having 子句的作用是篩選

60、滿足條件的組即在分組之后過濾數據條件中經常包含聚合函數。GROUP BY 是分組查詢, 一般 GROUP BY 是和聚合函數配合使用102 電子科技大學數據庫原理及應用注意：除了使用GROUP BY語句外，列的名稱是不允許和內置函數一起混合使用。以下語句不規(guī)范。SELECT MaxHours, SUM(MaxHours)FROMPROJECTWHEREProjectID = 1200;DBMS產品在使用內置函數的方式也不一樣。一般來說，內置函數是不能用于WHERE子句中的。以下語句不規(guī)范:SELECT ProjectID, MaxHoursFROMPROJECTWHEREMaxHours 40