Basic Command

Beberapa perintah dasar untuk CRUD (Create, Read, Update, Delete) tabel dalam database.

Create:

CREATE TABLE <nama_tabel> (
    <nama_kolom1> <tipe_data> <opsi_lain> 
    <nama_kolom2> <tipe_data> <opsi_lain>, 
    ...
);


contoh:

CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);

atau

CREATE TABLE `city` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `Name` CHAR(35) NOT NULL DEFAULT '',
  `CountryCode` CHAR(3) NOT NULL DEFAULT '',
  `District` CHAR(20) NOT NULL DEFAULT '',
  `Population` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
);

Read:

SELECT <parameter> FROM <nama_tabel>;


contoh:

SELECT * FROM celebs;
SELECT name, imdb_rating FROM movies;
SELECT DISTINCT genre FROM movies;

Update:

INSERT INTO <nama_tabel> <urutan_kolom> VALUES <input_data>


contoh:

INSERT INTO celebs (id, name, age) VALUES (1, ‘justin’, 32)

ALTER TABLE <nama_tabel> ADD COLUMN <nama_kolom> <tipe_data>


contoh:

ALTER TABLE celebs ADD COLUMN twitter_name TEXT;

ALTER TABLE <nama_tabel> ALTER COLUMN <nama_kolom> <tipe_data>


contoh:

ALTER TABLE celebs ALTER COLUMN nama VARCHAR(100);

UPDATE <nama_tabel> SET <apa> WHERE <dimana>


contoh:

UPDATE celebs SET age = 22 WHERE id = 1;

Delete:

DELETE FROM <nama_tabel> <kondisi>


contoh:

DELETE FROM celebs WHERE twitter_handle IS null;
DELETE FROM celebs WHERE id = 1;

ALTER TABLE <nama_tabel> DROP COLUMN <nama_kolom>


contoh:

ALTER TABLE celebs DROP COLUMN twitter_name;

DROP TABLE <nama_tabel>


contoh:

DROP TABLE celebs;

TRUNCATE TABLE <nama_tabel>


contoh:

TRUNCATE TABLE celebs;