... konulu sunumlar: "5. Temel SQL (Basic SQL) 5.1. SQL Veri Tanımlama ve Veri Tipleri (SQL Data Definition and Data Types) 5.1.1. SQL de Şema ve Katalog Kavramı (Schema and."— Sunum transkripti:
1 5. Temel SQL (Basic SQL)5.1. SQL Veri Tanımlama ve Veri Tipleri (SQL Data Definition and Data Types)SQL de Şema ve Katalog Kavramı (Schema and Catalog Concepts in SQL)SQL de CREATE TABLE Komutu (The CREATE TABLE Command in SQL)SQL de Nitelik Veri Tipleri ve Domainler (Attribute Data Types and Domains in SQL)5.2. SQL de Kısıtların Belirlenmesi (Specifying Constraints in SQL)Nitelik Kısıtlamalarının ve Nitelik Defaultları(Specifying Attribute Constraints and Attribute Defaults)Anahtar ve Refarans Bütünlüğü Kısıtlarının Belirlenmesi(Specifying Key and Referential Integrity Constraints)Kısıtlara İsim Verilmesi (Giving Names to Constraints)CHECK Kullanarak Tuplelar üzerinde Kısıt Belirleme(Specifying Constraints on Tuples Using CHECK)5.3. SQL de Temel Veri Alma Sorguları (Basic Retrieval Queries in SQL)Temel SQL Sorgularında SELECT-FROM-WHERE Yapısı(The SELECT-FROM-WHERE Structure of Basic SQL Queries)Belirsiz Nitelik İsimleri, Takma İsim Kullanma, Yeniden Adlandırma ve Tuple Değişkenleri(Ambiguous Attribute Names, Aliasing, Renaming, and Tuple Variables)WHERE Şartının Belirlenmemesi ve Asterisk Kullanma(Unspecified WHERE Clause and Use of the Asterisk)SQLde Küme gibi Tablolar (Tables as Sets in SQL)
2 5.3.5. Alt Dizi Deseni Eşleştirme ve Aritmetik Operatörler (Substring Pattern Matching and Arithmetic Operators)Sorgu Sonuçlarının Düzenlenmesi (Ordering of Query Results)5.4. SQLde INSERT, DELETE ve UPDATE Komutları(INSERT, DELETE, and UPDATE Statements in SQL)INSERT Komutu (The INSERT Command)DELETE Komutu (The DELETE Command)UPDATE Komutu (The UPDATE Command)5.5. SQLin Ek Özellikleri (Additional Features of SQL)
3 5. Temel SQL (Basic SQL)SQL dili, ilişkisel veritabanlarının ticari başarısının önemli nedenlerinden biri olarak kabul edilebilir. İlişkisel veri tabanları için bir standart olduğundan, kullanıcılar database sisteminin diğer çeşitlerinden database uygulaması uyarlanması konusunda daha az endişe duyarlar. Örneğin network ve hiyerarşik sistemlerden ilişkisel sisteme. Eğer kullanıcı belli bir DBMS ürününden memnun kalmaz ise, iki sisteminde aynı dil standardına sahip olmasından dolayı başka bir ilişkisel DBMSe çevirme işleminin çok pahalı ve zaman alıcı bir işlem olması beklenmez. Bir standarda sahip olmasının diğer bir avantajı, DBMSin dil değiştirmesine gerek kalmadan birden fazla database'e erişim sağlayabilmesidir.SQL isminin açılımı, Structured Query Language (Yapısal Sorgulama Dili)dir. Başlangıçta, ismi SEQUEL (Structured English QUEry Language) idi ve IBM araştırmada, SYSTEM R denen deneysel ilişkisel veritabanı sistemi için bir arayüz olarak tasarlanmış ve uygulanmıştır. Amerikan Ulusal Standartlar Enstitüsü (ANSI) ve Uluslararası Standartlar Organizasyonu (ISO) tarafından yürütülen ortak bir çaba ile SQL-86 yada SQL1 adında standart bir versiyon oluşturulmuştur. (ANSI 1986) Bu gelişim süreci 1992 de SQL-92, 1999da SQL3, SQL:2003 ve içerisine XML özellikleri eklenen SQL:2006 şeklinde devam etmiştir de yapılan son güncellemeyle daha fazla nesne tabanlı bir dil haline gelmiştir. Biz bu son sürümünün üzerine gideceğiz.SQL kapsamlı bir veritabanı dilidir: veri tanımları, sorgular ve güncellemeler için ifadeleri vardır. Dolayısıyla, hem DDL ve hem de DML barındırır. Buna ek olarak, bütünlük kısıtlamalarını tanımlamak, güvenlik ve yetki belirlemek, işlem kontrolleri belirlemek ve veritabanı viewlerini tanımlamak için olanaklara sahiptir. Aynı zamanda Java, COBOL, ya da C / C ++ gibi genel amaçlı programlama dilleri içine SQL ifadelerini gömmek için kuralları vardır.
4 5.1. SQL Veri Tanımlama ve Veri Tipleri (SQL Data Definition and Data Types) SQL, ilişkisel model için sırasıyla tablo, satır, sütun gibi terimler yerine relation (varlık), tuple ve nitelik gibi terimler kullanır. Veri tanımlama için ana SQL komutu CREATE (Yaratmak/oluşturmak)tir. Bu şema, tablo ve domain yanısıra view, assertion ve trigger tanımlamak için de kullanılır.CREATE komusutunu tanımlamadan önce 5.1.1de şema be katalog kavramını tartışacağız de tablo nasıl oluşturulur, de nitelik tanımlama için önemli veri türlerini göreceğiz.SQL de Şema ve Katalog Kavramı (Schema and Catalog Concepts in SQL)SQLin ilk sürümleri ilişkisel veritabanı şema kavramını içermiyordu; tüm tablolar (varlıklar) aynı şemanın bir parçası oalrak görülüyordu. SQL şema kavramı, tablo ve diğer yapıları bir araya getirmek amacıyla SQL2 ile birlikte SQLe katılmaya başlanmış. SQL şema, şema ismiyle tanımlanır ve user veya kendi şemasına sahip hesapları yetkilendirici uygulmaların yanı sıra şemadaki her bir elemanı tanımlayıcılar içerir. Şema elemanları tablolar, kısıtlar, viewler, domainler ve yetkilendiriciler gibi yapıları içerir.Şema, tüm şema elemanlarının tanımlarıyla beraber CREATE SCHEMA komutu ile oluşturulur. Örneğin; aşağıdaki komut ile, yetki verilmiş ‘Jsmith’ kullanıcısı için COMPANY adında şema oluşturur.(SQL her komut noktalı virgül ile biter unutmayın.)CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;Genel olarak, tüm kullanıcılar şemaları ve şema öğeleri oluşturmak için yetkili değildir Şema, tablo ve diğer yapıları oluşturma yetkisi, DBA tarafından ilgili kullanıcı hesaplarına verilmelidir.
5 5.1.2. SQL de CREATE TABLE Komutu (The CREATE TABLE Command in SQL) Şema kavramına ek olarak SQL, şemalar koleksiyonu olarak adlandırılan katalog kavramını da kullanır. Katalog her zaman, katalogdaki tüm şemala ve tüm eleman tanımları hakkında bilgi içeren INFORMATION_SCHEMA adında özel bir şema içerir. Yalnızca aynı katalog içindeki şemada bulunan iki varlık arasında referans bütünlüğü gibi kısıtlamalar tanımlanabilir.SQL de CREATE TABLE Komutu (The CREATE TABLE Command in SQL)CREATE TABLE komutu, isim vererek venitelik ve kısıtlarını belirterek yeni bir varlıktanımlamak için kullanılır. İlk olarak niteliklerbelirtilir, her niteliğe isim,değer domainibelirtilmesi için veri türü ve her nitelik içinNOT NULL gibi kısıtlar belirlenir. Anahtar,varlık bütünlüğü ve referans bütünlüğükısıtları,nitelikler belirlendikten sonraCREATE TABLE komutu ile belirlenir veyaALTER TABLE komutu ile sonradaneklenebilir. Şekil 4.1, SQLde COMPANYilişkisel database şemasınının basit veritanımlamasını gösterir.
6 Genelde, SQL şemada varlık açıkça yazılmaz. Alternatif olarak, açıkça ilişki adına, şema adınıekleyebilirsiniz. Örneğin,CREATE TABLE COMPANY.EMPLOYEE ... (Açık hali)CREATE TABLE EMPLOYEE ...CREATE TABLE komutu ile kesinleştirilen varlıklar baztablo veya baz varlık adı verilir, bunun anlamı DBMStarafından varlık ve tupleların aslında dosya gibioluşturuluduğu ve saklandığıdır. Baz varlıklar,CREATE VIEW komutu ile yaratılan ve aslında fizikselbir dosya olarak saklanmayan sanal varlıklardan ayırtedilir. SQLde, baz tablolardaki nitelikler,CREATE TABLE komutunda belirtilen dizi içindesıralandığı düşünülür. Ancak satırlar (Tuplelar) varlıkiçinde sıralandığı düşünülmez.Şekil 4.1 de hatalara neden olabilecek bazı yabancı anahtarlarında var olduğu önemlidir. Çünkü onlar ya sirküler referans üzerinden yada oluşturulmayan bir tabloya referans verilmesiyle belirtilmiştir. Örnek olarak; EMPLOYEE tablosundaki yabancı anahtar Super_ssn sirküler referanstır. EMPLOYEE tablosundaki yabancı anahtar Dno, henüz oluşturulmamış DEPARTMENT tablosunu referans eder. Bu problemi çözmek için bu kısıtlar CREATE TABLE komutunda değil daha sonra ALTER TABLE komutunda yer alabilir.
7 5.1.3. SQL de Nitelik Veri Tipleri ve Domainler (Attribute Data Types and Domains in SQL) Nitelikler için kullanılabilir temel veri türleri numeric, character string, bit string, Boolean, date, and time gibi türleri içerir.Numeric veri türü; çeşitli boyutlarda integer sayılarını, float, ve real veri türlerini içerir.Character-string veri türü; sabit uzunluklu n sayıda karakter içeren CHAR(n) yada CHARACTER(n) içerir. String değeri belirtirken, her biri tek tırnak içene yazılır. Genelde küçük büyük harfe de duyarlıdır. Sabitlenmiş uzunluklarda karakterler sağ yatık olarak gösterilir. Örneğin;CHAR (10) veri türü için 'Smith' değeri 5 boş karakterden sonra yazılır. ‘abc’ || ‘XYZ’ şeklinde karakter birleştirme de yapılabilir.Bit-string veri türü; sabit uzunluklu BIT(n) yada maksimum n bitli değişken uzunluklu BIT VARYING(n)dir. Örnek B‘10101’dir.Boolean veri türü; doğru yada yanlışı veren 0 yada 1 dir. Burada NULL değerinin de olabileceğini unutmamalı.DATE veri türü; YYYY-MM-DD şeklide tarih yer alır.Y year(yıl), M month (ay), D day (gün). TIME, HH:MM:SS şeklinde yazılır. H hour (saat), M minute (dakika), S second (saniye). Örnek olarak ; DATE ‘ ’ or TIME ‘09:12:47’.Şekil 4.1'de olduğu gibi, doğrudan her niteliğin veri türünü belirtmek mümkündür; alternatif olarak domain belirtilebilir, domain adı nitelik ile birlikte belirtilir. Bu domain için veri türünü daha kolay değiştimemizi sağlar, şemada bir çok nitelikte kullanılmıştır, şemanın okunurluğunu arttırır. Örnek olarak domain SSN_TYPE oluşturulur.CREATE DOMAIN SSN_TYPE AS CHAR(9);
8 5.2. SQL de Kısıtların Belirlenmesi (Specifying Constraints in SQL) Burada bir kez domain tanımlanmasıyla EMPLOYEE de Ssn ve Super_ssn, DEPARTMENT da Mgr_ssn, WORKS_ON da Essn ve DEPENDENT da Essn de kullanılır.5.2. SQL de Kısıtların Belirlenmesi (Specifying Constraints in SQL)Bu bölümde tablo oluştururken belirtilecek temel kısıtlar gösterilmiştir. Anahtar, referans bütünlük kısıtları, nitelik domain kısıtları ve her bir tupleüzerinde bazı kısıtları içerir.Nitelik Kısıtlamalarının veNitelik Defaultları (SpecifyingAttribute Constraints andAttribute Defaults)SQLde NULL değere izin verilmediğinde NOT NULL kısıtı kullanılır. Açıkça görülmeyen bu kısıt tanımlamada yer almalıdır.Tanımlama yaparken DEFAULT <value> ile varsayılan olarak değer ataması yapmak da mümkündür. Şekil 4.2 de default tanımlamasını görmek mümkündür.
9 Yeni eleman yazarken departman numarasına 1, yeni departman yazarken de yöneticisi ‘ ’ ssn nolu çalışan. Her bir default değer ataması olan nitelikte NOT NULL kısıtı olduğuna dikkat etmeliyiz.Başka çeşit bir kısıt ise CHECK şartı ile nitelik veya domain değerini kısıtlamaktır. Örneğin; Departman numaralarının 1-20 arasında olması kısıtı var olsun.Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);CHECK şartı CREATE DOMAIN ile de birleştirilebilir.CREATE DOMAIN D_NUM AS INTEGER CHECK (D_NUM > 0 AND D_NUM < 21);D_NUM domainini oluşturabiliriz. DEPARTMENT'ın Dnumber, PROJECTin Dnum ve EMPLOYEEnin Dno nitelikleri için kullanılabilir.Anahtar ve Refarans Bütünlüğü Kısıtlarının Belirlenmesi (Specifying Key and Referential Integrity Constraints)Anahtar ve referans bütünlüğü kısıtları çok önemli olduğundan, onları tanımlamak için CREATE TABLE ile birlikte özel şartlar vardır. Bunlardan bazıları şekil 4.1 de belirtilmiştir. PRIMARY KEY şartı ile tabloda bir veya daha fazla niteliği birincil anahtar yapabiliriz. Örneğin DEPARTMENTın birincil anahtar tanımlaması,Dnumber INT PRIMARY KEY;UNIQUE şartı alternatif (ikincil) anahtarı belirtir. Şekil 4.1de DEPARTMENT ve PROJECT tablolarında gösterilir.Dname VARCHAR(15) UNIQUE;Şekil 4.1deki gibi referans bütünlüğü FOREIGN KEY tarafından belirlenir.
10 5.2.3. Kısıtlara İsim Verilmesi (Giving Names to Constraints) Daha önce bahsettiğimiz gibi, tuple insert veya delete edildiğinde veya yardımcı veya birincil anahtar niteliği değeri update edildiğinde referans bütünlüğü kısıtı ihlal edilebilir. Bütünlük kısıtını bozacağı düşünülen update işlemi default olarak reddedilir. Ancak şema tasarımcısı, yabancı anahtara referans triggeri ekleyerek alternatif işlemler de belirleyebilir.Bu alternatif işlem SET NULL, CASCADE ve SET DEFAULT seçeneklerini içerebilir. Bu işlem hem delete hemde update işleminde belirtilmelidir. Şekil 4.2 de bu örneklendirilir.Burada database tasarımcısı EMPLOYEEnin Super_ssn'i için ON DELETE SET NULL ve ON UPDATE CASCADE. Bunun anlamı eğer Super_ssn yardımcı anahtarı silinmek isterse buna verilen referanslara NULL değeri atanacak, eğer update edilirse buna verilen referanslar da update edilecek. Bunların dışında ON UPDATE CASCADE işlemi tüm referans verilen tupleları siler, hem ON DELETE hem ON UPDATE işlemi için SET DEFAULT işlemi aynıdır; referans verilen niteliklere belirlenmiş default değerini atar.Kısıtlara İsim Verilmesi (Giving Names to Constraints)Şekil 4.2 de, CONSTRAINT kelimesinden sonra kısıtlara nasıl isim verileceği gösterilmiştir. Kısıt daha sonra silinebilir veya başka bir kısıtla değiştirebilir diye unique bir isim verilmelidir.CHECK Kullanarak Tuplelar üzerinde Kısıt Belirleme (Specifying Constraints on Tuples Using CHECK)Ek olarak anahtar ve referans bütünlük kısıtları ve diğer tablo kısıtları CREATE TABLE komutunun sonuna CHECK şartı eklenerek belirlenebilir. Bunlara Tuple tabanlı kısıtlar da denebilir. Çünkü her tuple eklendiğinde veya update edildiğinde bu tuplelar ayrı ayrı kontrol edilir. Örneğin şekil 4.1 de DEPARTMENT tablosu Dept_create_date isminde bir niteliğe sahiptir. Departmanın açıldığı zamanı tutar. Burada CREATE TABLE komutundan sonra CHECK komutu getirebilir, burada yöneticinin başlama tarihi departmanın açıldığı tarihten sonra olmalıdır.CHECK (Dept_create_date <= Mgr_start_date);
11 5.3. SQL de Temel Veri Alma Sorguları (Basic Retrieval Queries in SQL) SQLin bir veri tabanından bilgi almak için temel komutu; SELECTdir. Şekil 3.5deki şema üzerinde tanımlanmış basit sorguları kullanacağız ve bu basit sorguların sonuçlarını gösterebilmek için şekil 3.6daki basit veritabanı durumlarını refere edeceğiz.Temel SQL Sorgularında SELECT-FROM-WHERE Yapısı (The SELECT-FROM-WHERE Structure of Basic SQL Queries)SQL sorguları çok karmaşık olabilir. Basit örneklerle başlayıp adım adım zorlaştıracağız. SELECT komutunun basit formları bazen mapping veya select-from-where block olarak adlandırılabillir. Bu form 3 ana bölümden oluşur; SELECT, FROM, ve WHERE.SELECT <attribute list>FROM <table list>WHERE <condition>;■ <attribute list> ; bilgi almak istediğimiz nitelik isimlerinin listesi,■ <table list> bilgi almak istediğimiz niteliklerin bulunduğu tabloların isimlerinin listesi,■ <condition> Boolean şartları/ koşulları.SQLde, basit mantıksal karşılaştırma operatörleri vardır. Bunlar condition (koşul) denen bölgede kullanılır. =, <, <=, >, >=, <>
12 Örnekler; Query 0; Adı ‘John B Örnekler; Query 0; Adı ‘John B. Smith’ olan çalışanın doğum tarihini ve adresini getiren sorgu.Q0: SELECT Bdate, AddressFROM EMPLOYEEWHERE Fname=‘John’ AND Minit=‘B’ AND Lname=‘Smith’;Bu sorgu, FROM komutundan sonra sadece EMPLOYEE tablosu olduğundan sadece bu tablodan bilgi alınır. Sadece WHERE koşulundabelirtilen tek bir tuple'ı seçer, dahasonra SELECT komutundalistelenen Bdate ve adresniteliklerini sonucu yansıtır.Verilen WHERE koşulundaBoolean şartına uyan tuplelarındanistenilen nitelik değerleriniSELECT koşulu getirir.Şekil 4.3(a), Şekil 3.6nındatabaseinde Q0 sorgusununsonucu gösterilir.
13 Query 1; Research departmanında çalışan tüm çalışanların isim ve adres bilgilerini getiren sorgu Q1: SELECT Fname, Lname, AddressFROM EMPLOYEE, DEPARTMENTWHERE Dname=‘Research’AND Dnumber=Dno;Q1 sorgusunda 2 tablo yer alır; EMPLOYEE, DEPARTMENT. Where şartında departmanı research olan çalışanları belirtiyor. Bu bilgi DEPARTMENT tablosunda Dname niteliğine aittir. Daha sonra çalışanların bilgilerini istediğinden bir çalışan için iki tabloda yer alan tuplelerı birleştirmeliyiz.Bunun için DEPARTMENT ile EMPLOYEE tablolarını bağlamalıyız. Bu bir join condition’dur. (Katılım şartı).Bu bağlamayı da iki tablo arasında birbirine referans verilen nitelikler ile yapabiliriz; DEPARTMENT dan Dnumber ve EMPLOYEE den Dno değerleri eşitleyerek. Bu şartlarla beraber EMPLOYEE tablosundan istenilen nitelik değerleri getirtilebilir.
14 Yapılan bu sorguya select-project-join sorgusu denir; Burada projecte (ürün) ek sadece selection (seçim) ve join condition (katılım koşulu) vardır. Project; sorgu sonucunda getirilen niteliklerdir.Query 2; ‘Stafford’ konumunda bulunan her projenin proje numarası, control eden departman numarası, departman yöneticisinin soyismi, adresi ve doğum tarihini veren sorgu.Q2: SELECT Pnumber, Dnum, Lname, Address, BdateFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE Dnum=Dnumber AND Mgr_ssn=Ssn ANDPlocation=‘Stafford’;Bu sorguda 2 join vardır. Yani 3 tablo bağlanmıştır; PROJECT, DEPARTMENT, EMPLOYEE. Bu joinler şöyledir; Projenin konumunu belirttiğinden ve bu projeyi kontrol eden departmanı istediğinden Dnum=Dnumber ile DEPARTMENT ve PROJECT; Departman yöneticisinin bilgilerini istediğinden Mgr_ssn=Ssn ile DEPARTMENT ve EMPLOYEE bağlanmıştır. PROJECTden plocation’ı ‘Stafford’ olan tupleların Pnumber, Dnum, Lname, Address, Bdate niteliklerinin değerlerini istiyor.Belirsiz Nitelik İsimleri, Takma İsim Kullanma, Yeniden Adlandırma ve Tuple Değişkenleri (Ambiguous Attribute Names, Aliasing, Renaming, and Tuple Variables)SQLde farklı tablolardaki farklı nitelikler için aynı isim kullanılabilir. Bu durumda nitelik isimlerini tablo ismiyle birlikte kullanarak ayırt edilebilir hale getirmeliyiz. Bunun örneklenmesi için Şekil 3.5 ve 3.6 ya bakabiliriz. EMPLOYEEnin Dno ve Lname nitelikleri Dnumber ve Name ve DEPARTMENTın Dname’i Name olarak adlandırılmıştır. Bunun örneği Q1 sorgusu Q1a olarak tekrarlanmıştır.
15 Q1A: SELECT Fname, EMPLOYEE.Name, Address FROM EMPLOYEE, DEPARTMENTWHERE DEPARTMENT.Name=‘Research’ ANDDEPARTMENT.Dnumber=EMPLOYEE.Dnumber;EMPLOYEE.Name, DEPARTMENT.Name, DEPARTMENT.Dnumber, EMPLOYEE.Dnumber deyimleri ile Name ve Dnumber nitelikleri ayırt edilmiştir. Uzun isim birleştirmeleri için alias (takma isim) verilebilir. Bunun örneği Q8dedir.Query 8. Her çalışan için isim soyisim ve o çalışanının supervisorunun isim ve soyismini getiren sorgu.Q8: SELECT E.Fname, E.Lname, S.Fname, S.LnameFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.Super_ssn=S.Ssn;Burada EMPLOYEE AS E ile EMPLOYEE tablosunun ismine E takma isimini verdik. Çalışanın supervisorunu istediğinden E.Super_ssn=S.Ssn ile iki EMPLOYEE tablosunun birbirine bağladık. E.