Belajar Mysql - Pembuatan LogFile Group

 

1. Membuat LogFile Group

Format:

CREATE LOGFILE GROUP logfile_group

    ADD UNDOFILE 'undo_file'

    [INITIAL_SIZE [=] initial_size]

    [UNDO_BUFFER_SIZE [=] undo_buffer_size]

    [REDO_BUFFER_SIZE [=] redo_buffer_size]

    [NODEGROUP [=] nodegroup_id]

    [WAIT]

    [COMMENT [=] comment_text]

    ENGINE [=] engine_name

Contoh :

CREATE LOGFILE GROUP lgData

ADD UNDOFILE 'undo.dat' INITIAL_SIZE = 10M;

2. Mengubah LogFile Group

Format:

ALTER LOGFILE GROUP logfile_group

    ADD UNDOFILE 'file_name'

    [INITIAL_SIZE [=] size]

    [WAIT]

    ENGINE [=] engine_name

Contoh :

ALTER LOGFILE GROUP lgData

    ADD UNDOFILE 'undo1.dat'

    INITIAL_SIZE=32M

    ENGINE=NDBCLUSTER;

3. Menghapus LogFile Group

Format:

DROP LOGFILE GROUP logfile_group

    ENGINE [=] engine_name

Contoh :

DROP LOGFILE GROUP lgData;

4.  Melihat list Logfile Group

SELECT LOGFILE_GROUP_NAME, LOGFILE_GROUP_NUMBER, EXTRA

    FROM INFORMATION_SCHEMA.FILES

    WHERE FILE_NAME = 'undo_10.dat';

5. Tablespace mengarah ke LogFile Group

CREATE TABLESPACE tsData

    ADD DATAFILE 'data1.dat'

    USE LOGFILE GROUP  lgdata

    INITIAL_SIZE 32M

    ENGINE NDBCLUSTER;

Belajar Mysql - Pembuatan Tablespace

 

1. Membuat Tablespace

Format :

CREATE TABLESPACE tablespace_name

    ADD DATAFILE 'file_name'

    USE LOGFILE GROUP logfile_group

    [EXTENT_SIZE [=] extent_size]

    [INITIAL_SIZE [=] initial_size]

    [AUTOEXTEND_SIZE [=] autoextend_size]

    [MAX_SIZE [=] max_size]

    [NODEGROUP [=] nodegroup_id]

    [WAIT]

    [COMMENT [=] comment_text]

    ENGINE [=] engine_name

Contoh :

CREATE TABLESPACE tsData;

2. Mengubah Tablespace

Format :

ALTER TABLESPACE tablespace_name

    {ADD|DROP} DATAFILE 'file_name'

    [INITIAL_SIZE [=] size]

    [WAIT]

    ENGINE [=] engine_name

Contoh :

ALTER TABLESPACE tsData RENAME TO tsDataNew;

3. Menghapus Tablespace

Format :

DROP TABLESPACE tablespace_name

    ENGINE [=] engine_name

Contoh :

DROP TABLESPACE tsDataNew;

4. Melihat list datafile pada tablespace

 SELECT LOGFILE_GROUP_NAME, FILE_NAME, EXTRA

    FROM INFORMATION_SCHEMA.FILES

    WHERE TABLESPACE_NAME = 'tsData' AND FILE_TYPE = 'DATAFILE';

5. Membuat table pada tablespace

CREATE TABLE t1 (

    c1 INT STORAGE DISK,

    c2 INT STORAGE MEMORY

    ) TABLESPACE tsData ENGINE NDB;

Belajar Mysql - Pembuatan Database

 

1. Membuat database

Format :

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

    [create_specification] ...

create_specification:

    [DEFAULT] CHARACTER SET [=] charset_name

  | [DEFAULT] COLLATE [=] collation_name

Contoh :

CREATE DATABASE dbData;

2. Mengubah database 

Format :

ALTER {DATABASE | SCHEMA} [db_name]

    alter_specification ...

ALTER {DATABASE | SCHEMA} db_name

    UPGRADE DATA DIRECTORY NAME

alter_specification:

    [DEFAULT] CHARACTER SET [=] charset_name

  | [DEFAULT] COLLATE [=] collation_name

Contoh :

ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;

ALTER DATABASE dbData CHARACTER SET= ascii;

ALTER DATABASE dbData COLLATE utf8_general_ci;

