MySQL

F o r D e v e l o p e r s
COURSE MATERIALS

You can access the course materials via this link

[Link]

Made with by 2
DAY 2 CONTENTS
• DML
• DRL
• Joins
• Union
• Sub-queries
• Views
• Transactions

Made with by 63
INSERTING DATA
• Strings should always be enclosed in pairs of single or
double quotation marks in MySQL. Numbers and dates do
not need quotes.
INSERT INTO table [(column1, column2,
column3,...)] VALUES
(value1, value2, value3,...);
INSERT INTO table
set column1 = ’ value1’,
column2 = ’ value2’,
column3 = ’ value3’;

DML Made with by 64

INSERTING DATA
• The INSERT...SELECT syntax is useful for copying rows
from an existing table, or (temporarily) storing a result set
from a query.
INSERT INTO table_name [(column_list)]
query_expression

mysql> INSERT INTO Top10_Cities(ID, Name,

CountryCode)
-> SELECT ID, Name, CountryCode
-> FROM City
-> ORDER BY Population DESC
-> LIMIT 10;

DML Made with by 65

INSERTING DATA
• You can use the MySQL-specific function
LAST_INSERT_ID() to retrieve the first value generated
for an AUTO_INCREMENT column by the last successful
INSERT statement.
SELECT LAST_INSERT_ID();

DML Made with by 66

INSERTING DATA
• In an INSERT statement, if you try to insert a row that
contains a unique index or primary key value that already
exists in the table, you aren’t able to add that row. A
REPLACE statement, however, DELETES the old row and
ADDS the new row
REPLACE INTO table
SET column1=value1, column2=value2;

DML Made with by 67

INSERTING DATA
• The main difference between REPLACE and ON
DUPLICATE KEY UPDATE is that in case of REPLACE, the
new row is added to the table, discarding the old row. In the
case of ON DUPLICATE KEY UPDATE, the old row is
preserved, discarding the new row.
mysql> INSERT INTO current_users (userid,
username)
-> VALUES (100, 'Tobias')
-> ON DUPLICATE KEY UPDATE visits=visits+1;

DML Made with by 68

UPDATING DATA
UPDATE [LOW_PRIORITY] [IGNORE] tablename
SET
column1=expression1,column2=expression2,...
[WHERE condition]
[ORDER BY order_criteria]
[LIMIT number]

DML Made with by 69

DELETING DATA
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM
table
[WHERE condition]
[ORDER BY order_cols]
[LIMIT number]
If you write
delete from table;
on its own, all the rows in a table will be deleted, so be
careful!

DML Made with by 70

TRUNCATE
TRUNCATE [TABLE] table_name;

• TRUNCATE statement starts the AUTO_INCREMENT count over

again, unlike the DELETE statement.
• TRUNCATE is generally faster than using a DELETE statement as
well.
• TRUNCATE is transactional statement.

DML Made with by 71

RETRIEVING DATA
• The basic syntax of the SELECT statement looks like this

• When the keyword DISTINCT is added to the statement, it

eliminates the duplicate rows from the result, returning only
unique rows.

DRL Made with by 72

RETRIEVING DATA

DRL Made with by 73

RETRIEVING DATA

DRL Made with by 74

RETRIEVING DATA
• LIKE uses simple SQL pattern matching. Patterns can
consist of regular text plus the % (percent) character to
indicate a wildcard match to any number of characters and
the _(underscore) character to wildcard-match a single
character.
select *
from table
where condition = exp or condition = exp;

DRL Made with by 75

RETRIEVING DATA
• The ORDER BY clause sorts the rows on one or more of the
columns listed in the SELECT clause. For example,
select name, address
from customers
order by name;

order by name asc;

order by name desc;

DRL Made with by 76

GROUPING AND AGGREGATING DATA
select avg(amount)
from orders;
• The output is something like this:
+-------------+
| avg(amount) |
+-------------+
| 54.985002 |
+-------------+

DRL Made with by 77

GROUPING AND AGGREGATING DATA

DRL Made with by 78

GROUPING AND AGGREGATING DATA
select customerid, avg(amount)
from orders
group by customerid;
• When you use a GROUP BY clause with an aggregate function,
it actually changes the behavior of the function. Instead of giving
an average of the order amounts across the table, this query
gives the average order amount for each customer
+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
| 1 | 49.990002 |
| 2 | 74.980003 |
| 3 | 47.485002 |
+------------+-------------+

DRL Made with by 79

