Rumah > Artikel > pangkalan data > Analisis kes pertanyaan berbilang jadual MySQL
Pemahaman produk Cartesian
select id,department_name from employees,departments;#错的 select id,department_id,department_name from employees CROSS JOIN departments;#错的
Setiap pekerja dan setiap jabatan dipadankan sekali (bilangan penyertaan ditemui = Bilangan id * bilangan jabatan)
Sebab ralat: Keadaan sambungan hilang
Tulis syarat sambungan: Jadual 1. Lajur = Jadual 2. Lajur (jika Untuk menyambung berbilang jadual, sekurang-kurangnya n-1 syarat sambungan mesti digunakan)
select id,employees.name,department_name from employees,departments WHERE employees.name = departments.name;
Nota: Jika lajur yang akan dipaparkan mempunyai nama yang sama dalam jadual untuk disoal, ia mesti menunjukkan jadual asalnya, cth: pekerja .name
Adalah disyorkan untuk menunjukkan maklumat jadual yang dipaparkan apabila menanyakan berbilang jadual (pengoptimuman)
Pengoptimuman: Anda boleh menggunakan alias jadual selepas FROM, tetapi setelah alias digunakan, seterusnya Pastikan anda menggunakan alias
Caburan setara: yang di atas dengan =
Bukan equijoin: tidak =
select t1.id,t1.name,t2.grade from employees t1,departments t2 WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary ;#非等值
Pautan bukan diri: Jadual 1 dan jadual 2 disambungkan
Pautan diri: Jadual 1 dan dirinya sendiri disambungkan
#显示员工(t1)和其管理者(t2)的基本信息 select t1.id,t1.name,t2.id,t2.name from employees t1,employees t2#一个表看作两个表 WHERE t1.manage_id = t2.id ;#自连接
Sambungan dalam: Gabungkan jadual yang mengandungi lajur yang sama Hasilnya tidak termasuk baris dalam satu jadual yang tidak sepadan dengan jadual lain
Sambungan dalam: lihat di atas
WHERE t1.department_id = t2.department_id(+)#左连接SQL99: Gunakan kaedah JOIN...ON Inner JOIN
select t1.id,t1.name,t2.department_name,t3.environment from employees t1 JOIN departments t2 ON t1.department_id = t2.department_id JOIN locations t3#加入第二个人表 ON t2.department_location = t3.department_location;OUTER JOINGuna OUTER JOIN...ON. .. KIRI OUTER JOIN
select t1.name,t2.department_name#左外连接 from employees t1 LEFT OUTER(可省略) JOIN departments t2 ON t1.department_id = t2.department_id;Penggunaan UMION
SELECT colum... FROM table1 UNION (ALL) SELECT colum... FROM table2
Gambar tengah (gabungan dalam):
select t1.name,t2.department_name from employees t1 JOIN departments t2 ON t1.department_id = t2.department_id;
Gambar kiri atas (sambungan luar kiri) ):
select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id;
Gambar kanan atas (sambungan luar kanan):
select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id;Gambar kiri tengah:
select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t2.department_id IS NULL;Tengah gambar kanan:
select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t1.department_id IS NULL;Gambar kiri bawah (gabungan Luar penuh):
#方式一:左上图 UNION ALL 右中图 select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id UNION ALL select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t1.department_id IS NULL; #方式二:左中图 UNION ALL 右上图 select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t2.department_id IS NULL UNION ALL select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id;Gambar kanan bawah:
#左中图 UNION ALL 右中图 select t1.name,t2.department_name from employees t1 LEFT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t2.department_id IS NULL UNION ALL select t1.name,t2.department_name from employees t1 RIGHT JOIN departments t2 ON t1.department_id = t2.department_id WHERE t1.department_id IS NULL;Ciri sintaks SQL baharuCaburan semula jadiGunakan kata kunci: NATURAL JOIN (tidak fleksibel) , secara automatik bertanya semua medan yang sama dalam jadual, dan kemudian lakukan sambungan yang setara GUNAKAN sambungan (tidak berkenaan untuk penyertaan sendiri) Gunakan kata kunci: USING (medan dengan nama yang sama) untuk menyertai nama yang sama dalam jadual Sambungan sama automatik medan
select t1.name,t2.department_name from employees t1 JOIN departments t2 ON t1.department_id = t2.department_id; 等价于 select t1.name,t2.department_name from employees t1 JOIN departments t2 USING(department_id);
Atas ialah kandungan terperinci Analisis kes pertanyaan berbilang jadual MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!