文章

目前顯示的是 2013的文章

SELECT : 從資料表中擷取資料

圖片
語法 SELECT [fields list] FROM [tables list] JOIN [tables] ON [conditions] WHERE [conditions] GROUP BY [columns] HAVING [conditions] ORDER BY [columns] (1) 從product資料表中,  擷取prod_id與prod_name的資料 SELECT prod_id, prod_name FROM product; (2)  從product, customer資料表中,  擷取prod_name與cus_name的資料 SELECT product.prod_name, customer.cus_name FROM product, customer; 但是這樣子的指令會產生什麼結果呢? 這是product, customer的資料 但是以 SELECT product.prod_name, customer.cus_name FROM product, customer; 之後的資料如下圖 如果以 SELECT product.prod_name, customer.cus_name FROM customer, product; 之 的資料如下圖 上面兩個指令的差異在哪裡呢? 為什麼FROM後面的表單順序不同,會造成不同結果呢? 有沒有看出來,其實product與customer這兩個資料表根本沒有關聯。 (3) SELECT cus_name, ord_id FROM customer, order_head; 會產生什麼結果呢?  以下是order_head的資料: SELECT cus_name, ord_id FROM customer, order_head; 的結果如下: 這個結果其實也是沒有意義的,除非我們給一些條件。 看看如下的結果: 最後的SQL結果就是: 從客戶跟訂單資料表中,找出客戶編號X並且訂購日期X的客戶名稱,訂單編號。 你也可以試看看: 從客戶跟訂單資料表中,找出訂購日期X的客戶名稱,訂單編號。

實作練習 (續)

圖片
在 實作練習 中,我們建立了四個表單,各是~ 客戶資料表 (customer)、產品資料表(product)、訂單表頭資料表(order_head)、訂單表身資料表(order_body)。 現在我們來練習SQL command的SELECT用法。 SELECT [fields list] FROM [tables list] JOIN [tables] ON [conditions] WHERE [conditions] GROUP BY [columns] HAVING [conditions] ORDER BY [columns] (1) 列出客戶資料表 (customer)中的所有客戶代號、客戶名稱。 SELECT cus_no, cus_name FROM customer; (2) 找到客戶代號為c01的客戶地址。 SELECT cus_address FROM customer WHERE cus_no='c01'; (3) 找到客戶名稱中存在"科技"字串的所有客戶地址。 SELECT cus_address FROM customer WHERE cus_name like '%科技%'; (4) 找到客戶名稱中以"科技"字串結尾的所有客戶地址。 SELECT cus_address FROM customer WHERE cus_name like '%科技'; (5) 透過GROUP BY找某類型的總額~ SELECT "欄位1", SUM("欄位2") FROM "表格名" GROUP BY "欄位1" SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body GROUP BY ordb_prod_id; (6) 透過HAVING限定函數條件 SELECT "欄位1", SUM("欄位2") FROM "表格名" GROUP BY "欄位1" HAVING (函數條件) 如果被

MYSQL的time, datetime, timestamp

圖片
MYSQL的time, datetime, timestamp都是時間類型的資料型態,但是有些微的差異: 當你需要日期加上時間,則使用datetime~ 範圍由'1000-01-01 00:00:00'到'9999-12-31 23:59:59' 當你只需要時間,則使用time~ 範圍由'00:00:00'到'23:59:59' 當你只需要日期,則使用date~ 範圍由'1000-01-01'到'9999-12-31' timestamp則是日期戳章,例如你可以透過SELECT CURRENT_TIMESTAMP(); 看到現在目前的日期戳章。timestamp經常用來當作自動插入的欄位,例如你可以宣告 myautotimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 那麼這個myautotimestamp不需要給資料,就會自動把目前的日期戳章插入。 但是在timestampe中,還分成兩種format,一種如'1365647419'這樣,另一種如'2013-04-11 10:30:19'。 SELECT UNIX_TIMESTAMP('2013-04-11 10:30:19'); //得到1365647419 SELECT FROM_UNIXTIME( '1365647419' ); //得到2013-04-11 10:30:19 其他的data type如 ~ date, year,則是日期跟年份的格式,如 '2013-04-11'、'2013'。

實作練習

圖片
我們希望可以完成如下的表單   (1) 先建立新的資料庫 (紅色部分依你的需求更改) 建立資料庫,名稱為 newdbname 並且使用字元集為 utf8,排序規則是 utf8_general_ci (不分大小寫)。 參考資料: 如何使用指令知道資料庫的字元集和排序規則? MySQL CHARACTER SET 與 COLLATION create database newdbname character set utf8 collate utf8_general_ci; (2) 開始使用 newdbname 這個資料庫。 use  newdbname ; (3)  建立資料表單 customer CREATE TABLE customer ( cus_id int NOT NULL, cus_name varchar(255) NOT NULL, cus_address varchar(255), cus_no char(3), PRIMARY KEY (cus_id) ); 參考資料(Create): https://www.mysql.tw/2013/03/sql-commands-of-ddl-data-definition.html https://www.mysql.tw/2014/05/mysql-ddl-data-definition-language.html 參考資料(Data type): https://www.mysql.tw/2017/03/mysql-data-types.html https://www.mysql.tw/2013/04/mysqltime-datetime-timestamp.html 參考資料(Keys): https://www.mysql.tw/2013/03/primaryuniqueindex.html 參考資料(Other SQL): https://www.mysql.tw/2014/05/sql-select.html https://www.mysql.tw/2013/03/sqlddldmldcltcl.html (4)  建立資料表單 product CREATE TABLE product ( prod_

