MODUL 2
Membuat
tabel dan manipulasi data
1.
Membuat tabel
mysql>
CREATE DATABASE PRAK2;
mysql> USE
PRAK2;
1.
Membuat tabel dengan primary key
mysql>
CREATE TABLE TABLE2(
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> );
2.
Memberikan nilai unique
mysql>
CREATE TABLE cars(
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> plate VARCHAR (10),
-> brand VARCHAR (10),
-> UNIQUE (plate)
-> );
3.
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
-> );
4.
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';
5.
Membuat temporary tabel
mysql>
CREATE TEMPORARY TABLE TEMPTBL(
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(30)
-> );
6.
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;
7.
Memodifikasi tabel dengan ALTER
TABLE
8.
Menambah kolom pada tabel
mysql>
CREATE TABLE TABLE5(
-> id int
-> );
9.
Memodifikasi nama dan definisi
tabel
mysql>
ALTER TABLE TABLE5
-> CHANGE name
last_name VARCHAR(30);
10.
Memodifikasi definisi tabel
mysql>
ALTER TABLE TABLE5
-> MODIFY first_name VARCHAR(15),
-> MODIFY
last_name VARCHAR(15);
11.
Menambahkan primary key
mysql> ALTER TABLE TABLE5 ADD PRIMARY KEY
(id);
12.
Menghapus
kolom
mysql> ALTER TABLE TABLE5 DROP first_name;
13.
Mengganti
nama tabel
mysql>
ALTER TABLE employee_copy RENAME senior_employee;
14.
Menghapus
semua data dalam tabel
mysql>
CREATE TABLE employee_copy AS
-> SELECT *
FROM employee;
mysql>
TRUNCATE TABLE employee_copy;
15.
Menghapus
Tabel
mysql> DROP TABLE employee_copy;
16.
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'
-> );
17.
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' ) ;
18.
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;
19.
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';
20. Mengubah nilai berdasarkan nilai yang dimiliki
sekarang
mysql> UPDATE employee SET salary = salary
+ 1;
21.
Mengganti
data dari tabel
mysql> REPLACE INTO
employee2 (id,name, city) VALUES
(12,'Sherlock
Holmes','Manchester');
22. Menghapus data dari tabel
DELETE FROM
<table_name>
WHERE (<column_name> = 'some_value');
mysql> DELETE FROM employee2 WHERE city =
'New York';
Belum ada tanggapan untuk "TUGAS 2.2"
Post a Comment