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()
-