Belajar Mysql - Pembuatan Event ( Schedule )

 

Event merupakan pembuatan dan menjadwalkan proses yang akan dilakukan. Ini membutuhkan hak istimewa EVENT untuk skema di mana acara akan dibuat (dan mungkin SUPER bergantung pada nilai DEFINER. Proses tidak akan berjalan kecuali Penjadwal diaktifkan. 

Untuk mengaktifkan supaya event bisa digunakan adalah :

1. Mengaktifkan

SET GLOBAL event_scheduler = ON;

SET @@global.event_scheduler = ON;

SET GLOBAL event_scheduler = 1;

SET @@global.event_scheduler = 1;

2. Men-Aktifkan 

SET GLOBAL event_scheduler = OFF;

SET @@global.event_scheduler = OFF;

SET GLOBAL event_scheduler = 0;

SET @@global.event_scheduler = 0;

1. Membuat Event

Format :

CREATE  [DEFINER = { user | CURRENT_USER }]

    EVENT  [IF NOT EXISTS]

    event_name  ON SCHEDULE schedule

    [ON COMPLETION [NOT] PRESERVE]

    [ENABLE | DISABLE | DISABLE ON SLAVE]

    [COMMENT 'comment']

    DO sql_statement;

schedule:

    AT timestamp [+ INTERVAL interval] ...

  | EVERY interval

    [STARTS timestamp [+ INTERVAL interval] ...]

    [ENDS timestamp [+ INTERVAL interval] ...]

interval:

    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Contoh 1 :

CREATE EVENT evtData     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

    DO  UPDATE myschema.mytable SET mycol = mycol + 1;

Contoh 2 :

CREATE EVENT evtData 

    ON SCHEDULE

      EVERY 6 HOUR

    COMMENT 'A sample comment.'

    DO

      UPDATE myschema.mytable SET mycol = mycol + 1;

2. Mengubah Event

Format :

ALTER  [DEFINER = { user | CURRENT_USER }]

    EVENT event_name

    [ON SCHEDULE schedule]

    [ON COMPLETION [NOT] PRESERVE]

    [RENAME TO new_event_name]

    [ENABLE | DISABLE | DISABLE ON SLAVE]

    [COMMENT 'comment']

    [DO event_body]

Contoh :

2.1. Mengubah jam

ALTER EVENT evtData

    ON SCHEDULE

      EVERY 12 HOUR

    STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;

2.2.Mengubah hari

ALTER TABLE evtData

    ON SCHEDULE

      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY

    DO

      TRUNCATE TABLE myschema.mytable;

2.3. Menonaktifkan

ALTER EVENT myevent

    DISABLE;

2.4. Menganti nama 

ALTER EVENT myevent

    RENAME TO yourevent;

2.5. mengarahkan ke database lain

ALTER EVENT olddb.myevent

    RENAME TO newdb.myevent;

3. Menghapus Event

Format :

DROP EVENT [IF EXISTS] event_name

Contoh :

DROP EVENT evtData;

Belajar Mysql - Pembuatan View, Index

 

1. View

1. 1. Membuat View

Format :

CREATE   [OR REPLACE]

    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    [DEFINER = { user | CURRENT_USER }]

    [SQL SECURITY { DEFINER | INVOKER }]

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]

Contoh :

CREATE VIEW vTblA (mycol) AS SELECT * From TblA;

1.2. Mengubah View

Format :

ALTER

    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    [DEFINER = { user | CURRENT_USER }]

    [SQL SECURITY { DEFINER | INVOKER }]

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]

Contoh :

ALTER VIEW vTblA (mycol) AS SELECT kode, nama From TblA;

1.3. Menghapus View

Format :

DROP VIEW [IF EXISTS]

    view_name [, view_name] ...

    [RESTRICT | CASCADE]

Contoh :

DROP VIEW vTblA;

2.  Index

2.1. Membuat Index

Format :

CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

    [index_type]

    ON tbl_name (index_col_name,...)

    [index_option] ...

index_col_name:

    col_name [(length)] [ASC | DESC]

index_type:

    USING {BTREE | HASH | RTREE}

index_option:

    KEY_BLOCK_SIZE [=] value

  | index_type

  | WITH PARSER parser_name

