Little Girl's Mostly Linux Blog

DatabaseManagement

Use the command line to administer your MySQL database

This page was last updated on June 13, 2009.

Table of Contents

Introduction

  • This document is intended to serve as a cheat sheet or reference guide for the administration, structure, and use of a MySQL database. It’s mostly in alphabetical order, so you can quickly look up the steps to accomplish specific tasks.
  • The examples were done on Ubuntu Server 8.04 running in VirtualBox 1.5.0_OSE with MySQL 5.0.51a-3ubuntu5.1 installed. You’re not required to have the same setup, but since this guide came about as a natural continuation of the two previous guides, How to install Ubuntu Server 8.04 in VirtualBox under Kubuntu and Yes, but what do I do now that Ubuntu Server 8.04 is installed in VirtualBox?, the instructions often include which machine (guest, host or remote) the commands should be executed on.
  • Disclaimer: When showing example tables in this document, the line showing the number of rows in a set and how long it took the server to select them has usually been removed from the examples since the tables themselves are the focus.
  • Disclaimer: It’s an accepted convention to use upper case when typing MySQL keywords. I do not follow this convention. If you feel strongly about it, feel free to change the keywords in any of the commands in this guide to upper case when using them.
  • If you’ve never worked with MySQL before, the manual at http://dev.mysql.com/doc/ has an excellent tutorial for learning MySQL. I highly recommend that you go there first and complete the tutorial.

Acknowledgements

I would like to thank Frank Pirrone for starting me on this journey of exploration.

Requirements

  • MySQL Server
  • An understanding of the terms used in this document. Please take a look at the Terminology section.

How To Use This Document

  • Make sure you meet the requirements in the Requirements section of this document.
  • I highly recommend that you create a copy of the example database on your MySQL server so you can safely try out the commands and see for yourself how they work. The steps for creating it can be found here.
  • Refer to the Further Information section for links to some pages on the internet that provide more detailed information than is available here.
  • Refer to the Questions And Answers section for the answers to questions that commonly come up.
  • Refer to the Troubleshooting section if you’ve followed the instructions on this page and you need to figure out why it’s not working.
  • Refer to the Getting Help section if you need more interactive help.

Log On And Off

  • This section contains instructions on how to log onto and off of your MySQL database from the guest machine, from the host machine, or from a remote computer.
  • Instructions are included for advanced users as well.
IMPORTANT
User names are case sensitive.

Log on from the guest machine

  1. Type this command at a command prompt or in a terminal window on the guest machine:
  2. mysql -u USERNAME -p

    Replace:

    • USERNAME with your username.

  3. Provide your MySQL password.

Log on from the host machine or a remote computer

  1. Type this command at a command prompt or in a terminal window on the host machine or a remote computer to log onto the guest machine:
    ssh USERNAME@IP

    Replace:

    • USERNAME with your username.
    • IP with the IP of the MySQL server.
  2. Provide the password you use on the server.
  3. Type this command to log onto the MySQL server:
    mysql -u USERNAME -p

    Replace:

    • USERNAME with your MySQL username.
  4. Provide your MySQL password.

Log on (for advanced users)

Advanced users might like to open port 3306 (the default MySQL port) in the router. Once the port is open, you can use one of the following commands to log onto your database from a remote computer as long as the computer you’re on has the MySQL client installed on it.

  • Use this command if the username you’re currently logged on as is the same as your MySQL username:
  • mysql -h IP -p

    Replace:

    • IP with the IP of the MySQL server.

  • Use this command if the username you’re currently logged on as is different from your MySQL username:
  • mysql -h IP -u USERNAME -p

    Replace:

    • USERNAME with your MySQL username.

  • If you’d like to circumvent the additional password prompt, you can add your password after the -p password argument, but this is not recommended, for security reasons. Note that there is no space between the -p and the password you enter:
  • mysql -h IP -u USERNAME -pPASSWORD

    Replace:

    • USERNAME with your MySQL username.
    • PASSWORD with your MySQL password.

  • If you’d like to log on and immediately be using a specific database, you can use this command:
  • mysql -h IP -u USERNAME -p DATABASENAME

    Replace:

    • USERNAME with your MySQL username.
    • DATABASENEME with the name of the database you’d like to use.

Log off

Regardless of which of the above methods you used to log onto your database, the method for logging off is always the same. Type this command:
exit

User Management

  • There are two kinds of access for users – local and remote.
  • A user can have just local access, just remote access, or local and remote access.
  • Follow all the steps for each kind of access you’d like a user to have.

Add a local user with full administrative privileges on all databases

These steps will let you add a “super” user who has full administrative rights on all databases and tables, and the power to grant rights to others. This user can log on from the guest machine. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to all databases from the guest machine:
  3. grant all privileges on *.* to 'USERNAME'@'localhost' identified by 'PASSWORD' with grant option;

    Replace:

    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.

  4. Type this command to apply the changes without having to restart the server:
  5. flush privileges;


Example:

Let’s say I want to add Mel as a local user and give him full administrative privileges so he can share the work of maintaining my MySQL server with me. I will be assigning spearmint as his password. I would type this command to give him full local administrative rights:

grant all privileges on *.* to 'Mel'@'localhost' identified by 'spearmint' with grant option;

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

Add a local user with full administrative privileges on a specific database

These steps will let you add a “super” user who has full administrative rights on all tables. This user can log on from the guest machine. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to a specific database from the guest machine:
    grant all privileges on DATABASENAME.* to 'USERNAME'@'localhost' identified by 'PASSWORD';

    Replace:

    • DATABASENAME with the name of the database.
    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a local user and give him full administrative privileges so he can share the work of maintaining all of the tables in the People database on my MySQL server with me. I will be assigning spearmint as his password. I would type this command to give him full local administrative rights:

grant all privileges on People.* to 'Mel'@'localhost' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

Add a local user with full administrative privileges on a specific table

These steps will let you add a “super” user who has full administrative rights on a specific table. This user can log on from the guest machine. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to a specific table in a specific database from the guest machine:
    grant all privileges on DATABASENAME.TABLENAME to 'USERNAME'@'localhost' identified by 'PASSWORD';

    Replace:

    • DATABASENAME with the name of the database.
    • TABLENAME with the name of the table.
    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a local user and give him full administrative privileges so he can share the work of maintaining the Address table in the People database on my MySQL server with me. I will be assigning spearmint as his password. I would type this command to give him full local administrative rights:

grant all privileges on People.Address to 'Mel'@'localhost' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

Add a remote user with full administrative privileges on all databases

These steps will let you add a “super” user who has full administrative rights on all databases and tables, and the power to grant rights to others. This user can log on from the host machine or remote computers. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to all databases from the host machine or remote computers:
    grant all privileges on *.* to 'USERNAME'@'%' identified by 'PASSWORD' with grant option;

    Replace:

    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a remote user and give him full administrative privileges so he can share the work of maintaining my MySQL server with me. I will be assigning spearmint as his password. I would type this command to give him full remote administrative rights:

grant all privileges on *.* to 'Mel'@'%' identified by 'spearmint' with grant option;

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

Add a remote user with full administrative privileges on a specific database

These steps will let you add a “super” user who has full administrative rights on a specific database. This user can log on from the host machine or remote computers. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to a specific database from the host machine or remote computers:
    grant all privileges on DATABASENAME.* to 'USERNAME'@'%' identified by 'PASSWORD';

    Replace:

    • DATABASENAME with the name of the database.
    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a remote user and give him full administrative privileges so he can share the work of maintaining the People database on my MySQL server with me. I will be assigning spearmint as his password. I would type this command to give him full remote administrative rights:

grant all privileges on People.* to 'Mel'@'%' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

Add a remote user with full administrative privileges on a specific table

These steps will let you add a “super” user who has full administrative rights on a specific table. This user can log on from the host machine or remote computers. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to the specific table in a specific database from the host machine or remote computers:
    grant all privileges on DATABASENAME.TABLENAME to 'USERNAME'@'%' identified by 'PASSWORD';

    Replace:

    • DATABASENAME with the name of the database.
    • TABLENAME with the name of the table.
    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a remote user and give him full administrative privileges so he can share the work of maintaining the Address table in the People database on my MySQL server with me. I will be assigning spearmint as his password. I would type this command to give him full remote administrative rights:

grant all privileges on People.Address to 'Mel'@'%' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

Add a local user with specific privileges on all databases

These steps will let you add a user whose only powers are to select, insert and update (edit) the information in all databases. This user can log on from the guest machine. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to the all databases from the guest machine:
    grant select, insert, update on *.* to 'USERNAME'@'localhost' identified by 'PASSWORD';

    Replace:

    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
    flush privileges;


Example:

Let’s say I want to add Mel as a local user and give him access to all databases on my MySQL server, but only give him the power to work with the data. I will be assigning spearmint as his password. I would type this command to give him local database access:

grant select, insert, update on *.* to 'Mel'@'localhost' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

There many privileges you can choose from.
Please see the MySQL manual.

Add a local user with specific privileges on a specific database

These steps will let you add a user whose only powers are to select, insert and update (edit) the information in a specific database. This user can log on from the guest machine. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to a specific database from the guest machine:
    grant select, insert, update on DATABASENAME.* to 'USERNAME'@'localhost' identified by 'PASSWORD';

    Replace:

    • DATABASENAME with the name of the database.
    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a local user and give him access to the People database on my MySQL server, but only give him the power to work with the data. I will be assigning spearmint as his password. I would type this command to give him local database access:

grant select, insert, update on People.* to 'Mel'@'localhost' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

There many privileges you can choose from.
Please see the MySQL manual.

Add a local user with specific privileges on a specific table

These steps will let you add a user whose only powers are to select, insert and update (edit) the information in a specific table. This user can log on from the guest machine. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to a specific table in a specific database from the guest machine:
    grant select, insert, update on DATABASENAME.TABLENAME to 'USERNAME'@'localhost' identified by 'PASSWORD';

    Replace:

    • DATABASENAME with the name of the database.
    • TABLENAME with the name of the table.
    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a local user and give him access to the Address table in the People database on my MySQL server, but only give him the power to work with the data. I will be assigning spearmint as his password. I would type this command to give him local database access:

grant select, insert, update on People.Address to 'Mel'@'localhost' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

There many privileges you can choose from.
Please see the MySQL manual.

Add a remote user with specific privileges on all databases

These steps will let you add a user whose only powers are to select, insert and update (edit) the information in all databases. This user can log on from the host machine or a remote computer. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to all databases from the host machine or remote computers:
    grant select, insert, update on *.* to 'USERNAME'@'%' identified by 'PASSWORD';

    Replace:

    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a remote user and give him access to all databases on my MySQL server, but only give him the power to work with the data. I will be assigning spearmint as his password. I would type this command to give him remote database access:

grant select, insert, update on *.* to 'Mel'@'%' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

There many privileges you can choose from.
Please see the MySQL manual.

Add a remote user with specific privileges on a specific database

These steps will let you add a user whose only powers are to select, insert and update (edit) the information in a specific databases. This user can log on from the host machine or a remote computer. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to a specific database from the host machine or remote computers:
    grant select, insert, update on DATABASENAME.* to 'USERNAME'@'%' identified by 'PASSWORD';

    Replace:

    • DATABASENAME with the name of the database.
    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.

  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a remote user and give him access to the People database on my MySQL server, but only give him the power to work with the data. I will be assigning spearmint as his password. I would type this command to give him remote database access:

grant select, insert, update on People.* to 'Mel'@'%' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

There many privileges you can choose from.
Please see the MySQL manual.

Add a remote user with specific privileges on a specific table

These steps will let you add a user whose only powers are to select, insert and update (edit) the information in a specific table. This user can log on from the host machine or a remote computer. You must choose a password for your user. He or she can change that password later.

  1. Make sure you’re logged on.
  2. Type this command to give the user access to a specific table from the host machine or remote computers:
    grant select, insert, update on DATABASENAME.TABLENAME to 'USERNAME'@'%' identified by 'PASSWORD';

    Replace:

    • DATABASENAME with the name of the database.
    • TABLENAME with the name of the table.
    • USERNAME with the name of the user.
    • PASSWORD with the password of the user.
  3. Type this command to apply the changes without having to restart the server:
  4. flush privileges;


Example:

Let’s say I want to add Mel as a remote user and give him access to the Address table in the People database on my MySQL server, but only give him the power to work with the data. I will be assigning spearmint as his password. I would type this command to give him remote database access:

grant select, insert, update on People.Address to 'Mel'@'%' identified by 'spearmint';

I would then apply the changes with this command:

flush privileges;


IMPORTANT
User names are case sensitive.

Passwords are case sensitive.

There many privileges you can choose from.
Please see the MySQL manual.

Change your password

  1. Make sure you’re logged on.
  2. Type this command to change your password:
    set password = password('NEWPASSWORD');

    Replace:

    • NEWPASSWORD with the new password you’d like to use.



Example:

Let’s say my current password is spearmint and I’d like to change it to peppermint. I would type this command:

set password=password('peppermint');


IMPORTANT
Passwords are case sensitive.

Change a user’s local password

This will change a user’s password for logging onto your MySQL server from the guest machine.

  1. Make sure you’re logged on.
  2. Type this command:
    set password for 'USERNAME'@'localhost' = password('NEWPASSWORD');

    Replace:

    • USERNAME with the name of the user.
    • NEWPASSWORD with the user’s new password.



Example:

Let’s say I have a user named Mel who has local access to my MySQL server. His current password is spearmint and he’d like me to change it to peppermint. I would type this command:

set password for 'Mel'@'localhost' = password('peppermint');


IMPORTANT
Passwords are case sensitive.

Change a user’s remote password

This will change a user’s password for logging onto your MySQL server from the host machine or a remote computer.

  1. Make sure you’re logged on.
  2. Type this command:
    set password for 'USERNAME'@'%' = password('NEWPASSWORD');

    Replace:

    • USERNAME with the name of the user.
    • NEWPASSWORD with the user’s new password.



Example:

Let’s say I have a user named Mel who has remote access to my MySQL server. His current password is spearmint and he’d like me to change it to peppermint. I would type this command:

set password for 'Mel'@'%' = password('peppermint');


IMPORTANT
Passwords are case sensitive.

Display users

  1. Make sure you’re logged on.
  2. Type this command to choose the default database that MySQL uses for all of its internal “housekeeping chores”:
  3. use mysql;
  4. Display all users on your server by tying this command:
  5. select user, host from mysql.user;

Remove a user

These steps will remove a user from your MySQL server regardless of what privileges they have.

  1. Make sure you’re logged on.
  2. Type this command to choose the default mysql database that MySQL uses for all of its internal “housekeeping chores”:
  3. use mysql;

  4. Type this command to remove the user:
  5. delete from user where user='USERNAME';

    Replace:

    • USERNAME with the name of the user you’d like to remove.

  6. Type this command to apply the changes without having to restart the server:
  7. flush privileges;


Example:

Let’s say that for some reason I no longer wish Mel to be a user on my MySQL server. I would type this command to use the MySQL housekeeping database:

use mysql;

Then I’d type this command to remove Mel as a user from my server:

delete from user where user='Mel';

And finally, I’d apply the changes with this command:

flush privileges;


IMPORTANT
If the user is logged into your MySQL server at the time,
they will continue to have access until they log out.

Database Management

This section is organized – for the most part – alphabetically, to make it easy to find what you’re looking for.

Back up and restore a database

For the purposes of this guide, backups can be done by exporting your databases to .sql or .csv files, and restores can be done by importing the files back into MySQL. What you do to further back up the exported files is an important decision only you can make after looking at the various ways Linux offers for doing backups and deciding which is the best fit for how you work.

Cancel a MySQL command

If you’ve accidentally typed an incomplete command and pressed the Enter key, MySQL will wait at the MySQL prompt until you finish or cancel the command. To cancel an incomplete command, type this at the MySQL prompt:
\c
Note that this does not always work, and you may occasionally find yourself in a position where you have to log out and back in again instead.

Change the name of a field

This will change the name of a field while preserving its type and attributes.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to make note of the current name, type, and attributes of the field you’re about to rename:
    describe TABLENAME;

    Replace:

    • TABLENAME with the name of the table the field is in.
  4. Type this command to rename the field:
    alter table TABLENAME change FIELDNAME NEWFIELDNAME TYPE(ATTRIBUTE);

    Replace:

    • TABLENAME with the name of the table the field is in.
    • FIELDNAME with the current name of the field.
    • NEWFIELDNAME with the new name for the field.
    • TYPE with the field type.
    • ATTRIBUTE with the attribute(s) of the field.

