{"id":39,"date":"2011-07-07T21:04:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"http:\/\/www.yazilimmutfagi.com\/?p=39"},"modified":"2018-11-20T00:56:26","modified_gmt":"2018-11-19T21:56:26","slug":"sql-alter-komutu-tablo-uzerinde-degisiklik-yapmak","status":"publish","type":"post","link":"https:\/\/www.yazilimmutfagi.com\/index.php\/2011\/07\/07\/sql-alter-komutu-tablo-uzerinde-degisiklik-yapmak\/","title":{"rendered":"SQL Alter Komutu, Tablo \u00dczerinde Degisiklik Yapmak"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">SQL ALTER TABLE KOMUTU<\/h2>\n\n\n\n<p>\n\nBazen mevcut tablomuzda degisiklik yapmamiz gerekir. Var olan tabloya bir alan eklemek, var olan alanin adini, tipi ,boyutunu vs. degistirmek gibi. Bu t\u00fcr tablo d\u00fczenleme islemleri i\u00e7in SQL&#8217;in &#8220;ALTER&#8221; komutunu kullaniyoruz. Bu makalemizde,SQL Alter komutu ile yapabilecegimiz islemleri tek tek inceleyecegiz. \u00d6ncelikle \u00f6rnek olarak kullanabilecegimiz bir tablo olusturuyoruz.Tablomuzun adi Personel.\n\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Create scripti:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE Personel \n( \n   Id int, \n   Adi vharchar(50), \n   Soyadi vharchar(50), \n   DogumTarihi DateTime \n)\ufeff<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Tabloya yeni kolon eklemek:<\/h3>\n\n\n\n<p>\n\nMevcut tablomuza yeni bir alan eklemek i\u00e7in kullanilan genel ifade:<a href=\"http:\/\/www.yazilimmutfagi.com\/wp-admin\/www.yazilimmutfagi.com\">.<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi \nADD Alan_adi Alan_t\u00fcr\u00fc\ufeff<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuza Dogum yeri i\u00e7in bir alan a\u00e7alim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel \nAdd DogumYeri vharchar(50)<\/code><\/pre>\n\n\n\n<p>Ekledigimiz alana ait \u00f6zellikleri daha da \u00f6zelle\u015ftirebiliriz. Mesela Cinsiyet adinda, tipi bit olan bir alan ekleyelim, bo\u015f olamas\u0131n, ve default de\u011feri False olsun<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-6743255534256567\" data-ad-slot=\"8051748284\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel \nAdd Cinsiyet Bit Not Null Default ((0))<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Tabloya birden \u00e7ok kolon eklemek:<\/h3>\n\n\n\n<p>\n\nMevcut tablomuza tek seferde birden \u00e7ok alan eklemek i\u00e7in genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi \nADD (Alan_adi1 Alan_t\u00fcr\u00fc1, Alan_adi2 Alan_t\u00fcr\u00fc2, \u2026)<\/code><\/pre>\n\n\n\n<p>\u00a0\u00d6rnek olarak Personel tablomuza Tc Kimlik numarasi ve SSK numarasi i\u00e7in bir alan a\u00e7al\u0131m:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel \nAdd ( TCKimlikNo int not null, SSKNo int null)\ufeff<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Tabloya Primary Key eklemek:<\/h3>\n\n\n\n<p>\n\nMevcut tablomuza bulunan bir alani primary key yapmak i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi \nADD PRIMARY KEY (Alan_adi)\ufeff<\/code><\/pre>\n\n\n\n<p>\n\n&nbsp;\u00d6rnek olarak Personel tablomuza bulunan Id alanini Primary key yapalim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel \nAdd Primary Key (Id)<\/code><\/pre>\n\n\n\n<p>\n\nMevcut tablomuza yeni bir alan ekleyip, primary key yapmak i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi \nADD Alan_adi Not Null PRIMARY KEY<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuza ID2 adinda bir alan ekleyip, Primary key yapalim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel \nAdd ID2 int Not Null IDENTITY(1,1) Primary Key\ufeff<\/code><\/pre>\n\n\n\n<p>\n\nTablodaki alanin Primary Key \u00f6zelligini silmek: Mevcut tablomuza bulunan primary key alanin bu \u00f6zelligini kaldirmak i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi DROP CONSTRAINT Alan_adi<\/code><\/pre>\n\n\n\n<p>\n\n&nbsp;\u00d6rnek olarak Personel tablomuza bulunan Id alaninin Primary key \u00f6zelligini kaldiralim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Drop Constraint Id<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Tablodaki kolonun adini degistirmek:<\/h3>\n\n\n\n<p>\n\nMevcut tablomuzdaki bir alanin adini degistirmek i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Oracle i\u00e7in:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi \nCHANGE alan_adi_eski to alan_adi_yeni<\/code><\/pre>\n\n\n\n<p>\u00d6rnek olarak Personel tablomuza ekledigimiz &#8220;TCKimlikNo&#8221; alaninin adini TcNo yapalim:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Change TCKimlikNo to TcNo<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">MySql i\u00e7in:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi RENAME COLUMN alan_adi_eski to alan_adi_yeni<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuza ekledigimiz &#8220;TCKimlikNo&#8221; alaninin adini TcNo yapalim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Rename Column TCKimlikNo to TcNo<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Sql server i\u00e7in:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC sp_rename ' Tablo_adi.[alan_adi_eski]', alan_adi_yeni, 'COLUMN'<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuza ekledigimiz &#8220;TCKimlikNo&#8221; alaninin adini TcNo yapalim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC sp_rename Personel.[TCKimlikNo], TcNo ,'COLUMN'<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Tablodaki kolona ait bilgileri degistirmek:<\/h3>\n\n\n\n<p>\n\nMevcut tablomuzda var olan bir alanin bilgilerini degistirmek i\u00e7in kullanilsan genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi ALTER COLUMN alan_adi alan_t\u00fcr\u00fc<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuza yukarda ekledigimiz SSK numarasinin tipini varchar olarak degistirelim:<a href=\"http:\/\/www.yazilimmutfagi.com\/wp-admin\/www.yazilimmutfagi.com\">.\ufeff<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Alter Column SSKNo Varchar(20)<\/code><\/pre>\n\n\n\n<p>\n\nBu komutu kullanarak alana ait &#8216;Allow Null&#8217;, yani bos birakma \u00f6zelligini de degistirebilirz. \u00d6rnek olarak doldurulmasi zorunlu yaptigimiz TCKimlik Numarasi nullable yapalim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Alter Column TcNo int null<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Tablodaki bir kolonu silmek:<\/h2>\n\n\n\n<p>\n\nMevcut tablomuzda var olan bir alani silmek i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-6743255534256567\" data-ad-slot=\"8051748284\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi DROP COLUMN alan_adi<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuza yukarda ekledigimiz SSK numarasi alanini silelim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Drop Column SSKNo<\/code><\/pre>\n\n\n\n<p>\n\nTablomuzda bulunan bir alani sildigimizde, o alandaki b\u00fct\u00fcn datalar silinir. Eger sildigimiz alan composite primary key ise, hem o alan silinir hem de tabloda yinelenen kayitlar silinir.\n\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Tablodaki bir kolona index eklemek \/indexi kaldirmak: Mevcut tablomuzda var olan bir alana index eklemek i\u00e7in kullanilan genel ifade:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi ADD INDEX Index_Adi (alan_adi)<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuzda bulunan &#8216;Adi&#8217; alanini indexleyelim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Add index Index1 (Adi)<\/code><\/pre>\n\n\n\n<p>\n\nMevcut tablomuzda var olan bir indexi silmek i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi DROP INDEX Index_Adi<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuzda bulunan &#8216;Adi&#8217; alaninin indexini silelim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Drop index Index1<\/code><\/pre>\n\n\n\n<p>\n\nTablodaki bir kolona Constraint eklemek \/ Constrainti kaldirmak:Mevcut tablomuzda var olan bir alana Constraint eklemek&nbsp; i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi ADD CONSTRAINT constraint_Adi UNIQUE (alan_adi)<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuzda bulunan &#8216;TCNo&#8217; alanina Constraint ekleyelim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Add constraint constraint1 Unique (TCNo)<\/code><\/pre>\n\n\n\n<p>\n\nMevcut tablomuzda var olan bir Constraint i silmek i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi DROP CONSTRAINT constraint_Adi<\/code><\/pre>\n\n\n\n<p>\n\n&nbsp;\u00d6rnek olarak Personel tablomuzda bulunan &#8216;TCNo&#8217; alaninin constraintini silelim:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Drop constraint constraint1<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Tablonun adini degistirmek:<\/h3>\n\n\n\n<p>\n\nMevcut tablomuzun adini degistirmek i\u00e7in kullanilan genel ifade:\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Tablo_adi_eski RENAMA TO Tablo_adi_yeni<\/code><\/pre>\n\n\n\n<p>\n\n\u00d6rnek olarak Personel tablomuzun adini &#8220;Calisan&#8221; olarak degistirelim :\n\n<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Alter Table Personel Rename to Calisan<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Bazen mevcut tablomuzda degisiklik yapmamiz gerekir. Var olan tabloya bir alan eklemek, var olan alanin adini, tipi ,boyutunu vs. degistirmek gibi. Bu t&#252;r tablo d&#252;zenleme islemleri i&#231;in SQL&#39;in &quot;ALTER&quot; komutunu kullaniyoruz.  Bu makalemizde,SQL Alter komutu ile yapabilecegimiz islemleri tek tek inceleyecegiz.<\/p>\n","protected":false},"author":525,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[422],"tags":[4524,4252,6281,6223,6121,6003,5805,6331,5267,5607,4995],"_links":{"self":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/39"}],"collection":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/users\/525"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/comments?post=39"}],"version-history":[{"count":5,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/39\/revisions"}],"predecessor-version":[{"id":4566,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/39\/revisions\/4566"}],"wp:attachment":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/media?parent=39"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/categories?post=39"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/tags?post=39"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}