Comprehensive Guide to SQL Data Definition Language (DDL) Commands

1. VTYS 2012 Mehmet Emin KORKUSUZ Ders - 03

2. Data Defination Language Create Alter Drop

3. Create Database Table Procedure ve Function Index View Trigger

4. Create Database CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name[create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

5. Default createdatabaseif not existBilgi_Sistemi defaultchar set utf8 defaultcollateutf8_turkish_ci; createdatabaseif not existBilgi_Sistemi char set utf8 collateutf8_turkish_ci;

6. CreateTable CREATE TABLE ornek( id INT, data VARCHAR(100) ); CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]

7. TableOptions {ENGINE|TYPE} [=] engine_name AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value [DEFAULT] CHARACTER SET [=] charset_name CHECKSUM [=] {0 | 1} [DEFAULT] COLLATE [=] collation_name COMMENT [=] 'string' CONNECTION [=] 'connect_string' DATA DIRECTORY [=] 'absolutepathtodirectory' DELAY_KEY_WRITE [=] {0 | 1}

8. TableOptions INDEX DIRECTORY [=] 'absolutepathtodirectory' INSERT_METHOD [=] { NO | FIRST | LAST } | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | UNION [=] (tbl_name[,tbl_name]...)

10. Data Type • BIT[(length)] • TINYINT[(length)] [UNSIGNED] [ZEROFILL] • SMALLINT[(length)] [UNSIGNED] [ZEROFILL] • MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] • INT[(length)] [UNSIGNED] [ZEROFILL] • INTEGER[(length)[UNSIGNED] [ZEROFILL] • BIGINT[(length)] [UNSIGNED] [ZEROFILL] • REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] • DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] • FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL • DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL] • NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]

11. integer

12. Data Type DATE TIME TIMESTAMP DATETIME YEAR CHAR[(length)] [CHARACTER SET charset_name] [COLLATE collation_name] VARCHAR(length) [CHARACTER SET charset_name] [COLLATE collation_name] BINARY[(length)] | VARBINARY(length)

13. Date-Time

14. Char - Varchar

15. TINYBLOB BLOB MEDIUMBLOB LONGBLOB TINYTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] TEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] MEDIUMTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] LONGTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name]

16. ENUM(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] SET(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name]

17. enum CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') ); ENUM('one', 'two', 'three')

18. set CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) VALUES -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

20. Diğer tipler

21. Mevcut Tabloyu Kopyalamak CREATE TABLE new_tbl SELECT * FROM orig_tbl; CREATE TABLE new_tbl LIKE orig_tbl;

22. Alterdatabase ALTER {DATABASE | SCHEMA} [db_name] alter_specification ... ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

23. Altertable ALTER [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options]

24. Add Change Modify Drop Rename Orderby

25. ALTER TABLE Example ADD ID INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (ID); ALTER TABLE Example ADD UNIQUE (URL); ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;

26. ALTER TABLE testalter_tbl MODIFY c CHAR(10); ALTER TABLE testalter_tbl CHANGE i j BIGINT; ALTER TABLE testalter_tbl CHANGE j j INT; ALTER TABLE testalter_tblMODIFY j BIGINT NOT NULL DEFAULT 100;

27. ALTER TABLE testalter_tblengine= MYISAM; SHOW TABLE STATUS LIKE 'testalter_tbl‘; ALTER TABLE testalter_tbl RENAME TO alter_tbl; ALTER TABLE testalter_tbl ORDER BY name;

28. rename RENAME TABLE old_table TO backup_table, new_table TO old_table;

29. truncate Truncateold_friends;

30. Drop DROP {DATABASE | SCHEMA} [IF EXISTS] db_name Dropifexistsveritabani; DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] Drop tablo;

31. Load data, insert Veri Girişi

32. LOAD DATA INFILE

33. [LOCAL] [LOW_PRIORITY | CONCURRENT] • [LOCAL] ifadesi veri alınacak dosyanın yerelde yani bu bilgisayarda olduğunu ifade eder. • [LOW_PRIORITY] Veri tabanından bilgi istenirken işlem önceliğinin olmayacağını belirten bir komuttur. • [CONCURRENT] ifadesi ise veri tabanında bilgi istenildiği zaman eş zamanlı olarak çağırmak içindir.

34. LOAD DATA [LOCAL] INFILE “C:/Users/Desktop/açık_dosya_adı” INTO TABLE tablo_ad COLUMNS ESCAPED BY “,” LINES TERMINATED BY “-”;

35. Select intooutfile Select intooutfileload data infile komutunun tam tersini yapmaktadır. Tablodaki verileri harici bir dosyaya yazdırmaya yarar. SELECT * INTO OUTFILE “dosya_yolu” [FIELDS | COLUMNS] [TERMINATED | ESCAPED] BY “karekter” LINES [TERMINATED | STARTING] BY “karakter” FROM tablo_adı;

36. UYGULAMA SELECT * INTO OUTFILE “C:/Users/Desktop/dosya_adı_ve_uzantısı” INTO TABLE tablo_adı COLUMNS ESCAPED BY “,” LINES TERMINATED BY “-” FROM tablo_ad; SELECT alan1,alan2,alan7 INTO OUTFILE “C:/Users/Desktop/dosya_adı_ve_uzantısı” INTO TABLE tablo_adı COLUMNS ESCAPED BY “;” LINES TERMINATED BY “\n” FROM tablo_ad;