SQLite on Ubuntu

SQLite เป็นโปรแกรมฐานข้อมูลที่มีขนาดเล็กมาก (ไม่ถึง 1MB)  เก็บฐานข้อมูลเป็นไฟล์โดยไม่จำเป็นต้องมีเซิร์ฟเวอร์ ทำให้ถูกใช้ในหลายๆ โปรแกรม

ในบทความนี้ขอแสดงวิธีใช้ SQLite แบบรันเป็นคำสั่ง (command line) คือรันคำสั่ง sqlite3 เพื่อเข้าใช้งาน หลายๆ OS เช่น Linux, MacOS จะติดตั้ง SQLite มาเป็นดีฟอลต์เลย เราสามารถรันคำสั่ง sqlite3 เพื่อใช้งานได้เลย
 

[unknow@cent6-dev ~]$ sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

คำสั่งใน sqlite (ที่ไม่ใช่ SQL) จะขึ้นต้นด้วยเครื่องหมายจุด “.” หากต้องการดูคำสั่ง และวิธีการใช้ ให้พิมพ์คำสั่ง .help

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
... (คำสั่งอื่นๆ)

ลองสร้างตารางแล้วลองใส่ข้อมูล ด้วย SQL

sqlite> CREATE TABLE books(

           id_book INTEGER PRIMARI KEY, 

           name_book TEXT NOT NULL, 

           author TEXT NOT NULL,

           isbn INTEGER NOT NULL UNIQUE

        ); 

sqlite> CREATE TABLE authors( 

          name_author TEXT PRIMARI KEY,

          email TEXT NOT NULL,

          phone INTEGER NOT NULL UNIQUE 

        ); 

sqlite> CREATE TABLE publishers(

          name_publisher TEXT PRIMARI KEY,

          email TEXT NOT NULL, 

          phone INTEGER NOT NULL UNIQUE

          ); 

sqlite> INSERT INTO books VALUES (name_book, author, isbn);
         VALUES 

         ('Database', 'Ben', 12345789);

sqlite> SELECT * FROM books ;
Database|Ben|12345789
sqlite> 

ถ้ารันคำสั่ง sqlite3 อย่างเดียวโดยไม่ได้ระบุไฟล์ฐานข้อมูลแบบนี้ หลังจากสร้าง table ใส่ข้อมูลแล้ว ฐานข้อมูลที่สร้างจะถูกเก็บไว้ในหน่วยความจำชั่วคราว ถ้าพิมพ์คำสั่ง .exit ออกจาก sqlite เลย ข้อมูล table ที่สร้างนี้จะหายไป เราต้อง
บันทึกฐานข้อมูลลงเป็นไฟล์ด้วยคำสั่ง .backup
 

sqlite> .backup work1.db 

 พิมพ์คำสั่ง .exit ออกจาก sqlite

sqlite> .exit
[unknow@cent6-dev ~]$

 
ลอง ls ดู จะมีไฟล์ test1.db ถูกสร้างขึ้นมา

[unknow@cent6-dev ~]$ ls -l work1.db
-rw-r--r--. 1 unknow users 2048 Feb 9 15:22 work1.db

SQL Tutorial

What is SQL?

      SQL หรือ Structured Query Language  เป็นภาษาในการเข้าถึง ฐานข้อมูล เราสามารถใช้งานภาษา SQL ในการทำการดึงข้อมูล จากฐานข้อมูล และมันเป็นมาตรฐานกลางที่ใช้ในระบบฐานข้อมูลต่างๆ โดยเป็นมาตรฐานของ ANSI (American National Standard Institute)

What Can SQL do?

• ใช้ในการค้นข้อมูลในฐานข้อมูล
• ใช้ใส่ข้อมูลเพิ่มไปยังฐานข้อมูล
• ใช้แก้ไขข้อมูลในฐานข้อมูล
• ใช้ลบข้อมูลที่เราไม่ต้องการออกจากฐานข้อมูล
• ใช้สร้างฐานข้อมูลขึ้นมาใหม่
• ใช้สร้างตาราง ในฐานข้อมูล
• และอื่นๆ 

RDBMS

• RDBMS ย่อมาจาก Relational Database Management System 
• RDBMS เป็นพื้นฐานสำหรับ SQL และสำหรับระบบฐานข้อมูลที่ทันสมัยเช่น MS SQL Server, IBM DB2, Oracle, MySQL และ Microsoft Access 
• ข้อมูลใน RDBMS ถูกเก็บไว้ในวัตถุฐานข้อมูลที่เรียกว่าตาราง ตารางคือชุดของรายการข้อมูลที่เกี่ยวข้องและประกอบด้วยคอลัมน์และแถว

 