GROUPING AND AGGREGATING DATA
select customerid, avg(amount)
from orders
group by customerid
with rollup ;
• The WITH ROLLUP modifier can be used in the GROUP BY
clause to produce multiple levels of summary values
+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
| 1 | 49.990002 |
| 2 | 74.980003 |
| 3 | 47.485002 |
|NULL | 172.455007 |
+------------+-------------+

DRL Made with by 80

GROUPING AND AGGREGATING DATA
select customerid, avg(amount)
from orders
group by customerid
having avg(amount) > 50;
• Note that the HAVING clause applies to the groups. This
query returns the following output:
+------------+-------------+
| customerid | avg(amount) |
+------------+-------------+
| 2 | 74.980003 |
+------------+-------------+

DRL Made with by 81

CHOOSING WHICH ROWS TO RETURN
select name
from customers
limit 2, 3;
• This query can be read as, “Select name from customers,
and then return 3 rows, starting from row 2 in the output.”
Note that row numbers are zero indexed; that is, the first
row in the output is row number zero.

DRL Made with by 82

DATA FORM MULTIPLE TABLES
• Simple Two-Table Joins, Ex:
select [Link], [Link],
[Link]
from customers, orders
on [Link] = [Link];
where [Link] = ‘Islam Askar’

Joins Made with by 83

DATA FROM MULTIPLE TABLES
• By listing two tables, you also specify a type of join,
possibly without knowing it.
• The comma between the names of the tables is equivalent
to typing INNER JOIN or CROSS JOIN. This is a type of
join sometimes also referred to as a full join, or the
Cartesian product of the tables.
• By adding this join condition to the query, you actually
convert the join to a different type, called an equi-join.

Joins Made with by 84

LEFT & RIGHT OUTER JOIN
• This type of join matches up rows on a specified join
condition between two tables. If no matching row exists in
the right table, a row will be added to the result that
contains NULL values in the right columns.
• Let’s look at an example:
select [Link], [Link],
[Link]
from customers left join orders
on [Link] = [Link];

Joins Made with by 85

SUMMARY OF JOINS

Joins Made with by 86

SUMMARY OF JOINS

Joins Made with by 87

UNIONS
• It is used to combine the results of multiple SELECT
statements in one result set.
• To join two statements in this way, two main conditions
should be obeyed:
• The statements must return the same number of values
• The data types for the returned values must correspond to each
other
SELECT AuthFN, AuthMN, AuthLN
FROM Authors
UNION
SELECT AuthFN, AuthMN, AuthLN
FROM Authors2;

Union Made with by 88

ALTERING TABLES AFTER CREATION
The basic form of this statement is:
ALTER TABLE tablename alteration [,
alteration ...]
ALTER TABLE tbl_name ENGINE = engine_name;

alter table customers

modify name char(70) not null;

TABELS Made with by 89

ALTERING TABLES AFTER CREATION

TABELS Made with by 90

ALTERING TABLES AFTER CREATION

TABELS Made with by 91

RENAMING TABLES
• To rename a table you can use alter:
ALTER TABLE tbl_name RENAME TO new_tbl_name;
• Also you can use rename:
RENAME TABLE old_name TO new_name [,…,…];

TABELS Made with by 92

DROPPING TABLES
• The basic form of this statement to drop table is:
DROP TABLE tbl_name;
DROP TABLE IF EXISTS tbl_name;
• Drop Temporary table:
DROP TEMPORARY TABLE tbl_name;
• To Drop multiple tables:
DROP TABLE tbl_name1, tbl_name2, ... ;

TABELS Made with by 93

SUBQUERIES
• A subquery is a query that is nested inside another query.
• The most common use of subqueries is to use the result of
one query in a comparison in another query.
• Types of subquery:
• Scalar subqueries: the subquery is treated as single value.
• Row subqueries: the subquery is treated as a single row.
• Table subqueries: the subquery is treated as a (readonly) table
that contains zero or more rows
select customerid, amount
from orders
where amount = (select max(amount) from
orders);

Sub-queries Made with by 94

SCALAR SUBQUERIES
• To evaluate a scalar subquery, its query expression is
executed and expected to retrieve at most one row having
exactly one column.
select customerid, amount
from orders
where amount = (select max(amount) from orders);

SELECT Name,
(SELECT COUNT(*) FROM City
WHERE CountryCode = Code) AS Cities,
(SELECT COUNT(*) FROM CountryLanguage
WHERE CountryCode = Code) AS Languages FROM
Country;

Sub-queries Made with by 95

TABLE SUBQUERIES

Sub-queries Made with by 96