IMPORTANT
It’s always wise to back up a database before making changes to its structure.

When doing the describe TABLENAME; command, note that the Default field
is misleading in that it lists NULL as the default for all fields, whether they’re
null or not. To check whether you’ve defined a field as null or not null, check
whether it says YES or NO in the Null field.

Check MySQL history

Type this command at the command prompt or in a terminal window on the guest machine to check your MySQL history:

cat ~/.mysql_history

If you find yourself frequently typing the same commands over and over while in MySQL, you can edit this file with any text editor to contain just those commands. Then you can use your up and down arrow keys at a MySQL prompt to shop through the commands.

Check MySQL version

  1. Make sure you’re logged on.
  2. Type this command to check the version of MySQL you’re using:
  3. select version();

Choose a database to work with

  1. Make sure you’re logged on.
  2. Display all databases on the server by typing this command:
  3. show databases;

  4. Choose the database you want to work with by typing this command:
    use DATABASENAME;

    Replace:

    • DATABASENAME with the name of the database.

Create a database

This will create a new, empty database after checking whether you already have a database by the same name.

  1. Make sure you’re logged on.
  2. Type this command to create a database:
    create database if not exists DATABASENAME;

    Replace:

    • DATABASENAME with the name you’d like to give the database.



Example:

Let’s say I want to create a database named People. I can type this command:

create database if not exists People;

If I already have a database named People, MySQL will respond with a warning and not create the new database:

Query OK, 0 rows affected, 1 warning (0.00 sec)

If I then decide that I’ll name my new database People2, I can type this command:

create database if not exists People2;

MySQL will respond without a warning and create the new database:

Query OK, 1 row affected (0.01 sec)


IMPORTANT
Database names are case sensitive.

Create a table

This will create a table with no required fields. This means that any of the cells are allowed to be empty, and MySQL will fill them in with the default NULL placeholder to indicate to you that they’re empty.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create a table:
    create table TABLENAME (FIELDNAME TYPE(ATTRIBUTE));

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field.
    • TYPE with the type of field you’re creating.
    • ATTRIBUTE with the attribute(s) of the field.

  • Note: You can add as many additional fields as you like by separating each from the one before it with a comma and a space, and defining the type and attribute(s) of each. For example:
  • create table TABLENAME (FIELDNAME1 TYPE1(ATTRIBUTE1), FIELDNAME2 TYPE2(ATTRIBUTE2), FIELDNAME3 TYPE3(ATTRIBUTE3));

HOW THIS TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into only some of the fields:
    • MySQL will insert the data you typed into the field(s).
    • MySQL will insert the NULL placeholder into the empty field(s).
  • If you do a complete insert and insert the NULL placeholder into any of the fields:
    • MySQL will insert the NULL placeholder into the empty field(s).
    • MySQL will insert the data you typed into the other fields.
  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.



Example:

Let’s say I want to create the example Phone table. Since this is a default table I’m creating, I’m not specifying that any of the fields in my table are required.

I need a contact field for the names of the people I add to the database. Since names can use letters and other characters, I’ll choose varchar as the type of field. Since names can be of varying lengths, I’ll give the contact field a width of 40, which should give me plenty of space to fit a name.

I need a homephone and cellphone field to hold the numbers where I can reach my contacts. Since phone numbers can use parentheses and dashes, I cannot choose an integer type for the homephone and cellphone fields. I’ll use varchar for those as well. I will make (XXX) XXX-XXXX the accepted method for entering phone numbers with X representing any number. There are 14 characters in that example, so I’ll give the homephone and cellphone fields a width of 14.

Now that all of that is decided, I can create the table:

create table Phone (contact varchar(40), homephone varchar(14), cellphone varchar(14));

I can then verify that my table got created correctly:

describe Phone;

MySQL will then show me the structure of my table:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| contact   | varchar(40) | YES  |     | NULL    |       |
| homephone | varchar(14) | YES  |     | NULL    |       |
| cellphone | varchar(14) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Everything is as I want it to be! Now I can add some data. First I’ll add Hugo’s information:

insert into Phone values ("Hugo", "(800) 111-1111", "(900) 111-1111");

Then I’ll add the information for Lucas:

insert into Phone values ("Lucas", "(800) 222-2222", "(900) 222-2222");

And finally I’ll add Spencer’s information. But let’s say I don’t know Spencer’s cell phone number. I’ll do a partial insert, filling in the fields I do know:

insert into Phone (contact, homephone) values ("Spencer", "(800) 333-3333");

I can then look at my table to see the entries I just added:

select * from Phone;

All my entries are just as I want them to be. Notice that MySQL has kindly inserted the NULL placeholder into the cellphone field of Spencer’s record since I left that field empty:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | NULL           |
+---------+----------------+----------------+

Now suppose I have Trent’s home phone number, but can’t remember his name or cell phone number, I could do a partial insert, putting just his home phone number into my table and figuring I’ll check on his name and fill it in later:

insert into Phone (homephone) values ("(800) 444-4444");

I can then look at my table again to see the entry I just added.

select * from Phone;

MySQL has kindly inserted the NULL placeholder into both the contact and cellphone fields of Trent’s record:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | NULL           |
| NULL    | (800) 444-4444 | NULL           |
+---------+----------------+----------------+

I could, instead, have done a complete insert by using this command and specifying values for all of the fields:

insert into Phone values (NULL, "(800) 444-4444", NULL);

Notice that in order to insert the NULL placeholder, I typed NULL without quotes around it. If I had used quotes, MySQL would have treated it as a text string (word) rather than a MySQL placeholder which represents the absence of data.


Create a table with a required field or fields

This will create a table specifying (with not null) that one or more of the fields must be filled out when data is entered into the table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create a table:
    create table TABLENAME (FIELDNAME TYPE(ATTRIBUTE) not null);

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field.
    • TYPE with the type of field you’re creating.
    • ATTRIBUTE with the attribute(s) of the field.

  • Note: You can add as many additional fields as you like by separating each from the one before it with a comma and a space, and defining the type and attribute(s) of each. You can designate as many of the fields not null as you like:
  • create table TABLENAME (FIELDNAME1 TYPE1(ATTRIBUTE1) not null, FIELDNAME2 TYPE2(ATTRIBUTE2), FIELDNAME3 TYPE3(ATTRIBUTE3) not null);

HOW THIS TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into just the non-required fields:
    • MySQL will print a warning.
    • MySQL will insert the data you typed into the non-required fields.
    • MySQL will place nothing in the empty required field(s).
  • If you do a partial insert and type valid data into just the required field(s):
    • MySQL will insert the data you typed into the required field(s).
    • MySQL will insert the NULL placeholder into the empty, non-required field(s).
  • If you do a complete insert and attempt to insert the NULL placeholder into the required field(s):
    • MySQL will print an error message.
    • MySQL will not add the record.
  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.



Example:

Let’s say I want to create the example Phone table.

I need a contact field for the names of the people I add to the database. Since names can use letters and other characters, I’ll choose varchar as the type of field. Since names can be of varying lengths, I’ll give the contact field a width of 40, which should give me plenty of space to fit a name. Since my table wouldn’t make much sense without people’s names, I want to make this field required by using the not null designation.

I need a homephone and cellphone field to hold the numbers where I can reach my contacts. Since phone numbers can use parentheses and dashes, I cannot choose an integer type for the homephone and cellphone fields. I’ll use varchar for those as well. I will make (XXX) XXX-XXXX the accepted method for entering phone numbers with X representing any number. There are 14 characters in that example, so I’ll give the homephone and cellphone fields a width of 14. I will not make these fields required.

Now that all of that is decided, I can create the table:

create table Phone (contact varchar(40) not null, homephone varchar(14), cellphone varchar(14));

I can then verify that my table got created correctly:

describe Phone;

MySQL will then show me the structure of my table:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| contact   | varchar(40) | NO   |     | NULL    |       |
| homephone | varchar(14) | YES  |     | NULL    |       |
| cellphone | varchar(14) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Everything is as I want it to be! The contact field is required (NOT NULL) and the other two fields are optional (NULL). This command can be misleading in that the Default field shows NULL as the value for all fields, even though the contact field is NOT NULL. What’s important when looking at the results of the describe TABLE; command is to check the Null field. If it says NO, then the field is NOT NULL. If it says YES, then the field is NULL.

Now I can add some data. First I’ll add Hugo’s information:

insert into Phone values ("Hugo", "(800) 111-1111", "(900) 111-1111");

Then I’ll add the information for Lucas:

insert into Phone values ("Lucas", "(800) 222-2222", "(900) 222-2222");

And finally I’ll add Spencer’s information, but let’s say I don’t know Spencer’s cell phone number. I’ll do a partial insert, filling in the fields I do know:

insert into Phone (contact, homephone) values ("Spencer", "(800) 333-3333");

I can then look at my table to see the entries I just added:

select * from Phone;

All my entries are just as I want them to be. Notice that MySQL has kindly inserted the NULL placeholder into the cellphone field of Spencer’s record since I left that field empty:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | NULL           |
+---------+----------------+----------------+

Suppose I have Trent’s home phone number, but can’t remember his name or cell phone number, I could do a partial insert, putting just his home phone number into my table and figuring I’ll check on his name and fill it in later:

insert into Phone (homephone) values ("(800) 444-4444");

MySQL will print out a warning letting me know I’ve done something wrong:

Query OK, 1 row affected, 1 warning (0.00 sec)

I can then look at my table again to see the entry I just added. As you can see, MySQL left the required contact field completely empty, filled in the field I gave it data for, and inserted the NULL placeholder into the empty cellphone field:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | NULL           |
|         | (800) 444-4444 | NULL           |
+---------+----------------+----------------+

I could, instead, have tried to add Trent’s record as a complete insert by using this command and specifying values for all the fields:

insert into Phone values (NULL, "(800) 444-4444", NULL);

Notice that in order to insert the NULL placeholder, I typed NULL without quotes around it. If I had used quotes, MySQL would have treated it as a text string (word) rather than a MySQL placeholder which represents the absence of data.

MySQL would have immediately kicked out an error message letting me know that the contact field cannot be null:

ERROR 1048 (23000): Column 'contact' cannot be null

Trent’s record would not have been added.

It’s very important to pay careful attention to what you’re doing when you want to manually insert NULL placeholders into a table. If I had tried to do a complete insert and accidentally placed quotes around the first instance of NULL in the command, MySQL would not have printed out an error and it would have added the record, inserting NULL as text rather than as a placeholder. Since they would both look the same when displaying the data in the table, if I wasn’t paying close attention, I might not notice that I had done this.


Create a table with a primary key field

This will create a table with a primary key field that you are required to fill out manually with unique data. When creating a primary key in this way, you are not restricted to using an integer type of field.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create a table:
    create table TABLENAME (FIELDNAME TYPE(ATTRIBUTE) not null primary key);

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the primary key field.
    • TYPE with the type of field you’d like to create.
    • ATTRIBUTE with the attribute(s) of the field.

  • Note that you can add as many additional fields as you like by separating each from the one before it with a comma and a space, and defining the type and attribute(s) of each:
  • create table TABLENAME (FIELDNAME1 TYPE1(ATTRIBUTE1) not null primary key, FIELDNAME2 TYPE2(ATTRIBUTE2), FIELDNAME3 TYPE3(ATTRIBUTE3));

HOW THIS TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into just the non-required fields:
    • MySQL will print a warning.
    • MySQL will insert the data you typed into the non-required fields.
    • MySQL will place nothing in the empty primary key field and any other required fields.

  • If you do a partial insert and type valid data into just the primary key field:
    • MySQL will insert the data you typed into the primary key field.
    • MySQL will insert the NULL placeholder into the empty, non-required fields.
    • MySQL will print a warning if there are any empty required fields other than the primary key field.
    • MySQL will place nothing in any empty required fields other than the primary key field.
  • If you do a complete insert and insert the NULL placeholder into the primary key field:
    • MySQL will print an error message.
    • MySQL will not add the record.
  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.



Example:

Let’s say I want to create a very simple table named Identification with just a primary key field named ID. I would type this command:

create table Identification (ID varchar(6) not null primary key);

For a more complex example, let’s say I have a club whose members are listed in the example Address table. I’d like to create a table just like that, but with the first field being a primary key field named ID that I can type membership numbers into. I’d like the ID and contact fields to be required. I would type this command:

create table Address (ID varchar(6) not null primary key, contact varchar(40) not null, street varchar(40), city varchar(20), state char(2), zip int(5));

If I then filled the table with the same data that’s in the example Address table and added an ID number for each contact, my table would look like this:

+--------+---------+---------------+-------------+-------+-------+
| ID     | contact | street        | city        | state | zip   |
+--------+---------+---------------+-------------+-------+-------+
| CHXD01 | Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| CHXD02 | Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| CHXD03 | Spencer | 3 Main Street | Salem       | OR    | 33333 |
+--------+---------+---------------+-------------+-------+-------+
  • Note that the entries in the primary key field in this example are incremented. You do not need to do this. When adding records manually to this table, you can type any data into the primary key field as long as it’s unique (hasn’t already been used in that field).


Create a table with a primary key field (automatically incremented)

