Rabu, 30 Januari 2013

Modul 6 - MySQL (Screenshot)








Diposting oleh di Tidak ada komentar:

Modul 6 - MySQL (Sintaks)


Nomor 1
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bandara            |
| dbperpustakaan     |
| mysql              |
| performance_schema |
| pethouse           |
| prak1              |
| prak2              |
| smbd               |
| test               |
+--------------------+
10 rows in set (0.00 sec)

mysql> CREATE DATABASE kuliah;
Query OK, 1 row affected (0.00 sec)

mysql> USE kuliah;
Database changed
mysql> CREATE TABLE instruktur(nip char(1) primary key, namains varchar(30) not
null, jurusan varchar(30) not null, asalkota varchar(30) not null);
Query OK, 0 rows affected (0.08 sec)

mysql> DESC instruktur;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| nip      | char(1)     | NO   | PRI | NULL    |       |
| namains  | varchar(30) | NO   |     | NULL    |       |
| jurusan  | varchar(30) | NO   |     | NULL    |       |
| asalkota | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> CREATE TABLE matakuliah(nomk varchar(6) primary key, namamk varchar(50) n
ot null, sks int not null);
Query OK, 0 rows affected (0.09 sec)

mysql> DESC matakuliah;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| nomk   | varchar(6)  | NO   | PRI | NULL    |       |
| namamk | varchar(50) | NO   |     | NULL    |       |
| sks    | int(11)     | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> CREATE TABLE kuliah(nip char(1) not null references instruktur(nip), nomk
 varchar(6) not null references matakuliah(nomk), ruangan varchar(3) not null, j
mlmhs int not null);
Query OK, 0 rows affected (0.09 sec)

mysql> DESC kuliah;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| nip     | char(1)    | NO   |     | NULL    |       |
| nomk    | varchar(6) | NO   |     | NULL    |       |
| ruangan | varchar(3) | NO   |     | NULL    |       |
| jmlmhs  | int(11)    | NO   |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM instruktur;
+-----+---------------+---------------+----------+
| nip | namains       | jurusan       | asalkota |
+-----+---------------+---------------+----------+
| 1   | Steve Wozniak | Ilmu Komputer | Bantul   |
| 2   | Steve Jobs    | Seni Rupa     | Solo     |
| 3   | James Gosling | Ilmu Komputer | Klaten   |
| 4   | Bill Gates    | Ilmu Komputer | Magelang |
+-----+---------------+---------------+----------+
4 rows in set (0.00 sec)


mysql> SELECT * FROM matakuliah;
+--------+--------------------------------+-----+
| nomk   | namamk                         | sks |
+--------+--------------------------------+-----+
| KOM101 | Algoritma dan Pemrograman      |   3 |
| KOM102 | Basis Data                     |   3 |
| KOM201 | Pemrograman Berorientasi Objek |   3 |
| SR101  | Desain Elementer               |   3 |
+--------+--------------------------------+-----+
4 rows in set (0.00 sec)

mysql> SELECT * FROM kuliah;
+-----+--------+---------+--------+
| nip | nomk   | ruangan | jmlmhs |
+-----+--------+---------+--------+
| 1   | KOM101 | 101     |     50 |
| 1   | KOM102 | 102     |     35 |
| 2   | SR101  | 101     |     45 |
| 3   | KOM201 | 101     |     55 |
+-----+--------+---------+--------+
4 rows in set (0.00 sec)


Nomor 2
Nomor a
mysql> SELECT * FROM instruktur;
+-----+---------------+---------------+----------+
| nip | namains       | jurusan       | asalkota |
+-----+---------------+---------------+----------+
| 1   | Steve Wozniak | Ilmu Komputer | Bantul   |
| 2   | Steve Jobs    | Seni Rupa     | Solo     |
| 3   | James Gosling | Ilmu Komputer | Klaten   |
| 4   | Bill Gates    | Ilmu Komputer | Magelang |
+-----+---------------+---------------+----------+
4 rows in set (0.00 sec)


Nomor b
mysql> SELECT matakuliah.nomk FROM matakuliah JOIN kuliah ON matakuliah.nomk = k
uliah.nomk WHERE kuliah.jmlmhs > 40;
+--------+
| nomk   |
+--------+
| KOM101 |
| SR101  |
| KOM201 |
+--------+
3 rows in set (0.06 sec)

