SQL基礎入門教學課件

1、第章SQL第1頁，共116頁。SQL概述(Structured Query Language)簡稱SQL作用：結構化查詢語言是一種數(shù)據庫查詢和程序設計語言，用于存取數(shù)據以及查詢、更新和管理關系數(shù)據庫系統(tǒng)2第2頁，共116頁。學習目標創(chuàng)建數(shù)據庫利用SQL的DDL語句創(chuàng)建數(shù)據表，并理解SQL支持的數(shù)據類型利用SQL建立完整性約束理解查詢語句基本結構能夠用SELECT語句進行簡單查詢能夠按照字段對信息進行篩選能夠對查詢的記錄進行排序能夠進行集合查詢能夠使用聚集函數(shù)查詢能夠進行嵌套查詢理解連接的函義，并使用連接進行查詢利用SQL進行數(shù)據庫的修改3第3頁，共116頁。一、創(chuàng)建數(shù)據庫方法：1、圖形化創(chuàng)建2

2、、用CREATE語句創(chuàng)建4第4頁，共116頁。任務：在Management Studio中創(chuàng)建數(shù)據庫student。【任務分析】 根據任務要求，要求使用SQL Server Management Studio的圖形化界面創(chuàng)建名為student的數(shù)據庫，并保存在C盤db文件夾下。數(shù)據庫的具體屬性如下表所示：student的數(shù)據文件和事務日志文件邏輯名稱文件類型文件組系統(tǒng)文件名初始大小最大容量增長容量student1數(shù)據文件primaryC:/db/student_data.mdf3MB30MB10MBstudentlog事務日志文件C:/db/student_log.ldf1MB10MB10%第

3、5頁，共116頁。相關知識數(shù)據庫的文件結構邏輯結構是指數(shù)據庫由哪些性質的信息組成。SQL Server的數(shù)據庫是由表、視圖、索引、存儲過程和觸發(fā)器等各種不同的對象組成，它們構成了數(shù)據庫的邏輯結構。數(shù)據庫對象說明表用于存放數(shù)據，由行和列組成視圖可以看成是虛擬表或存儲查詢索引用于快速查找所需信息存儲過程用于完成特定功能的SQL語句集觸發(fā)器一種特殊類型的存儲過程SQL Server 2012數(shù)據庫的常用對象第6頁，共116頁。物理結構也稱為存儲結構，表示數(shù)據庫文件是如何在磁盤上存放的。SQL Server 2012中的數(shù)據庫文件在磁盤上以文件的單位存放，由數(shù)據庫文件和事務日志文件組成。 根據文件作用

4、的不同，又可以將它們分為三類：主數(shù)據庫文件、輔助數(shù)據庫文件和事務日志文件。其中，主數(shù)據庫文件是必須要有的，輔助數(shù)據庫文件可以根據需要設置一個或者多個，而事務日志文件至少有一個。相關知識數(shù)據庫的文件結構數(shù)據庫文件功能擴展名主數(shù)據庫文件存放DB的啟動信息、部分或全部數(shù)據和數(shù)據庫對象.mdf輔助數(shù)據庫文件存放除主數(shù)據庫文件外的數(shù)據和數(shù)據庫對象.ndf事務日志文件用來存放恢復數(shù)據庫所需的事務日志信息，記錄數(shù)據庫更新情況.ldf第7頁，共116頁。 基本概念主要數(shù)據文件數(shù)據文件數(shù)據庫 次要數(shù)據文件事務日志文件用于存儲數(shù)據庫中的所有對象，如表用來存儲數(shù)據庫的啟動信息、部分或全部數(shù)據，擴展名為.mdf每個數(shù)

