Relational database systems usually have the same structure. They store data in different databases or schemas, which separate the data from different applications. These schemas are just collections of tables. Tables are definitions of specific data structures and are composed of fields. A field is a basic data type that defines the smallest component of information as though they were the atoms of the data. So, schemas are group of tables that are composed of fields. Let's look at each of these elements.
As defined before, schemas or databases— in MySQL, they are synonyms—are collections of tables with a common context, usually belonging to the same application. Actually, there are no restrictions around this, and you could have several schemas belonging to the same application if needed. However, for small web applications, as it is our case, we will have just one schema.
Your server probably already has some schemas. They usually contain the metadata needed for MySQL in order to operate, and we highly recommend that you do not modify them. Instead, let's just create our own schema. Schemas are quite simple elements, and they only have a mandatory name and an optional charset. The name identifies the schema, and the charset defines which type of codification or "alphabet" the strings should follow. As the default charset is latin1, if you do not need to change it, you do not need to specify it.
Use CREATE SCHEMA followed by the name of the schema in order to create the schema that we will use for our bookstore. The name has to be representative, so let's name it bookstore. Remember to end your line with a semicolon. Take a look at the following:
mysql> CREATE SCHEMA bookstore; Query OK, 1 row affected (0.00 sec)
If you need to remember how a schema was created, you can use SHOW CREATE SCHEMA to see its description, as follows:
mysql> SHOW CREATE SCHEMA bookstore G *************************** 1. row *************************** Database: bookstore Create Database: CREATE DATABASE `bookstore` /*!40100 DEFAULT CHARACTER SET latin1 */ 1 row in set (0.00 sec)
As you can see, we ended the query with G instead of a semicolon. This tells the client to format the response in a different way than the semicolon does. When using a command of the SHOW CREATE family, we recommend that you end it with G to get a better understanding.
Tip
Should you use uppercase or lowercase?
When writing queries, you might note that we used uppercase for keywords and lowercase for identifiers, such as names of schemas. This is just a convention widely used in order to make it clear what is part of SQL and what is your data. However, MySQL keywords are case-insensitive, so you could use any case indistinctively.
All data must belong to a schema. There cannot be data floating around outside all schemas. This way, you cannot do anything unless you specify the schema you want to use. In order to do this, just after starting your client, use the USE keyword followed by the name of the schema. Optionally, you could tell the client which schema to use when connecting to it, as follows:
mysql> USE bookstore; Database changed
If you do not remember what the name of your schema is or want to check which other schemas are in your server, you can run the SHOW SCHEMAS; command to get a list of them, as follows:
mysql> SHOW SCHEMAS; +--------------------+ | Database | +--------------------+ | information_schema | | bookstore | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec)
As in PHP, MySQL also has data types. They are used to define which kind of data a field can contain. As in PHP, MySQL is quite flexible with data types, transforming them from one type to the other if needed. There are quite a few of them, but we will explain the most important ones. We highly recommend that you visit the official documentation related to data types at http://dev.mysql.com/doc/refman/5.7/en/data-types.html if you want to build applications with more complex data structures.
Numeric data can be categorized as integers or decimal numbers. For integers, MySQL uses the INT data type even though there are versions to store smaller numbers, such as TINYINT, SMALLINT, or MEDIUMINT, or bigger numbers, such as BIGINT. The following table shows what the sizes of the different numeric types are, so you can choose which one to use depending on your situation:
|
Type |
Size/precision |
|---|---|
|
|
-128 to 127 |
|
|
-32,768 to 32,767 |
|
|
-8,388,608 to 8,388,607 |
|
|
-2,147,483,648 to 2,147,483,647 |
|
|
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
Numeric types can be defined as signed by default or unsigned; that is, you can allow or not allow them to contain negative values. If a numeric type is defined as UNSIGNED, the range of numbers that it can take is doubled as it does not need to save space for negative numbers.
For decimal numbers we have two types: approximate values, which are faster to process but are not exact sometimes, and exact values that give you exact precision on the decimal value. For approximate values or the floating-point type, we have FLOAT and DOUBLE. For exact values or the fixed-point type we have DECIMAL.
MySQL allows you to specify the number of digits and decimal positions that the number can take. For example, to specify a number that can contains five digits and up to two of them can be decimal, we will use the FLOAT(5,2) notation. This is useful as a constraint, as you will note when we create tables with prices.
Even though there are several data types that allow you to store from single characters to big chunks of text or binary code, it is outside the scope of this chapter. In this section, we will introduce you to three types: CHAR, VARCHAR, and TEXT.
CHAR is a data type that allows you to store an exact number of characters. You need to specify how long the string will be once you define the field, and from this point on, all values for this field have to be of this length. One possible usage in our applications could be when storing the ISBN of the book as we know it is always 13 characters long.
VARCHAR or variable char is a data type that allows you to store strings up to 65,535 characters long. You do not need to specify how long they need to be, and you can insert strings of different lengths without an issue. Of course, the fact that this type is dynamic makes it slower to process compared with the previous one, but after a few times you know how long a string will always be. You could tell MySQL that even if you want to insert strings of different lengths, the maximum length will be a determined number. This will help its performance. For example, names are of different lengths, but you can safely assume that no name will be longer than 64 characters, so your field could be defined as VARCHAR(64).
Finally, TEXT is a data type for really big strings. You could use it if you want to store long comments from users, articles, and so on. As with INT, there are different versions of this data type: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Even if they are very important in almost any web application with user interaction, we will not use them in ours.
In MySQL, you can force a field to have a set of valid values. There are two types of them: ENUM, which allows exactly one of the possible predefined values, and SET, which allows any number of the predefined values.
For example, in our application, we have two types of customers: basic and premium. If we want to store our customers in a database, there is a chance that one of the fields will be the type of customer. As a customer has to be either basic or premium, a good solution would be to define the field as an enum as ENUM("basic", "premium"). In this way, we will make sure that all customers stored in our database will be of a correct type.
Although enums are quite common to use, the use of sets is less widespread. It is usually a better idea to use an extra table to define the values of the list, as you will note when we talk about foreign keys in this chapter.
Date and time types are the most complex data types in MySQL. Even though the idea is simple, there are several functions and edge cases around these types. We cannot go through all of them, so we will just explain the most common uses, which are the ones we will need for our application.
DATE stores dates—that is, a combination of day, month, and year. TIME stores times—that is, a combination of hour, minute, and second. DATETIME are data types for both date and time. For any of these data types, you can provide just a string specifying what the value is, but you need to be careful with the format that you use. Even though you can always specify the format that you are entering the data in, you can just enter the dates or times in the default format—for example, 2014-12-31 for dates, 14:34:50 for time, and 2014-12-31 14:34:50 for the date and time.
A fourth type is TIMESTAMP. This type stores an integer, which is the representation of the seconds from January 1, 1970, which is also known as the Unix timestamp. This is a very useful type as in PHP, it is really easy to get the current Unix timestamp with the now() function, and the format for this data type is always the same, so it is safer to work with it. The downside is that the range of dates that it can represent is limited as compared to other types.
There are some functions that help you manage these types. These functions extract specific parts of the whole value, return the value with a different format, add or subtract dates, and so on. Let's take a look at a short list of them:
Do not worry if you are confused on how to use any of these functions; we will use them during the rest of the book as part of our application. Also, an extensive list of all the types can be found at http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html.
Now that you understand the different types of data that fields can take, it is time to introduce tables. As defined in the Schemas and tables section, a table is a collection of fields that defines a type of information. You could compare it with OOP and think of tables as classes, fields being their properties. Each instance of the class would be a row on the table.
When defining a table, you have to declare the list of fields that the table contains. For each field, you need to specify its name, its type, and some extra information depending on the type of the field. The most common are:
NOT NULL: This is used if the field cannot be null—that is, if it needs a concrete valid value for each row. By default, a field can be null.UNSIGNED: As mentioned earlier, this is used to forbid the use of negative numbers in this field. By default, a numeric field accepts negative numbers.DEFAULT <value>: This defines a default value in case the user does not provide any. Usually, the default value is null if this clause is not specified.
Table definitions also need a name, as with schemas, and some optional attributes. You can define the charset of the table or its engine. Engines can be a quite large topic to cover, but for the scope of this chapter, let's just note that we should use the InnoDB engine if we need strong relationships between tables. For more advanced readers, you can read more about MySQL engines at https://dev.mysql.com/doc/refman/5.0/en/storage-engines.html.
Knowing this, let's try to create a table that will keep our books. The name of the table should be book, as each row will define a book. The fields could have the same properties the Book class has. Let's take a look at how the query to construct the table would look:
mysql> CREATE TABLE book( -> isbn CHAR(13) NOT NULL, -> title VARCHAR(255) NOT NULL, -> author VARCHAR(255) NOT NULL, -> stock SMALLINT UNSIGNED NOT NULL DEFAULT 0, -> price FLOAT UNSIGNED -> ) ENGINE=InnoDb; Query OK, 0 rows affected (0.01 sec)
As you can note, we can add more new lines until we end the query with a semicolon. With this, we can format the query in a way that looks more readable. MySQL will let us know that we are still writing the same query showing the -> prompt. As this table contains five fields, it is very likely that we will need to refresh our minds from time to time as we will forget them. In order to display the structure of the table, you could use the DESC command, as follows:
mysql> DESC book; +--------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+-------+ | isbn | char(13) | NO | | NULL | | | title | varchar(255) | NO | | NULL | | | author | varchar(255) | NO | | NULL | | | stock | smallint(5) unsigned | NO | | 0 | | | price | float unsigned | YES | | NULL | | +--------+----------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
We used SMALLINT for stock as it is very unlikely that we will have more than thousands of copies of the same book. As we know that ISBN is 13 characters long, we enforced this when defining the field. Finally, both stock and price are unsigned as negative values do not make sense. Let's now create our customer table via the following script:
mysql> CREATE TABLE customer( -> id INT UNSIGNED NOT NULL, -> firstname VARCHAR(255) NOT NULL, -> surname VARCHAR(255) NOT NULL, -> email VARCHAR(255) NOT NULL, -> type ENUM('basic', 'premium') -> ) ENGINE=InnoDb; Query OK, 0 rows affected (0.00 sec)
We already anticipated the use of enum for the field type as when designing classes, we could draw a diagram identifying the content of our database. On this, we could show the tables and their fields. Let's take a look at how the diagram of tables would look so far:
Note that even if we create tables similar to our classes, we will not create a table for Person. The reason is that databases store data, and there isn't any data that we could store for this class as the customer table already contains everything we need. Also, sometimes, we may create tables that do not exist as classes on our code, so the class-table relationship is a very flexible one.