1 Basis Data I Pertemuan Ke-10 (SQL Part 1) Noor Ifada 12 Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Co...
Basis Data I Pertemuan Ke-10 (SQL – Part 1) Noor Ifada [email protected]
1
Sub Pokok Bahasan Structured Query Language (SQL) Tipe Data Basisdata Tennis: CDM dan PDM Constraint Contoh Data
Data Definition Language (DDL) Data Manipulation Language (DML)
2
Structured Query Language (SQL) Bahasa standar yang digunakan untuk memanipulasi basisdata relasional Terdiri dari: Data Definition Language (DDL): CREATE tables, indexes, views, Establish primary/foreign keys, DROP / ALTER tables .... Data Manipulation Language (DML): INSERT / UPDATE / DELETE, SELECT .... Data Control Language (DCL): COMMIT / ROLLBACK work, GRANT / REVOKE .... 3
Tipe Data Tipe Data NUMERIK Tipe Data STRING Tipe Data DATE & TIME
4
Tipe Data: Numerik
BIT A bit-field, from 1 to 64 bits wide. (Prior to MySQL 5 BIT was functionally equivalent to TINYINT) BIGINT Integer value, supports numbers from -9223372036854775808 to 9223372036854775807 (or 0 to 18446744073709551615 if UNSIGNED) BOOLEAN (or BOOL) Boolean flag, either 0 or 1, used primarily for on/off flags DECIMAL (or DEC) Floating point values with varying levels of precision DOUBLE Double-precision floating point values FLOAT Single-precision floating point values 5
Tipe Data: Numerik (contd-2)
INT (or INTEGER) Integer value, supports numbers from -2147483648 to 2147483647 (or 0 to 4294967295 if UNSIGNED) MEDIUMINT Integer value, supports numbers from -8388608 to 8388607 (or 0 to 16777215 if UNSIGNED) REAL 4-byte floating point values SMALLINT Integer value, supports numbers from -32768 to 32767 (or 0 to 65535 if UNSIGNED) TINYINT Integer value, supports numbers from -128 to 127 (or 0 to 255 if UNSIGNED) 6
Tipe Data: String
CHAR Fixed-length string from 1 to 255 chars long. Its size must be specified at create time, or MySQL assumes CHAR(1) ENUM Accepts one of a predefined set of up to 64K strings LONGTEXT Same as TEXT, but with a maximum size of 4GB MEDIUMTEXT Same as TEXT, but with a maximum size of 16K SET Accepts zero or more of a predefined set of up to 64 strings
7
Tipe Data: String (contd-2)
TEXT Variable-length text with a maximum size of 64K TINYTEXT Same as TEXT, but with a maximum size of 255 bytes VARCHAR Same as CHAR, but stores just the text. The size is a maximum, not a minimum BLOB Blob with a maximum length of 64K MEDIUMBLOB Blob with a maximum length of 16MB LONGBLOB Blob with a maximum length of 4GB TINYBLOB Blob with a maximum length of 255 bytes 8
Tipe Data: Date and Time
DATE Date from 1000-01-01 to 9999-12-31 in the format YYYY-MM-DD TIME Time in the format HH:MM:SS YEAR A 2 or 4 digit year, 2 digit years support a range of 70 (1970) to 69 (2069), 4 digit years support a range of 1901 to 2155 DATETIME A combination of DATE and TIME TIMESTAMP Functionally equivalent to DATETIME (but with a smaller range)
9
Contoh: CDM untuk Basisdata Tennis
10
Contoh: PDM untuk Basisdata Tennis
11
Contoh: Constraints dlm Basisdata Tennis Column value constraints: PLAYER(SEX) IN {'M','F'} MATCHES(WON) IN {0,1,2,3} MATCHES(WON) IN {0,1,2,3} COMMITTEE_MEMBERS(POSITION) IN {'Chairman', 'Secretary', 'Treasurer', 'General member'} PLAYERS (JOINED) >= 1970 PENALTIES (PAYMENT_DATE) >= '1970-01-01' PENALTIES (AMOUNT) > 0.00 COMMITTEE_MEMBERS (BEGIN_DATE) > '1990-01-01'
Row level constraints:
PLAYERS: YEAR (BIRTH_DATE) <= JOINED COMMITTEE_MEMBERS: END_DATE >= BEGIN_DATE 12
Contoh: Data dlm Basisdata Tennis Players
S1 Teknik Informatika Unijoyo
13
Contoh: Data dlm Basisdata Tennis (contd-2) Teams
Matches
Penalties
S1 Teknik Informatika Unijoyo
14
Contoh: Data dlm Basisdata Tennis (contd-3) Committee_members
S1 Teknik Informatika Unijoyo
15
Data Definition Language (DDL) CREATE/DROP basisdata CREATE tabel DROP tabel RENAME tabel ALTER tabel
16
DDL: Create/Drop (Basisdata) CREATE DATABASE db_name Contoh 1: Buatlah basisdata baru dengan nama tennis CREATE DATABASE tennis
DROP DATABASE db_name Contoh 2: Drop basisdata tennis dari sistem DROP DATABASE tennis
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ... Contoh 4: Gantilah nama tabel Teams menjadi Groups! RENAME TABLE Teams TO Groups
18
DDL: Create (Tabel) Contoh 5: Buat tabel Players! SQL: CREATE TABLE PLAYERS (PLAYERNO SMALLINT NAME CHAR(15) INITIALS CHAR(3) BIRTH_DATE DATE SEX CHAR(1) JOINED SMALLINT STREET CHAR(15) HOUSENO CHAR(4) POSTCODE CHAR(6) TOWN CHAR(10) PHONENO CHAR(10) LEAGUENO CHAR(4) PRIMARY KEY (PLAYERNO)
Contoh 6: Buat tabel Committee_Members! SQL:
NOT NULL, NOT NULL, NOT NULL, , NOT NULL, NOT NULL, NOT NULL, , , NOT NULL, , , );
CREATE TABLE COMMITTEE_MEMBERS (PLAYERNO SMALLINT NOT NULL, BEGIN_DATE DATE NOT NULL, END_DATE DATE , POSITION CHAR(20) , PRIMARY KEY (PLAYERNO, BEGIN_DATE) );
19
DDL: Create (Tabel) (contd-2) Contoh 7: Buat tabel Players! CREATE TABLE PLAYERS (PLAYERNO SMALLINT NOT NULL, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE , SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET CHAR(15) NOT NULL, HOUSENO CHAR(4) , POSTCODE CHAR(6) , TOWN CHAR(10) NOT NULL, PHONENO CHAR(10) , LEAGUENO CHAR(4) , PRIMARY KEY (PLAYERNO) , CHECK (PLAYERNO >= 1) , CHECK (SEX IN ('M', 'F')) , CHECK (JOINED >= 1970) , CHECK (YEAR(BIRTH_DATE)<= JOINED) );
Contoh 8: Buat tabel Committee_Members! SQL: CREATE TABLE COMMITTEE_MEMBERS (PLAYERNO SMALLINT NOT NULL, BEGIN_DATE DATE NOT NULL, END_DATE DATE , POSITION CHAR(20) , PRIMARY KEY (PLAYERNO, BEGIN_DATE) , FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO) , CHECK (POSITION IN ('Chairman', 'Secretary','Treasurer', 'General member')) , CHECK (BEGIN_DATE >= '1990-01-01'), CHECK (END_DATE >= BEGIN_DATE) );
20
DDL: Alter (Tabel) ALTER TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD CHANGE & MODIFY DROP RENAME
21
DDL: Alter (Tabel) - Add ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] ADD [COLUMN] (col_name column_definition,...)
Contoh 9: Tambahkan satu kolom bernama TYPE di dalam tabel TEAMS. Kolom TYPE ini untuk memberikan identifikasi tim Pria dan Wanita! SQL: ALTER TABLE TEAMS ADD COLUMN TYPE CHAR (1);
Contoh 10: Dengan adanya kolom TYPE, maka (misalkan) tim 2 sebagai tim Pria harus di-update. SQL: UPDATE TEAMS SET TYPE = 'M' WHERE TEAMNO = 2; 22
Contoh 11: Tambahkan panjang karakter kolom TOWN dari 10 menjadi 20! ALTER TABLE PLAYERS MODIFY COLUMN TOWN CHAR (20);
23
DDL: Alter (Tabel) – Drop & Rename DROP [COLUMN] col_name DROP PRIMARY KEY DROP FOREIGN KEY fk_symbol Contoh 12: Hapuskan kolom TYPE dari tabel TEAMS! SQL: ALTER TABLE TEAMS DROP COLUMN TYPE
RENAME [TO] new_tbl_name Contoh 13: Ubah nama tabel Teams menjadi Groups! SQL : ALTER TABLE Teams RENAME Groups
24
Data Manipulation Language (DML) INSERT / UPDATE / DELETE, SELECT Klausa-klausa SELECT: FROM --> menentukan tabel(-tabel) sumber WHERE --> memilih baris(-baris) yang memenuhi kondisi (-kondisi) GROUP BY --> menggabungkan baris(-baris) yang kolomnya memiliki nilai yang sama HAVING --> memilih grup yang memenuhi kondisi tertentu SELECT --> memilih kolom(-kolom) ORDER BY --> mengurutkan barisbaris berdasarkan nilai-nilai yang ada dalam kolom(-kolom) 25
DML: Klausa Select Contoh 14: Tampilkan seluruh data penalti. SQL: SELECT * FROM Penalties Hasil:
DML: Klausa Where Contoh 16: Dapatkan nomor, nama, jenis kelamin dan tanggal lahir dari masing-masing pemain pria yang lahir setelah tahun 1970. SELECT FROM WHERE
playerno, name, sex, birth_date Players sex = ‘M’ AND Year(birth_date) > 1970
Contoh 17: Dapatkan nomor pemain yang pernah terkena penalti lebih besar dari 25! SELECT FROM WHERE
playerno Penalties amount > 25
27
DML: Klausa Order By Contoh 18: Buat daftar nomor pembayaran dan nomor pemain untuk masingmasing penalti; urutkan hasil berdasarkan nomor pemain dan nomor pembayaran untuk masing-masing pemain. SQL: SELECT FROM ORDER BY atau:
paymentno, playerno Penalties playerno, paymentno
ORDER BY
Hasil: PAYMENTNO 1 6 3 8 2 5 7 4
2, 1 PLAYERNO 6 8 27 27 44 44 44 104
28
DML: Klausa Select (lengkap) Contoh 19: Cari nomor dari masing-masing pemain yang telah melakukan lebih dari 1 penalti yang besarnya lebih dari 25. SQL: SELECT FROM WHERE GROUP BY HAVING ORDER BY
DML: Klausa Select (lebih dari 1 tabel) Contoh 20: Tampilkan nomor tim dan nama kapten dari masing-masing tim SQL: SELECT TEAMNO, NAME FROM TEAMS, PLAYERS WHERE TEAMS.PLAYERNO = PLAYERS.PLAYERNO
30
DML: Klausa Select (lebih dari 1 tabel) (contd-2) Contoh 21: Dari data penalti, tampilkan nomor pembayaran, jumlah penalti, nomor pemain dan nama serta inisial pemain tersebut! SQL: SELECT PAYMENTNO, PENALTIES.PLAYERNO, AMOUNT, NAME, INITIALS FROM PENALTIES, PLAYERS WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO
Contoh 22: Cari nama dan inisial pemain yang telah bermain di dalam pertandingan ! SQL: SELECT DISTINCT P.NAME, P.INITIALS FROM PLAYERS AS P, MATCHES AS M WHERE P.PLAYERNO = M.PLAYERNO
31
DML: Insert data ke dalam Tabel INSERT INTO
[] VALUES (<expression> [{,<expression>}. . . ]) Contoh 23: Sebuah tim baru dengan pemain nomor 100 sebagai kaptennya telah bergabung di dalam liga. Tim ketiga ini akan bermain di dalam divisi ketiga. SQL: INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (3,100,'third') atau: INSERT INTO TEAMS VALUES (3,100,'third')
32
DML: Update data dalam Tabel UPDATE
SET [ WHERE ] Contoh 24: Keluarga Parmenter telah pindah rumah. Sekarang mereka tinggal di Palmer Street nomor 83, kota Inglewood, kode pos 1234UU. Nomor telepon mereka yang baru belum diketahui. SQL: UPDATE PLAYERS SET STREET = 'Palmer Street', HOUSENO = '83', TOWN = 'Inglewood', POSTCODE = '1234UU', PHONENO = NULL WHERE NAME = 'Parmenter'
Contoh 25: Naikkan jumlah penalti sebanyak 5%! SQL: UPDATE PENALTIES SET AMOUNT = AMOUNT * 1.05 33
DML: Delete data dalam Tabel DELETE FROM
[ WHERE ] Contoh 26: Hapus seluruh data penalti yang dilakukan oleh pemain nomor 44 pada tahun 1980! Query: DELETE FROM PENALTIES WHERE PLAYERNO = 44 AND YEAR(PAYMENT_DATE) = 1980
