Difference between revisions of "MySQL"
Jump to navigation
Jump to search
Line 3: | Line 3: | ||
====Login==== | ====Login==== | ||
− | + | mysql -u <username> -p | |
Kemudian isikan passwordnya | Kemudian isikan passwordnya | ||
Line 10: | Line 10: | ||
====Melihat database yang ada==== | ====Melihat database yang ada==== | ||
− | + | show databases; | |
====Membuat database==== | ====Membuat database==== | ||
− | + | CREATE DATABASE <nama_database>; | |
====Drop database==== | ====Drop database==== | ||
− | + | DROP DATABASE <nama_database>; | |
====Melihat tabel yang ada==== | ====Melihat tabel yang ada==== | ||
− | + | show tables; | |
====Membuat tabel==== | ====Membuat tabel==== | ||
− | + | CREATE TABLE Mahasiswa ( | |
− | + | NPM int(11) PRIMARY KEY, | |
− | + | Nama varchar(255) NOT NULL | |
− | + | ); | |
Jika ID autoincrement | Jika ID autoincrement | ||
− | + | CREATE TABLE Barang ( | |
− | + | Barangid int NOT NULL AUTO_INCREMENT, | |
− | + | NamaBarang varchar(255) NOT NULL, | |
− | + | PRIMARY KEY (Barangid) | |
− | + | ); | |
====Modifikasi tabel==== | ====Modifikasi tabel==== | ||
− | + | ALTER TABLE Mahasiswa ADD Email varchar(255); | |
− | + | ALTER TABLE Mahasiswa DROP COLUMN Email; | |
− | + | ALTER TABLE <table_name> MODIFY COLUMN <column_name> <datatype>; | |
− | + | ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>; | |
====Insert data==== | ====Insert data==== | ||
− | + | INSERT INTO Mahasiswa (NPM, Nama) VALUES | |
− | + | ('001', 'Mahasiswa 1'), | |
− | + | ('002', 'Mahasiswa 2'); | |
====Update data==== | ====Update data==== | ||
− | + | UPDATE <table_name> SET <column>="<value>" WHERE <column>="<value>"; | |
− | + | UPDATE mahasiswa SET nilai='90' WHERE npm='123'; | |
====Menghapus data==== | ====Menghapus data==== | ||
Line 70: | Line 70: | ||
mysqldump -u <username> -p <nama-database> > db.sql | mysqldump -u <username> -p <nama-database> > db.sql | ||
mysqldump -u <username> -p <nama_database> table1 table2 table3 > dump.sql | mysqldump -u <username> -p <nama_database> table1 table2 table3 > dump.sql | ||
+ | |||
+ | Export ke csv | ||
+ | |||
+ | SELECT | ||
+ | orderNumber, status, orderDate, requiredDate, comments | ||
+ | FROM | ||
+ | orders | ||
+ | WHERE | ||
+ | status = 'Cancelled' | ||
+ | INTO OUTFILE '/var/lib/mysql-files/report.csv' | ||
+ | FIELDS ENCLOSED BY '"' | ||
+ | TERMINATED BY ';' | ||
+ | ESCAPED BY '"' | ||
+ | LINES TERMINATED BY '\r\n'; | ||
====Import sql==== | ====Import sql==== | ||
− | + | mysql -u <username> -p <nama-database> < db.sql | |
Line 86: | Line 100: | ||
====Menghitung jumlah value dalam satu column berdasarkan id==== | ====Menghitung jumlah value dalam satu column berdasarkan id==== | ||
select sum(<nama_kolom>) as total from <nama_tabel> where id in ('1','2'); | select sum(<nama_kolom>) as total from <nama_tabel> where id in ('1','2'); | ||
+ | |||
+ | |||
+ | |||
+ | ===Referensi=== | ||
+ | * https://www.mysqltutorial.org/mysql-export-table-to-csv/ |
Revision as of 08:18, 17 February 2023
Contents
- 1 Perintah
- 1.1 Login
- 1.2 Melihat database yang ada
- 1.3 Membuat database
- 1.4 Drop database
- 1.5 Melihat tabel yang ada
- 1.6 Membuat tabel
- 1.7 Modifikasi tabel
- 1.8 Insert data
- 1.9 Update data
- 1.10 Menghapus data
- 1.11 Export sql
- 1.12 Import sql
- 1.13 GRANT ALL
- 1.14 Menampilkan data
- 1.15 Menghitung jumlah value dalam satu column berdasarkan id
- 2 Referensi
Perintah
Login
mysql -u <username> -p
Kemudian isikan passwordnya
Melihat database yang ada
show databases;
Membuat database
CREATE DATABASE <nama_database>;
Drop database
DROP DATABASE <nama_database>;
Melihat tabel yang ada
show tables;
Membuat tabel
CREATE TABLE Mahasiswa ( NPM int(11) PRIMARY KEY, Nama varchar(255) NOT NULL );
Jika ID autoincrement
CREATE TABLE Barang ( Barangid int NOT NULL AUTO_INCREMENT, NamaBarang varchar(255) NOT NULL, PRIMARY KEY (Barangid) );
Modifikasi tabel
ALTER TABLE Mahasiswa ADD Email varchar(255); ALTER TABLE Mahasiswa DROP COLUMN Email; ALTER TABLE <table_name> MODIFY COLUMN <column_name> <datatype>; ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
Insert data
INSERT INTO Mahasiswa (NPM, Nama) VALUES ('001', 'Mahasiswa 1'), ('002', 'Mahasiswa 2');
Update data
UPDATE <table_name> SET <column>="<value>" WHERE <column>="<value>"; UPDATE mahasiswa SET nilai='90' WHERE npm='123';
Menghapus data
DELETE FROM table_name WHERE condition;
Export sql
mysqldump -u <username> -p <nama-database> > db.sql mysqldump -u <username> -p <nama_database> table1 table2 table3 > dump.sql
Export ke csv
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE '/var/lib/mysql-files/report.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Import sql
mysql -u <username> -p <nama-database> < db.sql
GRANT ALL
create user 'user' identified by 'password'; grant all privileges on database-name.* to 'user';
Menampilkan data
select * from <tabel>;
Menghitung jumlah value dalam satu column berdasarkan id
select sum(<nama_kolom>) as total from <nama_tabel> where id in ('1','2');