Rabu, 30 Januari 2013

Modul 3 - MySQL (Screenshot)















Diposting oleh di Tidak ada komentar:

Modul 3 - MySQL (Sintaks)


1.    Mengambil data dengan SELECT
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy


4.      Memberikan alias hasil query pada SELECT
mysql> SELECT CONCAT(first_name," ",last_name) AS name,
description
-> FROM employee
-> WHERE description = "Detective";
5.      Query data bertipe teks dengan pattern matching
mysql> SELECT CONCAT(first_name," ", last_name)
-> FROM employee
-> WHERE first_name LIKE 'J____';
mysql> SELECT CONCAT(first_name," ", last_name)
-> FROM employee
-> WHERE first_name NOT LIKE '%n';

6.      Query data unik menggunakan DISTINCT
mysql> SELECT DISTINCT description FROM employee;

7.      Membatasi hasil query dengan LIMIT
mysql> SELECT *
-> FROM employee
-> LIMIT 5;
mysql> SELECT *
-> FROM employee
-> LIMIT 2,3;
mysql> SELECT * FROM employee LIMIT 0,3;

8.      Mengelompokkan hasil query menggunakan GROUP BY
mysql> SELECT * FROM employee GROUP BY city;

9.      Mendapatkan jumlah anggota setiap kelompok menggunakan COUNT()
mysql> SELECT city, COUNT(*) FROM employee GROUP BY city;

mysql> SELECT city, COUNT(*)
-> FROM employee
-> WHERE description = 'Tester'
-> GROUP BY city;

10.  Parameter kondisional dengan HAVING
mysql> SELECT first_name, last_name, salary
-> FROM employee
-> HAVING salary > 3000;

mysql> SELECT city, COUNT(*), salary FROM employee WHERE
salary > 3000 GROUP BY city;

mysql> SELECT city, COUNT(*), salary FROM employee GROUP BY
city HAVING salary > 3000;

11.  Mengurutkan hasil query menggunakan ORDER BY
mysql> SELECT CONCAT(first_name," ",last_name) AS name
-> FROM employee
-> ORDER BY name;



mysql> SELECT CONCAT(first_name," ",last_name) AS name
-> FROM employee
-> ORDER BY name DESC;

12.  Mengurutkan hasil query berdasarkan lebih dari satu kolom
mysql> SELECT first_name, last_name, city FROM employee ORDER
BY first_name, city;
mysql> SELECT first_name, last_name, city FROM employee ORDER
BY first_name, city DESC;

13.  Kombinasi ORDER BY dengan LIMIT
mysql> SELECT first_name, last_name, city FROM employee ORDER
BY city LIMIT 4;

14.  Operator BETWEEN
mysql> SELECT first_name, last_name, salary FROM employee
-> WHERE salary BETWEEN 1000 and 3000;
mysql> SELECT first_name, last_name, salary FROM employee WHERE
salary BETWEEN 1233.78 and 1235.56;
mysql> SELECT first_name, last_name, salary FROM employee WHERE
salary BETWEEN 1233.78 and 1235.57;
mysql> SELECT first_name,last_name FROM employee WHERE
first_name BETWEEN "Elvis" and "James";
mysql> SELECT first_name,last_name FROM employee WHERE
first_name NOT BETWEEN "Elvis" and "James";

15.  SUB QUERY
mysql> SELECT first_name, last_name, salary
-> FROM employee
-> WHERE salary = (SELECT MAX(salary) FROM employee);

16.  Sub query dengan ALL
mysql> SELECT first_name, last_name, salary
-> FROM employee
-> WHERE salary > ALL(SELECT salary FROM employee
WHERE salary <2000);

17.  Sub query dengan ANY
mysql> SELECT first_name, last_name, salary FROM employee WHERE
salary > ANY(SELECT salary FROM employee WHERE salary
<2000);
mysql> SELECT first_name, last_name, salary FROM employee WHERE
salary > SOME(SELECT salary FROM employee WHERE salary
<2000);


18.  Sub query dengan EXISTS
mysql> SELECT first_name, last_name, city FROM employee WHERE
EXISTS (SELECT * FROM employee WHERE city = "Toronto");
mysql> SELECT first_name, last_name, city FROM employee WHERE
NOT EXISTS (SELECT * FROM employee WHERE city =
"Toronto");

19.  Sub query dengan IN
mysql> CREATE TABLE job (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> title VARCHAR(20));

