Master SQL With MySQL Tutorial

SQL stands for 'Structured Query Language'. SQL refers to the set of operations that can be performed over a relational database, more generally, any database. To proceed further into SQL, we need to know what a database is. A database is a huge repository of information maintained as a substitute to the conventional file processing system. Every organization has its set of databases which contain processed information about various sectors, from sales to employee records. Maintaining databases makes it easier to access, modify and extract information rather than a conventional file processing system. SQL provides the necessary commands to perform all these actions over a database. These commands in SQL are called queries. Let us now look at one particular SQL, called mySQL.

The first step in mySQL is to construct a database over which to perform these queries. mySQL databases are in the standard database format, containing tables with fields. The user has to create a table first. This can be done using the mySQL table creation wizard or using PHP. The user has to specify the fields for the table and define the data type for the field (ex. Integer, Character, String, etc.). Once the table is created, before giving the SQL queries, the PHP source code should be connected to the database server. This is done by using the 'mysql_connect' function. The syntax for the function is

mysql_connect(localhost,$username,$password);

The statement tells PHP to connect to the local host server using the specified username and password. Once the operations are all done, the connection should be terminated using the 'mysql_close( )' command. After this, the user needs to select the database over which to execute his queries. This is done by using the command,

@mysql_select_db($database) or die("Error! Unable to open database");

The above statement selects the database specified in the $database column, and in case it is not found, returns the error message displayed within the quotes.

To insert data into the created table, we use the SQL query 'insert'. The syntax for insert is as follows.

$query = INSERT INTO tablename VALUES (value1, value2…);

This query will add the values specified, to the fields in order.

Once data has been entered into the table, we have to see how to display it. The display query in mySQL has the syntax,

SELECT * FROM tablename

The above query will select all the fields and all the records present in the specified table. To perform a narrow search, we need to specify the fields that we want displayed, instead of the *. For example, to display the name and addresses from a table called my_table, we use the following query.

SELECT name, address FROM my_table

This statement will output the name and address of every record. To store the records in another array, we can use the assignment statement as follows.

$names = SELECT name FROM my_table $addresses = SELECT address FROM my_table

More specific searches can also be made. For example, to select the records with the value 'Manager' for the field 'Designation', we can use the following SQL query.

SELECT * from my_table WHERE Designation = 'Manager'

Now that we have seen how to input and output data, we need to look at updating and deleting records. Updating records is done using the following query,

UPDATE tablename SET field1 = value1, field2 = value2 … where field i = value i

For ex, to update the table my_table, we can use

UPDATE my_table SET name = 'John' designation = 'Manager' address = 'London' where id = E104

Similarly, deletion can also be performed using the query,

DELETE FROM tablename WHERE field i = value i

For example, to delete the above record we can use the statement,

DELETE FROM my_table WHERE id = E104

The briefing given above is a small tutorial to mySQL. It is only the tip of the iceberg; mySQL contains thousands of such queries which can further be combined to make more complex queries. mySQL is a versatile implementation of SQL which allows a user to access and modify a database to his desire.

Privacy Policy