A little bit more about SQL

scacchicgardenSoftware and s/w Development

Dec 13, 2013 (3 years and 8 months ago)

57 views

A little bit more about SQL


Alternatives to MySQL


Views/Triggers

By Loïs Desplat

Alternatives to MySQL


PostgreSQL


It has more features than
many other DBMS including MySQL and
is completely free thanks to its BSD
license.


SQLite


Small library, meant to be
embedded inside an application without
many features but surprisingly
compliant to the SQL standard (source
under the public domain!)

Differences between DBMS


Even though all three of the mentioned DBMS
(MySQL, PostgreSQL, SQLite) have a strong
adherence to the standard, there are some
differences.


Even though you might have chosen one
DBMS, be aware of the limitations and
differences of the other DBMS so that if you
have to switch, you will have designed your
program to easily switch to another DBMS.

Differences (continued)


Be aware when you use special features
of one DBMS, you will be practically
locked to that DBMS and it might be
very hard to switch to another DBMS.


Thankfully, most DBMS based on SQL
do try very hard to adhere to the
standard and the differences are almost
always very small.

Views


A view is a subset of a table. You can
use it to retrieve and update data or
even delete rows.


You create a view from attributes/tuples
of other tables and from there you can
do almost everything that you can do
with a table.

Creating a view with MySQL

CREATE TABLE t (qty INT, price INT);

INSERT INTO t VALUES(3, 50);

CREATE VIEW v AS SELECT qty, price,
qty*price AS value FROM t;



qty

price

value

3

50

150

Deleting a view


Very simple command


DROP VIEW v;


Changing the view definition


ALTER VIEW v AS SELECT qty, price
FROM t;


Same as CREATE VIEW. It actually
DROPS the view and then creates it
again. It is a shortcut.

Some properties of a view


When you update the tuples inside the
view, they are updated in the table
where you took them from.


So let’s say some crazy CEO decided to
triple the price.


UPDATE v SET price=price*3;

qty

price

value

3

150

150




Did you see that?


Our value attribute didn’t get updated
when the price went up.


Thankfully, triggers are at the rescue or
are they?


Actually they won’t help us here, but
we’ll see why later!

Triggers


A trigger is an object in a database that
is associated with a table and is
activated when a particular event
occurs in the table.


Unfortunately, triggers can only be
associated with a permanent table and
not with a temporary one or a view (at
least in MySQL)

Triggers (continued)


Let’s assume that our view v is now a
permanent table so that we can
associate a trigger with it.


CREATE TRIGGER updatevalue AFTER
UPDATE ON v


FOR EACH ROW BEGIN



UPDATE v SET value=price*qty;


END

Triggers (continued)


So there you go, we can now have our value
attribute updated every single time that the
table changes.


The previous command did not look very
efficient to me (what if you updated only one
tuple.. You don’t need to go through all the
rows).


It appears that for most uses of a trigger you
will need to go through all the rows though.

Triggers


You can also remove a trigger.


DROP TRIGGER updatevalue;



Other DBMS like Oracle and Postgres
seem to have a more advanced trigger
implementation.


All the features shown in this presentation are
only present since the release of MySQL 5.0.10


Other DBMS have had these features for a very
long time including Oracle and Postgres.


SQLite mentioned earlier is, at its name implies,
light. Therefore it has limited support for
Triggers and Views. For example views are read
-
only in sqlite.

Websites


MySQL:
http://www.mysql.com


PostgreSQL:
http://www.postgresql.org


SQLite:
http://www.sqlite.org