ALTER DATABASE dbData CHARACTER SET utf8 COLLATE utf8_general_ci ENCRYPTION = 'Y' READ ONLY = 1;

3. Mengganti nama database

Format :

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

Contoh :

Rename Database dbData to dbDataNew;

4. Melihat List Database

Format :

SHOW {DATABASES | SCHEMAS}

    [LIKE 'pattern' | WHERE expr]

Contoh :

SHOW DATABASES;

SHOW CREATE DATABASE dbData;

5. Menghapus database

Format :

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Contoh :

DROP DATABASE  dbDataNew;

Array Pada PostfreSQL

 

1. Buat Table

CREATE TABLE person_details (  

    id serial PRIMARY KEY,  

    person_name VARCHAR (200) ,  

    mobile_numbers TEXT []  

);

2. Insert 

2.1. Single Record

INSERT INTO person_details (person_name, mobile_number)  

VALUES('Maria Smith',ARRAY [ '(444)-333-1234','(555)-333-5432' ]);

2.2. Multi Record 

INSERT INTO person_details (person_name, mobile_number)  

VALUES('Mike Taylor','{"(444)-333-1234"}'),  

('Emma Garcia','{"(568)-333-5678"}'),  

('David Smith','{"(444)-333-7658","(308)-589-23458"}'); 

3. Select 

3.1. SELECT person_name, mobile_number  FROM person_details;  

3.2. SELECT person_name, mobile_number[1]  FROM person_details; 

3.3. SELECT person_name  FROM person_details  WHERE mobile_number[2]='(308)-589-23458'; 

3.4. Arrays data type : SELECT person_name,  unnest(mobile_number) FROM person_details;  

3.5. Search  : SELECT person_name, mobile_number  FROM person_details  

WHERE '(555)-333-5432' = ANY (mobile_number); 

4. Update

4.1. UPDATE person_details  SET mobile_number [2] = '(308)-859-54378'  WHERE ID = 4;  

4.2. UPDATE person_details  SET mobile_number = '{"(308)-859-54378"}'  WHERE ID = 4;  

Create Database Pada PostgreSQL

 

1. Create DB

Format :

CREATE DATABASE db_name  

OWNER =  role_name  

TEMPLATE = template           

ENCODING = encoding           

LC_COLLATE = collate              

LC_CTYPE = ctype  

TABLESPACE = tablespace_name  

CONNECTION LIMIT = max_concurrent_connection

Contoh :

CREATE DATABASE dbPostgre;

2. Select Database

2.1. Connect : postgres=# 

2.2. List Database : \l 

2.3. Connect ke database : postgres-# \c dbPostgre 

3. Drop DB

             

DROP DATABASE [ IF EXISTS] name;

REVOKE CONNECT ON DATABASE dbPostgre from public; 

3.1. Command SQL

dropdb [option...] dbname   

contoh :

dropdb -h localhost -p 5432 -U postgress dbPostgre

Password for user postgress: ****  

4. Akses Dalam Bentuk JSON

4.1. JSON

SELECT Purchase_description  -> 'purchaser' AS pruchaser  FROM Purchase; 

4.2.  JSON Condition

SELECT Purchase_description ->> 'purchaser' AS Purchaser  

FROM Purchase  WHERE Purchase_description-> 'items' ->> 'product' = 'ice cream'; 

4.3. Text

SELECT Purchase_description  ->> 'purchaser' AS pruchaser  FROM Purchase; 

4.4. JSON & Text

SELECT Purchase_description -> 'items' ->> 'product' as Product  FROM Purchase  

ORDER BY Product; 

4.5. json_object_keys function

SELECT json_object_keys (Purchase_description->'items')  FROM Purchase;   

4.6. json_each function

SELECT json_each (Purchase_description)  FROM Purchase;

4.7. json_each_text()

SELECT json_each_text(Purchase_description)  FROM Purchase; 

4.8. json_typeof function 

SELECT json_typeof(Purchase_description->'items')  FROM Purchase;

Table Pada PostgreSQL

 

1. Create Table

Format :

CREATE TABLE table_name(    

   column1 datatype,    

   column2 datatype,    

   .....    

   columnN datatype,    

   PRIMARY KEY(one or more columns )    

);

Contoh :

Create table department  

( 

dept_no int constraint dept_details_pk primary key   

dept_name text NOT NULL,  

Location varchar(15),  

);

2. Drop Table

DROP TABLE table_name;

