Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL (Index (Avoid indexes on (Small tables, Tables with frequent batch…
SQL
Index
Description
An index allows for quick retrieval of information from a table. It is a distinct structure in the database that requires its own disk space and holds a copy of the table data that is to be indexed. It is a reference to the table, so no data is changed.
Syntax
- Create: create index index_name
on table_name (column1, column2, ...);
- Remove: drop index index_name
-
Avoid indexes on
-
- Tables with frequent batch update/insert operations
- Columns that are frequently updated
LIKE operator
-
-
Syntax
select column1, column2, ...
from table_name
where column_name like pattern;
Examples
where customer_name like 'a_%_%'
Finds any values that start with "a" and are at least 3 characters long
-
View
Description
An SQL statement that is stored in the database with an associated name. It is a table of a predefined SQL query.
Syntax
- Create: create view view_name as
select column1, column2...
from table_name
where [condition];
- Query a view: select * from view_name
- Remove: drop view view_name
-
Updating Entries
update table_name
set column1 = value1, column2 = value2, ...
where condition;
-
IN operator
-
Syntax
select column_name(s)
from table_name
where column_name in (value1, value2, ...);
-
BETWEEN operator
Description: Used to select values within a given range. Can be used for numbers, text or dates.
-
Joins
Descripiton: Used to combine rows from multiple tables, based on a related column between them.
Types
Inner Join
-
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
-
min(),max(),avg(),count(),sum()
-