บทความจากซีรีส์เกี่ยวกับการสร้างโปรเจ็กต์ Java (ลิงก์ไปยังเนื้อหาอื่นอยู่ท้ายสุด) เป้าหมายคือการวิเคราะห์เทคโนโลยีที่สำคัญ ผลลัพธ์คือการเขียนบอทโทรเลข ส่วนนี้ประกอบด้วยการวิเคราะห์งานขั้นสุดท้ายในฐานข้อมูล !["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ตอนที่ 6 - การตรวจสอบงานสุดท้าย - 1]()
สวัสดีผู้อ่านที่รัก วันนี้เราจะวิเคราะห์งานจากบทความล่าสุดในฐานข้อมูล เป็นเรื่องที่น่าสนใจเพราะมีไว้สำหรับการสัมภาษณ์ระดับกลาง นั่นคือหลังจากงานนี้คุณสามารถไปสัมภาษณ์ได้แล้วและคุณจะผ่านอย่างน้อยส่วนหนึ่งของสิ่งที่เกี่ยวข้องกับฐานข้อมูลเชิงสัมพันธ์ได้สำเร็จ ฉันรู้ว่าบทความนี้มีความจำเป็นเพียงใด ดังนั้นฉันจะนำประสบการณ์ทั้งหมดที่มีมาทำให้บทความนี้มีประโยชน์และน่าสนใจ และถ้าคุณไม่เผลอหลับกลางบทความแสดงว่าบรรลุเป้าหมายแล้ว ฉันจะไม่ทำซ้ำงานทั้งหมด: ฉันจะอ้างอิงก่อนทำงานแต่ละงานให้เสร็จโดยขีดเส้นใต้เป็นตัวเอียง ฉันคาดหวังว่าทุกคนที่อ่านบทความนี้จะเรียกใช้แบบสอบถามทั้งหมดในฐานข้อมูลของตนและได้รับสิ่งเดียวกัน อันจะนำมาซึ่งประโยชน์สูงสุดแก่ธุรกิจ และจะมีความสุขขึ้นอีกหน่อยจากการคิดว่าได้ช่วยใครสักคนในงานยากๆ ของเรา)
แบบฝึกหัดที่ 1
เขียนสคริปต์ SQL เพื่อสร้างตาราง 'นักเรียน' โดยมีฟิลด์ต่อไปนี้: id (คีย์หลัก), ชื่อ, นามสกุล, อีเมล (ไม่ซ้ำกัน) เราได้ทำสิ่งนี้ไปแล้ว ดังนั้นจึงไม่น่ามีปัญหาใดๆ ในสคริปต์ คุณจะต้องระบุคีย์หลักและฟิลด์เฉพาะที่แตกต่างจากคีย์หลัก ขั้นแรก เรามาสร้างฐานข้อมูลใหม่สำหรับงานนี้:
$ CREATE DATABASE Final_task; และลองใช้ฐานข้อมูลนี้:
$ USE Final_task; เมื่อสภาพแวดล้อมได้รับการตั้งค่าและพร้อมที่จะรันงาน เราสามารถเขียนสคริปต์ต่อไปนี้:
$ CREATE TABLE Student ( id INT AUTO_INCREMENT, name VARCHAR(40), Last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( รหัส), UNIQUE (อีเมล ) ); จนถึงขณะนี้ยังไม่มีอะไรใหม่เมื่อเทียบกับสิ่งที่เราได้ผ่านมาแล้ว ความคิดเห็นใดๆ ก็ไม่จำเป็น เรามาต่อกันดีกว่า
ภารกิจที่ 2-3
เขียนสคริปต์ SQL เพื่อสร้างตาราง 'Book' โดยมีฟิลด์ต่อไปนี้: id, title (id + title = คีย์หลัก) เชื่อมโยง 'นักเรียน' และ 'หนังสือ' กับความสัมพันธ์ 'หนังสือ' แบบหนึ่งต่อกลุ่มของ 'นักเรียน' มารวมงานสองงานเป็นงานเดียวเพื่อให้รวดเร็วและสะดวกยิ่งขึ้น ฉันได้กล่าวถึงวิธีการเพิ่มคีย์ต่างประเทศแยกต่างหากในบทความก่อนหน้านี้แล้ว นอกจากนี้ เราต้องจำไว้ว่าเราเชื่อมโยงกันอย่างไรและผ่านอะไร บทความก่อนหน้านี้จะช่วยคุณได้และนี่คือสคริปต์:
$ CREATE TABLE book ( id INT, title VARCHAR(100), Student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES Student (id ) ); ด้วยวิธีง่ายๆ นี้ เราได้เพิ่มคีย์ผสมสำหรับตาราง
PRIMARY KEY (id, title) ของเรา ซึ่งตอนนี้คีย์จะเป็นคู่กันพอดี ซึ่งหมายความว่าสามารถมีค่าฟิลด์รหัสที่เหมือนกันได้มากกว่าหนึ่งค่าในตาราง และเหมือนกันทุกประการสำหรับชื่อเรื่อง
ภารกิจที่ 4
เขียนสคริปต์ SQL เพื่อสร้างตาราง 'ครู' โดยมีฟิลด์ต่อไปนี้: id (คีย์หลัก), ชื่อ, นามสกุล, อีเมล (ไม่ซ้ำกัน), หัวเรื่อง เรายังคงเตรียมฐานข้อมูลของเราสำหรับการสืบค้น สร้างตารางครู:
$ CREATE TABLE teacher( id INT AUTO_INCREMENT, name VARCHAR(30), Last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( id), UNIQUE (อีเมล) ); จนถึงตอนนี้ก็ไม่ใช่เรื่องยากใช่ไหม? สามภารกิจจบลงแล้ว!
ภารกิจที่ 5
เชื่อมโยง 'นักเรียน' และ 'ครู' กับความสัมพันธ์ 'นักเรียน' แบบกลุ่มต่อกลุ่มครู ตอนนี้มันน่าสนใจยิ่งขึ้น! เราเพิ่งพูดถึงเรื่องนี้ครั้งสุดท้าย ฉันขอเตือนคุณถึงสิ่งที่จำเป็นสำหรับการบรรลุเป้าหมาย: คุณต้องสร้างตารางกลางที่จะจัดเก็บคู่นักเรียนและครู ด้วยความช่วยเหลือนี้ คุณจะสามารถสร้างความสัมพันธ์แบบกลุ่มต่อกลุ่มได้ ดังนั้นเรามาสร้างตาราง
students_x_techers กันดี กว่า วิธีการตั้ง ชื่อ เป็นแบบเปิดและอาจเป็น:
Student_teacher $ สร้างตาราง students_x_teachers ( Student_id INT ไม่เป็นโมฆะ, teacher_id INT ไม่เป็นโมฆะ, คีย์หลัก (student_id, teacher_id), คีย์ต่างประเทศ (student_id) การอ้างอิง นักเรียน (id), คีย์ต่างประเทศ (teacher_id) การอ้างอิง ครู (id) ); อย่างที่คุณเห็นทุกอย่างเสร็จสิ้นอย่างชัดเจนและสอดคล้องกัน เรามีคีย์ผสมสำหรับคีย์ต่างประเทศสองคีย์: Student_id และ teacher_id ทำไมต้องใช้คีย์ต่างประเทศด้วย? เพื่อให้เรามั่นใจว่ามีบันทึกในตารางนักเรียนและครูสำหรับคู่ที่ถูกบันทึก
ภารกิจที่ 6
เลือก 'Student' ที่มี 'oro' ในนามสกุล เช่น 'Sidorov', 'Voronovsky' เพื่อให้น่าสนใจและเป็นภาพสำหรับเรา ฉันขอแนะนำให้เพิ่มนักเรียนหลายๆ คนก่อน เพื่อให้บางคนเหมาะสมกับคำขอนี้ และบางคนไม่เหมาะกับคำขอนี้ ดังนั้นเรามาเขียนผู้ที่
ควรรวมไว้อันเป็นผลมาจากคำขอ:
$ INSERT INTO นักเรียน (ชื่อ, นามสกุล, อีเมล) ค่านิยม ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO นักเรียน (ชื่อ, นามสกุล, อีเมล) ค่า ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); และผู้ที่ไม่ควรเข้าไป:
$ INSERT INTO นักเรียน (ชื่อ, นามสกุล, อีเมล) ค่านิยม ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO นักเรียน (ชื่อ, นามสกุล, อีเมล) ค่า ('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); ตรวจสอบผลลัพธ์ดูรายการข้อมูลในตารางนักเรียน:
$ SELECT * FROM นักเรียน; และเราได้รับ:
!["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ตอนที่ 6 - การตรวจสอบงานสุดท้าย - 2]()
มีทั้งหมดสี่บันทึก สองรายการควรพอดี และสองรายการไม่ควรพอดี หลังจากเตรียมข้อมูลทั้งหมดสำหรับคำขอแล้ว เราสามารถส่งคำขอสำหรับงานได้:
$ SELECT * FROM Student WHERE Last_name LIKE '%oro%'; !["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ส่วนที่ 6 - การตรวจสอบงานสุดท้าย - 3]()
เป็นผลให้อีวานและนิโคไลผ่านรายการ
ภารกิจที่ 7
งานต่อไปที่เราอ่าน:
เลือกจากตาราง 'นักเรียน' นามสกุลทั้งหมด ('last_name') และจำนวนการทำซ้ำ พิจารณาว่ามีชื่อซ้ำกันในฐานข้อมูล เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
นามสกุล |
ปริมาณ |
เปตรอฟ |
15 |
อีวานอฟ |
12 |
ซิโดรอฟ |
3 |
เพื่อความชัดเจน คุณต้องเพิ่มข้อมูลเพิ่มเติม โดยไม่ต้องกังวลใจอีกต่อไป มาเพิ่ม Petrovs, Ivanovs และ Sidorovs ที่ไม่รู้จักเครือญาติของพวกเขา ;) ฉันจะไม่สร้างที่อยู่อีเมล ฉันจะแยกที่อยู่อีเมลออกจากรายการใหม่เท่านั้น เรียกใช้คำสั่งต่อไปนี้ 12 ครั้ง:
$ INSERT INTO นักเรียน (ชื่อ, นามสกุล) VALUES ('Ivan', 'Ivanov'); มาเพิ่ม 15 Petrovs:
$ INSERT INTO นักเรียน (ชื่อ, นามสกุล) VALUES ('Petr', 'Petrov'); และ Sidorov สองตัว (เรามีอยู่แล้ว))):
$ INSERT INTO นักเรียน (ชื่อ, นามสกุล) ค่านิยม ('Sidor', 'Sidorov'); ตอนนี้ข้อมูลพร้อมแล้ว หากต้องการรับข้อมูลดังกล่าว คุณต้องจัดกลุ่ม หากต้องการจัดกลุ่ม คุณต้องใช้ตัวดำเนินการ Group By และคุณต้องดำเนินการนี้โดยใช้ช่อง Last_name คุณยังสังเกตได้ว่าจำนวนการทำซ้ำถูกกำหนดให้เป็น
quantityและที่นี่คุณต้องจำวิธีสร้างนามแฝงใน SQL ด้วย:
$ SELECT Last_name, COUNT(*) as quantity FROM Student GROUP BY Last_name ORDER BY COUNT(*) DESC ; !["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ส่วนที่ 6 - การตรวจสอบงานสุดท้าย - 4]()
ดังนั้นฉันจึงทำมากเกินไปกับ Petrovs - กลายเป็น 16))
ภารกิจที่ 8
เงื่อนไข:
เลือกชื่อซ้ำมากที่สุด 3 อันดับแรกจาก 'Student' เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
ชื่อ |
ปริมาณ |
อเล็กซานเดอร์ |
27 |
เซอร์เกย์ |
10 |
ปีเตอร์ |
7 |
โอ้ เพื่อจุดประสงค์นี้ เรามี Ivans, Peters และ Sidors อยู่แล้ว ดังนั้นจึงไม่จำเป็นต้องเพิ่มเข้าไป เรารู้วิธีจัดเรียงแล้ว สิ่งเดียวที่เราไม่ได้พูดถึงในวันนี้คือวิธีเลือกบันทึกจำนวนหนึ่ง สิ่งนี้ได้ปรากฏในวิธีแก้ไขปัญหาฐานข้อมูลก่อนหน้านี้แล้ว ส่วนใครที่ยังไม่ได้อ่านก็อ่านได้เลย ส่วนที่เหลือ เรามาตรงประเด็นกันดีกว่า:
$ SELECT name, COUNT(*) as quantity FROM students GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; !["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ส่วนที่ 6 - การตรวจสอบงานสุดท้าย - 5]()
ดังที่เห็นได้จากแบบสอบถาม หากคุณทราบลำดับของตัวดำเนินการในแบบสอบถาม SELECT ก็จะไม่มีปัญหาในการดำเนินการแบบสอบถามดังกล่าว และงานนี้ก็ยังขึ้นอยู่กับเรา และความรู้ที่นำเสนอไปก่อนหน้านี้ก็เพียงพอที่จะแก้ไขปัญหานี้ได้
ภารกิจที่ 9
เงื่อนไขงาน:
เลือก 'นักเรียน' ที่มี 'หนังสือ' มากที่สุดและ 'ครู' ที่เกี่ยวข้อง เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
นามสกุลอาจารย์ |
นามสกุลของนักเรียน |
ปริมาณหนังสือ |
เปตรอฟ |
ซิโดรอฟ |
7 |
อีวานอฟ |
สมิธ |
5 |
เปตรอฟ |
คันกะวะ |
2 |
สรุปงานนี้ยากกว่าครั้งก่อนชัดเจนใช่ไหม? ไม่น่าแปลกใจเลยที่มันมีกลิ่นเหมือน join... และมากกว่าหนึ่ง) อันดับแรก เราต้องเข้าใจว่าต้องทำอย่างไร... ชัดเจนว่าปริมาณของ Book ต้องมีการจัดกลุ่ม แต่อะไร? และทำไมเราจึงควรจัดกลุ่มพวกมัน? แบบสอบถามเกี่ยวข้องกับสามตาราง การจัดกลุ่ม และการเรียงลำดับ เมื่อพิจารณาจากข้อเท็จจริงที่ว่าบันทึกที่ไม่มีหนังสือไม่แสดง หมายความว่าคุณต้องเข้าร่วม INNER JOIN เราจะทำการร้องขอ LEFT JOIN เพื่อที่จะไม่มีปัญหากับเรื่องนี้ และมีหลายทางเลือก สิ่งแรกที่เราทำคือรวมสามตารางไว้ในระเบียนเดียว ต่อไป เราจะจัดกลุ่มตามนักเรียนและเพิ่มชื่อครูเข้าไป เราจะเลือกอะไร? ชื่ออาจารย์ นักเรียน และจำนวนหนังสือ มาเพิ่มข้อมูลสำหรับคำขอ:
- ครูสามคน;
- หนังสือสิบเล่ม;
- เชื่อมต่อนักเรียนสองคนกับครูสามคน
ครูสามคน
$ INSERT INTO ค่าครู(last_name) ('Matvienko'); $ INSERT INTO ค่าครู (last_name) ('Shevchenko'); $ INSERT INTO ค่าครู (last_name) ('Vasilenko');
10 เล่ม
ฉันจะรับบัตรประจำตัวของนักเรียนคนที่ 1 และ 2 ฉันจะแนบหนังสือไปกับพวกเขา เนื่องจากไม่ได้ตั้งค่า AUTO_INCREMENT เพื่อไม่ให้เขียน ID ใหม่ในแต่ละครั้ง คุณต้องดำเนินการดังต่อไปนี้:
$ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; ถัดไป เพิ่มหนังสือสำหรับนักเรียนคนแรก:
$ INSERT INTO book (title, Student_id) VALUES('book1', 1); $ INSERT INTO หนังสือ (ชื่อ, Student_id) VALUES('book2', 1); $ INSERT INTO หนังสือ (ชื่อ, Student_id) VALUES('book3', 1); $ INSERT INTO หนังสือ (ชื่อ, Student_id) VALUES('book4', 1); $ INSERT INTO หนังสือ (ชื่อ, Student_id) VALUES('book5', 1); $ INSERT INTO หนังสือ (ชื่อ, Student_id) VALUES('book6', 1); และหนังสือสำหรับนักเรียนคนที่สอง:
$ INSERT INTO book (title, Student_id) VALUES('book7', 2); $ INSERT INTO หนังสือ (ชื่อ, Student_id) VALUES('book8', 2); $ INSERT INTO หนังสือ (ชื่อ, Student_id) VALUES('book9', 2); $ INSERT INTO หนังสือ (ชื่อ, Student_id) VALUES('book10', 2);
การเชื่อมต่อระหว่างครูกับนักเรียน
เมื่อต้องการทำเช่นนี้ ให้เพิ่ม students_x_teachers ลงในตาราง:
$ INSERT INTO students_x_teachers VALUES (1,1); $INSERT INTO students_x_teachers VALUES(1,2); $INSERT INTO students_x_teachers VALUES(2,3);
มาดำเนินการตามคำขอกัน
เราทำขั้นตอนแรก - เราเชื่อมโยงสามตารางเป็นหนึ่งบันทึก:
$ SELECT * FROM teacher tch INNER JOIN students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN นักเรียน st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; !["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ส่วนที่ 6 - การตรวจสอบงานสุดท้าย - 6]()
แน่นอนว่าเรายังไม่มีบันทึกใดๆ อย่างไรก็ตาม เราจะเห็นว่าเราได้เชื่อมต่อสามตารางสำเร็จแล้ว ตอนนี้เราเพิ่มการจัดกลุ่มหนังสือ การเรียงลำดับ และฟิลด์ที่เราต้องการ:
$ SELECT tch.last_name, st.last_name, st.id, COUNT(*) เป็นหนังสือจากนักเรียน st INNER JOIN book b ON st.id = b.student_id INNER เข้าร่วม students_x_teachers st_x_tch ON st.id = st_x_tch.student_id เข้าร่วมภายใน ครู tch ON tch.id = st_x_tch.teacher_id จัดกลุ่มตาม st.id เรียงตามหนังสือ DESC; แต่เราได้รับข้อผิดพลาดใน SQL และคำตอบต่อไปนี้:
รหัสข้อผิดพลาด: 1055 นิพจน์ #1 ของรายการ SELECT ไม่อยู่ใน GROUP BY clause และมีคอลัมน์ 'final_task.tch.last_name' ที่ไม่ได้รวมกลุ่ม ซึ่งไม่ได้ขึ้นอยู่กับฟังก์ชันการทำงานตามคอลัมน์ใน GROUP BY ข้อ ใช้องค์ประกอบเหล่านี้ไม่ได้ผลเนื่องจากมีความสัมพันธ์แบบกลุ่มต่อกลุ่มระหว่างครูและนักเรียน และเป็นความจริง: เราไม่สามารถรับครูเพียงคนเดียวต่อนักเรียนหนึ่งคนได้ งั้นไปทางอื่นกันเถอะ ลองใช้สิ่งที่เรียกว่า View SQL แนวคิดคืออะไร: เราสร้างมุมมองแยกต่างหากซึ่งเป็นตารางใหม่พร้อมการจัดกลุ่มที่เราต้องการแล้ว และในตารางนี้เราจะเพิ่มชื่อครูที่จำเป็น แต่เราคำนึงถึงความจริงที่ว่าอาจมีครูมากกว่าหนึ่งคนจึงจะมีการทำซ้ำ สร้างมุมมอง:
$ CREATE VIEW นักเรียนหนังสือเป็น SELECT st.last_name,st.id,COUNT(*) เป็นหนังสือจากนักเรียน st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY books DESC; ต่อไป เราทำงานกับมุมมองนี้เป็นตารางธรรมดาที่มีสามฟิลด์: นามสกุลนักเรียน, Student_id และจำนวนหนังสือ ตาม ID ของนักเรียน เรายังสามารถเพิ่มครูผ่านการเข้าร่วมสองรายการ:
$ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' จากหนังสือนักเรียน sbw INNER JOIN students_x_teachers stch ON sbw id = stch.student_id เข้าร่วมภายใน ครู tch ON tch.id = stch.teacher_id; และตอนนี้ผลลัพธ์จะเป็น:
!["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ส่วนที่ 6 - การตรวจสอบงานสุดท้าย - 7]()
ห๊ะ! นี่คือคำขอใช่ไหม?) มันเป็นไปตามที่คาดไว้: นักเรียนที่มี id=1 มีหนังสือหกเล่มและครูสองคน และนักเรียนที่มี id=2 มีหนังสือสี่เล่มและครูหนึ่งคน
ภารกิจที่ 10
เงื่อนไข:
เลือก 'ครู' ที่มี 'หนังสือ' มากที่สุดในบรรดา 'นักเรียน' ทั้งหมดของเขา เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
นามสกุลอาจารย์ |
ปริมาณหนังสือ |
เปตรอฟ |
9 |
อีวานอฟ |
5 |
ที่นี่เราสามารถใช้คำขอสำเร็จรูปจากงานก่อนหน้าได้ เราต้องเปลี่ยนแปลงอะไรเกี่ยวกับเรื่องนี้? เรามีข้อมูลนี้แล้ว เราเพียงแค่ต้องเพิ่มกลุ่มอื่นและลบชื่อนักเรียนออกจากข้อมูลที่ส่งออก แต่ก่อนอื่น เรามาเพิ่มนักเรียนอีกหนึ่งคนให้กับครูเพื่อทำให้ผลลัพธ์น่าสนใจยิ่งขึ้น ในการดำเนินการนี้ เราเขียน:
$ INSERT INTO students_x_teachers VALUES (2, 1); และแบบสอบถามนั้นเอง:
$ SELECT tch.last_name as 'Teacher', SUM(sbw.books) เป็น 'Books' จากหนังสือนักเรียน sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch . teacher_id จัดกลุ่มตาม tch.id; เป็นผลให้เราได้รับ:
!["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ตอนที่ 6 - การตรวจสอบงานสุดท้าย - 8]()
ครูคนนั้น Vasilenko มีหนังสือ 10 เล่ม และ Shevchenko มี 6 เล่ม...)
ภารกิจที่ 11
เงื่อนไข:
เลือก 'ครู' ซึ่งมีจำนวน 'หนังสือ' สำหรับ 'นักเรียน' ทั้งหมดของเขาอยู่ระหว่าง 7 ถึง 11 เล่ม เรียงตามปริมาณจากมากไปน้อย มันควรมีลักษณะเช่นนี้:
นามสกุลอาจารย์ |
ปริมาณหนังสือ |
เปตรอฟ |
สิบเอ็ด |
ซิโดรอฟ |
9 |
อีวานอฟ |
7 |
นี่คือที่ที่เราจะใช้ HAVING เราพูดคุยเกี่ยวกับเขา คำขอจะเหมือนเดิมทุกประการ เพียงคุณเพิ่มเงื่อนไขว่าจำนวนหนังสือจะอยู่ในช่วงที่กำหนด และอย่างที่ผมบอกไปแล้วในบทความก่อนๆ เมื่อเราจำเป็นต้องทำการกรองระหว่างการจัดกลุ่มและ/หรือฟังก์ชันการรวม เราจำเป็นต้องใช้
HAVING :
$ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from หนังสือนักเรียน sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id จัดกลุ่มตาม tch.id มี SUM(sbw.books) > 6 และ SUM(sbw.books) < 12; ฉันได้เน้นส่วนที่ฉันเพิ่ม และในความเป็นจริงผลลัพธ์ที่คาดหวัง:
!["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ตอนที่ 6 - การตรวจสอบงานสุดท้าย - 9]()
มีเพียงวาซิเลนโกเท่านั้นที่ผ่านรอบนี้))
ภารกิจที่ 12
เงื่อนไข:
พิมพ์ 'last_name' และ 'ชื่อ' ทั้งหมด ของ 'ครู' และ 'นักเรียน' ทั้งหมด พร้อมช่อง 'ประเภท' (นักเรียนหรือครู) จัดเรียงตามตัวอักษรตาม 'last_name' มันควรมีลักษณะเช่นนี้:
นามสกุล |
พิมพ์ |
อีวานอฟ |
นักเรียน |
คันกะวะ |
ครู |
สมิธ |
นักเรียน |
ซิโดรอฟ |
ครู |
เปตรอฟ |
ครู |
นั่นคือเราจำเป็นต้องรวมสองเอาต์พุตเข้าด้วยกัน และนี่คือสิ่งที่ UNION มีไว้เพื่อ กล่าวอีกนัยหนึ่ง เราจะนำบันทึกจากนักเรียนและครูมาพิมพ์ร่วมกัน:
$ SELECT Last_name, 'teacher' เป็นประเภทจากครู UNION ALL เลือก Last_name, 'student' เป็นประเภทจากนักเรียน ORDER BY Last_name; !["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ตอนที่ 6 - การตรวจสอบงานสุดท้าย - 10]()
และก็จะมีทั้งครูและนักเรียน ดูเหมือนว่าทุกอย่างจะง่าย แต่นี่คือเมื่อเราดูผลลัพธ์แล้ว ดังนั้นคุณต้องเดาสองสิ่ง
ภารกิจที่ 13
เงื่อนไข:
เพิ่มคอลัมน์ 'อัตรา' ลงในตาราง 'นักเรียน' ที่มีอยู่ ซึ่งจะจัดเก็บหลักสูตรที่นักเรียนอยู่ในปัจจุบัน (ค่าตัวเลขตั้งแต่ 1 ถึง 6) แก้ไขตาราง นักเรียนเพิ่มข้อจำกัด check_rate CHECK (อัตรา > 0 และอัตรา < 7); ที่นี่เรากำลังเพิ่มฟิลด์ผ่าน ALTER TABLE และ CHECK เพื่อตั้งค่าขีดจำกัดของฟิลด์นี้ตั้งแต่ 1 ถึง 6
ภารกิจที่ 14
เงื่อนไข:
รายการนี้ไม่จำเป็น แต่จะได้รับการพิจารณาเป็นพิเศษ เขียนฟังก์ชันที่จะอ่าน 'หนังสือ' ทั้งหมดและแสดง 'ชื่อ' ทั้งหมดโดยคั่นด้วยเครื่องหมายจุลภาค ที่นี่คุณเพียงแค่ต้องส่งคืนบรรทัดตามคำขอซึ่งจะมีชื่อหนังสือทั้งหมด ที่นี่ฉันต้อง google อีกครั้ง มีฟังก์ชั่นดังกล่าว -
GROUP_CONCATซึ่งทำได้ง่ายมาก:
$ SELECT GROUP_CONCAT(ชื่อ) จากหนังสือ; !["โครงการ Java จาก A ถึง Z": เราวิเคราะห์ฐานข้อมูลและภาษา SQL ตอนที่ 6 - การตรวจสอบงานสุดท้าย - 11]()
เท่านี้ก็เรียบร้อย...)) งานทั้ง 14 งานพร้อมแล้ว
ข้อสรุป
เอ่อ... มันไม่ง่ายเลย มันน่าสนใจ. งานนั้นคุ้มค่า ฉันมั่นใจมากกว่า ในขณะที่เรากำลังทำงานเหล่านี้ เราได้มองข้ามหลายสิ่งหลายอย่างที่ไม่เคยรู้มาก่อน:
- มุมมอง SQL
- GROUP_CONCAT
- ยูเนี่ยน
และอื่น ๆ ขอบคุณทุกคนที่มีพลังที่จะอ่านและทำซ้ำสิ่งที่ฉันทำ ใครจะรู้วิธีส่งคำขอได้ดีขึ้น - เขียนความคิดเห็นฉันจะอ่านอย่างแน่นอน)
GO TO FULL VERSION