Nomor c
mysql> SELECT matakuliah.nomk, matakuliah.namamk FROM matakuliah JOIN kuliah ON
matakuliah.nomk = kuliah.nomk WHERE kuliah.jmlmhs > 40;
+--------+--------------------------------+
| nomk   | namamk                         |
+--------+--------------------------------+
| KOM101 | Algoritma dan Pemrograman      |
| KOM201 | Pemrograman Berorientasi Objek |
| SR101  | Desain Elementer               |
+--------+--------------------------------+
3 rows in set (0.05 sec)

Nomor d
mysql> SELECT kuliah.nip FROM kuliah WHERE nomk = 'KOM102';
+-----+
| nip |
+-----+
| 1   |
+-----+
1 row in set (0.00 sec)

Nomor e
mysql> SELECT kuliah.nip FROM kuliah JOIN matakuliah USING(nomk) WHERE namamk =
'Basis Data';
+-----+
| nip |
+-----+
| 1   |
+-----+
1 row in set (0.00 sec)

Nomor f
mysql> SELECT kuliah.nip, instruktur.namains FROM kuliah JOIN matakuliah USING(n
omk) JOIN instruktur USING(nip) WHERE namamk = 'Basis Data';
+-----+---------------+
| nip | namains       |
+-----+---------------+
| 1   | Steve Wozniak |
+-----+---------------+
1 row in set (0.05 sec)

Nomor g
mysql> SELECT matakuliah.namamk, kuliah.ruangan FROM kuliah JOIN matakuliah USIN
G(nomk) JOIN instruktur USING(nip) WHERE instruktur.namains = 'Steve Jobs';
+------------------+---------+
| namamk           | ruangan |
+------------------+---------+
| Desain Elementer | 101     |
+------------------+---------+
1 row in set (0.00 sec)

Nomor h
mysql> SELECT SUM(kuliah.jmlmhs) FROM kuliah JOIN instruktur USING(nip) JOIN mat
akuliah USING(nomk) WHERE instruktur.namains = 'Steve Wozniak';
+--------------------+
| SUM(kuliah.jmlmhs) |
+--------------------+
|                 85 |
+--------------------+
1 row in set (0.08 sec)

Nomor i
mysql> SELECT instruktur.nip, instruktur.namains FROM instruktur JOIN kuliah USI
NG(nip) WHERE jmlmhs IN (SELECT MAX(jmlmhs) FROM kuliah);
+-----+---------------+
| nip | namains       |
+-----+---------------+
| 3   | James Gosling |
+-----+---------------+
1 row in set (0.00 sec)

Nomor j
mysql> SELECT instruktur.nip, instruktur.namains FROM instruktur WHERE nip NOT I
N (SELECT nip FROM kuliah);
+-----+------------+
| nip | namains    |
+-----+------------+
| 4   | Bill Gates |
+-----+------------+
1 row in set (0.00 sec)



Nomor 3
Nomor a
mysql> CREATE VIEW view1 AS SELECT instruktur.nip, instruktur.namains FROM instr
uktur WHERE nip NOT IN (SELECT nip FROM kuliah);
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM view1;
+-----+------------+
| nip | namains    |
+-----+------------+
| 4   | Bill Gates |
+-----+------------+
1 row in set (0.04 sec)

Nomor b
mysql> SELECT kuliah.nip, instruktur.namains, count(nomk) FROM kuliah JOIN instr
uktur USING(nip) GROUP BY nip;
+-----+---------------+-------------+
| nip | namains       | count(nomk) |
+-----+---------------+-------------+
| 1   | Steve Wozniak |           2 |
| 2   | Steve Jobs    |           1 |
| 3   | James Gosling |           1 |
+-----+---------------+-------------+
3 rows in set (0.00 sec)


Nomor 4
mysql> DELIMITER //
mysql> CREATE TRIGGER up_data AFTER UPDATE
    -> ON kuliah
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT INTO log
    -> VALUES(user(), concat('merubah ruangan ',NEW.nomk,' dari ruang ',OLD.ruan
gan ,' ke ',NEW.ruangan));
    -> END //
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM kuliah;
    -> //
+-----+--------+---------+--------+
| nip | nomk   | ruangan | jmlmhs |
+-----+--------+---------+--------+
| 1   | KOM101 | 101     |     50 |
| 1   | KOM102 | 102     |     35 |
| 2   | SR101  | 101     |     45 |
| 3   | KOM201 | 101     |     55 |
+-----+--------+---------+--------+
4 rows in set (0.00 sec)

