DEVTOME.COM HOSTING COSTS HAVE BEGUN TO EXCEED 115$ MONTHLY. THE ADMINISTRATION IS NO LONGER ABLE TO HANDLE THE COST WITHOUT ASSISTANCE DUE TO THE RISING COST. THIS HAS BEEN OCCURRING FOR ALMOST A YEAR, BUT WE HAVE BEEN HANDLING IT FROM OUR OWN POCKETS. HOWEVER, WITH LITERALLY NO DONATIONS FOR THE PAST 2+ YEARS IT HAS DEPLETED THE BUDGET IN SHORT ORDER WITH THE INCREASE IN ACTIVITY ON THE SITE IN THE PAST 6 MONTHS. OUR CPU USAGE HAS BECOME TOO HIGH TO REMAIN ON A REASONABLE COSTING PLAN THAT WE COULD MAINTAIN. IF YOU WOULD LIKE TO SUPPORT THE DEVTOME PROJECT AND KEEP THE SITE UP/ALIVE PLEASE DONATE (EVEN IF ITS A SATOSHI) TO OUR DEVCOIN 1M4PCuMXvpWX6LHPkBEf3LJ2z1boZv4EQa OR OUR BTC WALLET 16eqEcqfw4zHUh2znvMcmRzGVwCn7CJLxR TO ALLOW US TO AFFORD THE HOSTING.

THE DEVCOIN AND DEVTOME PROJECTS ARE BOTH VERY IMPORTANT TO THE COMMUNITY. PLEASE CONTRIBUTE TO ITS FURTHER SUCCESS FOR ANOTHER 5 OR MORE YEARS!

Introduction

The goal of this tutorial is to show a novice how to use MySQL from the command line. It will assume that one have very little knowledge of MySQL. This tutorial will get the users started with some basic commands and show how to create and manipulated the database on a basic level. The aim is to get one up and running in MySQL.

What is MySQL?

MySQL is one of the most widely used relational databases used on the internet. The SQL part stands for Structured Query Language which is a programming language used to communicate and manipulate data in the database. While the “My” part came from the name of a co-founder’s daughter. Relational database means that the data is stored in separate tables that can be pulled together in different way. Learn MySQL is not only a must for any web developer but can be a powerful tool in any programmer skill set.

Basic Usage of MySQL

How to Connect to MySQL

Note: I will assume that you have successfully installed and created a user account on MySQL on your local computer. To connect to MySQL from the Command Line one must type the following: Mysql –h localhost –u user –p It will then ask you for a password, just enter the password and hit enter. You will see your terminal window display mysql> . If you see this then you have successfully connected to the database server. The “-h” in the above command stands for host. Since you are connected locally you just use localhost but if one is connecting remotely the parameter will have to be adjusted. The –u stands for the username of the account you want to connect to the database. While –p stands for password. The user account is usually set up when you installed MySQL onto the computer.

Creating Your First Database

To create a database in MySQL, you would use the CREATE DATABASE command. Below is an example command to create a database called “useraccount”. Just enter the following into your terminal once you have successfully connected to MySQL.

Create database useraccount;

If the command is successful you would see something like the following

Query OK, 1 row affected (0.08 sec)

This basically means the creation of the database was successful and it took 0.08 to create. If you see the above result than congratulations yourself as you have created your first database. Now that was not too hard.

Displaying the Databases in MySQL

To show all the databases within MySQL, you would use the SHOW DATABASE command. Just enter the following into the terminal window Mysql>show databases; If everything goes right you should see the following result.

+----------------------------------------------------+
| Databases                                          |
+----------------------------------------------------+
| information_schema                                 |
| performance_schema                                 |
| mysql                                              |
| useraccount                                        |
+----------------------------------------------------+
4 rows in set (0.01 sec)

As you can see, the database “useraccount” that you have created is there along with three other databases. I will not go into what the other three databases are as it is not important for the beginner.

Data Types in MySQL

Data Types defines what type of data is going to be stored in the database. There are many different data types used in MySQL but I will only go over the basic and most used data type in this tutorial.

Numeric Data Types

Integer Data Type

The first numeric data type is an integer. I would probably assume correctly that most would know what an integer is. It is basically a whole number (a number without a decimal place). For example, 466 is an integer but 566.55 is not. There are four basic integer types which is defined by the range of number it supports.

  1. SMALLINT - A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535
  2. MEDIUMINT - A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
  1. INT - A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
  1. BIGINT - A large integer. The signed range -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

The next type of numeric data type is the floating point (or decimal, you can used either word in MySQL) are basically real number (numbers with decimal point). And just like integers it has different flavors based on the size of the data to be stored.

Floating Point Data Type

  1. FLOAT - A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38
  2. DOUBLE - A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to - 2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308

Time and Date Data Type