Contoh :

CREATE TABLE lookup (id INT) ENGINE = MEMORY;

CREATE INDEX idxData USING BTREE ON tblA (id);

2.2.  Menghapus Index

Format :

DROP INDEX index_name ON tbl_name

    [algorithm_option | lock_option] ...

algorithm_option:

    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:

    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Contoh :

DROP INDEX `PRIMARY` ON idxData;

Belajar Mysql - Pembuatan Server

Definisi server digunakan dengan mesin penyimpanan FEDERATED. Pernyataan CREATE SERVER membuat baris baru di dalam tabel server di dalam database mysql. Pernyataan ini membutuhkan hak istimewa SUPER.

Nama server harus menjadi referensi unik ke server. Definisi server bersifat global dalam lingkup server, tidak mungkin untuk memenuhi syarat definisi server ke database tertentu. nama server memiliki panjang maksimum 64 karakter (nama yang lebih panjang dari 64 karakter akan dipotong secara diam-diam), dan tidak peka huruf besar-kecil. Anda dapat menentukan nama sebagai string yang dikutip. Untuk setiap opsi, Anda harus menentukan karakter literal atau numerik literal. Literal karakter adalah UTF-8, mendukung panjang maksimal 64 karakter dan standarnya adalah string kosong (kosong). String literal dipotong secara diam-diam menjadi 64 karakter. Literal numerik harus berupa angka antara 0 dan 9999, nilai defaultnya adalah 0.

1. Membuat Server

Format :

CREATE SERVER server_name

    FOREIGN DATA WRAPPER wrapper_name

    OPTIONS (option [, option] ...)

option:

  { HOST character-literal

  | DATABASE character-literal

  | USER character-literal

  | PASSWORD character-literal

  | SOCKET character-literal

  | OWNER character-literal

  | PORT numeric-literal }

Contoh :

CREATE SERVER sData

FOREIGN DATA WRAPPER mysql

OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'dbData');

CREATE TABLE t (s1 INT) ENGINE=FEDERATED CONNECTION='sData';

2. Mengubah Server

Format :

ALTER SERVER  server_name

    OPTIONS (option [, option] ...)

Contoh :

ALTER SERVER sData OPTIONS (USER 'Remote');

3. Menghapus Server

Format :

DROP SERVER [ IF EXISTS ] server_name

Contoh :

DROP SERVER [ IF EXISTS ] sData'

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;

CRUD Pada PostgreSQL

 

Berikut adalah CRUD  pada PostgreSQL :

1. Insert

Format :

INSERT INTO TABLE_NAME   

(column1,   column2, column3, ……columnN)    VALUES (value1, value2, value3, ….. valueN);

1.1. Insert Single

Format :

INSERT INTO table  (column1, column2, ... )  DEFAULT VALUES;

Contoh :

INSERT INTO myschema."Student"(  

"St_id", "St_Name", "St_age", "St_address", "St_blood_group")  

1.2. Insert Multi Record

Format :

INSERT INTO table_name  (column1, column2, ... )  

SELECT expression1, expression2, ...  FROM source_table  [WHERE conditions];

Contoh :

INSERT INTO myschema."Student"(  

"St_id", "St_Name", "St_age", "St_address", "St_blood_group")  

VALUES(101, 'John', 24, 'New York', 'A+')  

(102, 'Mike', 22, 'Chicago', 'B-'),  

(103, 'Emily', 24, 'Boston', 'A-'),  

(104, 'James', 20, 'Philadelphia', 'O+'),  

(105, 'Sophia', 21, 'New York', 'B+'); 

2. Select

Format : SELECT select_list  FROM table_name;   

Contoh :

SELECT column1, column2,  ……columnN  FROM table_name;

3. Update

Format :

UPDATE table_name    SET column1 = value1, column2 = value2....,   

columnN = valueN   WHERE condition;

Contoh :

UPDATE department  SET last_update = DEFAULT  

WHERE  last_update IS NULL;

4. Delete

Format : 

DELETE FROM table_name   WHERE [condition];

DELETE FROM table USING another_table  WHERE table.id = another_table.id AND

DELETE FROM table  WHERE table.id = (SELECT id FROM another_table);

Contoh :

DELETE FROM department  WHERE dept_id = 6; 

