MySQL

MySQL Basic fundamentals

A Basic MySQL Tutorial

 mysql-logo

 Introduction: MySQL

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.

mysql

 

 

1.2 command line install

  /usr/local/etc/rc.d.

 

  • The ability to use pkg_info -L to see which files are installed.
  • The ability to use pkg_delete to 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”.

 

Centos:

sudo yum install mysql-server
/etc/init.d/mysqld start
 1.3  As shown in the image above, you can visit mysql and download the right software for the OS that you are using.

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:

SHOW DATABASES;

Your screen should look something like this:

mysql table

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

mysql create db 2

to delete objects is Drop. You would delete a MySQL database with this command:

 DROP DATABASE database name;
  my del db 3

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:

 USE archers;

In the same way that you could check the available databases, you can also see an overview of the tables that the database contains.

 SHOW tables;

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:

  1. It has created a table called Roster within the directory of Archers.
  2. We have set up 2 columns in the table—id, name, and address.
  3. The “id” column has a command (INT NOT NULL PRIMARY KEY AUTO_INCREMENT) that automatically numbers each row.
  4. The “name” column has been limited by the VARCHAR command to be under 20 characters long.
  5. The ” address” column has a limit of 30 characters within each row.
  6. Let’s take a look at the created table now using “show tables;” command and you will see the new content added.
db table created

 

We can remind ourselves about the table’s organization with this command:

 

 DESCRIBE archers;

 

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.

 

  db describe1

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

INSERT INTO

When you want to check a tables content, use this command:

 

********SIDE NOTE: If you encounter an error ,start by checking your syntax.

insert table 1 error edited

Let’s add more people to our table:

insert table ex 3 edited
  Select *  from _tablename_
Can be used to check your table contents

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

add row mysql

 

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;

 

drop row mysql

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.

delete row mysql final

and when you check the table to confirm the row is deleted it will look like this.

delete row 2 final

Observe that the ID does not change when deleting a row.

Related Articles

  • Reset MySQL Password

    Resetting a MySQL Root Password is one of the easiest things to do. However, finding documentation on how to do so can be challenging.   We as system administrators, have...
  • How can I convert my MyISAM tables to InnoDB?

    If you want to convert a MyISAM table to InnoDB, the process is fairly easy, but you can do something extra to speed things up. Before converting the table, adjust...
  • How to Display MySQL Storage Engines

    How to Display MySQL Storage Engines From the MySQL command line, issue the following command: SHOW ENGINES; You should receive a result similar to:   Typically the default storage engine...
  • LAMP STACK

    What is a LAMP Stack? “LAMP stack is a popular open source web platform commonly used to run dynamic web sites and servers. It includes Linux, Apache, MySQL, and PHP/Python/Perl...