3. Show Table

3.1. SELECT * FROM pg_catalog.pg_tables  WHERE  schemaname != 'pg_catalog'  AND schemaname != 'information_schema';

3.2. select *   from information_schema.tables   where table_schema='public'; 

3.3. SELECT * FROM pg_catalog.pg_tables  WHERE schemaname = 'myschema'   AND schemaname != 'information_schema';

4. Describe Table

SELECT COLUMN_NAME  FROM information_schema.COLUMNS  WHERE TABLE_NAME = 'customer';

5. ALTER table

Format :

ALTER TABLE table_name action;

5.1. ADD Column

ALTER TABLE Station  ADD COLUMN Latitude REAL;

5.2. Drop column

ALTER TABLE Station  DROP COLUMN Latitude;

5.3. Rename Column

ALTER TABLE Station  RENAME COLUMN St_Name TO Name;

5.4. Alter Column 

ALTER TABLE Station1  

ALTER COLUMN St_City TYPE Varchar(30),  

ALTER COLUMN St_State TYPE Varchar(20);

5.5. Add NOT NULL constraint

ALTER TABLE Station1 ALTER COLUMN St_City SET NOT NULL; 

5.6. Remove NOT NULL constraint 

ALTER TABLE Station1   ALTER COLUMN St_City DROP NOT NULL;

5.7. Add PRIMARY KEY 

ALTER TABLE Station1 ADD PRIMARY KEY (St_id);

6. Change Column Type

6.1. Single 

ALTER TABLE table_name  ALTER COLUMN column_name [SET DATA] TYPE new_data_type; 

6.2. Multi

ALTER TABLE table_name  

ALTER COLUMN column_name_1 [SET DATA] TYPE new_data_type,  

ALTER COLUMN column_name_2 [SET DATA] TYPE new_data_type; 

7. Truncate table

Format :

TRUNCATE TABLE table_name;  [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] 

7.1. truncate table student_information;  

7.2. TRUNCATE TABLE table_name   RESTART IDENTITY;  

7.3. TRUNCATE TABLE student_information   RESTART IDENTITY; 

7.4. TRUNCATE TABLE table_name1, table_name2 ...;  

7.5. Delete all Record

TRUNCATE TABLE table_name   CASCADE; 

8. temporary table

CREATE TEMPORARY TABLE temp_table_name(...); 

9. Column Alias

SELECT column_name AS alias_name  FROM table_name Conditions...;

10. Sequence

Format :

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name  

    [ AS { SMALLINT | INT | BIGINT } ]  

    [ INCREMENT [ BY ] increment ]  

    [ MINVALUE minvalue | NO MINVALUE ]   

    [ MAXVALUE maxvalue | NO MAXVALUE ]  

    [ START [ WITH ] start ]   

    [ CACHE cache ]   

    [ [ NO ] CYCLE ]  

    [ OWNED BY { table_name.column_name | NONE } ]

10.1. CREATE SEQUENCE jtpsequence  INCREMENT 3  START 20;

10.2. CREATE SEQUENCE Purchase_module_id  START 5  

INCREMENT 5  MINVALUE 5 OWNED BY Purchase_details.Module_id; 

11. Identity Column

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

12. Upsert

12.1. INSERT INTO table_name(column_list)   VALUES(value_list) ON CONFLICT target action; 

12.2.  INSERT INTO officers (officers_name, officers_addrerss )  VALUES('Olivia','Florida')  

ON CONFLICT ON CONSTRAINT officers_name  

DO NOTHING; 

12.3. INSERT INTO officers   VALUES(102,'Olivia','olivia22@gmail.com','Florida')  

ON CONFLICT (officers_name)   DO NOTHING;

12.4. INSERT INTO officers   VALUES(102,'Olivia','olivia22@gmail.com','Florida')  

ON CONFLICT (officers_name)  DO UPDATE SET officers_address =   

EXCLUDED.officers_address || ',' || officers.officers_address;

13. Subquery

13.1.  Kondisi Subquery

SELECT c1.car_name, c1.car_model,  

    (SELECT MIN (car_id)  

     FROM car c2  

     WHERE c1.car_id = c2.car_id) Subquery1  

FROM car c1;

13.2. Dalam Kategori

SELECT course.course_name, Subquery2.course_category_id  