Time and Date data types hold time and date data in the database and I will cover the four main time and date data types which are Time, Date, Timestamp and Datetime.

  1. Time - The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format,
  2. Date - A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format,
  3. Datetime - The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQLdisplays DATETIME values in 'YYYY-MM-DD HH:MM:SS'
  4. Timestamp The range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. TIMESTAMP values are stored as the number of seconds since the date of 1970-01-0.

String Data type

A string data type holds a sequence of characters. For example, ‘Matt’ and ‘joe55’ would be considered a string data type. Please note the single quotes before and after the string, this is how to tell MySQL that the data is a going to be a string data type.

I will be discussing three of the main string data types and they are Char, VarChar and TEXT.

  1. Char(XX) – This is used when the strings you want to store in the database has a fix-length. Also, on the performance level it is quicker than the VarChar data type. The maximum number of characters in the newer MySQL is 65,535.
  2. VarChar(XX) – This is used when the you want to store strings that varies in length. The maximum number of characters in the newer MySQL is 65,535
  3. TEXT This has the maximum length of 65,535 (216 – 1) characters. And on the performance level is the slowest of the three.

Creating Tables for a Database

What is a table?. Well, the easiest way to think about it is to think of the database as the file cabinet and the tables are the files within the file cabinet. The tables are where the data is stored and manipulated in the database. Before you can work on a table, you must tell MySQL what database you want to work on. To do this you use the following command at the terminal window.

Mysql> use useraccount;

This will tell MySQL to connect to the useraccount database that you have created earlier.

To create a new table within the useraccount database, you will use the CREATE TABLE command. We will create a table that holds login information. The table will hold the username and password that will be used by the user to login to their account. Type the following into the terminal window.

Create table Login (id int auto_increment not null, username varchar(50) not null, password varchar(50), primary key (id));

The login name is used to give the table a name. The first field id is used to give each data row a unique identifier which is a common practice in database creation. The id field is an integer and the not null option tells MySQL that this field must be given a value. While the auto_increment option, tells MySQL that with each new line added to the table login, increment one to the id field, thus creating a unique identifier for the row of data. The next field is given the name username and has the data type of varchar(50) which if you remember from the previous section means that this field will have a string value that is not fixed and is limited to 50 characters. The next field is name password and has the same data type as the username field. The primary key option tells mysql to make the field called Id into a primary key.

What is a primary key you might be asking? It is a way to link data in one table to the data in another table and is common used technique in database developers. For example, in our database, you might want to link the login information to another table that holds items the person buy. I will not be going into how to use it as it is beyond the scope of a beginners tutorial and not really needed in our simply database example but I want to show you it exist so you will have a basic knowledge of what it is used for because as you progress you will be using it extensively.

If the commanded above was successfully you should see the following result

Query OK, 0 rows affected, 0.89 (sec)

Show Tables

You can have MySQL display a list of tables in the current used database by using the SHOW TABLES command. Enter the following into the terminal window.

Mysql>show tables;

If it is successful you should see the following results

+---------------------------------------------+
|  Tables_in_useraccount                      |
+---------------------------------------------+
| Login                                       |
+---------------------------------------------+
1 row in set (0.01 sec)

How to Insert Data into the Table

To add data into the table one would use the INSERT command. Enter the following command to enter some data into the Login table in the terminal window

Insert into login (username, password) Values (‘Matthew’,’password1’);

This command will insert ‘Matthew’ into the data field username and ‘password1’ into the data field password of the login table. Note that you did not have to insert anything into the id field as it was set up to be automatically filled in with an increment integer. Notice that the data ‘Matthew’ and ‘password’ has a single quote around them. This is due to these fields being set up as string data type and thus, you will have use single quotes around them to let MySQL know that it is a string that you are trying to input into the data. If you tried to enter the data without the quotes, MySQL will not be happy and throw up an error. Also, please note that one should never keep password in plain text like I did in this example. You should always hash the password using SHA256 or preferably BCrypt but I am not doing this as it makes it easier for you to see what is going on in the database.

If the above command was successful you should see the following results.

Query OK, 1 rows affected, 0.12(sec)

Now, let’s see if you can enter the following data into the login table: username = ‘John’ and password=’password2. The correct format is given below.

Insert into login (username,password) Values (‘John’,’password2’);

Displaying Data in the Table

To see data in a table, you would want to use the SELECT command. Enter the following command at the terminal window

Select username, password from login;

If the command was successful you should see the following results

+---------------------------------+----------------------------+
| username                        | password                   |
+---------------------------------+----------------------------+
| Matthew                         | password1                  |
| John                            | password2                  |
+---------------------------------+----------------------------+
2 rows in the set (0.12 sec)

The above command basically tells MySQL to display the username and password field from the table name login.

