บทเรียน SQL
SQL หรือ Structured Query Language เป็นภาษาโปรแกรมที่ออกแบบมาเพื่อช่วยคุณจัดการข้อมูลที่เก็บไว้ในระบบจัดการฐานข้อมูลเชิงสัมพันธ์ (RDBMS)
บทเรียน SQL
SQL หรือ Structured Query Language เป็นภาษาโปรแกรมที่ออกแบบมาเพื่อช่วยคุณจัดการข้อมูลที่เก็บไว้ในระบบจัดการฐานข้อมูลเชิงสัมพันธ์ (RDBMS)
บทช่วยสอน SQL
SQL หรือ Structured Query Language เป็นภาษาโปรแกรมที่ออกแบบมาเพื่อช่วยคุณจัดการข้อมูลที่เก็บไว้ใน...
ทำความเข้าใจเกี่ยวกับ SQL
คุณอาจสังเกตเห็นว่าระบบการจัดการฐานข้อมูลจำนวนมากมีตัวย่อว่า SQL...
กรณีคำชี้แจง
ดูตัวอย่างที่สาธิตการใช้คำสั่ง 'Case'
การจัดการข้อความ / การเข้าถึงอักระ
SQL สามารถใช้เพื่อจัดการข้อความหรือที่เรียกว่าสตริง ต่อไปนี้เป็นรายการฟังก์ชันที่สามารถ...
เลือกใบแจ้งยอด
Select Statement ใช้เพื่อแยกหรือสืบค้นข้อมูลจากฐานข้อมูลและสามารถแยกย่อยได้ดังต่อไปนี้...
แบบสอบถามภายใน
SQL เป็นภาษาที่ยืดหยุ่นมากซึ่งช่วยให้คุณแยกข้อมูลได้หลายวิธี
มาตราไหน
โดยที่ส่วนคำสั่งเป็นส่วนหนึ่งของคำสั่ง select ที่ใช้ในการกรองชุดผลลัพธ์
เข้าร่วมตาราง
โดยทั่วไปข้อมูลในฐานข้อมูลจะมีโครงสร้างในลักษณะที่ป้องกันข้อมูลซ้ำซ้อน
การรวมกัน
คุณสามารถใช้สหภาพเพื่อเพิ่มค่าคงที่ลงในชุดผลลัพธ์ หรือเพื่อรวมตารางต่างๆ ที่ไม่มีการเชื่อมโยง...
ฟังก์ชันรวม
ฟังก์ชันรวมดำเนินการกับกลุ่มของเรคคอร์ด แทนที่จะดำเนินการกับเรคคอร์ดหรือตัวแปรแต่ละรายการ
แบบสอบถามที่ซ้อนกัน
คุณสามารถป้องกันการทำซ้ำของแบบสอบถามย่อยที่มีความยาวได้โดยใช้การซ้อนเพื่อเลือกค่าในระดับหนึ่ง จากนั้น...
การคำนวณตัวเลข
SQL สามารถใช้เป็นเครื่องคำนวณตัวเลขได้
การคำนวณวันที่
SQL สามารถใช้เป็นเครื่องคำนวณวันที่ได้
NULL Values
เมื่อฟิลด์ว่างเปล่า จะมีค่า NULL - ไม่มีค่าใดๆ นี่เป็นสิ่งสำคัญมากที่ต้องทำความเข้าใจเมื่อ...
User-Defined Fields
ค่าฟิลด์ที่ผู้ใช้กำหนดทั้งหมดจะถูกจัดเก็บไว้ในตารางเดียวใน QuickEasy ซึ่งก็คือ FIELDVALUES แต่ละบันทึก...
การลบเพจที่หมดอายุและเพจที่ถูกละเลยในฐานข้อมูล
นี่คือสคริปต์ที่คุณต้อง...
ตัวอย่าง SQL
คลิกที่นี่เพื่อดูตัวอย่าง...
บทช่วยสอน SQL
SQL หรือ Structured Query Language เป็นภาษาโปรแกรมที่ออกแบบมาเพื่อช่วยคุณจัดการข้อมูลที่เก็บไว้ในระบบจัดการฐานข้อมูลเชิงสัมพันธ์ (RDBMS) SQL กลายเป็นมาตรฐานของ American National Standards Institute (ANSI) ในปี 1986 และขององค์การระหว่างประเทศเพื่อการมาตรฐาน (ISO) ในปี 1987 ตั้งแต่นั้นเป็นต้นมา มาตรฐานได้รับการแก้ไขให้รวมชุดคุณลักษณะที่ใหญ่ขึ้น แม้ว่าจะมีมาตรฐานดังกล่าวอยู่ แต่โค้ด SQL ส่วนใหญ่ไม่สามารถพกพาได้อย่างสมบูรณ์ระหว่างระบบฐานข้อมูลที่แตกต่างกัน
QuickEasy ใช้ฐานข้อมูลเชิงสัมพันธ์ของ Firebird ดังนั้น คู่มือนี้จะเป็นไปตามมาตรฐานที่ใช้งานได้ใน Firebird และจะมุ่งเน้นไปที่ Data Manipulation Language (DML) ซึ่งเป็นขอบเขตของภาษา SQL ที่ใช้ในการแทรก อัปเดต ลบ และสืบค้นฐานข้อมูล
สำหรับการศึกษาขั้นสูงเพิ่มเติม โปรดไปที่ลิงก์นี้ไปยังคู่มืออ้างอิง Firebird ออนไลน์
FlameRobin เป็นเครื่องมือบริหารจัดการข้อมูลฟรีที่สามารถใช้เพื่อเข้าถึงฐานข้อมูล Firebird ได้โดยตรงโดยใช้ SQL
วิธีการเชื่อมโยงตาราง
ฐานข้อมูลคือชุดของตารางและแต่ละตารางประกอบด้วยข้อมูลเฉพาะ เช่น. คุณอาจมีตารางสำหรับลูกค้า ซัพพลายเออร์ ส่วนหัวของธุรกรรม รายการ ฯลฯ
ตารางในฐานข้อมูลเชื่อมโยงกันโดยใช้คีย์หลักและคีย์ต่างประเทศ โดยส่วนใหญ่แล้วความสัมพันธ์แบบหนึ่งต่อกลุ่มจะอยู่ระหว่างตารางที่เชื่อมโยง นอกจากนี้ยังเรียกอีกอย่างว่าความสัมพันธ์หลัก-รายละเอียด หรือความสัมพันธ์ระหว่างผู้ปกครอง-รองในฐานข้อมูล ความสัมพันธ์ประเภทนี้ช่วยให้แน่ใจว่าความสมบูรณ์ของข้อมูลยังคงอยู่ โดยที่บันทึกหลัก (หรือหลัก) ควบคุมการมีอยู่ของบันทึกรายละเอียด (หรือรายการย่อย) ในกรณีนี้ เขตข้อมูล Foreign Key ในตารางลูก/รายละเอียดจะเชื่อมโยงไปยังเขตข้อมูลคีย์หลักในตารางหลัก/หลัก ด้านล่างนี้คือคำอธิบายว่าคีย์หลักและคีย์ต่างประเทศทำงานอย่างไร
- คีย์หลัก - ทุกตารางต้องมีอย่างน้อยหนึ่งฟิลด์ที่ระบุแต่ละระเบียนในตารางโดยไม่ซ้ำกัน สิ่งนี้เรียกว่าคีย์หลัก (PK) และใน QuickEasy ฟิลด์จำนวนเต็มจะถูกใช้ทำหน้าที่เป็นคีย์หลักในแต่ละตาราง
- Foreign Keys - Foreign Keys ใช้ในตาราง Child/Detail เพื่อลิงก์ไปยัง Primary Key ในตาราง Parent/Master และจะต้องมีประเภทข้อมูลเดียวกันกับ Primary Keys ที่เชื่อมโยงกัน
ความสัมพันธ์ของคีย์หลัก/คีย์ต่างประเทศจะใช้เฉพาะในกรณีที่จำเป็นต้องมีความสัมพันธ์เท่านั้น ตัวอย่างเช่น รายการธุรกรรมสามารถมีอยู่ได้ก็ต่อเมื่อมีการเชื่อมโยงกับส่วนหัวของธุรกรรม และหากส่วนหัวของธุรกรรมถูกลบ รายการในธุรกรรมจะต้องถูกลบด้วย
ประเภทข้อมูล
ประเภทข้อมูลที่แตกต่างกันใช้ในการจัดเก็บข้อมูลประเภทต่างๆ เช่น ข้อความ ตัวเลข วันที่ รูปภาพ ฯลฯ แต่ละฟิลด์ในตารางจึงเชื่อมโยงกับประเภทข้อมูล ต่อไปนี้เป็นภาพรวมของประเภทข้อมูลที่ใช้บ่อยที่สุดใน QuickEasy
ประเภทข้อมูลข้อความ
- VARCHAR(XX) - ใช้เพื่อจัดเก็บข้อความ 'xx' จะกำหนดจำนวนอักขระสูงสุดที่อาจจัดเก็บไว้ในฟิลด์ โดยทั่วไปใช้สำหรับฟิลด์ชื่อและคำอธิบาย
- BLOB SUB_TYPE TEXT - ใช้เพื่อจัดเก็บข้อความ แต่ไม่จำกัดจำนวนอักขระที่สามารถเก็บไว้ในฟิลด์ - โดยทั่วไปจะใช้สำหรับฟิลด์ 'หมายเหตุ'
ประเภทข้อมูลเชิงตัวเลข
- INTEGER - ใช้เพื่อจัดเก็บค่าตัวเลขที่ไม่มีค่าทศนิยม ใน QuickEasy ส่วนใหญ่จะใช้สำหรับคีย์หลักและคีย์ต่างประเทศ การตั้งค่า และฟิลด์ True/False
- FLOAT - ใช้เพื่อจัดเก็บค่าตัวเลขที่มีทศนิยม เรียกอีกอย่างว่าชนิดข้อมูลจุดลอยตัว โดยทั่วไปจะใช้เพื่อจัดเก็บปริมาณและค่าตัวเลขอื่นๆ ที่อาจใช้ในการคำนวณ เช่น ปริมาณโดยประมาณหรือปริมาณสต็อค
- NUMERIC(18,X) - ใช้เพื่อจัดเก็บค่าสกุลเงิน 'X' กำหนดจำนวนทศนิยม โดยปกติจะเป็น 2 หรือ 4
ประเภทข้อมูลวันที่และเวลา
- DATE - เก็บเฉพาะวันที่เท่านั้น ใช้สำหรับวันที่ทำธุรกรรม
- TIME - เก็บเฉพาะเวลาเท่านั้น โดยทั่วไปจะใช้ในการตั้งค่า เช่น เวลาเริ่มต้นและสิ้นสุดของวันทำงานหรือกะ
- TIMESTAMP - เก็บวันที่และค่าเวลา โดยทั่วไปใช้สำหรับวันครบกำหนด การบอกเวลา การตรวจสอบ ฯลฯ ซึ่งเวลาของวันมีความสำคัญพอๆ กับตัววันเอง
รูปภาพและไฟล์
- BLOB SUB_TYPE 0 - โดยทั่วไปประเภทข้อมูลนี้สามารถจัดเก็บไฟล์ใด ๆ ที่คุณสามารถบันทึกลงในฮาร์ดไดรฟ์ของคุณได้ รวมถึงไฟล์รูปภาพด้วย
ทำความเข้าใจเกี่ยวกับ SQL
หมายเหตุ : แม้ว่า SQL จะมีประสิทธิภาพมาก แต่ SQL ก็สามารถสร้างความเสียหายให้กับฐานข้อมูลได้หากใช้ไม่ถูกต้อง ดังนั้นโปรดฝึกฝนสคริปต์ SQL เหล่านี้ได้ตามใจชอบ แต่ ใช้ฐานข้อมูลสาธิต
คุณอาจสังเกตเห็นว่าระบบการจัดการฐานข้อมูลจำนวนมากมีตัวย่อ SQL (มักออกเสียงว่า "ภาคต่อ") นี่เป็นคำที่เกิดขึ้นบ่อยมากเมื่อพูดถึงฐานข้อมูล ย่อมาจาก Structured Query Language และเป็นภาษาที่ฐานข้อมูลเชิงสัมพันธ์ทั้งหมดเข้าใจ “การสืบค้นฐานข้อมูล” คือสิ่งที่ผู้ใช้ถามฐานข้อมูล และ SQL คือภาษาที่ใช้เขียนการสืบค้น
นี่คือวิธีที่คุณจะสร้างตาราง "ลูกค้า" ในฐานข้อมูล:
CREATE TABLE MYCLIENTS ( IDNUMBER INT PRIMARY KEY, NAME TEXT, CITY TEXT, TELEPHONE TEXT, BUDGET INT);
สิ่งนี้จะสร้างตารางชื่อ MYCLIENTS โดยมีห้าคอลัมน์ คอลัมน์ IDNUMBER คือคีย์หลักของตาราง ซึ่งหมายความว่าคอลัมน์นี้ใช้เพื่อระบุแถวโดยไม่ซ้ำกัน ดังนั้นจึงต้องไม่ซ้ำกัน กล่าวอีกนัยหนึ่ง ฐานข้อมูลนี้จะไม่อนุญาตให้คุณกำหนดหมายเลข ID เดียวกันให้กับลูกค้าสองราย ซึ่งสมเหตุสมผลหากคุณลองคิดดู คนสองคนไม่สามารถมีหมายเลขประจำตัวเดียวกันได้ในชีวิตจริง INT หมายความว่า IDNMUBER เป็นจำนวนเต็ม (ตัวเลขที่ไม่มีจุดทศนิยม) และ TEXT หมายความว่าคอลัมน์ชื่อมีข้อความ (คุณอาจใช้อย่างอื่นในสถานการณ์จริงเนื่องจาก TEXT ค่อนข้างไม่มีประสิทธิภาพ) คีย์เวิร์ด SQL เขียนด้วยตัวหนา คุณไม่สามารถใช้คำเหล่านี้เพื่อตั้งชื่อสิ่งต่าง ๆ ในฐานข้อมูลได้เนื่องจากล่าม SQL (โปรแกรมที่สื่อสารกับฐานข้อมูล) ใช้เพื่อออกคำสั่ง
หมายเหตุเครื่องหมายอัฒภาค (;) ที่ส่วนท้ายของคำสั่ง SQL เหมือนกับการลงท้ายประโยคภาษาอังกฤษ ช่วยให้ล่ามรู้ว่าเราพูดประโยคนี้เสร็จแล้ว การจัดรูปแบบของข้อความในข้อความไม่สำคัญและสามารถเขียนในรูปแบบใดก็ได้ที่เห็นชัดเจนที่สุดสำหรับคุณ ตัวอย่างเช่น ข้อความเดียวกันสามารถเขียนได้ดังนี้:
CREATE TABLE MYCLIENTS ( IDNUMBER INT PRIMARY KEY, NAME TEXT, CITY TEXT, TELEPHONE TEXT, BUDGET INT);
หรือแม้กระทั่ง:
CREATE TABLE MYCLIENTS ( IDNUMBER INT PRIMARY KEY, NAME TEXT, CITY TEXT, TELEPHONE TEXT, BUDGET INT );
เพียงจำไว้ว่าคุณอาจไม่ใช่คนเดียวที่อ่านข้อความ ดังนั้นยิ่งคุณรักษามันให้เรียบร้อย ความสับสนในระยะยาวก็จะยิ่งน้อยลงเท่านั้น
จากนั้นคุณสามารถใช้คำสั่ง SQL เช่นนี้เพื่อแทรกข้อมูลลงในตารางใหม่ของคุณได้:
INSERT INTO MYCLIENTS VALUES (100, 'Smiths Records', 'Cape Town', '020 8888 2222', 50000);
ซึ่งจะเป็นการเพิ่มลูกค้าที่มีหมายเลขรหัสลูกค้า 100 รายละเอียดการติดต่อบางส่วน และงบประมาณ โปรดสังเกตว่าเรามักจะใช้เครื่องหมายคำพูดเดี่ยวเพื่อระบุข้อความ (หรือที่เรียกว่าสตริงในภาษาฐานข้อมูล) เมื่อคุณเพิ่มข้อมูลลงในตารางโดยใช้คำสั่งที่คล้ายกันแล้ว คุณสามารถดำเนินการค้นหาโดยใช้คำสั่ง SQL ได้ดังนี้:
SELECT IDNUMBER, NAME FROM MYCLIENTS WHERE CITY='Cape Town';
วิธีนี้จะดึงข้อมูลหมายเลขประจำตัวและชื่อของลูกค้าทั้งหมดที่อยู่ในเคปทาวน์ หากคุณต้องการรับฟิลด์ทั้งหมด แทนที่จะเป็นเพียงหมายเลข ID และชื่อ คุณสามารถใช้เครื่องหมายดอกจัน (*) แทนการพิมพ์ชื่อฟิลด์ทั้งหมดดังนี้:
SELECT * FROM MYCLIENTS WHERE CITY='Cape Town';
ดัดแปลงมาจาก บทนำสู่ฐานข้อมูล
แหล่งข้อมูลเพิ่มเติม:
คำชี้แจงกรณี
ด้านล่างนี้เป็นตัวอย่างที่สาธิตการใช้คำสั่ง 'Case'
select C.CCENTREID, C.NAME as Cost_Centre, sum(SC.ESTTIME) as "CM_Hrs", sum(SC.SUBTOTALCOST) as "CM_Amount", case when (C.NAME = 'Parva') then (sum(SC.QTY)*2) when (C.NAME = 'GTO 46') then (sum(SC.QTY)/2) else sum(SC.QTY) end from SOCOSTS SC left join CCENTRE C on SC.CCID = C.CCENTREID left join SOMAIN S on SC.SOID = S.SOID where S.INVDATE between :DATESTART and :DATEEND and SC.CCID > 0 and C.COSTTYPE = 'Printing' group by C.CCENTREID, C.NAME, C.COSTTYPE order by C.NAME
การจัดการข้อความ / สตริง
SQL สามารถใช้เพื่อจัดการข้อความหรือที่เรียกว่า สตริง ต่อไปนี้เป็นรายการฟังก์ชันที่สามารถใช้เพื่อจัดการข้อความ (คลิกที่ฟังก์ชั่นเพื่อเปิดคู่มืออ้างอิง Firebird)
- ascii_char() - ส่งกลับอักขระ ASCII ที่สอดคล้องกับตัวเลขที่ส่งผ่านในอาร์กิวเมนต์
- Coalesce() - ฟังก์ชัน COALESCE รับอาร์กิวเมนต์ตั้งแต่ 2 อาร์กิวเมนต์ขึ้นไป และส่งกลับค่าของอาร์กิวเมนต์แรกที่ไม่ใช่ NULL หากอาร์กิวเมนต์ทั้งหมดประเมินเป็น NULL ผลลัพธ์จะเป็น NULL
- iif() - IIF มีสามข้อโต้แย้ง หากค่าแรกประเมินเป็นจริง อาร์กิวเมนต์ที่สองจะถูกส่งกลับ มิฉะนั้นอันที่สามจะถูกส่งคืน
- left() - ส่งกลับส่วนซ้ายสุดของสตริงอาร์กิวเมนต์ จำนวนอักขระถูกกำหนดไว้ในอาร์กิวเมนต์ที่สอง
- lower() - ส่งคืนค่าตัวพิมพ์เล็กที่เทียบเท่ากับสตริงอินพุต ผลลัพธ์ที่แน่นอนขึ้นอยู่กับชุดอักขระ
- lpad() - วางสตริงด้วยการเว้นวรรคหรือสตริงที่ผู้ใช้ระบุจนกว่าจะถึงความยาวที่กำหนด
- ตำแหน่ง() - ส่งกลับตำแหน่ง (ตาม 1) ของการเกิดขึ้นครั้งแรกของสตริงย่อยในสตริงโฮสต์
- replace() - แทนที่สตริงย่อยทั้งหมดในสตริง
- right() - ส่งกลับส่วนขวาสุดของสตริงอาร์กิวเมนต์ จำนวนอักขระถูกกำหนดไว้ในอาร์กิวเมนต์ที่สอง
- rpad() - คลิกขวาที่สตริงโดยเว้นวรรคหรือสตริงที่ผู้ใช้ระบุจนกว่าจะถึงความยาวที่กำหนด
- substring() - ส่งกลับสตริงย่อยของสตริงโดยเริ่มต้นจากตำแหน่งที่กำหนด ไม่ว่าจะไปสิ้นสุดสตริงหรือตามความยาวที่กำหนด
- trim() - ลบช่องว่างนำหน้าและ/หรือต่อท้าย (หรือสตริงอื่นๆ ที่เป็นทางเลือก) ออกจากสตริงอินพุต
- upper() - ส่งคืนค่าตัวพิมพ์ใหญ่ที่เทียบเท่ากับสตริงอินพุต ผลลัพธ์ที่แน่นอนขึ้นอยู่กับชุดอักขระ
การใช้ Firebird เป็นเครื่องคิดเลข
คุณไม่จำเป็นต้องใช้ตารางและฟิลด์เพื่อทำการคำนวณใน Firebird คุณสามารถรันการคำนวณโดยใช้ การเลือก[CALCULATION] จากรูปแบบ RDB$DATABASE ดังตัวอย่างด้านล่าง
select upper( 'i love quickeasy' ) from RDB$DATABASE |
หมายเหตุ
- ใน Firebird SQL ข้อความจะถูกคั่นด้วยเครื่องหมายคำพูดเดี่ยว - เช่น 'นี่คือข้อความ'
- สามารถรวมสตริง (ต่อกัน) โดยใช้ || เช่น. 'ชื่อ'||', '||'นามสกุล'
- สามารถแทรกตัวแบ่งบรรทัด (การขึ้นบรรทัดใหม่) ลงในสตริงข้อความโดยใช้: ascii_char(13)||ascii_char(10)
- หากมีค่า ว่าง ในการคำนวณ/การต่อข้อมูล ผลลัพธ์จะเป็น ค่าว่าง
ตัวอย่าง - การสร้างข้อความแสดงข้อผิดพลาดตามมูลค่าธุรกรรม
select case when ((ERROR1 is null ) and (ERROR2 is null )) then 'Everything OK' when ((ERROR1 is not null ) and (ERROR2 is null )) then ERROR1 when ((ERROR1 is null ) and (ERROR2 is not null )) then ERROR2 when ((ERROR1 is not null ) and (ERROR2 is not null )) then ERROR1||ascii_char( 13 )||ascii_char( 10 )||ERROR2 end as ERRORMESSAGE from (select cast(iif(COMPANYID is null , 'Customer not selected' , null ) as blob) as ERROR1, cast(iif(CONTACTID is null , 'Contact not selected' , null ) as blob) as ERROR2 from (select L.TRANSID, L.DOCID, L.TRANSDATE, L.COMPANYID, L.CONTACTID from LEDGER L where L.TRANSID = 99 ) ) |
เลือกใบแจ้งยอด
Select Statement ใช้เพื่อแยกหรือสืบค้นข้อมูลจากฐานข้อมูลและสามารถแบ่งออกเป็นส่วนหลักๆ ต่อไปนี้:
select [FIELDNAME1, FIELDNAME1, ETC.] or [*= All Fields] from [TABLENAME1] join [TABLENAME] on [CONDITIONS] where [CONDITIONS] order by [FIELDNAME1, FIELDNAME2, etc.] |
เช่น. คำสั่ง Select เพื่อส่งคืนลูกค้าทั้งหมดในฐานข้อมูล QuickEasy โดยเริ่มต้นด้วย 'A' และจัดเรียงตามชื่อบริษัท อาจมีลักษณะดังนี้:
select CUSID, COMPANY, TEL, EMAIL from CUSMAIN where COMPANY starting with 'A' order by COMPANY |
การจัดรูปแบบ
ช่องว่างและตัวแบ่งบรรทัดจะถูกละเว้น ดังนั้นการเว้นบรรทัดว่างไว้ระหว่างส่วนต่างๆ ของคำสั่ง Select จะทำให้ SQL ของคุณสามารถอ่านได้ง่ายขึ้นมาก
อีกวิธีในการทำให้ SQL ของคุณอ่านง่ายขึ้นคือการใช้ UPPER และ ตัวพิมพ์เล็ก ในบทช่วยสอนนี้ เราจะใช้ UPPERCASE สำหรับชื่อฟิลด์และตารางทั้งหมด และ ตัวพิมพ์เล็ก สำหรับส่วนที่เหลือของคำสั่ง
ความคิดเห็น
ความคิดเห็นสามารถใช้เพื่ออธิบายวิธีการทำงานของคิวรี และเน้นส่วนที่ตัวแปรอาจจำเป็นต้องเปลี่ยนแปลง สิ่งนี้มีประโยชน์อย่างยิ่งเมื่อทำการพอร์ตการสืบค้นข้ามฐานข้อมูลที่แตกต่างกัน และเมื่อบุคคลอื่นจำเป็นต้องสนับสนุนลูกค้าของคุณ ความคิดเห็นที่สามารถใช้ได้มีสองประเภท:
คอมเม้นไลน์ --คอมเม้นท์
-- Comments the rest of the line
บล็อกความคิดเห็น /* ความคิดเห็น */
/* Block comments can stretch over multiple lines. They are ideal for creating a detailed description of how a query works. They are also useful for commenting out blocks of a query that might be returning errors*/
คำสั่งเลือกแบบฝัง
SQL เป็นภาษาที่ยืดหยุ่นมากซึ่งช่วยให้คุณแยกข้อมูลได้หลายวิธี สิ่งสำคัญคือการเริ่มต้นจากเล็กๆ น้อยๆ และสร้างแบบสอบถามของคุณอย่างเป็นระบบ ลองดูตัวอย่างต่อไปนี้...
หากคุณต้องการสร้างรายงานที่จัดกลุ่มตามศูนย์ต้นทุน ให้เริ่มต้นด้วยการเลือกศูนย์ต้นทุนพื้นฐานเสมอ - ตัวอย่างนี้จะเลือกศูนย์ต้นทุนการผลิตทั้งหมด - ใช้นามแฝงเสมอ 'C' สำหรับ CCENTRE ในตัวอย่างนี้:
select C.CCENTREID, C.NAME from CCENTRE C where C.ISPRODUCTION = 'Yes' order by C.NAME
ตอนนี้คุณสามารถเพิ่มคำสั่ง Select ที่ฝังไว้ภายในคำสั่งนี้ได้ - ในตัวอย่างด้านล่าง ฉันได้แทรกตัวเลือกย่อยที่จะเลือกต้นทุนทั้งหมดสำหรับศูนย์ต้นทุนแต่ละศูนย์:
select C.CCENTREID, C.NAME, (select sum(SC.TOTALCOST) from SOCOSTS SC where SC.CCID = C.CCENTREID and SC.INTEXT = 'Internal') as COST from CCENTRE C where C.ISPRODUCTION = 'Yes' order by C.NAME
ก้าวไปอีกขั้นและตั้งค่าพารามิเตอร์วันที่สำหรับต้นทุนโดยใช้พารามิเตอร์ :DATESTART และ :DATEEND ซึ่งจะเปิดใช้งานการควบคุมตัวกรองวันที่ - วันที่อยู่ในตาราง SOMAIN (ส่วนหัวของใบสั่งขาย) ดังนั้นเราจึงต้องเข้าร่วมตารางนี้:
select C.CCENTREID, C.NAME, (select sum(SC.TOTALCOST) from SOCOSTS SC left join SOMAIN S on SC.SOID = S.SOID where SC.CCID = C.CCENTREID and S.INVDATE between :DATESTART and :DATEEND and SC.INTEXT = 'Internal') as COST from CCENTRE C where C.ISPRODUCTION = 'Yes' order by C.NAME
ต่อไป เราสามารถกรองใบสั่งขายที่ยกเลิกได้:
select C.CCENTREID, C.NAME, (select sum(SC.TOTALCOST) from SOCOSTS SC left join SOMAIN S on SC.SOID = S.SOID where SC.CCID = C.CCENTREID and S.INVDATE between :DATESTART and :DATEEND and S.STATUS <> 'Cancelled' and SC.INTEXT = 'Internal') as COST from CCENTRE C where C.ISPRODUCTION = 'Yes' order by C.NAME
เมื่อการเลือกย่อยทำงานได้ตามที่คุณต้องการ คุณสามารถคัดลอกได้อย่างง่ายดายเพื่อบอกว่าเลือกเวลาโดยประมาณสำหรับศูนย์ต้นทุนแต่ละแห่งโดยใช้พารามิเตอร์วันที่เดียวกัน ฯลฯ:
select C.CCENTREID, C.NAME, (select sum(SC.TOTALCOST) from SOCOSTS SC left join SOMAIN S on SC.SOID = S.SOID where SC.CCID = C.CCENTREID and S.INVDATE between :DATESTART and :DATEEND and S.STATUS <> 'Cancelled' and SC.INTEXT = 'Internal') as COST, (select sum(SC.ESTTIME) from SOCOSTS SC left join SOMAIN S on SC.SOID = S.SOID where SC.CCID = C.CCENTREID and S.INVDATE between :DATESTART and :DATEEND and S.STATUS <> 'Cancelled' and SC.INTEXT = 'Internal') as TIME from CCENTRE C where C.ISPRODUCTION = 'Yes' order by C.NAME
จากที่นี่เป็นขั้นตอนง่ายๆ ในการเพิ่มพารามิเตอร์วันที่ที่แตกต่างกันเพื่อเลือกพูดต้นทุนจาก 12 เดือนที่แล้ว:
select C.CCENTREID, C.NAME, (select sum(SC.TOTALCOST) from SOCOSTS SC left join SOMAIN S on SC.SOID = S.SOID where SC.CCID = C.CCENTREID and S.INVDATE between :DATESTART1 and :DATEEND1 and S.STATUS <> 'Cancelled' and SC.INTEXT = 'Internal') as COST_THISMONTH, (select sum(SC.TOTALCOST) from SOCOSTS SC left join SOMAIN S on SC.SOID = S.SOID where SC.CCID = C.CCENTREID and S.INVDATE between :DATESTART13 and :DATEEND13 and S.STATUS <> 'Cancelled' and SC.INTEXT = 'Internal') as COST_THISMONTHLASTYEAR, (select sum(SC.ESTTIME) from SOCOSTS SC left join SOMAIN S on SC.SOID = S.SOID where SC.CCID = C.CCENTREID and S.INVDATE between :DATESTART and :DATEEND and S.STATUS <> 'Cancelled' and SC.INTEXT = 'Internal') as TIME from CCENTRE C where C.ISPRODUCTION = 'Yes' order by C.NAME
ข้อไหน
โดยที่ ส่วนคำสั่งเป็นส่วนหนึ่งของคำสั่ง select ที่ใช้ในการกรองชุดผลลัพธ์ ตัวดำเนินการแบบมีเงื่อนไขต่อไปนี้สามารถใช้เพื่อเปรียบเทียบค่าในส่วนคำสั่ง Where
= เท่ากับ
<> ไม่เท่ากับ
> มากกว่า
< น้อยกว่า
>= มากกว่าหรือเท่ากับ
<= น้อยกว่าหรือเท่ากับ
ระหว่างและ ...> = และ< = ค่าก่อนและหลัง และ ตามลำดับ
ใน (1,2,3) เท่ากับค่าใดค่าหนึ่งในวงเล็บ
ไม่อยู่ใน (1,2,3) ไม่เท่ากับค่าใดค่าหนึ่งในวงเล็บ
เป็น ฟิลด์ว่าง
ไม่เป็น null ฟิลด์ไม่ว่างเปล่า
โดยที่ อนุประโยคสามารถมีหลายเงื่อนไขที่ถูกจัดกลุ่มโดยใช้ และ และ หรือ ต้องใช้ความระมัดระวังในการจัดกลุ่มเงื่อนไขให้ถูกต้องโดยใช้วงเล็บกลม ()
เข้าร่วมตาราง
โดยทั่วไปข้อมูลในฐานข้อมูลจะมีโครงสร้างในลักษณะที่ป้องกันข้อมูลซ้ำซ้อน
ตัวอย่างเช่น ข้อมูลลูกค้าจะถูกจัดเก็บไว้ในตารางเดียวเท่านั้น จากนั้นจะเชื่อมโยงกับธุรกรรมบนคีย์หลัก ดังนั้น หากคุณต้องการสร้างแบบสอบถามที่แสดงรายละเอียดธุรกรรม คุณจะต้องเข้าร่วมตารางลูกค้าและธุรกรรม เนื่องจากตารางธุรกรรมไม่มีข้อมูลลูกค้า จึงมีเพียงลิงก์ไปยังตารางลูกค้าเท่านั้น
มีตัวเลือกมากมายสำหรับการเข้าร่วมตารางใน SQL แต่คุณต้องพิจารณาเพียงสองตัวเลือกเท่านั้น ซึ่งโดยทั่วไปจะมีฟังก์ชันในการดึงข้อมูลใดๆ จาก QuickEasy:
- เข้าร่วม - ใช้เมื่อมีลิงก์รับประกันไปยังตารางที่กำลังเข้าร่วม บันทึกย่อยต้องมีบันทึกหลักที่เกี่ยวข้อง เช่น. การเข้าร่วมส่วนหัวของธุรกรรมกับรายการธุรกรรม - รายการไม่สามารถมีอยู่ได้หากไม่มีส่วนหัว การรวมด้านซ้าย จะใช้ได้ในสถานการณ์นี้ แต่จะช้ากว่ามากในฐานข้อมูลขนาดใหญ่
- left join - ใช้เมื่ออาจมีลิงก์ไปยังตารางที่กำลังเข้าร่วม เช่น. เมื่อเข้าร่วมรายละเอียดตัวแทนในธุรกรรม - ธุรกรรมบางอย่างอาจไม่เชื่อมโยงกับตัวแทน หากใช้ การเข้าร่วม ธุรกรรมที่ไม่ได้เชื่อมโยงกับตัวแทนจะไม่ปรากฏขึ้น
เมื่อรวมตาราง เราต้องพิจารณาลำดับที่จะรวมตารางด้วย ด้วยการจำกัดตัวเองให้ เข้าร่วม และ ออกจากตัวเลือกการเข้าร่วม เราจำเป็นต้องเริ่มต้นด้วยตารางรายละเอียดเสมอเมื่อเข้าร่วมตารางที่มีความสัมพันธ์หลัก-รายละเอียด ตัวอย่างเช่น หากเราต้องการแสดงข้อมูลจากลูกค้า ส่วนหัวของธุรกรรม รวมถึงรายการธุรกรรม เราจะเริ่มต้นด้วยรายการธุรกรรม รวมส่วนหัวของธุรกรรม จากนั้นจึงรวมตารางลูกค้า
นามแฝง
เมื่อมีการรวมตารางหลายตารางเข้ากับแบบสอบถาม คุณจะต้องระบุตารางที่มีการเชื่อมโยงแต่ละเขตข้อมูลในแบบสอบถาม เช่น.
select VATITEMS.*, LEDGER.*, CUSMAIN.* from VATITEMS join LEDGER on VATITEMS.TRANSID = LEDGER.TRANSID join CUSMAIN on LEDGER.COMPANYID = CUSMAIN.CUSID |
คุณยังสามารถทำเช่นนี้ได้โดยการกำหนดนามแฝงให้กับแต่ละตาราง ซึ่งสามารถใช้เพื่อระบุตารางที่เชื่อมโยงกับฟิลด์ได้ ตัวอย่างข้างต้นจะมีลักษณะดังนี้:
select V.*, L.*, C.* from VATITEMS V join LEDGER L on V.TRANSID = L.TRANSID join CUSMAIN C on L.COMPANYID = C.CUSID |
เงื่อนไข
เมื่อรวมตารางในแบบสอบถาม คุณจะต้องระบุเงื่อนไขการรวมด้วย เช่น. ช่องใดในแต่ละตารางจะสร้างลิงค์ ในกรณีส่วนใหญ่ คุณเพียงแค่ระบุฟิลด์ที่สร้างการรวม ในบางกรณีอาจมีเงื่อนไขเพิ่มเติมที่ต้องเพิ่ม เช่น. แบบสอบถามเพื่อรวมปริมาณประมาณการที่เลือกเข้ากับรายการประมาณการ
select * from VATITEMS V left join QUOTEQTY Q on V.VATITEMID = Q.VATITEMID and Q.STATUSID = 1 |
ยูเนี่ยน
คุณสามารถใช้ สหภาพ เพื่อเพิ่มค่าคงที่ลงในชุดผลลัพธ์ หรือเพื่อรวมตารางต่างๆ ที่ไม่ได้เชื่อมโยงไว้ในชุดผลลัพธ์เดียว
หมายเหตุ
ฟิลด์ในแต่ละ สหภาพ ต้องเหมือนกัน - ชื่อฟิลด์จะนำมาจากแบบสอบถามแรกในสหภาพ
สามารถเรียงลำดับแบบสอบถามที่มี สหภาพ ได้โดยเพิ่มส่วนคำสั่ง 'เรียงลำดับตาม' ที่ส่วนท้ายของแบบสอบถามโดยใช้รูปแบบ: เรียงลำดับตาม 1 (โดยที่ 1 = หมายเลขคอลัมน์)
ตัวอย่างต่อไปนี้แสดงให้เห็นถึงวิธีการใช้สหภาพในการเพิ่มตัวเลือก 'ทั้งหมด' ให้กับรายการหมวดหมู่รายการ
select -1 as ID, 'All' as CATEGORY from RDB$DATABASE union select MATCATID, NAME from MATCAT order by 2
ตัวอย่างต่อไปนี้แสดงให้เห็นว่าสามารถเลือกวัสดุและบริการ (ต้นทุน) ของธุรกรรมให้เป็นชุดผลลัพธ์ชุดเดียวได้อย่างไร
--Materials select M.QUOTEOUTID as ID, 'Material' as COSTTYPE, M.DESCRIPTION, M.QTY, M.TOTAL from QUOTEOUTSOURCE M join VATITEMS I on M.VATITEMID = I.VATITEMID join LEDGER L on I.TRANSID = L.TRANSID where L.ENTRYTYPEID = 1 -- ENTRYTYPEID for Quotations and L.DOCID = 18 -- Transaction No. union --Services select S.QUOTEFINID, 'Service', S.DESCRIPTION, S.QTY, S.TOTAL from QUOTEFIN S join VATITEMS I on S.VATITEMID = I.VATITEMID join LEDGER L on I.TRANSID = L.TRANSID where L.ENTRYTYPEID = 1 -- ENTRYTYPEID for Quotations and L.DOCID = 18 -- Transaction No. order by 1,2
ฟังก์ชันรวม
ฟังก์ชันรวมดำเนินการกับกลุ่มของเรคคอร์ด แทนที่จะดำเนินการกับเรคคอร์ดหรือตัวแปรแต่ละรายการ มักใช้ร่วมกับ GROUP BY clause
ฟังก์ชันรวมไม่จัดการ NULL ในลักษณะเดียวกับฟังก์ชันและตัวดำเนินการทั่วไป แทนที่จะคืนค่า NULL ทันทีที่พบตัวถูกดำเนินการ NULL พวกเขาจะพิจารณาเฉพาะฟิลด์ที่ไม่ใช่ NULL เท่านั้นในขณะที่คำนวณผลลัพธ์
ฟังก์ชันการรวม Firebird
- list() - ส่งคืนสตริงที่ประกอบด้วยค่าอาร์กิวเมนต์ที่ไม่ใช่ NULL ในกลุ่ม
- max() - ส่งกลับค่าอาร์กิวเมนต์สูงสุดในกลุ่ม ถ้าอาร์กิวเมนต์เป็นสตริง นี่คือค่าที่มาสุดท้ายเมื่อใช้การจัดเรียงที่ใช้งานอยู่
- min() - ส่งกลับค่าอาร์กิวเมนต์ขั้นต่ำในกลุ่ม ถ้าอาร์กิวเมนต์เป็นสตริง นี่คือค่าที่มาก่อนเมื่อใช้การเปรียบเทียบที่ใช้งานอยู่
- sum() - ส่งคืนผลรวมของค่าอาร์กิวเมนต์ในกลุ่ม
- ave() - ส่งกลับค่าเฉลี่ยของค่าอาร์กิวเมนต์ในกลุ่ม
- count() - ส่งคืนจำนวนบันทึกในกลุ่ม
แบบสอบถามที่ซ้อนกัน
คุณสามารถป้องกันการทำซ้ำของแบบสอบถามย่อยที่มีความยาวได้โดยใช้การซ้อนเพื่อเลือกค่าในระดับหนึ่ง จากนั้นจึงคำนวณค่าเหล่านั้นในระดับถัดไป
ข้อมูลต่อไปนี้แสดงวิธีการใช้แบบสอบถามเพื่อเลือกวันเริ่มต้นและสิ้นสุดของเดือนปัจจุบัน จากนั้นคำสั่ง Select อื่นจะสามารถใช้ค่าเหล่านี้ได้
select (select sum(TOTAL) from LEDGER where ENTRYTYPEID = 1 --ENTRYTYPEID for QUOTES and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as QUOTES, (select sum(TOTAL) from LEDGER where ENTRYTYPEID = 2 --ENTRYTYPEID for SALES ORDERS and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as ORDERS, (select sum(TOTAL) from LEDGER where ENTRYTYPEID = 6 --ENTRYTYPEID for INVOICES and TRANSDATE between STARTOFTHEMONTH and ENDOFTHEMONTH) as INVOICES from ( select (current_date - extract(day from current_date) + 1) as STARTOFTHEMONTH, current_date - extract(day from current_date) + 32 - extract(day from (current_date - extract(day from current_date) + 32)) as ENDOFTHEMONTH from RDB$DATABASE )
ตัวอย่างต่อไปนี้แสดงวิธีการแยกค่าฟิลด์ที่ผู้ใช้กำหนดก่อน เพื่อให้คุณสามารถคำนวณได้ในระดับถัดไป
select MATUNITID, DESCRIPTION, CODE, DEPTH, WIDTH, GSM, (DEPTH/1000*WIDTH/1000) as AREA, (cast(DEPTH as float)/1000*cast(WIDTH as float)/1000*cast(GSM as float)/1000) as UNITKG, maxvalue(DEPTH,WIDTH) as LONGSIDE from ( select MU.MATUNITID, MU.DESCRIPTION, MU.CODE, (select iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer)) from FIELDVALUES F where F.MODULEID = 3000 + MC.PRODTYPEID and F.RECORDID = MU.MATUNITID and F.NAME = 'DEPTH') as DEPTH, (select iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer)) from FIELDVALUES F where F.MODULEID = 3000 + MC.PRODTYPEID and F.RECORDID = MU.MATUNITID and F.NAME = 'WIDTH') as WIDTH, (select iif(F.FIELDVALUE is null, 0, cast(F.FIELDVALUE as integer)) from FIELDVALUES F where F.MODULEID = 3000 + MC.PRODTYPEID and F.RECORDID = MU.MATUNITID and F.NAME = 'GSM') as GSM from MATUNIT MU join MATCAT MC on MU.MATCATID = MC.MATCATID where MU.MATCATID = 10 --Paper Category )
ตัวอย่างต่อไปนี้แสดงวิธีการเลือกรายการชื่อกระดาษที่แตกต่างกัน โดยไม่รวมขนาดแผ่นงาน
select distinct(NAME||', '||COLOUR||', '||GSM||'gsm') as DESCRIPTION, min(MATUNITID) as ID from ( select MU.MATUNITID, (select F.FIELDVALUE from FIELDVALUES F where F.RECORDID = MU.MATUNITID and F.MODULEID = 3004 and F.NAME = 'NAME') as NAME, (select F.FIELDVALUE from FIELDVALUES F where F.RECORDID = MU.MATUNITID and F.MODULEID = 3004 and F.NAME = 'COLOUR') as COLOUR, (select F.FIELDVALUE from FIELDVALUES F where F.RECORDID = MU.MATUNITID and F.MODULEID = 3004 and F.NAME = 'GSM') as GSM from MATUNIT MU where MU.MATCATID = 10 ) group by DESCRIPTION order by DESCRIPTION
การคำนวณตัวเลข
SQL สามารถใช้เป็นเครื่องคำนวณตัวเลขได้
ต่อไปนี้เป็นรายการฟังก์ชันที่ใช้กันทั่วไปซึ่งสามารถใช้ในการคำนวณตัวเลข (คลิกที่ฟังก์ชันเพื่อเปิดคู่มืออ้างอิง Firebird):
- cast() - CAST แปลงนิพจน์เป็นประเภทข้อมูลหรือโดเมนที่ต้องการ หากไม่สามารถแปลงได้ จะเกิดข้อผิดพลาด
- ceiling() - ส่งกลับจำนวนเต็มที่น้อยที่สุดที่มากกว่าหรือเท่ากับอาร์กิวเมนต์
- floor() - ส่งคืนค่าจำนวนเต็มที่มากที่สุดซึ่งน้อยกว่าหรือเท่ากับอาร์กิวเมนต์
- iif() - มีสามข้อโต้แย้ง หากค่าแรกประเมินเป็นจริง อาร์กิวเมนต์ที่สองจะถูกส่งกลับ มิฉะนั้นอันที่สามจะถูกส่งคืน
- maxvalue() - ส่งคืนค่าสูงสุดจากรายการนิพจน์ตัวเลข สตริง หรือวันที่/เวลา ฟังก์ชันนี้รองรับข้อความ BLOB ที่มีความยาวและชุดอักขระเท่าใดก็ได้
- minvalue() - ส่งคืนค่าต่ำสุดจากรายการนิพจน์ตัวเลข สตริง หรือวันที่/เวลา ฟังก์ชันนี้รองรับข้อความ BLOB ที่มีความยาวและชุดอักขระเท่าใดก็ได้
- pi() - ส่งกลับค่าประมาณของค่า pi
- round() - ปัดเศษตัวเลขให้เป็นจำนวนเต็มที่ใกล้ที่สุด หากส่วนที่เป็นเศษส่วนเท่ากับ 0.5 พอดี การปัดเศษขึ้นสำหรับจำนวนบวกและปัดลงสำหรับจำนวนลบ ด้วยอาร์กิวเมนต์สเกลที่เป็นทางเลือก ตัวเลขสามารถปัดเศษเป็นทวีคูณยกกำลังสิบ (สิบ ร้อย สิบ ร้อย ฯลฯ) แทนที่จะเป็นเพียงจำนวนเต็ม
การใช้ Firebird เป็นเครื่องคิดเลข
คุณไม่จำเป็นต้องใช้ตารางและฟิลด์เพื่อทำการคำนวณใน Firebird คุณสามารถรันการคำนวณโดยใช้ การเลือก[CALCULATION] จากรูปแบบ RDB$DATABASE ดังตัวอย่างด้านล่าง
select ( 2 + 3 ) * 6 from RDB$DATABASE |
หมายเหตุ
หากส่งค่า Null เข้าสู่การคำนวณ ผลลัพธ์จะเป็น Null เสมอ
ผลลัพธ์ของการคำนวณจะเป็นประเภทข้อมูลเดียวกันกับค่าที่ถูกส่งเข้าสู่การคำนวณ - เช่น 6/4 = 1 และ โยน (6 ลอย) / โยน (4 ลอย) = 1.5
การคำนวณวันที่
คุณสามารถใช้ SQL เพื่อคำนวณวันที่ได้
ต่อไปนี้เป็นรายการฟังก์ชันที่สามารถใช้เพื่อจัดการวันที่ใน Firebird (คลิกที่ฟังก์ชั่นเพื่อเปิดคู่มืออ้างอิง Firebird)
- dateadd() - เพิ่มจำนวนปี เดือน วัน ชั่วโมง นาที วินาที หรือมิลลิวินาทีที่ระบุให้กับค่าวันที่/เวลา
- datediff() - ส่งกลับจำนวนปี เดือน วัน ชั่วโมง นาที วินาที หรือมิลลิวินาทีที่ผ่านไประหว่างวันที่/เวลาสองค่า
- extract() - แยกและส่งกลับองค์ประกอบจากนิพจน์ DATE, TIME หรือ TIMESTAMP
หมายเหตุ
สามารถดึงวันที่และเวลาออกจากเซิร์ฟเวอร์ได้โดยใช้
- วันที่ - เลือก CURRENT_DATE จาก RDB$DATABASE
- เวลา - เลือก CURRENT_TIME จาก RDB$DATABASE
- การประทับเวลา - เลือก CURRENT_TIMESTAMP จาก RDB$DATABASE
คุณสามารถเพิ่มวันที่คงที่ลงใน SQL ในรูปแบบต่อไปนี้:
- 'm/d/yyyy' - เช่น '9/30/2015'
- 'mdyyyy' - เช่น '9.30.2015'
- 'd mmm yyyy' - เช่น '30 Sep 2015'
การคำนวณวันที่เริ่มต้นและสิ้นสุดของเดือนปัจจุบัน
select (CURRENT_DATE - extract(day from CURRENT_DATE) + 1 ) as STARTOFTHEMONTH, CURRENT_DATE - extract(day from CURRENT_DATE) + 32 - extract(day from (CURRENT_DATE - extract(day from CURRENT_DATE) + 32 )) as ENDOFTHEMONTH from RDB$DATABASE |
แยกข้อมูลสรุปสามเดือนตามวันที่ปัจจุบัน
select MON, YEA, (select sum(L.TOTAL) from LEDGER L where L.ENTRYTYPEID = 1 and extract(month from L.TRANSDATE) = MON and extract(year from L.TRANSDATE) = YEA) as QUOTES, (select sum(L.TOTAL) from LEDGER L where L.ENTRYTYPEID = 2 and extract(month from L.TRANSDATE) = MON and extract(year from L.TRANSDATE) = YEA) as ORDERS, (select sum(L.TOTAL) from LEDGER L where L.ENTRYTYPEID = 6 and extract(month from L.TRANSDATE) = MON and extract(year from L.TRANSDATE) = YEA) as INVOICES from (select extract(month from CURRENT_DATE) as MON, extract(year from CURRENT_DATE) as YEA from RDB$DATABASE union select extract(month from dateadd(- 1 month to CURRENT_DATE)) as MON, extract(year from dateadd(- 1 month to CURRENT_DATE)) as YEA from RDB$DATABASE union select extract(month from dateadd(- 2 month to CURRENT_DATE)) as MON, extract(year from dateadd(- 2 month to CURRENT_DATE)) as YEA from RDB$DATABASE ) |
NULL Values
เมื่อฟิลด์ว่างเปล่า จะมีค่า NULL - ไม่มีค่าใดๆ นี่เป็นสิ่งสำคัญมากที่ต้องทำความเข้าใจเมื่อทำงานกับ SQL เนื่องจากการคำนวณหรือการต่อข้อมูลที่มีค่า NULL จะส่งกลับคำตอบ NULL อย่างหลีกเลี่ยงไม่ได้ ไม่ว่าคุณจะทำงานกับข้อมูลประเภทใดก็ตาม ต่อไปนี้เป็นฟังก์ชันและตัวอย่างบางส่วนที่สามารถใช้เพื่อหลีกเลี่ยงค่า NULL
ฟังก์ชั่นที่สามารถใช้เพื่อแปลงค่า NULL เป็นค่าที่ใช้งานได้
- iif() - IIF มีสามข้อโต้แย้ง หากค่าแรกประเมินเป็นจริง อาร์กิวเมนต์ที่สองจะถูกส่งกลับ มิฉะนั้นอันที่สามจะถูกส่งคืน เช่น.
- Coalesce() - ฟังก์ชัน COALESCE รับอาร์กิวเมนต์ตั้งแต่ 2 อาร์กิวเมนต์ขึ้นไป และส่งกลับค่าของอาร์กิวเมนต์แรกที่ไม่ใช่ NULL หากอาร์กิวเมนต์ทั้งหมดประเมินเป็น NULL ผลลัพธ์จะเป็น NULL
ชนิดข้อมูลตัวเลข
เมื่อทำงานกับประเภทตัวเลข คุณจะต้องแปลงค่า NULL เป็น 0 เป็นส่วนใหญ่ คุณต้องจำไว้ว่าการหารด้วย 0 จะส่งผลให้เกิดข้อผิดพลาด ตัวอย่างดังต่อไปนี้:
iif(FIELDNAME is null, 0, FIELDNAME)
coalesce(FIELDNAME, 0)
ประเภทข้อมูลข้อความ / สตริง
เมื่อทำงานกับสตริงข้อความ คุณจะต้องแปลงค่า NULL เป็น '' เป็นส่วนใหญ่ อย่างไรก็ตาม ในการต่อข้อมูล คุณสามารถเพิ่มหรือลบสตริงทั้งหมดได้โดยขึ้นอยู่กับว่าฟิลด์นั้นมีค่า NULL หรือไม่ ตัวอย่างดังต่อไปนี้:
coalesce('Printed in '||COLOURS||' colours', 'Not Printed')
iif(COLOURS is null, 'Not Printed', 'Printed in '||COLOURS||' colours')
ฟิลด์ที่ผู้ใช้กำหนด
ค่าฟิลด์ที่ผู้ใช้กำหนดทั้งหมดจะถูกจัดเก็บไว้ในตารางเดียวใน QuickEasy ซึ่งก็คือ FIELDVALUES แต่ละบันทึกในตารางนี้จะเชื่อมโยงกับโมดูล (เช่น ลูกค้า ซัพพลายเออร์ ฯลฯ) บันทึกในโมดูลนั้นตลอดจนฟิลด์ที่ผู้ใช้กำหนดซึ่งกำหนดไว้
ลิงก์ไปยังโมดูลถูกสร้างขึ้นโดยใช้ฟิลด์ MODULEID ต่อไปนี้เป็นรายการคีย์โมเดลที่ใช้ใน QuickEasy:
- 0:Customers
- 1:Customer Contacts
- 2:Suppliers
- 3:Supplier Contacts
- 4:Staff
ลิงก์ไปยังคีย์หลักของเรคคอร์ดในโมดูลถูกสร้างขึ้นโดยใช้ฟิลด์ RECORDID
ลิงก์ไปยังคีย์หลักของฟิลด์ที่ผู้ใช้กำหนดซึ่งกำหนดคุณสมบัติจะถูกสร้างขึ้นโดยใช้ฟิลด์ FIELDSPECID
ค่าของฟิลด์ที่ผู้ใช้กำหนดจะถูกเก็บไว้ใน FIELDVALUE, FIELDVALUE_DATE หรือ FIELDVALUE_BLOB ขึ้นอยู่กับประเภทของฟิลด์
ตัวอย่าง - ฟิลด์ที่ผู้ใช้กำหนดในลูกค้า
เริ่มต้นด้วยคำสั่งการเลือกลูกค้าขั้นพื้นฐาน:
select C.CUSID, C.COMPANY from CUSMAIN C order by C.COMPANY
ตอนนี้เพิ่มคำสั่งเลือกแบบฝังเพื่อแยกค่าของฟิลด์ที่ผู้ใช้กำหนด:
select C.CUSID, C.COMPANY, (select F.FIELDVALUE from FIELDVALUES F where F.MODULEID = 0 and F.RECORDID = C.CUSID and F.FIELDSPECID = 1) as BEE_Rating from CUSMAIN C order by C.COMPANY
หมายเหตุ
โดยที่ F.MODULEID = 0 (0 = ค่าคงที่สำหรับโมเดลลูกค้า)
และ F.RECORDID = C.CUSID (จะเชื่อมโยงฟิลด์ผู้ใช้กับเรกคอร์ดในโมเดลลูกค้า)
และ F.FIELDSPECID = 1 (ค่านี้ระบุฟิลด์ที่ผู้ใช้กำหนดและสามารถดูได้ในฟิลด์ ID ของตารางฟิลด์ที่ผู้ใช้กำหนด - แก้ไข> การตั้งค่าและค่าเริ่มต้น> ฟิลด์ที่ผู้ใช้กำหนด)
การลบเพจที่หมดอายุและเพจที่ถูกละเลย
นี่คือสคริปต์ที่คุณต้องมี:
- ลบราคากระดาษที่หมดอายุแล้ว (ลบราคากระดาษหมดอายุ.sql)
- ลบเอกสารที่ไม่มีราคาอีกต่อไป (ลบเพจที่ถูกละเลย Units.sql)
วิธีใช้สคริปต์เหล่านี้
ก่อนที่จะรันสคริปต์ โปรดตรวจสอบให้แน่ใจว่าคุณได้ทำการสำรองฐานข้อมูลและอัปเดตราคาทั้งหมดจากอินเทอร์เน็ต ราคากระดาษที่ไม่ได้รับการอัปเดต (เช่น กระดาษที่เลิกผลิตเช่น Dukuza และ Avalon) จะสามารถลบออกได้โดยใช้สคริปต์ด้านบน
สคริปต์แรกกำหนดให้คุณป้อน 'MATCATID' ของหมวดกระดาษ - มิฉะนั้นราคาหมึกและเพลทจะถูกลบด้วย โดยส่วนใหญ่แล้วจะเป็น '1' แต่เพื่อให้แน่ใจว่าคุณสามารถเรียกใช้สคริปต์ต่อไปนี้:
เลือก MATCHATID, NAME จาก MATCAT;
1. ลบ Expid Paper Price.sql
delete from MATPRICE where MATPRICEID in (select MP.MATPRICEID from MATPRICE MP left join MATUNIT MU on MP.MATUNITID = MU.MATUNITID where MU.MATCATID = 1 and MP.UPDATED < (select (CURRENT_DATE - 30) from RDB$DATABASE)); commit work;
2. ลบ Orphan Units.sql
Delete from MATUNIT MU where (select count(*) from MATPRICE MP where MP.MATUNITID = MU.MATUNITID) = 0; commit work;
ตัวอย่าง SQL
รับการประเมินมูลค่าปัจจุบันของหุ้นในมือ
select STOCKID, MATUNITID, DESCRIPTION, CODE, UNIT, INSTOCK, COSTPRICE, INSTOCK * COSTPRICE as STOCKVALUE from ( select S.STOCKID, M.MATUNITID, M.DESCRIPTION, M.CODE, U.DESCRIPTION as UNIT, iif(S.QTYINSTOCK = 0, NULL, S.QTYINSTOCK) as INSTOCK, (select max(MC.COSTPRICE / MC.PRICEQTY) from MATCOST MC where MC.MATUNITID = S.MATUNITID and MC.UNITID = S.UNITID and MC.STATUSID = 1) as COSTPRICE from STOCK S join MATUNIT M on S.MATUNITID = M.MATUNITID left join UNITS U on S.UNITID = U.UNITID ) order by 2,3,4
คำนวณจำนวนออกเมื่อทำงานกับชีต
select maxvalue(QTYOUT_STRAIGHT, QTYOUT_ROTATED) as QTYOUT, iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, QTYDOWN_ROTATE, QTYDOWN_STRAIGHT) as QTYDOWN, iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, QTYACCROSS_ROTATE, QTYACCROSS_STRAIGHT) as QTYACCROSS, iif(QTYOUT_ROTATED > QTYOUT_STRAIGHT, 'Yes', 'No') as ROTATED, (AREA_PARENT-(maxvalue(QTYOUT_STRAIGHT, QTYOUT_ROTATED)*AREA_CHILD))/AREA_PARENT*100 as WASTE from ( select floor((DEPTH_PARENT / DEPTH_CHILD)) * floor((WIDTH_PARENT / WIDTH_CHILD)) as QTYOUT_STRAIGHT, floor((DEPTH_PARENT / WIDTH_CHILD)) * floor((WIDTH_PARENT / DEPTH_CHILD)) as QTYOUT_ROTATED, floor((DEPTH_PARENT / DEPTH_CHILD)) as QTYDOWN_STRAIGHT, floor((WIDTH_PARENT / WIDTH_CHILD)) as QTYACCROSS_STRAIGHT, floor((DEPTH_PARENT / WIDTH_CHILD)) as QTYDOWN_ROTATE, floor((WIDTH_PARENT / DEPTH_CHILD)) as QTYACCROSS_ROTATE, DEPTH_PARENT * WIDTH_PARENT as AREA_PARENT, DEPTH_CHILD * WIDTH_CHILD as AREA_CHILD from ( select cast(1200 as float) as DEPTH_PARENT, cast(2400 as float) as WIDTH_PARENT, cast(600 as float) as DEPTH_CHILD, cast(900 as float) as WIDTH_CHILD from RDB$DATABASE ) )
คำนวณจำนวนออกเมื่อทำงานกับวงล้อ
select iif(WASTE_ROTATE < WASTE_STRAIGHT, QTYACCROSS_ROTATE, QTYACCROSS_STRAIGHT) as QTYACCROSS, iif(WASTE_ROTATE < WASTE_STRAIGHT, 'Yes', 'No') as ROTATED, minvalue(WASTE_ROTATE, WASTE_STRAIGHT) as WASTE from ( select QTYACCROSS_STRAIGHT, QTYACCROSS_ROTATE, ((WIDTH_REEL - (QTYACCROSS_STRAIGHT * WIDTH_CHILD))/WIDTH_REEL * 100) as WASTE_STRAIGHT, ((WIDTH_REEL - (QTYACCROSS_ROTATE * DEPTH_CHILD))/WIDTH_REEL * 100) as WASTE_ROTATE from ( select WIDTH_REEL, DEPTH_CHILD, WIDTH_CHILD, floor((WIDTH_REEL / WIDTH_CHILD)) as QTYACCROSS_STRAIGHT, floor((WIDTH_REEL / DEPTH_CHILD)) as QTYACCROSS_ROTATE from ( select cast(2000 as float) as WIDTH_REEL, cast(600 as float) as DEPTH_CHILD, cast(1200 as float) as WIDTH_CHILD from RDB$DATABASE ) ) )
สร้างรายชื่อผู้ใช้และระดับการเข้าถึงเป็นประเภทธุรกรรม
select USERNAME, case when ((ACCESS is null) or (ACCESS = 0)) then 'Access Denied' when ACCESS = 1 then 'View - User only' when ACCESS = 2 then 'View - Rep only' when ACCESS = 3 then 'View - All' when ACCESS = 4 then 'Edit - User only' when ACCESS = 5 then 'Edit - Rep only' when ACCESS = 6 then 'Edit - All' when ACCESS = 7 then 'Edit & Delete' when ACCESS = 8 then 'Full Access' end as ACCESS from ( select S.SHORTNAME as USERNAME, (select max(A.ACCESSID) from MODULESSETTINGS A where A.ENTRYTYPEID = 1 --ENTRYTYPEID and ((A.USERID = S.STAFFID) or (A.ROLEID in (select R.ROLEID from MODULESUSERROLES R where R.USERID = S.STAFFID)))) as ACCESS from CPYSTAFF S )
เชื่อมต่อสตริงที่อาจมีหรือไม่มีอยู่ให้เป็นสตริงที่คั่นด้วยเครื่องหมายจุลภาคหนึ่งสตริง
select ERROR1 ||trim(leading ' ' from iif(((ERROR1 > '') and (ERROR2 > '')), ', ', '')) ||ERROR2 ||trim(leading ' ' from iif(((ERROR1||ERROR2 > '') and (ERROR3 > '')), ', ', '')) ||ERROR3 ||trim(leading ' ' from iif(((ERROR1||ERROR2||ERROR3 > '') and (ERROR4 > '')), ', ', '')) ||ERROR4 FROM ( select --'' as ERROR1, 'This is Error 1' as ERROR1, '' as ERROR2, --'This is Error 2' as ERROR2, --'' as ERROR3, 'This is Error 3' as ERROR3, --'' as ERROR4 'This is Error 4' as ERROR4 from RDB$DATABASE )