mysql> UPDATE kuliah SET ruangan='102' WHERE nomk = 'KOM101';
    -> //
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DELIMITER ;
mysql> SELECT * FROM log;
+----------------+----------------------------------------------+
| user_id        | deskripsi                                    |
+----------------+----------------------------------------------+
| root@localhost | merubah ruangan KOM101 dari ruang 101 ke 102 |
+----------------+----------------------------------------------+
1 row in set (0.00 sec)


Nomor 5
Nomor c
mysql> DELIMITER //
mysql> CREATE PROCEDURE showRoom (IN nama varchar(30))
    -> BEGIN
    -> SELECT matakuliah.namamk, kuliah.ruangan FROM kuliah JOIN matakuliah USIN
G(nomk) JOIN instruktur USING(nip) WHERE instruktur.namains LIKE nama;
    -> END //
Query OK, 0 rows affected (0.04 sec)

mysql> DELIMITER ;
mysql> CALL showRoom('Steve Jobs');
+------------------+---------+
| namamk           | ruangan |
+------------------+---------+
| Desain Elementer | 101     |
+------------------+---------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> CALL showRoom('Steve Wozniak');
+---------------------------+---------+
| namamk                    | ruangan |
+---------------------------+---------+
| Algoritma dan Pemrograman | 102     |
| Basis Data                | 102     |
+---------------------------+---------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


Nomor d
mysql> DELIMITER //
mysql> CREATE PROCEDURE getSks(IN nama varchar(30))
    -> BEGIN
    -> SELECT SUM(matakuliah.sks) FROM kuliah JOIN matakuliah USING(nomk) JOIN i
nstruktur USING(nip) WHERE instruktur.namains LIKE nama;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL getSks('Steve Jobs');
    -> //
+---------------------+
| SUM(matakuliah.sks) |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL getSks('Steve Wozniak');
+---------------------+
| SUM(matakuliah.sks) |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)


Diposting oleh di Tidak ada komentar:

Modul 5

Nomor 1



Nomor 2



Diposting oleh di Tidak ada komentar:

Modul 4 - MySQL


VIEW DAN TRIGGER
View adalah query tersimpan yang menghasilkan result set ketika dipanggil. View bertindak sebagai
tabel virtual.

1.        Pembuatan view
CREATE [OR REPLACE] [<algorithm attributes>] VIEW [database.]<
name> [(<columns>)] AS <SELECT statement> [WITH <LOCAL | CASCADE> <check options>]

mysql> CREATE VIEW view1 AS
-> SELECT CONCAT(first_name, " ", last_name), city FROM employee;
mysql> SELECT * FROM view1;
mysql> CREATE VIEW view2 (name, place) AS
-> SELECT CONCAT(first_name, " ", last_name), city FROM employee;
mysql> SELECT * FROM view2;

2.        Updatable View
mysql> UPDATE view2 SET place = "Canberra" WHERE place =
"Toronto";
mysql> select * from employee where city = 'Canberra';

mysql> UPDATE view2 SET place = "Toronto" WHERE place =
"Canberra";

3.        Klausa WITH CHECK OPTION
mysql> CREATE VIEW v1 AS
-> SELECT first_name, salary, city FROM employee WHERE salary <
3000
-> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE salary > 0
WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE salary > 0
WITH CASCADED CHECK OPTION;
mysql> INSERT INTO v2 VALUES ("Doe", 3500, "Indonesia");

4.        Merubah View
mysql> ALTER VIEW view1 (name, city) AS
-> SELECT CONCAT(first_name, " ", last_name), city FROM
employee;

5.        Melihat definisi pembuatan view
mysql> SHOW CREATE VIEW view1\G


6.        Menghapus View
DROP VIEW view1;

TRIGGER
Trigger adalah sebuah objek database yang diasosiasikan dengan sebuah tabel. Trigger diaktifkan ketika sebuah event terjadi pada tabel yang diasosiasikan. Tabel yang diasosiasikan dengan trigger haruslah sebuah tabel yang permanen dan bukan temporary tabel.

1.        Membuat trigger
CREATE TRIGGER <trigger_name> <trigger_time> <trigger_event>
ON <table> FOR EACH ROW <trigger_body statements>

2.        Before Trigger
mysql> DELIMITER //
mysql> CREATE TRIGGER before_insert BEFORE INSERT ON employee
-> FOR EACH ROW
-> BEGIN
-> IF NEW.salary IS NULL OR NEW.salary = 0 THEN
-> SET NEW.salary = 1000;
-> ELSE
-> SET NEW.salary = NEW.salary + 100;
-> END IF;
-> END //
mysql> DELIMITER ;

