On this occasion we will discuss what is Query SQL. You can read it to the end to find out
For those of you who are just getting to know databases or are just learning MySQL databases. Must be familiar with the term query on the database. This query is very familiar in database processing.
However, what is a query? Then, how to use MySQL database queries?
Come on! Check out our article below! Here we will thoroughly explain the explanation of database queries and the use of database queries in MySQL.
What are Query?
Query is a syntax or command used to access and display data in the database system. Queries have the ability to set which data needs to be displayed the way you want. In addition, queries can be used to make data interact with each other.
Query is also often referred to as a query language. Currently the most popular query language among Database Administrators is SQL.
Three Types of Database Query in SQL
There are three types of database queries in SQL, namely:
- DDL ( Data Definition Language )
- DML ( Data Manipulation Language )
- DCL ( Data Control Language ).
These three queries function to create/define database objects such as creating tables, manipulating databases, and controlling databases.
To be more complete, the following is an explanation of the three types of database queries.
1. DDL ( Data Definition Language )
DDL is a SQL query method used to define data in a database. With this query you can create new tables, modify tables, create indexes, define table storage structures and so on. Following are the queries that DDL has:
|CREATE||Used to create databases and tables.|
|Drop||Used to delete tables and databases.|
|Alter||Used to make changes to the table structure that has been created. For example, adding a Field ( Add ), renaming a Field ( Change ) or renaming it ( Rename ), and deleting a Field ( Drop ).|
2. DML ( Data Manipulation Language )
DML is a query method that is used when the DDL has been created. i This DML query is used to manipulate the database. The following are the queries that DML has:
|INSERT||Used to enter data in database tables.|
|UPDATE||Used to modify existing data in database tables.|
|DELETE||Used to delete data in a database table.|
3. DCL ( Data Control Language )
DCL is a SQL query method that is used to provide database access authorization rights, audit database usage, space allocation, and space definition. Following are the queries that DCL has:
|GRANT||Used to allow users to access tables in the database.|
|REVOKE||Used to cancel user rights permissions.|
|COMMIT||Used to specify database storage.|
|ROLLBACK||Used to cancel database save.|
Example of Using Database Query
In this guide, we will provide some basic guidelines for using database queries in MySQL Database. For this tutorial we are using an Ubuntu 16.04 VPS server that has MySQL installed. The following is an example of using a database query.
1. Creating a Database
The database is the main medium for building a database. In this database later you can put some tables. The following are the commands used to create a MySQL database:
create database database_name;
In the example above, Query OK shows the creation of a database with the name office successfully created. To view existing databases in MySQL, you can use the following command:
Then the output of the above command will display a collection of databases on your server. Here is a picture of the command above.
2. Delete Database
To delete a database that has been created, you can use the following SQL query command:
drop database database_name;
Drop means delete. This SQL query command serves to delete a database, as in the example in the following image:
3. Creating a Table
Tables are objects that are used to store all data in the database. Therefore, the table is an object that must exist in the database.
The table is located in a database, so the table creation is done after a database has been created. In a table there are rows and columns. Rows are called recordsets while columns are called fields .
To create a table or more, the database must be activated first. Because the table will be inserted into the database that has been active. Here is the command to enable Database:
After logging into the database, you can create one or more tables. To create a table, you can use the command below:
create table identity (no int(3),name varchar(35),address varchar(60),pisi varchar(40),phone_number varchar(15),);
In the example above, Query OK states that the creation of a table with the name identity was successfully created. To view the existing tables in the database, you can use the following command:
4. View Table Structure
After the table is created, you can view the data type and length of the recordset by displaying the table structure. The command used to display the table structure is as follows:
desc table_name;Ataudescribe table_name;
Then the output will be like in the image below:
5. Delete Table
To delete a table that has been created, you can use the SQL query command: DROP. Here’s an example:
drop table table_name;
6. Entering Data into Tables
To enter data, you must first have a table in the database. Then, enter data or data entry with the INSERT command . All programs that use SQL queries use the same standard commands.
Here’s an example of when the INSERT command is used:
insert into identity values('001','William','New York','SEO Team','026135998789');
To view the results of the data that has been entered in the table, use the following command:
select * from table_name;
7. Updating Data Content
If you need to update the contents of the data or update the data, use the SQL query command used is UPDATE .
update identity set name='Angelina Schmidt' where name="William";
When you check the table, the data will change as in the image below:
8. Delete Data
To delete data, MySQL has a query named DELETE . To use this query, you need to add the name of the data to be deleted. If you want to delete all the data contained in the table, here’s the command:
delete from table_name;
If you want to delete certain data from a table, the following syntax is used:
DELETE FROM table_name WHERE condition;
To delete data with id number 3 contained in the data_diri table, use a query as below:
delete from identity where no='3';