counter customizable free hit

Tuesday, August 23, 2005

DUAL

Most of the time when you are using SQL you will be selecting against a table in your database. But there are the odd occassions where you don't actually want to select something from a table but still display information.

In Oracle this is handled by the DUAL table. DUAL is a single row all purpose table which allows you to issue a select statement on values which are not stored in an actual table. Take for example the sysdate function which returns the current date.

SQL> select sysdate from dual;

SYSDATE
---------
23-AUG-05


MySQL has full support for the DUAL table also.

mysql> select curdate() from dual;
+------------+
| curdate() |
+------------+
| 2005-08-23 |
+------------+
1 row in set (0.00 sec)

However MySQL doesn't really need the DUAL table to return values. You can simply issue a select statement that doesn't have a from clause. It's perfectly legal and in fact the most common way to select values that don't belong in a table.

mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2005-08-23 |
+------------+
1 row in set (0.00 sec)

You can use this method to return information about the MySQL server or perform calculations. It's especially good for testing SQL you are going to use in the select section of an SQL statement.

Warning : It should be noted that you shouldn't issue anything other than select statements against the DUAL table, issuing INSERTS, UPDATES and worst of all DELETES against DUAL can have at least undesirable results.

Wizzy Colours

You may have noticed in today's entry the SQL was shown in red and orange. I just wanted to point out if it wasn't obvious from now on any SQL or Code shown in red relates to Oracle and anything in Orange relaters to MySQL.

Outer Joins

Depending on the amount of experience you have with databases in general it's likely that as an Oracle user you would use the Oracle sytle join syntax. From version 9i Oracle has supported the ANSI join syntax but many users and many examples of Oracle SQL on the web still use the Oracle syntax.

Essentially the Oracle syntax uses the where clause to specifiy the join condition simply by comparing a column in one table with the other.

SQL> select e.emp_id, d.description from emp e, dept d
2 where e.dept_id = d.dept_id;

EMP_ID DESCRIPTION
---------- ------------------------------
1 HR
2 HR
3 IT
4 ADMIN


The good news is that this also works in MySQL. MySQL fully supports ANSI join conditions but it also allows (in part) the joining of tables via the where clause.

mysql> select e.emp_name, d.description from emps e, dept d
-> where e.dept_id = d.dept_id;
+----------+-------------+
| emp_name | description |
+----------+-------------+
| Paul | IT |
| John | HR |
| Alan | IT |
+----------+-------------+
3 rows in set (0.02 sec)


The bad news is that you can't use the Oracle outer join syntax. In a normal join, know as an inner join only rows where the join matches exactly are returned. This means that in our example above if an employee record does not have a dept_id (or at least one which isn't in the dept table) the record will not be displayed. If we want to display all the records in a table regardless of the fact there may not be a match we can use an outer join.

In Oracle to use an outer join we can simply use (+) against the table we are joining against, so for example.

SQL> select e.emp_id, d.description from emp e, dept d
2 where e.dept_id = d.dept_id(+);

EMP_ID DESCRIPTION
---------- ------------------------------
1 HR
2 HR
3 IT
4 ADMIN
5

We can now see a new record that wasn't shown in the first select. In Oracle we can simply move the (+) to either column the join is used against to specify which table to perform the outer join on.

SQL> select e.emp_id, d.description from emp e, dept d
2 where e.dept_id(+) = d.dept_id;

EMP_ID DESCRIPTION
---------- ------------------------------
1 HR
2 HR
3 IT
4 ADMIN
PR

As mentioned MySQL doesn't support (+) for outer joins. You therefore need to use the ANSI join syntax. We won't go into the full syntax here, but to use an outer join you need to use the left outer join keywords between the table definitions. The biggest difference is that the join condition becomes part of the from clause.

mysql> select e.emp_name, d.description
from emps e left outer join dept d
on e.dept_id = d.dept_id;

+----------+-------------+
| emp_name | description |
+----------+-------------+
| Barry | NULL |
| Paul | IT |
| John | HR |
| Alan | IT |
+----------+-------------+
4 rows in set (0.11 sec)

Using the ANSI standard you can change which table is treated as the outer table by changing the LEFT keyword to RIGHT.

select e.emp_name, d.description
from emps e right outer join dept d
on e.dept_id = d.dept_id;

+----------+-------------+
| emp_name | description |
+----------+-------------+
| Paul | IT |
| Alan | IT |
| John | HR |
+----------+-------------+
3 rows in set (0.00 sec)

