Users
| id | name |
|---|---|
| 1 | Ann |
| 2 | Ben |
สารบัญบทอ่าน / Databases and SQL / 12.3
04 · เขียนโค้ดและนำระบบไปใช้ · บท 12.3
Combining and Investigating Data
รวมและตรวจข้อมูล
เมื่อข้อมูลกระจายหลายตาราง ต้องเลือกวิธีรวมให้ตรงคำถาม และตรวจจำนวนแถวที่เพิ่มขึ้น
ควรรู้ก่อน: 12.2 การสืบค้น
หัวข้อในบทนี้
ศัพท์ในบทนี้ · กดคำที่ขีดเส้นใต้ในเนื้อหาเพื่อดูความหมายได้ด้วย
- IP
- ที่อยู่และโปรโตคอลส่ง packet ระหว่างเครือข่าย รุ่น IPv4 และ IPv6 มีรูปแบบต่างกัน
- SQL
- ภาษาประมวลข้อมูลในฐานข้อมูล เช่นเลือก กรอง join และเปลี่ยนข้อมูล
จากหลักการไปถึงกลไก
join เพิ่มความสัมพันธ์ ไม่ใช่ต่อรายการเฉย ๆ
Join จับแถวจากสองชุดตามเงื่อนไข เช่น users.id = devices.user_id INNER JOIN เก็บคู่ที่ match LEFT JOIN เก็บทุกแถวฝั่งซ้ายพร้อมแถวขวาที่ match ถ้าไม่มีคู่ คอลัมน์ขวาจะเป็น NULL แต่ถ้าฝั่งขวามีหลายคู่ แถวซ้ายหนึ่งแถวอาจปรากฏหลายครั้งได้
เปรียบเทียบความหมาย · ไม่ใช่ลำดับเวลาjoin เพิ่มความสัมพันธ์ ไม่ใช่ต่อรายการเฉย ๆ
- Users
ผู้ใช้ 1 มีชื่อ Ann ผู้ใช้ 2 มีชื่อ Ben เป็นชุดซ้าย
- Devices
เครื่อง D1 และ D2 ผูก user_id 1 แต่ไม่มีเครื่องผูก 2
- INNER JOIN
ได้ Ann–D1 และ Ann–D2 รวมสองแถว Ben ไม่อยู่เพราะไม่มีคู่
- LEFT JOIN
ได้สองแถวของ Ann และ Ben–NULL อีกหนึ่งแถว จึงมีสามแถว
- Filter placement
เงื่อนไขฝั่งขวาใน WHERE อาจตัดแถว NULL ออก ต่างจากใส่เงื่อนไขนั้นใน ON
Devices
| id | user_id |
|---|---|
| D1 | 1 |
| D2 | 1 |
| D3 | 9 |
| User | Device |
|---|---|
| Ann | D1 |
| Ann | D2 |
2 แถว · Ann ปรากฏสองครั้งเพราะมีสองคู่
เงื่อนไข users.id = devices.user_id D3 เป็นข้อมูล unmatched สำหรับสาธิต ไม่ใช่ตัวอย่างฐานข้อมูลที่ตั้ง foreign key บังคับครบ การรองรับ FULL OUTER JOIN ขึ้นกับ dialect
หนึ่ง user จับคู่ได้มากกว่าหนึ่งแถวเส้นแสดงคู่ที่ตรงเงื่อนไข Users.id = Devices.user_id
INNER เก็บเฉพาะคู่ ส่วน LEFT รักษา user ฝั่งซ้ายที่ไม่มีคู่ FULL รักษาทั้งสองฝั่ง กดชนิด JOIN ในตัวอย่างด้านบนแล้วเทียบจำนวนแถวที่ได้
RIGHT JOIN รักษาทุกแถวฝั่งขวา FULL OUTER JOIN รักษารายการไม่มีคู่ทั้งสองฝั่งตาม dialect ที่รองรับ เวลาค้น security events ต้องรู้ cardinality เพื่อไม่ count จำนวนคนเป็นจำนวนแถว join เข้าใจชนิดและความสัมพันธ์ ส่วน shell filtering จัดข้อความหรือวัตถุ ไม่แทน relational semantics โดยอัตโนมัติ
01 / 03
เชื่อมตารางและชนิดการเชื่อมJoins and Join Types · 12.3.1
JOIN รวมข้อมูลด้วยเงื่อนไข INNER กับ LEFT JOIN เก็บแถวที่ไม่พบคู่ต่างกัน
JOIN รวมข้อมูลจากแถวที่ตรงเงื่อนไข INNER เก็บคู่ที่ตรง LEFT เก็บทุกแถวฝั่งซ้ายพร้อมข้อมูลฝั่งขวาที่พบ RIGHT สลับฝั่ง และ FULL เก็บทั้งสองฝั่งที่ระบบรองรับ แถวไม่มีคู่มี NULL ในฝั่งที่หาย
| hostname | owner_id |
|---|---|
| PC-1 | 7 |
| PC-2 | NULL |
| id | name |
|---|---|
| 7 | Alice |
| hostname | name |
|---|---|
| PC-1 | Alice |
| PC-2 | NULL |
Join condition คือ devices.owner_id = users.id PC-2 ไม่มีคู่แต่ LEFT JOIN ยังคืน PC-2 หาก INNER JOIN จะไม่คืนแถวนั้น NULL ในผลหมายถึงไม่มีค่าคู่ ไม่ใช่ชื่อผู้ใช้ว่า NULL
LEFT JOIN devices กับ users ช่วยคงอุปกรณ์ไม่มีเจ้าของ แต่ถ้า WHERE กรอง users.team โดยไม่เผื่อ NULL อุปกรณ์ไม่มีเจ้าของอาจหาย ตรวจจำนวนแถวด้วยเพราะหนึ่งต่อหลายเพิ่มแถวได้
ON versus WHERE in LEFT JOIN (ตำแหน่งเงื่อนไขมีผล)
LEFT JOIN devices ON users.id = devices.user_id AND devices.state = 'online' กำหนดว่าแถวขวาใดเป็นคู่ แต่ยังรักษาแถว users ที่ไม่มีคู่ออนไลน์ หากย้ายเป็น WHERE devices.state = 'online' หลัง join แถวที่ไม่มีคู่และมี NULL ฝั่ง devices จะถูกตัดจากผลได้
การ count หลัง join ต้องรู้ว่าคนหนึ่งมีหลายคู่ COUNT(*) นับแถวผล ไม่ใช่จำนวนผู้ใช้ไม่ซ้ำและไม่จำเป็นต้องเท่าจำนวนเครื่อง เงื่อนไข join และ cardinality จึงเป็นส่วนของความหมายรายงาน ไม่ใช่เพียงเทคนิคเขียน query ให้รันผ่าน
SELECT d.hostname, u.name
FROM devices AS d
LEFT JOIN users AS u ON d.owner_id = u.id;d กับ u เป็นชื่อย่อทุกเครื่องฝั่ง devices ถูกคงไว้ เครื่องที่ owner_id ไม่มีคู่จะได้ u.name เป็น NULL จำนวนแถวอาจเพิ่มหากเงื่อนไขฝั่งขวามีหลายคู่
ลองมีพนักงาน A B C และเครื่องที่ผูกกับ A B เท่านั้น INNER JOIN จะได้ A B ส่วน LEFT JOIN ที่มีพนักงานอยู่ซ้ายจะได้ A B C โดยคอลัมน์เครื่องของ C เป็น NULL ภาพข้างบนใช้ความต่างนี้อธิบายว่าทำไมต้องเลือกชนิด join ตามโจทย์
ถ้า A มีสองเครื่อง ก็อาจได้สองแถวสำหรับ A ในผล join นี่เป็นผลของความสัมพันธ์ one-to-many ไม่ใช่ฐานข้อมูลเพิ่มคนโดยอัตโนมัติ
ที่มาของหัวข้อนี้ · 2 รายการ
คำอธิบายและภาพในหน้านี้เรียบเรียงใหม่ ชื่อบทต้นทางด้านล่างใช้ตรวจที่มา ไม่ได้แทนเนื้อหาที่คุณต้องไปอ่านเพิ่มเพื่อเข้าใจหน้านี้ และไม่ยืนยันว่าทุกประโยคมีอยู่ในคอร์ส
- Tools of the Trade: Linux and SQL · Module 4: Databases and SQLVideo: Join tables in SQLCY4.M4.V9
- Tools of the Trade: Linux and SQL · Module 4: Databases and SQLVideo: Types of joinsCY4.M4.V10
02 / 03
สืบค้นข้อมูลความปลอดภัยSecurity Data Queries · 12.3.2
คำถามความปลอดภัยใช้ข้อมูลบัญชี อุปกรณ์ และเหตุการณ์ร่วมกันเพื่อหาบริบท
Security query ใช้ข้อมูลเพื่อตอบคำถามที่ระบุ เช่น login ล้มเหลวตาม account และเวลา ต้องเข้าใจความหมายแต่ละ event แหล่งข้อมูล และช่วงที่เก็บ การนับอย่างเดียวไม่พิสูจน์การโจมตี
•Question
เหตุการณ์ที่ต้องการหา
•Query / context
เงื่อนไขและข้อมูลประกอบ
•Finding
ข้อสรุปที่หลักฐานรองรับ
หลาย failed login อาจเป็นรหัสเก่าของ service account ดูแหล่ง เวลา success ที่ตามมา และสิทธิ์บัญชี ก่อนสรุป เก็บ query เงื่อนไขและ timezone เพื่อให้ตรวจซ้ำได้
ที่มาของหัวข้อนี้ · 2 รายการ
คำอธิบายและภาพในหน้านี้เรียบเรียงใหม่ ชื่อบทต้นทางด้านล่างใช้ตรวจที่มา ไม่ได้แทนเนื้อหาที่คุณต้องไปอ่านเพิ่มเพื่อเข้าใจหน้านี้ และไม่ยืนยันว่าทุกประโยคมีอยู่ในคอร์ส
- Tools of the Trade: Linux and SQL · Module 4: Databases and SQLVideo: Query databases with SQLCY4.M4.V3
- Tools of the Trade: Linux and SQL · Module 4: Databases and SQLVideo: Adedayo: SQL in cybersecurityCY4.M4.V4
03 / 03
การกรองด้วยเอสคิวแอลและเชลล์SQL versus Shell Filtering · 12.3.3
SQL กรองข้อมูลที่มี schema ส่วน shell มักกรองไฟล์หรือ stream ต้องเลือกตามแหล่งข้อมูล
ขอ engine ประมวลชุดข้อมูลตามโครงสร้าง Shell เรียกโปรแกรมและจัดการไฟล์หรือ process ทั้งสองเชื่อมกันได้แต่ไม่ใช่ภาษาเดียวกัน การ escape ค่าและสิทธิ์คนละชั้นต้องพิจารณาแยก
•SQL
ประมวลข้อมูลใน DB
•Shell
ประสานโปรแกรมและระบบ
•Boundary
ส่งข้อมูลและสิทธิ์ระหว่างเครื่องมือ
grep ค้นบรรทัด log ส่วน SELECT กรองแถวและ join ตาราง การเรียก DB ผ่าน shell ต้องไม่ใส่รหัสผ่านลง history และการส่ง input ไป ใช้ parameter ไม่ต่อข้อความเพียงเพราะอยู่ใน shell
ที่มาของหัวข้อนี้ · 1 รายการ
คำอธิบายและภาพในหน้านี้เรียบเรียงใหม่ ชื่อบทต้นทางด้านล่างใช้ตรวจที่มา ไม่ได้แทนเนื้อหาที่คุณต้องไปอ่านเพิ่มเพื่อเข้าใจหน้านี้ และไม่ยืนยันว่าทุกประโยคมีอยู่ในคอร์ส
- Tools of the Trade: Linux and SQL · Module 4: Databases and SQLReading: SQL filtering versus Linux filteringCY4.M4.R1
เชื่อมความเข้าใจ
นำศัพท์มาเชื่อมกับงาน
ตัวอย่างให้เห็นภาพ
LEFT JOIN ช่วยหาเครื่องที่มีในทะเบียน แต่ยังไม่มีรายการอัปเดตที่ตรงกัน
สิ่งที่บทนี้เชื่อมไว้
เมื่อข้อมูลกระจายหลายตาราง ต้องเลือกวิธีรวมให้ตรงคำถาม และตรวจจำนวนแถวที่เพิ่มขึ้น
ใช้ภาพกับตัวอย่างด้านบนเชื่อมหน้าที่ของแต่ละส่วน แล้วตรวจความเข้าใจด้วยการอธิบายเหตุผลของผลที่เห็น ก่อนเปิดบทถัดไป
บันทึกในเบราว์เซอร์นี้
บทเรียนที่เกี่ยวข้องและที่มาของบทอ่าน
transcript ที่เคยใช้ตรวจแนวคิดพื้นฐานและตัวอย่างบางส่วน: T0272 · Tools of the Trade Linux and SQL คำอธิบายเป็นการเขียนใหม่ ไม่ใช่การแปลครบทุกประโยค ภาพและตัวอย่างเป็นการเรียบเรียงเพื่อเชื่อมความเข้าใจ
คำอธิบายหน้านี้เขียนใหม่ตามหัวข้อใน Atlas รายการด้านล่างเป็นชื่อ Video/Reading ที่เคยจับคู่ไว้ แสดงไว้ให้ตรวจที่มาของชื่อหัวข้อ และไม่รับรองว่าคอร์สสอนทุกส่วนของคำอธิบายนี้