MySQL

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>;

Menghapus isi tabel

TRUNCATE TABLE <nama_tabel>;

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

Import ke csv

LOAD DATA INFILE '/var/lib/mysql-files/<nama_file>.csv' 
INTO TABLE <nama_tabel>
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

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');

Referensi

• https://www.mysqltutorial.org/mysql-export-table-to-csv/
• https://stackoverflow.com/questions/31951468/error-code-1290-the-mysql-server-is-running-with-the-secure-file-priv-option