Database Tables

     Table เป็นโครงสร้างพื้นฐานที่จะใช้ในการเก็บข้อมูล ประกอบด้วย Field ต่างๆ ที่จะกำหนดว่าจะเก็บเรื่องราวอะไรบ้าง และข้อมูลต่างๆ ที่บันทึกเข้ามาใน Table จะเรียกว่า Record
ถือเป็นโครงสร้างที่สำคัญมากๆ ในฐานข้อมูล (Database) จากรูปจะเห็นได้ว่า Table นี้ประกอบด้วย ShipperID, ShipperName, Phone

 -------------------------------   END SQL Theory  --------------------------------

 

The SQL SELECT Statement

 

      การดึงข้อมูลที่อยู่ในตาราง หรือ table กลับออกมาเพื่อใช้ตามเงือนไขที่เราระบุกัน โดยคำสั่งที่ใช้คือ SQL Select statement ซึ่งผลลัพธ์ที่ได้จะมีลักษณะเป็นข้อมูลตาราง หรือ table ที่ประกอบด้วย column และ row

SELECT Syntax 

SELECT * FROM table_name;

      เมื่อเราใส่คำสั่งนี้ลงไปจะได้ผลลัพธ์คือ จะดึงข้อมูลทุก column ของ table นั้นๆ ในตัวอย่างเราจะดึงข้อมูลทุก column ของ table customer  ก็จะใช้คำสั่ง SELECT * FROM Customers; จะได้ผลลัพธ์ดังกล่าว

 

 

SELECT Syntax

 

SELECT column1, column2, ... FROM table_name;

ถ้าเราอยากจะเห็น "เฉพาะบาง column" ของ database นั้นๆให้ใช้คำสั่งด้านบนผลลัพธ์คือ จะดึงข้อมูล"เฉพาะบาง column" ของ table นั้นๆ ในตัวอย่างเราจะดึงข้อมูล column CustomerName ของ table customer  ก็จะใช้คำสั่ง SELECT CustomerName FROM Customers; จะได้ผลลัพธ์ดังกล่าว

SELECT DISTINCT Syntax

 SELECT DISTINCT column1, column2, ... FROM table_name;

      ในบางครั้งข้อมูล column ที่เรานำมาแสดงผล อาจจะมีข้อมูลซ้ำกันบ้าง แต่ถ้าเราต้องการแสดงผลข้อมูลทุก column โดยที่แสดงผลแบบข้อมูลไม่ซ้ำกัน หรือ unique ข้อมูล
จะทำได้ด้วย keyword DISTINCT ซึ่งระบบจะทำการเอาข้อมูลที่ซ้ำออกไปให้ เช่น

 SELECT  Country FROM Customers;

ผลลัพธ์



 จะเห็นได้ว่าส่วนของ Country นั้นมีข้อมูลซ้ำกันนั้นคือ Germany และ Mexico แต่ถ้าเราใช้ คำสั่ง

SELECT DISTINCT Country  FROM Customers;

ผลลัพธ์



 จะเห็นได้ว่าข้อมูลไม่มีส่วนที่ซ้ำกัน และยังมีการจัดเรียงข้อมูลตามตัวอักษรด้วย

SELECT COUNT DISTINCT Syntax

SELECT COUNT(DISTINCT Country) FROM Customers;

      ถ้าเราต้องการนับข้อมูลเฉพาะบาง column โดยที่แสดงผลแบบข้อมูลไม่ซ้ำกันก็สามารถทำได้ด้วยคำสั่งด้านบน

ผลลัพธ์

 

 -------------------------------   END SQL Select statement   --------------------------------

 

The SQL WHERE Clause

 

      วิธีการสร้างเงือนไข ในกรณีที่ข้อมูลใน table มีจำนวนมาก และเราต้องการข้อมูลเฉพาะแถวที่เราต้องการ เราจะเพิ่มส่วนของ SQL Where Clause statement เข้ามาช่วยในการทำ select ข้อมูลจาก table

WHERE Syntax

 

SELECT column1, column2, ... FROM table_name WHERE condition; 

      เราสามารถนำเอา Where condition เพิ่มต่อเข้าไปที่ SQL Select statement ได้เลย เพื่อให้ข้อมูลที่แสดงถูกกรองให้เหลือเฉพาะข้อมูลที่อยู่ในเงื่อนไขที่ต้องการ เช่น ถ้าเราต้องการเฉพาะข้อมูลของ Customers ที่ Country Mexico  คำสั่งคือ 

SELECT * FROM Customers
WHERE Country='Mexico';

ผลลัพธ์


 

 

Text fields VS Numeric fields 

      บางครั้งเราอาจจะมีเงื่อนไขที่ต้องค้นหาจากตัวเลขเราไม่จำเป็นที่จะต้องใส่เครื่องหมาย quotes (' ') เช่น เราต้องการหา customers คนที่ 1 

