Genel Bakış

SQL'in ne olduğunu, onu ne için kullanabileceğimizi öğreneceğiz ve sonra bir veritabanından verileri seçmek ve filtrelemek için bazı temel sorgular yazmaya başlayacağız.

Not: Bugün aslında bir SQL veritabanıyla çalışmayacağız, bir veri çerçevesi oluşturmak için Python kullanacağız ve ardından onu sorgulamak için pandasql adlı bir kitaplık kullanacağız. Python'a aşina değilseniz, öğreticiyi takip etmenizi ve SQL komutlarına ve bunların çıktılarına dikkat etmenizi öneririm.

SQL nedir?

Veriler her yerde ve her dakika daha fazlası oluşturuluyor. Bu verileri kağıt üzerinde dev dosya dolaplarında saklardık ama şimdi bunları dijital olarak veritabanı adı verilen şeylerde saklıyoruz. Şimdi, istediğimiz verileri bu dijital veri tabanından nasıl kolayca çekebiliriz? SQL bunun için! SQL (Yapılandırılmış Sorgu Dili), veritabanlarımızla iletişim kurmak için kullandığımız bir dildir. Bir veritabanına veri çekmek, düzenlemek veya eklemek istiyorsanız, bunu yapmak için SQL'i kullanabilirsiniz. Veritabanları, çeşitli dillerde (C ++, Java) yazılmış, çeşitli mimarilerde (dev veri gölleri, basit tek tablo şemaları) oluşturulabilir, ancak SQL, herkesin bu verilere evrensel sözdizimi kullanarak erişmesini sağlayan ortak zemindir.

SQL kullanarak bir veritabanına veri ekleyebileceğimizi biliyoruz, ancak SQL, çok büyük kayıtları filtreleme ve yalnızca tam olarak aradığımız girdileri döndürme yeteneği nedeniyle mevcut verileri çekmek istediğimizde en kullanışlı hale geliyor. Her biri kendi benzersiz sözdizimine ve komut içinde gerekli konuma sahip neredeyse sonsuz sayıda SQL yöntemi vardır. Bundan sonra daha fazlası.

Sözdizimi ve en iyi uygulamalar

Veritabanından veri çekmek için bir SQL komutunun kullanılması sorgu olarak adlandırılır ve SQL'de, izlenmezse kodunuzun bozulmasına neden olabilecek birkaç yaygın sözdizimi kuralı vardır. Sorgular bazen çok uzun ve son derece karmaşık olabilir, bu nedenle sorguların düzgün ve okunabilir olmasını sağlamak için yıllar içinde bazı en iyi uygulamalar geliştirilmiştir. SQL sözdiziminin temellerini ve SQL sorguları yazarken izlememiz gereken en iyi uygulama kurallarını gözden geçirelim.