CRUD MongoDB Pada Node.js

 

1. Insert 

Format : (Insert Single record)

1. Insert Single Record

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var myobj = { name: "Kumar", age: "28", address: "India" };  

db.collection("employees").insertOne(myobj, function(err, res) {  

if (err) throw err;  

console.log("1 record inserted");  

db.close();  

});  

});

2. Insert Multi Record

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var myobj = [     

{ name: "Mahesh Sharma", age: "23", address: "Ghaziabad"},  

{ name: "Tom Moody", age: "43", address: "CA"},  

{ name: "Zahira Abdul", age: "18", address: "Islamabad"},  

{ name: "John Ross", age: "33", address: "London"}  

];  

db.collection("customers").insert(myobj, function(err, res) {  

if (err) throw err;  

console.log("Number of records inserted: " + res.insertedCount);  

db.close();  

});  

});

2. Select

2.1. Select Single Record

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

  if (err) throw err;  

  db.collection("employees").findOne({}, function(err, result) {  

    if (err) throw err;  

    console.log(result.name);  

    db.close();  

  });  

});

2.2. Select Multi Record

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

  if (err) throw err;  

  db.collection("employees").find({}).toArray(function(err, result) {  

    if (err) throw err;  

    console.log(result);  

    db.close();  

  });  

});

2.3. Select Condition dengan sama dengan ( = )

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var query = { address: "Delhi" };  

db.collection("employees").find(query).toArray(function(err, result) {  

if (err) throw err;  

console.log(result);  

db.close();  

});  

});

2.4. Select Condition dengan Start / Like

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var query = { address: /^L/ };  

db.collection("employees").find(query).toArray(function(err, result) {  

if (err) throw err;  

console.log(result);  

db.close();  

});  

});

2.5. Sorting /Filter

2.5.1. Sort Ascending

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var mysort = { name: 1 };  

db.collection("employees").find().sort(mysort).toArray(function(err, result) {  

if (err) throw err;  

console.log(result);  

db.close();  

});  

});

2.5.2. Sort Descending

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var mysort = { name: -1 };  

db.collection("employees").find().sort(mysort).toArray(function(err, result) {  

if (err) throw err;  

console.log(result);  

db.close();  

});  

});

3. Remove

var http = require('http');  

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

var myquery = { address: 'London' };  

db.collection("employees").remove(myquery, function(err, obj) {  

if (err) throw err;  

console.log(obj.result.n + " record(s) deleted");  

db.close();  

});  

});

Koneksi Databasse MongoDB Pada Node.js

 

1. Create Connection

1.1. Download MongoDB : apt-get install mongodb

1.2. Install MongoDB :  npm install mongodb --save

1.3. start MongoDb services : service mongodb start 

2. Create Database

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

console.log("Database created!");  

db.close();  

});

3. Create Collection

var MongoClient = require('mongodb').MongoClient;  

var url = "mongodb://localhost:27017/ MongoDb";  

MongoClient.connect(url, function(err, db) {  

if (err) throw err;  

db.createCollection("employees", function(err, res) {  

if (err) throw err;  

console.log("Collection is created!");  

db.close();  

});  

});

CRUD MySQL Pada Node.js

 

1. Insert Record

1.1. Insert Single

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql" 

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

var sql = "Insert Inyo employees (id, name, age, city) Values ('1', 'Ajeet Kumar', '27', 'Allahabad')";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("1 record inserted");  

});

1.2. Insert Multi Record

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

var sql = "INSERT INTO employees (id, name, age, city) VALUES ?";  

var values = [  

['2', 'Bharat Kumar', '45', 'Mumbai'],  

['3', 'John  Tan', '25', ?Las Vegas'],  

['4', 'David Mur', '17', ?CA']  

];  

con.query(sql, [values], function (err, result) {  

if (err) throw err;  

console.log("Number of records inserted: " + result.affectedRows);  

});  

});

2. Delete Record

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

var sql = "DELETE FROM employees WHERE city = 'Malyasia'";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("Number of records deleted: " + result.affectedRows);  

});  

});

3. Select Record

3.1.  Select all data

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

con.query("SELECT * FROM employees", function (err, result) {  

if (err) throw err;  

console.log(result);  

});  

});

