MySQL

DB basics and built-in functions

VIEW IN MYSQL
Simplify complex
query
A virtual table
Make the
business logic
consistent
Advantages
Add extra
security layers

Enable backward
compatibility
Restrictions on Views
● View processing is not optimized:

a. Not possible to create an index on a view.

b. Indexes can be used for views processed using the merge algorithm. However, a view that is processed with

the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can

be used during generation of the temporary tables).

● Cannot modify a table and select from the same table in a subquery (general principle).

● The same principle also applies if you select from a view that selects from the table, if the view selects from the table

in a subquery and the view is evaluated using the merge algorithm.

● If the view is evaluated using a temporary table, you can select from the table in the view subquery and still modify

that table in the outer query. In this case, the view is stored in a temporary table and thus you are not really selecting

from the table in a subquery and modifying it at the same time. (This is another reason you might wish to force

MySQL to use the temptable algorithm by specifying ALGORITHM = TEMPTABLE in the view definition.)
View Syntax

● MySQL allows you to use the ORDER BY

clause in the SELECT statement but ignores
it if you select from the view with a query
that has its own ORDER BY clause.

● This statement changes the definition of a

view, which must exist.

● DROP VIEW removes one or more views

MySQL CREATE VIEW examples
1) Creating a simple view example
CREATE VIEW statement to create a view that
orderDetails represents total sales per order.
table
MySQL CREATE VIEW examples
We have many ways to create a view

1) Creating a simple view

2) Creating a view based on another view

3) Creating a view with join

4) Creating a view with a subquery

5) Creating a view with explicit view columns

…
MySQL CREATE VIEW examples
2) Creating a view based on another view example

● Create a view called bigSalesOrder based on the salesPerOrder

view to show every sales order whose total is greater than
60,000 as follows:

● Now, we can query the data from the bigSalesOrder view as

follows:
MySQL View Processing Algorithms
• The algorithm determines how MySQL process a view and can take one of three values
MERGE, TEMPTABLE, and UNDEFINE.
MySQL View Processing Algorithms
MERGE

When you query from a MERGE view, MySQL processes the following steps:

● First, merge the input query with the SELECT statement in the view definition into a single
query.
● Then, execute the combined query to return the result set
MySQL View Processing Algorithms

Example:

MySQL performs these steps:

● Convert view name contactPersons to table name customers.

● Convert askterisk (*) to a list column names customerName, firstName, lastName, phone,
which corresponds to customerName, contactFirstName, contactLastName, phone.
● Add the WHERE clause.
MySQL View Processing Algorithms
TEMPTABLE

When you issue a query to a TEMPTABLE view, MySQL performs these steps:

● First, create a temporary table to store the result of the SELECT in the view definition.
● Then, execute the input query against the temporary table.

Because MySQL has to create the temporary table to store the result set and moves the data
from the base tables to the temporary table, the algorithm TEMPTABLE is less efficient than
the MERGE algorithm.

Note that TEMPTABLE views cannot be updatable.

MySQL View Processing Algorithms
UNDEFINED

The UNDEFINED is the default algorithm when you create a view without specifying the
ALGORITHM clause or you explicitly specify ALGORITHM=UNDEFINED.

In addition, when you create a view with ALGORITHM = MERGE and MySQL can only process
the view with a temporary table, MySQL automatically sets the algorithm to UNDEFINED and
generates a warning.

The UNDEFINED allows MySQL to choose either MERGE or TEMPTABLE. And MySQL prefers
MERGE over TEMPTABLE if possible because MERGE is often more efficient than TEMPTABLE.
Creating MySQL Updatable Views
• In MySQL, views are not only query-able but also updatable. It means that you can use the
INSERT or UPDATE statement to insert or update rows of the base table through the
updatable view. In addition, you can use DELETE statement to remove rows of the
underlying table through the view.
• However, to create an updatable view, the SELECT statement that defines the view must
not contain any of the following elements:
● Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
● DISTINCT
● GROUP BY clause.
● HAVING clause.
● UNION or UNION ALL clause.
● Left join or outer join.
● Subquery in the SELECT clause or in the WHERE clause that refers to the table
appeared in the FROM clause.
● Reference to non-updatable view in the FROM clause.
● Reference only to literal values.
● Multiple references to any column of the base table.
Checking updatable view information
SELECT

table_name,

is_updatable

FROM

information_schema.views

WHERE

table_schema = 'db_name';
Index
● MySQL uses indexes to quickly find rows with specific column values. Without an index,
MySQL must scan the whole table to locate the relevant rows. The larger table, the slower
it searches.
● An index is a data structure such as B-Tree that improves the speed of data retrieval on a
table at the cost of additional writes and storage to maintain it.
● When you create a table with a primary key or unique key, MySQL automatically creates a
special index named PRIMARY. This index is called the clustered index.

The PRIMARY index is special because the index itself is stored together with the data in
the same table. The clustered index enforces the order of rows in the table.