5、據庫只能有一個主要數(shù)據文件用于保存所有主要數(shù)據文件中容納不下的數(shù)據，擴展名為.ndf可有多個保存用于恢復數(shù)據庫的日志信息至少一個第8頁，共116頁。操作步驟1、打開Management Studio界面第9頁，共116頁。操作步驟2、打開“新建數(shù)據庫”對話框第10頁，共116頁。操作步驟3、按要求設置數(shù)據庫基本屬性第11頁，共116頁。任務 用CREATE DATABASE語句創(chuàng)建數(shù)據庫student 【任務分析】 要求使用Management Studio創(chuàng)建名為student的數(shù)據庫，同時設置數(shù)據庫的相關屬性。student的數(shù)據文件和事務日志文件邏輯名稱文件類型文件組系統(tǒng)文件名初始大小最

6、大容量增長容量student_data數(shù)據文件primaryC:/db/student_data.mdf3MB30MB10MBstudent_log事務日志文件C:/db/student_log.ldf1MB10MB5MB第12頁，共116頁。知識說明：CREATE DATABASE語句的基本格式CREATE DATABASE database_name ON PRIMARY（）LOG ON （）第13頁，共116頁。關鍵代碼create database studenton primary(name=student1,filename=d:dbstudent.mdf,size=10MB,ma

7、xsize=100MB,filegrowth=5MB),第14頁，共116頁。(name=student2,filename=d:dbstudent.ndf,size=8MB,maxsize=50MB,filegrowth=2MB)15第15頁，共116頁。log on(name=studentlog,filename=d:dbstudent.ldf,size=5MB,maxsize=50MB,filegrowth=5MB)16第16頁，共116頁。分離與附加數(shù)據庫 1.分離數(shù)據庫用 SQL Server Management Studio分離數(shù)據庫展開樹形目錄，定位到要分離的數(shù)據庫上單擊鼠標