3.2  Select  Condition Unique

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

con.query("SELECT * FROM employees WHERE id = '1'", function (err, result) {  

if (err) throw err;  

console.log(result);  

});  

});

3.3. Select  Condition Wildcard

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

con.query("SELECT * FROM employees WHERE city LIKE 'M%'", function (err, result) {  

if (err) throw err;  

console.log(result);  

});  

});

4. Drop Table

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

var sql = "DROP TABLE employee2";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("Table deleted");  

});  

});

Koneksi Databasse MySQL Pada Node.js

 

1. MySQL Create Connection

1.1. Install MySQL Driver : npm install mysql 

1.2. Create Connection : connection.js

var mysql = require('mysql');  

var con = mysql.createConnection({  

  host: "localhost",  

  user: "root",  

  password: "abcd"  

});  

con.connect(function(err) {  

  if (err) throw err;  

  console.log("Connected!");  

});

2. MySQL Create Database

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd"  

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

con.query("CREATE DATABASE dbMysql", function (err, result) {  

if (err) throw err;  

console.log("Database created");  

});  

});

3. MySQL Create Table

3.1. Membuat Table

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "abcd",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

var sql = "CREATE TABLE employees (id INT, name VARCHAR(255), age INT(3), city VARCHAR(255))";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("Table created");  

});  

}); 

3.2. Membuat Table Primary Key

var mysql = require('mysql');  

var con = mysql.createConnection({  

host: "localhost",  

user: "root",  

password: "12345",  

database: "dbMysql"  

});  

con.connect(function(err) {  

if (err) throw err;  

console.log("Connected!");  

var sql = "CREATE TABLE employee2 (id INT PRIMARY KEY, name VARCHAR(255), age INT(3), city VARCHAR(255))";  

con.query(sql, function (err, result) {  

if (err) throw err;  

console.log("Table created");  

});  

});

Line Charts Pada Programming-R

 

Grafik garis memiliki garis yang menghubungkan semua titik dalam diagram.

Untuk membuat garis, gunakan fungsi plot() dan tambahkan parameter tipe dengan nilai "l":

Contoh :

plot(1:10, type="l")

plot(1:10, type="l", col="blue")

plot(1:10, type="l", lwd=2)

Note : lwd adalah ketebalan

parameter :

• 1 is default, while 0.5 means 50% smaller
• 2 means 100% larger

plot(1:10, type="l", lwd=5, lty=3)

note : lty merupakan styles

parameter : 

• 0 removes the line
• 1 displays a solid line
• 2 displays a dashed line
• 3 displays a dotted line
• 4 displays a "dot dashed" line
• 5 displays a "long dashed" line
• 6 displays a "two dashed" line

Multiline

line1 <- c(1,2,3,4,5,10)
line2 <- c(2,5,7,8,9,10)

plot(line1, type = "l", col = "blue")
lines(line2, type="l", col = "red")

Scatterplot Charts Pada Programming-R

 

Scatterplot Charts digunakan untuk menampilkan hubungan antara dua variabel numerik, dan memplot satu titik untuk setiap pengamatan. Dibutuhkan dua vektor dengan panjang yang sama, satu untuk sumbu x (horizontal) dan satu untuk sumbu y (vertikal)

Contoh :

x <- c(5,7,8,7,2,2,9,4,11,12,9,6)
y <- c(99,86,87,88,111,103,87,94,78,77,85,86)

plot(x, y)

x <- c(5,7,8,7,2,2,9,4,11,12,9,6)
y <- c(99,86,87,88,111,103,87,94,78,77,85,86)

plot(x, y, main="Observation", xlab="age", ylab="speed")

# day one, the age and speed of 12 cars:
x1 <- c(5,7,8,7,2,2,9,4,11,12,9,6)
y1 <- c(99,86,87,88,111,103,87,94,78,77,85,86)

# day two, the age and speed of 15 cars:
x2 <- c(2,2,8,1,15,8,12,9,7,3,11,4,7,14,12)
y2 <- c(100,105,84,105,90,99,90,95,94,100,79,112,91,80,85)

plot(x1, y1, main="Observation", xlab="age", ylab="speed", col="red", cex=2)
points(x2, y2, col="blue", cex=2)