● Other indexes other than the PRIMARY index are called secondary indexes or non-
clustered indexes.
MySQL CREATE INDEX statement
● Typically, you create indexes for a table
at the time of creation

● To add an index for a column or a set

of columns, you use the CREATE INDEX
statement as follows:
Storage Engine Allowed Index
Types
● By default, MySQL creates the B-Tree
index if you don’t specify the index InnoDB BTREE

type. The following shows the

permissible index type based on the MyISAM BTREE
storage engine of the table:
● To see how MySQL internally performed this MEMORY/HEAP HASH, BTREE

query, you add the EXPLAIN clause at the

beginning of the SELECT statement
Composite indexes
● MySQL allows you to create a composite index that
consists of up to 16 columns.
● In this syntax, the composite index consists of three
columns c2, c3, and c4.
● Notice that if you have a composite index on
(c1,c2,c3), you will have indexed search capabilities on
one the following column combinations (leftmost
prefix):
MySQL Prefix Index
● In case the columns are the string columns, the index will consume a lot of disk space
and potentially slow down the INSERT operations.

-> Create an index for the leading part of the column values of the string columns:

CREATE INDEX index_name

ON table_name(column_name(length));

● MySQL allows you to optionally create column prefix key parts for CHAR, VARCHAR,
BINARY, and VARBINARY columns. If you create indexes for BLOB and TEXT columns, you
must specify the column prefix key parts.
● For InnoDB tables with REDUNDANT or COMPACT row format, the maximum prefix length
is 767 bytes. However, for the InnoDB tables with DYNAMIC or COMPRESSED row format,
the prefix length is 3,072 bytes. MyISAM tables have the prefix length up to 1,000 bytes.
MySQL Prefix Index
How do you choose the length of the prefix?
1. Find the number of rows in the table:
SELECT
COUNT(*)
FROM
table_name;
2. Evaluate different prefix length until you can achieve the reasonable uniqueness of rows:
SELECT
COUNT(DISTINCT LEFT(column_name, 20)) unique_rows
FROM
table_name;
In this case, 20 is a good prefix length in this case because if we use the first 20 characters of
the column_name for the index, all values are unique.
How do MySQL Indexes work?
● When a table has a primary key or unique constraint, MySQL will cluster the
table based on that primary key or unique constraint. These tend to be
good candidates for the clustered index key as they are highly unique.
● However, when a table is created without a primary key or unique
constraint, a clustered index is still generated behind the scenes for you.
● Even though this table does have a Clustered Index, it is not useful for
querying purposes because the index key is essentially the rowid – which
doesn’t include the column that you query.
How do MySQL Indexes work?
CLUSTER INDEXES
● B-tree indexes can be considered an upside-down tree structure where:
The root page is at the top.
Intermediate-level pages store pointers to additional pages in lower levels in the index.
● Once you get to the leaf level of the index – all the columns in the table are stored in the
B-tree structure. These intermediate level pages and leaf level pages are doubly-linked
lists – they maintain the ordering of the index, so it is possible to easily find values in the
ordered list but also to be able to order the resultset in ascending or descending order
without additional work to provide the sort. There is no separate storage for table data.
How do MySQL Indexes work?
SECONDARY INDEXES
● Secondary indexes are also B-tree indexes and essentially operate similarly to the
clustered index – they’re stored and maintained in sorted order.
● The design is almost the same as the clustered index – with the only difference being
what is stored at the leaf level of the index.
● While the leaf level of the clustered index stores all the column values for the row, the
secondary index leaf level only stores the columns defined in the index (with the left-
most key being the column that orders the data structure) and some pointer back to the
base table. It is also worth noting that pages at the leaf level are also doubly-linked lists,
supporting previous and next page lookups as well as bi-directional sorting.
How do MySQL Indexes work?
● There is a relationship between the
clustered index key and the key of the
secondary indexes.
● Because secondary indexes are copies of
data in the base clustered table, there
must be a way to associate the record in
the secondary index to the full row in the
clustered index. To facilitate this, for each
row in the secondary index, the clustered
key for that row is also stored. This is
important to understand because the
wider the clustered index key is for your
underlying table, the wider your
secondary indexes will be.
Clustered And Non - Clustered Index
Clustered Index Non – Clustered Index
A clustered index Is a table where data for rows The indexes other than primary is non – clustered
are stored. index.
Sort the records and store the index in physical Create logical ordering and use pointer to access
memory physical data files
It stores records in the leaf node of index Does not store record in the leaf node of index
It automatically uses Primary key as clustered Can have one or more than 1 non – clustered
index or NOT NULL and UNIQUE value as index (max is 64)
clustered index (if there is no primary key)
Index - Use cases
• Should not use Index when
• Table with small data
• Working with Insert, update and delete frequently
• Null value (because use IS NULL not WHERE)
• Non – clustered is faster than clustered when working with insert, update and delete.
Non clustered is faster because when insert clustered tend to sort then insert data, which
means as if one row is inserted in the middle then all the remaining row will be moved.
Conclusion of Index
• Using an index in SQL has many advantages like optimized search performance, faster
sorting and grouping of records, and easier maintenance of unique columns.
• It also comes with different drawbacks and downsides, like extra use of disk space and
poor performance in data modification while using INSERT, UPDATE, and DELETE
statements.
• No extra disk space is required for the clustered index, but non clustered index takes extra
space as it stores separately from the table.
MySQL SELECT INTO variable
• In MySQL, SELECT INTO is used to store result query result in one or more variables.
SELECT
c1, c2, c3, ...
INTO
@v1, @v2, @v3,...
FROM
table_name
WHERE
condition;
c1, c2, and c3 are columns or expressions that you want to select and store into the
variables.
@v1, @v2, and @v3 are the variables which store the values from c1, c2 and c3.
• The number of variables must be the same as the number of columns or expressions in the
select list. In addition, the query must returns zero or one row.
MySQL SELECT INTO variable - Example
The following statement causes an error because the query returns multiple rows:
SELECT
creditLimit
INTO
@creditLimit
FROM
customers
WHERE
customerNumber > 103;

