Whether you want to keep a record of patients’ appointments, track profits and
sales for your business, or even store parts of resumes using MySQL, creating
databases and tables are the first step. This tutorial will show you how to make
databases and tables, insert values and load data into them, update entries, and
drop databases and tables.
To start off, create a database.
mysql> CREATE DATABASE mydb;
Now you will need to select this database so that you can use it.
mysql> USE mydb;
That was easy, huh? Things get more complicated when you start making tables,
deciding on their structures and interconnections, and updating and selecting values.
We’ll stick with something simple for now.
mysql > CREATE TABLE resumes (name VARCHAR(30), phone_number
VARCHAR(20), references TEXT, email VARCHAR(30), birth DATE);
This creates a table, ‘resumes,’ with data types VARCHAR, TEXT and
DATE. VARCHAR(number) indicates that a field will hold up to a certain number
of characters, instead of a static value. This is useful for names, phone numbers,
and anything else which may not be uniform in length. TEXT type differs from
VARCHAR because it holds larger lengths of characters. The DATE type stores a
date in the format ‘YYYY-MM-DD.’ If you ever forget what data types your table
stores, you can use the DESCRIBE command.
mysql> DESCRIBE resumes;
There are two ways to populate your table with values. The first is the INSERT
command, which you may already be familiar with.
mysql> INSERT INTO resumes VALUES (‘John Shoe’, ‘1-234-567-8910’,
‘reference 1, reference 2’, ‘myemail@email.com’, ‘1993-01-17’);
Of course, if you have a large data set, you will want to manually enter each entry.
Instead, create a text file with values separated by tabs in the order that you created the
table columns. New lines in the text file represent new rows in the table.
For example, in a text file called ‘person.txt’ you would include the following row.
John Shoe 1-234-567-8910 reference 1, reference 2 myemail@email.com 1993-01-17
Now load the data into the table.
mysql> LOAD DATA LOCAL INFILE ‘/mypath/person.txt’ INTO
TABLE resumes;
Sometimes MySQL is picky about the line endings. If you used a text editor which
terminates lines with \r\n, which is common on Windows editors, use this
command instead.
This changes the phone number of all rows to “new phone number” that have a value
of “John Shoe” in the “name” field.
Finally, you can delete a table or database by using the DROP command. Make sure
that you want to do this before you execute the command because dropping a database
or table will delete all the other entries or tables.
mysql> DROP TABLE resumes;
mysql> DROP DATABASE mydb;
Now you can get started on creating tables and databases for your own business or personal use.
Once you master SQL select statements, you will have most of the basics
of MySQL within your reach.