數(shù)據(jù)庫系統(tǒng)概論課程設(shè)計(jì)報(bào)告

1、課程設(shè)計(jì)說明書設(shè)計(jì)題目：SELECT查詢功能實(shí)現(xiàn)專業(yè)：班級：02級2班設(shè)計(jì)人：_山東科技大學(xué)2006年月日課程設(shè)計(jì)任務(wù)書一、課程設(shè)計(jì)題目：（ 1） 設(shè)計(jì)一個(gè) DBMS，并實(shí)現(xiàn) select 查詢功能（ 2） select 查詢功能包括單表、雙表、三表查詢（ 3） 實(shí)現(xiàn)簡單的 and、or 、where 查詢二、課程設(shè)計(jì)主要參考資料：（ 1） 王珊 薩師煊 . 數(shù)據(jù)庫系統(tǒng)概論 . 高等教育出版社（第四版）（ 2） 孫繼磊 劉亮亮 . 精通 Visual C#2008 . 人民郵電出版社（ 3） Solid Quality Learning.SQL Server 2005 從入門到精通 . 清華大

2、學(xué)出版社三、課程設(shè)計(jì)應(yīng)解決的主要問題：（ 1） 表的物理文件用 xml 文件存儲（ 2） 實(shí)現(xiàn)圖形界面的對整個(gè)表的查詢（ 3） 命令行形式實(shí)現(xiàn)單表、多表、 where、and、or 等的查詢四、 課程設(shè)計(jì)相關(guān)附件（如 : 圖紙、軟件等）：（ 1） Visual studio 2008（ 2） xml 文本編輯五、任務(wù)發(fā)出日期：2011-4-29課程設(shè)計(jì)完成日期： 2011-7-1指導(dǎo)教師簽字：系主任簽字：指導(dǎo)教師對課程設(shè)計(jì)的評語指導(dǎo)教師簽字：2011年月日山東科技大學(xué)學(xué)生課程設(shè)計(jì)一、設(shè)計(jì)要求：SELECT查詢語句的實(shí)現(xiàn)要求：（ 1）表的物理文件采用Access、VFP等系統(tǒng)的文件；（ 2）語句

3、以命令行和圖形化界面兩種形式實(shí)現(xiàn)；二、需求分析：數(shù)據(jù)庫系統(tǒng)能夠有效地組織和管理大量的數(shù)據(jù)。 查詢功能是數(shù)據(jù)庫非常重要的功能， 通過對數(shù)據(jù)庫概論的學(xué)習(xí)，了解了數(shù)據(jù)文件的組織，內(nèi)存數(shù)據(jù)結(jié)構(gòu)（鏈表、數(shù)組）的定義，數(shù)據(jù)字典的存儲和訪問，命令行語句的解析，數(shù)據(jù)操縱語句和數(shù)據(jù)查詢語句的實(shí)現(xiàn)方法， 數(shù)據(jù)庫的完整性約束等。然后通過編程，進(jìn)一步加深對數(shù)據(jù)庫技術(shù)的理解。三、設(shè)計(jì)思想：本課程設(shè)計(jì)主要實(shí)現(xiàn)以下功能：（1） 圖形界面查詢界面查詢時(shí) ,靜態(tài)的在 treeview 中添加數(shù)據(jù)條目 (數(shù)據(jù)庫名稱 ,表的名稱,每個(gè)表的列名 ),在點(diǎn)擊相應(yīng)的條目的時(shí)候 ,在 DataGridView 中顯示信息。根據(jù)選中的信息，

4、確定確定 DataGridView 中的數(shù)據(jù)源。確定數(shù)據(jù)源后，將數(shù)據(jù)和 DataGridView 數(shù)據(jù)綁定，在開始讓 DataGridView 中所有的信息不能顯示，然后根據(jù)條件判斷要顯示的條目，再在 DataGridView 中顯示出來。（2） SQL語句查詢先判斷輸入的查詢語句是否正確， 若輸入正確繼續(xù)， 否則提示錯(cuò)誤信息。語句正確時(shí)，再確定查詢內(nèi)容（確定 select 后面的內(nèi)容），打開相應(yīng)的數(shù)據(jù)表 （ from 后面的信息），將數(shù)據(jù)源與 DataGridView 數(shù)據(jù)綁定，然后在 DataGridView 中顯示出查詢結(jié)果，并提示操作結(jié)果是否正確。將 Select 查詢查詢分為查詢條件