mysql> insert into employee(id,first_name, last_name, start_date, end_Date,
salary, City, Description)
-> values (1,'John', 'Doe', '19960725', '20060725', 0, 'Canberra', 'Programmer');

mysql> DELIMITER &&
mysql> CREATE TRIGGER before_update BEFORE UPDATE ON
employee
-> FOR EACH ROW
-> BEGIN
-> UPDATE employee SET salary = salary+(NEW.salary - OLD.salary)
-> WHERE id = NEW.id;
-> END &&
mysql> DELIMITER ;

3.        After Trigger
mysql> CREATE TABLE trans_log(
-> user_id VARCHAR(15),
-> description VARCHAR(50)
-> );
mysql> DELIMITER $$
mysql> CREATE TRIGGER log_salary AFTER UPDATE
-> ON employee
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO trans_log
-> VALUES (user(), CONCAT('merubah akun ',NEW.id,' dari ',OLD.salary, ' to
',NEW.salary));
-> END $$
mysql> DELIMITER ;

mysql> UPDATE employee SET salary = salary + 1000;
mysql> SELECT * FROM trans_log;

4.      Melihat trigger yang sudah dibuat
mysql> SHOW TRIGGERS IN PRAK2\G

5.      Menghapus Trigger
mysql> DROP TRIGGER before_insert;

Screenshot / lanjutan ada di :

MODUL 5
FUNCTION DAN PROCEDURE
Intro Stored Routine
Stored routine (function dan procedure) merupakan sekumpulan statement SQL yang dapat disimpan dalam server. Setelah routine disimpan, client tidak perlu memanggil statement individual terus menerus, namun cukup dengan memanggil stored routine.

Function
Sebuah function dapat digunakan secara langsung dalam statement SELECT, UPDATE, dan DELETE. Hasil dari function dapat dikembalikan sebagai output. Sebuah function hanya dapat mengembalikan sebuah nilai saja.

1.    Membuat function
mysql> DELIMITER //
mysql> CREATE FUNCTION full_name( in_first_name VARCHAR(15),
in_last_name VARCHAR(15))
-> RETURNS VARCHAR(35)
-> BEGIN
-> RETURN CONCAT(in_first_name,' ',in_last_name);
-> END//


mysql> DELIMITER ;
mysql> SELECT full_name(first_name, last_name) FROM employee;

2.      Melihat function yang telah dibuat
mysql> SHOW FUNCTION STATUS;

3.      Menghapus function
mysql> DROP FUNCTION full_name;

Procedure
Procedure dapat berisi statement SQL (INSERT, UPDATE, DELETE, SELECT) atau operasi lain yang disimpan dalam database. Sebuah procedure dapat dipanggil menggunakan statement CALL nama_procedure disertai parameter yang diperlukan.

1.        Membuat procedure
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
routine_body
mysql> DELIMITER //
mysql> CREATE PROCEDURE show_employees()
-> BEGIN
-> SELECT * FROM employee;
-> END //
mysql> DELIMITER ;
mysql> CALL show_employees();

2.      Parameter dalam procedure
proc_parameter:
[ IN | OUT | INOUT ] param_name type

Parameter IN
mysql> DELIMITER $$
mysql> CREATE PROCEDURE getEmployeeByCity (IN cityName
VARCHAR(255))
-> BEGIN
-> SELECT * FROM employee WHERE city LIKE cityName;
-> END $$
mysql> DELIMITER ;
mysql> CALL getEmployeeByCity("Vancouver");
Parameter OUT
mysql> DELIMITER :)
mysql> CREATE PROCEDURE getNumEmployee (OUT numEmployee
INT)
-> BEGIN
-> SELECT COUNT(*) INTO numEmployee FROM employee;
-> END :)
mysql> DELIMITER ;
mysql> CALL getNumEmployee(@num);
mysql> SELECT @num;

Parameter INOUT
mysql> DELIMITER ^^
mysql> CREATE PROCEDURE increase(INOUT number INT)
-> BEGIN
-> SET number = number + 15;
-> END ^^

mysql> DELIMITER ;

mysql> SET @num = 100;
mysql> CALL increase(@num);
mysql> SELECT @num;


3.      Melihat procedure yang telah dibuat
mysql> SHOW PROCEDURE STATUS;

4.      Menghapus procedure
mysql> DROP PROCEDURE increaseSalary;
Pemrograman di Function dan Procedure
Di dalam function dan procedure, kita bisa memasukkan logika pemrograman. Ada beberapa karakteristik pemrograman yang didukung oleh MySQL. Beberapa di antaranya adalah penggunaan variabel, kendali kondisional, dan perulangan.

