MySQL
While there are many GUI methods to manage a database, such as PHPMyAdmin, the best way for programmers to get started in MySQL is to write the code in the command line. This will help greatly as you progress to writing your MySQL code in php to form the bridge between the server and the client.
The basic terminology used in database management includes the terms database, table and field. Think of a database as an Excel document. Each excel document has a variety of information and data that is, in some way, related. Inside the document, we often have several tabs, each of these represents a table. Each table has its own purpose and may refer to other tables, but can also stand on its own. Within each table, we often label the columns as to the specific information that is to be stored in the table. These column headers are the fields.
When working with MySQL in C9, we need to do a few things to get set up. In a terminal file, we need to start the sql server and enter the sql command client
<![CDATA[mysql-ctl start
mysql-ctl cli]]>
This will change the basic prompt to mysql>. To exit the client and stop the server we type
<![CDATA[quit;
mysql-ctl stop]]>
Database Commands
Before we can start implementing tables and fields, we need to learn how to create and manage our databases. Here are the basic database commands for MySQL:
- show databases; //shows a list of all databases
- create database dbName; //used to create database dbName
- use dbName; //make dbName the active database
- DROP DATABASE dbName; //deletes an entire database
So, typically, programmers will create a database, make sure that it is in the active records and then select the database to be used. That code may look like this:
<![CDATA[mysql> create database accounting;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| accounting |
| c9 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
mysql> use database accounting;
ERROR 1049 (42000): Unknown database 'database'
mysql> use accounting;
Database changed]]>
We are now ready to operate inside the accounting database.
Table Commands
Once operating in a database, we need to use the tables available to us. The following commands are useful in managing tables in a database
- show tables; //shows a list of all tables in the active database
- create table tblName(varname TYPE, varname TYPE, etc); //creates a table with elements
- describe tblName; //get information about the table
- DROP TABLE tblName; //deletes the entire table (structure and data)
- TRUNCATE TABLE tblName; //deletes all data from a table
It is important to note that you must assign field names and types at the time of creation. It is possible to alter the structure of the table at a later time if necessary, but it is better to plan ahead of time and create it originally with all necessary fields. Let's create the client table of our accounting database. We will first show the tables to make sure that it is not already created, and then we will create it with an abbreviated version of the contents. Finally, we will ask the server to describe the table.
<![CDATA[mysql> show tables;
Empty set (0.00 sec)
mysql> create table clients(
-> id int(5) unsigned not null primary key,
-> name tinytext not null,
-> bday date);
Query OK, 0 rows affected (0.05 sec)
mysql> describe clients;
+-------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id | int(5) unsigned | NO | PRI | NULL | |
| name | tinytext | NO | | NULL | |
| bday | date | YES | | NULL | |
+-------+-----------------+------+-----+---------+-------+
3 rows in set (0.02 sec)]]>
Field Types
| CHAR[length] | a fixed-length field from 0 to 255 characters |
| VARCHAR(length) | a fixed-length field from 0 to 255 characters |
| TINYTEXT | a string with a max length of 255 characters |
| TEXT | a string with a maximum length of 65,535 characters |
| MEDIUMTEXT | a string with a max length of 16, 777, 215 characters |
| LONGTEXT | a string with a max length of 4,294,967,295 characters |
| TINYINT[displayWidth] | -128 -> 127 |
| SMALLINT[displayWidth] | -32,768 -> 32,767 |
| MEDIUMINT[displayWidth] | -8,388,608 à 8,388,607 |
| INT[displayWidth] | -2,147,483,648 à 2,147,483,647 |
| BIGINT[displayWidth] | -9,223,372,036,854,775,808 à 9,223,372,036,854,775,807 |
| FLOAT | a small number with a floating decimal point |
| DOUBLE | a large number with a floating decimal point |
| DATE | in the format of YYYY-MM-DD |
| DATETIME | in the format of YYYY-MM-DD HH:MM:SS |
| TIMESTAMP | in the format of YYYYMMDDHHMMSS; up to year 2037 |
| TIME | in the format of HH:MM:SS |
Other values that can be used when defining field types are as follows:
- UNSIGNED - makes all numbers positive, used for number types
- ZEROFILL - fills any empty space with zeros, used for number types
- NOT NULL - prevents a field from being left empty
- DEFAULT - allows a default value to be entered if no other values are
- PRIMARY KEY - denotes the primary key for a table
- AUTO_INCREMENT - MySQL automatically creates the next logical value in sequence
Inserting Data into a Table
<![CDATA[INSERT INTO tableName VALUES (value1, value2, value3, etc.); //must list all values in order
INSERT INTO tableName (col1Name, col2Name, etc) VALUES (value1, value2, etc); //to enter only certain pieces of data]]>
When entering values:
- VARCHAR values need to be in single quotes
- Fields that are AUTO INCREMENT should be sent a value of NULL
- DATE values need to be entered in YYYY-MM-DD format with single quotes