5、是 * 的，與不是 * 兩種情況進(jìn)行討論。四、程序流程圖：（ 1）界面查詢流程：第 1頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)（ 2） SQL語句查詢程序流程圖輸入 SQL 語句錯(cuò)誤提示N輸入是否合法Y提取查詢表路徑Y(jié)查詢條件是否為*N提取查詢的列屬性提取查詢表路徑顯示相關(guān)信息N查詢條件whereY查詢條件whereN顯示相關(guān)信息提取查詢條件Y是否為單表查詢顯示相關(guān)信息錯(cuò)誤提示N是否能夠執(zhí)行YN提取多個(gè)表的路徑顯示相應(yīng)結(jié)果提取查詢的條件建立連接關(guān)系錯(cuò)誤提示N是否能夠執(zhí)行顯示相應(yīng)結(jié)果五、主要源程序：1、查詢語句關(guān)鍵字集合private void richTextBox1_TextChanged(object

6、sender, EventArgs e)List<string> keyWord = new List<string>(); / 建立關(guān)鍵字的集合 keyWord.Add(" select ");keyWord.Add("select ");keyWord.Add(" select");keyWord.Add(" where ");keyWord.Add("where ");keyWord.Add(" where");keyWord.Add("

7、; from ");keyWord.Add(" from");keyWord.Add("from ");keyWord.Add(" and ");keyWord.Add(" and");keyWord.Add("and ");keyWord.Add("or ");第 2頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)keyWord.Add(" or ");keyWord.Add(" or");keyWord.Add(" distinct

8、");keyWord.Add(" distinct");keyWord.Add("distinct ");foreach (string str in keyWord)int startindex = 0;string str_key = str;int index = -1;while (true)index = richTextBox1.Text.IndexOf(str_key, index + 1); if (index != -1)startindex = richTextBox1.Text.Length; richTextBox1.S

9、electionStart = index; richTextBox1.SelectionLength = str_key.Length; richTextBox1.SelectionColor = Color.Blue; richTextBox1.SelectionStart = startindex; richTextBox1.SelectionColor = richTextBox1.ForeColor;elsebreak;/this.richTextBox1.Focus();2、界面查詢部分代碼private void treeView2_AfterSelect_1(object se

10、nder, TreeViewEventArgs e)string selectedNodeText = treeView2.SelectedNode.Text; if (selectedNodeText = 數(shù)"據(jù)庫 ") DataSet ds = new DataSet();dataGridView2.DataSource = ds;else if (selectedNodeText = "student")DataSet ds = new DataSet();dataGridView2.DataSource = ds;第 3頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)

11、else if (selectedNodeText = "employ")DataSet ds = new DataSet();dataGridView2.DataSource = ds;else if (selectedNodeText = "stu")DataSet ds = new DataSet();ds.ReadXml("././DataSource/stu.xml");dataGridView2.DataSource = ds.Tables0;else if (selectedNodeText = "gra&qu

12、ot;)DataSet ds = new DataSet();ds.ReadXml("././DataSource/gra.xml");dataGridView2.DataSource = ds.Tables0;else if (selectedNodeText = "cou")DataSet ds = new DataSet();ds.ReadXml("././DataSource/cou.xml");dataGridView2.DataSource = ds.Tables0;else if (selectedNodeText =

13、"emp")DataSet ds = new DataSet();ds.ReadXml("././DataSource/emp.xml");dataGridView2.DataSource = ds.Tables0;else if (selectedNodeText = "day")DataSet ds = new DataSet();ds.ReadXml("././DataSource/day.xml");dataGridView2.DataSource = ds.Tables0;else if (selecte

14、dNodeText = "dep")DataSet ds = new DataSet();ds.ReadXml("././DataSource/dep.xml");dataGridView2.DataSource = ds.Tables0;3、SQL語句查詢代碼if (sql.Substring(0, 8) = "distinct")第 4頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)sql = sql.Substring(8, sql.Length - 8).Trim();if (sql.Substring(0, 1) = "*")

15、sql = sql.Substring(1, sql.Length - 1).Trim();if (sql.Substring(0, 4).ToLower() = "from")sql = sql.Substring(4, sql.Length - 4).Trim();char c = sql.ToCharArray();for (int i = 0; i < sql.Length; i+)if (ci = ' ')for (int j = 0; j < i; j+)tableName += cj;tableName = "././&q

