Category Archives: MySQL

MySQL Tutorial – Like clause

MySQL Tutorial – Like clause

Like clause in a CREATE TABLE command will create an exact copy of your existing table. The newly copied or created table will also acquire the column definition or attributes of the original one.

Syntax of like clause:

CREATE TABLE nameOfNewTable LIKE nameOfExistingTable;

Where:

nameOfNewTable – the name of the new table to be created.

nameOfExistingTable – the name of the existing table in your database.

Restore MySQL database using PHPMyAdmin

phpMyAdmin is a free software tool written in PHP, it is a browser-based platform used to manage and administer MySQL database.

On the previous tutorial we have learned how to create a backup of our databases using the mysqldump command. Now we are going to reestablish or restore our database to its previous state.

Backup MySQL database on WAMP

Backup MySQL database on WAMP

In this tutorial we are going to learn how to back up our MySQL database in WAMP using the mysqldump command.

mysqldump command is used to export a database or all databases that resides on your MySQL Server. The exported file has an extension of .sql which commonly consists of SQL commands to create a database, create tables and insert records into tables.

Here’s how to create a backup file using the mysqldump command

  1. Click Start > Run then type cmd and hit enter.
  2. Syntax: C:\wamp\bin\mysql\[mysql version]\bin\mysqldump -u root -p [database_name] > [path where you want to put the file and the filename]
  3. Type this command: C:\wamp\bin\mysql\mysql5.5.24\bin\mysqldump -u root -p dk> c:\dk.sql

MySQL ALTER Command

In this lesson we are going to learn how to use the MySQL alter command.

There are lots of Graphical User Interface (GUI) Tools to manage our MySQL databases as discussed on this page (link), through that modifying the structure of a database as well as the structure of tables, the field data types and much more depending on the capability of that tool. All of that comes in a handy way because these tools has a drag and drop capability. The users can easily change the data types of a certain field, rename a table, and add new columns. In this lesson we are not going to tolerate those manners (drag, drop and click).

MySQL SHOW Command

MySQL SHOW Command

SHOW command is used to learn more about the databases found in the server, tables found in a database, and specific information about the columns or fields within a table.

Let’s take a look on the uses and examples of the SHOW Command.

We want to know the databases that reside on our MySQL server, to do that issue the command:

mysql>show databases;
+----------+
| Database |
+----------+
| mysql    |
| sample   |
| test     |
+----------+
3 rows in set (0.14 sec)

Note: the results may vary depending on the stored databases on your system.

Drop Database and Table in MySQL

Dropping the Database

To delete an entire database, use the DROP DATABASE command. This command will delete the database and the tables inside it. Let’s try some example:

Syntax of DROP DATABASE command:

DROP DATABASE database_name;

Where: database_name is the name of the database you want to drop or delete.

Graphical Tools to interact with MySQL

Graphical Tools to interact with MySQL

If you’re tired of using the traditional mysql built-in command line tool then this article is just for you.  We’ve prepared a list of Graphical Tools used to manage our MySQL databases. First things first, why do we need to need to use a GUI Tools to manage our MySQL databases? Let us first discuss the advantages of using a Graphical Tools to access our MySQL databases:

  • having a tool with Graphical User Interface (GUI) can speed up your development
  • able to manage our MySQL databases without actually entering SQL commands
  • with graphical interface, it makes creating and managing our MySQL databases simple

MySQL Database Activity 2

MySQL Database Activity 2

This activity will test your knowledge on basic sql statements. To review our MySQL Lessons and Tutorials, kindly visit this link MySQL Tutorials Compilation. The answers can be found on our facebook page or you can download the answers here.

1. Write the complete SQL statement for creating a new database called contacts.

2. How would you list all the databases available on the system?

3. Create a table called contacts_data with the following field:id, firstname, lastname, age, email, address, contact_number. Apply the appropriate data type.

MySQL Database Activity 1

MySQL Database Activity 1

This activity will test your knowledge on basic sql statements. To review our MySQL Lessons and Tutorials, kindly visit this link MySQL Tutorials Compilation. The answers can be found on our facebook page or you can download the answers here.

1. Create a student Database

2. Create a table (student_data) under the student database that contains the following entity:

a. id – int unsigned not null auto_increment primary key
b. lname – varchar(20)
c. mname – varchar(20)
d. fname – varchar(20)
e. contact – int(20)
f. address – varchar(25)

MySQL Tutorials Compilation

MySQL Tutorials Compilation

Here are the list of our MySQL Lessons and Tutorials

  1. Introduction to MySQL Database
  2. MySQL Tutorial – Creating a database in MySQL
  3. MySQL Tutorial – Creating a Table in MySQL
  4. MySQL Tutorial – INSERT INTO Command
  5. MySQL Tutorial – How to Insert Multiple Records
  6. MySQL Tutorial – MySQL UPDATE
  7. MySQL Tutorial – MySQL DELETE command
  8. MySQL Tutorial – MySQL SELECT command
  9. MySQL Tutorial – MySQL Order BY Clause
  10. MySQL Tutorial – Using the WHERE clause
  11. MySQL Tutorial –MySQL AND & OR Operators
  12. MySQL Tutorial – The GROUP BY clause
  13. MySQL Tutorial – Limiting Data Retrieval
  14. MySQL Tutorial – LIKE Operator
  15. MySQL Tutorial – SQL Aggregate Functions
  16. MySQL Tutorial – Using Column Aliases
  17. MySQL Tutorial – Using HAVING clause
  18. MySQL Tutorial – Using the IN function
  19. MySQL Tutorial – Using BETWEEN condition