Answer»
An Index retrieves data much faster than otherwise. Indexes speed up the query/search. A user cannot view an Index. Updating a table with an index takes more time because both table and index have to be updated. The view is a virtual table which takes the result of an SQL query. Users can access a View. They have rows and columns similar to a table.
| COMMAND | FUNCTION | SYNTAX |
|---|
| create index | Creates a new index from an existing table. Allows duplicate values. |
> CREATE INDEX indexname
ON tablename (column1, column2, ...); | | create index unique | Similar to creating an index. But only allows unique values. |
>CREATE UNIQUE INDEX indexname
ON tablename (column1, column2, ...); | | drop index | Deletes an existing index. |
> DROP INDEX INDEXNAME; | | rebuild index | Used to rebuild one or all indexes in a table if corrupted. |
>REINDEX INDEX INDEXNAME; | | create view | Creates a view if it doesn’t exist. |
> CREATE VIEW VIEWNAME AS SELECT COLUMN1,COLUMN2 FROM TABLE WHERE CONDITION; | | update view | Creates or edits an existing view. |
> CREATE OR REPLACE viewname
AS
SELECT COLUMN1,COLUMN2 FROM TABLE WHERE CONDITION; | | rename view | Changes the name of the view. |
> RENAME TABLE VIEWNAME TO NEWVIEWNAME; | | drop view | Deletes an existing view. |
> DROP VIEW VIEWNAME; | | drop views | Deletes multiple views. |
> DROP VIEW VIEW1,VIEW2…; | | show views | Displays all views in a database. |
> SHOW FULL TABLES
[{FROM | IN } databasename]
WHERE table_type = 'VIEW'; |
|