Output: Error Code: 1172. Result consisted of more than one row
To fix it, we can use the LIMIT 1 clause as follows:
SELECT
creditLimit
INTO
@creditLimit
FROM
customers
WHERE
customerNumber > 103
LIMIT 1;
MySQL INSERT INTO SELECT
• INSERT INTO SELECT statement to insert data into a table, where data comes from the
result of a SELECT statement.
INSERT INTO table_name(column_list)
SELECT
select_list
FROM
another_table
WHERE
condition;

• The INSERT INTO SELECT statement is very useful when you want to copy data from
other tables to a table or to summary data from multiple tables into a table.
• The INSERT INTO SELECT statement requires that the data types in source and target
tables matches.
MySQL INSERT INTO SELECT - Example
First, create a new table called stats:
CREATE TABLE stats (
totalProduct INT,
totalCustomer INT,
totalOrder INT

);

Second, use the INSERT statement to insert values that come from the SELECT statements:
INSERT INTO stats(totalProduct, totalCustomer, totalOrder)
VALUES(
(SELECT COUNT(*) FROM products),
(SELECT COUNT(*) FROM customers),
(SELECT COUNT(*) FROM orders)
);
Syntax to copy a table

• Copy structure of the table

• Copy table data to another table

• Copy data to another table of different DB

Limit
• Limit the records that want to show

• Limit syntax can be used like this equal to

How limit works
MySQL built-in function

Built-in
function

Date and Advanced

String Numeric Aggregate
time function
String
• CONCAT(expression1, expression2, expression3,...): Adds two or more
expressions together.
• LENGTH(string): Returns the length of a string (in bytes)

• REPLACE(string, substring, new_string):Replaces all occurrences of a substring

within a string, with a new substring

• SUBSTRING(string, start, length): Extracts a substring from a string (starting at

any position)
Numeric
• ABS(number): Returns the absolute value of a number

• CEILING(number): Returns the smallest integer value that is >= to a number

• SQRT(number): Returns the square root of a number

• The MOD() function returns the remainder of a number divided by another number:
MOD(x,y) or x MOD y or x % y

• POW(x, y): Returns the value of a number raised to the power of another number

• TRUNCATE(number, decimals): Truncates a number to the specified number of decimal

places

• Greatest(arg1, arg2, …): return the greatest value of the list of arguments

• Least(arg1, arg2, …): return the least value of the list of arguments
Date and time
• CURDATE(): The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric)

• CURTIME(): The time is returned as "HH-MM-SS" (string) or as [Link] (numeric).

• DATEDIFF(date1, date2): Returns the number of days between two date values (date1 - date2)

• ADDDATE(date, days) || ADDDATE(date, INTERVAL value addunit): Adds a time/date interval to

a date and then returns the date

• PERIOD_ADD(period, number): Adds a specified number of months to a period

period: YYMM or YYYYMM

number: The number of months to add to period. Both positive and negative values are
allowed
Aggregate
• Avg(data/column_name): calculate average value of all records of 1 column

• Sum(data/column_name): calculate sum of 1 column

• Min(data/column_name): find min value of 1 column

• Max(data/column_name): find max value

• Count(data/column_name): Returns the number of records returned by a

select query
Advanced functions
• CAST(value AS datatype): Converts a value (of any type) into a specified
datatype.
• COALESCE(val1, val2, ...., val_n): Returns the first non-null value in a list

• ISNULL(expression): return 1 or 0 depending on expression is null or not.

• IFNULL(exp, alt_value): Return a specified value if the expression is NULL,

otherwise return the expression.

• IF(condition, value_if_true, if_false): Returns a value if a condition is TRUE, or

another value if a condition is FALSE.