DatabaseManagement

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

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

flush privileges;

set password=password('peppermint');

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

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

use mysql;

delete from user where user='Mel';

flush privileges;

cat ~/.mysql_history

create database if not exists People;

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

create database if not exists People2;

Query OK, 1 row affected (0.01 sec)

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

describe Phone;

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

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

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

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

select * from Phone;

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

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

select * from Phone;

+---------+----------------+----------------+
| 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           |
+---------+----------------+----------------+

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

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

describe Phone;

+-----------+-------------+------+-----+---------+-------+
| 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)

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

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

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

select * from Phone;

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

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

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

+---------+----------------+----------------+
| 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           |
+---------+----------------+----------------+

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

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

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

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));

+--------+---------+---------------+-------------+-------+-------+
| 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 |
+--------+---------+---------------+-------------+-------+-------+

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

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));

+----+---------+---------------+-------------+-------+-------+
| 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 table Table1 (code tinyint(1) not null primary key, grade char(6) not null) engine=innodb;

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

select * from Table1;

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

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

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

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

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

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`))

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

select * from Table2;

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

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

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

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

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

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

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

select * from Table1;

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

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;

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");

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

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`))

select * from Table2;

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

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

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

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

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

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

show indexes from Phone;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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 table Phone (contact varchar(40) not null, homephone varchar(14), cellphone varchar(14), index (contact, homephone));

show indexes from Phone;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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 table Phone (contact varchar(40) unique, homephone varchar(14), cellphone varchar(14), index (contact));

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");

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

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

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

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");

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

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

update Phone set homephone="carview.php?tsp=" where homephone="(800) 111-1111";

update Phone set homephone="carview.php?tsp=" where contact="Hugo";

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

drop database if exists People;

alter table Phone drop column cellphone;

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

delete from Address where contact="Hugo";

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

use People;

drop table Phone;

use People;

drop table Phone;

select * from Address;

+---------+---------------+-------------+-------+-------+
| 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)

select * from Address\G

*************************** 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)

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

show indexes from Phone;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

describe Phone;

+-----------+-------------+------+-----+---------+-------+
| 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)

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

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:

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.

+----------+------------+----------------+----------------+
| 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 |
+----------+------------+----------------+----------------+

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

+----------+------------+----------------+----------------+
| 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 |
+----------+------------+----------------+----------------+

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:

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.

create index index_street on Address(street);

show indexes from Address;

+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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)

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:

show indexes from TABLENAME;

Replace:

• TABLENAME with the name of the table.




4. Type this command:

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.

show indexes from Address;

+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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)

alter table Address drop index index_street;

show indexes from Address;

  Empty set (0.00 sec)

select * from Address;

+---------+---------------+-------------+-------+-------+
| 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)

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:

alter table TABLENAME add unique (FIELDNAME);

Replace:

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

alter table Address add unique (contact);

alter table Address add unique (contact);

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

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:

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

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:

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.

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

"Hugo"
"Lucas"
"Spencer"

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:

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.

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

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

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:

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.

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

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

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:

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';

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

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';

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

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:

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.

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:

mysqladmin create DATABASENAME -p

Replace:

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




2. Provide your MySQL password.
3. Type this command to import the .sql file into the database:

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.




4. Provide your MySQL password.

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.

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:

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.

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

use People;

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

+---------+---------------+-------------+-------+-------+
| 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 |
+---------+---------------+-------------+-------+-------+

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:

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.

alter table Phone add identification tinyint(3) first;

+----------------+---------+----------------+----------------+
| 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 |
+----------------+---------+----------------+----------------+

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:

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.

alter table Phone add workphone varchar(14);

+---------+----------------+----------------+----------------+
| 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           |
+---------+----------------+----------------+----------------+

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:

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.

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

+---------+----------------+--------------+----------------+
| 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 |
+---------+----------------+--------------+----------------+

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:

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.

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

+----+---------+----------------+----------------+
| 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 |
+----+---------+----------------+----------------+

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:

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.

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

+---------+----------------+----------------+
| 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           |
+---------+----------------+----------------+

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:

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.

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

+---------+----------------+----------------+
| 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           |
+---------+----------------+----------------+

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:

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.

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

select * from Address;

+---------+---------------+-------------+-------+-------+
| 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 |
+---------+---------------+-------------+-------+-------+

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:

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.

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

select * from Address;

+---------+---------------+-------------+-------+-------+
| 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 |
+---------+---------------+-------------+-------+-------+

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:

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.

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

