Modul 2 - SQL (Sintaks)

MODUL 2

Membuat tabel dan manipulasi data

1.      Membuat tabel

mysql> CREATE DATABASE PRAK2;

mysql> USE PRAK2;

2.      Membuat tabel dengan primary key

mysql> CREATE TABLE TABLE2(

-> id INT AUTO_INCREMENT PRIMARY KEY,

-> name VARCHAR(30)

-> );

3.      Memberikan nilai unique

mysql> CREATE TABLE cars(

-> id INT PRIMARY KEY AUTO_INCREMENT,

-> plate VARCHAR (10),

-> brand VARCHAR (10),

-> UNIQUE (plate)

-> );

4.      Parameter IF NOT EXISTS

mysql> CREATE TABLE IF NOT EXISTS TABLE1(

-> id INT AUTO_INCREMENT,

-> name VARCHAR(30) NOT NULL,

-> salary FLOAT(10,2) DEFAULT 1000000

-> );

mysql> CREATE TABLE IF NOT EXISTS TABLE4(

-> id INT AUTO_INCREMENT,

-> name VARCHAR(30) NOT NULL,

-> salary FLOAT(10,2) DEFAULT 1000000

-> );

5.      Menyalin tabel dengan statement SELECT

mysql> CREATE TABLE employee(

-> id INT AUTO_INCREMENT PRIMARY KEY,

-> first_name VARCHAR(15),

-> last_name VARCHAR(15),

-> start_date DATE,

-> end_date DATE,

-> salary FLOAT(8,2),

-> city VARCHAR(10),

-> description VARCHAR(15)

-> );

mysql> CREATE TABLE employee_copy AS

-> SELECT *

-> FROM employee

-> WHERE start_date BETWEEN '1970-01-01' AND '1990-12-       31';

6.      Membuat temporary tabel

mysql> CREATE TEMPORARY TABLE TEMPTBL(

-> id INT PRIMARY KEY AUTO_INCREMENT,

-> name VARCHAR(30)

-> );

7.      Membuat tabel dengan foreign key

mysql> CREATE TABLE models (

-> modelid smallint not null auto_increment,

-> name varchar(40) not null,

-> primary key (modelid)

-> )

-> engine=InnoDB;

mysql> CREATE TABLE orders (

-> id SMALLINT NOT NULL PRIMARY KEY,

-> modelid SMALLINT NOT NULL,

-> description VARCHAR(40),

-> FOREIGN KEY (modelid) REFERENCES models (modelid)

ON UPDATE CASCADE ON DELETE CASCADE

-> ) engine=InnoDB;

mysql> INSERT INTO models VALUES (1, 'testsatu');

mysql> insert into orders value (1, 1, 'deskripsisatu');

mysql> DELETE FROM models WHERE modelid = 1;

mysql> SELECT * FROM models;

mysql> select * from orders;

8.      Memodifikasi tabel dengan ALTER TABLE

9.      Menambah kolom pada tabel

mysql> CREATE TABLE TABLE5(

-> id int

-> );

10.  Memodifikasi nama dan definisi tabel

mysql> ALTER TABLE TABLE5

-> CHANGE name last_name VARCHAR(30);

11.  Memodifikasi definisi tabel

mysql> ALTER TABLE TABLE5

-> MODIFY first_name VARCHAR(15),

-> MODIFY last_name VARCHAR(15);

12.  Menambahkan primary key

mysql> ALTER TABLE TABLE5 ADD PRIMARY KEY (id);

13.  Menghapus kolom

mysql> ALTER TABLE TABLE5 DROP first_name;

14.  Mengganti nama tabel

mysql> ALTER TABLE employee_copy RENAME senior_employee;

15.  Menghapus semua data dalam tabel

mysql> CREATE TABLE employee_copy AS

-> SELECT * FROM employee;

mysql> TRUNCATE TABLE employee_copy;

16.  Menghapus Tabel

mysql> DROP TABLE employee_copy;

17.  Memasukkan data dengan INSERT

INSERT INTO <table_name>

VALUES (

value1,

value2,

etc......

);

mysql> INSERT INTO EMPLOYEE VALUES (

-> 9,

-> 'James',

-> 'Bond',

-> '1982-04-21',

-> '2002-09-23',

-> 1234.56,

-> 'London',

-> 'Spy'

-> );

18.  Memasukkan lebih dari satu baris dalam satu INSERT

mysql> INSERT INTO employee (first_name, last_name, start_date, end_date, salary, city, description) VALUES

-> ('Lincoln', 'Rhyme', '1999-05-25', '2011-07-13', 3212.98, 'New York',

'Forensics' ),

-> ('Sherlock','Holmes', '1923-08-12','1945-07-21',4123.21, 'London',

'Detective' ) ;

19.  INSERT menggunakan data dari tabel lain

mysql> CREATE TABLE employee2 (

-> id INT AUTO_INCREMENT PRIMARY KEY,

-> name VARCHAR(15),

-> city VARCHAR(20)

-> );

mysql> INSERT INTO employee2

-> (id, name, city)

-> SELECT id, CONCAT(first_name,' ',last_name), city

-> FROM employee;

20.  Mengubah data menggunakan UPDATE

UPDATE <table_name>

SET <column_name> = 'new_value'

WHERE (<column_name> = 'some_value');

mysql> UPDATE employee2

-> SET city = 'Ottawa'

-> WHERE city = 'Vancouver';

21.  Mengubah nilai berdasarkan nilai yang dimiliki sekarang

mysql> UPDATE employee SET salary = salary + 1;

22.  Mengganti data dari tabel

mysql> REPLACE INTO employee2 (id,name, city) VALUES

(12,'Sherlock Holmes','Manchester');

23.  Menghapus data dari tabel

DELETE FROM <table_name>

WHERE (<column_name> = 'some_value');

mysql> DELETE FROM employee2 WHERE city = 'New York';