=> create database basisdata;
=> use basisdata;
id_pegawai
INT(11),
nama
VARCHAR(25),
alamat
VARCHAR(50),
kd_post
CHAR(5),
email
VARCHAR(30)
);
=>Melihat struktur table pegawai
(sintak 'describe')
describe
pegawai;
=>Perubahan nama kolom (sintak
'change')
ALTER
TABLE pegawai CHANGE kd_post kd_pos VARCHAR(25);
=>Perubahan type data (sintak
'modify')
ALTER
TABLE pegawai MODIFY id_pegawai VARCHAR(10);
=>Penambahan field (sintak
'add')
ALTER
TABLE pegawai ADD jabatan VARCHAR(25);
=>Pengahapusan field (sintak
'drop column')
ALTER
TABLE pegawai DROP COLUMN jabatan;
=>Mengganti nama table (sintak
'rename')
ALTER
TABLE pegawai RENAME pegawai_new;
=>Lihat table yang telah di ganti
SHOW
TABLES;
=>Drop table
DROP
TABLE pegawai_new;
=>Buat database nama_kalian
CREATE DATABASE nama_kalian;
=>Buat table matkul
CREATE TABLE matkul(
id INT(3),
kode_mk VARCHAR(10),
nama VARCHAR(25),
sks INT(2)
);
=>lihat struktur table matkul
Describe matkul;
=>Memasukan data
INSERT INTO matkul(id, kode_mk, nama, sks) VALUES (1,
'A', 'Basis Data', 3);
=>memasukan banyak data
INSERT INTO matkul(id, kode_mk, nama, sks)
VALUES(2, 'A1', 'Prak. Basis Data', 3),
(3, 'B', 'Alogritma', 3),
(4, 'B1', 'Prak. Alogritma', 3);
=>Menampilkan 1 kolom
SELECT kode_mk FROM matkul;
=>Menampilkan 2 kolom
SELECT kode_mk, nama FROM matkul;
=>Menampilkan semua kolom
SELECT * FROM matkul;
=>Mengurutkan Data besar => kecil
SELECT * FROM matkul ORDER BY id DESC;
=>Mengurutkan data kecil => besar
SELECT * FROM matkul ORDER BY id ASC;
=>Update data
UPDATE matkul SET sks=2 WHERE id=1;
UPDATE matkul SET nama='Sistem Operasi' WHERE id=3;
=> CREATE DATABASE nama_kalian;
=> USE nama_kalian;
=> CREATE TABLE mhs(
id INT(2),
nim INT(15),
nama VARCHAR(25),
sek VARCHAR(1),
ttl DATE
);
=> DESCRIBE mhs;
=>Masukan data
INSERT INTO mhs() VALUES(1,
2345, 'Rita Susilowati', 'P', '1992-12-29'),
(2, 2346, 'Andi
Nugroho', 'L', '1980-02-27'),
(3, 2347, 'Slamet
Widodo', 'L', '1970-11-20'),
(4, 2348, 'Nike
Astuti', 'P', '1990-11-20'),
(5, 2349, 'Novi
Yulianti', 'P', '1992-12-29');
=> SELECT * FROM mhs;
=>tampilkan data dengan operator =
SELECT * FROM mhs WHERE sek='P';
=> SELECT * FROM mhs WHERE
id >= 3;
=>OPERATOR AND
SELECT * FROM mhs WHERE sek = 'P' AND ttl =
'1990-11-20';
=>OPERATOR <>
SELECT * FROM mhs WHERE sek <> 'P';
=>BETWEEN DAN NOT BETWEEN
SELECT * FROM mhs WHERE nim BETWEEN 2346 AND 2347;
SELECT * FROM mhs WHERE nim NOT BETWEEN 2346 AND
2347;
=>OPERATOR LIKE
SELECT * FROM mhs WHERE nama
LIKE 'Rita Susilowati';
SELECT * FROM mhs WHERE nama
LIKE '%o';
SELECT * FROM mhs WHERE nama
LIKE '%a%';
SELECT * FROM mhs WHERE nama
NOT LIKE 'a%';
=>buat database nama_kalian
CREATE DATABASE nama_kalian;
=>buat table mhs
CREATE TABLE mhs(
npm INT(11) NOT NULL PRIMARY KEY,
nama VARCHAR(25),
alamat VARCHAR(25),
sex VARCHAR(25),
tgl_lahir DATE
);
=>isikan data ke table mhs
INSERT INTO mhs() VALUES
(1052000, 'Johan Saputra', 'Jl. Raya Cianjur', 'L',
'1990-12-30'),
(1052001, 'Laksmi Dewi', 'Jl. Raya Cianjur Km.2',
'P', '1993-08-12'),
(1052003, 'Annisa', 'Jl. Tata Surya', 'P',
'1994-08-07'),
(1052004, 'Leon', 'Jl. Raya Sukabumi Km.2', 'L',
'1990-04-18'),
(1052005, 'Dani', 'Jl. Raya Cipanas', 'L',
'1990-12-30'),
(1052006, 'Nijar', 'Jl. Tajur', 'L', '1992-12-21'),
(1052007, 'Faroni', 'Cikalong', 'L', '1991-07-22'),
(1052008, 'Faris', 'Bogor', 'L', '1993-05-23'),
(1052009, 'Hilmi', 'Sukabumi', 'L', '1990-06-24');
=>buat table nilai
CREATE TABLE nilai(
npm INT(11) NOT NULL PRIMARY KEY,
nama VARCHAR(25),
nilai INT(3)
);
=>isikan data ke table nilai
INSERT INTO nilai() VALUES
(1052000, 'Johan Saputra', 90),
(1052001, 'Laksmi Dewi', 40),
(1052002, 'Abu Bakar', 75),
(1052003, 'Annisa', 80),
(1052004, 'Leon', 60),
(1052005, 'Dani', 70),
(1052006, 'Nijar', 70),
(1052007, 'Faroni', 70),
(1052008, 'Faris', 50),
(1052009, 'Hilmi', 60);
=>menampilkan nilai mahasiswa dari 2 table
SELECT mhs.npm, mhs.nama, nilai.nilai FROM mhs, nilai
WHERE mhs.npm=nilai.npm;
=>cross join
SELECT mhs.nama, mhs.alamat, mhs.sex, nilai.nilai
FROM mhs CROSS JOIN nilai;
=>inner join
SELECT * FROM mhs INNER JOIN nilai ON
mhs.npm=nilai.npm;
=>STRAIGHT_JOIN
SELECT nilai FROM mhs STRAIGHT_JOIN nilai;
=>left join
SELECT mhs.npm, mhs.nama,nilai.nilai FROM mhs LEFT JOIN
nilai ON mhs.npm=nilai.npm;
=>right join
SELECT mhs.npm, mhs.nama,nilai.nilai FROM mhs RIGHT
JOIN nilai ON mhs.npm=nilai.npm;
CREATE TABLE kelas(
npm INT(11) NOT NULL PRIMARY KEY,
kls VARCHAR(10)
);
INSERT INTO kelas() VALUES
(1052000, 'If C 2009'),
(1052001, 'If A 2009'),
(1052002, 'If C 2009'),
(1052003, 'If B 2009'),
(1052004, 'If B 2009'),
(1052005, 'If C 2009'),
(1052006, 'If C 2009'),
(1052007, 'If B 2009'),
(1052008, 'If C 2009'),
(1052009, 'If A 2009');
SELECT mhs.npm, mhs.nama,
nilai.nilai, kelas.kls FROM mhs,nilai,kelas WHERE mhs.npm=nilai.npm AND
mhs.npm=kelas.npm;
CREATE DATABASE praktikum6;
USE praktikum6;
=>table mahasiswa
CREATE TABLE mahasiswa(
npm INT(11) NOT NULL PRIMARY KEY,
namaMhs VARCHAR(25),
alamat VARCHAR(50)
);
/*masukan data*
INSERT INTO mahasiswa() VALUES
(1234, 'Andi Santoso', 'Sukaasih, Bandung'),
(1235, 'Bunafit Yahya', 'Cimahi, Bandung'),
(1236, 'Ike Ferina', 'Baros, Bandung'),
(1237, 'Riani', 'Pasirkaliki, Bandung'),
(1238, 'Krisjiono', 'Ciwastra, Bandung'),
(1239, 'Marfaniatun', 'Cijerah, Bandung');
=>table matkul
CREATE TABLE matkul(
kodeMatkul VARCHAR(5) NOT NULL PRIMARY KEY,
namaMatkul VARCHAR(20),
sks INT(3)
);
/*masukan Data*/
INSERT INTO matkul() VALUES
('MK-01', 'Sistem Informasi', 3),
('MK-02', 'Algoritma Pemrograman', 3),
('MK-03', 'Pemrograman Web', 3),
('MK-04', 'Apsi', 3),
('MK-05', 'Jaringan Komputer', 3);
=>table ambilMatkul
CREATE TABLE ambilmatkul(
npm INT(11),
kodeMatkul VARCHAR(5)
);
/*masukan data*/
INSERT INTO ambilmatkul()
VALUES
(1234, 'MK-01'),
(1234, 'MK-02'),
(1235, 'MK-03'),
(1236, 'MK-03'),
(1237, 'MK-01'),
(1238, 'MK-02');
=>table jadwal
CREATE TABLE jadwal(
kodeMatkul VARCHAR(5) NOT NULL PRIMARY KEY,
kelas VARCHAR(10),
hari VARCHAR(10)
);
/*masukan Data*/
INSERT INTO jadwal() VALUES
('MK-01', 'FT-01', 'Senin'),
('MK-02', 'FT-02', 'Selasa'),
('MK-03', 'FT-01', 'Rabu'),
('MK-04', 'FT-02', 'Kamis'),
('MK-05', 'FT-01', 'Jumat');
jawaban
1. SELECT mahasiswa.npm,
mahasiswa.namaMhs, mahasiswa.alamat, matkul.namaMatkul, matkul.sks FROM
mahasiswa,ambilmatkul, matkul WHERE mahasiswa.npm=ambilmatkul.npm AND
matkul.kodeMatkul=ambilmatkul.kodeMatkul ORDER BY npm ASC;
2. SELECT mahasiswa.npm,
mahasiswa.namaMhs, matkul.kodeMatkul, matkul.namaMatkul, jadwal.kelas,
jadwal.hari FROM mahasiswa,matkul,ambilmatkul,jadwal WHERE
mahasiswa.npm=ambilmatkul.npm AND matkul.kodeMatkul=ambilmatkul.kodeMatkul AND
jadwal.kodeMatkul=ambilmatkul.kodeMatkul;
=>table gaji_pegawai
CREATE TABLE gaji_pegawai(
nip INT(11),
nama VARCHAR(25),
sek VARCHAR(1),
kode_bag INT(11),
gaji INT(11),
kota VARCHAR(25)
);
=>Isikan data
INSERT INTO gaji_pegawai()
VALUES
(101, 'Bondan', 'L', 501, 200000, 'Bandung'),
(102, 'Yuri', 'P', 501, 180000, 'Bandung'),
(103, 'Bambang', 'L', 501, 170000, 'Cianjur'),
(104, 'Sohib', 'L', 501, 200000, 'Lembang'),
(105, 'Jana', 'L', 501, 230000, 'Lembang'),
(106, 'Andri', 'L', 502, 160000, 'Sukabumi'),
(107, 'Aris', 'L', 502, 200000, 'Pajajaran'),
(108, 'Vino', 'L', 502, 200000, 'Bogor'),
(109, 'Dadan', 'L', 502, 210000, 'Bogor'),
(110, 'Andi', 'L', 503, 300000, 'Cianjur'),
(111, 'Faris', 'L', 503, 450000, 'Bandung'),
(112, 'Endang', 'L', 503, 270000, 'Bandung'),
(113, 'Nizar', 'L', 504, 320000, 'Cianjur'),
(114, 'Bunga', 'P', 504, 170000, 'Sukabumi');
=>Menghtung rata-rata keseluruhan gaji
SELECT AVG(gaji) FROM
gaji_pegawai;
=>Menghitung jumlah pekerja berdasarkan kota
SELECT kota, COUNT(sek) FROM
gaji_pegawai GROUP BY kota;
SELECT gaji_pegawai.kode_bag,
COUNT(gaji), SUM(gaji) FROM gaji_pegawai GROUP BY kode_bag;
TRIGGER
//database
create database mytrigger;
use mytrigger;
//tabel server
CREATE TABLE t_server(NPM
INT(11) PRIMARY KEY, nama VARCHAR(25), Alamat VARCHAR(50));
//tabel client
CREATE TABLE t_client(NPM
INT(11) PRIMARY KEY, nama VARCHAR(25), Alamat VARCHAR(50));
//trigger auto insert
DELIMITER |
CREATE TRIGGER auto_insert_data
BEFORE INSERT ON t_server FOR
EACH ROW
BEGIN
INSERT INTO t_client (npm, nama, alamat) VALUES
(NEW.npm, NEW.nama, NEW.alamat);
END;
|
//trigger auto_delete;
DELIMITER |
CREATE TRIGGER auto_delete_data
BEFORE DELETE ON t_server FOR
EACH ROW
BEGIN
DELETE FROM t_client WHERE npm=OLD.npm;
END;
|
//trigger auto_update
DELIMITER |
CREATE TRIGGER auto_update_data
BEFORE UPDATE ON t_server FOR
EACH ROW
BEGIN
UPDATE t_client SET nama=NEW.nama, alamat=NEW.alamat
WHERE npm=NEW.npm ;
END;
|
Semua Artikel :
0 komentar:
Monggo komen disini ;)