FROM course,  

    (SELECT course_categories.course_category_id, course_categories.course_category,   

    COUNT (course_category_id) AS total  

    FROM course_categories  

    GROUP BY course_categories.course_category_id, course_categories.course_category) Subquery2  

WHERE Subquery2.course_category_id = course.course_id; 

13.3. Kondisi Where 

SELECT c.client_id, c.client_name, c.client_profession  

FROM client c  

WHERE c.client_id IN  

    (SELECT cd.client_id  

        FROM client_details cd  

        WHERE cd.client_id < 6  

    AND c.client_name LIKE 'M%'); 

Select Dengan Condition Pada PostgreSQL

 

1. Where

Format :

SELECT column1, column2, ..... columnN   FROM table_name    WHERE [search_condition]    ORDER BY sort_expression 

1.1.  sama dengan ( = )

SELECT first_name,last_name  FROM employee WHERE last_name = 'Tan'; 

1.2. And

SELECT first_name, last_name  FROM   employee  WHERE   first_name = 'John' AND last_name = 'Tan';

1.3. OR

SELECT first_name, last_name  FROM employee  WHERE   first_name = 'John' OR last_name = 'Tan';

1.4. Like 

SELECT first_name,last_name  FROM employee  WHERE last_name LIKE 'Joh%';

1.5.  IN

SELECT first_name,last_name  FROM employee  WHERE last_name IN ('John','Mike'); 

1.6. LENGTH

SELECT last_name, LENGTH(last_name) name_length  FROM employee  

WHERE last_name LIKE '%an' AND LENGTH(last_name) BETWEEN 2 AND 6  

ORDER BY name_length;

1.7. Tidak sama dengan ( <> )

SELECT first_name, last_name, address  FROM employee  WHERE address LIKE 'New%' AND first_name <> 'Oli';

2. Order By

Format :

SELECT column-list    FROM table_name    [WHERE condition]    

[ORDER BY column1, column2, .. columnN] [ASC | DESC];

2.1 Ascending 

SELECT first_name, last_name  FROM employee ORDER BY first_name ASC;

2.2. Desending

SELECT first_name, last_name  FROM employee  ORDER BY first_name desc ;

2.3. Null

ORDER BY sort_expresssion [ASC | DESC] [NULLS FIRST | NULLS LAST] 

Contoh :

SELECT num  FROM demo ORDER BY num NULLS LAST;

SELECT num FROM demo ORDER BY num NULLS FIRST;

3. Group By

Format :

SELECT column-list    FROM table_name    WHERE [conditions ]    

GROUP BY column1, column2....columnN   ORDER BY column1, column2....columnN

Contoh :

SELECT first_name || ' ' || last_name as full_name,  SUM (salary) salary  

FROM employee  INNER JOIN employee_details USING (emp_id)        

GROUP BY full_name ORDER BY salary;  

SELECT first_name,  COUNT (emp_id) FROM employee  GROUP BY first_name;

4. Having

Format :

SELECT column1, aggregate_function (column2)  FROM table1, table2    

WHERE [ conditions ]    GROUP BY column1, column2 HAVING [ conditions ]    

ORDER BY column1, column2 

Contoh :

SELECT emp_id, first_name, SUM (salary)  FROM employee  

GROUP BY first_name, emp_id   HAVING SUM (salary) > 20000  order by first_name DESC;

5. Distinct

Format :  Select Distinct column1  FROM table_name;

Contoh :

SELECT DISTINCT column1, column2  FROM table_name;

SELECT DISTINCT ON (column1) column_alias, column2  

FROM table_name  ORDER BY column1, column2 ;

6. Limit

Format :

SELECT select_list   FROM table_name ORDER BY sort_expression LIMIT row_count 

6.1 skip :

SELECT select_list FROM table_name  LIMIT row_count OFFSET row_to_skip;

6.2. Limit 

SELECT Car_id, Car_name, Body_Style  FROM CAR  ORDER BY Car_id  LIMIT 6;

6.3. OFFSET

SELECT Car_id, Car_name, Body_Style  FROM CAR  ORDER BY Car_id  LIMIT 5 OFFSET 2;

7. Fetch

Format :

OFFSET start { ROW | ROWS }  FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY 

Contoh :

SELECT car_id, car_name, car_model  FROM Car  ORDER BY car_name   FETCH FIRST ROW ONLY;

SELECT car_id, car_name, car_model  FROM Car  ORDER BY car_name   FETCH first 1 ROW ONLY;