İşte adı verilen bir veritabanındaki her satırı ve sütunu basitçe döndüren çok basit bir SQL komutu store_sales. (Unutmayın ki bu, SQL'de yazabileceğimiz en basit sorgulardan biridir, sorgular nadiren bu kadar kısa olacaktır)

''' 
SELECT * FROM store_sales;
'''

• ’’’…’’’- SQL sorguları her zaman dizelerdir ve üçlü tırnak kullanarak çoğu sorgunun birden çok satır gerektirdiği gerçeğini aşabiliriz
• SELECT - bu, bir veritabanından veri çekmek için kullanılan komuttur, ardından her zaman veri çekme için hangi sütunlara erişileceğinin bir göstergesi gelir, her zaman sorgudaki ilk komut
• * - bu "tümü" için SQL konuşmasıdır, her satırı ve her sütunu seçecektir (not: bu, erişmek istediğimiz sütun (lar) a bağlı olarak başka bir şey olabilir)
• FROM - hangi veri kaynağının kullanılacağını bu şekilde belirleriz, her zaman SELECT komutundan önce gelir ve sonra gelen her şey komutun hedefi olur
• store_sales - bu sanal veritabanımızın adıdır, bu, kullandığınız veritabanına bağlı olarak farklı olacaktır
• ; - bu, veritabanımıza komutumuzla işimizin bittiğini söyler, her zaman bir sorguyu sonlandırırız ;

Daha önce de belirtildiği gibi, SQL'deki her komutun kendi sözdizimi ve sorgu içinde yerleşimi vardır. Sözdizimi ve kullanım durumlarını hızlıca kontrol etmek için gerçekten sevdiğim bir referans sayfası: https://www.w3schools.com/sql/sql_ref_keywords.asp . Birkaç örnek komut da dahil olmak üzere, nasıl kullanılacağı hakkında daha fazla bilgi almak için bir yöntem adını tıklamanız yeterlidir.

Veri seçmenin temelleri

Bu makalenin geri kalanında, verileri seçmenin ve filtrelemenin temel yöntemlerine odaklanacağız. Bu hayali senaryo etrafında çalışacağız: 20 çocuklu bir okul öğretmeniyiz. Çeyreğin sonu. 10 haftalık dönemde her hafta sonunda öğrencilere bir önceki haftanın materyalleri üzerinde bir test yapılır. Bu testlerin puanları okulumuzun sistemindeki bir SQL veritabanında saklanır, bu nedenle öğrencilerimiz ve test puanları hakkında bilgi almak için bazı sorgular yazacağız.

Daha önce de belirtildiği gibi, kullanacağımız verileri (pandalar DataFrames) oluşturmak için Python kullanacağız ve pandasqlsorgulamak için adında bir kitaplık kullanacağız. pandasqlUygulamalarda yerel / istemci depolaması için oldukça yaygın bir SQL çeşidi olan SQLite kullanır.

İthalat ve veri oluşturma

Sadece sorgulara dalmak istiyorsanız, NEREDE bölümüne atlayın. Python'a aşina değilseniz, bunu yapmanızı öneririm.

Rastgele test puanı verilerimizi oluşturmak için kullandığım kod burada. Çok ayrıntılı olarak anlatmayacağım, neler olduğunu anlamanıza yardımcı olacak birkaç yorum var. Son pandalarımız DataFrame, scoresSQL sorgularımızı yazacağımız bir değişkende saklanacaktır .

Kodun en altına kaydırırsanız, verilerimizin ilk 5 satırının yazdırıldığını görürsünüz.

NEREDE

WHEREBağımsız değişken taban filtreleme bağımsız değişken olarak kullanılır. Her zaman FROM …beyanınızdan sonra gelir ve her zaman filtrelemek istediğiniz koşul izler. Koşullar sayısal veya dize verileri için olabilir, sütunlar her iki veri türünü de içeremez. WHEREoperatörler diğer herhangi bir programlama diliyle hemen hemen aynı şekilde çalışır, ancak işte kısa bir genel bakış:

Şimdi, diyelim ki biz öğretmenler olarak, sınıfımızda kimin ilk hafta sınavında o kadar iyi performans göstermediğini bilmek istiyoruz. Şimdi sorgunun bunun için nasıl görünebileceğine bir göz atalım (bir WHEREcümle kullanarak ). % 77'den az bir puan olarak "çok sıcak yapmadım" ifadesini ölçeceğiz.

Tamam, peki burada ne oldu? Fark edebileceğiniz ilk şey, bunun SELECT name, test_1_scoreyerine kullandığımızdır SELECT *. Bunların her ikisi de scorestablomuzdaki sütunlar ve SELECTifadede belirterek sadece bu iki sütunda bilgi döndürmemizi söylüyoruz.

Sonra, seçmek istediğimizi söylüyoruz FROM scores. İçinde pandasql, çalışma ortamımızdaki mevcut pandalar DataFrame nesneleri erişilebilir SQL tabloları olarak kabul edilir. Bu durumda, scoresbizim veri kaynağımızdır ve FROMmaddede bu şekilde belirtiyoruz .

Biz de sadece kayıtları istediğimizi söyledik WHERE test_1_score <77. Verilerimiz sayısal olduğundan, tüm değerlerin daha az olmasını istediğimizi söylemek için bir matematiksel operatör kullanabiliriz, teşekkürler _blank_

Başka bir tane deneyelim. Bu sefer, haftalık son testte (test 10) hangi öğrencilerin özellikle iyi performans gösterdiğini (% 90 veya daha iyi) görmek istiyoruz.

Harika! Biz sadece biz değerlere filtreleme uygulamak istediğini belirtti test_10_scoreaksine test_1_score, sonra bizim operatör işareti çevirdi 'Daha büyük.'

İki değer arasında

Ya materyali açıkça anlayan, ancak olması gerektiği kadar iyi çalışmayan öğrenciler bulmak isteseydik? % 80'den yüksek ancak% 90'dan düşük puan alan öğrenciler için olan şeyin bu olduğunu varsayacağız.

Bu sorgunun, operatörlerimizi basitçe 'yığdığımız' şuna benzeyeceğini düşünebilirsiniz.

Ancak gördüğümüz gibi, 80–90 aralığının dışında kalan her türden değer elde ediyoruz.

Bunun nedeni, bunun yerine kullanmamız gereken yerleşik bir SQL operatörü olmasıdır. Bu yöntemin adının ne olduğunu tahmin edebilir misin? Sen tahmin ettin, bu BETWEEN. BETWEENteknik olarak mantıksal bir operatördür, bu nedenle her zaman WHERE komutunu izler. İki değer ister - istenen aralığın başlangıcı ve bitişi AND- bir sayı aralığını belirterek - ayrılır . Burada bir not: SQL BETWEENkapsayıcıdır (ile eşdeğerdir 80<=test_10_score<=90).

Şimdi, sadece% 80-90 arasında puan alan öğrencilerin isimlerini ve puanlarını içeren çok daha küçük bir listemiz olduğunu görebiliriz.

VE / VEYA

Şimdiye kadar, WHEREifadelerimizde yalnızca tek bir koşul belirtiyorduk. Gerçekte, istediğimiz kadar koşula sahip olabiliriz, bu da SQL'i karmaşık filtreleme için gerçekten kullanışlı hale getirir. Bir WHEREkomutun ardından birden çok koşulu belirtmenin iki yolu vardır :

• VE - her iki koşulun da / tüm koşulların doğru olduğu girdileri döndürmek istediğimizde kullanılır (örneğin, 9. ve 10. testlerde% 90'ın üzerinde puan alan öğrenciler istiyoruz)
• OR - koşullardan herhangi birinin doğru olduğu girdileri döndürmek istediğimizde kullanılır, OR zincirindeki herhangi bir sayıdaki koşulla eşleşen tüm kayıtları döndürür

Harika! Şimdi, terimin 1. ve 2. haftalarının aynı materyali kapsadığını varsayalım ve bu materyalin en azından iyi bir kısmını anlayan öğrencilerin bir listesini almak istiyoruz. Bunun için, test 1 veya test 2'de% 85'ten daha iyi puan alan öğrencilerin adlarını iade etmek istiyoruz. Bu, bir ORoperatöre mükemmel bir şekilde ödünç verir .

Peki tamam. Çoğunlukla, sınıfımız ilk iki haftanın materyalinin iyi bir bölümünü anlamış gibi görünüyor.

Hakkında çok şey ANDve ORbirlikte bunların çoğu olarak zincir istediğiniz gibi olmasıdır. Hatta eşleşmeyi bile karıştırabilirsiniz!

Örneğin, son testimiz (test 10) 1-9. Haftalar arasındaki tüm materyallerin son testiyse, dönem başında kötü performans gösteren ve aynı zamanda başarısız olan öğrencilerin bir listesini görmek isteyebiliriz. dönem sonu. 1. veya 2. haftalarda sınavlarda pek başarılı olmayan ve dönem boyunca ders çalışmak için zaman ayırmayan ve son sınavda da kötü performans gösteren öğrencilere bakacağız (test 10 ). Aşağıdaki SQL sorgusuna bir göz atın ve ne yaptığını anlayıp anlamadığınızı görün.

Bu sefer, sadece test 1 veya test 2'de% 75'in altında puan alan ve yine de 10. testte% 75'in altında puan alan öğrencilerin adlarını geri vermek istediğimizi söyledik. ORİlk önce kullandık çünkü hangi testi umursamadık kötü yaptık, ikisine de kötü yapıp yapmadıklarını önemsedik . Daha sonra AND, son koşulun doğru olması için kesinlikle ihtiyacımız olduğunu belirtirdik. Girişler ilk iki koşuldan biriyle ve son koşulla eşleşmezse , iadeye dahil edilmezler.

TARAFINDAN SİPARİŞ

Bazen uzun bir sorgu dönüşünü incelemek yine de zor olabilir ve çıktı içindeki satırları daha fazla organize etmek ve filtrelemek faydalı olabilir. ORDER BYbunu yapmak için çok yaygın ve kullanışlı bir yöntemdir ve hemen hemen tüm veri türlerinde (dizi, zaman serisi, sayısal vb.) çalışır. ORDER BYve çeşitli değiştiriciler her zaman sona yakın yerleştirilmeli ve her zaman koşullu cümleciklerinden sonra gelmelidir (örneğin WHERE).

Tablomuzda, son 10 haftadaki her öğrencinin test puanının genel ortalaması olan yeni bir sütun oluşturacağız. Bu sütunu arayacağız test_avg.

Artık ortalamalarına göre saklanan öğrencilerimizin sıralı bir listesini alabiliriz. test puanı. Bu sorgu buna benzer bir şeye benzeyecektir, ancak çıktımıza bir göz atın.

Mükemmel şekilde sıralanmış bir liste elde ederiz, ancak bu liste artan sırada. Bu, SQL'deki varsayılan ayardır ORDER BY. Azalan sırada sıralanmış bir liste elde etmek istersek , SQL bunu çok kolaylaştırır; DESCetiketi sütun göstergemizden sonra ekliyoruz .

Artık dönüş tablomuzun başında en yüksek değerlere sahibiz.

İadelerimizi birden fazla sütuna göre de sıralayabiliriz. Bunu, ORDER BYargümanınızın ardından sütun değerlerini virgülle ayırarak yaparsınız . Örneğin, aşağıdaki sorgu döner name, test_avgve test_10_scoresıralama kriteri test_10_score, önce ve sonra sıralama kriteri test_avg. Bu incelikli, ama ikinci sınıflandırma katmanının nerede olduğunu anlayabilecek misiniz bir bakın.

Yakından bakarsak (1 ve 2, 3 ve 4 ve 12 ve 13 numaralı satırları kontrol edin), bu iki katmanlı sıralamayı iş başında görebiliriz.

Bu çift sıralama, bu durum için çok yararlı olmayabilir, ancak aşağıdaki örneği alın. Bir araba bayisi için çalıştığınızı ve bir onarım işi için belirli bir marka ve modelin belirli bir satışını bulmak için on binlerce kaydı ayırmanız gerektiğini hayal edin. Şimdi iki değere göre sıralama (diyelim yearve sonra car_model) son derece gerekli hale geliyor ve SQL bunu bizim için kolaylaştırıyor.

GRUPLAMA

Burada bahsedeceğimiz son ana yöntem GROUP BYyöntemdir. Tam olarak göründüğü gibi yapar, satırları belirtilen sütuna göre gruplandırır, ancak a'nın dönüşü GROUP BYgeçmişte gördüğümüz sorgulardan biraz farklı görünüyor. GROUP BYOrtak değerlere dayalı olarak satırları toplar ve genellikle kategorik değişkenler içeren sütunlar için kullanılır. Şuna benzer şekilde ORDER BY, daima sorgunun sonunda gider. GROUP BYBir ORDER BYcümle olarak aynı sorguda kullanmak yaygındır , bu durumda her zaman GROUP BYen sona koyuyoruz .

Öğretmen / test puanı örneğimizden yola çıkarak, öğrencileri harf notuna göre gruplamak için bu işlevi kullanabiliriz. Ve biz de bunu yapacağız. Öncelikle tablomuzda yeni bir sütun oluşturalım. Bu sütunu arayacağız gradeve aşağıdaki ortalama test puanı koşullarına göre harf notlarıyla dolduracağız:

En sağa doğru bakarsanız, yeni sütunumuzu göreceksiniz.

Şimdi oldukça güzel sorgular yazabiliriz. Aşağıdaki sorguya bir göz atın ve neler olduğunu anlayıp anlayamayacağınızı görün (henüz ele almadığımız bir yöntem var).

Muhtemelen sorguda ilk fark ettiğiniz şey COUNT(name)argümandır. Bizim SELECTmaddemizde var, bu yüzden masamızdan aldığımız bir tür bilgi olduğunu biliyoruz. COUNT()dönüşteki girişlerin sayısını temsil eden tek bir değer döndüren, ancak belirttiğiniz şekilde gruplandırılan SQL'de gerçekten kullanışlı bir yerleşik yöntemdir. Her gruptaki öğrenci adlarını saymayı seçiyoruz. Gruplar, GROUP BY gradebirçok satırımızı gradesütundaki tüm geçerli değerleri temsil eden birkaç satırda toplayan argümanımız tarafından belirtilir .

Başka bir çok kullanışlı yerleşik SQL yöntemi AVG(). COUNT()Sağlanan sütunun ortalamasını temsil eden tek bir değer döndürmekle aynı çizgide çalışır , ancak belirlediğiniz şekilde gruplanır. Aşağıdaki sorguda, her harf not grubundaki tüm öğrencilerimizin ortalama test puanını döndürüyoruz.

COUNT()Ve tarafından oluşturulan yeni sütunlara dikkat edin AVG(). Sezgisel olarak adlandırılmışlar, sütunun tam olarak ne içerdiğini açıklıyorlar, ancak aynı zamanda biraz dağınık görünüyor. Bunu SQL ASargümanını kullanarak düzeltebiliriz . Bu, araç kemerinizde olması gereken gerçekten kullanışlı bir argümandır, özellikle birçok farklı kategoriyi toplamaya başladığınızda, tanıdık ve özelleştirilebilir bir adlandırma kuralına sahip olmak gerçekten yararlı olabilir.

Ekstra yardımcılar: AS ve AVG ()

Aşağıdaki sorgu AS, yeniden adlandırılmış tek bir değer döndürmek için bir ifade kullanır test_1_avg. Muhtemelen tahmin edebileceğiniz gibi, bu değer 1. test için sınıf çapında bir puan ortalaması olacaktır.

Çıktımızda hemen sonucun test_1_avgtek bir değer içeren yeni dublajlı bir sütun olduğunu görebiliriz.

AS yeniden adlandırmak istediğiniz nesnenin hemen arkasına yerleştirilir ve sorgunuzda bir sütun veya yeni değer eklediğiniz her yerde kullanılabilir.

Sonuç

Bu, SQL Crash Course'un bu bölümü için yapacak, SQL tablolarını ve veritabanlarını - tabloları depolamak için kullanılan SQL mimarisi - nasıl oluşturacağımız bu serideki bir sonraki makale için bizi izlemeye devam edin.

Öğrendiklerimizi özetlemek gerekirse, önce SQL (Yapılandırılmış Sorgu Dili) 'nin veritabanlarıyla konuşmak için kullandığımız dil olduğunu ve veritabanlarından veri eklememize, silmemize, düzenlememize ve almamıza izin verdiğini öğrendik. Daha sonra verileri almak için bir avuç yöntem öğrendik ve en iyi SQL uygulamaları ve sözdizimini öğrendik. Argümanların bir sorgu içinde olması gereken belirli yerleri olduğunu öğrendik, sorguların sonunda noktalı virgül gerektiğini öğrendik ve veri satırlarını ve sütunlarını toplamak için tonlarca yerleşik yöntem olduğunu öğrendik.

Okuduğunuz için teşekkürler, bir dahaki sefere ayarlayın ve SQL için mutlu olun!