1.        Variabel
DECLARE var_name [, var_name] ... type [DEFAULT value]
DECLARE total_sale INT
DECLARE x, y INT DEFAULT 0

SET total_sale = 50;
SELECT COUNT(*) INTO numEmployee FROM employee;

mysql> CREATE FUNCTION addTax(salary FLOAT(8,2))
-> RETURNS FLOAT (8,2)
-> BEGIN
-> DECLARE tax FLOAT DEFAULT 0.05;
-> RETURN salary * (1 - tax);
-> END ^_^
mysql> DELIMITER ;
mysql> SELECT first_name, addTax(salary) FROM employee;


mysql> DELIMITER **
mysql> CREATE PROCEDURE checkScope()
-> BEGIN
-> DECLARE first_name VARCHAR(15) DEFAULT 'bob';
-> SELECT id, first_name FROM employee WHERE first_name =
first_name;
-> END **
mysql> DELIMITER ;
mysql> CALL checkScope();

2.        Kendali Kondisional
Kendali IF
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF;

mysql> DELIMITER &&
mysql> CREATE FUNCTION hideSalary(salary FLOAT(8,2))
-> RETURNS VARCHAR(20)
-> BEGIN
-> DECLARE sal VARCHAR(20);
-> IF salary < 4000 THEN SET sal = 'Low Salary';
-> ELSE SET sal = 'High Salary';
-> END IF;
-> RETURN sal;
-> END &&
mysql> DELIMITER ;
mysql> SELECT first_name, last_name, hideSalary(salary)
-> FROM employee;

Kendali CASE
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

mysql> DELIMITER ##
mysql> CREATE FUNCTION calcTax(job VARCHAR (20)))
-> RETURNS FLOAT(3,2)
-> BEGIN
-> DECLARE tax FLOAT(3,2) DEFAULT 0.05;
-> CASE job
-> WHEN 'Manager' THEN SET tax = 0.1;
-> WHEN 'Programmer' THEN set tax = 0.07;
-> WHEN 'Tester' THEN set tax = 0.06;
-> ELSE SET tax = 0.05;
-> END CASE;
-> RETURN tax;
-> END ##
mysql> delimiter ;
mysql> SELECT first_name, last_name, calcTax(description) FROM
employee;

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

mysql> DELIMITER >>
mysql> CREATE FUNCTION calcTax2(job VARCHAR(20))
-> RETURNS FLOAT(3,2)
-> BEGIN
-> DECLARE tax FLOAT(3,2);
-> CASE
-> WHEN job = 'Manager' THEN SET tax = 0.1;
-> WHEN job = 'Programmer' THEN SET tax = 0.07;
-> WHEN job = 'Tester' THEN SET tax = 0.06;
-> ELSE SET tax = 0.05;
-> END CASE;
-> RETURN tax;
-> END >>
mysql> DELIMITER ;
mysql> SELECT first_name, last_name, calcTax2(description) FROM
employee;

3.        Perulangan
Perulangan WHILE
WHILE search_condition DO
statement_list
END WHILE
mysql> DELIMITER //
mysql> CREATE PROCEDURE mod12(IN number INT(10))
-> BEGIN
-> WHILE number MOD 12 > 0 DO
-> SET number = number + 1;
-> END WHILE;
-> SELECT number;
-> END //

mysql> CALL mod12(10);
mysql> CALL mod12(24);

Perulangan REPEAT … UNTIL
REPEAT
statement_list
UNTIL search_condition
END REPEAT

mysql> DELIMITER /.
mysql> CREATE PROCEDURE repeatDemo(IN number INT(10))
-> BEGIN
-> REPEAT
-> SET number = number +1;
-> UNTIL number MOD 12 = 0
-> END REPEAT;
-> SELECT number;
-> END /.

mysql> DELIMITER ;
mysql> CALL repeatDemo(10);


Perulangan LOOP
[begin_label:] LOOP
statement_list
END LOOP [end_label]

mysql> DELIMITER /?
mysql> CREATE PROCEDURE iterateDemo(number INT)
-> BEGIN
-> label1: LOOP
-> SET number = number + 1;
-> IF number MOD 12 > 0 THEN
-> ITERATE label1;
-> END IF;
-> LEAVE label1;
-> END LOOP label1;
-> SELECT number;
-> END /?

mysql> DELIMITER ;
mysql> CALL iterateDemo(10);
mysql> CALL iterateDemo(20);


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