A Basic MySQL Tutorial
MySQL is an open source database management software that helps users store, organize, and retrieve data. It is a very powerful program with a lot of flexibility—this tutorial will provide the simplest introduction to MySQL . Many of the world’s largest and fastest-growing organizations including Facebook, Google, Adobe, Alcatel Lucent and Zappos rely on MySQL to save time and money powering their high-volume websites, business-critical systems and packaged software.
1.1 How to Install MySQL on FreeBSD ,CentOS, and other platform.
FreeBSD: There is a few ways on how to install Mysql on FreeBSD .
1.1 – One way is from the MySQL community server that allows you to download a zip,tar for a variety to OS’s .
Visit MySQL.com at http://dev.mysql.com/downloads/cluster/
for a list of platforms. Here is what the screen will look like.
1.2 command line install
- The ability to use
pkg_info -Lto see which files are installed.
- The ability to use
pkg_deleteto remove MySQL if you no longer want it on your machine.
- The MySQL build process requires GNU make (gmake) to work. If GNU make is not available, you must install it first before compiling MySQL.To install using the ports system:
# cd /usr/ports/databases/mysql51-server # make … # cd /usr/ports/databases/mysql51-client # make …
- The default port installation places to the server is
/usr/local/libexec/mysqld, with the startup script for the MySQL server placed in
/usr/local/etc/rc.d/mysql-server.Some additional notes on the BSD implementation:
- For removing MySQL after installation using the ports system:
# cd /usr/ports/databases/mysql51-server # make deinstall ... # cd /usr/ports/databases/mysql51-client # make deinstall ...
If you need some troubleshooting help you can get more information from setting the
TZ variable should help. See Section 2.14, “Environment Variables”.
sudo yum install mysql-server /etc/init.d/mysqld start
1.2 Using the MySQL shell
Once you have MySQL installed you can access the MySQL shell by typing the following command into terminal:
mysql -u root -p
When entering MySQL you may get access to input the root MySQL password into the prompt you will be able to start building your MySQL database.
Note: Note that all text commands must be first on line and end with ‘ ; ‘
1.3 Creating and Deleting a MySQL Database
MySQL organizes its information into databases; each one can hold tables with specific data.
You can quickly check what databases are available using the command line functions:
Your screen should look something like this:
Creating a database is very easy and they are the same for Windows or Linux command line.
CREATE DATABASE database name;
As an example, I will call the created database “archers.”
to delete objects is Drop. You would delete a MySQL database with this command:
DROP DATABASE database name;
How to Access a MySQL Database
Once we have a new database, we can begin to fill it with information.
The first step is to create a new table within the larger database.
Let’s open up the database we want to use:
In the same way that you could check the available databases, you can also see an overview of the tables that the database contains.
Since this is a new database, MySQL has nothing to show, and you will get a message that says, “Empty set”
How to Create a MySQL Table
Let’s imagine that we are planning a get together of friends. We can use MySQL to track the details of the event.
Let’s create a new MySQL table:
CREATE TABLE Rosters (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20),address(30));
This command accomplishes a number of things:
- It has created a table called Roster within the directory of Archers.
- We have set up 2 columns in the table—id, name, and address.
- The “id” column has a command (INT NOT NULL PRIMARY KEY AUTO_INCREMENT) that automatically numbers each row.
- The “name” column has been limited by the VARCHAR command to be under 20 characters long.
- The ” address” column has a limit of 30 characters within each row.
- Let’s take a look at the created table now using “show tables;” command and you will see the new content added.
We can remind ourselves about the table’s organization with this command:
remember MySQL command line does not pay attention to cases, however the table and database names are case sensitive so archers is not the same as ARCHERS or Archers.
How to Add Information to a MySQL Table
Adding information to a table can be easy but you can also encounter errors easily if you input the wrong syntax for a command.
Below is and (example 1) of the command line input and results NULL
When you want to check a tables content, use this command:
********SIDE NOTE: If you encounter an error ,start by checking your syntax.
Let’s add more people to our table:
How to Add and Delete a Column
We are creating a handy chart, but it is missing some important information: our attendees’ emails.
We can easily add this:
ALTER TABLE potluck ADD email VARCHAR(40);
This command puts the new column called “email” at the end of the table by default, and the VARCHAR command limits it to 40 characters.
However, if you need to place that column in a specific spot in the table, we can add one more phrase to the command.
ALTER TABLE Roster ADD email VARCHAR after name(40);
Now the new “email” column goes after the column “name”.
Just as you can add a column, you can delete one as well:
ALTER TABLE Roster DROP email;
How to Delete a Row
When you want to drop a row of content, the commands will be:
DELETE from [table name] where [column name]=[field text];
In this case I want to delete Fransico because the name was spelled incorrectly.
Observe that the ID does not change when deleting a row.