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;