SQL PRIMARY KEY Constraint

圖片
你可以在建立表單時宣告主鍵(primary key),也可以使用ALTER TABLE來建立或是刪除。 CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ); 你也可以使用以下指令來建立: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID  PRIMARY KEY (P_Id) ); 如果Primary key是多欄組成: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY ( P_Id,LastName ) ); 而使用ALTER TABLE時,也可以使用如下: ALTER TABLE Persons ADD PRIMARY KEY (P_Id); 或是 ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName); 刪除primary key時,使用以下指令: ALTER TABLE Persons DROP PRIMARY KEY; 多了CONSTRAINT而寫成 CONSTRAINT pk_PersonID PRIMARY KEY ( P_Id,LastName ) ); 跟PRIMARY KEY ( P_Id,LastName ) ); 有何差別

如何使用指令知道資料庫的字元集和排序規則?

圖片
假設我們以如下指令新建一個資料庫 CREATE DATABASE mytable CHARACTER SET utf8 COLLATE utf8_general_ci ; 那麼我們就產生了一個資料庫 mytable,並且字元集是 utf8 ,以 utf8_general_ci為排序規則。 但是建立完成之後,過陣子我萬一忘記了字元集跟排序規則的設定,我可以從哪裡知道呢? 你可以使用指令知道資料庫的字元集和排序規則,如下圖。 (1) 你先使用 use mycompany; 開始使用該資料庫。 (2) 以指令 show variables like 'character_set_database' ; 把 mycompany 的字元集資料叫出來。你就看到了字元集是 utf8。 (3) 以指令 show variables like 'collation_database' ; 把 mycompany 的排序規則資料叫出來。你就看到了排序規則是 utf8_general_ci。

實體關係模型(Entity-relationship model)

圖片
實體關係模型 ( Entity-relationship model ) 由美籍華裔計算機科學家陳品山(Peter Chen)發明,是概念數據模型的高層描述所使用的數據模型或模式圖,它為表述這種實體聯繫模式圖形式的數據模型提供了圖形符號。 下圖就是一個ER Model的範例 (可點選放大): 下圖是ER Model常用的符號: 實體(Entity)以長方形表示,實體可以被(粗略地)認為是名詞,如計算機、僱員、歌曲、數學定理。 屬性(Attribute)以橢圓形表示,實體和關聯都可以有屬性,用來代表實體或是關聯外在可以描述的值,例如「國民」這個實體有「身份證字號」這個屬性,「員工」與「公司」間的「雇用」關聯,會有一個「雇用開始日期」屬性。 關聯(Relationship)以菱形表示,關聯描述了兩個或更多實體相互如何關聯。聯繫可以被(粗略地)認為是動詞,如:在公司和計算機之間的擁有關聯,在僱員和部門之間的管理關聯,在演員和歌曲之間的表演關聯,在數學家和定理之間的證明關聯。 弱實體(Weak Entity)以雙線長方形表示,弱實體是指不能獨立存在,必須依靠某個實體而存在的物件。例如訂單品項(order item)就必須跟著訂單(order)而存在,訂單品項(order item)無法獨立存在 (如下圖)。 多值屬性(Multivalued Attribute)以雙線橢圓形表示,如下圖每個老師可能會教授一個以上的課程,所以subjects就必須是多值屬性。 弱關聯(Weak Relationship)以雙線菱形表示,指不能獨立存在而必須依靠某個關聯而存在。 下圖是連接的表示方式: 例如: 在美國的制度中,每一個人都只能有一個社會安全號碼 例如: 下圖應該怎麼解釋呢? 例如: 下圖應該怎麼解釋呢? 例如: 下圖是完整的一個範例 但是也不是只有以上的描述方式,例如下圖,就是另外的表示法: 資料來源:  http://cs-exhibitions.uni-klu.ac.at/index.php?id=431 ER-Model的例子如下: (供應商)透過<供應>這個關係,跟(產品)有關係。而 (供應商)(產品)與<供應>這三個物件各有

資料庫正規化 (normalization)