This will create a table with an integer type primary key field that can be automatically added and incremented for you by MySQL.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create a table:
    create table TABLENAME (FIELDNAME TYPE(#) not null auto_increment primary key);

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the primary key field.
    • TYPE with an integer type of field.
    • # with the number of integers wide you’d like the field to be.

  • Note that you can add as many additional fields as you like by separating each from the one before it with a comma and a space, and defining the type and attribute(s) of each:
  • create table TABLENAME (FIELDNAME1 TYPE1(#) not null auto_increment primary key, FIELDNAME2 TYPE2(ATTRIBUTE2), FIELDNAME3 TYPE3(ATTRIBUTE3));

HOW THIS TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into just the non-required field(s):
    • MySQL will fill in the primary key field for you with an automatically incremented number.
    • MySQL will insert the data you typed into the non-required field(s).
    • MySQL will insert the NULL placeholder into any empty non-required fields.
    • MySQL will print a warning if there are any empty required fields other than the primary key field.
    • MySQL will place nothing in any empty required fields other than the primary key field.
  • If you do a partial insert and type an integer into just the primary key field:
    • MySQL will insert the integer you typed into the primary key field.
    • MySQL will use the integer you entered into the primary key field as the new starting point from which to continue automatically incrementing integers in that field.
    • MySQL will insert the NULL placeholder into the empty, non-required fields.
    • MySQL will print a warning if there are any empty required fields other than the primary key field.
    • MySQL will place nothing in any empty required fields other than the primary key field.
  • If you do a complete insert and insert the NULL placeholder into the primary key field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.
    • MySQL will use the integer you entered into the primary key field as the new starting point from which to continue automatically incrementing integers in that field.



Example:

Let’s say I want to create a very simple table named Identification with just a primary key field named ID. I would type this command:

create table Identification (ID tinyint(3) not null auto_increment primary key);

For a more complex example, let’s say I want to create the example Address table and I’d like to have the first field be a primary key field named ID. I would type this command:

create table Address (ID tinyint(3) not null auto_increment primary key, contact varchar(40) not null, street varchar(40), city varchar(20), state char(2), zip int(5));

If I then did a partial insert to fill in the table with the same data that’s in the example Address table, MySQL would automatically number the ID field for me, and my table would look like this:

+----+---------+---------------+-------------+-------+-------+
| ID | contact | street        | city        | state | zip   |
+----+---------+---------------+-------------+-------+-------+
| 1  | Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| 2  | Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| 3  | Spencer | 3 Main Street | Salem       | OR    | 33333 |
+----+---------+---------------+-------------+-------+-------+


Create a table with a foreign key field (simple)

This will create a table with a foreign key field that is linked to the primary key field of any other table in the same database. The parent table has a primary key field, and the child table has a foreign key field that links to the parent table’s primary key field. This example is as simple as possible. Each of the tables has only two fields.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create the parent table:
    create table TABLENAME1 (FIELDNAME1 TYPE1(ATTRIBUTE1) not null primary key, FIELDNAME2 TYPE2(ATTRIBUTE2) not null) engine=innodb;

    Replace:

    • TABLENAME1 with the name of the parent table.
    • FIELDNAME1 with the name of the first (primary key) field.
    • TYPE1 with the type of primary key field you’d like to create.
    • ATTRIBUTE1 with the attribute(s) of the primary key field.
    • FIELDNAME2 with the name of the second field.
    • TYPE2 with the type of the second field.
    • ATTRIBUTE2 with the attribute(s) of the second field.
  4. Type this command to create the child table:
    create table TABLENAME2 (FIELDNAME1 TYPE1(ATTRIBUTE1) not null, FIELDNAME2 TYPE2(ATTRIBUTE2) not null, foreign key (FOREIGNKEYFIELDNAME) references TABLENAME1 (TABLENAME1PRIMARYKEYFIELDNAME)) engine=innodb;

    Replace:

    • TABLENAME2 with the name of the child table.
    • FIELDNAME1 with the name of the first field.
    • TYPE1 with the type of the first field.
    • ATTRIBUTE1 with the attribute(s) of the first field.
    • FIELDNAME2 with the name of the second field.
    • TYPE2 with the type of the second field.
    • ATTRIBUTE2 with the attribute(s) of the second field.
    • FOREIGNKEYFIELDNAME with the name of the field you’d like to use as the foreign key (pick one of the fields you just created).
    • TABLENAME1 with the name of the parent table.
    • TABLENAME1PRIMARYKEYFIELDNAME with the name of the primary key field from the parent table.

  • Note that you can add as many additional fields as you like by separating each from the one before it with a comma and a space, and defining the type and attribute(s) of each.
IMPORTANT
The foreign key field should have the same type and
attribute(s) as the parent table’s primary key field.

HOW THE PARENT TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into just one field:
    • MySQL will print a warning.
    • MySQL will insert the data you typed into the required field.
    • MySQL will place nothing in the empty required field.
  • If you do a complete insert and insert the NULL placeholder into either field:
    • MySQL will print an error message.
    • MySQL will not add the record.
  • If you do a complete insert and type valid data into both fields:
    • MySQL will insert the data you typed into both fields.

HOW THE CHILD TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into just the foreign key field:
    • MySQL will print a warning.
    • MySQL will insert the data you typed into the foreign key field.
    • MySQL will place nothing in the empty required field.
  • If you do a partial insert and type valid data into just the non-foreign key field:
    • MySQL will print an error message.
    • MySQL will not add the record.
  • If you do a complete insert and insert the NULL placeholder into either field:
    • MySQL will print an error message.
    • MySQL will not add the record.
  • If you do a complete insert and type valid data into both fields:
    • MySQL will insert the data you typed into both fields.
  • If you do a partial or complete insert and type invalid data into the foreign key field:
    • MySQL will print an error message.
    • MySQL will not add the record.



Example:

Let’s say I want to give Hugo and his pals some tests and I’d like to keep track of whether they pass or fail. I can create a parent table with a primary key field named code to store the values I want to use in the child table, and another field named grade to specify which code value means passing or failing:

create table Table1 (code tinyint(1) not null primary key, grade char(6) not null) engine=innodb;

Then I can insert the records for passing and failing into the table:

insert into Table1 values ("1", "passed"), ("2", "failed");

I can take a look at the table:

select * from Table1;

MySQL shows me that the table was created properly:

+------+--------+
| code | grade  |
+------+--------+
|    1 | passed |
|    2 | failed |
+------+--------+

Next I’ll create the child table with a name field for Hugo and his pals to be listed in and a results foreign key field which links to (references) the code field in Table1:

create table Table2 (name varchar(40) not null, results tinyint(1) not null, foreign key (results) references Table1 (code)) engine=innodb;

Since Hugo passed the test, I’ll add his record with a 1 in the results field:

insert into Table2 values ("Hugo", "1");

Since Lucas failed the test, I’ll add his record with a 2 in the results field:

insert into Table2 values ("Lucas", "2");

Now let’s say that Spencer passed the test and I intended to put a 1 in the results field, but for some reason I pressed the 3 key instead:

insert into Table2 values ("Spencer", "3");

MySQL immediately lets me know that it’s refusing to add the record because I chose a value for the foreign key field that doesn’t exist in the code field of the parent table:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`Test/Table2`, CONSTRAINT `Table2_ibfk_1` FOREIGN KEY (`code`) REFERENCES `Table1` (`code`))

I can add that record properly by choosing a value from the code field from Table1:

insert into Table2 values ("Spencer", "1");

I can take a look at the table:

select * from Table2;

MySQL will show me that Spencer got added:

+---------+---------+
| name    | results |
+---------+---------+
| Hugo    |       1 |
| Lucas   |       2 |
| Spencer |       1 |
+---------+---------+
3 rows in set (0.00 sec)

Let’s say I just want to see the records of those who passed the test. I can type this command, selecting the name field from the child table and the grade field from the parent table, and specifying that I only want the records from those who have a value of 1 in the results field of the child table:

select Table2.name, Table1.grade from Table2, Table1 where Table2.results=Table1.code and Table2.results="1";

MySQL would show me who passed the test:

+---------+--------+
| name    | grade  |
+---------+--------+
| Hugo    | passed |
| Spencer | passed |
+---------+--------+

Or maybe I’d like to see who failed the test. I can type this command, selecting the name field from the child table and the grade field from the parent table, and specifying that I only want the records from those who have a value of 2 in the results field of the child table:

select Table2.name, Table1.grade from Table2, Table1 where Table2.results=Table1.code and Table2.results="2";

MySQL would show me who failed the test:

+-------+--------+
| name  | grade  |
+-------+--------+
| Lucas | failed |
+-------+--------+


Create a table with a foreign key field (advanced)

This will create a table with a foreign key field that is linked to the primary key field of any other table in the same database. This example is a bit more advanced. The parent table has two fields. The child table has three fields. Both tables have an automatically incremented primary key field. The child table has a foreign key field that links to the parent table’s primary key field. In this example, the third field is the foreign key field. Adjust the instructions to specify whichever field you’d like as the foreign key field.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create the parent table:
    create table TABLENAME1 (FIELDNAME1 TYPE1(#) not null primary key auto_increment, FIELDNAME2 TYPE2(ATTRIBUTE2) not null) engine=innodb;

    Replace:

    • TABLENAME1 with the name of the parent table.
    • FIELDNAME1 with the name of the first (primary key) field.
    • TYPE1 with an integer type.
    • # with the number of characters wide you’d like the primary key field to be.
    • FIELDNAME2 with the name of the second field.
    • TYPE2 with the type of the second field.
    • ATTRIBUTE2 with the attribute(s) of the second field.


  4. Type this command to create the child table:
  5. create table TABLENAME2 (FIELDNAME1 TYPE1(#) not null primary key auto_increment, FIELDNAME2 TYPE2(ATTRIBUTE2) not null, FIELDNAME3 TYPE3(ATTRIBUTE3) not null, index (FOREIGNKEYFIELDNAME), foreign key (FOREIGNKEYFIELDNAME) references TABLENAME1 (TABLENAME1PRIMARYKEYFIELDNAME)) engine=innodb;

    Replace:

    • TABLENAME2 with the name of the child table.
    • FIELDNAME1 with the name of the first (primary key) field.
    • TYPE1 with an integer type.
    • # with the number of characters wide you’d like the primary key field to be.
    • FIELDNAME2 with the name of the second field.
    • TYPE2 with the type of the second field.
    • ATTRIBUTE2 with the attribute(s) of the second field.
    • FIELDNAME3 with the name of the third field.
    • TYPE3 with the type of the third field.
    • ATTRIBUTE3 with the attribute(s) of the third field.
    • All instances of FOREIGNKEYFIELDNAME with the name of the field you’d like to use as the foreign key (pick one of the fields you just created).
    • TABLENAME1 with the name of the parent table.
    • TABLENAME1PRIMARYKEYFIELDNAME with the name of the primary key field from the parent table.

  • Note that you can add as many additional fields as you like by separating each from the one before it with a comma and a space, and defining the type and attribute(s) of each.
IMPORTANT
The foreign key field should have the same type and
attribute(s) as the parent table’s primary key field.

HOW THE PARENT TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into just one field:
    • MySQL will print a warning.
    • MySQL will insert the data you typed into the required field.
    • MySQL will place nothing in the empty, required fields.

  • If you do a partial insert and type an integer into just the primary key field:
    • MySQL will print a warning.
    • MySQL will insert the integer you typed into the primary key field.
    • MySQL will use the integer you entered into the primary key field as the new starting point for continuing to automatically increment integers in that column.
    • MySQL will place nothing in the empty, required fields.

  • If you do a complete insert and insert the NULL placeholder into either field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a complete insert and type valid data into both fields:
    • MySQL will insert the data you typed into both fields.
    • MySQL will use the integer you entered into the primary key field as the new starting point for continuing to automatically increment integers in that column.

HOW THE CHILD TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into just the foreign key field:
    • MySQL will print a warning.
    • MySQL will insert the data you typed into the foreign key field.
    • MySQL will leave the other two fields empty.

  • If you do a partial insert and type valid data into just the non-foreign key fields:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a complete insert and insert the NULL placeholder into any of the fields:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.
    • MySQL will use the integer you entered into the primary key field as the new starting point for continuing to automatically increment integers in that column.

  • If you do a partial or complete insert and type invalid data into the foreign key field:
    • MySQL will print an error message.
    • MySQL will not add the record.



Example:

Let’s say I have a laboratory in which I do experiments and I’d like to keep track of which ones have succeeded, which have failed, which are currently incomplete, and which ones I’m completely unsure of. I can create a parent table that lists the four different types of classification using the primary key field to assign numbers to them:

create table Table1 (id tinyint(3) not null primary key auto_increment, classification varchar(50) not null) engine=innodb;

I can then do a partial insert, filling in only the classification field, since MySQL will handle the id field for me:

insert into Table1 (classification) values ("succeeded"), ("failed"), ("incomplete"), ("unknown");

I can then take a look at my table to verify that the primary key numbered my classifications for me:

select * from Table1;

MySQL shows me that all is well:

+----+----------------+
| id | classification |
+----+----------------+
|  1 | succeeded      |
|  2 | failed         |
|  3 | incomplete     |
|  4 | unknown        |
+----+----------------+
4 rows in set (0.01 sec)

I can then create a child table with a primary key field of its own, and a foreign key field named FK that links to the primary key field in the parent table:

create table Table2 (id tinyint(3) not null primary key auto_increment, experiment varchar(50) not null, FK tinyint(3) not null, index (FK), foreign key (FK) references Table1 (id)) engine=innodb;

I can then add records to my table as I do my experiments. All of these records will be considered acceptable since I chose a value for the FK field from the id field of the parent table:

insert into Table2 (experiment, FK) values ("Experiment1", "1");

insert into Table2 (experiment, FK) values ("Experiment2", "2");

insert into Table2 (experiment, FK) values ("Experiment3", "4");

insert into Table2 (experiment, FK) values ("Experiment4", "4");

Just as an example, if I tried to add this record, I’d run into trouble:

insert into Table2 (experiment, FK) values ("Experiment5", "5");

MySQL would immediately let me know that it’s refusing to add the record because I chose a value for the foreign key field that doesn’t exist in the primary key field of the parent table:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`Test/Table2`, CONSTRAINT `Table2_ibfk_1` FOREIGN KEY (`FK`) REFERENCES `Table1` (`id`))

I can take a look at the entire child table:

select * from Table2;

MySQL will show me the entire table:

+----+-------------+----+
| id | experiment  | FK |
+----+-------------+----+
|  1 | Experiment1 |  1 |
|  2 | Experiment2 |  2 |
|  3 | Experiment3 |  4 |
|  4 | Experiment4 |  4 |
+----+-------------+----+
4 rows in set (0.00 sec)

Let’s say I just want to see the experiment names and classification of any experiments that succeeded. I can type this command:

select Table2.experiment, Table1.classification from Table2, Table1 where Table2.FK=Table1.id and Table2.FK="1";

MySQL will show me the experiments that succeeded:

+-------------+----------------+
| experiment  | classification |
+-------------+----------------+
| Experiment1 | succeeded      |
+-------------+----------------+
1 row in set (0.00 sec)

Let’s say I just want to see the experiment names and classification of any experiments whose classification is unknown. I can type this command:

select Table2.experiment, Table1.classification from Table2, Table1 where Table2.FK=Table1.id and Table2.FK="4";

MySQL will show me all experiments whose classification is unknown:

+-------------+----------------+
| experiment  | classification |
+-------------+----------------+
| Experiment3 | unknown        |
| Experiment4 | unknown        |
+-------------+----------------+
2 rows in set (0.00 sec)


Create a table with an index field

This will create a table with one index field.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create the table:
    create table TABLENAME (FIELDNAME1 TYPE1(ATTRIBUTE1), FIELDNAME2 TYPE2(ATTRIBUTE2), FIELDNAME3 TYPE3(ATTRIBUTE3), index (FIELDNAMETOINDEX));

    Replace:

    • TABLENAME with the name you’d like to give the table.
    • FIELDNAME1 with the name of the first field.
    • TYPE1 with the type of the first field.
    • ATTRIBUTE1 with the attribute(s) of the first field.
    • FIELDNAME2 with the name of the second field.
    • TYPE2 with the type of the second field.
    • ATTRIBUTE2 with the attributes of the second field.
    • FIELDNAME3 with the name of the third field.
    • TYPE3 with the type of the third field.
    • ATTRIBUTE3 with the attributes of the third field.
    • FIELDNAMETOINDEX with the name of the field you’d like to index (pick one of the fields you just created).


HOW THIS TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into only some of the fields:
    • MySQL will insert the data you typed into the field(s).
    • MySQL will insert the NULL placeholder into the empty fields.

  • If you do a complete insert and insert the NULL placeholder into any of the fields:
    • MySQL will insert the NULL placeholder into the field(s) you specified.
    • MySQL will insert the data you typed into the other fields.

  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.



Example:

Let’s say I want to create the example Phone table, and I’d like the contact field to be indexed. I would type this command:

create table Phone (contact varchar(40) not null, homephone varchar(14), cellphone varchar(14), index (contact));

I could then verify that I indexed the contact field by typing this command:

show indexes from Phone;

MySQL would show me the indexes:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Phone |          1 | contact  |            1 | contact     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)


Create a table with more than one index field

This will create a table with more than one index field:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create the table:
  4. create table TABLENAME (FIELDNAME1 TYPE1(ATTRIBUTE1), FIELDNAME2 TYPE2(ATTRIBUTE2), FIELDNAME3 TYPE3(ATTRIBUTE3), index (FIELDNAMETOINDEX1, FIELDNAMETOINDEX2));

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME1 with the name of the first field.
    • TYPE1 with the type of the first field.
    • ATTRIBUTE1 with the attribute(s) of the first field.
    • FIELDNAME2 with the name of the second field.
    • TYPE2 with the type of the second field.
    • ATTRIBUTE2 with the attributes of the second field.
    • FIELDNAME3 with the name of the third field.
    • TYPE3 with the type of the third field.
    • ATTRIBUTE3 with the attributes of the third field.
    • FIELDNAMETOINDEX1 with the first name of a field you’d like to index (pick one of the fields you just created).
    • FIELDNAMETOINDEX2 with the second name of a field you’d like to index (pick another of the fields you just created).

HOW THIS TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into only some of the fields:
    • MySQL will insert the data you typed into the fields.
    • MySQL will insert the NULL placeholder into the empty fields.

  • If you do a complete insert and insert the NULL placeholder into any of the fields:
    • MySQL will insert the NULL placeholder into the fields you specified.
    • MySQL will insert the data you typed into the other fields.

  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.



Example:

Let’s say I want to create the example Phone table and I’d like the contact and homephone fields to be indexed. I would type this command:

create table Phone (contact varchar(40) not null, homephone varchar(14), cellphone varchar(14), index (contact, homephone));

I could then verify that I indexed the contact and homephone fields by typing this command:

show indexes from Phone;

MySQL would show me the indexes:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Phone |          1 | contact  |            1 | contact     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| Phone |          1 | contact  |            2 | homephone   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)


Create a table with a unique field

This will create a table with a unique field.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create the table:
  4. create table TABLENAME (FIELDNAME1 TYPE1(ATTRIBUTE1) unique, FIELDNAME2 TYPE2(ATTRIBUTE2), FIELDNAME3 TYPE3(ATTRIBUTE3));

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME1 with the name of the first field.
    • TYPE1 with the type of the first field.
    • ATTRIBUTE1 with the attribute(s) of the first field.
    • FIELDNAME2 with the name of the second field.
    • TYPE2 with the type of the second field.
    • ATTRIBUTE2 with the attributes of the second field.
    • FIELDNAME3 with the name of the third field.
    • TYPE3 with the type of the third field.
    • ATTRIBUTE3 with the attributes of the third field.

HOW THIS TABLE WILL BEHAVE:

  • If you do a partial insert and type valid data into only one non-unique field:
    • MySQL will insert the NULL placeholder into the unique field.
    • MySQL will insert the data you typed into the non-unique field.
    • MySQL will insert the NULL placeholder into the other non-unique field.

  • If you do a partial insert and type valid data into both non-unique fields:
    • MySQL will insert the NULL placeholder into the unique field.
    • MySQL will insert the data you typed into the non-unique fields.

  • If you do more than one partial insert in which you don’t fill in the unique field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a partial insert and type valid (unique) data into only the unique field:
    • MySQL will insert the data you typed into the unique field.
    • MySQL will insert the NULL placeholder into the non-unique fields.

  • If you do a partial insert and type invalid (non-unique) data into only the unique field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a partial insert and insert the NULL placeholder into only the unique field:
    • MySQL will insert the NULL placeholder into the unique field.
    • MySQL will insert the NULL placeholder into the non-unique fields.

  • If you do a complete insert and insert the NULL placeholder into the unique field and valid data into the non-unique fields:
    • MySQL will insert the NULL placeholder into the unique field.
    • MySQL will insert the data you typed into the non-unique fields.

  • If you do a complete insert and type invalid (non-unique) data into the unique field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.



Example:

Let’s say I want to create the example Phone table with no required fields, and I want to make the contact field unique so that no two names can be identical. I would type this command:

create table Phone (contact varchar(40) unique, homephone varchar(14), cellphone varchar(14), index (contact));

I could then insert all the data that’s in the example Phone table with this command:

insert into Phone values ("Hugo", "(800) 111-1111", "(900) 111-1111"), ("Lucas", "(800) 222-2222", "(900) 222-2222"), ("Spencer", "(800) 333-3333", "(900) 333-3333");

Let’s say I’m not aware that there’s already a Hugo in the database and I try to add a new record with another Hugo as the contact:

insert into Phone values ("Hugo", "(800) 444-4444", "(900) 444-4444");

MySQL would print an error message and refuse to add the record, since no two records may be the same in a unique field:

ERROR 1062 (23000): Duplicate entry 'Hugo' for key 1


Create a table with a unique required field

This will create a table with a unique, required field.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Type this command to create the table:
  4. create table TABLENAME (FIELDNAME1 TYPE1(ATTRIBUTE1) not null unique, FIELDNAME2 TYPE2(ATTRIBUTE2), FIELDNAME3 TYPE3(ATTRIBUTE3));

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME1 with the name of the first field.
    • TYPE1 with the type of the first field.
    • ATTRIBUTE1 with the attribute(s) of the first field.
    • FIELDNAME2 with the name of the second field.
    • TYPE2 with the type of the second field.
    • ATTRIBUTE2 with the attribute(s) of the second field.
    • FIELDNAME3 with the name of the third field.
    • TYPE3 with the type of the third field.
    • ATTRIBUTE3 with the attribute(s) of the third field.

HOW THIS TABLE WILL BEHAVE:

  • If you do a partial insert and type data into only one non-required field:
    • MySQL will print a warning.
    • MySQL will leave the required, unique field empty.
    • MySQL will insert the data you typed into the non-required field.
    • MySQL will insert the NULL placeholder into the other non-required field.

  • If you do a partial insert and type data into both non-required fields:
    • MySQL will print a warning.
    • MySQL will leave the required, unique field empty.
    • MySQL will insert the data you typed into the non-required fields.

  • If you do more than one partial insert in which you don’t fill in the required, unique field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a partial insert and type valid data into only the required, unique field:
    • MySQL will insert the data you typed into the required, unique field.
    • MySQL will insert the NULL placeholder into the non-required fields.

  • If you do a partial insert and type invalid (non-unique) data into the required, unique field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a partial insert and insert the NULL placeholder into the required, unique field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a complete insert and type valid data into all of the fields:
    • MySQL will insert the data you typed into all of the fields.

  • If you do a complete insert and type invalid (non-unique) data into the required, unique field:
    • MySQL will print an error message.
    • MySQL will not add the record.

  • If you do a complete insert and insert the NULL placeholder into the required, unique field:
    • MySQL will print an error message.
    • MySQL will not add the record.



Example:

Let’s say I want to create the example Phone table and I want to make the contact field unique so that no two names can be identical. I would type this command:

create table Phone (contact varchar(40) not null unique, homephone varchar(14), cellphone varchar(14), index (contact));

I could then insert all the data that’s in the example Phone table with this command:

insert into Phone values ("Hugo", "(800) 111-1111", "(900) 111-1111"), ("Lucas", "(800) 222-2222", "(900) 222-2222"), ("Spencer", "(800) 333-3333", "(900) 333-3333");

Let’s say I’m not aware that there’s already a Hugo in the database and I try to add a new record with another Hugo as the contact:

insert into Phone values ("Hugo", "(800) 444-4444", "(900) 444-4444");

MySQL would print an error message and refuse to add the record, since no two records may be the same in a unique field:

ERROR 1062 (23000): Duplicate entry 'Hugo' for key 1


Delete a cell

To delete a single cell in a record, you can update the record, setting the field the cell is in to “” to remove its contents:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to delete a cell:
  4. update TABLENAME set FIELDNAME1="" where FIELDNAME2="CRITERION";

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME1 with the name of the field the cell you want to delete is in.
    • FIELDNAME2 with the name of the field you’d like to use to identify the record(s).
    • CRITERION with the data that must be in that field to identify the record(s).
    • Note that FIELDNAME2 can be the same as FIELDNAME1.

IMPORTANT
This will not make the cell NULL. To make the cell NULL, use this command instead:

update TABLENAME set FIELDNAME1=NULL where FIELDNAME2=”CRITERION”;


Example:

To see how this works, take a look at the example Phone table in the Examples section. Let’s say Hugo no longer has a home phone, so I wish to remove his home phone entry from my table. I would type this command:

update Phone set homephone="" where homephone="(800) 111-1111";

Or this command:

update Phone set homephone="" where contact="Hugo";

My table would then look like this:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    |                | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | (900) 333-3333 |
+---------+----------------+----------------+


Delete a database

  1. Make sure you’re logged on.
  2. Type this command to delete a database:
  3. drop database if exists DATABASENAME;

    Replace:

    • DATABASENAME with the name of the database.


Example:

Let’s say I want to delete the People database. I can type this command:

drop database if exists People;

If the People database exists, MySQL will delete it.


Delete a field

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to delete a field:
  4. alter table TABLENAME drop column FIELDNAME;

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field you’d like to remove.


Example:

To see how this works, take a look at the example Phone table in the Examples section. Let’s say I no longer wish to contact Hugo or his pals by cell phone. To remove the cellphone field from that table, I’d type this command:

alter table Phone drop column cellphone;

My table would then look like this:

+---------+----------------+
| contact | homephone      |
+---------+----------------+
| Hugo    | (800) 111-1111 |
| Lucas   | (800) 222-2222 |
| Spencer | (800) 333-3333 |
+---------+----------------+


Delete a record or several records

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. To delete all records that contain a specific criterion in a specific field, type this command:
  4. delete from TABLENAME where FIELDNAME="CRITERION";

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field.
    • CRITERION with the contents of the field you’d like to use to specify the record(s) to remove.


Example:

To see how this works, take a look at the example Address table in the Examples section. Let’s say I no longer want anything to do with Hugo. To remove him from the database, I’d type this command:

delete from Address where contact="Hugo";

My table would then look like this:

+---------+---------------+-------------+-------+-------+
| contact | street        | city        | state | zip   |
+---------+---------------+-------------+-------+-------+
| Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 |
+---------+---------------+-------------+-------+-------+


Delete a table

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Remove a table by typing this command:
  4. drop table TABLENAME;

    Replace:

    • TABLENAME with the name of the table.


Example:

Let’s say I want to delete the Phone table from the People database. I can type this command to choose the People database:

use People;

I can then type this command to get rid of the Phone table:

drop table Phone;

MySQL will delete the Phone table.


Delete all data from a table

This will delete all the data from a table, but not the table or its structure.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to delete all the data from a table:
  4. delete from TABLENAME;

    Replace:

    • TABLENAME with the name of the table.


Example:

Let’s say I want to delete the data in the Phone table, which is in the People database. I can type this command to choose the People database:

use People;

I can then type this command to delete the data from the Phone table:

drop table Phone;

MySQL will delete all the data from the Phone table, but the table and its structure will still exist, so I can enter new data into it.


Display all databases

This will display a list of all the databases on your server.

  1. Make sure you’re logged on.
  2. Type this command:
  3. show databases;
IMPORTANT
Administrators will see all databases.

Users will see only databases they have access to.

Display all data in a table (tabular output)

The use of ; at the end of a select statement will display the output as a table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to display all the data in a table:
  4. select * from TABLENAME;

    Replace:

    • TABLENAME with the name of the table.


Example:

To see all the data in the example Address table, I would type this command:

select * from Address;

MySQL would show me all the data in the table in table format:

+---------+---------------+-------------+-------+-------+
| contact | street        | city        | state | zip   |
+---------+---------------+-------------+-------+-------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 |
+---------+---------------+-------------+-------+-------+
3 rows in set (0.00 sec)


Display all data in a table (block output)

The use of \G at the end of a select statement will display the output as a block of text with dividing header rows.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to display all the data in a table:
  4. select * from TABLENAME\G

    Replace:

    • TABLENAME with the name of the table.


Example:

To see all the data in the example Address table, I would type this command:

select * from Address\G

MySQL would show me all the data in the table in block format:

*************************** 1. row ***************************
contact: Hugo
 street: 1 Main Street
   city: Jackson
  state: MS
    zip: 11111
*************************** 2. row ***************************
contact: Lucas
 street: 2 Main Street
   city: Springfield
  state: IL
    zip: 22222
*************************** 3. row ***************************
contact: Spencer
 street: 3 Main Street
   city: Salem
  state: OR
    zip: 33333
3 rows in set (0.00 sec)


Display all tables

This will display all the tables in the current database.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to show all the tables in the database:
  4. show tables;

Display current database

This will display which database you’re currently using.

  1. Make sure you’re logged on.
  2. Type this command:
  3. select database();

Display indexes in a table

This will show the indexes in a table, if it has any.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to show the indexes in a table:
  4. show indexes from TABLENAME;

    Replace:

    • TABLENAME with the name of the table.


Example:

Let’s say I had used this command to create the example Phone database with the contact field indexed:

create table Phone (contact varchar(40) not null, homephone varchar(14), cellphone varchar(14), index (contact));

I could then display the indexes of the Phone table with this command:

show indexes from Phone;

MySQL would show me that the contact field is indexed:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Phone |          1 | contact  |            1 | contact     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


Display table structure

This will display the structure of the specified table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. If you’re not sure of the table name, display all the tables in the current database.
  4. Type this command to display the table structure:
  5. describe TABLENAME;

    Replace:

    • TABLENAME with the name of the table.


Example:

Let’s say I want to look at the structure of the example Phone table, which should have the contact field as a required field. I can type this command:

describe Phone;

MySQL will show me the structure of the table:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| contact   | varchar(40) | NO   |     | NULL    |       |
| homephone | varchar(14) | YES  |     | NULL    |       |
| cellphone | varchar(14) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

I can tell from the Null field that the contact field is not null, which is exactly what I want it to be.


IMPORTANT
When doing the describe TABLENAME; command, note that the Default field
is misleading. It lists NULL as the default for all fields. To check whether you’ve
defined a field as null or not null, check whether it says YES or NO in the Null field.

Edit a record or several records in a table (simple)

This will allow you to edit a record or several records in a table. This example uses one criterion.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to edit the record(s) in a table:
  4. update TABLENAME set FIELDNAME1="NEWVALUE" where FIELDNAME2="CRITERION";

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME1 with the name of the field the cell(s) you’d like to change is/are in.
    • NEWVALUE with the new contents for the cell(s) in that field.
    • FIELDNAME2 with the name of the field to use for determining which record(s) to change.
    • CRITERION with the data that must be in that field to determine which record(s) to change.


Example

To see how this works, take a look at the example Phone table in the Examples section. Let’s say that Hugo changed his name to Hugh. If I wished to change every instance of “Hugo” to “Hugh” in my table, I would type this command:

update Phone set contact=”Hugh” where contact=”Hugo”;

My table would then look like this:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugh    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | (900) 333-3333 |
+---------+----------------+----------------+


Edit a record or several records in a table (advanced)

This will allow you to edit a record or several records in a table. This example uses two criteria.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to edit the record(s) in a table:
  4. update TABLENAME set FIELDNAME1="NEWVALUE" where FIELDNAME2="CRITERION1" and FIELDNAME3="CRITERION2";

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME1 with the name of the field you’d like to change.
    • NEWVALUE with the new contents for the cell(s) in that field.
    • FIELDNAME2 with the name of the field that determines which record(s) to change.
    • CRITERION1 with the data that must be in that field to determine which record(s) to change.
    • FIELDNAME3 with the name of an additional field that determines which record(s) to change.
    • CRITERION2 with the data that must be in that field to determine which record(s) to change.


Example:

To see how this works, take a look at this example. In this table there are fields for lastname and firstname. Two of the people have the first name of Phillip:

+----------+------------+----------------+----------------+
| lastname | firstname  | homephone      | cellphone      |
+----------+------------+----------------+----------------+
| Davis    | Phillip    | (800) 111-1111 | (900) 111-1111 |
| Jones    | Phillip    | (800) 222-2222 | (900) 222-2222 |
| Smith    | Steve      | (800) 333-3333 | (900) 333-3333 |
+----------+------------+----------------+----------------+

Let’s say that Phillip Davis prefers to be known as Phil. Since there are two Phillips in my table, I’ll need an additional criterion to make the command more specific:

update Phone set firstname="Phil" where lastname="Davis" and firstname="Phillip";

My table would then look like this:

+----------+------------+----------------+----------------+
| lastname | firstname  | homephone      | cellphone      |
+----------+------------+----------------+----------------+
| Davis    | Phil       | (800) 111-1111 | (900) 111-1111 |
| Jones    | Phillip    | (800) 222-2222 | (900) 222-2222 |
| Smith    | Steve      | (800) 333-3333 | (900) 333-3333 |
+----------+------------+----------------+----------------+


Edit a table to index an existing field

This will index an existing field in a table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to index an existing field:
  4. create index INDEXNAME on TABLENAME(FIELDNAME);

    Replace:

    • INDEXNAME with whatever you’d like to name the index.
    • TABLENAME with the name of the table.
    • FIELDNAME with the name of an existing field you’d like to index.

  • Note that the INDEXNAME is used by MySQL, and will only be visible to you when you do a specific command to view the indexes in your table.


Example:

Let’s say I often work with the example Address table, and what I look up most often is the streets Hugo and his pals live on. To make my searches go faster, I can index the street field by typing this command:

create index index_street on Address(street);

I can then type this command to verify that I indexed the street field:

show indexes from Address;

MySQL would show me that the field I indexed is the street field and the name I gave the index is index_street:

+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Address |          1 | index_street |            1 | street      | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)


Edit a table to remove an index from a field

This will remove an index from a field. Note that the data will not be removed.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Check which fields are indexed in the table and get the name of the index you wish to remove by typing this command:
  4. show indexes from TABLENAME;

    Replace:

    • TABLENAME with the name of the table.


  5. Type this command:
  6. alter table TABLENAME drop index INDEXNAME;

    Replace:

    • TABLENAME with the name of the table.
    • INDEXNAME with the name of the index you wish to remove.


Example:

Let’s say I had previously indexed the street field in the example Address table, but I no longer need that field to be indexed. First I would type this command to check for indexes in my table and get the name of the index:

show indexes from Address;

MySQL would show me that the street field is indexed and that the name of the index is index_street:

+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Address |          1 | index_street |            1 | street      | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

I would then remove the index:

alter table Address drop index index_street;

I could make sure I had removed it with this command:

show indexes from Address;

Since the Address table only contained one index and I removed it, MySQL would return an empty set, verifying that I had succeeded in removing the index:

  Empty set (0.00 sec)

And, for good measure, I could select all the data from the Address table:

select * from Address;

MySQL would verify that I had done no harm to the table itself:

+---------+---------------+-------------+-------+-------+
| contact | street        | city        | state | zip   |
+---------+---------------+-------------+-------+-------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 |
+---------+---------------+-------------+-------+-------+
3 rows in set (0.00 sec)


Edit a table to make an existing field unique

This will index an existing field as unique, making it so duplicate values cannot be added to that field. This will only work on fields that do not already have duplicate entries.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to make an existing field unique:
  4. alter table TABLENAME add unique (FIELDNAME);

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field.


Example:

Let’s say I’m working with the example Address table and I want the contact field to be unique. I can type this command:

alter table Address add unique (contact);

MySQL will now allow only unique values in the contact field.

Let’s say I tried to do that after I or someone else had already added a second record with “Hugo” as the contact:

alter table Address add unique (contact);

MySQL would see the duplicate “Hugo” value and refuse to make the contact field unique:

  ERROR 1062 (23000): Duplicate entry 'Hugo' for key 1

I would then have to either change or remove the duplicate values before making the contact field unique, or choose not to make it unique.


Export a database to an .sql file

This will export an entire database into an .sql file in whatever directory you’re in when you execute the command.

Type this command at the command prompt or in a terminal window on the guest machine:

mysqldump -u USERNAME -p DATABASENAME >> DATABASENAME.sql

Replace:

  • USERNAME with your MySQL username.
  • Both instances of DATABASENAME with the name of the database you’d like to export.



Example:

Let’s say my MySQL username is Self and I want to export the example People database into an .sql file. I would either go to the command prompt or open a terminal window on my guest machine and type this command:

mysqldump -u Self -p People >> People.sql

MySQL would then create the People.sql file in whichever directory I was in when I typed the command. This file would contain everything MySQL would need to fully recreate the People database if I were to import the file onto a MySQL server.


Export a field to a .csv file

This will export one field from a table into a .csv file in the /tmp directory on your computer since MySQL has authorization to write to that directory. Quoted fields will be separated by commas, a back-slash will be used as the escape character, a null values will be represented by \N and each line will be terminated by a carriage return and line feed.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to export a field:
  4. select FIELDNAME from TABLENAME into outfile "/tmp/FILENAME.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

    Replace:

    • FIELDNAME with the name of the field.
    • TABLENAME with the name of the table.
    • FILENAME with the name you’d like the exported file to have.


Example:

Let’s say I want to export just the contacts from the example Phone table into a .csv file. I can type this command:

select contact from Phone into outfile "/tmp/Contacts.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

If I go into the /tmp directory on my server and open the Contacts.csv file, this is what it will look like inside:

"Hugo"
"Lucas"
"Spencer"


Export all fields to a .csv file

This will export the entire contents of a table into a .csv file in the /tmp directory on your computer since MySQL has authorization to write to that directory. Quoted fields will be separated by commas, a back-slash will be used as the escape character, null values will be represented by \N and each line will be terminated by a carriage return and line feed.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to export all fields:
  4. select * from TABLENAME into outfile "/tmp/FILENAME.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

    Replace:

    • TABLENAME with the name of the table.
    • FILENAME with whatever you’d like to name the exported file.


Example:

Let’s say I want to export all the data from the example Phone table into a .csv file. I would type this command:

select * from Phone into outfile "/tmp/PhoneRecords.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

If I then went into the /tmp directory on my server and opened the PhoneRecords.csv file, this is what it would look like inside:

"Hugo","(800) 111-1111","(900) 111-1111"
"Lucas","(800) 222-2222","(900) 222-2222"
"Spencer","(800) 333-3333","(900) 333-3333"


Export specific fields to a .csv file

This will export more than one field from a table into a .csv file in the /tmp directory on your computer since MySQL has authorization to write to that directory. Quoted fields will be separated by commas, a back-slash will be used as the escape character, null values will be represented by \N and each line will be terminated by a carriage return and line feed.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to export specific fields:
  4. select FIELDNAME1, FIELDNAME2, FIELDNAME3 from TABLENAME into outfile "/tmp/FILENAME.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

    Replace:

    • FIELDNAME1 with the name of the first field you’d like to export.
    • FIELDNAME2 with the name of the second field you’d like to export.
    • FIELDNAME3 with the name of the third field you’d like to export.
    • TABLENAME with the name of the table the field is in.
    • FILENAME with the name you’d like the exported file to have.

  • Note that you can add as many additional fields as you like by separating each from the one before it with a comma and a space.


Example:

Let’s say I want to export the names and home phone numbers of all my contacts from the example Phone table into a .csv file. I can type this command:

select contact, homephone from Phone into outfile "/tmp/HomeNumbers.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

If I go into the /tmp directory on my server and open the HomeNumbers.csv file, this is what it will look like inside:

"Hugo","(800) 111-1111"
"Lucas","(800) 222-2222"
"Spencer","(800) 333-3333"


Export specific records to a .csv file

This will export one or more records from a table into a .csv file in the /tmp directory on your computer since MySQL has authorization to write to that directory. Quoted fields will be separated by commas, a back-slash will be used as the escape character, null values will be represented by \N and each line will be terminated by a carriage return and line feed.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to export one or more records from a table:
  4. select * from TABLENAME where FIELDNAME="CRITERION" into outfile "/tmp/FILENAME.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field you’d like to use to identify the record(s).
    • CRITERION with the data that must be in that field to identify the record(s).
    • FILENAME with the name you’d like to give the exported file.

  • Note that you can add additional fields and criteria for a more specific selection:
  • select * from TABLENAME where FIELDNAME1="CRITERION1" and FIELDNAME2="CRITERION2" into outfile "/tmp/FILENAME.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';


Example:

Let’s say I want to export Hugo’s Phone record from the example Phone table into a .csv file. I can type this command:

select * from Phone where contact="Hugo" into outfile "/tmp/HugoPhone.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

Or, if I want to be more specific in my selection, I can type this command:

select * from Phone where contact="Hugo" and homephone="(800) 111-1111" into outfile "/tmp/HugoPhone.csv" fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\r\n';

If I go into the /tmp directory on my server and open the HugoPhone.csv file, this is what it will look like inside:

"Hugo","(800) 111-1111","(900) 111-1111"


Import a .csv file into a table

To import the data from a .csv file into a table, you need to prepare the table to hold the imported data.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Create a table, defining valid fields (the number of fields must correspond to the number of values in each line of the .csv file, and the correct field types must be created to accept the kind of data you’ll be importing) to store the data from your .csv file.
  4. Type this command to import the .csv file:
  5. load data infile '/PATH/FILENAME.csv' into table TABLENAME fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

    Replace:

    • PATH with the path to the .csv file.
    • FILENAME.csv with the name of the .csv file.
    • TABLENAME with the name of the table.

IMPORTANT
The command used here assumes that the .csv file you will be importing from uses
a comma (,) to separate the fields, quotes (“) to enclose data in each field, \N to
represent the null value, and a carriage return and line feed at the end of each
line. If the structure of your .csv files is different from this, either change the
structure of the .csv file or modify the command to reflect the structure your .csv
file uses.

Import an .sql file into a database

To import an .sql file into a database, you must put it into an empty database. If you already have one, you can skip Step 1 of these instructions. Otherwise, follow the instructions in Step 1 to create a new database. Also, make sure you have a copy of the .sql file in a directory on the guest machine.

  1. Type this command at the command prompt or in a terminal window on the guest machine to create a database:
  2. mysqladmin create DATABASENAME -p

    Replace:

    • DATABASENAME with the name of the database you’d like to create.


  3. Provide your MySQL password.
  4. Type this command to import the .sql file into the database:
  5. mysql -D DATABASENAME < /PATH/FILENAME.sql -p

    Replace:

    • DATABASENAME with the name of the database you just created.
    • PATH with the path to the .sql file on the guest machine.
    • FILENAME.sql with the name of the .sql file you wish to import.


  6. Provide your MySQL password.

Import a text file into a table

You can import a text file into a table to add records. This can be done with a brand new, empty table or an existing table with records already in it.

IMPORTANT

The text file must have this structure:

  • There may only be one record per line.
  • There must be one tab between each of the values in the records.
  • There must be as many values on each line of the text file as there are fields in the table.
  • Each line must end with a press of the Enter key.
  • The values must be entered in the same order as they appear in the table.
  • “” can be used to represent empty cells.
  • \N must be used to represent NULL values.
  1. Make sure you’re logged on.
  2. Choose the database you want to work with – creating it first, if needed.
  3. Create a table, if necessary, defining valid fields (the number of fields must correspond to the number of values in each line of the .csv file, and the correct field types must be created to accept the kind of data you’ll be importing) to store the data from your .csv file.
  4. Type this command to import the text file into a table:
  5. load data infile '/PATH/FILENAME.txt' into table TABLENAME;

    Replace:

    • PATH with the path to the .txt file on the guest machine.
    • FILENAME.txt with the name of the file.
    • TABLENAME with the name of the table you’d like to import the file into.


Example:

Let’s say I already have the example Address table in my People database, and I’d like to import two new records into it from a tabbed text file. I could create a text file and enter the records for Hugo’s new pals in the same order they would appear in the example Address table, separating each value with a tab. In this example I don’t know Winston’s street address or zip code, so I’ll put \N as the value for those fields to let MySQL know that I don’t have values for those fields.

The contents of the text file would look like this:

  Winston	\N	Charleston	NC	\N
  Dutch	5 Main Street	Aspen	CO	55555

As you can see, this doesn’t properly line up the fields so that it’s easy to visually distinguish them. It can be tempting to add an extra tab here or an extra space there to get the fields to line up and look nice, but I should not do this, since it can cause unexpected results when I import the file.

Let’s say I named the text file Address.txt and put it in the /tmp directory on my server. I would log on and then choose the People database to use:

use People;

I would execute this command to import the text file into the Address table:

load data infile '/tmp/Address.txt' into table Address;

My table would then look like this:

+---------+---------------+-------------+-------+-------+
| contact | street        | city        | state | zip   |
+---------+---------------+-------------+-------+-------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 |
| Winston | NULL          | Charleston  | NC    | NULL  |
| Dutch   | 5 Main Street | Aspen       | CO    | 55555 |
+---------+---------------+-------------+-------+-------+


Insert a field at the beginning of a table

This inserts a field at the beginning of a table or after a specific existing field.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert the field:
  4. alter table TABLENAME add FIELDNAME TYPE(ATTRIBUTE) first;

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field you’d like to add.
    • TYPE with the type of the field.
    • ATTRIBUTE with the attribute(s) of the field.


Example:

To see how this works, take a look at the example Phone table in the Examples section. Let’s say I’d like to manually assign unique identification numbers to Hugo and his pals, and I’d like that number to be in the first field of my table.

alter table Phone add identification tinyint(3) first;

My table would then look like this:

+----------------+---------+----------------+----------------+
| identification | contact | homephone      | cellphone      |
+----------------+---------+----------------+----------------+
|           NULL | Hugo    | (800) 111-1111 | (900) 111-1111 |
|           NULL | Lucas   | (800) 222-2222 | (900) 222-2222 |
|           NULL | Spencer | (800) 333-3333 | (900) 333-3333 |
+----------------+---------+----------------+----------------+

I can then add the identification numbers of Hugo and his pals by following the instructions in the Edit a record or several records in a table section of this document.


Insert a field at the end of a table

This inserts a field at the end of a table after the last existing field.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert the field:
  4. alter table TABLENAME add FIELDNAME TYPE(ATTRIBUTE);

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field you’d like to add.
    • TYPE with the type of the field.
    • ATTRIBUTE with the attribute(s) of the field.


Example:

To see how this works, take a look at the example Phone table in the Examples section. Let’s say I’d like to add the work numbers of Hugo and his pals. To add the workphone field and make it identical in structure to the homephone and cellphone fields, I’d type this command:

alter table Phone add workphone varchar(14);

My table would then look like this:

+---------+----------------+----------------+----------------+
| contact | homephone      | cellphone      | workphone      |
+---------+----------------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 | NULL           |
| Lucas   | (800) 222-2222 | (900) 222-2222 | NULL           |
| Spencer | (800) 333-3333 | (900) 333-3333 | NULL           |
+---------+----------------+----------------+----------------+

I can then add the work numbers of Hugo and his pals by following the instructions in the Edit a record or several records in a table section of this document.


Insert a field between two existing fields in a table

This inserts a field immediately after an existing field so that you can “wedge” the new field between any two already existing fields.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert the field:
  4. alter table TABLENAME add FIELDNAME TYPE(ATTRIBUTE) after EXISTINGFIELD;

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field you’d like to add.
    • TYPE with the type of the field.
    • ATTRIBUTE with the attribute(s) of the field.
    • EXISTINGFIELD with the name of the field you’d like to position the new field immediately after.


Example:

To see how this works, take a look at the example Phone table in the Examples section. Let’s say I’d like to add the work numbers of Hugo and his pals. I’d like the workphone field to be identical in structure to the homephone and cellphone fields and I’d like the workphone field to come between the homephone and cellphone fields. I’d type this command:

alter table Phone add workphone varchar(14) after homephone;

My table would then look like this:

+---------+----------------+--------------+----------------+
| contact | homephone      | workphone    | cellphone      |
+---------+----------------+--------------+----------------+
| Hugo    | (800) 111-1111 | NULL         | (900) 111-1111 |
| Lucas   | (800) 222-2222 | NULL         | (900) 222-2222 |
| Spencer | (800) 333-3333 | NULL         | (900) 333-3333 |
+---------+----------------+--------------+----------------+

I can then add the work numbers of Hugo and his pals by following the instructions in the Edit a record or several records in a table section of this document.


Insert a primary key field into a table

This will insert a primary key field into an existing table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. alter table TABLENAME add FIELDNAME TYPE(#) not null auto_increment primary key first;

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field you’d like to add.
    • TYPE with an integer type of field (tinyint or int).
    • # with the number of characters wide you’d like the primary key field to be.


Example:

To see how this works, take a look at the example Phone table in the Examples section. Let’s say I want to add a primary key field to the Phone table so that Hugo’s entry and his pal’s entries will be automatically numbered by MySQL. I would type this command:

alter table Phone add ID tinyint(4) auto_increment primary key first;

My table would then look like this:

+----+---------+----------------+----------------+
| ID | contact | homephone      | cellphone      |
+----+---------+----------------+----------------+
|  1 | Hugo    | (800) 111-1111 | (900) 111-1111 |
|  2 | Lucas   | (800) 222-2222 | (900) 222-2222 |
|  3 | Spencer | (800) 333-3333 | (900) 333-3333 |
+----+---------+----------------+----------------+


IMPORTANT
When adding new records to a table with an auto_increment primary field,
it will behave differently depending on the kind of insert command you use:

If you do a partial insert by adding data to any/all of the other fields, MySQL
will add the primary key field for you.

If you use a complete insert, you will have to fill in the primary key field
manually.

Insert a record into a table (partial record with one field)

This will insert data into one field in a table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert a partial record:
  4. insert into TABLENAME (FIELDNAME) values ("VALUE");

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field.
    • VALUE with the data you’d like to put into that field.


Example:

Let’s say I’m working with the example Phone table and I’d like to add Trent to the table, but I don’t know his home or cell phone numbers. I could type this command to just add his name to the contact field:

insert into Phone (contact) values ("Trent");

MySQL would insert Trent’s name into the contact field and the NULL placeholder into the homephone and cellphone fields. My table would then look like this:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | (900) 333-3333 |
| Trent   | NULL           | NULL           |
+---------+----------------+----------------+


IMPORTANT
This command will fail if you attempt to insert NULL into required fields.

This command will cause your table to behave in unexpected ways if you
do not insert data into required fields.

Insert a record into a table (partial record with multiple fields)

This will insert one record’s data into two or more fields in a table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert a partial record:
  4. insert into TABLENAME (FIELDNAME1, FIELDNAME2) values ("VALUE1", "VALUE2");

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME1 with the name of the first field you’d like to insert data into.
    • FIELDNAME2 with the name of the second field you’d like to insert data into.
    • VALUE1 with the data you’d like to put into the first field.
    • VALUE2 with the data you’d like to put into the second field.

  • Note that you can add as many field names and values as you need for your table, separating each from the one before it with a comma and a space.


Example:

Let’s say I’m working with the example Phone table and I’d like to add Trent to the table. I know his home phone number, but not his cell phone number. I could type this command to add his name and home phone number to the contact and homephone fields:

insert into Phone (contact, homephone) values ("Trent", "(800) 444-4444");

MySQL would insert Trent’s name and home phone number into the contact and homephone fields and the NULL placeholder into the cellphone field. My table would then look like this:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | (900) 333-3333 |
| Trent   | (800) 444-4444 | NULL           |
+---------+----------------+----------------+


IMPORTANT
This command will fail if you attempt to insert NULL into required fields.

This command will cause your table to behave in unexpected ways if you
do not insert data into required fields.

Insert multiple records into a table (partial records with one field)

This will insert multiple records of data into one field in a table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert a partial record:
  4. insert into TABLENAME (FIELDNAME) values ("VALUE1"), ("VALUE2"), ("VALUE3");

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field you’d like to add records into.
    • VALUE1 with the value of the first record.
    • VALUE2 with the value of the second record.
    • VALUE3 with the value of the third record.


Example:

Let’s say I’m working with the example Address table and I want to add three new contacts to it, but I only know their names. I could type this one command to add all three new partial records at once:

insert into Address (contact) values ("Dutch"), ("Winston"), ("Myron");

I could then take a look at my table with this command:

select * from Address;

My table would then look like this:

+---------+---------------+-------------+-------+-------+
| contact | street        | city        | state | zip   |
+---------+---------------+-------------+-------+-------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 |
| Dutch   | NULL          | NULL        | NULL  |  NULL |
| Winston | NULL          | NULL        | NULL  |  NULL |
| Myron   | NULL          | NULL        | NULL  |  NULL |
+---------+---------------+-------------+-------+-------+


IMPORTANT
This command will fail if you attempt to insert NULL into required fields.

This command will cause your table to behave in unexpected ways if you
do not insert data into required fields.

Insert multiple records into a table (partial records with multiple fields)

This will insert multiple records of data into multiple fields in a table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert multiple partial records:
  4. insert into TABLENAME (FIELDNAME1, FIELDNAME2) values ("VALUE1-1", "VALUE1-2"), ("VALUE2-1", "VALUE2-2"), ("VALUE3-1", "VALUE3-2");

    Replace:

    • TABLENAME with the name of the table.
    • FIELDNAME1 with the name of the first field you’d like to add records into.
    • FIELDNAME2 with the name of the second field you’d like to add records into.
    • VALUE1-1 with the value of the first record’s first field.
    • VALUE1-2 with the value of the first record’s second field.
    • VALUE2-1 with the value of the second record’s first field.
    • VALUE2-2 with the value of the second record’s second field.
    • VALUE3-1 with the value of the third record’s first field.
    • VALUE3-2 with the value of the third record’s second field.

  • Note that you can add as many field names and values as you need for your table, separating each from the one before it with a comma and a space.


Example:

Let’s say I’m working with the example Address table and I want to add three new contacts to it, but I only know their names and street addresses. I could type this one command to add all three new partial records at once:

insert into Address (contact, street) values ("Dutch", "4 Main Street"), ("Winston", "5 Main Street"), ("Myron", "6 Main Street");

I could then take a look at my table with this command:

select * from Address;

My table would then look like this:

+---------+---------------+-------------+-------+-------+
| contact | street        | city        | state | zip   |
+---------+---------------+-------------+-------+-------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 |
| Dutch   | 4 Main Street | NULL        | NULL  |  NULL |
| Winston | 5 Main Street | NULL        | NULL  |  NULL |
| Myron   | 6 Main Street | NULL        | NULL  |  NULL |
+---------+---------------+-------------+-------+-------+


IMPORTANT
This command will fail if you attempt to insert NULL into required fields.

This command will cause your table to behave in unexpected ways if you
do not insert data into required fields.

Insert a record into a table (complete record)

This will insert a complete record into a table. To use this command, you must fill out all fields in the table with either valid data or the NULL placeholder. Field names do not need to be specified since you’ll be filling out all fields. You must insert the values in the same order as the fields appear in the table from left to right.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert a complete record:
  4. insert into TABLENAME values ("VALUE1", "VALUE2", "VALUE3");

    Replace:

    • TABLENAME with the name of the table.
    • VALUE1 with the value of the first field.
    • VALUE2 with the value of the second field.
    • VALUE3 with the value of the third field.

  • Note that you can add as many values as you need for your table, separating each from the one before it with a comma and a space.


Example:

Let’s say I’m working with the example Phone table and I want to add Trent. I know his name, home phone number and cell phone number, so I can add a complete record by typing this command:

insert into Phone values ("Trent", "(800) 444-4444", "(900) 444-4444");

I can then take a look at my table with this command:

select * from Phone;

My table would then look like this:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | (900) 333-3333 |
| Trent   | (800) 444-4444 | (900) 444-4444 |
+---------+----------------+----------------+


IMPORTANT
This command will fail if you attempt to insert NULL into required fields.

Insert multiple records into a table (complete records)

This will insert multiple records into a table. The records will be added in the order given, from left to right. To use this command, you must fill out all fields in the table with either valid data or the NULL placeholder.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to insert multiple complete records:
  4. insert into TABLENAME values ("VALUE1-1", "VALUE1-2"), ("VALUE2-1", "VALUE2-2"), ("VALUE3-1", "VALUE3-2");

    Replace:

    • TABLENAME with the name of the table.
    • VALUE1-1 with the value of the first record’s first field.
    • VALUE1-2 with the value of the first record’s second field.
    • VALUE2-1 with the value of the second record’s first field.
    • VALUE2-2 with the value of the second record’s second field.
    • VALUE3-1 with the value of the third record’s first field.
    • VALUE3-2 with the value of the third record’s second field.

  • Note that you can add as many values as you need for your table, separating each from the one before it with a comma and a space.


Example:

Let’s say I’m working with the example Address table and I want to add three complete contacts to it. Instead of adding each record individually, I can add all three at once by typing this command:

insert into Address values ("Dutch", "4 Main Street", "Chicago", "IL", "44444"), ("Winston", "5 Main Street", "Los Angeles", "CA", "55555"), ("Myron", "6 Main Street", "Albuquerque", "NM", "66666");

I can take a look at my table with this command:

select * from Address;

My table would then look like this:

+---------+---------------+-------------+-------+-------+
| contact | street        | city        | state | zip   |
+---------+---------------+-------------+-------+-------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 |
| Dutch   | 4 Main Street | Chicago     | IL    | 44444 |
| Winston | 5 Main Street | Los Angeles | CA    | 55555 |
| Myron   | 6 Main Street | Albuquerque | NM    | 66666 |
+---------+---------------+-------------+-------+-------+


IMPORTANT
This command will fail if you attempt to insert NULL into required fields.

Rename a database

The MySQL rename command turned out to be a security risk and could lead to loss of data, so renaming a database must now be done creatively. The most effective way to do so is to export the database to an .sql file on your computer, create a new database with the name you’d like your database to have, and then import the .sql file into the new database. Once you ensure that the process went smoothly by verifying that the new database is intact, then you can delete the old database.

  1. Type this command at the command prompt or in a terminal window on the guest machine to export the database to your hard drive:
  2. mysqldump -u USERNAME -p DATABASENAME >> DATABASENAME.sql

    Replace:

    • USERNAME with your MySQL username.
    • Both instances of DATABASENAME with the name of the database you’d like to rename.
  3. Type this command to create a new database:
  4. mysqladmin -u USERNAME -p create NEWDATABASENAME

    Replace:

    • USERNAME with your MySQL username.
    • NEWDATABASENAME with the new name you’d like your database to have.
  5. Type this command to import the .sql file into the new database:
  6. mysql -u USERNAME -p NEWDATABASENAME < OLDDATABASENAME.sql

    Replace:

    • USERNAME with your MySQL username.
    • NEWDATABASENAME with the name of the new database you created.
    • OLDDATABASENAME with the old name of your database.

Rename a field

This will rename an existing field in a table. It’s a good idea to back up your table before making structural changes to it.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to rename the field:
  4. alter table TABLENAME change CURRENTFIELDNAME NEWFIELDNAME TYPE(ATTRIBUTE);

    Replace:

    • TABLENAME with the table the field is in.
    • CURRENTFIELDNAME with the name of the field you’d like to change.
    • NEWFIELDNAME with the new name you’d like to give the field.
    • TYPE with the same type the field currently has.
    • ATTRIBUTE with the same attribute(s) the field currently has.


Example:

alter table Phone change homephone workphone varchar(14);


Rename a table

This will rename a table. It’s a good idea to back up your table before making structural changes to it.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to rename a table:
  4. alter table OLDTABLENAME rename to NEWTABLENAME;

    Replace:

    • OLDTABLENAME with the current name of the table.
    • NEWTABLENAME with the new name you’d like it to have.

Select data from 1 table (all fields)

This will select all the data from one table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select * from TABLENAME;

    Replace:

    • TABLENAME with the name of the table.


Example:

Let’s say I want to look at all the data I’ve entered into the example Phone table. I would type this command to select all fields from the Phone table:

select * from Phone;

MySQL would then show me all the data in the Phone table:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | (900) 333-3333 |
+---------+----------------+----------------+


Select data from 1 table (specific field)

This will select all the data from one field in a table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select FIELDNAME from TABLENAME;

    Replace:

    • FIELDNAME with the name of the field.
    • TABLENAME with the name of the table.


Example:

Let’s say I want to see just the contact names from the example Phone table. I would type this command to select just the contact field from the phone table:

select contact from Phone;

MySQL would then show me all the data in the contact field:

+---------+
| contact |
+---------+
| Hugo    |
| Lucas   |
| Spencer |
+---------+


Select data from 1 table (specific fields)

This will select all the data from multiple fields in a table.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select FIELDNAME1, FIELDNAME2 from TABLENAME;

    Replace:

    • FIELDNAME1 with the name of the first field.
    • FIELDNAME2 with the name of the second field.
    • TABLENAME with th name of the table.

  • Note that you can add as many additional fields as you like by separating them from the second one with a comma and a space.


Example:

Let’s say I’m working with the example Phone table and I’d like to see all the contacts and their cell phone numbers. I would type this command:

select contact, cellphone from Phone;

MySQL would then show me all the data in the contact and cellphone fields:

+---------+----------------+
| contact | cellphone      |
+---------+----------------+
| Hugo    | (900) 111-1111 |
| Lucas   | (900) 222-2222 |
| Spencer | (900) 333-3333 |
+---------+----------------+


Select data from 1 table (specific records)

This will select data from one field in a table using a specific criterion.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select FIELDNAME1 from TABLENAME where CRITERIONFIELDNAME="CRITERION";

    Replace:

    • FIELDNAME1 with the name of the field you want to see output from.
    • TABLENAME with the name of the table.
    • CRITERIONFIELDNAME with the name of the field that contains the criterion to idenfity the record.
    • CRITERION with the data that must be in the field to identify the record.


Example:

Let’s say I want to know what Hugo’s cell phone number is. If I was using the example Phone table, I would want to see the cellphone field on any record where Hugo is the contact, so I would type this command:

select cellphone from Phone where contact="Hugo";

MySQL would give me Hugo’s cell phone number:

+----------------+
| cellphone      |
+----------------+
| (900) 111-1111 |
+----------------+


This will select data from more than one field in a table using a specific criterion.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select FIELDNAME1, FIELDNAME2 from TABLENAME where CRITERIONFIELDNAME="CRITERION

    Replace:

    • FIELDNAME1 with the name of the first field you want to see output from.
    • FIELDNAME2 with the name of the second field you want to see output from.
    • TABLENAME with the name of the table.
    • CRITERIONFIELDNAME with the name of the field that contains the criterion to idenfity the record(s).
    • CRITERION with the data that must be in the field to identify the record(s).


Example:

Let’s say I want to know what Hugo’s cell phone and home phone numbers are. If I was using the example Phone table, I would want to see the cellphone and homephone fields on any record where Hugo is the contact, so I would type this command:

select homephone, cellphone from Phone where contact="Hugo";

MySQL would give me Hugo’s cell phone and home phone numbers:

+----------------+----------------+
| homephone      | cellphone      |
+----------------+----------------+
| (800) 111-1111 | (900) 111-1111 |
+----------------+----------------+


Select data from 1 table (specific records that do not match the criterion)

This will select all data from specific fields, using a criterion to determine which records not to select.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. select FIELDNAME1, FIELDNAME2 from TABLENAME where CRITERIONFIELDNAME!="CRITERION";

    Replace:

    • FIELDNAME1 with the name of the first field you’d like to select.
    • FIELDNAME1 with the name of the second field you’d like to select.
    • TABLENAME with the name of the table.
    • CRITERIONFIELDNAME with the name of the field that contains the criterion to idenfity the record(s).
    • CRITERION with the data that must be in the field to identify the record(s) not to select.


Example:

To see how this works, take a look at the example Preferences table in the Examples section. Let’s say I want to throw a party and invite Lucas and Spencer, but not Hugo. I’d need to know what Lucas and Spencer like, so I want to see all the records in the Preferences table where Hugo is not a contact, since he won’t be at the party. I would type this command:

select color, food, music from Preferences where contact!="Hugo";

My results would look like this:

+--------+---------+-------------+
| color  | food    | music       |
+--------+---------+-------------+
| Pink   | Chicken | Pop         |
| Orange | Pizza   | Rock & Roll |
+--------+---------+-------------+

My party will have a very strange color theme, some chicken and pizza, and the band will be playing Pop and Rock & Roll.


Select data from 2 tables (all fields)

This will select all data from two tables. A common field is used to ensure that the data is related. The common field will be shown more than once since it’s present in both tables.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to show all records from two tables:
  4. select * from TABLENAME1, TABLENAME2 where TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2;

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.

  • Note that each table’s common field can have a different name.


Example:

Let’s say I want to see all of the records in the example Address and Phone tables that have the contact field in common displayed as one selection. I would type this command:

select * from Address, Phone where Address.contact=Phone.contact;

My selection would look like this:

+---------+---------------+-------------+-------+-------+---------+----------------+----------------+
| contact | street        | city        | state | zip   | contact | homephone      | cellphone      |
+---------+---------------+-------------+-------+-------+---------+----------------+----------------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 | Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 | Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 | Spencer | (800) 333-3333 | (900) 333-3333 |
+---------+---------------+-------------+-------+-------+---------+----------------+----------------+

Notice that the contact field is shown twice since it’s present in both tables.


Select data from 2 tables (all fields with common field shown only once)

This will select all data from two tables using a common field to ensure that the data is related. The common field will only be shown once.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. select * from TABLENAME1 join TABLENAME2 using (COMMONFIELDNAME);

    Replace:

    • TABLENAME1 with the name of the first table.
    • TABLENAME2 with the name of the second table.
    • COMMONFIELDNAME with the name of the field they both have in common.


Example:

Let’s say I want to see all of the records in the example Phone and Preferences tables that have the contact field in common displayed as one selection, but I don’t want the common field displayed more than once. I would type this command:

select * from Phone join Preferences using (contact);

My selection would look like this:

+---------+----------------+----------------+--------+---------+-------------+
| contact | homephone      | cellphone      | color  | food    | music       |
+---------+----------------+----------------+--------+---------+-------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 | Blue   | Cheese  | Disco       |
| Lucas   | (800) 222-2222 | (900) 222-2222 | Pink   | Chicken | Pop         |
| Spencer | (800) 333-3333 | (900) 333-3333 | Orange | Pizza   | Rock & Roll |
+---------+----------------+----------------+--------+---------+-------------+


Select data from 2 tables (specific fields)

This will select all data in specific fields from two tables using a common field. There are different ways to do this. Here I will show three different commands to accomplish the same thing. You’ll find that sometimes one of them will be more appropriate to use than the others.

The first command uses “where” to specify the names of the common fields:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. select TABLENAME1.FIELDNAME1, TABLENAME2.FIELDNAME2 from TABLENAME1, TABLENAME2 where TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2;

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • FIELDNAME1 with the name of the field you’d like to select from the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • FIELDNAME2 with the name of the field you’d like to select from the second table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.

  • Note that each table’s common field can have a different name.


Example:

Let’s say I wish to select the contact field from the example Address table and the cellphone field from the example Phone table, I want all records that have the contact field in common, and I want the resulting selection to be sorted by the contact field. I would type this command:

select Address.contact, Phone.cellphone from Address, Phone where Address.contact=Phone.contact;

My selection would look like this:

+---------+----------------+
| contact | cellphone      |
+---------+----------------+
| Hugo    | (900) 111-1111 |
| Lucas   | (900) 222-2222 |
| Spencer | (900) 333-3333 |
+---------+----------------+


The second command joins two tables using “on” to specify the names of the common fields:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. select TABLENAME1.FIELDNAME1, TABLENAME2.FIELDNAME2 from TABLENAME1 join TABLENAME2 on (TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2);

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • FIELDNAME1 with the name of the field you’d like to select from the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • FIELDNAME2 with the name of the field you’d like to select from the second table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.

  • Note that each table’s common field can have a different name.


Example:

Let’s say I wish to select the contact field from the example Address table and the cellphone field from the example Phone table, I want all records that have the contact field in common, and I want the resulting selection to be sorted by the contact field. I would type this command:

select Address.contact, Phone.cellphone from Address join Phone on (Address.contact=Phone.contact);

My selection would look like this:

+---------+----------------+
| contact | cellphone      |
+---------+----------------+
| Hugo    | (900) 111-1111 |
| Lucas   | (900) 222-2222 |
| Spencer | (900) 333-3333 |
+---------+----------------+


The third command joins two tables using “using” to specify the name of a field:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. select TABLENAME1.FIELDNAME1, TABLENAME2.FIELDNAME2 from TABLENAME1 join TABLENAME2 using (COMMONFIELDNAME);

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • FIELDNAME1 with the name of the field you’d like to select from the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • FIELDNAME2 with the name of the field you’d like to select from the second table.
    • COMMONFIELDNAME with the name of the field that will be used as the common field.

  • Note that to use this command, each table’s common field must have the same name.


Example:

Let’s say I wish to select the contact field from the example Address table and the cellphone field from the example Phone table, I want all records that have the contact field in common, and I want the resulting selection to be sorted by the contact field. I would type this command:

select Address.contact, Phone.cellphone from Address join Phone using (contact);

My selection would look like this:

+---------+----------------+
| contact | cellphone      |
+---------+----------------+
| Hugo    | (900) 111-1111 |
| Lucas   | (900) 222-2222 |
| Spencer | (900) 333-3333 |
+---------+----------------+


Select data from 2 tables (specific records)

This will select specific records in specific fields from two tables. A common field is used to ensure that the data is related.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. select FIELDNAME1, FIELDNAME2 from TABLENAME1, TABLENAME2 where TABLENAME1.COMMONFIELDNAME1=TABLE2.COMMONFIELDNAME2 and CRITERIONTABLENAME.CRITERIONFIELDNAME="CRITERION";

    Replace:

    • FIELDNAME1 with the name of the field you’d like to select from the first table.
    • FIELDNAME2 with the name of the field you’d like to select from the second table.
    • All instances of TABLENAME1 with the name of the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.
    • CRITERIONTABLENAME with the name of the table that contains the criterion to idenfity the record(s).
    • CRITERIONFIELDNAME with the name of the field that contains the criterion to idenfity the record(s).
    • CRITERION with the data that must be in the field to identify the record(s).

  • Note that you can add as many additional fields as you like by separating each from the one before it with a comma and a space.
  • Note that each table’s common field can have a different name.


Example:

Let’s say I want Hugo’s cell phone number and street address from the example Phone and Address tables. I would type this command:

select cellphone, street from Phone, Address where Phone.contact=Address.contact and Phone.contact="Hugo";

My selection would look like this:

+----------------+---------------+
| cellphone      | street        |
+----------------+---------------+
| (900) 111-1111 | 1 Main Street |
+----------------+---------------+


Select data from 2 tables (specific records that do not match the criterion)

This will select all data from specific fields, using a criterion to determine which records not to select.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. select TABLENAME1.FIELDNAME1, TABLENAME1.FIELDNAME2, TABLENAME2.FIELDNAME3, TABLENAME2.FIELDNAME4, from TABLENAME1, TABLENAME2 where TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2 and CRITERIONTABLENAME.CRITERIONFIELDNAME!="CRITERION";

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • FIELDNAME1 with the name of the first table’s first field you’d like to select.
    • FIELDNAME2 with the name of the first table’s second field you’d like to select.
    • All instances of TABLENAME2 with the name of the second table.
    • FIELDNAME3 with the name of the second table’s first field you’d like to select.
    • FIELDNAME4 with the name of the second table’s second field you’d like to select.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME1 with the name of the second table’s field that it has in common with the first table.
    • CRITERIONTABLENAME with the name of the table that contains the criterion to idenfity the record(s).
    • CRITERIONFIELDNAME with the name of the field that contains the criterion to idenfity the record(s).
    • CRITERION with the data that must be in the field to identify the record(s) not to select.

  • Note that you can add as many additional fields as you like by separating each from the one before it with a comma and a space, and by following the format of TABLENAME.FIELDNAME.


Example:

Let’s say I want to throw a party and invite Lucas and Spencer, but not Hugo. I’d need their home phone numbers and I’d need to know what they like. I can grab their names and numbers from the contact and homephone fields from the example Phone table and I can find out what they like from the color, food and music fields from the example Preferences table, and I can make sure I don’t get Hugo’s information by making his name the criterion for what not to select:

select Phone.contact, Phone.homephone, Preferences.color, Preferences.food, Preferences.music from Phone, Preferences where Phone.contact=Preferences.contact and Phone.contact!="Hugo";

MySQL will then show me just the fields from Lucas and Spencer:

+---------+----------------+--------+---------+-------------+
| contact | homephone      | color  | food    | music       |
+---------+----------------+--------+---------+-------------+
| Lucas   | (800) 222-2222 | Pink   | Chicken | Pop         |
| Spencer | (800) 333-3333 | Orange | Pizza   | Rock & Roll |
+---------+----------------+--------+---------+-------------+

My party will have a very strange color theme, some chicken and pizza, and the band will be playing Pop and Rock & Roll.


Select data from 3 tables (all fields)

This will select all data from three tables using a common field. The common field will be shown more than once, since it appears in each table. There are different ways to do this. Here I will show two different commands to accomplish the same thing. You’ll find that sometimes one of them will be more appropriate to use than the other.

The first command uses “where” to specify the names of the common fields:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select * from TABLENAME1, TABLENAME2, TABLENAME3 where TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2 and TABLENAME1.COMMONFIELDNAME3=TABLENAME3.COMMONFIELDNAME4;

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • All instances of TABLENAME3 with the name of the third table.
    • COMMONFIELDNAME1 with the name of the field in the first table that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the field in the second table that it has in common with the first table.
    • COMMONFIELDNAME3 with the name of the field in the first table that it has in common with the third table.
    • COMMONFIELDNAME4 with the name of the field in the third table that it has in common with the first table.


Example:

Let’s say I want to see all data on my contacts in the example Address, Phone and Preferences tables. I would type this command:

select * from Address, Phone, Preferences where Address.contact=Phone.contact and Address.contact=Preferences.contact;

MySQL would show me all data in the three tables:

+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+
| contact | street        | city        | state | zip   | contact | homephone      | cellphone      | contact | color  | food    | music       |
+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 | Hugo    | (800) 111-1111 | (900) 111-1111 | Hugo    | Blue   | Cheese  | Disco       |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 | Lucas   | (800) 222-2222 | (900) 222-2222 | Lucas   | Pink   | Chicken | Pop         |
| Spencer | 3 Main Street | Salem       | OR    | 33333 | Spencer | (800) 333-3333 | (900) 333-3333 | Spencer | Orange | Pizza   | Rock & Roll |
+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+


The second command uses “on” to specify the names of the common fields:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select * from TABLENAME1 join TABLENAME2 on (TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2) join TABLENAME3 on (TABLENAME1.COMMONFIELDNAME3=TABLENAME3.COMMONFIELDNAME4);

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • All instances of TABLENAME3 with the name of the third table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.
    • COMMONFIELDNAME3 with the name of the first table’s field that it has in common with the third table.
    • COMMONFIELDNAME4 with the name of the third table’s field that it has in common with the first table.


Example:

Let’s say I want to see all data on my contacts in the example Address, Phone and Preferences tables. I would type this command:

select * from Address join Phone on (Address.contact=Phone.contact) join Preferences on (Address.contact=Preferences.contact);

MySQL would show me all data in the three tables:

+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+
| contact | street        | city        | state | zip   | contact | homephone      | cellphone      | contact | color  | food    | music       |
+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 | Hugo    | (800) 111-1111 | (900) 111-1111 | Hugo    | Blue   | Cheese  | Disco       |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 | Lucas   | (800) 222-2222 | (900) 222-2222 | Lucas   | Pink   | Chicken | Pop         |
| Spencer | 3 Main Street | Salem       | OR    | 33333 | Spencer | (800) 333-3333 | (900) 333-3333 | Spencer | Orange | Pizza   | Rock & Roll |
+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+


Select data from 3 tables (all fields with common field shown only once)

This will select all data from three tables with the common field being shown only once.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select * from TABLENAME1 join TABLENAME2 using (COMMONFIELDNAME1) join TABLENAME3 using (COMMONFIELDNAME2);

    Replace:

    • TABLENAME1 with the name of the first table.
    • TABLENAME2 with the name of the second table.
    • COMMONFIELDNAME1 with the name of the field the first and second table have in common.
    • TABLENAME3 with the name of the third table.
    • COMMONFIELDNAME2 with the name of the field the first and third table have in common.


Example:

Let’s say I want to see all data in the example Address, Phone and Preferences tables. Each table has the contact field, and I only want MySQL to display that field once. I would type this command:

select * from Address join Phone using (contact) join Preferences using (contact);

MySQL would then display all data from the three tables, but only show the contact field once:

+---------+---------------+-------------+-------+-------+----------------+----------------+--------+---------+-------------+
| contact | street        | city        | state | zip   | homephone      | cellphone      | color  | food    | music       |
+---------+---------------+-------------+-------+-------+----------------+----------------+--------+---------+-------------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 | (800) 111-1111 | (900) 111-1111 | Blue   | Cheese  | Disco       |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 | (800) 222-2222 | (900) 222-2222 | Pink   | Chicken | Pop         |
| Spencer | 3 Main Street | Salem       | OR    | 33333 | (800) 333-3333 | (900) 333-3333 | Orange | Pizza   | Rock & Roll |
+---------+---------------+-------------+-------+-------+----------------+----------------+--------+---------+-------------+


Select data from 3 tables (specific fields)

This will select all records in specific fields from three tables using a common field. There are different ways to do this. Here I will show three different commands to accomplish the same thing. You’ll find that sometimes one of them will be more appropriate to use than the others.

The first command uses “where” to specify the names of the common fields:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select TABLENAME1.FIELDNAME1, TABLENAME2.FIELDNAME2, TABLENAME3.FIELDNAME3 from TABLENAME1 join TABLENAME2 join TABLENAME3 where TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2 and TABLE1NAME.COMMONFIELDNAME3=TABLENAME3.COMMONFIELDNAME4;

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • FIELDNAME1 with the name of the field you’d like to select from the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • FIELDNAME2 with the name of the field you’d like to select from the second table.
    • All instances of TABLENAME3 with the name of the third table.
    • FIELDNAME3 with the name of the field you’d like to select from the third table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.
    • COMMONFIELDNAME3 with the name of the first table’s field that it has in common with the third table.
    • COMMONFIELDNAME4 with the name of the third table’s field that it has in common with the first table.

  • Note that each table’s common field can have a different name.


Example:

Let’s say I’m working with the example Address, Phone and Preferences tables and I need the street address, home phone number and favorite color of all of my contacts. I would type this command:

select Address.street, Phone.homephone, Preferences.color from Address join Phone join Preferences where Address.contact=Phone.contact and Address.contact=Preferences.contact;

MySQL would then display the information I requested on all of my contacts:

+---------------+----------------+--------+
| street        | homephone      | color  |
+---------------+----------------+--------+
| 1 Main Street | (800) 111-1111 | Blue   |
| 2 Main Street | (800) 222-2222 | Pink   |
| 3 Main Street | (800) 333-3333 | Orange |
+---------------+----------------+--------+


The second command uses “on” to specify the names of the common fields:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select TABLENAME1.FIELDNAME1, TABLENAME2.FIELDNAME2, TABLENAME3.FIELDNAME3 from TABLENAME1 join TABLENAME2 join TABLENAME3 on (TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2 and TABLENAME1.COMMONFIELDNAME3=TABLENAME3.COMMONFIELDNAME4);

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • FIELDNAME1 with the name of the field you’d like to select from the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • FIELDNAME2 with the name of the field you’d like to select from the second table.
    • All instances of TABLENAME3 with the name of the third table.
    • FIELDNAME3 with the name of the field you’d like to select from the third table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.
    • COMMONFIELDNAME3 with the name of the first table’s field that it has in common with the third table.
    • COMMONFIELDNAME4 with the name of the third table’s field that it has in common with the first table.

  • Note that each table’s common field can have a different name.


Example:

Let’s say I’m working with the example Address, Phone and Preferences tables and I need the street address, home phone number and favorite color of all of my contacts. I would type this command:

select Address.street, Phone.homephone, Preferences.color from Address join Phone join Preferences on (Address.contact=Phone.contact and Address.contact=Preferences.contact);

MySQL would then display the information I requested on all of my contacts:

+---------------+----------------+--------+
| street        | homephone      | color  |
+---------------+----------------+--------+
| 1 Main Street | (800) 111-1111 | Blue   |
| 2 Main Street | (800) 222-2222 | Pink   |
| 3 Main Street | (800) 333-3333 | Orange |
+---------------+----------------+--------+


The third command uses “using” to specify the common field:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the data:
  4. select TABLENAME1.FIELDNAME1, TABLENAME2.FIELDNAME2, TABLENAME3.FIELDNAME3 from TABLENAME1 join TABLENAME2 using (COMMONFIELDNAME1) join TABLENAME3 using (COMMONFIELDNAME2);

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • FIELDNAME1 with the name of the field you’d like to select from the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • FIELDNAME2 with the name of the field you’d like to select from the second table.
    • All instances of TABLENAME3 with the name of the third table.
    • FIELDNAME3 with the name of the field you’d like to select from the third table.
    • COMMONFIELDNAME1 with the name of the field the first and second tables have in common.
    • COMMONFIELDNAME2 with the name of the field the first and third tables have in common.

  • Note that to use this command, each table’s common field must have the same name.


Example:

Let’s say I’m working with the example Address, Phone and Preferences tables and I need the street address, home phone number and favorite color of all of my contacts. I would type this command:

select Address.street, Phone.homephone, Preferences.color from Address join Phone using (contact) join Preferences using (contact);

MySQL would then display the information I requested on all of my contacts:

+---------------+----------------+--------+
| street        | homephone      | color  |
+---------------+----------------+--------+
| 1 Main Street | (800) 111-1111 | Blue   |
| 2 Main Street | (800) 222-2222 | Pink   |
| 3 Main Street | (800) 333-3333 | Orange |
+---------------+----------------+--------+


Select data from 3 tables (specific records)

This will select specific records in specific fields from three tables. A common field is used to ensure that the data is related.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to select the record(s):
  4. select FIELDNAME1, FIELDNAME2, FIELDNAME3 from TABLENAME1, TABLENAME2, TABLENAME3 where TABLENAME1.COMMONFIELD1=TABLENAME2.COMMONFIELD2 and TABLENAME1.COMMONFIELD3=TABLENAME3.COMMONFIELD4 and TABLENAME1.CRITERIONFIELDNAME="CRITERION";

    Replace:

    • FIELDNAME1 with the name of the field you’d like to select from the first table.
    • FIELDNAME2 with the name of the field you’d like to select from the second table.
    • FIELDNAME3 with the name of the field you’d like to select from the third table.
    • All instances of TABLENAME1 with the name of the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • All instances of TABLENAME3 with the name of the third table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.
    • COMMONFIELDNAME3 with the name of the first table’s field that it has in common with the third table.
    • COMMONFIELDNAME4 with the name of the third table’s field that it has in common with the first table.
    • CRITERIONFIELDNAME with the name of the field that contains the criterion to idenfity the record(s).
    • CRITERION with the data that must be in the field to identify the record(s).

  • Note that each table’s common field can have a different name.


Example:

Let’s say I keep tabs on which musical performers are coming to the various locations my contacts live in. There’s a disco band coming to Mississippi, so I need to know which of my contacts like disco and live in Mississippi, and I’ll need their phone numbers. I could use this command to pull up the information about my contacts from the example Address, Phone and Preferences tables:

select state, homephone, music from Address, Phone, Preferences where Address.contact=Phone.contact and Address.contact=Preferences.contact and Preferences.music="Disco";

MySQL would let me know the phone numbers of the contacts I have listed who live in Mississippi and like disco:

+-------+----------------+-------+
| state | homephone      | music |
+-------+----------------+-------+
| MS    | (800) 111-1111 | Disco |
+-------+----------------+-------+


Select data from 3 tables (specific records that do not match the criterion)

This will select all data from specific fields, using a criterion to determine which records not to select.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command:
  4. select TABLENAME1.FIELDNAME1, TABLENAME1.FIELDNAME2, TABLENAME2.FIELDNAME3, TABLENAME2.FIELDNAME4, TABLENAME3.FIELDNAME5, TABLENAME3.FIELDNAME6 from TABLENAME1, TABLENAME2, TABLENAME3 where TABLENAME1.COMMONFIELDNAME1=TABLENAME2.COMMONFIELDNAME2 and TABLENAME1.COMMONFIELDNAME3=TABLENAME3.COMMONFIELDNAME4 and CRITERIONTABLENAME.CRITERIONFIELDNAME!="CRITERION";

    Replace:

    • All instances of TABLENAME1 with the name of the first table.
    • FIELDNAME1 with the name of the first field you’d like to select from the first table.
    • FIELDNAME2 with the name of the second field you’d like to select from the first table.
    • All instances of TABLENAME2 with the name of the second table.
    • FIELDNAME3 with the name of the first field you’d like to select from the second table.
    • FIELDNAME4 with the name of the second field you’d like to select from the second table.
    • All instances of TABLENAME3 with the name of the third table.
    • FIELDNAME5 with the name of the first field you’d like to select from the third table.
    • FIELDNAME6 with the name of the second field you’d like to select from the third table.
    • COMMONFIELDNAME1 with the name of the first table’s field that it has in common with the second table.
    • COMMONFIELDNAME2 with the name of the second table’s field that it has in common with the first table.
    • COMMONFIELDNAME3 with the name of the first table’s field that it has in common with the third table.
    • COMMONFIELDNAME4 with the name of the third table’s field that it has in common with the first table.
    • CRITERIONTABLENAME with the name of the table that contains the criterion to idenfity the record(s).
    • CRITERIONFIELDNAME with the name of the field that contains the criterion to idenfity the record(s).
    • CRITERION with the data that must be in the field to identify the record(s) not to select.

  • Note that you can add as many additional fields as you like by separating them from the last one with a comma and a space, and by following the format of TABLENAME.FIELDNAME.


Example:

Let’s say I’m throwing a party at each of my corporate offices and I’d like to invite some of the contacts from the example database. Since the parties will be held in different locations, I’ll need the names and locations of my contacts, their phone numbers so I can invite them, and I’ll want to know what kind of food and music they like. For whatever reason, I do not want anyone to attend who likes disco, so I’ll want to specify that those pople not be invited. I would type this command to pull up the information from the example Address, Phone and Preferences tables:

select Address.contact, Address.state, Phone.homephone, Phone.cellphone, Preferences.food, Preferences.music from Address, Phone, Preferences where Address.contact=Phone.contact and Address.contact=Preferences.contact and Preferences.music!="disco";

MySQL would show me the information I need on all the contacts I have that don’t like disco:

+---------+-------+----------------+----------------+---------+-------------+
| contact | state | homephone      | cellphone      | food    | music       |
+---------+-------+----------------+----------------+---------+-------------+
| Lucas   | IL    | (800) 222-2222 | (900) 222-2222 | Chicken | Pop         |
| Spencer | OR    | (800) 333-3333 | (900) 333-3333 | Pizza   | Rock & Roll |
+---------+-------+----------------+----------------+---------+-------------+


Sort selected data by field

To specify a field to sort your selection by, you can add the order by statement to your select command. This can be done with almost any select command.

The default sort order for selected data is ascending.

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to sort the data:
  4. select FIELDNAME1, FIELDNAME2 from TABLENAME order by FIELDNAME;

    Replace:

    • FIELDNAME1 with the name of the first field.
    • FIELDNAME2 with the name of the second field.
    • TABLENAME with the name of the table you’d like to select data from.
    • FIELDNAME with the name of the field you’d like to use to determine the sort order.


Example:

Let’s say I want to select all the contacts in the example Address table and sort them by the contact field. I would type this command:

select contact, city from Address order by contact;

My selection would look like this:

+---------+-------------+
| contact | city        |
+---------+-------------+
| Hugo    | Jackson     |
| Lucas   | Springfield |
| Spencer | Salem       |
+---------+-------------+

Let’s say I want to select all the contacts in the example Address table and sort them by the city field. I would type this command:

select contact, city from Address order by city;

My selection would look like this:

+---------+-------------+
| contact | city        |
+---------+-------------+
| Hugo    | Jackson     |
| Spencer | Salem       |
| Lucas   | Springfield |
+---------+-------------+


To sort the selection in reverse (descending) order, you need to add desc to the order by statement:

  1. Make sure you’re logged on.
  2. Choose the database you want to work with.
  3. Type this command to sort the data in reverse:
  4. select FIELDNAME1, FIELDNAME2 from TABLENAME order by FIELDNAME desc;

    Replace:

    • FIELDNAME1 with the name of the first field you’d like to select.
    • FIELDNAME2 with the name of the second field you’d like to select.
    • TABLENAME with the name of the table.
    • FIELDNAME with the name of the field you’d like to use to sort the output by.


Example:

Let’s say I want to select the contact and street fields from the example Address table. I would type this command:

select contact, street from Address;

My selection would look like this:

+---------+---------------+
| contact | street        |
+---------+---------------+
| Hugo    | 1 Main Street |
| Lucas   | 2 Main Street |
| Spencer | 3 Main Street |
+---------+---------------+

If I’d like to sort the selection in reverse using the contact field, I would type this command:

select contact, street from Address order by contact desc;

My selection would then look like this:

+---------+---------------+
| contact | street        |
+---------+---------------+
| Spencer | 3 Main Street |
| Lucas   | 2 Main Street |
| Hugo    | 1 Main Street |
+---------+---------------+


Appendix A


Terminology

This section contains detailed descriptions of the terms used in this guide.

Attribute

Attributes can be specified when creating fields. They let MySQL know additional information about certain field types, like how many characters wide to make a field or where to place the decimal point.

IMPORTANT
Determining the width of a field often involves guessing,
but try to get as close as possible without coming up short.

Some field types, like date and time, require no attributes.

Auto_increment

Auto_increment can be specified when creating fields. If a field is specified as using auto_increment, MySQL will insert a number in that field for you when you add records to the table. Each number will be one higher than the highest existing number in the field, and MySQL assumes by default in an empty table that the highest existing number is 0, so the first record will start with 1.

You can define a different number for MySQL to start the count with. The simplest way to do this is by inserting a dummy record into the table with the number in the auto_increment field one number lower than the number you’d like MySQL to begin the count with and then deleting that record. Note that if you do this in a table that has existing records in it, the existing records will not be renumbered for you by MySQL.

One other thing to note is that if you insert an auto_increment field into a table with existing records in it, MySQL will fill in the auto_increment field for you automatically, beginning with 1 for the first existing record, 2 for the second existing record, and so on. If you don’t want the count to start with 1, you’ll need to manually renumber those records. In this case you would not need to additionally add a dummy record. MySQL will continue the count from the highest number you manually placed in the field.

Cell

A cell is the intersection of a record and a field.

+----------+----------+---------------+----------+
|          |          |               |          |
+----------+----------+---------------+----------+
|          |          |This is a cell |          |
+----------+----------+---------------+----------+
|          |          |               |          |
+----------+----------+---------------+----------+
|          |          |               |          |
+----------+----------+---------------+----------+

Data

Information.

Database

A database is a structured form of data storage.

Field

A field is a column in a table. There are different types of fields, and many of them have attributes. You’ll often see this represented by FIELD TYPE(ATTRIBUTES) in this guide. Fields can have additional specifications like null, not null, index, unique, primary key and foreign key.

+----------+----------+----------+----------+
|          |          |  This    |          |
+----------+----------+----------+----------+
|          |          |   is     |          |
+----------+----------+----------+----------+
|          |          |    a     |          |
+----------+----------+----------+----------+
|          |          |  field   |          |
+----------+----------+----------+----------+

Foreign Key

A foreign key is a field that is linked to the primary key field of any other table in the same database, which will guarantee that the data in the table with the foreign key field relates to the data in the other table. This relationship is a parent/child relationship, with the table with the foreign key as the child table and the table with the primary key it links to as the parent table.

When adding records to the child table, you are required to fill in the foreign key field with data from the primary key field of the parent table. Think of the parent table’s primary key field as a mandatory “pick list” for the child table’s foreign key field.

Note:

  • Both tables must use the InnoDB engine (this is specified in the command).
  • The fields used to link the two tables must be the same type.
  • You may not delete a parent table if there are child tables depending on it.

Import

Import refers to inserting data from a .csv, .txt or .sql file into a table or database.

Index

An index is a special file created by MySQL to store records in a field. MySQL uses indexes to find records faster than by reading through all the records in a table. If you have a table with a number of fields and you frequently look up information in one of them, those searches will go faster if that field is indexed.

MySQL stores the records in your table and the indexes you define for your table in separate files. When doing data selection, instead of having to read through all of your table records, MySQL will use the primary key index, followed by any unique and non-unique indexes you’ve defined, which can improve performance.

MySQL

MySQL is a relational database management system (RDBMS) which runs as a server.

MySQL database

A MySQL database is a container for a structured collection of data which is stored in tables. It can consist of a single table, independent tables, or relational tables that can be combined in an interactive way.

Null

To MySQL, the null placeholder means “a missing unknown value.” Null is not a string (word or integer). It is an entity representing absence of data. This entity appears as the word NULL or the integer 0 in empty, non-required fields. For example:

+----------+----------------+----------------+
| contact  | homephone      | cellphone      |
+----------+----------------+----------------+
| Hugo     | (800) 111-1111 | NULL           |
+----------+----------------+----------------+

MySQL considers all fields null by default. This means that you are free to put data into a field or not, and MySQL will fill any fields you leave empty with the NULL placeholder to indicate to you that there’s no data in those fields.

You can manually insert the null placeholder into a field when adding a record. For example:

insert into TABLENAME (FIELDNAME) values (NULL);
IMPORTANT
If you wish to manually insert the null placeholder into a field when adding a record to a table,
it’s very important to not put quotes around NULL. If you accidentally add quotes, this will
insert the word NULL as a text string instead of the NULL placeholder. The table will look
normal, but will behave in unexpected ways during the insert and when you perform actions
which require MySQL to recognize and act on null fields.

Not null

If you’d like to specify that a field is required (must be filled in when adding a record to a table), you can use the not null specification when creating the field. If you then add a record without filling in that field, MySQL will either print a warning and leave the field completely empty or refuse to create the record at all, depending on the method used to add the record.

Primary Key

A primary key is a unique not null field or fields that is automatically indexed by MySQL. MySQL stores indexes and records in separate files. When doing data selection, MySQL will use the primary key index first, which can improve performance. Some important things to note about primary keys:

  • If you don’t specify not null when creating the primary key field, MySQL will do it for you automatically.
  • A primary key can be made of one or more fields.
  • A table can have only one primary key.
  • If your table has no primary key and an application asks for one, MySQL will use the first unique not null index as the primary key.

Record

A record is a single row of data in a table.

+----------+----------+----------+----------+
|          |          |          |          |
+----------+----------+----------+----------+
|          |          |          |          |
+----------+----------+----------+----------+
|  This    |    is    |    a     | record   |
+----------+----------+----------+----------+
|          |          |          |          |
+----------+----------+----------+----------+

Table

A table is data arranged in records and fields. Before creating a table, there are a few things to consider. The create table command needs a table name and at least one field name, type, and possibly an attribute or attributes. Other values can also optionally be defined. Please make sure you understand these terms:

Type

When you choose a type of field, you are telling MySQL what kind of data you’ll be putting into the field. Some of the more commonly used field types are:

  • blob – For storage of large binary files.
  • char – For a fixed number of characters.
  • date – For dates entered in YYYY-MM-DD format.
  • decimal – For decimal numbers.
  • int – For normal-size integers.
  • text – For large ASCII files.
  • time – For time entered in 00:00:00 format.
  • tinyint – For very small integers.
  • varchar – For a variable number of characters from 1 to 65535.

See the MySQL manual for your version at http://dev.mysql.com/doc/ for information on all available types.

Unique

You can specify that a field is unique when creating or editing a table. What this means is that MySQL will not let you insert duplicate values into the specified field.

Value

The data in a field.

Appendix B


Example Database

The example database is named People. It contains three tables: Address, Phone and Preferences. I highly recommend that you create this database on your system so that you can try out the steps that are demonstrated in this guide for yourself.

The Address table:

+---------+---------------+-------------+-------+-------+
| contact | street        | city        | state | zip   |
+---------+---------------+-------------+-------+-------+
| Hugo    | 1 Main Street | Jackson     | MS    | 11111 |
| Lucas   | 2 Main Street | Springfield | IL    | 22222 |
| Spencer | 3 Main Street | Salem       | OR    | 33333 |
+---------+---------------+-------------+-------+-------+

The Phone table:

+---------+----------------+----------------+
| contact | homephone      | cellphone      |
+---------+----------------+----------------+
| Hugo    | (800) 111-1111 | (900) 111-1111 |
| Lucas   | (800) 222-2222 | (900) 222-2222 |
| Spencer | (800) 333-3333 | (900) 333-3333 |
+---------+----------------+----------------+

The Preferences table:

+---------+--------+---------+-------------+
| contact | color  | food    | music       |
+---------+--------+---------+-------------+
| Hugo    | Blue   | Cheese  | Disco       |
| Lucas   | Pink   | Chicken | Pop         |
| Spencer | Orange | Pizza   | Rock & Roll |
+---------+--------+---------+-------------+

Create the example database

  1. Make sure you’re logged on.
  2. Type this command to create the database:
  3. create database if not exists People;
  4. Choose to use the newly created database:
  5. use People;
  6. Type this command to create the Address table:
  7. create table Address (contact varchar(40) not null, street varchar(40), city varchar(20), state char(2), zip int(5));
  8. Type this command to create the Phone table:
  9. create table Phone (contact varchar(40) not null, homephone varchar(14), cellphone varchar(14));
  10. Type this command to create the Preferences table:
  11. create table Preferences (contact varchar(40) not null, color varchar(8), food varchar(12), music varchar(12));
  12. Type this command to add three records to the Address table:
  13. insert into Address values ("Hugo", "1 Main Street", "Jackson", "MS", "11111"), ("Lucas", "2 Main Street", "Springfield", "IL", "22222"), ("Spencer", "3 Main Street", "Salem", "OR", "33333");
  14. Type this command to add three records to the Phone table:
  15. insert into Phone values ("Hugo", "(800) 111-1111", "(900) 111-1111"), ("Lucas", "(800) 222-2222", "(900) 222-2222"), ("Spencer", "(800) 333-3333", "(900) 333-3333");
  16. Type this command to add three records to the Preferences table:
  17. insert into Preferences values ("Hugo", "Blue", "Cheese", "Disco"), ("Lucas", "Pink", "Chicken", "Pop"), ("Spencer", "Orange", "Pizza", "Rock & Roll");

Appendix C

Further Information

Questions and Answers

I have a new question. How do I ask it?

If you use the contact address at the top of this document, you will receive a reply via e-mail and your question may be added to this section.

Troubleshooting

  • Make sure that you meet the requirements in the Requirements section.
  • If a command isn’t working, it may be because you didn’t complete it with a semicolon. If you are faced with a -> prompt after typing in a command, this indicates that MySQL is waiting for more input and doesn’t consider the command complete yet. Try making sure the command is entered correctly. If so, try adding ; for tabular outpur or \G for block output at the -> prompt to let MySQL know you’re done typing the command.
  • If a command isn’t working, it may be because you don’t have the correct syntax. Verify the exact structure of the command and make sure the command you’re trying is correct.
  • If a command isn’t working, it may be because you have the case wrong. MySQL can be particular, so pay attention to upper and lower case letters in your databases and tables.
  • If a command isn’t working, it may be because you’re trying to enter the wrong kind of data into a record. If, for example, a field is defined as an integer field and you try to place anything other than a number into that field, it will not work.
  • If a command isn’t working, it may be because you are referring to a nonexistent database, table or field. You may have forgotten to choose the correct database to work with, and still be in another database. To check which database you’re currently using, follow the instructions in the Display current database section. If you’re unsure which database you should be working with, you may need to use a combination of displaying all databases, displaying all tables in the database you’re currently in or any other database you think the table might be in, and/or display all data in the table you think the field might be in.
  • If a command isn’t working, it may be because you’re using a reserved word. MySQL reserves some words for its own use which it will reject if you attempt to use them in naming. More information can be found here: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html.
  • If you need more help, see my GettingHelp page.



Obligatory Happy Ending

And they all lived happily ever after. The end.

Back to top

Creative Commons License Content on this page is Copyright © 2007 – 2014 by Little Girl, licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 United States License, and offered free of charge without warranty of any kind, either expressed or implied.

Advertisements

1 Comment »

  1. thanks for the post

    Comment by linuxhospital — October 10, 2012 @ 10:32 am


RSS feed for comments on this post. TrackBack URI

Comment:

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: