In our last episode we covered sorting, searching and grouping. We found out that using the COUNT(*) can be problematic when we have unexpected NULL data. Now we look at how to resolve data issues by updating the data, and perhaps even the table schema. We'll use the same database we did for the last episode. You can download it here ».

Updating Data

Let's recap.  If we pull the list of books, and authors, we find that "The Broken Shore" from Peter Temple has no price.  Not that it has a zero price, but it has a NULL value.

mysql> SELECT CONCAT_WS(' ',`author`.`first_name`,`author`.`last_name`) AS `author`,
 `book`.`title`, `book`.`price`
 FROM `author` INNER JOIN `book` ON `author`.`id` = `book`.`author`
 ORDER BY `book`.`price`
 LIMIT 1; …

In our last episode we were able to select some information from our bookshop database, this time we look at putting this into some semblence of order.  To fully investigate this topic we need a few more entries in our database, so rather than detail them here, I've put together this SQL file you can download and build your database to follow along.

To install the database, unpack the SQL file from its ZIP archive, and use the SOURCE command to pull the data into your database:

SOURCE mysql101_bookshop_20110912.sql;

You can also pass the file to the mysql command line interpreter from the shell:

mysql -uroot -p bookshop < …

In our last episode we started building up our online bookshop database, with tables for publishers, authors, formats and books.  At the moment we only have one book in there, so before we go too far, lets add a few more:

INSERT INTO `book` VALUES
 ( NULL, 'The Big Score', 2, 4, 1, '2007-01-01',  9781741752236, 29.95 ),
 ( NULL, 'Split', 3, 2, 1, '2003-01-01', 0732268133, 29.95 );

So what is this NULL thing, and why have I used it?  If you remember we set the first field to an auto_increment id.  Because we don't want to supply a value for this, but let the database create the next value, we need to give a value that indicates we want this to happen.  For this instance, NULL is the value to use.  We must supply a value because we didn't restrict our insert by supplying a …

In our last episode we found out how to connect to a MySQL server.  This time we learn how to lay out a database and start creating it. For this, and following episodes, we will be looking at creating a database to support an online bookshop.

Creating the database

Using the mysql command line client, you can connect to the server and then create the database.  We need a name for the database, and in this case we'll call it 'bookshop'.   We'll also create a user who is specifically allowed to add and update the database, but not alter its structure:

mysql> CREATE DATABASE `bookshop`;
Query OK, 1 row affected (0.01 sec)

mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON `bookshop`.* to 'bookuser'@'localhost' identified by 'bookpass';
Query OK, 0 rows affected (0.00 sec) …

In our last episode we looked at getting MySQL, today we will be looking at how you connect to a MySQL server and what that implies.

Connection basics

Before a client can connect to a MySQL server it needs a path by which that connection can be made. One method that is almost universal is the 'socket'.  As its name implies it is a way of plugging two (or more) applications together.  Sockets can either be end points for a network connection (for instance a TCP socket) or can use the same system-level functions but use a local connection.  This is sometimes called a UNIX socket, and relies on there being a special file that the two applications can use to initiate a connection.  MySQL can use both.

For a network connection you need a number of pieces of information.  As a connection …

For those that weren’t able to attend the fantastic NoSQL Now Conference in San Jose last week, but are still interested in the slides about how people are doing Ad Hoc analytics on top of NoSQL data systems, here’s my slides from my presentation:

No sql now2011_review_of_adhoc_architectures View more presentations from ngoodman We obviously continue to hear from our community that LucidDB is a great solution sitting in front of a Big Data/NoSQL system. Allowing easy SQL access (including super fast, analytic database cached views) is a big win for reducing …

This is the first in a series of posts on MySQL® for those new to the database, or those migrating from another DBMS.

So you've made the decision to try MySQL.  Now you just have to get it installed. Luckily for most purposes MySQL is quite often already available.  If you have a Linux installation then chances are that both the server and client are installed.  If you are planning on using MySQL for your website, chances are the hosting provider gives you several MySQL databases for your use.

Before diving in too deep though, let's get some background and terminology out of the way.

What is MySQL?

This depends.  MySQL was a company, is a trademark, is an ecosystem and is the name of arguably the most popular relational database management system (RDBMS) on the planet.  Originally developed by MySQL AB, MySQL (both the database and the trademark) are now owned by Oracle Corporation. But …

A friend recently bought a GM car. I proceeded to inform him that I am shorting GM stock (technically a put option). He was shocked. “But they make great cars,” he exclaimed. I responded, “I’m not shorting the cars, I’m shorting the company.” Why am I recounting this exchange? Because I believe that the new wave of NoSQL companies—as opposed to the rebranded ODBMS—presents the same situation. I am long the products, but short the companies.
Let me explain. NoSQL companies have built some very cool products that solve real business problems. The challenge is that they are all open source products serving niche markets. They have customer funnels that are simply too small to sustain the companies given their low conversion/monetization rates.
These companies could certainly be tasty acquisition targets for companies that actually make money. But as standalone companies, sadly, I would short them. On that note, I am off to …

Linuxcon was a celebration of twenty years of Linux. I remember colleagues telling me to try the funny little operating system for PCs instead of a BSD variant, their excitement, and making distro ‘floppies’ for . The time has flown, Linux has become a core infrastructure for IT, and many technologies built upon upon Linux has flourished. But could it be better? At least for the database world, things could be better.

Databases are a ‘full body workout’ for Linux according to Josh Berkus. The CEO of PostgreSQL Experts had a session and a BOF at Linuxcon. The goals of these session was to make Linux developers aware of some of the special problems for databases and to get some hints of what database developers can do to better exploit Linux. Linux is the overwhelming OS choice for both the multi-process databases (PostgreSQL and Oracle) and the …

I will be presenting The Care and Feeding of a MySQL Database for Linux System Administrators at Linuxcon. This is a short talk on what Linux Admins can do to get their MySQL instances performing properly, where to budget your server money, and some other tips to make life easier. It can not turn a good system admin into a good DBA no more than a 40 minute talk on being a Linux Admin will make a DBA a good Linux Admin. But it is a good overview of the subject. And for the sake of the environment, I will be recycling all my old jokes again. So see you Friday, 3:00 in Plaza B in the Hyatt Regency in Vancouver.

…