Selasa, 29 Oktober 2013
STUDI KASUS :
Create
Database DB_MHS;
Use DB_MHS;
Create Table Biodata (NIM Char(4)not Null,
Nama Varchar(6), Jurusan Varchar (10)) ;
Insert Biodata values ( ‘0611’ , ‘Diana’ , ’ Akuntansi’);
Insert Biodata values ( ;0512’ , ‘Susan’ , ’Komputer’);
Insert Biodata values ( ‘0713’ , ‘Pandi’ , ’B_Inggris’);
Insert Biodata values ( ‘0415’ , ‘Reza’ , ’Komputer’);
Insert Biodata values ( ‘0816’ , ‘Dani’ , ’B_Inggris’);
Insert Biodata values ( ‘0414’ , ‘Ronal’ , ’Akutansi’);
Desc Biodata ;
Select * From Biodata;
1. Mengganti Namafield
BU : AlterTableNama_TabelChange
Field_Lama Field_Baru Type ([Ukuran]);
Cth
: ~ Alter table Biodata change NIM NIRM Char (5) Null ;
~Alter table Biodata change NIRM NIM Char (4) not Null ;
2. Menyisip Primary Key
BU
: Alter Table Nama_Tabel Add Primary Key (Field);
Cth
:Alter Table Biodata Add Primary Key (NIM);
3. Menghapus Primary Key
BU :
Alter Table Nama_Tabel Drop Primary Key;
Cth
:Alter Table Biodata Drop Primary Key;
4. Menambah field
BU
: Alter Table Nama_Tabel Add Field Type
([Ukuran]);
Cth : Alter Table Biodata Add Alamat varchar (10) ;
5. Menghapus field
BU
: Alter Table Nama_Tabel Drop Field;
Cth
: Alter Table Biodata Drop Alamat ;
6. Mengganti record tabel
BU
: Update Nama_tabel Set Field_1 = Nilai_Baru_1, Field_2 = Nilai_Baru_2 , . .
.,Field_n = Nilai_Baru_n
Where (Kondisi);
UPDATE BIODATA SET NIM = ‘0101’ WHERE NAMe = 'diana';
Cth1 :
Update Biodata set Nama = 'Melati' Where NIM = ‘0611 ’;
Cth2 : Update Biodata set NIM = '0601' where
NIM = ‘0611’ ;
Cth3
: Update Biodata set Nama = 'Jamal' , Jur = 'Akuntansi' where NIM = ‘0512’ ;
Cth4
: Update Biodata set Jur = 'Komputer' where NIM = '0512' or NIM = ‘0713’;
7. Menghapus sebagian record tabel
BU
: Delete from Nama_Tabel Where Kondisi ;
Cth
: Delete from Biodata Where NIM = ‘0414’ ;
Atau
Delete
from Biodata Where Jurusan = Akuntansi ;
8. Menghapus seluruh record tabel
BU : Delete from Nama_Tabel ;
Cth
: Delete from Biodata;
-
Tambahkan kondisi dengan perintah where untuk menghapus record
Berdasarkan kriteria tertentu.
.-
Tambahkan perintah limit untuk batasan penghapusan record.
Cth : Delete from Biodata limit 3;
9. Mengetahui jumlah record
BU
: Select count ( * ) from Nama_Tabel;
Cth
: Select count ( * ) from Biodata;
Select count ( * ) as Jumlah from Biodata;
Select count ( * ) as Jlh Data from Biodata;