圖片
資料庫正規化 (normalization)的定義: Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. 資料庫正規化就是指把關聯式資料庫的欄位與表單做規劃,讓資料重覆性與相依性能夠降到最低。當然這個"資料重覆性與相依性能夠降到最低"情況下,還必須讓資料庫可以正常運作。 重複的資料 會浪費磁碟空間,並產生維護方面的問題, 不一致的相依性 會讓資料出錯誤。 資料庫正規化有一些規則。每條規則都稱為「正規形式 Normal Form」。如果遵守第一條規則,資料庫就稱為屬於「第一正規形式」。如果遵守前三條規則,資料庫就被視為屬於「第三正規形式」。 雖然可能會有其他層級的正規形式,但第三正規形式被視為大部分應用程式所需的最高階正規形式。雖然有許多正式規則與規格,但真實情況不一定永遠完全都相同。一般而言,正規化需要其他資料表,有些客戶也會嫌麻煩。如果您決定違反正規化前三個原則中的其中一個原則,請確定您的應用程式能夠掌握所有可能發生的問題,例如重複的資料與不一致的相依性。 第一正規形式(1NF) ~刪除各個資料表中的重複群組。 ~為每一組關聯的資料建立不同的資料表。 ~使用主索引鍵識別每一組關聯的資料。 以下是違反第一正規形式的例子: table_employee (empid, empname, empaddress1, empaddress2) 類似的資料有多個欄位 empaddress1, empaddress2 上面這個例子,如果需要再輸入某個員工的第三個地址,就沒有辦法。 再如下圖的資料表,數量欄位內有多筆資料,也就是 重複群 存在數量欄位內,也是違反第一正規形式的例子。 上面這個例子,有沒有辦法知道某個顧客,在特定日期交易數量是多少? 因為有許多筆資料 無法確實知道哪個數量才是答案。 再如下圖的資料表, 缺乏唯一識別碼 ,也是違反第一正規形式的例子。 上面這個例子,Pete在Monday有多筆交易,但是無法知道上面這兩筆是重複資料? 還是兩筆不同交易?

SQL commands of DML (Data Manipulation Language)

圖片
DML (Data Manipulation Language)的SQL commands有: (1) SELECT ~ retrieve data from the a database 語法: SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition ] [GROUP BY { col_name | expr | position } [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition ] [ORDER BY { col_name | expr | position } [ASC | DESC], ...] [LIMIT {[ offset ,] row_count | row_count OFFSET offset }] [PROCEDURE procedure_name ( argument_list )] [INTO OUTFILE ' file_name ' export_options | INTO DUMPFILE ' file_name ' | INTO var_name [, var_name ]] [FOR UPDATE | LOCK IN SHARE MODE]] 更多參考:  http://dev.mysql.com/doc/refman/5.0/en/select.html 例如: SELECT * FROM mytable WHERE field1>100 ;

SQL commands of DDL (Data Definition Language)

圖片
DDL (Data Definition Language) 的SQL commands有: (1)CREATE - to create objects in the database  建立資料庫(database) 語法: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name 更多參考:  http://dev.mysql.com/doc/refman/5.0/en/create-database.html 例如: CREATE DATABASE mydatabase; 或是 CREATE DATABASE mydatabase CHARACTER SET big5 COLLATE big5_chinese_ci ; 或是 CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_chinese_ci ; 關於character set與collate,請參考 http://www.mysql.tw/2013/03/mysql-character-set-collation.html 建立表單(table) 語法: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] 使用temporary的意思是指建立暫時的表單,單connection結束自動刪除。 更多參考:  http://dev.mysql.com/doc/refman/5.0/en/create-table.html 例如: CREATE TABLE mytable (field1 CHAR(10), field2 INT(10)) ; (2) ALTER - alters the structure of the d

Primary、Unique、Index各代表什麼意義?

圖片
在資料表的欄位設定中,你可以設定~ Primary Key = 主鍵、Unique Key = 不重覆鍵、Index Key = 索引鍵,這三個代表的意義是什麼呢? 當你設定一個欄位為Primary Key,也代表這個欄位是not null,並且unique。 如果Primary Key為多欄位組合,例如(id1+id2),這個(id1+id2)也必須是not null & unique。 Primary Key 如下圖,原本可以null的欄位設定成Primary Key 之後,就變成no null,並且Key變成PRI。 其index結構如下圖: 但是如果我們把Primary Key取消,如下圖,PRI不見了,但是仍然是no null。 取消Primary Key之後的index變怎樣呢? 如下圖,index變空的。 要恢復原本的允許null,就必須再下指令,如下圖: Unique Key 當你設定一個欄位為Unique,代表這個欄位不能重覆,但是可以null,如下圖: index變怎樣呢? 如下圖,又有了index結構,表示設成unique,跟設索引是一樣的。 如果我們把Unique取消,如下圖,UNI就不見了。 把Unique取消之後,其index結構也不見了,如下圖: Index Key 當你設定一個欄位為Index,如下圖,顯示成為MUL。 設了index之後,其index結構如下圖: 如果我們把Index取消,如下圖,MUL就不見了,當然index結構也就不見了。 結論 Primary Key比較沒有爭議,它一定是非null且unique,並且一個table只能有一個Primary Key。 當你想要建立兩個Primary Key,就會出現錯誤,如下圖: Unique允許null這件事,其實有點奇怪,既然是unique(不能重覆),當然不能兩個null同時存在,因此雖然unique允許null,但是其實也可以把unique的欄位設成not null,比較不會看得很彆扭。 而UNI跟MUL有何差別呢? 當我們設定unique與index後,都還是可以允許null,但是一個變成UNI