MySQL is the world’s most popular open source database and powers all kinds of different applications in the technology industry. Of course the blogging platform WordPress uses MySQL as it’s database technology, but MySQL goes far beyond just basic blog applications. Some of the biggest names in technology today make use of MySQL, including Twitter, Facebook, Etsy, and more. We also love working with MySQL when writing PHP either natively, or with a great framework like Laravel or Codeigniter. These frameworks do a great job of hiding away the details of how MySQL is working behind the scenes. While this is great, it is really important to have a good grasp of the fundamentals. This series of articles will do just that, dig into the nuts and bolts of what MySQL is, how it works, and how we can leverage its power.
Working with MySQL at the command line
While applications like phpmyadmin, MySQL Workbench, and sequel pro are truly excellent tools for working with MySQL, the best way to really dig into the technology is to explore everything it has to offer right at the command line. Let’s have a look at our first command.
show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | authdemo | | blog | | laratut | | laravelblog | | mysql | | performance_schema | | repotut | | test | | wordpress | +--------------------+ 10 rows in set (0.00 sec)
Congratulations! You just ran the first command of MySQL at the command line. Consider it a hello world of sorts. In our output, we can see there are a bunch of databases already present in this installation. Not to worry, we are going to be creating new databases and tables from scratch.
Getting Your Hands Dirty With MySQL
The best option for learning MySQL is to of course have it installed on your local machine. Whether you are on Mac, Linux, or Windows, there is an option for you. The example listed above was at the command line of a local installation using wamp. For example if you are accessing from a wamp install, you would click on the wamp task tray icon, then go to MySQL->MySQL Console
Fantastic! You’re ready to start working with MySQL.
What if I don’t have MySQL Installed?
Like we said, it really is best if you install MySQL locally on your machine to work along with the examples. In the rare instance that you don’t, you can still practice the MySQL syntax online. In fact some of these tools will prove useful, even if you do have MySQL installed locally but would like more options. You might be familiar with sites like https://jsfiddle.net/ where you can quickly mock up some HTML and JavaScript to get nice examples of code. You get to skip the whole process of booting up you local server, opening your IDE and so on. Well did you know there is a great tool to do this as well with MySQL? Behold, I give you…
http://sqlfiddle.com/
At this handy little website you can choose what type of database you’d like to work with, you are not limited to just MySQL. For our purposes however, we will be using MySQL. To get started, you can click ‘Build Schema’to get a session ready. You can then simply enter your commands in the window on the right, then click ‘Run SQL’.
Nice! Plus one of the design of the site as well. They’re using the Bootstrap Framework which we are also huge fans of.
http://demo.phpmyadmin.net/master-config/
Another option for working with MySQL if you do not have it installed locally is to visit the demo page at the phpmyadmin website. Here you can use the SQL tab to run MySQL commands all day long if you like. Once you test it out, if you like the UI, you can easily download a copy for use in your own environment.
Database Terminology and High Level Overview
When we think of a database, one analogy is to use the database vs spreadsheet comparison. While they are definitely not the same thing, they do share some similarities. Let’s examine.
Similarities of Databases to Spreadsheets
- Data is stored in Columns and Rows
- Both can calculate new data based on existing data
- Both have Multi User Capabilities
Characteristics of Spreadsheets
Data is stored in Cells, which can also have built in calculation or formatting information. You may be familiar with the fact that with a spreadsheet, there can be all kinds of markup which decorates the data in various ways. Because of this reason, spreadsheets are a meeting room favorite among the business world. In addition, any time you make an operation on data within a spreadsheet, that operation creates more data. Because of this, you will find that you can run into unwanted bloat as you add more and more calculations.
Characteristics of Databases
Databases on the other hand store their information in what we call a Record. The database will store only the data and values. All formatting and calculations are applied later. The analogy to MVC or model, view, controller kind of applies here in that just like in web development where we try to separate out logic from markup, it makes sense as well to keep formatting and actual data separated with our databases. With databases, when you perform calculations, sorting and so on, these data operations take place only after the data has been retrieved. This allows you to display only the result of said calculations, rather than having to store the result as well. A huge benefit of working with a database is that you can eliminate duplicate information.
Databases are Efficient Mechanisms of Storage
In summary, we can now see why it is great to use databases for the persistence layer in our web applications. Databases allow us to specify a type for our data as well as providing the ability to eliminate redundancy and formatting differences. Databases are also less error prone, and are not effected by users performing various sorting on the data which might break data associations within a spreadsheet for example. There is a fair amount of effort involved in truly understanding how databases work, but it is well worth the effort.
Flat Files and Relational Databases
Believe it or not, flat files are a very efficient means of storing and accessing data. Flat files can easily show and manipulate data, and they also have the added benefit that stored data does not change when calculations are done after data retrieval. Flat files also have powerful approaches to deal with filtering and matching operations. Text files however do not have data types. You might use something like a tab character, or comma to separate fields. This is fine, yet it puts the burden of enforcing the distinction between fields squarely on your shoulders, and who wants that? Text file and Flat file are basically the same thing, just a different way of defining them.
Databases on the other hand provide the ability to assign a type to any data you store within it. The idea of data types in MySQL is very important, especially when we are using a programming language to interact with our database. You may want to store real numbers, integers, dates, times, and so on. Databases give you this very thing. In addition, when data is stored in the database, all data is kept separated from display. This helps to eliminate duplicate information and data bloat. Flat files are great for some simple applications but databases are the more powerful tool to employ when you need to have greater control over your data.
Database Normalization
When working with databases, you’ll often run into the term, Normalization. More specifically, we’re talking about how to organize fields and tables within the database. For example, which fields should belong to which tables? Database normalization is the process of deciding how to organize the data in order to make querying the database at a later time more efficient. The goal is to organize the tables and fields in the database to reduce as much as possible any dependency or redundancy. The objective is to isolate data so that any additions, deletions, or modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships. This is part of the reason for calling it a relational database.
Forms of Normalization
Third normal form, or 3NF, is the standard to which database design best practices adhere to. Third normal form includes the First Normal Form as well as the Second Normal Form. In order to understand 3NF, we’ll have to first observe 1NF and 2NF.
First Normal Form: First normal form is the idea that there is a single value for each field.
Second Normal Form: The table must first be in 1NF, as well as the fact that all of the information in the table is dependent on what defines the row.
Third Normal Form: The table must first be in 1NF, and 2NF. All of the non defining fields must be directly dependent on the defining fields.
Beyond the academic definitions of the various forms of normalization, is a key concept to take away. When designing your database schema, you want to keep your tables as concise as you possibly can, and may need to break apart tables that have too many fields into two or more tables with a relation defined. This is the tricky part of learning the ins and outs of a relational database but once we have a good understanding of database normalization, we’ll be on our way to better database design.
Acronyms upon Acronyms
As if you didn’t have enough acronyms to keep up with in your day to day life as a web developer, we’ll be adding a few more to your toolkit for working with databases. Here are the most common you’ll come across.
- DB: Database
- RDBMS: Relational Database Management System – A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd of IBM’s San Jose Research Laboratory.
- SQL: Structured Query Language – SQL is a special-purpose programming language designed for managing data held in a relational database management system.
- DDL: Data Definition Language – A data definition language or data description language is a syntax similar to a computer programming language for defining database schemas.
- DML: Data Manipulation Language – is similar to a computer programming language used for selecting, inserting, deleting and updating data in a database.
- OLAP: Online Analytical Processing – is an approach to answering multi-dimensional analytical queries quickly.
- OLTP: Online Transactional Processing – a class of IT applications that facilitates transaction-oriented systems, usually for data entry and retrieval transaction processing.
- CRUD: Create Read Update Delete – Perhaps the most common acronym of them all which stands for Create, Read, Update, and Delete.
Kicking the MySQL Tires
You now have a rudimentary grasp of the high level overview of database technology. Now we can jump in to actually using MySQL from the command line. The first thing we’ll do is to take a look at the information_schema database. Let’s take a look at the tables in this database.
mysql> show tables from information_schema;
+---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | OPTIMIZER_TRACE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLESPACES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | | INNODB_LOCKS | | INNODB_TRX | | INNODB_SYS_DATAFILES | | INNODB_LOCK_WAITS | | INNODB_SYS_TABLESTATS | | INNODB_CMP | | INNODB_METRICS | | INNODB_CMP_RESET | | INNODB_CMP_PER_INDEX | | INNODB_CMPMEM_RESET | | INNODB_FT_DELETED | | INNODB_BUFFER_PAGE_LRU | | INNODB_SYS_FOREIGN | | INNODB_SYS_COLUMNS | | INNODB_SYS_INDEXES | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_SYS_FIELDS | | INNODB_CMP_PER_INDEX_RESET | | INNODB_BUFFER_PAGE | | INNODB_CMPMEM | | INNODB_FT_INDEX_TABLE | | INNODB_FT_BEING_DELETED | | INNODB_SYS_TABLESPACES | | INNODB_FT_INDEX_CACHE | | INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_TABLES | | INNODB_BUFFER_POOL_STATS | | INNODB_FT_CONFIG | +---------------------------------------+ 59 rows in set (0.03 sec)
Very slick! You’ll notice we had to specify what database to view tables from. Let’s fix that by changing the database to use.
mysql> use information_schema;
Database changed
mysql> show tables;
This will give us the same result, but now we don’t have to specify which database we’re trying to view tables on. The reason for this is because we switched databases with the use command.
We can drill down further and view columns on various tables from within the database. Let’s see how.
mysql> show columns from schemata;
+----------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------------+--------------+------+-----+---------+-------+ | CATALOG_NAME | varchar(512) | NO | | | | | SCHEMA_NAME | varchar(64) | NO | | | | | DEFAULT_CHARACTER_SET_NAME | varchar(32) | NO | | | | | DEFAULT_COLLATION_NAME | varchar(32) | NO | | | | | SQL_PATH | varchar(512) | YES | | NULL | | +----------------------------+--------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
Using Dot Notation
MySQL also has the ability to use dot notation so that you can view tables that are not part of the currently in use database. This is how to do this.
mysql> use mysql
Database changed
mysql> show columns from information_schema.schemata;
This will produce the same output as the prior example, but do note that first we changed the default database from information_schema to mysql. From there we were still able to get information about information_schema by using the dot notation to reference it.
Getting Help with MySQL
If you’re at the command line and you run into trouble, your first thought might be to hit that Google Search box. That’s not a bad idea actually. Built in to MySQL however is a great help system that you have access to. Let’s test it out.
Let’s say you need help with the SHOW command:
mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
LIKE 'pattern'
| WHERE expr
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.6/en/extended-show.html.
URL: http://dev.mysql.com/doc/refman/5.6/en/show.html
As we can see here, you are greeted with a wealth of great information relating to the show command. It gives you a nice overview along with detailed examples. Finally, at the end of this output, they provide a link for you to go right to the point in the official documentation for more information. Very nice.
MySQL Introduction Conclusion
This has been a great introduction to database technology as well as MySQL. We’ll be digging in much further as we move through this web series to make sure our database interaction skill are up to speed. When we’re writing native PHP or using frameworks to provide a persistence layer to our application, if we don’t have knowledge of the fundamentals that our application is effecting, we could run into some hiccups.