8、右鍵，在彈出的快捷菜單中，選擇【任務】|【分離】命令。2022/8/317第17頁，共116頁。附加數(shù)據庫1. 用 SQL Server Management Studio附加數(shù)據庫1）在數(shù)據庫節(jié)點上單擊鼠標右鍵，彈出快捷菜單，選擇【附加】命令。2）出現(xiàn)【附加數(shù)據庫】對話框，單擊【添加】按鈕，出現(xiàn)【定位數(shù)據庫文件】對話框,在這個對話框里，默認只顯示數(shù)據庫的數(shù)據文件，即“mdf”文件。2022/8/318第18頁，共116頁。數(shù)據庫分離與附加的作用和功能一、可以切斷數(shù)據庫的使用 (比如當數(shù)據庫日志很大占用了空間時，可以用分離數(shù)據庫的方法來切斷數(shù)據庫，從而刪除以前的數(shù)據庫日志，來節(jié)省空間）二、可以

9、在需要更換數(shù)據庫物理存放地址時使用。如果您數(shù)據庫系統(tǒng)安裝在系統(tǒng)盤（比如 C 盤），由于 C 盤容易受病毒侵害，您也許希望您的數(shù)據存放在非系統(tǒng)盤（比如 D 盤），要做的這點很簡單，您并不需要重裝數(shù)據庫，只要把數(shù)據“分離”，然后將相關文件移動到 D 盤的某個目錄，接著“附加”數(shù)據庫即可。第19頁，共116頁。相關知識脫機與聯(lián)機脫機與聯(lián)機是針對數(shù)據庫的當前狀態(tài)來說的，當一個數(shù)據庫處于可操作、可查詢的狀態(tài)時就是聯(lián)機狀態(tài)，而一個數(shù)據庫盡管可以看到其名字出現(xiàn)在數(shù)據庫節(jié)點中，但對其不能執(zhí)行任何有效的數(shù)據庫操作時就是脫機狀態(tài)。第20頁，共116頁。脫機與聯(lián)機數(shù)據庫脫機數(shù)據庫1. 用 SQL Server Ma

10、nagement Studio脫機數(shù)據庫1）展開樹形目錄，定位到要脫機的數(shù)據庫上單擊鼠標右鍵，在彈出的快捷菜單中，選擇【任務】|【脫機】命令2）彈出【使數(shù)據庫脫機】對話框，單擊【關閉】按鈕即可完成操作。2022/8/321第21頁，共116頁。聯(lián)機數(shù)據庫1. 用 SQL Server Management Studio聯(lián)機數(shù)據庫1）展開樹形目錄，定位到要聯(lián)機的數(shù)據庫上單擊鼠標右鍵，在彈出的快捷菜單中，選擇【任務】|【聯(lián)機】命令。2）彈出【使數(shù)據庫聯(lián)機】對話框，單擊【關閉】按鈕即可完成操作2022/8/322第22頁，共116頁。收縮數(shù)據庫1 用 Management Studio收縮數(shù)據庫和文

11、件1）在目標數(shù)據庫上單擊鼠標右鍵，在彈出的快捷菜單中選擇【任務】|【收縮】|【數(shù)據庫】。2）出現(xiàn)【收縮數(shù)據庫】對話框，選中并設置相應的值，點擊【確認】。2022/8/323第23頁，共116頁。收縮數(shù)據庫3）如果想收縮數(shù)據庫的指定數(shù)據文件或日志文件，可右擊相應的數(shù)據庫，彈出快捷菜單，選擇【任務】|【收縮】|【文件】4）出現(xiàn)【收縮文件】對話框，選中并設置相應的值，點擊【確認】。第24頁，共116頁。刪除數(shù)據庫1 用 SQL Server Management Studio刪除數(shù)據庫在【對象資源管理器】中，定位在目標數(shù)據庫上單擊鼠標右鍵，在彈出的快捷菜單中選擇【刪除】。2用 T-SQL 命令刪除數(shù)

12、據庫 DROP DATABASE database_name,.n2022/8/325可同時刪除多個數(shù)據庫-例1DROP DATABASE BJGL例2DROP DATABASE student2-例3DROP DATABASE 東軟第25頁，共116頁。思考假如沒有對日志文件進行定義，那么日志文件的大小和數(shù)據文件有什么差別？怎樣創(chuàng)建有多個數(shù)據文件的數(shù)據庫？第26頁，共116頁。課堂實踐操作1在SQL Server中創(chuàng)建數(shù)據庫，具體要求如下：（1）數(shù)據庫名稱為college。（2）主要數(shù)據文件：邏輯文件名為college1 ，物理文件名為college.mdf，初始容量為10MB，最大容量為1

13、00MB，遞增量為10MB。（3）次要數(shù)據文件：邏輯文件名為college 2，物理文件名為college.ndf，初始容量為5MB，最大容量為50MB，遞增量為5MB。（4）事務日志文件：邏輯文件名為college Log，物理文件名為college .ldf，初始容量為5MB，最大容量為50MB，遞增量為5MB。第27頁，共116頁。二、創(chuàng)建數(shù)據表方法：1、圖形化創(chuàng)建2、用DDL語句創(chuàng)建28第28頁，共116頁。數(shù)據類型29第29頁，共116頁。整數(shù)類型比較bigint：從-263(-9223372036854775808)到263-1(9223372036854775807)的整型數(shù)據，

14、存儲大小為 8 個字節(jié)。一個字節(jié)就是8位，那么bigint就有64位int：從-231(-2,147,483,648)到231-1(2,147,483,647)的整型數(shù)據，存儲大小為 4 個字節(jié)。int類型，最大可以存儲32位的數(shù)據 smallint：從-215(-32,768)到215-1(32,767)的整數(shù)數(shù)據，存儲大小為 2 個字節(jié)。smallint就是有16位 tinyint：從0到255的整數(shù)數(shù)據，存儲大小為 1 字節(jié)。tinyint就有8位。30第30頁，共116頁。字符類型數(shù)據1、CHAR。CHAR存儲定長數(shù)據很方便，CHAR字段上的索引效率級高，比如定義char(10)，那么

15、不論你存儲的數(shù)據是否達到了10個字節(jié)，都要占去10個字節(jié)的空間。 2、VARCHAR。存儲變長數(shù)據，但存儲效率沒有CHAR高。如果一個字段可能的值是不固定長度的，我們只知道它不可能超過10個字符，把它定義為 VARCHAR(10)是最合算的。3、TEXT。text存儲可變長度的非Unicode數(shù)據，最大長度為231-1(2,147,483,647)個字符。31第31頁，共116頁。任務一：在student數(shù)據庫中創(chuàng)建數(shù)據表student的表結構【任務分析】 數(shù)據表包括表結構、約束以及記錄等三個要件。這個任務我們主要是完成對數(shù)據表student表結構的創(chuàng)建，表結構如下表所示：表 25 stude

16、nt表的結構字段名類型是否允許為空備注snochar(10)不允許學生編碼snamechar(10)不允許姓名ssexchar(2)允許性別sbirthdaydatetime允許出生日期sscorenumeric(18, 0)允許入學成績classnochar(8)不允許班級編碼第32頁，共116頁。任務一：創(chuàng)建數(shù)據表student的表結構【任務分析】可以使用兩種方法來解決這個問題：利用Management Studio工具，在圖形化的界面中創(chuàng)建student表結構。使用CREATE TABLE語句創(chuàng)建表結構。第33頁，共116頁。方法一：操作界面（操作步驟）展開需要創(chuàng)建表的數(shù)據庫“stude

17、nt”并右擊“表”，在彈出的快捷菜單中選擇“新建表”命令。第34頁，共116頁。方法一：操作界面（操作步驟）在打開的表設計器對話框中，按照任務要求設置表student各列的列名、數(shù)據類型、允許空（非空約束） 第35頁，共116頁。方法一：操作界面（操作步驟）各列創(chuàng)建完成后，單擊工具欄中的【保存】按鈕，系統(tǒng)自動打開“選擇名稱”對話框，輸入新建的表的名稱“student” 第36頁，共116頁。知識說明數(shù)據表的三個要件列名：即字段名，最大長度為128個字符，可以包含漢字、英文字母、數(shù)字、下劃線及其他符號。同一張表中列名必須唯一。數(shù)據類型：即字段的數(shù)據類型。見下頁。允許空：為復選框，選中表示該列在插

18、入和修改記錄時可以不賦值。說明：是對此列的注釋第37頁，共116頁。練習請在student數(shù)據庫中建下列表第38頁，共116頁。練習：class的表結構練習：department的表結構字段名類型是否允許為空備注classnochar(8)不允許班級編號classnamechar(16)不允許班級名pnochar(4)不允許專業(yè)編號字段名類型是否允許為空備注deptnochar(2)允許系編號deptnamechar(20)允許系名第39頁，共116頁。練習：teacher的表結構字段名類型是否允許為空備注tnochar(4)不允許編號tnamechar(10)允許姓名tsexchar(2)允

19、許性別tbirthdaydatetime允許出生日期ttitlechar(10)允許職稱第40頁，共116頁。練習：professional的表結構練習：course的表結構字段名類型是否允許為空備注pnochar(4)不允許專業(yè)編號pnamechar(30)允許專業(yè)名deptnochar(2)允許系編號字段名類型是否允許為空備注cnochar(7)允許課程編號cnamechar(30)允許課程名Creditsreal允許學分第41頁，共116頁。練習：teaching的表結構練習：choice的表結構字段名類型是否允許為空備注tnochar(4)允許教師編號cnochar(7)允許課程編號字

20、段名類型是否允許為空備注snochar(10)不允許學號cnochar(7)不允許課程編號gradereal允許成績第42頁，共116頁。方法二、用DDL語句創(chuàng)建表43第43頁，共116頁。利用SQL的DDL語句創(chuàng)建數(shù)據表例：CREATE TABLE STUDENT (ID VARCHAR (13) NOT NULL , name VARCHAR (20) , tot_cred INT, dept_name VARCHAR (30) NOT NULL , Sbirthday datetime,Primary key (id),Foreign key (dept_name) references

21、 department如果沒有department表，則暫時不鍵立外鍵)字段名數(shù)據類型是否能空設置主碼設置外碼第44頁，共116頁。練習：用SQL在college數(shù)據庫建立下表關系模式如下：Department(dept_name, building, budget)dept_name表示系部名稱，類型為VARCHAR (30),非空Building表示所在教學樓名稱，類型為VARCHAR (30)Budget表示年度預算，類型為 NUMERIC (10,2)Course(course_id,title,credits,dept_name)Course_id表示課程編號，類型為VARCHAR

22、(10),非空Title表示課程名稱，類型為VARCHAR (40)Credits表示學分，類型為intdept_name表示所屬系部，類型為VARCHAR (30)45第45頁，共116頁。三、利用SQL修改表461、修改字段2、修改字段屬性3、修改字段是否為空4、修改主鍵5、修改外鍵第46頁，共116頁。-如需在表中添加列ALTER TABLE table_nameADD column_name datatype例：ALTER TABLE classADD x int47第47頁，共116頁。-如需在表中刪除列ALTER TABLE table_nameDROP COLUMN column

23、_name例：ALTER TABLE classDROP COLUMN x48第48頁，共116頁。-改變表中列的名稱方法一：先刪除列，再建新列方法二：EXEC sp_rename 表名.原列名, 新列名例：exec sp_rename class.pnoo,pno49第49頁，共116頁。-修改表的名稱EXEC sp_rename 表名, 新表名例：exec sp_rename class,class150第50頁，共116頁。2、改變表中列的數(shù)據類型ALTER TABLE table_nameALTER COLUMN column_name datatype例：Alter table cl

24、assAlter column pno char(5) 51第51頁，共116頁。3、通過修改表：建立非空/空約束例：Alter table class alter column pno char(4) not null52第52頁，共116頁。4、通過修改表：建立主鍵Use collegegoalter table studentadd constraint newpk11primary key(studentid)注意：如果在建表時，已創(chuàng)建主鍵，請先刪除再建。53第53頁，共116頁。5、通過修改表：建立外鍵Use collegegoalter table studentadd const

25、raint newfkforeign key(dept_name) references department54第54頁，共116頁。練習：1、在student數(shù)據庫中分別給學生表，班級表，系部表，課程表中用圖形化界面的方法建立主、外鍵。2、刪除college表中的主、外鍵，通過修改語句，在college數(shù)據庫中分別給學生表，系部表，課程表建立主、外鍵，要求用SQL語句完成，并保存代碼。3、將課堂演示中修改過的表的名稱，屬性等修改回來。55第55頁，共116頁。鼠標右擊“對象資源管理器”窗口的節(jié)點【student】的子節(jié)點【數(shù)據庫關系圖】，彈出快捷菜單 6、建立數(shù)據庫關系圖顯示表間關系第56

26、頁，共116頁。2.單擊【新建數(shù)據庫關系圖】命令，在“Management Studio”中打開一個數(shù)據庫關系圖的標簽頁，并且激活了“添加表”對話框，在【表】列表框中選擇表student和表class 第57頁，共116頁。單擊【添加】按鈕，并單擊【關閉】按鈕，關閉“添加表”對話框。在【關系圖】的標簽頁中顯示已添加的表student和class 第58頁，共116頁。4、單擊【保存】按鈕，會彈出“選擇名稱”對話框，輸入關系圖的名稱“學生-班級”或者“student_class”，可以將數(shù)據庫關系圖保存在數(shù)據庫中。59第59頁，共116頁。7、在數(shù)據表中插入數(shù)據方法一：圖形化界面插入數(shù)據 60第

27、60頁，共116頁。方法二：利用代碼語法：Insert into tablename (first_column,.last_column) values (first_value,.last_value)61第61頁，共116頁。四、利用SQL實現(xiàn)完整性約束62第62頁，共116頁。知識說明-數(shù)據完整性實體完整性：又稱行完整性，要求在表中不能存在完全相同的行，而且每行都要具有一個非空且又不重復的主鍵值。例如：學生管理系統(tǒng)的student表(sno,sname,ssex,)中，sno為主鍵，該屬性不能為空63第63頁，共116頁。知識說明-數(shù)據完整性參照完整性：又稱引用完整性，指表間的規(guī)則，作

28、用于有關聯(lián)的兩個或兩個以上的表，通過使用主鍵和外鍵（或唯一鍵）之間的關系，使表中的鍵值在相關表中保持一致。64第64頁，共116頁。知識說明-數(shù)據完整性用戶自定義完整性：指針對某一具體關系數(shù)據庫的約束條件，它反映某一具體應用所涉及的數(shù)據必須滿足的語義要求。SQL Server 2012提供非空約束、對屬性的check約束、對元組的check約束、觸發(fā)器等來實現(xiàn)用戶的完整性要求。65第65頁，共116頁。1、非空、主、外鍵約束（前節(jié)已學習）2、unique約束3、check子句4、創(chuàng)建規(guī)則66第66頁，共116頁。2、建立唯一鍵67第67頁，共116頁。例：ALTER TABLE student

29、1ADD CONSTRAINT uk_bjmcUNIQUE NONCLUSTERED(id)68第68頁，共116頁。練習：1、在student數(shù)據庫中分別向學生表，班級表，系部表，課程表中用圖形化界面的方法分別插入5條記錄2、在college數(shù)據庫中分別向學生表，系部表，課程表分別插入3條記錄，要求用SQL語句完成，并保存代碼。3、建立student、college數(shù)據庫中各表的數(shù)據關系圖，并保存.、給student、college數(shù)據庫中的數(shù)據表中添加唯一鍵約束。69第69頁，共116頁。3、check約束70第70頁，共116頁。將數(shù)據表的sscore字段設置在306到650的范圍之間。

30、【任務分析】 假定該校學生的入學成績的最高分是650，最低分是306分，即在306到650的范圍之間，該任務就要為sscore設置檢查約束，如果輸入的入學成績超出這個范圍，系統(tǒng)會認為輸入的信息有誤拒絕接收數(shù)據，從而保證了數(shù)據的完整性 71第71頁，共116頁。操作方式創(chuàng)建步驟： 展開“對象資源管理器”窗口中的節(jié)點“dbo.student”，右擊其子節(jié)點“約束”，彈出快捷菜單。選擇快捷菜單中的“新建約束”命令，打開“CHECK約束”對話框，單擊【添加】按鈕，單擊“表達式”右側 按鈕，彈出“CHECK 約束表達式”對話框。72第72頁，共116頁。在其中編輯約束條件“sscore=306 and

31、sscore=306 and sscore、=、=、!、!= 比較結果是布爾值，即：true(表達式結果為真)、false及unknown。數(shù)據類型為text、ntext、image的數(shù)據不可用比較運算符。105第105頁，共116頁。使用邏輯運算符 and、or、not三種。BETWEENAND運算符查詢指定條件范圍的記錄 使用BETWEENAND運算符查詢時在BETWEEN運算符和AND運算符后面都需要給定一個值。其語法格式如下： 字段1 BETWEEN 值1 AND 值2 106第106頁，共116頁。 集合運算符in、not in，用來查找某個值是否屬于某個集合記錄。107第107頁，共116頁。例：查詢總分超過500的學生信息查詢總分在500到1000之間的學生信息查詢總分在小于500或者大于1000的學生信息查詢總分既不小于500也不大于1000的學生信息108第10