Transcript 第十二章 SQL語言簡介

第十二章 SQL語言簡介
 講授大綱：
新增資料庫
新增資料表
新增資料
修改資料
刪除資料
查詢資料
王有禮教授編著
透視ASP.NET-第12章
1
新增資料庫
用SQL command來建立資料庫的語法如下：
Create Database 新資料庫名稱
連結字串的寫法，其中database的值要設定成master，即“database=master”。
範例程式
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
<%@ 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章
2
新增資料表(1/2)
方法一:
Create table 新資料表名稱 (
欄位名稱一 資料型別(長度) 是否允許Null
欄位名稱二 資料型別(長度) 是否允許Null
…)
方法二:
Select 欄位名稱一, 欄位名稱二, …
into 新資料表名稱
from 舊資料表名稱
where 條件式
王有禮教授編著
透視ASP.NET-第12章
3
新增資料表(2/2)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
<%@ 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)"
 Create table的SQL Command
dim objCmd as new SQLCommand(strSQL, Conn)
Create table Students (
try
StudentName varchar(16) Not Null,
objCmd.Connection.Open
objCmd.ExecuteNonQuery
StudentNo char(5) Primary Key,
Response.write("Successful.")
Department varchar(16) Not Null,
catch ex as Exception
Class char(4) Not Null)
Response.write("資料庫更改錯誤")
end try
objCmd.Connection.Close
end sub
</script>
王有禮教授編著
透視ASP.NET-第12章
4
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章
5
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章
6
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章
7
新增資料
方法一:
Insert into 資料表名稱(欄位名稱一, 欄位名稱二, …)
Values (欄位值一, 欄位值二, …)
方法二:
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章
8
修改資料
Update 資料表名稱
set 欄位名稱一=欄位值一, 欄位名稱二=欄位值二, …
where 條件式
Update 資料表名稱
set 欄位名稱一=欄位值一, 欄位名稱二=欄位值二, …
例如王立詳教授升等為副教授，則資料表Teachers的內容要做修改，其
statement如下：
Update Teachers set TeacherTitle = "副教授" where TeacherName = "王立詳"
欄位值不一定要是常數，也可以是變數、運算式或小括弧中含一個子查詢(視
同一個算式)。算式中可以有"+"(加)、"-"(減)、"*"(乘)、"/"(除)、"%"(餘數)等簡
單的算術運算符號，也可以有邏輯運算符號"and"、"or"、"not"，另外還提供
數學、字串、日期等函數。例如:
Update Grades set Scores = sqrt(Scores) * 10
王有禮教授編著
透視ASP.NET-第12章
9
刪除資料
Delete 資料表名稱
where 條件式
Delete 資料表名稱:
一行一行的方式刪除資料表中所有的資料，資料表仍然存在
Truncate table 資料表名稱:
一次刪光資料表中所有的資料，資料表仍然存在
Drop table資料表名稱:
整個資料表一起刪去，資料表不再存在
例如吳致賢要退選平行計算，可用第一種語法，其寫法如
下：
Delete Grades where StudentNo = "B9901" and
CourseID = "EE01"
王有禮教授編著
透視ASP.NET-第12章
10
查詢資料
由於是查詢資料，通常都要顯示在螢幕上，故須
配合DataGrid、DataList、Repeater等控制標籤一
起使用。
查詢的基本語法如下：
Select 欄位名稱一, 欄位名稱二, …
[into 新資料表名稱]
from 資料表名稱
[where 條件式]
[group by 欄位名稱A, 欄位名稱B, …]
[having 尋找條件式]
[order by 排序算式 [ASC|DESC]]
上述語法中以中括號表示的子句是可有可無的，
不在中括號內的子句是不可或缺的，
中括號內的項目則是以regular expression表示。
王有禮教授編著
透視ASP.NET-第12章
11
簡單的資料查詢(1/2)

Select 欄位名稱一, 欄位名稱二, …
from 資料表名稱
例如從Students資料表中選出學生姓名及系別，其寫法如下：
Select StudentName, Department from Students
1.
<%@ Page Language="VB" %>
2.
<%@ Import Namespace="System.Data" %>
3.
<%@ Import Namespace="System.Data.SQLClient" %>
4.
<script runat="server">
5.
sub Page_Load(obj as Object, e as EventArgs)
6.
dim myConnection as new SQLConnection _
7.
("server=localhost;uid=sa;pwd=;database=CourseInformation")
8.
dim myCommand as new SQLDataAdapter _
9.
("select StudentName, Department from Students", myConnection)
10.
dim ds as DataSet = new DataSet()
11.
myCommand.Fill(ds, "Students")
12.
ListData.DataSource = ds.Tables("Students")
13.
ListData.DataBind()
14.
end sub
15.
</script>
16.
<html><body><form runat="server">
17.
<asp:DataGrid ID="ListData" Runat="server" BorderColor="black" CellPadding="4"
18.
Font-Size="8pt" HeaderStyle-BackColor="#cccc99" ItemStyle-BackColor="#ffffff"
19.
AlternatingItemStyle-BackColor="#cccccc" AutoGenerateColumns="false">
20.
<Columns>
21.
<asp:BoundColumn HeaderText="姓名" DataField="StudentName" />
22.
<asp:BoundColumn HeaderText="系別" DataField="Department" />
23.
</Columns>
24.
</asp:DataGrid>
25.
</form></body></html>
透視ASP.NET-第12章
王有禮教授編著

