Thích bài viết này? Chúng tôi đề nghị
Lấy hồ sơ từ nhiều bảng
Không có gì tốt khi đặt hồ sơ vào cơ sở dữ liệu trừ khi cuối cùng bạn lấy chúng và làm điều gì đó với chúng. Đó là mục đích của câu lệnh Chọn để giúp bạn nhận được dữ liệu của mình. Chọn có thể được sử dụng thường xuyên hơn bất kỳ ai khác trong ngôn ngữ SQL, nhưng nó cũng có thể là khó nhất; Các ràng buộc bạn sử dụng để chọn hàng có thể phức tạp tùy ý và có thể liên quan đến so sánh giữa các cột trong nhiều bảng.
Cú pháp cơ bản của câu lệnh Chọn trông như thế này:
SELECT selection_list # What columns to select FROM table_list # Where to select rows from WHERE primary_constraint # What conditions rows must satisfy GROUP BY grouping_columns # How to group results ORDER BY sorting_columns # How to sort results HAVING secondary_constraint # Secondary conditions rows must satisfy LIMIT count; # Limit on results
Tất cả mọi thứ trong cú pháp này là tùy chọn ngoại trừ từ chọn và phần select_list chỉ định những gì bạn muốn truy xuất. Một số cơ sở dữ liệu yêu cầu mệnh đề từ. MySQL không, cho phép bạn đánh giá các biểu thức mà không đề cập đến bất kỳ bảng nào:
SELECT SQRT[POW[3,2]+POW[4,2]];
Trong Chương 1, chúng tôi dành khá nhiều sự chú ý cho các câu lệnh chọn bảng đơn, tập trung chủ yếu vào danh sách cột đầu ra và nhóm, theo thứ tự, có, và hạn chế các điều khoản. Phần này bao gồm một khía cạnh của lựa chọn thường gây nhầm lẫn cho các chữ viết của viết thư; Đó là, chọn các câu lệnh truy xuất các bản ghi từ nhiều bảng. Chúng tôi sẽ thảo luận về các loại hỗ trợ tham gia MySQL, ý nghĩa của chúng và cách chỉ định chúng. Điều này sẽ giúp bạn sử dụng MySQL hiệu quả hơn bởi vì, trong nhiều trường hợp, vấn đề thực sự là tìm ra cách viết một truy vấn là xác định cách thích hợp để kết hợp các bảng với nhau.
Một vấn đề với việc sử dụng Chọn là khi bạn gặp phải một loại vấn đề mới, không phải lúc nào cũng dễ dàng để xem cách viết truy vấn chọn để giải quyết nó. Tuy nhiên, sau khi bạn tìm ra nó, bạn có thể sử dụng trải nghiệm đó khi bạn chạy qua các vấn đề tương tự trong tương lai. Chọn có lẽ là tuyên bố mà kinh nghiệm trong quá khứ đóng vai trò lớn nhất trong việc có thể sử dụng nó một cách hiệu quả, đơn giản là vì nhiều vấn đề tuyệt đối mà nó áp dụng.
Khi bạn có được kinh nghiệm, bạn sẽ có thể điều chỉnh tham gia dễ dàng hơn với các vấn đề mới và bạn sẽ thấy mình nghĩ những điều như "Ồ, vâng, đó là một trong những điều trái cùng còn lại" hoặc, "Aha, đó là một Tham gia ba chiều bị hạn chế bởi các cặp cột chính. " .
Nhiều ví dụ chứng minh cách sử dụng các hình thức hoạt động tham gia mà MySQL hỗ trợ sử dụng hai bảng sau, T1 và T2. Chúng nhỏ, làm cho chúng đủ đơn giản để có thể nhìn thấy hiệu ứng của từng loại tham gia:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
Sự tham gia tầm thường
Sự tham gia đơn giản nhất là sự tham gia tầm thường, trong đó chỉ có một bảng được đặt tên. Trong trường hợp này, các hàng được chọn từ bảng được đặt tên:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+
Một số người không xem xét hình thức chọn tham gia này và chỉ sử dụng thuật ngữ cho các câu lệnh chọn lấy bản ghi từ hai hoặc nhiều bảng. Tôi cho rằng đó là vấn đề quan điểm.
Tham gia đầy đủ
Nếu một câu lệnh Chọn tên nhiều bảng trong mệnh đề từ với các tên được phân tách bằng dấu phẩy, MySQL sẽ thực hiện tham gia đầy đủ. Ví dụ: nếu bạn tham gia T1 và T2 như sau, mỗi hàng trong T1 được kết hợp với mỗi hàng trong T2:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+
Một sự tham gia đầy đủ cũng được gọi là tham gia chéo vì mỗi hàng của mỗi bảng được giao với mỗi hàng trong mỗi bảng khác để tạo ra tất cả các kết hợp có thể. Điều này còn được gọi là sản phẩm Cartesian. Tham gia các bảng theo cách này có khả năng tạo ra một số lượng rất lớn các hàng vì số lượng hàng có thể là sản phẩm của số lượng hàng trong mỗi bảng. Kết nối đầy đủ giữa ba bảng có chứa 100, 200 và 300 hàng, tương ứng, có thể trả về 100x200x300 = 6 triệu hàng. Đó là rất nhiều hàng, mặc dù các bảng riêng lẻ nhỏ. Trong các trường hợp như thế này, một mệnh đề WHERE thường sẽ được sử dụng để giảm kết quả được đặt ở kích thước dễ quản lý hơn.
Nếu bạn thêm một mệnh đề WHERe gây ra các bảng được khớp trên các giá trị của một số cột nhất định, thì sự tham gia sẽ trở thành một trong các bảng công bằng vì bạn chỉ chọn các hàng có giá trị bằng nhau trong các cột được chỉ định:
mysql> SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 2 | b | 2 | c | | 3 | c | 3 | b | +----+----+----+----+
Các loại tham gia tham gia và tham gia chéo tương đương với toán tử tham gia ',' [dấu phẩy]. Ví dụ, các câu sau đều giống nhau:
SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2; SELECT t1.*, t2.* FROM t1 JOIN t2 WHERE t1.i1 = t2.i2; SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 WHERE t1.i1 = t2.i2;
Thông thường, trình tối ưu hóa MySQL tự miễn phí để xác định thứ tự để quét các bảng để truy xuất các hàng nhanh nhất. Thỉnh thoảng, trình tối ưu hóa sẽ đưa ra một lựa chọn không tối ưu. Nếu bạn thấy điều này xảy ra, bạn có thể ghi đè lựa chọn của trình tối ưu hóa bằng cách sử dụng từ khóa thẳng_join. Một lần tham gia được thực hiện với Straight_join giống như tham gia chéo nhưng buộc các bảng phải được tham gia theo thứ tự có tên trong mệnh đề từ.
Straight_join có thể được chỉ định tại hai điểm trong một câu lệnh Chọn. Bạn có thể chỉ định nó giữa từ khóa chọn và danh sách lựa chọn để có hiệu ứng toàn cầu trên tất cả các tham gia chéo trong câu lệnh hoặc bạn có thể chỉ định nó trong mệnh đề từ. Hai câu sau đây là tương đương:
SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ; SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;
Tài liệu tham khảo cột đủ điều kiện
Các tham chiếu đến các cột bảng trong suốt câu lệnh Chọn phải giải quyết rõ ràng với một bảng có tên trong mệnh đề từ. Nếu chỉ có một bảng được đặt tên, không có sự mơ hồ vì tất cả các cột phải là cột của bảng đó. Nếu nhiều bảng được đặt tên, bất kỳ tên cột nào chỉ xuất hiện trong một bảng là tương tự không rõ ràng. Tuy nhiên, nếu một tên cột xuất hiện trong nhiều bảng, các tham chiếu đến cột phải đủ điều kiện bằng tên bảng bằng cách sử dụng cú pháp TBL_NAME.COL_NAME để chỉ định bảng nào của bạn. Giả sử một bảng MyTBL1 chứa các cột A và B và bảng MyTBL2 chứa các cột B và C. Trong trường hợp này, các tham chiếu đến các cột A hoặc C không rõ ràng, nhưng các tham chiếu đến B phải đủ điều kiện là mytbl1.b hoặc mytbl2.b:
Chọn a, mytbl1.b, mytbl2.b, c từ mytbl1, mytbl2 ...;
Đôi khi một vòng loại tên bảng không đủ để giải quyết một tham chiếu cột. Ví dụ: nếu bạn tham gia một bảng vào chính nó, bạn sẽ sử dụng nó nhiều lần trong truy vấn và nó không giúp đủ điều kiện một tên cột với tên bảng. Trong trường hợp này, các bí danh bảng rất hữu ích cho việc truyền đạt ý định của bạn. Bạn có thể gán bí danh cho bất kỳ trường hợp nào của bảng và tham khảo các cột từ trường hợp đó là bí danh_name.col_name. Truy vấn sau sẽ kết hợp một bảng cho chính nó, nhưng gán một bí danh cho một trường hợp của bảng để cho phép các tham chiếu cột được chỉ định rõ ràng:
Chọn mytbl.col1, m.col2 từ mytbl, mytbl dưới dạng m trong đó mytbl.col1> m.col1;
Tham gia trái và phải
Một công bằng chỉ hiển thị các hàng trong đó một trận đấu có thể được tìm thấy trong cả hai bảng. Các trận đấu bên trái và phải cũng hiển thị, nhưng cũng hiển thị các hàng trong một bảng không có khớp trong bảng khác. Các ví dụ trong phần này sử dụng tham gia bên trái, xác định các hàng trong bảng bên trái không khớp với bảng bên phải. Tham gia bên phải là như nhau ngoại trừ vai trò của các bảng bị đảo ngược. [Tham gia bên phải chỉ có sẵn kể từ MySQL 3.23.25.]
Một tham gia bên trái các hoạt động như thế này: Bạn chỉ định các cột được sử dụng để khớp các hàng trong hai bảng. Khi một hàng từ bảng bên trái khớp với một hàng từ bảng bên phải, nội dung của các hàng được chọn làm hàng đầu ra. Khi một hàng ở bảng bên trái không có khớp, nó vẫn được chọn cho đầu ra, nhưng được nối với hàng "giả" từ bảng bên phải trong đó tất cả các cột đã được đặt thành NULL. Nói cách khác, một tham gia bên trái buộc kết quả được đặt để chứa một hàng cho mỗi hàng trong bảng bên trái cho dù có phù hợp với nó trong bảng bên phải hay không. Các hàng không có khớp có thể được xác định bởi thực tế là tất cả các cột từ bảng bên phải là null.
Hãy xem xét một lần nữa hai bảng của chúng tôi, T1 và T2:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
Nếu chúng ta sử dụng tham gia chéo để khớp các bảng này trên T1.I1 và T2.I2, chúng ta sẽ chỉ nhận được đầu ra cho các giá trị 2 và 3, xuất hiện trong cả hai bảng:
mysql> SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 2 | b | 2 | c | | 3 | c | 3 | b | +----+----+----+----+
Một tham gia bên trái tạo ra đầu ra cho mỗi hàng trong T1, cho dù T2 có khớp với nó hay không. Để viết một lần tham gia bên trái, hãy đặt tên cho các bảng có tham gia bên trái ở giữa [thay vì dấu phẩy] và chỉ định điều kiện khớp bằng mệnh đề ON [thay vì mệnh đề WHERE]:
SELECT SQRT[POW[3,2]+POW[4,2]];0
Bây giờ có một hàng đầu ra ngay cả đối với giá trị 1, không có khớp trong T2.
Tham gia bên trái đặc biệt hữu ích khi bạn chỉ muốn tìm những hàng bảng bên trái không thể so sánh với bảng bên phải. Thực hiện điều này bằng cách thêm một mệnh đề WHERE tìm kiếm các hàng trong bảng phù hợp có giá trị null, nói cách khác, các hàng trong một bảng bị thiếu từ trên khác:
SELECT SQRT[POW[3,2]+POW[4,2]];1
Thông thường, những gì bạn thực sự theo sau là các giá trị chưa từng có trong bảng bên trái. Các cột null từ bảng bên phải không có hứng thú với mục đích hiển thị, vì vậy bạn sẽ không bận tâm đặt tên cho chúng trong danh sách cột đầu ra:
SELECT SQRT[POW[3,2]+POW[4,2]];2
Tham gia trái thực sự cho phép các điều kiện phù hợp được chỉ định hai cách. Trên là một trong số này; Nó có thể được sử dụng cho dù các cột bạn tham gia có cùng tên hay không:
SELECT SQRT[POW[3,2]+POW[4,2]];3
Cú pháp khác liên quan đến một mệnh đề sử dụng []; Điều này tương tự trong khái niệm với ON, nhưng tên của cột hoặc cột được nối phải giống nhau trong mỗi bảng. Ví dụ: truy vấn sau tham gia mytbl1.b với mytbl2.b:
SELECT SQRT[POW[3,2]+POW[4,2]];4
Tham gia trái có một vài từ đồng nghĩa và biến thể. Tham gia bên ngoài còn lại là một từ đồng nghĩa cho tham gia bên trái. Ngoài ra còn có một ký hiệu kiểu ODBC cho tham gia bên trái mà MySQL chấp nhận [OJ có nghĩa là "tham gia bên ngoài"]:
SELECT SQRT[POW[3,2]+POW[4,2]];5
Tham gia bên trái tự nhiên tương tự như tham gia bên trái; Nó thực hiện tham gia bên trái, phù hợp với tất cả các cột có cùng tên ở các bảng bên trái và phải.
Một điều cần chú ý với tham gia bên trái là nếu các cột mà bạn tham gia không được tuyên bố là không phải là null, bạn có thể nhận được các hàng có vấn đề trong kết quả. Ví dụ: nếu bảng bên phải chứa các cột có giá trị null, bạn sẽ không thể phân biệt các giá trị null đó với các giá trị null xác định các hàng chưa từng có.
Như đã đề cập, Left Join là hữu ích để trả lời "những giá trị nào bị thiếu?" câu hỏi. Khi bạn muốn biết giá trị nào trong một bảng không có trong một bảng khác, bạn sử dụng tham gia bên trái trên hai bảng và tìm kiếm các hàng trong đó NULL được chọn từ bảng thứ hai. Hãy xem xét một ví dụ phức tạp hơn về loại vấn đề này so với trường hợp được hiển thị trước đó bằng T1 và T2.
Đối với dự án giữ lớp lần đầu tiên được đề cập trong Chương 1, chúng tôi có một bảng học sinh liệt kê bảng học sinh, một bảng sự kiện liệt kê các sự kiện đã xảy ra và điểm số bảng điểm cho mỗi học sinh cho mỗi sự kiện. Tuy nhiên, nếu một học sinh bị bệnh vào ngày kiểm tra hoặc bài kiểm tra, bảng điểm sẽ không có bất kỳ điểm nào cho học sinh cho sự kiện đó, vì vậy một bài kiểm tra hoặc bài kiểm tra trang điểm nên được đưa ra. Làm thế nào để chúng tôi tìm thấy những hồ sơ còn thiếu này để chúng tôi có thể đảm bảo rằng những sinh viên đó trang điểm?
Vấn đề là xác định học sinh nào không có điểm cho một sự kiện nhất định và làm điều này cho mỗi sự kiện. Một cách khác để nói điều này là chúng tôi muốn tìm ra sự kết hợp của học sinh và sự kiện không được thể hiện trong bảng điểm. Từ ngữ "giá trị nào không có" này là một mẹo mà chúng tôi muốn tham gia bên trái. Tuy nhiên, sự tham gia không đơn giản như trong ví dụ trước, bởi vì chúng tôi không chỉ tìm kiếm các giá trị không có trong một cột duy nhất; Chúng tôi đang tìm kiếm một sự kết hợp hai cột. Các kết hợp chúng tôi muốn là tất cả các kết hợp sinh viên/sự kiện, được sản xuất bằng cách vượt qua bảng sinh viên với bảng sự kiện:
SELECT SQRT[POW[3,2]+POW[4,2]];6
Sau đó, chúng tôi lấy kết quả của sự tham gia đó và thực hiện tham gia bên trái với bảng điểm để tìm các trận đấu:
SELECT SQRT[POW[3,2]+POW[4,2]];7
Lưu ý rằng mệnh đề ON cho phép các hàng trong bảng điểm được nối theo các trận đấu trong các bảng khác nhau. Đó là chìa khóa để giải quyết vấn đề này. Các tham gia bên trái buộc một hàng được tạo cho mỗi hàng được tạo ra bởi sự tham gia chéo của các bảng sinh viên và sự kiện, ngay cả khi không có bản ghi bảng điểm tương ứng. Kết quả đặt các hàng cho các bản ghi điểm còn thiếu này có thể được xác định bởi thực tế là các cột từ bảng điểm sẽ không có giá trị. Chúng ta có thể chọn các bản ghi này trong mệnh đề WHERE. Bất kỳ cột nào từ bảng điểm sẽ làm, nhưng vì chúng tôi đang tìm kiếm điểm số bị thiếu, nên có thể rõ ràng nhất về mặt khái niệm để kiểm tra cột điểm:
SELECT SQRT[POW[3,2]+POW[4,2]];8
Chúng ta có thể đặt kết quả theo thứ tự bằng cách sử dụng mệnh đề theo đơn đặt hàng. Hai thứ tự hợp lý nhất là theo sự kiện cho mỗi học sinh hoặc bởi sinh viên mỗi sự kiện. Tôi sẽ chọn cái đầu tiên:
SELECT SQRT[POW[3,2]+POW[4,2]];9
Bây giờ tất cả những gì chúng ta cần làm là đặt tên cho các cột chúng ta muốn thấy trong đầu ra và chúng ta đã hoàn thành. Đây là truy vấn cuối cùng:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+0
Chạy truy vấn tạo ra những kết quả sau:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+1
Đây là một điểm tinh tế. Đầu ra hiển thị ID sinh viên và ID sự kiện. Cột Student_id xuất hiện trong cả bảng học sinh và bảng điểm, vì vậy, lúc đầu bạn có thể nghĩ rằng danh sách lựa chọn có thể đặt tên cho một trong hai học sinh. student_id hoặc scord.student_id. Đó không phải là trường hợp vì toàn bộ cơ sở để có thể tìm thấy các hồ sơ mà chúng tôi quan tâm là tất cả các trường bảng điểm được trả lại dưới dạng null. Chọn điểm.student_id sẽ chỉ tạo ra một cột của các giá trị null trong đầu ra. Nguyên tắc tương tự áp dụng để quyết định cột Event_id nào để hiển thị. Nó xuất hiện trong cả hai bảng sự kiện và điểm số, nhưng truy vấn chọn sự kiện.Event_id vì các giá trị score.event_id sẽ luôn luôn không có.
Sử dụng phụ
Một trong những tính năng mà MySQL 4.1 giới thiệu là hỗ trợ SubSelect, đây là khả năng được chờ đợi từ lâu cho phép một truy vấn chọn được lồng bên trong khác. Sau đây là một ví dụ tìm kiếm ID cho các bản ghi sự kiện tương ứng với các thử nghiệm ['t'] và sử dụng chúng để chọn điểm cho các bài kiểm tra đó:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+2
Trong một số trường hợp, phần phụ có thể được viết lại dưới dạng tham gia. Tôi sẽ chỉ ra cách làm điều đó sau trong phần này. Bạn có thể tìm thấy các kỹ thuật viết lại SubSelect hữu ích nếu phiên bản MySQL của bạn trước 4.1.
Một tính năng liên quan mà MySQL hỗ trợ là khả năng xóa hoặc cập nhật các bản ghi trong một bảng dựa trên nội dung của một bảng khác. Ví dụ: bạn có thể muốn xóa các bản ghi trong một bảng không khớp với bất kỳ bản ghi nào trong một bản khác hoặc sao chép các giá trị từ các cột trong một bảng sang các cột trong một bảng khác. Các loại hoạt động này được thảo luận trong phần "Xóa nhiều bảng và cập nhật" sau trong chương này.
Có một số hình thức bạn có thể sử dụng để viết phần phụ; Phần này khảo sát chỉ một vài trong số họ.
Sử dụng một phần phụ để tạo ra một giá trị tham chiếu. Trong trường hợp này, bạn muốn chọn bên trong để xác định một giá trị duy nhất được sử dụng trong so sánh với lựa chọn bên ngoài. Ví dụ: để xác định điểm số cho bài kiểm tra diễn ra vào '2002-09-23', hãy sử dụng chọn bên trong để xác định ID sự kiện đố vui và sau đó khớp các bản ghi điểm với nó trong lựa chọn bên ngoài: In this case, you want the inner SELECT to identify a single value to be used in comparisons with the outer SELECT. For example, to identify the scores for the quiz that took place on '2002-09-23', use an inner SELECT to determine the quiz event ID, and then match score records against it in the outer SELECT:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
3Với hình thức SubSelect này, trong đó truy vấn bên trong được đi trước bởi một toán tử so sánh, điều cần thiết là tham gia bên trong tạo ra không nhiều hơn một giá trị [nghĩa là một hàng, một cột]. Nếu nó tạo ra nhiều giá trị, truy vấn sẽ thất bại. [Trong một số trường hợp, có thể phù hợp để đáp ứng hạn chế này bằng cách giới hạn kết quả truy vấn bên trong với giới hạn 1.]
Hình thức SubSelect này có thể tiện dụng cho các tình huống mà bạn sẽ bị cám dỗ sử dụng hàm tổng hợp trong mệnh đề WHERE. Ví dụ, để xác định tổng thống sinh ra đầu tiên, bạn có thể thử như sau:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
4Điều đó không hoạt động vì bạn không thể sử dụng các tập hợp ở nơi mệnh đề. .
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
5- Existsandnotexistssubselects. Các dạng con này hoạt động bằng cách truyền các giá trị từ truy vấn bên ngoài đến bên trong để xem liệu chúng có khớp với các điều kiện được chỉ định trong truy vấn bên trong hay không. Vì lý do này, bạn sẽ cần đủ điều kiện tên cột có tên bảng nếu chúng mơ hồ [xuất hiện trong nhiều bảng]. Tồn tại và không tồn tại các phần phụ rất hữu ích cho việc tìm kiếm các bản ghi trong một bảng khớp hoặc không khớp với các bản ghi trong một bảng khác. and NOT EXISTS subselects. These forms of subselects work by passing values from the outer query to the inner one to see whether they match the conditions specified in the inner query. For this reason, you'll need to qualify column names with table names if they are ambiguous [appear in more than one table]. EXISTS and NOT
EXISTS subselects are useful for finding records in one table that match or don't match records in another.
Tham khảo một lần nữa vào các bảng T1 và T2 của chúng tôi:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
Truy vấn sau đây xác định các khớp giữa các bảng, đó là các giá trị có mặt trong cả hai:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
7Không tồn tại xác định các giá trị không phù hợp với các giá trị trong một bảng không có mặt khác:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
8Với các hình thức SubSelect này, truy vấn bên trong sử dụng * làm danh sách cột đầu ra. Không cần phải đặt tên cho các cột một cách rõ ràng vì truy vấn bên trong được đánh giá là đúng hay sai dựa trên việc nó có trả lại các hàng hay không, không dựa trên các giá trị cụ thể mà các hàng có thể chứa. Trong MySQL, bạn thực sự có thể viết khá nhiều thứ cho danh sách lựa chọn cột, nhưng nếu bạn muốn nói rõ rằng bạn sẽ trả về một giá trị thực khi chọn bên trong thành công, bạn có thể viết các truy vấn như thế này:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+
9 InandnotinsubSelects. Các hình thức trong và không ở các hình thức của SubSelect sẽ trả về một cột các giá trị từ bên trong chọn để được đánh giá trong một so sánh trong lựa chọn bên ngoài. Ví dụ: các truy vấn trước đó tồn tại và không tồn tại có thể được viết bằng cách sử dụng và không ở cú pháp như sau: and NOT IN subselects. The IN and NOT IN forms of subselect should return a single column of values from the inner SELECT to be evaluated in a comparison in the outer SELECT. For example, the preceding EXISTS and NOT EXISTS queries can be written using IN and NOT IN syntax as follows:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+
0
Viết lại SubSelects khi tham gia
Đối với các phiên bản của MySQL trước 4.1, không có sẵn. Tuy nhiên, thường có thể viết lại một truy vấn sử dụng một phần phụ theo sự tham gia. Trên thực tế, ngay cả khi bạn có MySQL 4.1 trở lên, thì không phải là một ý tưởng tồi để kiểm tra các truy vấn mà bạn có thể có xu hướng viết về mặt phân nhóm; Một sự tham gia đôi khi hiệu quả hơn so với một phần phụ.
Viết lại phần phụ chọn các giá trị khớpSau đây là một truy vấn ví dụ chứa một phần phụ; Nó chọn điểm từ bảng điểm cho tất cả các bài kiểm tra [nghĩa là nó bỏ qua điểm số đố]:
Table t1: Table t2: +----+----+ +----+----+ | i1 | c1 | | i2 | c2 | +----+----+ +----+----+ | 1 | a | | 2 | c | | 2 | b | | 3 | b | | 3 | c | | 4 | a | +----+----+ +----+----+2
Truy vấn tương tự có thể được viết mà không cần một phần phụ bằng cách chuyển đổi nó thành một tham gia đơn giản:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+2
Một ví dụ khác, truy vấn sau đây chọn điểm số cho sinh viên nữ:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+3
Điều này có thể được chuyển đổi thành một tham gia như sau:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+4
Có một mô hình ở đây. Các truy vấn phụ theo biểu mẫu này:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+5
Các truy vấn như vậy có thể được chuyển đổi thành tham gia bằng biểu mẫu sau:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+6Viết lại phần phụ chọn các giá trị không phù hợp [thiếu]
Một loại phổ biến khác của các truy vấn SubSelect tìm kiếm các giá trị trong một bảng không có trong một bảng khác. Như chúng ta đã thấy trước đây, loại vấn đề "giá trị không có" là manh mối mà tham gia bên trái có thể hữu ích. Sau đây là một truy vấn với một bài kiểm tra cho các sinh viên không được liệt kê trong bảng vắng mặt [nó tìm thấy những học sinh có tham dự hoàn hảo]:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+7
Truy vấn này có thể được viết lại bằng cách sử dụng tham gia bên trái như sau:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+8
Nói chung, biểu mẫu truy vấn SubSelect như sau:
mysql> SELECT * FROM t1; +----+----+ | i1 | c1 | +----+----+ | 1 | a | | 2 | b | | 3 | c | +----+----+9
Một truy vấn có biểu mẫu đó có thể được viết lại như thế này:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+0
Điều này giả định rằng Bảng2.column2 được tuyên bố là không phải là null.
Lấy từ nhiều bảng với Liên minh
Nếu bạn muốn tạo một kết quả được đặt bằng cách chọn các bản ghi từ nhiều bảng từng bảng, bạn có thể làm điều đó bằng cách sử dụng câu lệnh Union. Liên minh có sẵn kể từ MySQL 4, mặc dù trước đó bạn có thể sử dụng một vài cách giải quyết [hiển thị sau].
Đối với các ví dụ sau, giả sử bạn có ba bảng, T1, T2 và T3 trông giống như thế này:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+1
Bảng T1 và T2 có các cột số nguyên và ký tự, và T3 có các cột ngày và số nguyên. Để viết một tuyên bố công đoàn kết hợp nhiều truy xuất, chỉ cần viết một số câu lệnh chọn và đặt liên kết từ khóa giữa chúng. Ví dụ: để chọn cột số nguyên từ mỗi bảng, hãy làm điều này:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+2
Liên minh có các thuộc tính sau:
Các tên và loại dữ liệu cho các cột của kết quả Liên minh đến từ các tên và loại của các cột trong lần chọn đầu tiên. Các câu lệnh chọn thứ hai và tiếp theo trong Liên minh phải chọn cùng một số cột, nhưng chúng không cần phải có cùng tên hoặc loại. Các cột được khớp với vị trí [không phải theo tên], đó là lý do tại sao hai truy vấn này trả về kết quả khác nhau:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+
3Trong cả hai trường hợp, các cột được chọn từ T1 [I và C] xác định các loại được sử dụng trong kết quả liên kết. Các cột này có các loại số nguyên và chuỗi, do đó chuyển đổi loại diễn ra khi chọn các giá trị từ T3. Đối với truy vấn đầu tiên, D được chuyển đổi từ ngày sang chuỗi. Điều đó xảy ra dẫn đến không mất thông tin. Đối với truy vấn thứ hai, D được chuyển đổi từ ngày sang số nguyên [mất thông tin] và tôi được chuyển đổi từ số nguyên sang chuỗi.
Theo mặc định, Union loại bỏ các hàng trùng lặp từ tập kết quả:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+
4Cả T1 và T2 đều có một hàng chứa các giá trị là 1 và 'đỏ', nhưng chỉ có một hàng như vậy xuất hiện trong đầu ra. Ngoài ra, T3 có hai hàng chứa '2004-01-01' và 200, một trong số đó đã bị loại bỏ.
Nếu bạn muốn bảo tồn các bản sao, hãy làm theo từ khóa Liên minh đầu tiên với tất cả:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+
5Để sắp xếp một kết quả liên minh, hãy thêm một mệnh đề theo lệnh sau lần chọn cuối cùng; Nó áp dụng cho kết quả truy vấn nói chung. Tuy nhiên, vì Liên minh sử dụng tên cột từ lần chọn đầu tiên, nên thứ tự nên tham khảo các tên đó, không phải tên cột từ lựa chọn cuối cùng, nếu chúng khác nhau.
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+
6Bạn cũng có thể chỉ định một mệnh đề đơn hàng cho một câu lệnh chọn riêng lẻ trong Liên minh. Để thực hiện điều này, hãy gửi kèm theo chọn [bao gồm cả thứ tự của nó] trong ngoặc đơn:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+
7Giới hạn có thể được sử dụng trong một liên minh theo cách tương tự như theo thứ tự của. Nếu được thêm vào cuối tuyên bố, nó áp dụng cho kết quả liên minh nói chung:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+
8Nếu kèm theo trong ngoặc đơn như một phần của câu lệnh chọn riêng lẻ, nó chỉ áp dụng cho chọn đó:
mysql> SELECT t1.*, t2.* FROM t1, t2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 1 | a | 2 | c | | 2 | b | 2 | c | | 3 | c | 2 | c | | 1 | a | 3 | b | | 2 | b | 3 | b | | 3 | c | 3 | b | | 1 | a | 4 | a | | 2 | b | 4 | a | | 3 | c | 4 | a | +----+----+----+----+
9Bạn không cần chọn từ các bảng khác nhau. Bạn có thể chọn các tập hợp con khác nhau của cùng một bảng bằng các điều kiện khác nhau. Điều này có thể hữu ích như là một thay thế để chạy một số truy vấn chọn khác nhau, bởi vì bạn nhận được tất cả các hàng trong một tập kết quả thay vì một số tập kết quả.
Trước MySQL 4, Liên minh không có sẵn, nhưng bạn có thể làm việc xung quanh khó khăn này bằng cách chọn các hàng từ mỗi bảng vào bảng tạm thời và sau đó chọn nội dung của bảng đó. Trong MySQL 3.23 trở lên, bạn có thể xử lý vấn đề này một cách dễ dàng bằng cách cho phép máy chủ tạo bảng giữ cho bạn. Ngoài ra, bạn có thể biến bảng thành một bảng tạm thời để nó sẽ được bỏ tự động khi phiên của bạn với máy chủ chấm dứt. Để có hiệu suất nhanh hơn, hãy sử dụng bảng HEAP [trong bộ nhớ].
mysql> SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 2 | b | 2 | c | | 3 | c | 3 | b | +----+----+----+----+0
Vì TMP là một bảng tạm thời, máy chủ sẽ tự động thả nó khi phiên khách hàng của bạn kết thúc. .
Đối với các phiên bản của MySQL cũ hơn 3,23, khái niệm này tương tự nhau, nhưng các chi tiết khác nhau vì loại bảng HEAP và bảng tạm thời không có sẵn, như là tạo bảng ... Chọn. Để điều chỉnh quy trình trước, cần phải tạo bảng một cách rõ ràng trước khi lấy bất kỳ hàng nào vào nó. [Loại bảng duy nhất có sẵn sẽ là ISAM, vì vậy bạn không thể sử dụng tùy chọn loại.] Sau đó truy xuất các bản ghi vào bảng. Khi bạn hoàn thành nó, bạn phải sử dụng bảng thả một cách rõ ràng vì máy chủ sẽ không tự động thả nó.
mysql> SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2; +----+----+----+----+ | i1 | c1 | i2 | c2 | +----+----+----+----+ | 2 | b | 2 | c | | 3 | c | 3 | b | +----+----+----+----+1
Nếu bạn muốn chạy một truy vấn kiểu Liên minh trên các bảng Myisam có cấu trúc giống hệt nhau, bạn có thể thiết lập một bảng hợp nhất và truy vấn như một cách giải quyết cho việc thiếu liên minh. . Các bảng tạo nên bảng hợp nhất. Đó là, chọn trên một bảng hợp nhất giống như tất cả [các bản sao không được xóa] và chọn khác biệt giống như liên minh [trùng lặp được loại bỏ].