360 likes | 490 Views
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 :
E N D
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: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
Default createdatabaseif not existBilgi_Sistemi defaultchar set utf8 defaultcollateutf8_turkish_ci; createdatabaseif not existBilgi_Sistemi char set utf8 collateutf8_turkish_ci;
CreateTable CREATE TABLE ornek( id INT, data VARCHAR(100) ); CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]
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}
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]...)
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]
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)
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]
ENUM(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] SET(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name]
enum CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') ); ENUM('one', 'two', 'three')
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');
Mevcut Tabloyu Kopyalamak CREATE TABLE new_tbl SELECT * FROM orig_tbl; CREATE TABLE new_tbl LIKE orig_tbl;
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
Altertable ALTER [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options]
Add Change Modify Drop Rename Orderby
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;
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;
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;
rename RENAME TABLE old_table TO backup_table, new_table TO old_table;
truncate Truncateold_friends;
Drop DROP {DATABASE | SCHEMA} [IF EXISTS] db_name Dropifexistsveritabani; DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] Drop tablo;
Load data, insert Veri Girişi
[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.
LOAD DATA [LOCAL] INFILE “C:/Users/Desktop/açık_dosya_adı” INTO TABLE tablo_ad COLUMNS ESCAPED BY “,” LINES TERMINATED BY “-”;
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ı;
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;