Now you might be thinking to yourself “well that is fine and dandy but what if I only want to see certain data from the table. Well this is done by adding the key word WHERE to the select command.. To get a feel on how this works, enter the following command into the terminal window.

Select username, password from login Where username=”Matthew”;

If all goes well you should see the following results.

+---------------------------------+----------------------------+
| username                        | password                   |
+---------------------------------+----------------------------+
| Matthew                         | password1                  |
+---------------------------------+----------------------------+
1 rows in the set (0.08 sec)

Basically, this command tells MySQL to only return the row that has ‘Matthew’ for the username in the table login. Now let’s see if you can do one by yourself. I want you to select only the field that has password = ‘password2’. The correct command is listed below.

Select username, password from login Where password=’password2’;

If the above command was successful you should see the following result.

+---------------------------------+----------------------------+
| username                        | password                   |
+---------------------------------+----------------------------+
| John                            | password2                  |
+---------------------------------+----------------------------+
1 rows in the set (0.08 sec)

There is a wildcard character * can be used by the SELECT command. It basically means to list all the fields in the table that meets the select query. Type the following into your command window.

Mysql> Select * from login;

If all goes well you should see the following result

+--------------------------+---------------------------------+----------------------------+
| id                       |  username                       | password                   |
+--------------------------+---------------------------------+----------------------------+
| 1                        | Matthew                         | password                   |
| 2                        | John                            | password                   |
+--------------------------+---------------------------------+----------------------------+
2 rows in the set (0.56 sec)

The above command is equivalent to the following command.

Mysql> Select id, username, password from login;

The Select command has many more options and formats but I do not want to overwhelm the novice. After all, the goal is to give a brief introduction into using MySQL.

Changing Data in a Table

To edit the data in a table you would use the UPDATE command. Enter the following into the terminal window

Update Login Set username=”Grant” Where id=1;

If the command is successful than you should see the following

Query OK, 1 rows affected, (0.23 sec) 

The above comes causes MySQL to look through the table login until it finds the row of data that has id=1 and then change the username to ‘Grant’. To verify that this indeed work run the SELECT command that you used earlier. Type the following into the terminal window.

Select * from login;

You should see the following results

+--------------------------+---------------------------------+----------------------------+
| id                       |  username                       | password                   |
+--------------------------+---------------------------------+----------------------------+
| 1                        | Grant                           | password1                  |
| 2                        | John                            | password2                  |
+--------------------------+---------------------------------+----------------------------+
2 rows in the set (0.56 sec)

As you can see it changes the user that was ‘Matthew’ to ‘Grant. Now let’s see if you can do one. Change ‘password2’ to ‘password3’ in the second row. The correct commands are listed below. (There is more than one way to do this)

Update Login Set password=”password3” Where id=2; 

Update Login Set password=”password3” Where username=’John’; 

Update Login Set password=”password3” Where password=’password2’

All the above commands will give the same results.

To verify that it worked run the following SELECT command which you should be familiar with by now.

Select * from login;

If you did everything right you should see the following result.

+--------------------------+---------------------------------+----------------------------+
| id                       |  username                       | password                   |
+--------------------------+---------------------------------+----------------------------+
| 1                        | Grant                           | password1                  |
| 2                        | John                            | password3                  |
+--------------------------+---------------------------------+----------------------------+
2 rows in the set (0.51 sec)

Deleting Data from the Table

To erase data from the table you would want to use the DELETE Command. Enter the following into the terminal window.

Delete from login where id=2;

This command tells MySQL to look through the table called login and find the row of data that has id=2. Then delete this row of data. If the above command was successful then you should see the following.

Query OK, 1 rows affected, (0.34  sec)

To verify that it indeed worked, type the familiar command listed below.

Select * from login;

You should see that the second row was deleted and should display the following results.

+--------------------------+---------------------------------+----------------------------+
| id                       |  username                       | password                   |
+--------------------------+---------------------------------+----------------------------+
| 1                        | Grant                           | password1                  |
+--------------------------+---------------------------------+----------------------------+
1 rows in the set (0.21 sec)

Beware delete can lead to trouble if not used in the right way. So care should be taken when design Delete queries. For example, if you entered the following, it would delete all the data in the login table. <pre?> Delete from login; </pre>

Disconnecting From MySql

To disconnect from MySQL just type the following into your terminal window and it will take you back to the command prompt

Mysql> exit

Conclusion

MySQL is a powerful tool and one of the most commonly used database on the internet. It has many powerful features that I did not go into in this tutorial since the aim of this article is for beginners; I only went over the basic usages of MySQL. Hopefully once you read through this tutorial you will be able to connect and do some basic database manipulations on databases in MySQL.

Computing


QR Code
QR Code mysql (generated for current page)
 

Advertise with Anonymous Ads