MySQL - Revision history

Aditya at 03:21, 5 September 2023

2023-09-05T03:21:10Z

Aditya at 04:20, 18 July 2023

2023-07-18T04:20:25Z

Aditya at 02:12, 17 February 2023

2023-02-17T02:12:29Z

Aditya at 01:18, 17 February 2023

2023-02-17T01:18:52Z

Aditya: /* Export sql */

2023-02-07T06:27:29Z

Export sql

Aditya at 06:52, 28 December 2022

2022-12-28T06:52:44Z

Aditya at 00:01, 26 December 2022

2022-12-26T00:01:47Z

Aditya at 07:53, 7 December 2022

2022-12-07T07:53:07Z

Aditya at 07:52, 7 December 2022

2022-12-07T07:52:33Z

Aditya: /* Modifikasi tabel */

2022-05-15T21:51:47Z

Modifikasi tabel

@@ Line 51: / Line 51: @@
   ALTER TABLE <table_name> MODIFY COLUMN <column_name> <datatype>;
   ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
 ====Insert data====

← Older revision		Revision as of 04:20, 18 July 2023
Line 89:		Line 89:
	mysql -u <username> -p <nama-database> < db.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====		====GRANT ALL====

← Older revision		Revision as of 02:12, 17 February 2023
Line 105:		Line 105:
	===Referensi===		===Referensi===
	* https://www.mysqltutorial.org/mysql-export-table-to-csv/		* 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

@@ Line 3: / Line 3: @@
 ====Login====
-   mysql -u <username> -p
+ mysql -u <username> -p
 Kemudian isikan passwordnya
@@ Line 10: / Line 10: @@
 ====Melihat database yang ada====
-   show databases;
+ show databases;
 ====Membuat database====
-   CREATE DATABASE <nama_database>;
+ CREATE DATABASE <nama_database>;
 ====Drop database====
-   DROP DATABASE <nama_database>;
+ DROP DATABASE <nama_database>;
 ====Melihat tabel yang ada====
-   show tables;
+ show tables;
 ====Membuat tabel====
-   CREATE TABLE Mahasiswa (
+ CREATE TABLE Mahasiswa (
-     NPM int(11) PRIMARY KEY,
+   NPM int(11) PRIMARY KEY,
-     Nama varchar(255) NOT NULL
+   Nama varchar(255) NOT NULL
-   );
+ );
 Jika ID autoincrement
-   CREATE TABLE Barang (
+ CREATE TABLE Barang (
-     Barangid int NOT NULL AUTO_INCREMENT,
+   Barangid int NOT NULL AUTO_INCREMENT,
-     NamaBarang varchar(255) NOT NULL,
+   NamaBarang varchar(255) NOT NULL,
-     PRIMARY KEY (Barangid)
+   PRIMARY KEY (Barangid)
-   );
+ );
 ====Modifikasi tabel====
-   ALTER TABLE Mahasiswa ADD Email varchar(255);
+ ALTER TABLE Mahasiswa ADD Email varchar(255);
-   ALTER TABLE Mahasiswa DROP COLUMN Email;
+ ALTER TABLE Mahasiswa DROP COLUMN Email;
-   ALTER TABLE <table_name> MODIFY COLUMN <column_name> <datatype>;
+ ALTER TABLE <table_name> MODIFY COLUMN <column_name> <datatype>;
-   ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
+ ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
 ====Insert data====
-   INSERT INTO Mahasiswa (NPM, Nama) VALUES
+ INSERT INTO Mahasiswa (NPM, Nama) VALUES
-   ('001', 'Mahasiswa 1'),
+ ('001', 'Mahasiswa 1'),
-   ('002', 'Mahasiswa 2');
+ ('002', 'Mahasiswa 2');
 ====Update data====
-   UPDATE <table_name> SET <column>="<value>" WHERE <column>="<value>";
+ UPDATE <table_name> SET <column>="<value>" WHERE <column>="<value>";
-   UPDATE mahasiswa SET nilai='90' WHERE npm='123';
+ UPDATE mahasiswa SET nilai='90' WHERE npm='123';
 ====Menghapus data====
@@ Line 70: / Line 70: @@
   mysqldump -u <username> -p <nama-database> > db.sql
   mysqldump -u <username> -p <nama_database> table1 table2 table3 > dump.sql
 ====Import sql====
-   mysql -u <username> -p <nama-database> < db.sql
+ mysql -u <username> -p <nama-database> < db.sql
@@ Line 86: / Line 100: @@
 ====Menghitung jumlah value dalam satu column berdasarkan id====
   select sum(<nama_kolom>) as total from <nama_tabel> where id in ('1','2');

@@ Line 68: / Line 68: @@
 ====Export sql====
-   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
 ====Import sql====

← Older revision		Revision as of 00:01, 26 December 2022
Line 77:		Line 77:
	create user 'user' identified by 'password';		create user 'user' identified by 'password';
	grant all privileges on database-name.* to 'user';		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');

← Older revision		Revision as of 07:52, 7 December 2022
Line 58:		Line 58:
	('002', 'Mahasiswa 2');		('002', 'Mahasiswa 2');

		+	====Update data====
		+
		+	UPDATE <table_name> SET <column>='' WHERE <column>='';
		+	UPDATE mahasiswa SET nilai='90' WHERE npm='123';

	====Export sql====		====Export sql====