What is a database?
A database is a program that allows you to store data. Each flavor of database has specific methods for creating, updating, deleting, and accessing the data. In addition, searches can be performed.
When discussing MySQL, we’re talking about a Relational Database Management System, or RDBMS. We use these systems to store and manage enormous volumes of data. The term relational refers to the way the data is stored and indexed. Data is stored in different tables, and then, using either Primary or Foreign Keys, related to other tables.
A table is simply a collection of specific data, made up of rows and columns. If you can visualize how a spreadsheet looks, you can visualize a table.
Columns are for storing similar types of data, for example, one row of data might have one zip (or postal) code, but this would be stored in a specific column, so that each row could have the same type of data.
A row (or record) makes up a single group of related data. Think about storing an address in a database. You’d have columns in your row for street address, apartment number, city, state and zip. You might have other fields (or columns) depending on whether or not your application is expected to support different countries.
Together these columns, rows and tables form the database. You might have a separate table for people and one for addresses… you could list people in one table, and link (relate) them to an address in another table.
A well built MySQL database will be bound together by indexes. Indexing the data allows searches to happen much more quickly than without indexes.
Adding a user account
While users can be added through the control panel for some database services, this is a good example of utilizing your new database.
Step 1. Log into MySQL using your mysql client, see Connecting above.
Step 2. Use the mysql database, where users are stored.
mysql> use mysql; Database changed
Step 3. Insert a user.
mysql> INSERT INTO user ( host, user, password, select_priv, insert_priv, update_priv) VALUES ( 'your-hostname', 'new-test-user', PASSWORD('battery-correct-horse-staple'), 'Y', 'Y, 'Y' ); Query OK, 1 row affected (0.04 sec)
Step 4. Flush privileges to activate the user.
mysql> FLUSH PRIVILEGES; Query OK, 1 row affected (0.02 sec)
Step 5. Select the user’s record.
mysql> SELECT host, user, password FROM user WHERE user = 'new-test-user'; +---------------------|-------------------|------------------------+ | host | user | password | +---------------------|-------------------|------------------------+ | your-hostname | new-test-user | (password hash) | +---------------------|-------------------|------------------------+ 1 row in set (0.01 sec)
Using the PASSWORD function forces MySQL to encrypt the stored password. What you will see in the SELECT display is the user’s hashed password, the encrypted value, not your original password.
There are also other privileges that can be granted to each user, see the official MySQL documentation for a list of those.
This section will walk you through a list of important MySQL commands you will use periodically.
List the databases currently available:
Select the database to act upon with any follow up commands:
Lists the tables inside the currently selected database:
Show the details of the named table:
SHOW COLUMNS FROM tablename;
Shows the details of the indexes on the named table, including the Primary Key details:
SHOW INDEX FROM tablename;
Creating a table
In order to create a table, you will have to specify at a minimum the table’s name, the name of each field (column) and the field’s type.
Here’s the basic syntax:
CREATE TABLE tablename (columnname columntype);
And here’s a simple example:
mysql> USE databasename; Database changed mysql> CREATE TABLE addresses ( address_id INT NOT NULL AUTO_INCREMENT, address_street VARCHAR(100), address_apt VARCHAR(10), address_city VARCHAR(25), address_state VARCHAR(2), address_zip INT, address_entrydate DATE, PRIMARY_KEY (address_id) ); Query OK, 0 rows affected (0.12 sec)
Setting a primary key enables indexing to work efficiently.
Setting AUTO_INCREMENT tells MySQL to pick the next available number for the address_id field, and using NOT NULL forces that field to have something in it, in this case the id number.
Deleting a table
Deleting tables is even easier. Just remember that data removed is lost permanently and cannot be recovered.
mysql> USE databasename; Database changed mysql> DROP TABLE addresses; Query OK, 0 rows affected (0.63 sec)
There’s more to learn about using MySQL, but these commands should get you started on your journey!