TABLE SUBQUERIES
• Using FROM clause
SELECT AVG(cont_sum)
FROM (
SELECT Continent,
SUM(Population) AS cont_sum
FROM Country
GROUP BY Continent
) AS t;

Sub-queries Made with by 97

CORRELATED SUBQUERIES
• In correlated sub-queries, you can use items from the outer
query in the inner query.
• For example,
select isbn, title
from books
where not exists
(select * from order_items where
order_items.isbn=[Link]);

Sub-queries Made with by 98

VIEWS IN MYSQL
• View is a virtual or logical table which is composed of
result set of a SELECT query.
• View is dynamic so it is not related to the physical schema
and it is only stored as view definition.
• When the tables which are the source data of a view
changes; the data in the view change also.
• In some cases, a view is updatable and can be used with
statements such as UPDATE, DELETE, or INSERT to
modify an underlying base table

Views Made with by 99

VIEWS IN MYSQL
• If the caching is enabled the query against view is stored in
the cache.
• It increases the performance of query by pulling data from
the buffering system instead of making calling to hard disk.
• Queries of views in MySQL are processed in two ways:
• MySQL creates a temporary table based on the query which
defined the view and then execute the input query on this table.
• MySQL first combines the input query and query which defined the
view, then MySQL executes this query.

Views Made with by 100

VIEWS CREATION
• General Form:
CREATE [OR REPLACE] [ALGORITHM = {MERGE |
TEMPTABLE | UNDEFINED}]
VIEW [database_name]. [view_name]
AS [SELECT statement]

Views Made with by 101

VIEWS CREATION
• Algorithms: The algorithm attribute allows you to control
which mechanism is used when creating a view
• View name: View name cannot be the same name with
existing tables or other views within a database.
• SELECT statement, you can query any tables or views
existed in the database.
• There are several rules which SELECT statement has to
follow :
• Temporary tables cannot be used.
• View cannot be associated with trigger.

Views Made with by 102

EXAMPLE
CREATE VIEW vwProducts
AS
SELECT productCode, productName, buyPrice
FROM products
WHERE buyPrice >
( SELECT AVG (buyPrice)
FROM products )
ORDER BY buyPrice DESC

Views Made with by 103

EXAMPLE
CREATE VIEW customerOrders
AS SELECT [Link], customerName,
SUM(quantityOrdered * priceEach) total
FROM orderDetails D, orders O , customers C
WHERE orderNumber = [Link]
AND
[Link] = [Link]
GROUP BY [Link] ORDER BY total DESC

Views Made with by 104

UPDATEABLE VIEWS
• To create Updateable View: the SELECT statement which
defines view has to follow several rules as follow:
• It must not reference to more than one table. It means it must not
contain more than one table in FROM clause, other tables in JOIN
statement, or UNION with other tables.
• It must not use GROUP BY or HAVING clause.
• It must not use DISTINCT in the selection list.
• It must not reference to the view that is not updatable
• It must not contain any expression (aggregates, functions,
computed columns…)

Views Made with by 105

UPDATEABLE VIEWS
• Create updatable view:
CREATE VIEW officeInfo
AS SELECT officeCode, phone, city
FROM offices

• Then you can run update statement

UPDATE officeInfo
SET phone = '+33 14 723 5555'
WHERE officeCode = 4

Views Made with by 106

CHANGING VIEWS
• The view can become invalid if a table, view, or column on
which it depends is dropped or altered. To check a view for
problems of this nature
CHECK TABLE view_name\G

• To alter a view use:

ALTER VIEW view_name AS SELECT * FROM
tbl_name;
• To drop a view use:
DROP VIEW [IF EXISTS] view_name, [view_name];
• TO display the create statement of the view:
SHOW CREATE VIEW view_name;

Views Made with by 107

VIEWS META-DATA
• You can use these statements also with the view:
DESCRIBE view_name;
SHOW COLUMNS FROM view_name;

• To show views and tables you can use:

SHOW FULL TABLES FROM db_name;

Views Made with by 108

VIEWS ADVANTAGES
• Simplify complex query
• Limited access data to the specific users.
• Provide extra security.
• Computed column storing.

Views Made with by 109

TRANSACTIONS
• A transaction is a set of SQL statements that execute as a
unit and that can be canceled if necessary. Either all the
statements execute successfully, or none of them have any
effect.
• This is achieved through the use of commit and rollback
capabilities.
• If all of the statements in the transaction succeed, you
commit it to record their effects permanently in the
database. If an error occurs during the transaction, you roll
it back to cancel it. Any statements executed up to that
point within the transaction are undone, leaving the
database in the state it was in prior to the point at which the
transaction began.

