\u0130li\u015fkisel veritabanlar\u0131 ve Yap\u0131land\u0131r\u0131lm\u0131\u015f Sorgu Dili (SQL) ile \u00e7al\u0131\u015f\u0131rken, ili\u015fkisel bir veritaban\u0131 y\u00f6netim sisteminden veri depolayabilir, y\u00f6netebilir ve alabilirsiniz. SQL ise, verileri veritaban\u0131nda depoland\u0131\u011f\u0131 haliyle, oldu\u011fu gibi alabilir.<\/p>\n
SQL, fonksiyonlar kullanarak hesaplamalar yapabilir ve verileri i\u015fleyebilir. \u00d6rne\u011fin, fonksiyonlar\u0131 kullanarak \u00fcr\u00fcn fiyatlar\u0131n\u0131 en yak\u0131n dolara yuvarlayabilir, ortalama \u00fcr\u00fcn sat\u0131n alma say\u0131s\u0131n\u0131 hesaplayabilir veya bir sat\u0131n alma i\u015fleminin garantisinin bitmesine kalan g\u00fcn say\u0131s\u0131n\u0131 belirleyebilirsiniz.<\/p>\n
Bu e\u011fitimde, matematiksel hesaplamalar yapmak, dizeleri ve tarihleri i\u015flemek ve toplama i\u015flevlerini kullanarak \u00f6zetleri hesaplamak i\u00e7in \u00e7e\u015fitli SQL i\u015flevlerini kullanacaks\u0131n\u0131z.<\/p>\n
Bu k\u0131lavuzu takip etmek i\u00e7in, SQL tabanl\u0131 bir ili\u015fkisel veritaban\u0131 y\u00f6netim sistemi (RDBMS) \u00e7al\u0131\u015ft\u0131ran bir bilgisayara ihtiyac\u0131n\u0131z var. Bu k\u0131lavuzdaki talimatlar ve \u00f6rnekler, a\u015fa\u011f\u0131daki ortam kullan\u0131larak do\u011frulanm\u0131\u015ft\u0131r:<\/p>\n
Not: Bir\u00e7ok RDBMS kendi SQL uygulamas\u0131n\u0131 kullan\u0131r. Bu e\u011fitimde a\u00e7\u0131klanan komutlar \u00e7o\u011fu RDBMS'de \u00e7al\u0131\u015fsa da, standart SQL s\u00f6zdizimi yaln\u0131zca s\u0131n\u0131rl\u0131 say\u0131da i\u015flevi belirtir. Ayr\u0131ca, standart s\u00f6zdizimi deste\u011fi farkl\u0131 veritaban\u0131 motorlar\u0131 aras\u0131nda de\u011fi\u015fiklik g\u00f6sterir. MySQL d\u0131\u015f\u0131nda bir sistemde test ederseniz, kesin s\u00f6zdizimi veya \u00e7\u0131kt\u0131 farkl\u0131l\u0131k g\u00f6sterebilir.<\/span><\/p>\n Fonksiyonlar\u0131 kullanabilmeniz i\u00e7in \u00f6rnek verilerle y\u00fckl\u00fc tablolar i\u00e7eren bir veritaban\u0131na da ihtiyac\u0131n\u0131z olacak. Bir MySQL sunucusuna ba\u011flanma ve bu k\u0131lavuzdaki \u00f6rneklerde kullan\u0131lan test veritaban\u0131n\u0131 olu\u015fturma hakk\u0131nda ayr\u0131nt\u0131l\u0131 bilgi i\u00e7in "MySQL'e Ba\u011flanma ve \u00d6rnek Veritaban\u0131 Olu\u015fturma" b\u00f6l\u00fcm\u00fcn\u00fc incelemenizi \u00f6neririz.<\/p>\n Bu b\u00f6l\u00fcmde bir MySQL sunucusuna ba\u011flanacak ve bu k\u0131lavuzdaki \u00f6rnekleri takip edebilmeniz i\u00e7in \u00f6rnek bir veritaban\u0131 olu\u015fturacaks\u0131n\u0131z.<\/p>\n SQL veritaban\u0131 sisteminiz uzak bir sunucuda \u00e7al\u0131\u015f\u0131yorsa, SSH arac\u0131l\u0131\u011f\u0131yla sunucunuza giri\u015f yap\u0131n:<\/p>\n Daha sonra MySQL sunucu ortam\u0131n\u0131 a\u00e7\u0131n, bunun yerine Bookstore ad\u0131nda bir veritaban\u0131 olu\u015fturun:<\/p>\n Veritaban\u0131 ba\u015far\u0131yla olu\u015fturulduysa a\u015fa\u011f\u0131dakine benzer bir \u00e7\u0131kt\u0131 al\u0131rs\u0131n\u0131z:<\/p>\n Kitap\u00e7\u0131 veritaban\u0131n\u0131 se\u00e7mek i\u00e7in USE komutunu \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n A\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 g\u00f6receksiniz:<\/p>\n Bir veritaban\u0131 se\u00e7tikten sonra, i\u00e7inde \u00f6rnek tablolar olu\u015fturabilirsiniz. Bu k\u0131lavuzda, farkl\u0131 yazarlar\u0131n \u00e7e\u015fitli kitaplar\u0131n\u0131 satan kurgusal bir k\u00fct\u00fcphane kullanaca\u011f\u0131z.<\/p>\n Envanter tablosu, kitap\u00e7\u0131da bulunan kitaplar hakk\u0131nda bilgi i\u00e7erir ve a\u015fa\u011f\u0131daki s\u00fctunlar\u0131 i\u00e7erir:<\/p>\n A\u015fa\u011f\u0131daki komutla \u00f6rnek tabloyu olu\u015fturun:<\/p>\n A\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 al\u0131rsan\u0131z tablo olu\u015fmu\u015f demektir:<\/p>\n Daha sonra a\u015fa\u011f\u0131daki INSERT INTO i\u015flemini y\u00fcr\u00fcterek sat\u0131n almalar tablosunu baz\u0131 \u00f6rnek verilerle y\u00fckleyin:<\/p>\n INSERT INTO i\u015flemi, belirtilen de\u011ferlere sahip be\u015f kitab\u0131 envanter tablosuna ekler. A\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131, be\u015f sat\u0131r\u0131n da eklendi\u011fini g\u00f6sterir:<\/p>\n Art\u0131k rehberin geri kalan\u0131n\u0131 takip etmeye ve SQL'de fonksiyonlar\u0131 kullanmaya ba\u015flamaya haz\u0131rs\u0131n\u0131z.<\/p>\n Fonksiyonlar, bir veya daha fazla de\u011fer alan, veriler \u00fczerinde hesaplamalar veya d\u00f6n\u00fc\u015f\u00fcmler ger\u00e7ekle\u015ftiren ve sonu\u00e7 olarak yeni bir de\u011fer d\u00f6nd\u00fcren adland\u0131r\u0131lm\u0131\u015f ifadelerdir. SQL fonksiyonlar\u0131n\u0131 matematiksel fonksiyonlara benzer \u015fekilde d\u00fc\u015f\u00fcnebilirsiniz. \u00d6rne\u011fin, log(x) fonksiyonu x de\u011ferini al\u0131r ve x'in logaritmas\u0131n\u0131 d\u00f6nd\u00fcr\u00fcr.<\/p>\n \u00d6rne\u011fin, en pahal\u0131dan en ucuza do\u011fru t\u00fcm kitap ba\u015fl\u0131klar\u0131n\u0131 fiyatlar\u0131yla birlikte almak istiyorsan\u0131z, a\u015fa\u011f\u0131daki komutu \u00e7al\u0131\u015ft\u0131rabilirsiniz:<\/p>\n A\u015fa\u011f\u0131dakine benzer bir \u00e7\u0131kt\u0131 alacaks\u0131n\u0131z:<\/p>\n Bu komutta, Ancak, bir t\u00fcr i\u015flem veya d\u00fczenlemeden sonra veritaban\u0131ndan de\u011ferleri almak isteyebilirsiniz. Kitap fiyatlar\u0131n\u0131n en yak\u0131n dolara yuvarlanmas\u0131, kitap ba\u015fl\u0131klar\u0131n\u0131n b\u00fcy\u00fck harfle yaz\u0131lmas\u0131 veya ay veya g\u00fcn hari\u00e7, piyasaya s\u00fcr\u00fcld\u00fc\u011f\u00fc y\u0131l\u0131n belirtilmesi ilginizi \u00e7ekebilir. \u0130\u015fte bu noktada bir fonksiyon kullan\u0131rs\u0131n\u0131z.<\/p>\n SQL fonksiyonlar\u0131, \u00fczerinde \u00e7al\u0131\u015ft\u0131klar\u0131 veri t\u00fcr\u00fcne ba\u011fl\u0131 olarak genel olarak birka\u00e7 gruba ayr\u0131labilir. En s\u0131k kullan\u0131lan fonksiyonlar \u015funlard\u0131r:<\/p>\n Not: MySQL de dahil olmak \u00fczere \u00e7o\u011fu ili\u015fkisel veritaban\u0131, SQL standard\u0131 taraf\u0131ndan tan\u0131mlanan standart i\u015flev k\u00fcmesini, veritaban\u0131 motoruna \u00f6zg\u00fc ek i\u015flemlerle geni\u015fletir. Standart SQL i\u015flevleri k\u00fcmesinin d\u0131\u015f\u0131ndaki bir\u00e7ok i\u015flev, bir\u00e7ok veritaban\u0131nda benzer \u015fekilde \u00e7al\u0131\u015f\u0131rken, di\u011ferleri tek bir \u0130li\u015fkisel Veritaban\u0131 Y\u00f6netim Sistemine (RDBMS) ve onun benzersiz \u00f6zelliklerine \u00f6zg\u00fcd\u00fcr. Veritaban\u0131n\u0131n sa\u011flad\u0131\u011f\u0131 i\u015flevler hakk\u0131nda daha fazla bilgi edinmek i\u00e7in se\u00e7ti\u011finiz veritaban\u0131n\u0131n belgelerine ba\u015fvurabilirsiniz. MySQL \u00f6rne\u011finde ise, yerle\u015fik i\u015flevler ve operat\u00f6r referanslar\u0131 hakk\u0131nda daha fazla bilgi edinebilirsiniz.<\/span><\/p>\n A\u015fa\u011f\u0131daki \u00f6rnek, bir kitap\u00e7\u0131 envanter veritaban\u0131ndaki fiyat de\u011ferlerinin sonu\u00e7lar\u0131n\u0131 SELECT sorgusu kullanarak de\u011fi\u015ftirmek i\u00e7in EXAMPLE adl\u0131 hayali, var olmayan bir fonksiyonun kullan\u0131lmas\u0131na ili\u015fkin genel s\u00f6zdizimini g\u00f6stermektedir:<\/span><\/p>\n (EXAMPLE) i\u015flevi, parantez i\u00e7inde s\u00fctun ad\u0131n\u0131 (price) arg\u00fcman olarak al\u0131r. Sorgunun bu k\u0131sm\u0131, veritaban\u0131na s\u00fctundaki fiyat de\u011ferleri \u00fczerinde EXAMPLE i\u015flevini \u00e7al\u0131\u015ft\u0131rmas\u0131n\u0131 ve bu i\u015flemin sonu\u00e7lar\u0131n\u0131 d\u00f6nd\u00fcrmesini s\u00f6yler. AS new_price, veritaban\u0131na sorgu s\u0131ras\u0131nda hesaplanan de\u011ferlere ge\u00e7ici bir ad (new_price) atamas\u0131n\u0131 s\u00f6yler. Bu sayede, \u00e7\u0131kt\u0131da i\u015flevin sonu\u00e7lar\u0131n\u0131 belirleyebilir ve WHERE ve ORDER BY ifadelerini kullanarak hesaplanan de\u011ferlere ba\u015fvurabilirsiniz.<\/p>\n A\u015fa\u011f\u0131daki b\u00f6l\u00fcmde matematiksel fonksiyonlar\u0131 kullanarak yayg\u0131n hesaplamalar\u0131 ger\u00e7ekle\u015ftireceksiniz.<\/p>\n Matematiksel fonksiyonlar, bir kitab\u0131n fiyat\u0131 veya \u00f6rnek veritaban\u0131ndaki stoktaki kitap say\u0131s\u0131 gibi say\u0131sal de\u011ferlerle \u00e7al\u0131\u015f\u0131r. Bunlar, veritaban\u0131nda hesaplamalar yapmak ve sonu\u00e7lar\u0131 ihtiya\u00e7lar\u0131n\u0131za g\u00f6re uyarlamak i\u00e7in kullan\u0131labilir.<\/p>\n Yuvarlama, SQL'de matematiksel fonksiyonlar\u0131n en yayg\u0131n kullan\u0131mlar\u0131ndan biridir. Diyelim ki t\u00fcm kitaplar\u0131n fiyatlar\u0131n\u0131 alman\u0131z gerekiyor, ancak yaln\u0131zca de\u011ferlerin en yak\u0131n dolara yuvarlanmas\u0131n\u0131 istiyorsunuz. Bunu yapmak i\u00e7in \u015fu fonksiyonu kullanabilirsiniz: A\u015fa\u011f\u0131daki komutu \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n A\u015fa\u011f\u0131dakine benzer bir \u00e7\u0131kt\u0131 g\u00f6receksiniz:<\/p>\n Yukar\u0131daki komut s\u00fctunlar\u0131n de\u011ferlerini d\u00f6nd\u00fcr\u00fcr. ROUND i\u015flevi, yuvarlaman\u0131n ger\u00e7ekle\u015ftirilece\u011fi ondal\u0131k basamak say\u0131s\u0131n\u0131 belirten ek ba\u011f\u0131ms\u0131z de\u011fi\u015fkenlerin yan\u0131 s\u0131ra s\u00fctun ad\u0131 yerine aritmetik i\u015flemleri de kabul edebilir. \u00d6rne\u011fin, a\u015fa\u011f\u0131daki sorguyu \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n A\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 alacaks\u0131n\u0131z:<\/p>\n Uygulamak MySQL'e entegre edilmi\u015f di\u011fer matematiksel fonksiyonlar aras\u0131nda trigonometrik fonksiyonlar, karek\u00f6kler, kuvvetler, logaritmalar ve \u00fcsler bulunur. SQL'de matematiksel fonksiyonlar\u0131n kullan\u0131m\u0131 hakk\u0131nda daha fazla bilgiyi "SQL'de Matematiksel \u0130fadeler ve Toplama Fonksiyonlar\u0131 Nas\u0131l Kullan\u0131l\u0131r" ba\u015fl\u0131kl\u0131 e\u011fitimde bulabilirsiniz.<\/p>\n Bir sonraki b\u00f6l\u00fcmde SQL fonksiyonlar\u0131n\u0131 kullanarak veritaban\u0131ndaki metinleri i\u015fleyeceksiniz.<\/p>\n SQL'deki metin fonksiyonlar\u0131, dize de\u011ferlerini i\u015flemek ve de\u011fi\u015ftirmek i\u00e7in kullan\u0131labilir. \u00d6rne\u011fin, kitap adlar\u0131n\u0131 ba\u015fl\u0131klar\u0131n\u0131n ba\u015f\u0131ndaki ve sonundaki fazladan bo\u015fluklar\u0131 kald\u0131rarak daha d\u00fczenli bir \u015fekilde almak isteyebilirsiniz.<\/p>\n Bunu yapmak i\u00e7in \u015fu i\u015flevi kullanabilirsiniz: A\u015fa\u011f\u0131daki \u00f6rne\u011fi ele alal\u0131m:<\/p>\n Ekrana a\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131 yazd\u0131r\u0131l\u0131r:<\/p>\n LOWER SQL fonksiyonu tek bir arg\u00fcman al\u0131r ve i\u00e7eri\u011fini k\u00fc\u00e7\u00fck harfe d\u00f6n\u00fc\u015ft\u00fcr\u00fcr. AS title_downcase takma adl\u0131 s\u00fctun arac\u0131l\u0131\u011f\u0131yla, elde edilen veriler title_downcase adl\u0131 ge\u00e7ici bir s\u00fctunda sunulur.<\/p>\n \u015eimdi t\u00fcm yazarlar\u0131 b\u00fcy\u00fck harfe d\u00f6n\u00fc\u015ft\u00fcrerek al\u0131n. A\u015fa\u011f\u0131daki SQL sorgusunu \u00e7al\u0131\u015ft\u0131rmay\u0131 deneyin:<\/p>\n A\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 alacaks\u0131n\u0131z:<\/p>\n LOWER i\u015flevi yerine, benzer \u015fekilde \u00e7al\u0131\u015fan ancak metni b\u00fcy\u00fck harfe d\u00f6n\u00fc\u015ft\u00fcren UPPER i\u015flevini kulland\u0131n\u0131z. Veri al\u0131rken b\u00fcy\u00fck\/k\u00fc\u00e7\u00fck harf tutarl\u0131l\u0131\u011f\u0131n\u0131 sa\u011flamak istiyorsan\u0131z her iki i\u015flevi de kullanabilirsiniz.<\/p>\n Bir di\u011fer kullan\u0131\u015fl\u0131 dize i\u015fleme i\u015flevi de, metin de\u011ferlerini tutan birden fazla arg\u00fcman al\u0131p bunlar\u0131 birle\u015ftiren CONCAT't\u0131r. Yazarlar\u0131 ve kitap ba\u015fl\u0131klar\u0131n\u0131 tek bir s\u00fctunda almay\u0131 deneyin. Bunu yapmak i\u00e7in a\u015fa\u011f\u0131daki ifadeyi \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n Bu komut a\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 d\u00f6nd\u00fcr\u00fcr:<\/p>\n CONCAT i\u015flevi birden fazla dizeyi birle\u015ftirir ve \u00fc\u00e7 ba\u011f\u0131ms\u0131z de\u011fi\u015fkenle y\u00fcr\u00fct\u00fcl\u00fcr. \u0130lk ba\u011f\u0131ms\u0131z de\u011fi\u015fken, yazar\u0131n ad\u0131n\u0131 i\u00e7eren yazar s\u00fctununu ifade eder. \u0130kincisi, :, yazarlar\u0131 ve kitap ba\u015fl\u0131klar\u0131n\u0131 iki nokta \u00fcst \u00fcste i\u015faretiyle ay\u0131rmak i\u00e7in kullan\u0131lan keyfi bir dize de\u011feridir. Sonuncusu, ba\u015fl\u0131k, kitap ba\u015fl\u0131klar\u0131n\u0131 i\u00e7eren s\u00fctunu ifade eder.<\/p>\n Bu sorgu sonucunda yazarlar ve ba\u015fl\u0131klar, veritaban\u0131 motoru taraf\u0131ndan do\u011frudan birle\u015ftirilen full_title adl\u0131 ge\u00e7ici bir s\u00fctunda d\u00f6nd\u00fcr\u00fcl\u00fcr.<\/p>\n MySQL'e entegre di\u011fer dize i\u015flevleri aras\u0131nda dizeleri arama ve de\u011fi\u015ftirme, alt dizeleri alma, dize de\u011ferlerini doldurma ve k\u0131rpma, d\u00fczenli ifadeler uygulama vb. i\u015flevler bulunur. Birden fazla de\u011feri birle\u015ftirmek i\u00e7in SQL i\u015flevlerini kullanma hakk\u0131nda daha fazla bilgiyi "SQL'de CAST \u0130\u015flevleri ve Birle\u015ftirme \u0130fadeleriyle Verileri \u0130\u015fleme" ba\u015fl\u0131kl\u0131 e\u011fitimde bulabilirsiniz. Ayr\u0131ca MySQL belgelerindeki Dize \u0130\u015flevleri ve Operat\u00f6rleri b\u00f6l\u00fcm\u00fcne de ba\u015fvurabilirsiniz.<\/p>\n Bir sonraki b\u00f6l\u00fcmde, veritaban\u0131ndaki tarihleri i\u015flemek i\u00e7in SQL fonksiyonlar\u0131n\u0131 kullanacaks\u0131n\u0131z.<\/p>\n SQL'deki tarih ve saat i\u015flevleri, SQL sorgular\u0131n\u0131 i\u015flerken tarih ve zaman damgalar\u0131n\u0131 tutan s\u00fctunlarda depolanan de\u011ferleri i\u015flemenize olanak tan\u0131r. Tarih bilgilerinin bir k\u0131sm\u0131n\u0131 ay\u0131klamak, tarih hesaplamalar\u0131 yapmak veya tarih ve zaman damgalar\u0131n\u0131 gerekli \u00e7\u0131kt\u0131 bi\u00e7imlerine bi\u00e7imlendirmek i\u00e7in kullan\u0131labilirler.<\/p>\n \u00c7\u0131kt\u0131da tek bir tarih s\u00fctunu olmas\u0131 yerine, kitab\u0131n yay\u0131n tarihini y\u0131l, ay ve g\u00fcne ayr\u0131 ayr\u0131 b\u00f6lmeniz gerekti\u011fini varsayal\u0131m.<\/p>\n A\u015fa\u011f\u0131daki komutu \u00e7al\u0131\u015ft\u0131rmay\u0131 deneyin:<\/p>\n A\u015fa\u011f\u0131dakine benzer bir \u00e7\u0131kt\u0131 g\u00f6receksiniz:<\/p>\n Bu SQL ifadesi \u00fc\u00e7 ayr\u0131 i\u015flev kullan\u0131r: YIL, AY ve G\u00dcN. Her i\u015flev, tarihlerin depoland\u0131\u011f\u0131 s\u00fctunun ad\u0131n\u0131 ba\u011f\u0131ms\u0131z de\u011fi\u015fken olarak al\u0131r ve tam tarihin yaln\u0131zca bir k\u0131sm\u0131n\u0131 (s\u0131ras\u0131yla y\u0131l, ay veya g\u00fcn) \u00e7\u0131kar\u0131r. Bu i\u015flevleri kullanarak, SQL sorgular\u0131nda bir tarihin ayr\u0131 ayr\u0131 par\u00e7alar\u0131na eri\u015febilirsiniz.<\/p>\n Tarihleri de\u011fi\u015ftirmek i\u00e7in bir di\u011fer kullan\u0131\u015fl\u0131 i\u015flev ise, iki tarih aras\u0131ndaki g\u00fcn say\u0131s\u0131n\u0131 alman\u0131z\u0131 sa\u011flayan DATEDIFF'tir. \u015eimdi, her kitab\u0131n tan\u0131t\u0131ld\u0131\u011f\u0131 tarih ile ge\u00e7erli tarih aras\u0131nda ka\u00e7 g\u00fcn ge\u00e7ti\u011fini kontrol edin.<\/p>\n A\u015fa\u011f\u0131daki sorguyu \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n Ekrana a\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131 yazd\u0131r\u0131l\u0131r:<\/p>\n DATEDIFF i\u015flevi iki ba\u011f\u0131ms\u0131z de\u011fi\u015fken al\u0131r: ba\u015flang\u0131\u00e7 tarihi ve biti\u015f tarihi. DATEDIFF i\u015flevi, bu iki noktay\u0131 ay\u0131ran g\u00fcn say\u0131s\u0131n\u0131 hesaplar. Biti\u015f tarihi daha erkense, sonu\u00e7 negatif bir say\u0131 olabilir. Bu \u00f6rnekte, ilk ba\u011f\u0131ms\u0131z de\u011fi\u015fken, envanter tablosundaki tarihleri tutan introduction_date s\u00fctununun ad\u0131d\u0131r. \u0130kinci ba\u011f\u0131ms\u0131z de\u011fi\u015fken ise ge\u00e7erli sistem tarihini temsil eden CURRENT_DATE adl\u0131 ba\u015fka bir i\u015flevdir. Bu sorguyu \u00e7al\u0131\u015ft\u0131rmak, bu iki zaman noktas\u0131 aras\u0131ndaki g\u00fcn say\u0131s\u0131n\u0131 al\u0131r ve sonu\u00e7lar\u0131 days_since adl\u0131 ge\u00e7ici bir s\u00fctuna yerle\u015ftirir.<\/p>\n Not: DATEDIFF, resmi SQL standart fonksiyon setinin bir par\u00e7as\u0131 de\u011fildir. Bir\u00e7ok veritaban\u0131 bu fonksiyonu desteklese de, s\u00f6zdizimi genellikle farkl\u0131 veritaban\u0131 motorlar\u0131 aras\u0131nda farkl\u0131l\u0131k g\u00f6sterir. Bu \u00f6rnek, yerel MySQL s\u00f6zdizimini takip eder.<\/span><\/p>\n MySQL'e entegre edilmi\u015f di\u011fer tarih i\u015fleme i\u015flevleri aras\u0131nda zaman aral\u0131klar\u0131n\u0131 ve tarihleri ekleyip \u00e7\u0131karmak, tarihleri farkl\u0131 dil bi\u00e7imleri i\u00e7in bi\u00e7imlendirmek, g\u00fcn ve ay adlar\u0131n\u0131 almak veya yeni tarih de\u011ferleri olu\u015fturmak yer al\u0131r. SQL'de tarihlerle \u00e7al\u0131\u015fma hakk\u0131nda daha fazla bilgiyi SQL'de Tarih ve Saatlerle \u00c7al\u0131\u015fma e\u011fitiminde bulabilirsiniz. Ayr\u0131ca MySQL belgelerindeki Tarih ve Saat \u0130\u015flevleri b\u00f6l\u00fcm\u00fcne de ba\u015fvurabilirsiniz.<\/p>\n Bir sonraki b\u00f6l\u00fcmde toplama fonksiyonlar\u0131n\u0131n nas\u0131l kullan\u0131laca\u011f\u0131n\u0131 \u00f6\u011freneceksiniz.<\/p>\n \u00d6nceki \u00f6rneklerin hepsinde, bir kitap\u00e7\u0131daki bir kitab\u0131 temsil eden bir sat\u0131rdaki tek tek s\u00fctun de\u011ferlerine d\u00f6n\u00fc\u015f\u00fcmler veya hesaplamalar uygulamak i\u00e7in SQL i\u015flevlerini kulland\u0131n\u0131z. SQL, t\u00fcm veri k\u00fcmesi hakk\u0131nda toplu bilgi bulman\u0131za yard\u0131mc\u0131 olmak i\u00e7in birden fazla sat\u0131rda matematiksel hesaplamalar ger\u00e7ekle\u015ftirmenin bir yolunu sunar.<\/p>\n SELECT sorgunuza birden fazla toplama i\u015flevi ekleyebilirsiniz. Bir kitap\u00e7\u0131da listelenen kitap say\u0131s\u0131n\u0131, mevcut her kitab\u0131n maksimum fiyat\u0131n\u0131 ve t\u00fcm katalogdaki ortalama fiyat\u0131 kontrol etmek istedi\u011finizi varsayal\u0131m. Bunu yapmak i\u00e7in a\u015fa\u011f\u0131daki ifadeyi \u00e7al\u0131\u015ft\u0131r\u0131n:<\/p>\n Bu komut a\u015fa\u011f\u0131daki \u00e7\u0131kt\u0131y\u0131 d\u00f6nd\u00fcr\u00fcr:<\/p>\n Yukar\u0131daki sorgu ayn\u0131 anda \u00fc\u00e7 toplama i\u015flevi kullan\u0131r. COUNT i\u015flevi, sorgunun arad\u0131\u011f\u0131 sat\u0131rlar\u0131 sayar. Bu \u00f6rnekte, ba\u015fl\u0131k bir arg\u00fcman olarak ge\u00e7irilir, ancak sat\u0131r say\u0131s\u0131 i\u015faretli her s\u00fctun i\u00e7in ayn\u0131 olaca\u011f\u0131ndan, i\u015flev arg\u00fcman\u0131 olarak ba\u015fka herhangi bir s\u00fctun ad\u0131 kullanabilirsiniz. MAX i\u015flevi, fiyat s\u00fctunundaki maksimum de\u011feri hesaplar: Burada s\u00fctun ad\u0131 \u00f6nemlidir, \u00e7\u00fcnk\u00fc hesaplama bu s\u00fctundaki de\u011ferler \u00fczerinden yap\u0131l\u0131r. Son i\u015flev, fiyat s\u00fctunundaki t\u00fcm fiyatlar\u0131n ortalamas\u0131n\u0131 hesaplayan AVG i\u015flevidir.<\/p>\n Toplama i\u015flevlerini bu \u015fekilde kullanmak, veritaban\u0131na, toplam hesaplamalar\u0131n de\u011ferlerini temsil eden ge\u00e7ici s\u00fctunlar i\u00e7eren bir sat\u0131r\u0131n d\u00f6nd\u00fcr\u00fclmesiyle sonu\u00e7lan\u0131r. Kaynak sat\u0131rlar hesaplama i\u00e7in dahili olarak kullan\u0131l\u0131r, ancak sorgu arac\u0131l\u0131\u011f\u0131yla d\u00f6nd\u00fcr\u00fclmez. Bu \u00f6rnekte, \u00f6zet i\u00e7in t\u00fcm sat\u0131rlar\u0131 hesaba katarak, t\u00fcm envanter tablosundan istatistiksel de\u011ferleri tek seferde hesaplamak i\u00e7in toplama i\u015flevlerini kulland\u0131n\u0131z.<\/p>\n SQL ile bir tablonun sat\u0131rlar\u0131n\u0131 gruplara ay\u0131rabilir ve ard\u0131ndan bu gruplar\u0131n toplam\u0131n\u0131 ayr\u0131 ayr\u0131 hesaplayabilirsiniz. \u00d6rne\u011fin, hangi yazar\u0131n en pahal\u0131 kitaplar\u0131 yay\u0131nlad\u0131\u011f\u0131n\u0131 bulmak i\u00e7in farkl\u0131 yazarlar\u0131n kitaplar\u0131n\u0131n ortalama fiyat\u0131n\u0131 hesaplayabilirsiniz. Bu t\u00fcr hesaplamalar i\u00e7in sat\u0131rlar\u0131 gruplama hakk\u0131nda daha fazla bilgiyi SQL'de GROUP BY ve ORDER BY Kullan\u0131m\u0131 e\u011fitiminde bulabilirsiniz. Ayr\u0131ca, SQL'de Matematiksel \u0130fadeler ve Toplama Fonksiyonlar\u0131 Kullan\u0131m\u0131 e\u011fitiminde toplamalar\u0131 kullanma hakk\u0131nda daha fazla bilgi edinebilirsiniz.<\/p>\n Bu k\u0131lavuzda, veriler \u00fczerinde hesaplamalar yapmak ve daha karma\u015f\u0131k sorgular olu\u015fturmak i\u00e7in SQL'deki fonksiyonlar\u0131 nas\u0131l kullanaca\u011f\u0131n\u0131z\u0131 \u00f6\u011frendiniz. Bu fonksiyonlar, verileri farkl\u0131 \u015fekillerde i\u015flemenize ve daha iyi sonu\u00e7lar elde etmenize yard\u0131mc\u0131 olur. SQL'in tam s\u00f6zdiziminin, ili\u015fkisel veritaban\u0131 y\u00f6netim sisteminizin (RDBMS) t\u00fcr\u00fcne ba\u011fl\u0131 olarak de\u011fi\u015febilece\u011fini unutmay\u0131n.<\/p>","protected":false},"excerpt":{"rendered":"Giri\u015f \u0130li\u015fkisel veritabanlar\u0131 ve Yap\u0131land\u0131r\u0131lm\u0131\u015f Sorgu Dili (SQL) ile \u00e7al\u0131\u015f\u0131rken \u015funlar\u0131 yapabilirsiniz\u2026","protected":false},"author":1,"featured_media":16374,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_yoast_wpseo_focuskw":"\u0646\u062d\u0648\u0647 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 \u062a\u0648\u0627\u0628\u0639 SQL","_yoast_wpseo_title":"","_yoast_wpseo_metadesc":"","_yoast_wpseo_canonical":"","_yoast_wpseo_opengraph-description":"","_yoast_wpseo_opengraph-image":"","_yoast_wpseo_twitter-description":"","_yoast_wpseo_twitter-image":"","_yoast_wpseo_focuskeywords":"","_yoast_wpseo_primary_category":"193","footnotes":""},"categories":[193,349],"tags":[403,456],"class_list":{"0":"post-16372","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-tutorials","8":"category-database","9":"tag-data-base","10":"tag-sql"},"yoast_head":"\nMySQL'e ba\u011flanma ve \u00f6rnek bir veritaban\u0131 kurma<\/h3>\n
ssh sammy@your_server_ip<\/code><\/pre>\n<\/div>\nSammy<\/code> MySQL kullan\u0131c\u0131 ad\u0131n\u0131z\u0131 girin:<\/p>\nmysql -u sammy -p<\/code><\/pre>\n<\/div>\nCREATE DATABASE bookstore;<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 1 row affected (0.01 sec)<\/code><\/pre>\n<\/div>\nUSE bookstore;<\/code><\/pre>\n<\/div>\nOutput\r\nDatabase changed<\/code><\/pre>\n<\/div>\n\n
CREATE TABLE inventory (\r\nbook_id int,\r\nauthor varchar(50),\r\ntitle varchar(200),\r\nintroduction_date date,\r\nstock int,\r\nprice decimal(5, 2),\r\nPRIMARY KEY (book_id)\r\n);<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/pre>\n<\/div>\nINSERT INTO inventory\r\nVALUES\r\n(1, 'Oscar Wilde', 'The Picture of Dorian Gray', '2022-10-01', 4, 20.83),\r\n(2, 'Jane Austen', 'Pride and Prejudice', '2022-10-04', 12, 42.13),\r\n(3, 'Herbert George Wells', 'The Time Machine', '2022-09-23', 7, 21.99),\r\n(4, 'Mary Shelley', 'Frankenstein', '2022-07-23', 9, 17.43),\r\n(5, 'Mark Twain', 'The Adventures of Huckleberry Finn', '2022-10-01', 14, 23.15);<\/code><\/pre>\n<\/div>\nOutput\r\nQuery OK, 5 rows affected (0.00 sec)\r\nRecords: 5 Duplicates: 0 Warnings: 0\r\n<\/code><\/pre>\n<\/div>\nSQL fonksiyonlar\u0131n\u0131 anlama<\/h2>\n
SELECT title, price, introduction_date FROM inventory ORDER BY price DESC;<\/code><\/pre>\n<\/div>\nOutput\r\n+------------------------------------+-------+-------------------+\r\n| title | price | introduction_date |\r\n+------------------------------------+-------+-------------------+\r\n| Pride and Prejudice | 42.13 | 2022-10-04 |\r\n| The Adventures of Huckleberry Finn | 23.15 | 2022-10-01 |\r\n| The Time Machine | 21.99 | 2022-09-23 |\r\n| The Picture of Dorian Gray | 20.83 | 2022-10-01 |\r\n| Frankenstein | 17.43 | 2022-07-23 |\r\n+------------------------------------+-------+-------------------+\r\n5 rows in set (0.000 sec)<\/code><\/pre>\n<\/div>\nba\u015fl\u0131k<\/code>, fiyat<\/code> Ve giri\u015f_tarihi<\/code> s\u00fctunlar\u0131n adlar\u0131d\u0131r ve \u00e7\u0131kt\u0131da, veritaban\u0131 her kitap i\u00e7in bu s\u00fctunlardan al\u0131nan bozulmam\u0131\u015f de\u011ferleri g\u00f6sterir: kitab\u0131n tam ad\u0131, fiyat\u0131 ve kitab\u0131n k\u00fct\u00fcphaneye ula\u015ft\u0131\u011f\u0131 tarih.<\/p>\n\n
SELECT EXAMPLE(price) AS new_price FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nMatematiksel fonksiyonlar\u0131n kullan\u0131m\u0131<\/h2>\n
YUVARLAK<\/code> Yuvarlama i\u015flemini yapan kullan\u0131m.<\/p>\nSELECT title, price, ROUND(price) AS rounded_price FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+------------------------------------+-------+---------------+\r\n| title | price | rounded_price |\r\n+------------------------------------+-------+---------------+\r\n| The Picture of Dorian Gray | 20.83 | 21 |\r\n| Pride and Prejudice | 42.13 | 42 |\r\n| The Time Machine | 21.99 | 22 |\r\n| Frankenstein | 17.43 | 17 |\r\n| The Adventures of Huckleberry Finn | 23.15 | 23 |\r\n+------------------------------------+-------+---------------+\r\n5 rows in set (0.000 sec)<\/code><\/pre>\n<\/div>\nba\u015fl\u0131k<\/code> Ve fiyat<\/code> ge\u00e7ici bir s\u00fctunla yuvarlat\u0131lm\u0131\u015f_fiyat<\/code> Fonksiyonun sonucu nedir? ROUND(fiyat)<\/code> Bu fonksiyon bir arg\u00fcman al\u0131r, bu da s\u00fctun ad\u0131d\u0131r (burada, fiyat<\/code>) ve tabloda o s\u00fctundaki de\u011ferleri en yak\u0131n tam say\u0131ya yuvarlar.<\/p>\nSELECT title, price, ROUND(price * stock, 1) AS stock_price FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+------------------------------------+-------+-------+-------------+\r\n| title | stock | price | stock_price |\r\n+------------------------------------+-------+-------+-------------+\r\n| The Picture of Dorian Gray | 4 | 20.83 | 83.3 |\r\n| Pride and Prejudice | 12 | 42.13 | 505.6 |\r\n| The Time Machine | 7 | 21.99 | 153.9 |\r\n| Frankenstein | 9 | 17.43 | 156.9 |\r\n| The Adventures of Huckleberry Finn | 14 | 23.15 | 324.1 |\r\n+------------------------------------+-------+-------+-------------+\r\n5 rows in set (0.000 sec)<\/code><\/pre>\n<\/div>\nYUVARLAK(Fiyat * Stok, 1)<\/code> \u00d6nce tek bir kitab\u0131n fiyat\u0131, mevcut kitap say\u0131s\u0131yla \u00e7arp\u0131l\u0131r ve ard\u0131ndan elde edilen fiyat ilk ondal\u0131k basama\u011fa yuvarlan\u0131r. Sonu\u00e7, ge\u00e7ici "Stok_Fiyat\u0131" s\u00fctununda g\u00f6sterilir.<\/p>\nMetin i\u015flevlerini kullanma<\/h2>\n
KIRPMA<\/code> Bir dizenin ba\u015f\u0131nda ve sonunda bulunan t\u00fcm bo\u015fluklar\u0131 ve fazladan karakterleri kald\u0131rmak i\u00e7in bu fonksiyonu kullan\u0131n.<\/p>\nSELECT LOWER(title) AS title_lowercase FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+------------------------------------+\r\n| title_lowercase |\r\n+------------------------------------+\r\n| the picture of dorian gray |\r\n| pride and prejudice |\r\n| the time machine |\r\n| frankenstein |\r\n| the adventures of huckleberry finn |\r\n+------------------------------------+\r\n5 rows in set (0.001 sec)\r\n<\/code><\/pre>\n<\/div>\nSELECT UPPER(author) AS author_uppercase FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+----------------------+\r\n| author_uppercase |\r\n+----------------------+\r\n| OSCAR WILDE |\r\n| JANE AUSTEN |\r\n| HERBERT GEORGE WELLS |\r\n| MARY SHELLEY |\r\n| MARK TWAIN |\r\n+----------------------+\r\n5 rows in set (0.000 sec)<\/code><\/pre>\n<\/div>\nSELECT CONCAT(author, ': ', title) AS full_title FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+------------------------------------------------+\r\n| full_title |\r\n+------------------------------------------------+\r\n| Oscar Wilde: The Picture of Dorian Gray |\r\n| Jane Austen: Pride and Prejudice |\r\n| Herbert George Wells: The Time Machine |\r\n| Mary Shelley: Frankenstein |\r\n| Mark Twain: The Adventures of Huckleberry Finn |\r\n+------------------------------------------------+\r\n5 rows in set (0.001 sec)<\/pre>\n<\/div>\n
Tarih ve saat i\u015flevlerini kullanma<\/h2>\n
SELECT introduction_date, YEAR(introduction_date) as year, MONTH(introduction_date) as month, DAY(introduction_date) as day FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+-------------------+------+-------+------+\r\n| introduction_date | year | month | day |\r\n+-------------------+------+-------+------+\r\n| 2022-10-01 | 2022 | 10 | 1 |\r\n| 2022-10-04 | 2022 | 10 | 4 |\r\n| 2022-09-23 | 2022 | 9 | 23 |\r\n| 2022-07-23 | 2022 | 7 | 23 |\r\n| 2022-10-01 | 2022 | 10 | 1 |\r\n+-------------------+------+-------+------+\r\n5 rows in set (0.000 sec)<\/code><\/pre>\n<\/div>\nSELECT introduction_date, DATEDIFF(introduction_date, CURRENT_DATE()) AS days_since FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+-------------------+------------+\r\n| introduction_date | days_since |\r\n+-------------------+------------+\r\n| 2022-10-01 | -30 |\r\n| 2022-10-04 | -27 |\r\n| 2022-09-23 | -38 |\r\n| 2022-07-23 | -100 |\r\n| 2022-10-01 | -30 |\r\n+-------------------+------------+\r\n5 rows in set (0.000 sec)<\/code><\/pre>\n<\/div>\nToplama i\u015flevlerini kullanma<\/h2>\n
\n
SELECT COUNT(title) AS count, MAX(price) AS max_price, AVG(price) AS avg_price FROM inventory;\r\n<\/code><\/pre>\n<\/div>\nOutput\r\n+-------+-----------+-----------+\r\n| count | max_price | avg_price |\r\n+-------+-----------+-----------+\r\n| 5 | 42.13 | 25.106000 |\r\n+-------+-----------+-----------+\r\n1 row in set (0.001 sec)<\/code><\/pre>\n<\/div>\nSonu\u00e7<\/h2>\n