mysql> INSERT into job VALUES
-> (1, 'Tester'),
-> (2, 'Accountant') ,
-> (3, 'Programmer'),
-> (4, 'Professor');

mysql> SELECT first_name, last_name, description
-> FROM employee
-> WHERE description IN
-> (SELECT title FROM job
-> );
20.  Table Join
mysql> CREATE TABLE employee_join AS (SELECT * FROM
employee);
mysql> ALTER TABLE employee_join ADD job_id INT;
mysql> UPDATE employee_join,jobs SET employee_join.job_id =
jobs.job_id WHERE employee_join.description = jobs.title;
mysql> ALTER TABLE employee_join DROP description;

21.  Cross Join
mysql> SELECT *
-> FROM employee_join, jobs;

22.  Equi-Join atau Inner Join
mysql> SELECT * FROM
-> employee_join, jobs
-> WHERE employee_join.job_id = jobs.job_id;
mysql> SELECT * FROM employee_join, jobs WHERE
employee_join.job_id = jobs.job_id;
mysql> SELECT * FROM employee_join t1 INNER JOIN jobs t2 ON
t1.job_id = t2.job_id;
mysql> SELECT * FROM employee_join INNER JOIN jobs ON
employee_join.job_id = jobs.job_id;

mysql> SELECT * FROM employee_join INNER JOIN jobs USING
(job_id);

23.  Natural Join
mysql> SELECT * FROM employee_join NATURAL JOIN jobs;

24.  Left Join dan Right Join
mysql> INSERT INTO jobs VALUES (7, 'Developer');
mysql> SELECT * FROM jobs t1 LEFT JOIN employee_join t2 ON
t1.job_id = t2.job_id;
mysql> SELECT * FROM employee_join t1 RIGHT JOIN jobs t2 ON
t1.job_id = t2.job_id;

25.  Update menggunakan Join Table
UPDATE <table1>, <table2>
SET <column_name> = 'new_value'
WHERE <conditions>
mysql> SELECT first_name, last_name, title FROM employee_join
INNER JOIN jobs USING (job_id);
mysql> UPDATE employee_join, jobs
-> SET last_name = "Bono"
-> WHERE employee_join.job_id = jobs.job_id AND jobs.title =
'Spy';
26.  Delete menggunakan join table
mysql> DELETE employee_join FROM employee_join, jobs WHERE
employee_join.job_id = jobs.job_id AND jobs.title = 'Spy';
mysql> SELECT * FROM employee_join INNER JOIN jobs USING
(job_id);
Diposting oleh di Tidak ada komentar:

Modul 2 - MySQL (Screenshot)





























Diposting oleh di Tidak ada komentar:

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';




Diposting oleh di Tidak ada komentar:

MODUL 1 - MySQL

Pada modul ini, dibahas :
1.    Pengantar MySQL
MySQL Server, atau mysqld, adalah program server database. Program ini mengatur akses ke database di disk dan memory. MySQL Server bersifat multi-thread dan mendukung beberapa koneksi secara simultan. MySQL Server memiliki arsitektur modular, sehingga mendukung berbagai macam storage engine untuk menangani beberapa jenis tabel yang berbeda.
2.    Memasukkan Query
Setelah kita berhasil masuk ke mysql, kita dapat memasukkan beberapa command ke dalam mysql.
contoh sederhana untuk mengetahui nomor versi dan tanggal sekarang menggunakan : mysql> SELECT VERSION(), CURRENT_DATE;



3.    Membuat dan memilih database

4.    Menghapus database

5.    Membuat tabel
>> CREATE TABLE nama_tabel (field1 tipe_data_field1, field2 tipe_data_field2, ….dst);

6.    Memasukkan data ke tabel
>> INSERT INTO nama_tabel VALUES (isi_field1, isi_field2, ….dst);
>> LOAD DATA LOCAL INFILE ‘nama_file’ INTO TABLE nama_tabel;

7.    Menghapus tabel
DROP TABLE nama_tabel;


TUGAS MODUL 1
1.      Membuat sebuah tabel bernama 'event' dalam database 'pethouse'
2.      Mengisi tabel tersebut dengan data sebagai berikut menggunakan INSERT
3.      Mengisi tabel tersebut dengan data sebagai berikut menggunakan LOAD DATA
4.      Tampilan datanya



Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)