Membuat Database MySQL - Database Myshop

Dadan (dadannazril@gmail.
com)
1. Membuat Database
C:\Users\Jembar.id>cd C:\xampp\mysql\bin
C:\xampp\mysql\bin>mysql -u root
MariaDB [(none)]> create database myshop
MariaDB [(none)]> use myshop
2. Membuat table didalam Database
create table users(

-> id int(10) auto_increment,
-> name varchar(255),
-> email varchar(255),
-> pasword varchar(255),
-> primary key(id)
-> );
create table items(

-> description varchar(255),
-> price int(10),
-> stock int(10),
-> primary key(id),
-> category_id int(10),
-> foreign key (category_id) references users(id)
-> );
create table categories(

-> primary key (id)
-> );
show tables
-> ;
+------------------+
| Tables_in_myshop |
+------------------+
| categories |
| items |
| users |
+------------------+
desc users
-> ;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| pasword | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
desc items
-> ;
+-------------+--------------+------+-----+---------+----------------+
+-------------+--------------+------+-----+---------+----------------+
| description | varchar(255) | YES | | NULL | |
| price | int(10) | YES | | NULL | |
| stock | int(10) | YES | | NULL | |
| category_id | int(10) | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+----------------+
desc categories
-> ;
+-------+--------------+------+-----+---------+----------------+
+-------+--------------+------+-----+---------+----------------+
+-------+--------------+------+-----+---------+----------------+
3. Memasukan data pada table
MariaDB [myshop]> insert into users(name,email,pasword) values ("John

Doe","john@doe.com","john123"),("John Doe","john@doe.com","jenita123");
Query OK, 2 rows affected (0.128 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [myshop]> select * from users

-> ;
+----+----------+--------------+-----------+
| id | name | email | pasword |
+----+----------+--------------+-----------+
| 1 | John Doe | john@doe.com | john123 |
| 2 | John Doe | john@doe.com | jenita123 |
+----+----------+--------------+-----------+
2 rows in set (0.075 sec)
MariaDB [myshop]> insert into categories (name) values ("gadget"),("cloth"),

("men"),("women"),("branded");
MariaDB [myshop]> insert into items (name,description,price,stock,category_id)

values
-> ("Sumsang b50","hape keren dari merek sumsang",4000000,100,1),
-> ("Uniklooh","baju keren dari brand ternama",500000,50,2),
-> ("IMHO Watch","jam tangan anak yang jujur banget",2000000,10,1);
MariaDB [myshop]> select * from items

-> ;
+----+-------------+-----------------------------------+---------+-------
+-------------+
| id | name | description | price | stock |
category_id |
+----+-------------+-----------------------------------+---------+-------
+-------------+
| 1 | Sumsang b50 | hape keren dari merek sumsang | 4000000 | 100 |
1 |
| 2 | Uniklooh | baju keren dari brand ternama | 500000 | 50 |
2 |
| 3 | IMHO Watch | jam tangan anak yang jujur banget | 2000000 | 10 |
1 |
+----+-------------+-----------------------------------+---------+-------
+-------------+
MariaDB [myshop]> select * from categories

-> ;
+----+---------+
| id | name |
+----+---------+
| 1 | gadget |
| 2 | cloth |
| 3 | men |
| 4 | women |
| 5 | branded |
+----+---------+
5. Mengambil data dari Database

a. Mengambil data users
MariaDB [myshop]> select id,name,email from users;

+----+----------+--------------+
| id | name | email |
+----+----------+--------------+
| 1 | John Doe | john@doe.com |
| 2 | John Doe | john@doe.com |
+----+----------+--------------+
b. Mengambil data items
MariaDB [myshop]> select * from items where price > 1000000;

+----+-------------+-----------------------------------+---------+-------
+-------------+
category_id |
+----+-------------+-----------------------------------+---------+-------
+-------------+
1 |
1 |
+----+-------------+-----------------------------------+---------+-------
+-------------+
MariaDB [myshop]> select * from items where name like '%sang%';

+----+-------------+-------------------------------+---------+-------+-------------
+
| id | name | description | price | stock | category_id
|
+----+-------------+-------------------------------+---------+-------+-------------
+
| 1 | Sumsang b50 | hape keren dari merek sumsang | 4000000 | 100 | 1
|
+----+-------------+-------------------------------+---------+-------+-------------
+
1 row in set (0.000 sec)
c. Menampilkan data items join dari kategori
MariaDB [myshop]> select * from items inner join categories on

items.category_id=categories.id;
+----+-------------+-----------------------------------+---------+-------
+-------------+----+--------+
category_id | id | name |
+----+-------------+-----------------------------------+---------+-------
+-------------+----+--------+
1 | 1 | gadget |
2 | 2 | cloth |
1 | 1 | gadget |
+----+-------------+-----------------------------------+---------+-------
+-------------+----+--------+
5. Mengubah data dari Database
MariaDB [myshop]> update items set price = 2500000 where id = 1;

Query OK, 1 row affected (0.180 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [myshop]> select * from items;

+----+-------------+-----------------------------------+---------+-------
+-------------+
category_id |
+----+-------------+-----------------------------------+---------+-------
+-------------+
1 |
2 |
1 |
+----+-------------+-----------------------------------+---------+-------
+-------------+

Membuat Database MySQL - Database Myshop

Uploaded by

Copyright:

Available Formats

Membuat Database MySQL - Database Myshop

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Membuat Database MySQL - Database Myshop

Uploaded by

Copyright:

Available Formats

Dadan (dadannazril@gmail.

2. Membuat table didalam Database

create table users(

create table items(

create table categories(

3. Memasukan data pada table

MariaDB [myshop]> insert into users(name,email,pasword) values ("John

MariaDB [myshop]> select * from users

MariaDB [myshop]> insert into categories (name) values ("gadget"),("cloth"),

MariaDB [myshop]> insert into items (name,description,price,stock,category_id)

MariaDB [myshop]> select * from items

MariaDB [myshop]> select * from categories

5. Mengambil data dari Database

MariaDB [myshop]> select id,name,email from users;

b. Mengambil data items

MariaDB [myshop]> select * from items where price > 1000000;

MariaDB [myshop]> select * from items where name like '%sang%';

c. Menampilkan data items join dari kategori

MariaDB [myshop]> select * from items inner join categories on

5. Mengubah data dari Database

MariaDB [myshop]> update items set price = 2500000 where id = 1;

MariaDB [myshop]> select * from items;

You might also like