Friday, August 19, 2005

Metadata (a very brief introduction)

One of the great features of Oracle is the amount of Metadata available. For those that don't know metadata is essentially data about data, in Oracles case this is large number of tables which detail every aspect of the database objects.

In general there are 2 groups, the user tables and the all tables. The user tables give information about the current users database objects and the all tables which detail information for every user (schema).

The metadata is stored in tables, which can be accessed using SQL in the same way your normal tables are accessed.

For example lets say you want to see all of the tables owner by your user.

SQL> select table_name from user_tables where rownum < 10;

TABLE_NAME
------------------------------
ADMIN_CHART
ADMIN_CHART_ITEM
.
.
.
SUBCATEGORY
SYSTEM_PARAMETER
ZENDOR_STOCK_FEED

63 rows selected.

If we want to see all of the tables for all of the users we can use the all_tables view (the metadata tables are infact views on far more complex metadata tables).

SQL> select table_name from all_tables where rownum < 10;

TABLE_NAME
------------------------------
SEG$
CLU$
OBJ$
.
.
.
WISHLIST
WISHLIST_EVENT_NAME
WISHLIST_ITEM

831 rows selected.

So how does MySQL handle metadata? It depends on your version, most people will be running verison 4.1 or under, In these versions metadata is accessed using the show command. The show command consists of 22 different types of information about the database. The full list can be found here.
http://dev.mysql.com/doc/mysql/en/show.html

The biggest difference between show and Oracles approach is that the show command offers very limited selection requirements, it's pretty much all or nothing. In our examples above we limited our selection to show the table name only, but there are over 40 columns we can use. MySQL's show command does not allow use to pick and choose which of the columns we would like to see. There is support for a like keyword on some of the show commands but this is limited to say the table name or session variable name.

To see a list of tables in MySQL we can issue the SHOW TABLES command.

mysql> show tables;
+----------------+
| Tables_in_pers |
+----------------+
| clientdata |
| dept |
.
.
.
| test_letters |
| test_order |
| test_select |
+----------------+
14 rows in set (0.14 sec)

From version 5.0 (not currently a full release) MySQL have introduced the information_schema. This replicates the functionality of the metadata tables of Oracle. MySQL users can now use SQL statements against these new tables to select particular columns, use where clauses and group by. A list of the information schema tables cab be found here.
http://dev.mysql.com/doc/mysql/en/information-schema-tables.html

So we can see a list of tables using the information_schema like so.

mysql> select table_name from information_schema.tables;
+---------------------------------------+
| table_name |
+---------------------------------------+
| SCHEMATA |
| TABLES |
| COLUMNS |
.
.
.
| test_text |
| ts_data |
+---------------------------------------+
63 rows in set (1.74 sec)

Thursday, August 18, 2005

New Blog

Roland Bouman has started a new blog available here.

http://rpbouman.blogspot.com/

Roland is a very active member of the MySQL community especailly in the areas of Stored Procedures, Triggers and Views. He is also written some great work with the new information schema available in version 5 of MySQL. I'll admit to some bias as Roland has worked with me on www.mysqldevelopment.com

You can see some of Rolands work here.
http://mysql.gilfster.com/page.php?parent_id=6&page_id=6.0.1

Tuesday, August 16, 2005

SOUNDEX

If you have read any Oracle SQL Manuals or books you may have come across the SOUNDEX function. The function returns a value based on the phonetic representation of a string you supplied, this can then be used to compare with another word which sounds the same.

Soundex is available in MySQL also and operates in exactly the same way.

mysql> select * from sound_test
where soundex(word) = soundex('to');
+------+
| word |
+------+
| to |
| too |
+------+
2 rows in set (0.00 sec)


However MySQL also offers a much easier way of using and remembering the functionality. All you need to do is use the words sounds like as the comparison to perform the same functionality.

mysql> select * from sound_test
where word sounds like 'to';
+------+
| word |
+------+
| to |
| too |
+------+
2 rows in set (0.05 sec)

Concat

Often when dealing with text in an SQL statements it's desirable to join two or more strings together. For example we might want to join a persons title and surname to produce a suitable heading for an address block.

This joining of words is called concatenation and in Oracle is achieved using the pipe character twice like so ||
 
SQL> select title||' '||surname from person;

TITLE||''||SURNAME
-----------------------------------------
Mr Miller


