A little bit more about SQL

1
A little bit more about SQL

• Alternatives to MySQL
• Views/Triggers

By Loïs Desplat
2
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!)

3
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.

4
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.

5
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.

6
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, qtyprice AS
  value FROM t

qty price value
3 50 150
7
Deleting a view

• Very simple command
• DROP VIEW v

8
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.

9
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 lets say some crazy CEO decided to triple the
  price.
• UPDATE v SET priceprice3

qty price value
3 150 150
10

• Did you see that?
• Our value attribute didnt get updated when the
  price went up.
• Thankfully, triggers are at the rescue or are
  they?
• Actually they wont help us here, but well see
  why later!

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

12
Triggers (continued)

• Lets 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 valuepriceqty
• END

13
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
  dont 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.

14
Triggers

• You can also remove a trigger.
• DROP TRIGGER updatevalue
• Other DBMS like Oracle and Postgres seem to have
  a more advanced trigger implementation.

15

• 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.

16
Websites

• MySQL http//www.mysql.com
• PostgreSQL http//www.postgresql.org
• SQLite http//www.sqlite.org