第十二章 SQL語言簡介 講授大綱： 新增資料庫 新增資料表 新增資料 修改資料 刪除資料 查詢資料 透視ASP.NET-第12章

Presentation on theme: "第十二章 SQL語言簡介 講授大綱： 新增資料庫 新增資料表 新增資料 修改資料 刪除資料 查詢資料 透視ASP.NET-第12章"— Presentation transcript:

1 第十二章 SQL語言簡介 講授大綱： 新增資料庫 新增資料表 新增資料 修改資料 刪除資料 查詢資料 透視ASP.NET-第12章
王有禮教授編著

2 新增資料庫 用SQL command來建立資料庫的語法如下：
Create Database 新資料庫名稱 連結字串的寫法，其中database的值要設定成master，即“database=master”。 範例程式 Page Language="vb" %> Import Namespace="System.Data" %> Import Namespace="System.Data.SQLClient" %> <script runat="server"> sub Page_Load(obj as object, e as eventArgs) dim Conn as new _ SQLConnection("server=localhost;uid=sa;pwd=;database=master") dim objCmd as new SQLCommand("Create Database CourseInformation", Conn) try objCmd.Connection.Open objCmd.ExecuteNonQuery Response.write("Successful.") catch ex as Exception Response.write("Error updating the database.") end try objCmd.Connection.Close end sub </script> 透視ASP.NET-第12章 王有禮教授編著

3 新增資料表(1/2) 方法一: 方法二: Create table 新資料表名稱 ( 欄位名稱一 資料型別(長度) 是否允許Null
…) 方法二: Select 欄位名稱一, 欄位名稱二, … into 新資料表名稱 from 舊資料表名稱 where 條件式 透視ASP.NET-第12章 王有禮教授編著

