MySQL Query

Thebestindonesia.com – MySQL displays query results in tabular form (rows and columns). The first row shows the labels for that column. The following line is the result of the query.

MySQL displays the number of rows found and the query execution time, roughly showing the server’s performance.

After successfully logging into MySQL, several commands can be entered into MySQL. The following is an example command to find the MySQL version number and current date.

mysql> SELECT VERSION(), CURRENT_DATE;

The query above illustrates that:

A command typically consists of an SQL statement that ends with a semicolon (;).

When a command is entered, MySQL sends it to the server to be executed and displays the results, then displays the mysql> prompt again to indicate that it is ready to receive commands.

A command can also be written in multiple lines. MySQL determines when a statement ends by looking for a semicolon (;).

In other words, MySQL receives input but has not executed it before finding the semicolon. The following is an example of a command to find out if MySQL is being accessed by a user named Root and the current date.

mysql> select 
-> version() 
-> , 
-> current_date;

If you want to cancel the statement that is being entered, you can use the command \c. When the command is added, the rapid changes back to mysql>, indicating that the prompt is ready to accept new orders. The following is an example of deleting the statement currently being executed.

mysql> select 
-> user() 
-> \c 
mysql>

A command can be entered in any letter case. The following queries are queries that will give the same result.

mysql> SELECT VERSION(), CURRENT_DATE;  
mysql> select version(), curren_date; 
mysql> SeLecT veRsiOn(), current_DatE;

Write Comments

Comments in MySQL can be written with a prefix (#) character. All characters after the character (#) will be considered comments and will not be processed.

Apart from using the character (#), comments can also be written beginning with the sequence (–).

Read also : Data Definition Language (DDL)

For more than one line, comments can be surrounded by characters (/) and (/). All characters between the two marks will not be processed.

Creating and Selecting Databases

a. Creating a new database can be done using the CREATE DATABASE command. For example, a database will be created with the name ‘pethouse’ in the following way:

mysql> CREATE DATABASE pethouse;

b. The database that has been successfully created can be viewed with the following command:

mysql> SHOW DATABASES;

c. To choose which database to use, use the USE command. For example, when selecting the ‘pethouse’ database, you can use the following command:

mysql> USE pethouse

The USE command is the same as the QUIT command, without a semicolon (;) at the end. However, if you want to use a semicolon, it becomes no problem.

d. To find out which database is currently being used, you can use the following command:

mysql> SELECT DATABASE();

Deleting Databases
Delete a database using the DROP DATABASE command. The data stored in the database will also be deleted. For example, the ‘pethouse’ database will be deleted in the following way:

Read also : Data Manipulation Language (DML)

mysql> DROP DATABASE pethouse;

Creating Tables
After successfully creating a database, the next step is to create tables. For example, a ‘pet’ table will be made which contains the names of animals along with information related to these animals in the following way:

mysql> CREATE TABLE pet(name VARCHAR(20), owner VARCHAR(20),  -> species VARCHAR(20), sex CHAR(1), birth DATE);

To see if the tables that have been created have been added to the database, use the following command:

mysql> SHOW TABLES;

See if the table that has been created is as desired using the following command:

mysql> DESCRIBE pet;

Entering Data into Tables
After successfully creating a table, the next step is entering data using the INSERT command. For example, it will be filled in the data into the ‘pet’ table in the following way:

mysql> INSERT INTO pet(name, owner, species, sex, birth) -> VALUES ('Spot', 'Beauty', 'cat', 'P',  -> '1999-02-03');

Or you can also use the following command:

mysql> INSERT INTO pet
-> VALUES ('Husky', 'Jihan', 'dog', 'L',
-> '1996-12-06');

Apart from using the INSERT command, you can also use the LOAD DATA command to enter data from a file. For example, a file called ‘pet.txt’ will be created with the following data:

name	owner	species	sex	birth
Shinee	Mark	burung	L	2008-09-01
Ketty	Dion	kucing	P	2000-10-19
Gogani	Olivia	iguana	L	2009-05-05

The ‘pet.txt’ file is filled with one record per row, each value separated by tabs with column order as defined in table creation.

Read also : MySQL

Example line in the file:

The file is then inserted into the table using the LOAD DATA command and directed to where the ‘pet.txt’ file is stored. For example, the file ‘pet.txt’ is in the directory ‘C:/xampp/mysql/pet.txt’.

mysql> LOAD DATA LOCAL INFILE 'C:/xampp/mysql/pet.txt' -> INTO TABLE pet;

To see the data that has been entered, use the command as follows:

mysql> SELECT * FROM pet;

Delete Table
Delete a table using the DROP TABLE command. All data contained in the table will also be deleted. For example, the ‘pet’ table will be deleted in the following way:

mysql> DROP TABLE pet;