16、uot; + "DataSource/" + tableName + ".xml" break;if (i = sql.Length - 1)for (int j = 0; j <= i; j+)tableName += cj;tableName = "././" + "DataSource/" + tableName + ".xml" break;if (sql.Contains("where")sql = sql.Substring(sql.IndexOf(&quo

17、t;where") + 5, sql.Length - 5 - sql.IndexOf("where").Trim(); tryds.ReadXml(tableName);dt = ds.Tables0.Clone();ds.Tables0.Select(sql).CopyToDataTable<DataRow>(dt, LoadOption.PreserveChanges);/dt.Rows.RemoveAt(1);for (int o = 0; o < dt.Rows.Count; o+)for (int p = o + 1; p <

18、dt.Rows.Count; p+)第 5頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)coun = 0;for (int q = 0; q < dt.Columns.Count; q+)if (dt.Rowsoq.ToString() = dt.Rowspq.ToString()coun+;if (coun = dt.Columns.Count)dt.Rowsp.Delete();dataGridView1.DataSource = dt;dataGridView1.Visible = true;textBox2.Visible = false;catch (Exception ex)textBox2.T

19、ext = "找不到當(dāng)前的表或語法錯(cuò)誤，請重新輸入！"dataGridView1.Visible = false;textBox2.Visible = true;elsetryds.ReadXml(tableName);for (int o = 0; o < ds.Tables0.Rows.Count; o+)for (int p = o + 1; p < ds.Tables0.Rows.Count; p+)coun = 0;for (int q = 0; q < ds.Tables0.Columns.Count; q+)if (ds.Tables0.Ro

20、wsoq.ToString() = ds.Tables0.Rowspq.ToString()coun+;if (coun = ds.Tables0.Columns.Count)ds.Tables0.Rowsp.Delete();dataGridView1.DataSource = ds.Tables0;第 6頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)dataGridView1.Visible = true;textBox2.Visible = false;catch (Exception ex)textBox2.Text = "找不到當(dāng)前的表或語法錯(cuò)誤，請重新輸入！ " dataGridV

21、iew1.Visible = false;textBox2.Visible = true;else if (sql.Substring(0, 1) = "*")sql = sql.Substring(1, sql.Length - 1).Trim();if (sql.Substring(0, 4).ToLower() = "from")sql = sql.Substring(4, sql.Length - 4).Trim();char c = sql.ToCharArray();for (int i = 0; i < sql.Length; i+)

22、if (ci = ' ')for (int j = 0; j < i; j+)tableName += cj;tableName = "././" + "DataSource/" + tableName + ".xml" break;if (i = sql.Length - 1)for (int j = 0; j <= i; j+)tableName += cj;tableName = "././" + "DataSource/" + tableName + &quo

23、t;.xml" break;if (sql.Contains("where")sql = sql.Substring(sql.IndexOf("where") + 5, sql.Length - 5 - sql.IndexOf("where").Trim(); tryds.ReadXml(tableName);/ dataGridView1.DataSource = ds.Tables0.Select(sql); dt = ds.Tables0.Clone();ds.Tables0.Select(sql).CopyToDat

24、aTable<DataRow>(dt, LoadOption.PreserveChanges); /dt.Rows.RemoveAt(1);dataGridView1.DataSource = dt; dataGridView1.Visible = true;textBox2.Visible = false;第 7頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)catch (Exception ex)textBox2.Text = "找不到當(dāng)前的表或語法錯(cuò)誤，請重新輸入！"dataGridView1.Visible = false;textBox2.Visible = true;el

25、setryds.ReadXml(tableName);dataGridView1.DataSource = ds.Tables0;dataGridView1.Visible = true;textBox2.Visible = false;catch (Exception ex)textBox2.Text = "找不到當(dāng)前的表或語法錯(cuò)誤，請重新輸入！"dataGridView1.Visible = false;textBox2.Visible = true;else if (sql.Contains("from")string sqlAttri = sql

26、.Substring(0, sql.IndexOf("from", 0).Trim();/ 定義屬性列的字符串List<string> li = new List<string>();while (sqlAttri != "")int co = sqlAttri.IndexOf(",", 0);int co1 = sqlAttri.Length;if (co = -1) li.Add(sqlAttri.Substring(0, co1).Trim();/ 截取出屬性列break;li.Add(sqlAttri.

27、Substring(0, co).Trim();/ 截取出屬性列sqlAttri = sqlAttri.Substring(co + 1, sqlAttri.Length - co - 1).Trim();DataTable dtnew = new DataTable();dt.Columns.Add("ChannelState ", System.Type.GetType("System.String")foreach (String s in li)/重新構(gòu)建datatabledtnew.Columns.Add(s, System.Type.GetT

28、ype("System.String");sql = sql.Substring(sql.IndexOf("from", 0) + 4, sql.Length - 4 - sql.IndexOf("from", 0).Trim(); /from 后面字符串if (sql.Contains("where")/ 具有 where 的情況第 8頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)string strtablename = sql.Substring(0, sql.IndexOf("where", 0).Trim

29、();if (strtablename.Contains(",")/多表具有 where 的情況DataTable dt1 = new DataTable();DataTable dt2 = new DataTable();DataSet ds1 = new DataSet();int cc = 0;char ch = strtablename.ToCharArray();for (int ii = 0; ii < strtablename.Length; ii+)if (chii = ',')cc+;if (cc = 1)string tablena

30、me = new string6;trytablename0 = strtablename.Substring(0, strtablename.IndexOf(",", 0).Trim();tablename1 = strtablename.Substring(strtablename.IndexOf(",", 0) + 1, strtablename.Length - strtablename.IndexOf(",", 0) - 1).Trim();sql = sql.Substring(sql.IndexOf("wher

31、e", 0) + 5, sql.Length - 5 - sql.IndexOf("where", 0).Trim(); tablename0 = "././" + "DataSource/" + tablename0 + ".xml"tablename1 = "././" + "DataSource/" + tablename1 + ".xml" tablename2 = sql.Substring(0, sql.IndexOf("a

32、nd", 0).Trim();sql = sql.Substring(sql.IndexOf("and", 0) + 3, sql.Length - 3 - sql.IndexOf("and", 0).Trim(); tablename3 = sql.Substring(0, sql.IndexOf("and", 0).Trim();sql = sql.Substring(sql.IndexOf("and", 0) + 3, sql.Length - 3 - sql.IndexOf("and&q

33、uot;, 0).Trim(); tablename4 = sql;tablename5=tablename4.Substring(tablename4.IndexOf(".",0)+1,tablename4.IndexOf("=", 0) - tablename4.IndexOf(".", 0) - 1).Trim(); / 連接字段 ds.ReadXml(tablename0);dt1 = ds.Tables0.Clone();ds.Tables0.Select(tablename2).CopyToDataTable<Dat

34、aRow>(dt1, LoadOption.PreserveChanges);ds1.ReadXml(tablename1);dt2 = ds1.Tables0.Clone();ds1.Tables0.Select(tablename3).CopyToDataTable<DataRow>(dt2, LoadOption.PreserveChanges);for (int x = 0; x < dt1.Rows.Count; x+)for (int y = 0; y < dt2.Rows.Count; y+)if (dt1.Rowsxtablename5.Equal

35、s(dt2.Rowsytablename5)第 9頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)DataRow dr = dtnew.NewRow();foreach (String str in li)if (dt1.Columns.Contains(str)drstr = dt1.Rowsxstr;if (dt2.Columns.Contains(str)drstr = dt2.Rowsystr;dtnew.Rows.Add(dr);dataGridView1.DataSource = dtnew;dataGridView1.Visible = true;textBox2.Visible = false;ca

36、tch (Exception ex)textBox2.Text = "找不到當(dāng)前的表或語法錯(cuò)誤，請重新輸入！ " dataGridView1.Visible = false;textBox2.Visible = true;if (cc = 2)string tablename = new string9;DataSet ds2 = new DataSet();DataTable dt3 = new DataTable();trytablename0 = strtablename.Substring(0, strtablename.IndexOf(",",

37、 0).Trim();strtablename = strtablename.Substring(strtablename.IndexOf(",", 0) + 1, strtablename.Length - strtablename.IndexOf(",", 0) - 1).Trim();tablename1 = strtablename.Substring(0, strtablename.IndexOf(",", 0).Trim();tablename2 = strtablename.Substring(strtablename.

38、IndexOf(",", 0) + 1, strtablename.Length - strtablename.IndexOf(",", 0) - 1).Trim();sql = sql.Substring(sql.IndexOf("where", 0) + 5, sql.Length - 5 - sql.IndexOf("where", 0).Trim(); tablename0 = "././" + "DataSource/" + tablename0 + ".

39、xml"tablename1 = "././" + "DataSource/" + tablename1 + ".xml"tablename2 = "././" + "DataSource/" + tablename2 + ".xml" / 表名存儲 tablename3 = sql.Substring(0, sql.IndexOf("and", 0).Trim();sql = sql.Substring(sql.IndexOf("an

40、d", 0) + 3, sql.Length - 3 - sql.IndexOf("and", 0).Trim();第10頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)tablename4 = sql.Substring(0, sql.IndexOf("and", 0).Trim();sql = sql.Substring(sql.IndexOf("and", 0) + 3, sql.Length - 3 - sql.IndexOf("and", 0).Trim();tablename5 = sql.Substring(0,

41、 sql.IndexOf("and", 0).Trim();tablename6 = sql.Substring(sql.IndexOf("and", 0) + 3, sql.Length - 3 - sql.IndexOf("and", 0).Trim();tablename7 = tablename5.Substring(tablename5.IndexOf(".", 0) + 1, tablename5.IndexOf("=", 0) - tablename5.IndexOf("

42、.", 0) - 1).Trim();tablename8 = tablename6.Substring(tablename6.IndexOf(".", 0) + 1, tablename6.IndexOf("=", 0) - tablename6.IndexOf(".", 0) - 1).Trim(); ds.ReadXml(tablename0);dt1 = ds.Tables0.Clone();ds.Tables0.Select(tablename3).CopyToDataTable<DataRow>(dt

43、1, LoadOption.PreserveChanges);ds1.ReadXml(tablename1);dt2 = ds1.Tables0.Clone();ds1.Tables0.Select(tablename4).CopyToDataTable<DataRow>(dt2, LoadOption.PreserveChanges);ds2.ReadXml(tablename2);dt3 = ds2.Tables0;for (int x = 0; x < dt1.Rows.Count; x+)for (int y = 0; y < dt2.Rows.Count; y

44、+)for (int z = 0; z < dt3.Rows.Count; z+)if (dt1.Rowsxtablename7.Equals(dt3.Rowsztablename7) && dt2.Rowsytablename8.Equals(dt3.Rowsztablename8)DataRow dr = dtnew.NewRow();foreach (String str in li)if (dt1.Columns.Contains(str)drstr = dt1.Rowsxstr;if (dt2.Columns.Contains(str)drstr = dt2.R

45、owsystr;if (dt3.Columns.Contains(str)drstr = dt3.Rowszstr;dtnew.Rows.Add(dr);dataGridView1.DataSource = dtnew;dataGridView1.Visible = true;textBox2.Visible = false;第11頁山東科技大學(xué)學(xué)生課程設(shè)計(jì)catch (Exception ex)textBox2.Text = "找不到當(dāng)前的表或語法錯(cuò)誤，請重新輸入！"dataGridView1.Visible = false;textBox2.Visible = true

46、;else/單表具有 where 的情況sql = sql.Substring(sql.IndexOf("where") + 5, sql.Length - 5 - sql.IndexOf("where").Trim(); tableName = "././" + "DataSource/" + strtablename + ".xml"tryds.ReadXml(tableName);dt = ds.Tables0.Clone();ds.Tables0.Select(sql).CopyToDa

47、taTable<DataRow>(dt, LoadOption.PreserveChanges); dataGridView1.DataSource = dt;for (int q = 0; q < dataGridView1.ColumnCount; q+)dataGridView1.Columnsq.Visible = false;foreach (string s in li)dataGridView1.Columnss.Visible = true;dataGridView1.Visible = true;textBox2.Visible = false;catch

48、(Exception ex)textBox2.Text = "找不到當(dāng)前的表或語法錯(cuò)誤，請重新輸入！"dataGridView1.Visible = false;textBox2.Visible = true;else/ 不具有where 的情況char c = sql.ToCharArray();for (int i = 0; i < sql.Length; i+)if (ci = ' ')for (int j = 0; j < i; j+)tableName += cj;tableName = "././"+ "DataSource/" + tableName + ".xml"/獲得表的完整路徑擴(kuò)展 wherebreak;if (i = sql.Len