DATA DEFINITION LANGUAGE DDL UNIVERSITAS BINA DARMA 2013

DATA DEFINITION LANGUAGE (DDL) UNIVERSITAS BINA DARMA 2013

1. Create Database 2. Delete Database 3. Creating Tables 4. Deleting Tables 5. Seeing Table Structure 6. See Tables Have Been Created 7. Defining Null / Not Nulll 8. Defining Default Values 9. Defining a Primary Key 10. Deleting a Primary Key Table 11. Adding a New Column In Table 12. Changing the Data Type and Column Width in Table 13. Change Column Name 14. Delete Column In Table 15. Mendefeinisikan Foreign Key Table 16. Deleting a Foreign Key Table

1. Create Database 2. Delete Database a. Create databasename; example “ create database akademik; “ b. Drop databasename; example “ Drop database akademik” c. To open / use the database “use namadatabase; ” example “ use akademik; ”

3. Membuat Tabel

4. Deleting Tables Drop Table tablename ; example “Drop Table mahasiswa; ”

5. Seeing Table Structure the Command Structure“ Describe “ example describe mahasiswa;

. See Table A has been made Using Commands“ show tables “ example show tables;

To menduplicate table structure to new table To mencuplicate table structure to a new table that can use the following command: Create table karyawan_baru like employees; Example : Mysql > create table mahasiswa_baru like mahasiswa; Then it will create an additional table with the names of fruit karyawan_baru where the resulting structure together with the employee table

7. Defining Null / Not Null Not that the column with Null column type should not be vacated, and the column with Null column type can be left blank Syntax : CREATE TABLE namatabel ( Field 1 Tipe. Data 1 NOT NULL, Field 2 Tipe. Data 2 );

8. Defining Default Values The default value is the value given by the system automatically to a column when there is the addition of new lines, while the value of the field is not filled by user. Syntax : CREATE TABLE namatabel ( Field 1 Tipe. Data 1, Field 2 Tipe. Data 2 DEFAULT nilai ); value is the default value of the column.

9. Defining a Primary Key There are three ways to define the primary key. The following is a syntax to define the primary key for Field 1. 1. CREATE TABLE namatabel ( Field 1 Tipe. Data 1 NOT NULL PRIMARY KEY, Field 2 Tipe. Data 2 );

2. CREATE TABLE tablename ( Field 1 Tipe. Data 1, Field 2 Tipe. Data 2, PRIMARY KEY(Field 1) ); 3. ALTER TABLE tablename ADD CONSTRAINT constraintname PRIMARY KEY (columnname); example : create table jenisfilm with column type data type char (6), price int data type by defining a null note values and primary key for the column type and default value for the price column : or

or

10. Deleting a Primary Key Deleting Primary Key Method 1: If the primary key is made by using the alter table: ALTER TABLE DROP CONSTRAINT namatabel namaconstraint; Method 2: If the primary key is created through create table: tablename ALTER TABLE DROP PRIMARY KEY; Here is the command used to remove the primary key in the table jenisfilm :

11. Adding a New Column ALTER TABLE namatabel ADD fieldbaru tipe; namatabel is the name of tables to be added [by] his field. Fieldbaru is the name of column to be added, type is data type of column to be added. Following govern to add description column with data type of varchar(25) :

12. Altering Type Data and Wide of Column At Tables. ALTER TABLE namatabel MODIFY COLUMN field tipe namatabel is the name of tables to be added [by] his field. Fieldbaru is the name of column to be added, type is data type of column to be added. Following govern to add description column with data type of varchar(25) :

13. Altering the Name of Column. ALTER TABLE namatabel CHANGE COLUMN namabarukolom namalamakolom of him; namatabel is the name of tables to be altered by the name of his column, namalamakolom is column to by him, namabarukolom is new name of column, of him is data type of column. Following govern to alter the name of description column become ket. :

14. Vanishing the Name of Column. Syntax : ALTER TABLE namatabel DROP COLUMN namakolom; Following govern to vanish column of ket at tables of jenisfilm. :

15. Defining Foreign Key. To define key foreign, hence have to be ensured that [by] referred attribute and tables ( tables of mains of key foreign) have been defined beforehand. Syntax : 1. CREATE TABLE namatabel ( Field 1 Tipe. Data 1, Field 2 Tipe. Data 2, FOREIGN KEY (Field 2) REFERENCES namatabelinduk (namakolominduk)ON UPDATE CASCADE ON DELETE NO ACTION )

2. ALTER TABLE namatabel ADD CONSTRAINT namaconstraint FOREIGN KEY (namakolom) REFERENCES namatabelinduk (namakolominduk) ON UPDATE CASCADE ON DELETE NO ACTION; or

16. Vanishing Foreign Key. ALTER TABLE namatabel DROP FOREIGN KEY namaconstraint; Following govern to vanish key foreign at film tables. :