1 / 36

VTYS 2012

VTYS 2012. Mehmet Emin KORKUSUZ. Ders - 03. Data Defination Language. Create Alter Drop. Create. Database Table Procedure ve Function Index View Trigger. Create Database. CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [ create_specification ] ... create_specification :

val
Download Presentation

VTYS 2012

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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]...)

  9. 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]

  10. integer

  11. 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)

  12. Date-Time

  13. Char - Varchar

  14. 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]

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

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

  17. 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');

  18. Diğer tipler

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

  20. 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

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

  22. Add Change Modify Drop Rename Orderby

  23. 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;

  24. 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;

  25. 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;

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

  27. truncate Truncateold_friends;

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

  29. Load data, insert Veri Girişi

  30. LOAD DATA INFILE

  31. [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.

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

  33. 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ı;

  34. 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;

More Related