MYSQL – Getting Started
01 Jul 2010 Leave a Comment
in Uncategorized Tags: autonumber, constraints, mysql, ri, table
Right,
Enough faffing about! This post will just cover a few key points in getting going with MySQL. I’m going to approach it from the point of view of an oracle developer so some of the terminology might not be spot on.
Basics
First thing’s first, you need to change the root password for your MySQL server. This is pretty simple and can be found easily via googling. For convenience, I’ve replicated this below:
For a fresh install use:
mysqladmin -u root password <NEWPASSWORD>
There are ways of doing this by directly updating system tables but that just sounds a bit dodgy to me with my oracle background.
Once you’ve done this, you should be able to log onto the database server using:
mysql –u root -p
then entering the password selected in the previous step.
At this point you can try:
show databases ;
to see a list of databases on your server (remember the semicolon), the output might look something like this:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
Create a database
We’ll create a new database next, this is as simple as:
create database mydb ;
I dunno about you but I also like to create a user, instead of using the root account, to do my development. It helps identify problems with permissions at an early stage.
I’ve tried a few ways to do this and this one seems to work the best for me:
grant create, select, insert, update, delete, alter, lock tables on mydb.* to ‘myuser’@localhost identified by ‘Pa55word’ ;
If you now quit out of the MySQL command prompt (use \q) you should be able to log in with the new user details, e.g.:
mysql -u myuser -p
and enter the new password when prompted. Now enter:
use mydb
to start working with the new database, not sure if this is mandatory or whether it justs saves on a bit of subsequent typing.
Create some tables
A quick scan of the official documentation shows many options for table creation. I’m going to stick with a good old fashioned create statement that specifies the column names and types and I’ll also cover referential integrity (primary and foreign keys) and constraints (unique keys, permitted values). Ok, let’s create a couple of tables, we’ll leave out RI stuff for now.
create table cust ( id int, name varchar(50));
create table job (id int, notes varchar(200), cust_id int);
Ok, now add primary keys; these will be the id columns on both tables.
alter table cust add primary key(id) ;
alter table job add primary key(id) ;
If you use:
desc cust ;
..or
desc job ;
you’ll see that the key column has been populated with PRI for both id fields on both tables. Now to link the two tables together:
alter table job add constraint foreign key (cust_id) references cust (id) ;
..seems to work! The Key column when describing the job table contains “MUL” which seems to indicate that the column can contain multiple identical values (seems fair enough!).
Finally, let’s try and automatically generate the id column values. In oracle we’d use sequences and triggers but the solution here is more like Microsoft Access autonumbers:
alter table cust modify id int auto_increment ;
alter table job modify id int auto_increment ;
At some point it might be worth looking at using GUIDs instead of a linear sequence as this spreads the rows more evenly across the database and can have some benefits where rows are created by disconnected processes and then synced back to the main database (GUIDs are unique so you’ll never generate duplicates!!).
Let’s give it a quick check:
insert into cust (name) values (’Dave’) ;
insert into cust (name) values (’Tom’) ;
insert into cust (name) values (’Dick’) ;
insert into cust (name) values (’Harry’) ;
select * from cust ;
gives:
+----+-------+
| id | name |
+----+-------+
| 1 | Dave |
| 2 | Tom |
| 3 | Dick |
| 4 | Harry |
+----+-------+
4 rows in set (0.00 sec)
Incidentally, the inserts seem to have been commited as a rollback statement had no effect on the contents of the table after the inserts. I’ll need to look more at building transactions later on.
Let’s now check the referential integrity works:
insert into job (notes, cust_id) values (’Boiler Repair’,1) ;
insert into job (notes, cust_id) values (’Boiler Repair’,5) ;
Curiously, both statements work which is a shame as there’s no cust record with an id of 5!, back to the drawing board!!
Revised create tables
Right, the key to fix this problem appears to be to use the Engine=InnoDB clause when creating tables. I’ve dropped the tables and created a script to recreate them:
use mydb ;
create table cust (
id int auto_increment ,
name varchar(50),
primary key(id))
engine=innodb ;
create table job (
id int auto_increment,
notes varchar(200),
cust_id int,
primary key(id),
foreign key(cust_id) references cust(id))
engine=innodb ;
Now if I try to create a row on the job table that references a non existent customer, I get an error:
mysql> insert into job (notes, cust_id) values ('Fix Boiler',5) ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb/job`, CONSTRAINT `job_ibfk_1` FOREIGN KEY (`cust_id`) REFERENCES `cust` (`id`))
You can specify behavours to follow when a parent row is deleted (e.g. cascade) but I’ve not bothered at this stage.
