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’, ‘[email protected]’, ‘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 [email protected] 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.