Creating and Using a Database

1. Create database

CREATE DATABASE data_karyawan;

2. Show database list

show databases;

3. Use database

use data_karyawan;

4. Show current database

select database();

Working with Table

1. Create table

create table biodata_karyawan (nama varchar(20), umur int(2), gender char(1), tanggal_lahir date);

2. Show table list inside database

show tables;

3. Show table details

describe biodata_karyawan;

4. Edit field (column)

alter table biodata_karyawan change gender jenis_kelamin varchar(20);

5. Add new field

alter table biodata_karyawan add no_ktp int(16);

6. Delete field

alter table biodata_karyawan drop no_ktp;

Insert & Select Data

1. Add data to biodata_karyawan table in data_karyawan database

use data_karyawan;
insert into biodata_karyawan values ('Malik', 22, 'Laki-laki', '1999-08-13');

2. Show table contents

select * from biodata_karyawan;

3. Add more data

insert into biodata_karyawan values ('Andre', 22, 'Laki-laki', '1999-08-20');
insert into biodata_karyawan values ('Rezi', 22, 'Perempuan', '1999-06-01');
insert into biodata_karyawan values ('Bagas', 20, 'Laki-laki', '1999-07-11');
insert into biodata_karyawan values ('Mela', 18, 'Perempuan', '2002-07-11');

4. Show table contents

select * from biodata_karyawan;

5. Select distinct from umur

select distinct jenis_kelamin from biodata_karyawan;

6. Change tanggal lahir Andre to 1998-12-12

update biodata_karyawan set tanggal_lahir='1998-12-12' where nama='Andre';

7. Show table contents

select * from biodata_karyawan;

Where Clause & Where Operator

1. Show karyawan data with name Malik

select * from biodata_karyawan where nama='Malik';

2. Show karyawan data with umur>20

select * from biodata_karyawan where umur>20;

3. Show karyawan data with umur 20 or 18

select * from biodata_karyawan where umur=20 OR umur=18;

User Management

1. Create user student

create user 'student'@'localhost';

2. Show user list

select user from mysql.user;

3. Set user password

set password for 'student'@'localhost' = 'student_pass';

4. Give SELECT and UPDATE permission to user student

grant select,update on data_karyawan.* to 'student'@'localhost';
show grants for 'student'@'localhost';

5. Disconnect from server

quit

6. Login to user student

mysql -u student -p

7. Verification

use data_karyawan;

# Result : Success
update biodata_karyawan set tanggal_lahir='1998-11-11' where nama='Andre';

# Result : Failed
delete from biodata_karyawan where nama='Bagas';