How to create databases and tables

schema to database

1. We will begin our database and table creation process with the following command at the mysql> prompt: create database computers;

This will create a database called “computers”, and you of course should feel free to substitute the database "computers", and the  table "models" as desired.

mysql> create database computers;

2. Use the “show databases” command to verify that the database was created successfully.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema
| mysql              |
| computers       |
+--------------------+
3 rows in set (0.00 sec)

3. We are now ready to create our MySQL table. However, prior to doing this be certain to invoke the “use” command before performing operations on your database.

mysql> use computers;
Database changed

4. You will then perform the commands below to create the "models" table, with "manuf" being manufacturer, "form"denoting form factor, msrp being manufacturer's suggested retail price, and "cont_r" designating the contact representative, and the rest being self explanatory.

Therefore to create our "models" table, we invoke:

create table models (
id INT(10) AUTO_INCREMENT PRIMARY KEY,
manuf varchar(255),
form varchar(10),
year int(10)
msrp int(10)
store varchar(255)
cont_r varchar(255)
);

You would enter the commands in the following manner after the mysql> prompt. As you enter your commands, you will see a -> prompt, continue to enter your commands until you have entered all of your columns, once you have done that, you will then terminate your command entry process with a semi-colon.

mysql> create table models (
    -> id INT(10) AUTO_INCREMENT PRIMARY KEY,
    -> manuf varchar(255),
    -> form varchar(10),
    -> year int(10),
    -> msrp int(10),
    -> store varchar(255),
    -> cont_r varchar(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

5. To view the tables in the computers database, invoke the following:

mysql> show tables;
+------------------------+
| Tables_in_computers |
+------------------------+
| models               |
+------------------------+
1 row in set (0.00 sec)

 6. To view the table description, perform the following command:

mysql> desc models;
+--------+-------------+------+-----+---------+----------------+
| Field    |     Type        |  Null  | Key | Default |   Extra               |
+--------+-------------+------+-----+---------+----------------+
| id         | int(10)          | NO   | PRI | NULL    | auto_increment  |
| manuf   | varchar(255) | YES |        | NULL    |                         |
| form     | varchar(10)   | YES  |       | NULL    |                         |
| year     | int(10)          | YES  |       | NULL    |                         |
| msrp    | int(10)          | YES  |       | NULL    |                         |
| store    | varchar(255) | YES  |       | NULL     |                         |
| cont_r  | varchar(255)  | YES |       | NULL     |                         |
+--------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

7. Now you will begin to insert your records into your "models" table. To accomplish this you will enter your values in the order that they are in as dictated by your column order. Therefore "id" is 124, "manuf" is 'Asus', "form" is designated by'Server'and they and the other strings are in single quotes. This is because without quotes ,backquotes or backticks which appears as ` and is usually found with the tilde ~ key, MySQL will treat the word as a field name. Conversely, with single or double quotes, MySQL will designate the word as a string.

On the other hand, backquotes or backticks are used by MySQL to escape table names, therefore to use a reserved word, number or operator to be the name of an object such as a  table named “1″ or a column named “date” you need to use backquotes to avoid syntax errors. Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set. It is often recommended to avoid using reserved keywords as column or table identifiers when possible, thereby avoiding dilemna of quoting in its' entirety.

Single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, however single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.

Therefore to insert values into our columns, we would invoke the following:

insert into models values ('124','Asus','Server','2013','1000','Newegg','Jones');
insert into models values ('125','Compaq','Desktop','2013','500','Amazon','Sanchez');
insert into models values ('126','Dell','Server','2013','6000','Dell','Kline');
insert into models values ('127','Emachine','Netbook','2013','200','Bestbuy','Anwar');
insert into models values ('128','Gateway','Laptop','2013','600','Microcenter,'Xuyen');

Alternatively you could enter the fields and their values, thusly:

INSERT INTO models (id, manuf, form, year, msrp, store, cont_r)
 VALUES ('124','Asus','Server','2013','1000','Newegg','Jones');

If we desired, we could insert values into a specific column, and would specify the column names thusly:

mysql> insert into models(manuf,cont_r) values('Asus', 'Jones');
Query OK, 1 row affected (0.01 sec)

By the by, another related question that you may have is "must you capitalize MySQL commands?" The answer is that while capitalizing your MySQL commands may make your code easier to read, you do not have to capitalize your MySQL commands.

8. Finally, to see our table and the records in it, we would invoke:

mysql> SELECT * FROM MODELS;