建立資料庫(Create Table) １、建資料表： create table table_name(column1_name data_type [DEFAULT data_value] [null | not null [,...] ） 說明：table_type 資料表這資料表包含一個或多個指定資料型態的欄位 DEFAULT 指定欄位初始值data_value null表示是否允許資料的值為null當指定欄位not null時,資料庫系統將 拒絕新增 null 資料至該欄位 例 CREATE TABLE customer(First_Name char(50),Last_Name char(50), Address char(50),City char(50),Country char(25),Birth_Date date) ２、更改資料表 alter table table_name add column column_name datatype 說明：增加一個欄位（沒有刪除某個欄位的語法。) lter table table_name add primary key (column_name) 說明：更改表得的定義把某個欄位設為主鍵。 alter table table_name drop primary key (column_name) 說明：把主鍵的定義刪除。 ３、建立索引 create UNIQUE index empno_idx on table_name(emp_no) 說明：對某個表格的欄位建立索引以增加查詢時的速度。 ４、刪除 drop Table table_name //刪除資料表 drop Table temp //刪除temp資料表 drop Index index_name //刪除索引
資料操作語言 DQL（Data Manipulation Language)
常用、主要的SQL指令： ●Select(選取資料，屬DQL) 資料查詢 SELCET * FROM Table //過濾顯示所有資料庫 SELCET Name, Tel FROM Table //過濾顯示 Name Tel 資料 SELCET DISTINCT dept_no FROM table_name //DISTINCT 取消重復部份 SELCET DISTINCT au_lname FROM authors WHERE au_lname="Ringer" SELCET full_name, salsry*12 FROM table_name //全名及計算年薪 SELCET full_name, salsry*12 AS year_salary FROM table_name //在ISO 的SQL 語法標準中允許使用AS子句重新命名欄位名稱 SELCET * FROM table_name WHERE column1 = xxx [and column2 > yyy] [or column3 <> zzz] ------------------------------------------------------------------ 整合性的查詢： SELCET count (*) FROM table_name WHERE column_name = xxx //查詢符合條件的資料共有幾筆。 SELCET SUM(column1) FROM table_name 說明： 1.計算出總和，所選的欄位必須是可數的數字形態。 2.除此以外還有 avg() 是計算平均、max()、min()計算最大最小值的 整合性查詢。 ------------------------------------------------------------------ 組合查詢 組合查詢是指所查詢得資料來源並不隻有單一的表格，而是聯合一個以上 的表格纔能夠得到結果的。 SELCET * FROM table1,table2 WHERE table1.colum1=table2.column1 說明： 1.查詢兩個表格中其中 column1 值相同的資料。 2.當然兩個表格相互比較的欄位，其資料形態必須相同。 3.一個復雜的查詢其動用到的表格可能會很多個。 ------------------------------------------------------------------ 復合性的查詢 SELCET * FROM table_name1 WHERE exists ( SELCET * FROM table_name2 WHERE conditions ) 說明：1.where 的 conditions 可以是另外一個的 query。 2.exists 在此是指存在與否。 SELCET * FROM table_name1 WHERE column1 IN ( SELCET column1 FROM table_name2 WHERE conditions ) 說明：1. in 後面接的是一個集合，表示column1 存在集合裡面。 2. select 出來的資料形態必須符合 column1。 ------------------------------------------------------------------ 其他查詢 SELCET * FROM table_name1 WHERE column1 LIKE 'x%' //說明：like 必須和後面的'x%' 相呼應表示以 x為開頭的字串。 SELCET * FROM table_name1 WHERE column1 IN ('xxx','yyy',..) //說明：in 後面接的是一個集合，表示column1 存在集合裡面。 SELCET * FROM table_name1 WHERE column1 BETWEEN xx AND yy //說明：between 表示 column1 的值介於 xx 和 yy 之間。 SELCET * FROM 程式基本資料 WHERE 陽上姓名 LIKE "% 楊 %"; //過濾不特定"徐"的資料 SELCET * FROM Table WHERE Name LIKE "*徐*" //過濾特定"徐"之中的資料 SELCET full_name,hire_dat,phone_exit FROM table_name WHERE lase_name LIKE 'Le#%' ESCAPE '#'; //ESCAPE 比對符號要檢查lase_name是否內含'Le#% 的字串 SELCET full_name,hire_dat,phone_exit FROM table_name WHERE lase_name LIKE 'L%'; //lase_name LIKE 'L%' 代表第一個字母須以L字母為開頭其餘可任意字串
//lase_name NOT LIKE 'L%' 代表第一個字母不得為L字母 //lase_name LIKE 'L%' 代表第一個字母須以L字母為開頭其餘可任意字串 //lase_name LIKE 'L__' 姓氏須三個字元第一個字元為 L (底線字元'_'類似DOS下的'?'字元) //lase_name LIKE '%ee%'代表任何內含'ee'字串 //lase_name LIKE '%e'最後一個字元須為'e' ------------------------------------------------------------------ 資料篩選 資7料單一搜尋 SELCET full_name, hire_date,phone_exit FROM table_name WHERE dept_no=600 //顯示部門代號等於600的所有員工.... SELCET full_name,hire_dat,phone_exit FROM table_name WHERE phone_exit IS NULL //列出所有沒有分機號碼的員工姓名(雇員) SELCET full_name,hire_dat,phone_exit FROM table_name WHERE phone_exit IS NOT NULL //列出所有有分機號碼的員工姓名<非NULL值> ------------------------------------------------------------------ 資料多重搜尋 SELCET full_name,phone_exit FROM table_name WHERE phone_exit IS NULL AND hire_date > '20-jan-1992'; //未有分機且進入公司(雇員)日期之後員工 <多資料表連結> SELCET full_name, job_country, currency FROM table_name, table_name1 WHERE job_country = table_name1 SELCET full_name, job_country, currency FROM table_name, LEFT JOIN country ON job_country = table_name1 ------------------------------------------------------------------ 搜尋資料範圍 SELCET full_name,salary FROM table_name WHERE salary BETWEEN 100000 AND 200000; //列出薪資在100000至200000的員工 SELCET full_name,salary FROM table_name WHERE salary >= 100000 AND salary <= 200000; //比較運算的查詢語言 SELCET full_name,job_country FROM table_name//集合成員運算(IN子句) WHERE job_country IN('Italy France')//義大利及法國員工 ------------------------------------------------------------------ 資料排序結果 單一排序資料 SELCET full_name,salary,dept_no FROM table_name ORDER BY dept_no //依各部門代號順序列出員工姓名及薪資 SELCET column1,column2 FROM table_name order by column2 [desc] //說明：order by 是指定以某個欄位做排序，[desc]是指從大到小排列， 若沒有指明，則是從小到大排列 SELCET * FROM Table Order By Age Desc; //以年齡欄反排序 多欄排序資料 SELCET full_name,salary,dept_no FROM table_name ORDER BY dept_no, salary DESC; //部門代號由小而大,薪資由大而小輸出所有員工姓名 ------------------------------------------------------------------ SQL合計函數(aggregate function) AVG :平均值 COUNT:筆數 MIN :最小值 MAX :最大值 SUM :加總值 SELECT dept_no COUNT(salary)FROM table_name //錯誤的 SELECT dept_no MAX(salary)FROM table_name //錯誤的 SELECT dept_no MAX(salary)FROM table_name GROUP BY dept_no//正確的 < COUNT 的應用 > SELECT COUNT(*)FROM table_name WHERE dept_no = 100 //合計代號100的部門中有幾位員工 < COUNT(DISTINCT) 的應用 > SELECT COUNT(DISTINCT dept_no) FROM table_name //公司共有幾個部門 < COUNT及SUM 的應用 > SELECT COUNT(*),SUM(salsry)FROM table_name WHERE dept_no = 100 //部門代號為100的員工人數及薪資總數 < MIN,MAX,AVG 的應用 > SELECT MIN(salsry),MAX(salsry),AVG(salsry)FROM table_name WHERE dept_no = 100 //部門代號為100的員工的最低薪資和最高薪資及平均薪資 < GROUP BY子句應用 > SELECT COUNT(*),MIN(salsry),MAX(salsry),SUM(salsry) FROM table_name GROUP BY dept_no //找出所有部門的人數最低薪資 最高薪資 薪資總數 < HAVING子句應用 > SELECT COUNT(*),MIN(salsry),MAX(salsry),SUM(salsry) FROM table_name GROUP BY dept_no HAVING COUNT(dept_no)>2 //找出所有部門的人數大於2個人的最低薪資 最高薪資 薪資總數 ------------------------------------------------------------------ ●Insert(新增資料，屬DML) INSERT INTO 資料表名稱 Values (欄位1, 欄位2,...); INSERT INTO table_name VALUES('Taiwan','NTD'); INSERT INTO table_name(country,currency)VALUES('Taiwan','NTD'); //table_name中有country,currency 兩個欄位 INSERT INTO table_name[(column_list)] SELECT column_list FROM another_table_name...... //複製多筆資料至另一資料表 INSERT INTO table_name1 SELECT * FROM table_name //table_name 資料表所有資料加入table_name1中 INSERT INTO Table Values ("陳建中","037-271135","苗市中路","40"); INSERT INTO table_name(column1,column2,...)values(value1,value2,...) 說明：1.若沒有指定column 繫統則會按表格內的欄位順序填入資料。 2.欄位的資料形態和所填入的資料必須吻合。 3.table_name 也可以是景觀 view_name。 INSERT INTO table_name (column1,column2,...) select columnx,columny,... from another_table 說明：也可以經過一個子查詢（subquery）把別的表格的資料填入。
●Update(更新資料，屬DML) < 允許更新己存在的資料表資料> UPDATE table_name SET coumn_name1=data_value1[,coumn_name2 =data_value2,.........] WHERE search_condition] //table_name 須為資料表或可更新的view名稱 //SET 欲更新欄位名稱 //WHERE 子句用指定更條件(可省略)WHERE 子句中search_condition 條件符合會更新資料;更新的欄位值須以欄位所定義的資料型庇相容 < 更新所有資料> UPDATE table_name SET salary =salary*1.05; //員工薪資依物價上漲年增率5%調整 < 更新指定資料> UPDATE table_name SET salary =salary*1.1 WHERE dept_no=100; //所有部門代號100的員工薪資調升 1.1 < 更新多個欄位> UPDATE table_name SET job_grade=1 =salary= 11000 WHERE emp_no=2; //編號2號員工職級2級晉升為1級 ,薪資並調升110000 UPDATE table_name SET column1='xxx' WHERE conditoins 說明： 1.更改某個欄位設定其值為'xxx'。 2.conditions 是所要符合的條件、若沒有 where 則整個 table 的那個 欄位都會全部被更改。 ●Delete(刪除資料，屬DML) < 刪除指定的資料> DELETE FROM table_name WHERE dept_no=621; //部門代號621被裁撤 < 刪除所有的資料> DELETE FROM table_name DELETE * FROM table_name//錯誤的 DELETE FROM table_name WHERE conditions 說明：刪除符合條件的資料。 說明：關於where條件後面如果包含有日期的比較，不同數據庫有不同的 表達式。具體如下： (1)如果是access數據庫，則為：where mydate>#2000-01-01# (2)如果是oracle數據庫，則為：where mydate>cast('2000-01-01' as date) 或：where mydate>to_date('2000-01-01','yyyy-mm-dd') 在delphi中寫成： thedate='2000-01-01'; query1.sql.add('select * from abc where mydate>cast('+''+thedate+''+' as date)'); 如果比較日期時間型，則為： query1.sql.add('select * from abc where mydatetime>to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss')