select * from Phone;

+---------+----------------+----------------+
| 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 |
+---------+----------------+----------------+

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:

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.

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");

select * from Address;

+---------+---------------+-------------+-------+-------+
| 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 |
+---------+---------------+-------------+-------+-------+

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:

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.

2. Type this command to create a new database:

mysqladmin -u USERNAME -p create NEWDATABASENAME

Replace:

• USERNAME with your MySQL username.
• NEWDATABASENAME with the new name you’d like your database to have.

3. Type this command to import the .sql file into the new database:

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.

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:

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.

alter table Phone change homephone workphone varchar(14);

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:

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.

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:

select * from TABLENAME;

Replace:

• TABLENAME with the name of the table.

select * from Phone;

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

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:

select FIELDNAME from TABLENAME;

Replace:

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

select contact from Phone;

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

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:

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.

select contact, cellphone from Phone;

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

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:

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.

select cellphone from Phone where contact="Hugo";

+----------------+
| 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:

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).

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

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

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:

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.

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

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

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:

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.

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

+---------+---------------+-------------+-------+-------+---------+----------------+----------------+
| 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 |
+---------+---------------+-------------+-------+-------+---------+----------------+----------------+

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:

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.

select * from Phone join Preferences using (contact);

+---------+----------------+----------------+--------+---------+-------------+
| 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 |
+---------+----------------+----------------+--------+---------+-------------+

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:

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.

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

+---------+----------------+
| 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:

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.

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

+---------+----------------+
| 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:

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.

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

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

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:

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.

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

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

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:

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.

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

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

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:

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.

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

+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+
| 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:

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.

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

+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+
| 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 |
+---------+---------------+-------------+-------+-------+---------+----------------+----------------+---------+--------+---------+-------------+

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:

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.

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

+---------+---------------+-------------+-------+-------+----------------+----------------+--------+---------+-------------+
| 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 |
+---------+---------------+-------------+-------+-------+----------------+----------------+--------+---------+-------------+

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:

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.

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

+---------------+----------------+--------+
| 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:

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.

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

+---------------+----------------+--------+
| 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:

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.

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

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

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):

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.

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

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

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:

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.

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";

+---------+-------+----------------+----------------+---------+-------------+
| 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 |
+---------+-------+----------------+----------------+---------+-------------+

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:

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.

select contact, city from Address order by contact;

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

select contact, city from Address order by city;

+---------+-------------+
| 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:

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.

select contact, street from Address;

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

select contact, street from Address order by contact desc;

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

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

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.

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.

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

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

Information.

A database is a structured form of data storage.

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   |          |
+----------+----------+----------+----------+

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 refers to inserting data from a .csv, .txt or .sql file into a table or database.

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 is a relational database management system (RDBMS) which runs as a server.

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.

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);

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.

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.

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

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

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:

• attribute
• auto_increment
• cell
• field
• foreign key
• index
• null
• not null
• primary key
• record
• type
• unique

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 https://dev.mysql.com/doc/ for information on all available types.

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.

The data in a field.

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 |
+---------+--------+---------+-------------+

1. Make sure you’re logged on.
2. Type this command to create the database:

create database if not exists People;

3. Choose to use the newly created database:

use People;

4. Type this command to create the Address table:

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

5. Type this command to create the Phone table:

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

6. Type this command to create the Preferences table:

create table Preferences (contact varchar(40) not null, color varchar(8), food varchar(12), music varchar(12));

7. Type this command to add three records to the Address table:

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");

8. Type this command to add three records to the Phone table:

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");

9. Type this command to add three records to the Preferences table:

insert into Preferences values ("Hugo", "Blue", "Cheese", "Disco"), ("Lucas", "Pink", "Chicken", "Pop"), ("Spencer", "Orange", "Pizza", "Rock & Roll");

• To learn how to install Ubuntu Server 8.04 in VirtualBox under Kubuntu or Ubuntu, https://mostlylinux.wordpress.com/virtualbox/vbubuntuserver/ provides step-by-step instructions complete with screenshots.
• For some suggestions and instructions on what to do with Ubuntu Server once it’s installed in VirtualBox under Kubuntu or Ubuntu, https://mostlylinux.wordpress.com/virtualbox/vbwhatnext/ provides step-by-step instructions.
• The MySQL manual is at https://dev.mysql.com/doc/

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.

• 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: https://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

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.