In MySQL concatenation is achieved using a function called concat. All we need to do is call the concat function from the SQL statement passing in the columns or text we wish to concatenate.

mysql> select concat(title,' ',surname) from person
+---------------------------+
| concat(title,' ',surname) |
+---------------------------+
| Mr Miller |
+---------------------------+
1 row in set (0.02 sec)

However we can use an SQL Server Mode parameter to allow us to use the pipe character as we would in Oracle. SQL Server Modes are settings which change the behaviour of the server for an individual client. To use pipes for concatenation we can use the PIPES_AS_CONCAT option. This is set as follows.
 
set sql_mode = 'PIPES_AS_CONCAT';

So we can see this in action like so.

mysql> select title||' '||surname from person;
+---------------------+
| title||' '||surname |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.02 sec)

mysql> set sql_mode = 'PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

mysql> select title||' '||surname from person;
+---------------------+
| title||' '||surname |
+---------------------+
| Mr Miller |
+---------------------+
1 row in set (0.00 sec)

You may have noticed that the first call using the pipe characters does not cause an error, this is because the double pipe is used as a synonum for OR in MySQL. You should use PIPES_AS_CONCAT with caution for this reason.

Monday, August 15, 2005

CREATE TABLE LIKE

As with Oracle MySQL offers the ability to create a table based on the definition of another. This can be done in two ways, one which will be familiar to Oracle users and another which is only available in MySQL.

The first way is to use a select statement in the create table command like so.

mysql> create table emps2 as select * from emps;
Query OK, 3 rows affected (0.20 sec)
Records: 3 Duplicates: 0 Warnings: 0

This creates a table with the same structure as the table we based the select statement on. We can add or remove columns from the table by explicitly specifying the column names in the select like so.

mysql> create table emps3 as select emp_id, emp_name from emps;
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> desc emps3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_id | int(11) | NO | | 0 | |
| emp_name | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

The good thing about using this method is that it populates the table with the data from the select statement also. This makes it a great way to backup tables before running potentially damaging SQL against them. If you want the new table to be empty once it's created you can simply specify a where clause that returns no rows. Personally I use WHERE 1=2 as unless the rules of mathmatics change will always be false and hence return no rows.

mysql> create table emps4 as select emp_id, emp_name from emps where 1=2;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from emps4;
Empty set (0.00 sec)


One downside to this approach on both MySQL and Oracle is that it doesn't make an exact replica of the table, for example any index against the table will not be receated for the new table.

MySQL however offers a solution to this with the LIKE keyword. This can be used to create a table based on the definition of another. This is done as follows.

mysql> create table emps5 like emps;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from emps5;
Empty set (0.00 sec)

The downside of this method is that the data isn't copied across in the way it is using an SQL statement in the create. But it's easy to do that using a command like follows.

mysql> insert into emps5 select * from emps;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

It's Official.....

...seems some people are actually reading my Blog, it's difficult to know sometimes as there isn't the sort of statistics available for me to find out. However I have received a couple of emails about the blog over the last few days.

One thing I didn't realise is that to leave a comment you need a blogger account, I have changed the setting so you can now leave comments without having to sign up for an account.

I was also asked about a feed, this is available at

http://gilfster.blogspot.com/atom.xml

Friday, August 12, 2005

Sorry

I've been extremely busy this week so the blog hasn't been updated in a few days. If you are following the blog we would love to hear from you.

I'll update the blog over the weekend with 2 new articles.

Wednesday, August 10, 2005

Temporary Tables

In Oracle it's possible to create temporary tables. The name is a little missleading in that the table itself is not temporary but the data inside is. There are 2 basic types of temporary tables, those that clear the information in them apon a commit and those which preserve the rows when a commit takes place. However the data is always cleared from the table when the user ends their session.

The other feature of temporary tables in Oracle is that the data is available to the current session. Therefore 2 users can use the table without fear of causing problems in other sessions.

MySQL also supports temporary tables, but the big difference is that they are temporary. This means that they are only available during the current session, once the session has ended the table is dropped automatically. This means they do not exists as an object in the database and therefore need to be recreated each time they are to be used.

Like Oracle the data contained within them is available to the current session only, however unlike Oracle users can create temporary tables with the same name and a completely different structure.

Tuesday, August 09, 2005

Why switch from Oracle to MySQL at all.

I got an email from Curt Monash, the president of Monash Information Services this morning. He raised a good point, why would you want to switch to MySQL from Oracle? You can check out his blog here http://www.dbms2.com/ which just a quick look will give you an indication on why he's asking.

This site isn't really meant to be a MySQL properganda page. I became interested in MySQL development mainly because I wanted to try and implement something along the lines of Oracle's PL/SQL Web tool kit and that took me along a path of looking at MySQL's new Version 5.0 and it's implementation of Stored Procedures.

But what I did find was that despite the fact Oracle and MySQL are supposed to support ANSI standard SQL there are enough differences to annoy your average Oracle developer. This blog is aimed at those people who have been using Oracle for some time but just want to get to know those little tricks they used with Oracle but in MySQL.

So back to the question of why you might want to switch. The first and most obvious answer is that for most purposes MySQL is free. You can download and install Oracle as a developer with out cost but to use it in a production environment is expensive. Secondly and somewhat related to the first point is that a large majority of basic web hosting packages provide support for MySQL. As a database Oracle and MySQL are pretty comparable, but it's some of the advance features that might make a large corporation go for Oracle, but do you need them. I've worked with Oracle for a number of years and used only a few of the more advanced features, you just don't need them most of the time.

The final thing to say is that the staff at MySQL are extremely responsive both in terms of contact with reagards to bugs and feature requests and also on a personall level. I have written a number of white papers and web content on MySQL 5.0, and without any prompting on my part I have had a lot of communication with MySQL staff on the subject. It seems they appreciate what the community at large is doing and see that as a central part of their future plans.

So in summary, it's free in most cases, it's well supported by web hosting companies, it has all the features you need for a small to medium database (if not a large one) and MySQL are open, friendly and a joy to deal with.

Friday, August 05, 2005

Case Sensitivity in MySQL

Oracle is case sensitive when dealing with equality in select statements. So if we enter names with a capital in the first character position we need to specify the exact case to get a match.

SQL> select * from test_case where name = 'dave';

no rows selected

SQL> select * from test_case where name = 'Dave';

NAME
----------
Dave

However MySQL is case insensitive in the same scenario.

mysql> insert into test_case values ('Dave');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_case where name = 'dave';
+------+
| name |
+------+
| Dave |
+------+
1 row in set (0.03 sec)

To force MySQL to check for an exact case we can use the binary keyword. This is simply placed before the comparison we want to evaluate.

mysql> select * from test_case where binary name = 'dave';
Empty set (0.01 sec)

mysql> select * from test_case where binary name = 'Dave';
+------+
| name |
+------+
| Dave |
+------+
1 row in set (0.00 sec)

However you can make a column case senstive by adding the binary keyword to the create table creation statement.

mysql> create table test_case (name varchar(30) binary);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test_case values ('Dave');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test_case where name = 'dave';
Empty set (0.02 sec)

Thursday, August 04, 2005

Changing the MySQL command line tool

There are a number of GUI's for use with MySQL but being from an Oracle background I use the command line. I think it was Ford Prefect in Hitch Hikers Guide to the Galaxy that said always know where your SQL*Plus is (I think it was Towel actually). This is also true of MySQL which every OS version you're using you should be able to use the MySQL command line in exactly the same way on each machine.

While the command line doesn't offer the same powerfull reporting abilities as SQL*Plus it is possible to change it's behaviour somewhat.

Take for example the SQL*Plus command

SET HEADING OFF

Which turns off column headings during select statement execution like so...

SQL> select 'hello' as test from dual;

TEST
-----
hello

SQL> set heading off
SQL> select 'hello' as test from dual;

hello

SQL>

This can be replicated in mysql using the -N switch when starting the command line tool.

C:\Documents and Settings\Andrew>mysql -u root -p -N
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 5.0.7-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use pers
Database changed
mysql> select * from emps;
+---+------+------+--------+
| 1 | Paul | 1 | 999.99 |
| 2 | John | 2 | 999.99 |
| 3 | Alan | 1 | 999.99 |
+---+------+------+--------+
3 rows in set (0.16 sec)
mysql>

Another common modification people make is to change the command line prompt in SQL*Plus this is done in MySQL using the --prompt switch or simply by using the word prompt on the MySQL command line.

mysql> prompt MyPrompt >
PROMPT set to 'MyPrompt >'
MyPrompt >

However MySQL offers a number of dynamic elements that you can use in the prompt. So for example lets say we wanted to see which database we are connected to we simply use \d when defining out prompt.

mysql> prompt \d >
PROMPT set to '\d >'
pers >

This means we don't need to keep changing the prompt when connecting to different databases, this sort of prompt in SQL*Plus is achieved using a login script, it then means using another script when changing connect, buy in MySQL this is all automatic.

pers >use information_schema
Database changed
information_schema >


There are a number of options that can be used, a full list can be found here...

http://dev.mysql.com/doc/mysql/en/mysql-commands.html

Tuesday, August 02, 2005

Sequences in MySQL

In Oracle sequences are often used to maintain a unique series of numbers for an ID field. Sequences are independant of any table and can be used to keep a value unique across a number of tables. In fact they are not even restricted to use in tables.

MySQL doesn't currently support sequences. However it does have an auto increment value which can be applied to a primary key of a table. This is done during the table creation.

mysql> create table seq_test
(id int primary key auto_increment
,name varchar(30));
Query OK, 0 rows affected (0.06 sec)

As the name suggests the value is assigned automatically, this is in contrast to Oracle where we have to call the nextval function of the sequence to return the value when we need it.

So if we perform an insert against the table but do not specifiy a value for the auto_increment column it's assigned automatically.

mysql> insert into seq_test (name) values ('Dave');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+----+------+
| id | name |
+----+------+
| 1 | Dave |
+----+------+
1 row in set (0.03 sec)

However we can override the assignment using a value of our choosing.

mysql> insert into seq_test (id,name) values (100,'John');
Query OK, 1 row affected (0.05 sec)

mysql> select * from seq_test;
+-----+------+
| id | name |
+-----+------+
| 1 | Dave |
| 100 | John |
+-----+------+
2 rows in set (0.00 sec)

The sequence will then start from this new higher point.

mysql> insert into seq_test (name) values ('Penny');
Query OK, 1 row affected (0.02 sec)

mysql> select * from seq_test;
+-----+-------+
| id | name |
+-----+-------+
| 1 | Dave |
| 100 | John |
| 101 | Penny |
+-----+-------+
3 rows in set (0.00 sec)

We can insert records with values lower than the current highest value but these will be subject to the normal rules for a primary key (no duplicates) and will not effect the next number assigned automatically.

If all of the records are deleted from a table the sequence is not reset.

mysql> delete from seq_test;
Query OK, 3 rows affected (0.01 sec)

mysql> insert into seq_test (name) values ('garry');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+-----+-------+
| id | name |
+-----+-------+
| 103 | garry |
+-----+-------+
1 row in set (0.02 sec)

To do this you need to truncate the table like so.

mysql> truncate table seq_test;
Query OK, 1 row affected (0.03 sec)

mysql> insert into seq_test (name) values ('garry');
Query OK, 1 row affected (0.03 sec)

mysql> select * from seq_test;
+----+-------+
| id | name |
+----+-------+
| 1 | garry |
+----+-------+
1 row in set (0.00 sec)

Alternatively you can reset the sequence using an alter table command.
 
mysql> alter table seq_test auto_increment = 100;
Query OK, 1 row affected (0.27 sec)
Records: 1 Duplicates: 0 Warnings: 0

This method could also be used to assign a higher number to start the sequence rather than starting with 1 by calling the alter table straight after the table creation.

So in summary auto_increment offers a great way of assigning a unique value automatically to a table. However what it doesn't allow when compared with an Oracle Sequence is different increment values, ability to use across a number of tables and the option to assign in a reverse order.

Monday, August 01, 2005

Transactions in MySQL

In Oracle transactions are "turned on" by default, in other words DML statements (update, insert, delete) need to be commited or rolledback explicitly. It's possible to set your client, for example SQL*Plus, to auto commit for you but many Oracle developers (Myself included) are simply used to having to commit and rollback (and many times appreciate the fact when we forget to include a where clause on that delete statements).

One of the great features of MySQL is it's ability to use different storage engines. The defualt storage engine is InnoDB which supports transactions, however the command line client and Query Browser will commit and DML automatically by default.

If you need to use transactions you can do one of the following,

explictily start a transaction using the following command,

mysql > START TRANSACTION;

This will then continue the transaction until a commit or rollback takes place. To start a new transaction you need to issue the start transaction statment again.

If you would rather work in an environment like SQL*Plus where each and every statement needs to commited or rolled back you can use the SET command like so.

mysql > SET AUTOCOMMIT = 0;

A few points to note, when using transactions DDL does not perform a commit in the back ground, this is the case in Oracle. However issuing a lock tables command will issue a commit, even if the table with the changes is not part of the lock tables command.