SELECT * FROM Customers WHERE CustomerID=1;

ผลลัพธ์

 

Operators in The WHERE Clause

      ในส่วนของ condition เราสามารถนำเอา SQL Operator เช่น =, >, <, LIKE, IN มาสร้างเป็น SQL Expression เพื่อเปรียบเทียบข้อมูลใน table และ ข้อมูลที่เราต้องการได้ เช่น  เราต้องการดึงข้อมูล ProductID, ProductName, SupplierID, CategoryID, Unit, Price จาก table Products ที่มีเงื่อนไขว่า Price ต้องมากกว่า 20 ขึ้นไป

SELECT * FROM Products WHERE Price > 20 ;

ผลลัพธ์

-------------------------------   END SQL Where statement   --------------------------------

 

 

 

SQL INSERT INTO Statement

      

           คือการใส่ข้อมูลลงในตาราง ด้วย data type แต่ละ column ที่ต้องกำหนดให้ถูกต้อง ข้อมูลที่เพิ่มลงจะไป จะอยู่ในรูปแบบทีละแถวข้อมูล หรือ row นั่นเอง เราจะเรียกคำสั่งเพิ่มข้อมูลนี้ว่า SQL Insert into statement

INSERT Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

      การทดลองใช้คำสั่ง INSERT

เริ่มแรกข้อมูลใน Database ของเราจะมีดังนี้

จากนั้นเราจะใช้คำสั่ง 

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

ซึ่งเราจะได้ผลลัพธ์ดังภาพ คือมีข้อมูลใหม่เข้าไปใน Database

SQL UPDATE INTO Statement

           ตอนนี้เรามีข้อมูลอยู่ในตาราง หรือ table เราแล้ว ทีนี้ถ้าเราต้องการปรับเปลี่ยนค่าบางอย่าง หรือ บาง column ที่มีอยู่ปัจจุบัน เราจะใช้คำสั่ง SQL Update query statement ในการแก้ไขข้อมูลที่ต้องการ

 โดยที่เราสามารถนำเอา Where Clause statement มากำหนดกลุ่มของข้อมูลที่เราต้องการได้ด้วย

UPDATE Syntax

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

 การทดลองใช้คำสั่ง INSERT

เริ่มแรกข้อมูลใน Database ของเราจะมีดังนี้

จากนั้นเราจะใช้คำสั่ง 

UPDATE Customers

SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'

WHERE CustomerID = 1;

ซึ่งเราจะได้ผลลัพธ์ดังภาพ คือมีการแก้ไขข้อมูลที่ CustomerID 1

SQL DELETE Statement

           ตอนนี้เรามาดูวิธีการลบข้อมูลออกจาก table ด้วยคำสั่ง SQL Delete query statement โดยสามารถใช้คำสั่งร่วมกัน Where Clause ในการกำหนดกลุ่มของข้อมูลที่ต้องการจะลบออกจาก table

DELETE Syntax

DELETE FROM table_name WHERE condition;

การทดลองใช้คำสั่ง DELETE

เริ่มแรกข้อมูลใน Database ของเราจะมีดังนี้

จากนั้นเราจะใช้คำสั่ง 

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

ซึ่งเราจะได้ผลลัพธ์ดังภาพ คือมีการลบข้อมูล CustomerName : Alfreds 

 -------------------------------   END SQL TABLE statement   --------------------------------

SQL JOIN Statement

      

           ในบางครั้งการเก็บข้อมูลบน database อาจจะมีการกระจายของข้อมูลอยู่ในหลายๆ table ขึ้น ในบางครั้งข้อมูลใน table อาจจะมีความสัมพันธ์ที่ในบาง column ที่ข้อมูลเหมือนกัน และทาง user เองอาจต้องการเชื่อมข้อมูลของ 2 table นั้นเข้าด้วยกัน ซึ่งสามารถทำได้ด้วยคำสั่ง SQL Join statement

JOIN  Syntax

SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.common_field = table2.common_field;

      การทดลองใช้คำสั่ง JOIN


เริ่มแรกข้อมูลใน Database ของเราจะมีดังนี้

โดยมี 2 Database คือ 

1. Order

2. Customer

จากนั้นเราจะใช้คำสั่ง 

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

ซึ่งเราจะได้ผลลัพธ์ดังภาพ คือมีการ JOIN ข้อมูลการระหว่าง 2 Table

Conclude  09/02/2019

related info/link/reference   

• www.w3schools.com/sql 

 

any problem/solution 

• SELECT COUNT(DISTINCT colume1) FROM name_table;  ไม่สามารถใช้ได้กับ Browser Firefox จึงต้องเปลี่ยนไปใช้ Google Chrome แทน เพราะ FireFox ไม่ supported Microsoft Access databases.