Transactions Made with by 110

TRANSACTIONS
• The canonical example of this involves a financial transfer
where money from one account is placed into another
account.
• Suppose that Ahmed writes a check to Islam for $100.00
and Islam cashes the check. Ahmed’s account should be
decremented by $100.00 and Islam’s account incremented
by the same amount:
UPDATE account SET balance = balance - 100
WHERE name = ‘Ahmed';
UPDATE account SET balance = balance + 100
WHERE name = ‘Islam';

Transactions Made with by 111

TRANSACTIONS
• If a crash occurs between the two statements, the operation
is incomplete. Depending on which statement executes
first, Ahmed is $100 short without Islam having been
credited, or Islam is given $100 without Ahmed having been
debited.
• Another use for transactions is to make sure that the rows
involved in an operation are not modified by other clients
while you’re working with them.
• Transactional systems typically are characterized as
providing ACID properties. ACID is an acronym for Atomic,
Consistent, Isolated, and Durable, referring to four
properties that transactions should have:

Transactions Made with by 112

ACID
• Atomicity: The statements transaction consists of form a
logical unit. You can’t have just some of them execute.
• Consistency: The database is consistent before and after
the transaction executes. In other words, the transaction
doesn’t make a mess of your database.
• Isolation: One transaction has no effect on another.
• Durability: When a transaction executes successfully to
completion, its effects are recorded permanently in the
database.

Transactions Made with by 113

PERFORMING A TRANSACTION
• To use transactions, you must use a transactional storage
engine such as InnoDB Engines .MyISAM and MEMORY
will not work.
• By default, MySQL runs in autocommit mode, which
means that changes made by individual statements are
committed to the database immediately to make them
permanent.

Transactions Made with by 114

PERFORMING A TRANSACTION
• To perform transactions explicitly, disable autocommit
mode and then tell MySQL when to commit or roll back
changes.
• One way to perform a transaction is to issue a START
TRANSACTION (or BEGIN) statement to suspend
autocommit mode, execute the statements that make up
the transaction, and end the transaction with a COMMIT
statement to make the changes permanent. If an error
occurs during the transaction, cancel it by issuing a
ROLLBACK statement instead to undo the changes.

Transactions Made with by 115

PERFORMING A TRANSACTION
• The following example illustrates this approach. First,
create a table to use
CREATE TABLE t (name CHAR(20), UNIQUE (name))
ENGINE = InnoDB;
• Then perform the transaction.
START TRANSACTION;
INSERT INTO t SET name = ‘Islam';
INSERT INTO t SET name = ‘Ahmed';
COMMIT;
SELECT * FROM t;

Transactions Made with by 116

PERFORMING A TRANSACTION
• Another way to perform transactions is to manipulate the
autocommit mode directly using SET statements:
SET autocommit = 0;
SET autocommit = 1;
• The effect of any statements that follow becomes part of the
current transaction, which you end by issuing a COMMIT or
ROLLBACK statement to commit or cancel it.
• With this method, autocommit mode remains off until you
turn it back on, so ending one transaction also begins the
next one.
• You can also commit a transaction by re-enabling
autocommit mode.

Transactions Made with by 117

PERFORMING A TRANSACTION
• To see how this approach works, begin with the same table
as for the previous examples:
DROP TABLE t;
CREATE TABLE t (name CHAR(20), UNIQUE (name))
ENGINE = InnoDB;
• Then disable autocommit mode, insert some rows, and
commit the transaction:
SET autocommit = 0;
INSERT INTO t SET name = ‘Islam';
INSERT INTO t SET name = ‘Ahmed';
COMMIT;
SELECT * FROM t;

Transactions Made with by 118

TRANSACTIONS AND DDL
• If you issue any of the following statements while a
transaction is in progress, the server commits the
transaction first before executing the statement:
ALTER TABLE
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE

Transactions Made with by 119

TRANSACTIONS SAVEPOINT
• MySQL enables you to perform a partial rollback of a transaction.
To do this, issue a SAVEPOINT statement within the transaction
to set a [Link] roll back to just that point in the transaction
later, use a ROLLBACK statement that names the savepoint. The
following statements illustrate how this works:
CREATE TABLE t (i INT) ENGINE = InnoDB;
START TRANSACTION;
INSERT INTO t VALUES(1);
SAVEPOINT my_savepoint;
INSERT INTO t VALUES(2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO t VALUES(3);
COMMIT;

Transactions Made with by 120