12
簡單的資料查詢(2/2)
選出所有的欄位，其語法如下：
Select * from 資料表名稱
例如Select * from Students
最典型的查詢語法如下：
Select 欄位名稱一, 欄位名稱二, …
from 資料表名稱
where 條件式
例如：
Select StudentName, Department
from Students
where Department = "資管系"
王有禮教授編著
透視ASP.NET-第12章
13
Where條件式
表12.2 條件式中的運算符號
王有禮教授編著
運算子
說明
=
等於
<
小於
>
大於
<>或!=
不等於
<=
小於等於
>=
大於等於
!>
不大於
!<
不小於
In
限定的欄位
Between
介於一個範圍之間
Like
格式檢查
透視ASP.NET-第12章
14
Where範例(1/2)
例一:
要在資料表Grades中找出選修企管系的同學，可以寫成：
Select * from Grades
where CourseID in ("BM01", "BM02", "BM03")
例二:
印出的資料是沒修企管系課程的學生，可以寫成：
Select * from Grades
where CourseID not in ("BM01", "BM02", "BM03")
例如要在資料表Grades中，找出成績在80到95之間的學生，可以寫
成：
Select * from Grades
where Scores between 80 and 95
例如果要找出資料表Grades中課程代號有“M”的tuples，其寫法如下：
Select * from Grades
where CourseID like "%M%"
王有禮教授編著
此statement的“%M%”中的“%”
表示0個以上的任意字元，所以
“%M%”的意思是0個以上的任意字元，
其後跟隨一個英文字母“M”，
其後再跟隨0個以上的任意
透視ASP.NET-第12章
15
Where範例(2/2)
如果“M”一定要是第2個字元，則可利用底線符號“_”，如下例：

Select * from Grades
where CourseID like “_M%”
底線符號“_”表示該位置一定要有一個字元，
所以“_M%”的意思是，任意一個字元其後跟
隨英文字母"M"，其後再跟隨0個以上的任意字元。
若要求某幾個字元都可以，則可以利用中括號。例如從資料表
Teachers中找出副教授或助理教授，可以寫成：

Select * from Teachers
where TeacherTitle like "[副助]%“
如果like之後的字串寫成“[^副助]%”，則意思正好相反，是指第一個
字元不可以是“副”或“助”。
若是一連串的字元都可以，例如可由A到H開頭的字串，則可寫成
"[^A-H]%"。中括號的用法並不限定在字串的開頭才可使用，字串的其
他位置也可以使用。
王有禮教授編著
透視ASP.NET-第12章
16
合併資料表的查詢(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章
17
合併資料表的查詢(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章
18
合併資料表的查詢(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章
19
合併資料表的查詢(4/5)
把Students、Grades、Courses、Teachers這四個資料表合併成表11.2，其程式如圖12.16的
WebPage1208.aspx。
1.
<%@ Page Language="VB" %>
2.
<%@ Import Namespace="System.Data" %>
3.
<%@ Import Namespace="System.Data.SQLClient" %>
4.
<script runat="server">
5.
sub Page_Load(obj as Object, e as EventArgs)
6.
dim myConnection as new SQLConnection _
7.
("server=localhost;uid=sa;pwd=;database=CourseInformation")
8.
dim strSQL as string
9.
strSQL = "Select Students.StudentName, Students.StudentNo, "
10.
strSQL += "Students.Department, Students.Class, "
11.
strSQL += "Courses.CourseName, Courses.CourseID, Courses.CoursePoints, "
12.
strSQL += "Grades.Scores, "
13.
strSQL += "Teachers.TeacherName, Teachers.TeacherTitle "
14.
strSQL += "from Students inner join Grades "
15.
strSQl += "inner join Courses "
16.
strSQL += "inner join Teachers "
17.
strSQL += "on Courses.TeacherName = Teachers.TeacherName "
18.
strSQL += "on Courses.CourseID = Grades.CourseID "
19.
strSQL += "on Students.StudentNo = Grades.StudentNo "
20.
dim myCommand as new SQLDataAdapter(strSQL, myConnection)
21.
dim ds as new DataSet()
22.
myCommand.Fill(ds, "StudentScores")
23.
ListData.DataSource = ds.Tables("StudentScores")
24.
ListData.DataBind
25.
end sub
26.
</script>
透視ASP.NET-第12章
王有禮教授編著

20
合併資料表的查詢(5/5)
27)
28)
29)
30)
31)
32)
33)
34)
35)
36)
37)
38)
39)
40)
41)
42)
43)
44)
<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章
21
統計資料查詢(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" %>
3.
<%@ Import Namespace="System.Data.SQLClient" %>
4.
<script runat="server">
5.
sub Page_Load(obj as Object, e as EventArgs)
6.
dim myConnection as new SQLConnection _
7.
("server=localhost;uid=sa;pwd=;database=CourseInformation")
8.
dim strSQL as string
9.
strSQL = "Select Count(*) as TotalPerson, "
10.
strSQL += "Max(Scores) as MaxScore, "
11.
strSQL += "Min(Scores) as MinScore, "
12.
strSQL += "Avg(Scores) as AvgScore, "
13.
strSQL += "Sum(Scores) as SumScore "
14.
strSQL += "from Grades"
15.
dim myCommand as new SQLDataAdapter(strSQL, myConnection)
16.
dim ds as new DataSet()
17.
myCommand.Fill(ds, "StudentScores")
18.
ListData.DataSource = ds.Tables("StudentScores")
19.
ListData.DataBind
20.
end sub
21.
</script>
22.
<html><body><form runat="server">
透視ASP.NET-第12章
王有禮教授編著
1.
2.
22
統計資料查詢(2/2)
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
<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章
23