4 新增資料表(2/2) Create table的SQL Command Create table Students (
Page Language="VB" %> Import Namespace="System.Data" %> Import Namespace="System.Data.SQLClient" %> <script runat="server"> sub Page_Load(obj as object, e as eventArgs) dim Conn as new SQLConnection _ ("server=localhost;uid=sa;pwd=;database=CourseInformation") dim strSQL as string strSQL = "Create table Students ( " strSQL += "StudentName varchar(16) Not Null, " strSQL += "StudentNo char(5) Primary Key, " strSQL += "Department varchar(16) Not Null, " strSQL += "Class char(4) Not Null)" dim objCmd as new SQLCommand(strSQL, Conn) try objCmd.Connection.Open objCmd.ExecuteNonQuery Response.write("Successful.") catch ex as Exception Response.write("資料庫更改錯誤") end try objCmd.Connection.Close end sub </script> Create table的SQL Command Create table Students ( StudentName varchar(16) Not Null, StudentNo char(5) Primary Key, Department varchar(16) Not Null, Class char(4) Not Null) 透視ASP.NET-第12章 王有禮教授編著

5 SQL Server中的資料型態(1/2) 名稱 說明 bit 一個位元，值為0、1或Null。 int 四位元組的整數。 smallint
二位元組的整數。 tinyint 一位元組的整數。 numeric 十進位數，例numeric(6,2)表示6位數，其中兩位是小數。最多可到38位數。 decimal 意義同numeric。 float 浮點數，值介於-1.79E+308到1.79E+308之間，有效位數最多可到53位數。 real 浮點數，值介於-3.4E+38到3.4E+38之間，有效位數最多可到24位數。 datetime 日期和時間，精確度到秒。 smalldatetime 日期和時間，精確度到分。 char 固定字串長度，不管實際字串長短，都要用掉宣告的長度，最長可用8000字元。 varchar 變動字串長度，所用空間視實際字串長度而定，最長可用8000字元。 透視ASP.NET-第12章 王有禮教授編著

6 SQL Server中的資料型態(2/2) 名稱 說明 text 變動字串長度，最長可用2GB字元。 nchar
Unicode編碼，固定字串長度，最長可用4000字元。 nvarchar Unicode編碼，變動字串長度，最長可用4000字元。 ntext Unicode編碼，最長可用2GB字元。 binary 固定長度的二元碼，最長為8000個位元組。 varbinary 變動長度的二元碼，最長為8000個位元組。 image 變動長度的二元碼，最長為2GB個位元組，可用來儲存圖片資料。 money 金額資料型態，範圍在-9.2E+15到9.2E+15之間。 smallmoney 金額資料型態，範圍在-2.1E+6到2.1E+6之間。 timestamp 時間戳記，此欄不可設定值，系統會自動設定時間。 uniqueidentifier 資料表中tuple的唯一識別碼。 透視ASP.NET-第12章 王有禮教授編著

7 Grades、Courses、Teachers的建立
複合attributes為主鍵的語法： Constraint 主鍵名稱 Primary Key (欄位名稱一, 欄位名稱二, …) Create table Grades ( StudentNo char(5) Not Null, CourseID char(4) Not Null, Scores decimal(9) Null, Constraint StudentNo_CourseID Primary Key (StudentNo, CourseID)) Create table Courses ( CourseName varchar(16) Not Null, CourseID char(4) Primary Key, TeacherName varchar(16) Not Null, CoursePoints int Not Null) Create table Teachers ( TeacherName varchar(16) Primary Key, TeacherTitle varchar(16) Not Null) 透視ASP.NET-第12章 王有禮教授編著

8 新增資料 方法一: 方法二: 方法三: 例一: 例二: Insert into 資料表名稱(欄位名稱一, 欄位名稱二, …)
Values (欄位值一, 欄位值二, …) 方法二: Insert into 資料表名稱 方法三: Default values 例一: Insert into Students(StudentName, StudentNo, Department, Class) values ("李景維", "M9903", "資管系", "二") 例二: Insert into Students values ("李景維", "M9903", "資管系", "二") 範例程式:WebPage1203.aspx 透視ASP.NET-第12章 王有禮教授編著

9 修改資料 Update 資料表名稱 set 欄位名稱一=欄位值一, 欄位名稱二=欄位值二, … where 條件式
例如王立詳教授升等為副教授，則資料表Teachers的內容要做修改，其statement如下： Update Teachers set TeacherTitle = "副教授" where TeacherName = "王立詳" 欄位值不一定要是常數，也可以是變數、運算式或小括弧中含一個子查詢(視 同一個算式)。算式中可以有"+"(加)、"-"(減)、"*"(乘)、"/"(除)、"%"(餘數)等簡單的算術運算符號，也可以有邏輯運算符號"and"、"or"、"not"，另外還提供數學、字串、日期等函數。例如: Update Grades set Scores = sqrt(Scores) * 10 透視ASP.NET-第12章 王有禮教授編著

10 刪除資料 Delete 資料表名稱 where 條件式 Delete 資料表名稱: Truncate table 資料表名稱:
一行一行的方式刪除資料表中所有的資料，資料表仍然存在 Truncate table 資料表名稱: 一次刪光資料表中所有的資料，資料表仍然存在 Drop table資料表名稱: 整個資料表一起刪去，資料表不再存在 例如吳致賢要退選平行計算，可用第一種語法，其寫法如下： Delete Grades where StudentNo = "B9901" and CourseID = "EE01" 透視ASP.NET-第12章 王有禮教授編著

11 查詢資料 由於是查詢資料，通常都要顯示在螢幕上，故須配合DataGrid、DataList、Repeater等控制標籤一起使用。
查詢的基本語法如下： Select 欄位名稱一, 欄位名稱二, … [into 新資料表名稱] from 資料表名稱 [where 條件式] [group by 欄位名稱A, 欄位名稱B, …] [having 尋找條件式] [order by 排序算式 [ASC|DESC]] 上述語法中以中括號表示的子句是可有可無的， 不在中括號內的子句是不可或缺的， 中括號內的項目則是以regular expression表示。 透視ASP.NET-第12章 王有禮教授編著

12 簡單的資料查詢(1/2) Select 欄位名稱一, 欄位名稱二, … from 資料表名稱
例如從Students資料表中選出學生姓名及系別，其寫法如下： Select StudentName, Department from Students Page Language="VB" %> Import Namespace="System.Data" %> Import Namespace="System.Data.SQLClient" %> <script runat="server"> sub Page_Load(obj as Object, e as EventArgs) dim myConnection as new SQLConnection _ ("server=localhost;uid=sa;pwd=;database=CourseInformation") dim myCommand as new SQLDataAdapter _ ("select StudentName, Department from Students", myConnection) dim ds as DataSet = new DataSet() myCommand.Fill(ds, "Students") ListData.DataSource = ds.Tables("Students") ListData.DataBind() end sub </script> <html><body><form runat="server"> <asp:DataGrid ID="ListData" Runat="server" BorderColor="black" CellPadding="4" Font-Size="8pt" HeaderStyle-BackColor="#cccc99" ItemStyle-BackColor="#ffffff" AlternatingItemStyle-BackColor="#cccccc" AutoGenerateColumns="false"> <Columns> <asp:BoundColumn HeaderText="姓名" DataField="StudentName" /> <asp:BoundColumn HeaderText="系別" DataField="Department" /> </Columns> </asp:DataGrid> </form></body></html> 透視ASP.NET-第12章 王有禮教授編著

13 簡單的資料查詢(2/2) 選出所有的欄位，其語法如下： 例如Select * from Students 最典型的查詢語法如下： 例如：
where 條件式 例如： Select StudentName, Department from Students where Department = "資管系" 透視ASP.NET-第12章 王有禮教授編著

14 Where條件式 表12.2 條件式中的運算符號 運算子 說明 = 等於 < 小於 > 大於 <>或!= 不等於
<= 小於等於 >= 大於等於 !> 不大於 !< 不小於 In 限定的欄位 Between 介於一個範圍之間 Like 格式檢查 透視ASP.NET-第12章 王有禮教授編著

15 Where範例(1/2) 例一: 例二: 例如要在資料表Grades中，找出成績在80到95之間的學生，可以寫成：
Select * from Grades where CourseID in ("BM01", "BM02", "BM03") 例二: 印出的資料是沒修企管系課程的學生，可以寫成： where CourseID not in ("BM01", "BM02", "BM03") 例如要在資料表Grades中，找出成績在80到95之間的學生，可以寫成： where Scores between 80 and 95 例如果要找出資料表Grades中課程代號有“M”的tuples，其寫法如下： where CourseID like "%M%" 此statement的“%M%”中的“%” 表示0個以上的任意字元，所以 “%M%”的意思是0個以上的任意字元， 其後跟隨一個英文字母“M”， 其後再跟隨0個以上的任意 透視ASP.NET-第12章 王有禮教授編著

16 Where範例(2/2) 如果“M”一定要是第2個字元，則可利用底線符號“_”，如下例：
Select * from Grades where CourseID like “_M%” 若要求某幾個字元都可以，則可以利用中括號。例如從資料表Teachers中找出副教授或助理教授，可以寫成： Select * from Teachers where TeacherTitle like "[副助]%“ 如果like之後的字串寫成“[^副助]%”，則意思正好相反，是指第一個字元不可以是“副”或“助”。 若是一連串的字元都可以，例如可由A到H開頭的字串，則可寫成"[^A-H]%"。中括號的用法並不限定在字串的開頭才可使用，字串的其他位置也可以使用。 底線符號“_”表示該位置一定要有一個字元， 所以“_M%”的意思是，任意一個字元其後跟 隨英文字母"M"，其後再跟隨0個以上的任意字元。 透視ASP.NET-第12章 王有禮教授編著

17 合併資料表的查詢(1/5) 合併資料表的基本語法如下：
Select 欄位名稱一, 欄位名稱二, … from 資料表名稱一 合併類型 資料表名稱二 on 合併條件 例如我們要將學生資料表Students和成績資料表Grades合併成學生成績資料表StudentScores，其寫法如下： Select Students.StudentName, Students.StudentNo, Students.Department, Students.Class, Grades.CourseID, Grades.Scores from Students inner join Grades on Students.StudentNo = Grades.StudentNo 範例程式:WebPage1207.aspx 瀏覽WebPage1207.aspx 透視ASP.NET-第12章 王有禮教授編著

18 合併資料表的查詢(2/5) 若也要顯示李世傑的資料，但是課程代號及成績以Null代之。以避免有學生未選課，而學校並不知情。則合併類型要用left outer join，其SQL statement如下： Select Students.StudentName, Students.StudentNo, Students.Department, Students.Class, Grades.CourseID, Grades.Scores from Students left outer join Grades on Students.StudentNo = Grades.StudentNo 若將WebPage1207.aspx的第9行到第13行換成上述SQL statement，則顯示的畫面如圖12.15。 圖12.15 合併類型為left outer join 透視ASP.NET-第12章 王有禮教授編著

19 合併資料表的查詢(3/5) 合併類型為left outer join的原因是，學生資料表Students是位在left outer join的左邊。若學生資料表Students是位在右邊，則from部分要寫成 from Grades right outer join Students 若不管合併類型左或右邊的資料表，即使沒有對應的資料，也要顯示該筆資料，可用full outer join，即from部分寫成： from Grades full outer join Students 透視ASP.NET-第12章 王有禮教授編著

20 合併資料表的查詢(4/5) 把Students、Grades、Courses、Teachers這四個資料表合併成表11.2，其程式如圖12.16的WebPage1208.aspx。 Page Language="VB" %> Import Namespace="System.Data" %> Import Namespace="System.Data.SQLClient" %> <script runat="server"> sub Page_Load(obj as Object, e as EventArgs) dim myConnection as new SQLConnection _ ("server=localhost;uid=sa;pwd=;database=CourseInformation") dim strSQL as string strSQL = "Select Students.StudentName, Students.StudentNo, " strSQL += "Students.Department, Students.Class, " strSQL += "Courses.CourseName, Courses.CourseID, Courses.CoursePoints, " strSQL += "Grades.Scores, " strSQL += "Teachers.TeacherName, Teachers.TeacherTitle " strSQL += "from Students inner join Grades " strSQl += "inner join Courses " strSQL += "inner join Teachers " strSQL += "on Courses.TeacherName = Teachers.TeacherName " strSQL += "on Courses.CourseID = Grades.CourseID " strSQL += "on Students.StudentNo = Grades.StudentNo " dim myCommand as new SQLDataAdapter(strSQL, myConnection) dim ds as new DataSet() myCommand.Fill(ds, "StudentScores") ListData.DataSource = ds.Tables("StudentScores") ListData.DataBind end sub </script> 透視ASP.NET-第12章 王有禮教授編著

21 合併資料表的查詢(5/5) 圖12.17 瀏覽WebPage1208.aspx
<html><body><form runat="server"> <asp:DataGrid ID="ListData" Runat="server" BorderColor="black" CellPadding="4" Font-Size="8pt" HeaderStyle-BackColor="#cccc99" ItemStyle-BackColor="#ffffff" AlternatingItemStyle-BackColor="#cccccc" AutoGenerateColumns="false"> <Columns> <asp:BoundColumn HeaderText="學生姓名" DataField="StudentName" /> <asp:BoundColumn HeaderText="學號" DataField="StudentNo" /> <asp:BoundColumn HeaderText="系別" DataField="Department" /> <asp:BoundColumn HeaderText="年級" DataField="Class" /> <asp:BoundColumn HeaderText="課程名稱" DataField="CourseName" /> <asp:BoundColumn HeaderText="課程代號" DataField="CourseID" /> <asp:BoundColumn HeaderText="開課老師" DataField="TeacherName" /> <asp:BoundColumn HeaderText="老師職稱" DataField="TeacherTitle" /> <asp:BoundColumn HeaderText="學分" DataField="CoursePoints" /> <asp:BoundColumn HeaderText="成績" DataField="Scores" /> </Columns> </DataGrid> </form></body></html> 圖12.16 WebPage1208.aspx 圖12.17 瀏覽WebPage1208.aspx 透視ASP.NET-第12章 王有禮教授編著

22 統計資料查詢(1/2) 在SQL Server中，常用的統計函數有：Count、Max、Min、Avg、Sum，Count是計算總數的，Max是找欄位中的最大值，Min是找欄位中的最小值，Avg是計算所有欄位值的平均值，Sum是將所有欄位值加總。 圖12.19的WebPage1209.aspx就是算資料表Grades的上述統計資料。 Page Language="VB" %> Import Namespace="System.Data" %> Import Namespace="System.Data.SQLClient" %> <script runat="server"> sub Page_Load(obj as Object, e as EventArgs) dim myConnection as new SQLConnection _ ("server=localhost;uid=sa;pwd=;database=CourseInformation") dim strSQL as string strSQL = "Select Count(*) as TotalPerson, " strSQL += "Max(Scores) as MaxScore, " strSQL += "Min(Scores) as MinScore, " strSQL += "Avg(Scores) as AvgScore, " strSQL += "Sum(Scores) as SumScore " strSQL += "from Grades" dim myCommand as new SQLDataAdapter(strSQL, myConnection) dim ds as new DataSet() myCommand.Fill(ds, "StudentScores") ListData.DataSource = ds.Tables("StudentScores") ListData.DataBind end sub </script> <html><body><form runat="server"> 透視ASP.NET-第12章 王有禮教授編著

23 統計資料查詢(2/2) 圖12.20 瀏覽WebPage1208.aspx
<asp:DataGrid ID="ListData" Runat="server" BorderColor="black" CellPadding="4" Font-Size="8pt" HeaderStyle-BackColor="#cccc99" ItemStyle-BackColor="#ffffff" AlternatingItemStyle-BackColor="#cccccc" AutoGenerateColumns="false"> <Columns> <asp:BoundColumn HeaderText="學生人數" DataField="TotalPerson" /> <asp:BoundColumn HeaderText="最高分" DataField="MaxScore" /> <asp:BoundColumn HeaderText="最低分" DataField="MinScore" /> <asp:BoundColumn HeaderText="平均分數" DataField="AvgScore" /> <asp:BoundColumn HeaderText="分數總和" DataField="SumScore" /> </Columns> </DataGrid> </form></body></html> 圖12.19 WebPage1209.aspx 圖12.20 瀏覽WebPage1208.aspx 透視ASP.NET-第12章 王有禮教授編著