Although words such as SQL and database are often heard, many people may find it difficult to understand their meaning or content. An understanding of SQL is essential for anyone working with data. SQL is a database language that is also used to analyze big data, so it is also related to
digital marketing
that incorporates big data.
Therefore, in this article, we will provide an overview of SQL and databases, and introduce databases that are compatible with SQL. If you are not sure what SQL or databases are, please refer to this article to learn more.
What is SQL?
SQL is a language for operating databases. To be more precise, SQL performs the process of extracting information based on a search query from an RDB (relational database) among databases. By using SQL, it is possible to insert data into a database, and conversely remove or rewrite existing data.
SQL has been standardized by ISO (International Organization for Standardization), so all major databases can be operated using SQL.
The official pronunciation is “sequel,” but it is more commonly referred to as “esquelle.”
What is a database?
A database is a collection of data. Therefore, the address book on your smartphone and your LINE friend list can also be considered databases. A database is not just a collection of data. A database organizes data in tables.
The data in the database is clearly differentiated by attributes, numbers, etc. Therefore, you can search within the database to find data and differentiate between data.
By the way, an RDB handled by SQL is a collection of related data with a fixed definition. Different tables are created depending on the definition, and by linking each table, it is possible to process complex data.
Regarding language differences
Basically, there are two main languages used in the web world.
- database language
- programming language
These two languages are used together in development, so it is desirable to have an understanding of both. From here, we will explain the above two languages in turn.
database language
Data entered into a database is organized and manipulated by a DBMS, a system that manages data searches and updates. All this language can do is search the database for the data you want. By combining programming languages and database languages, you can add, delete, and replace data.
programming language
A programming language is a language that humans use to give instructions to computers. Many web services and applications are developed using this programming language. While there are only a few database languages, mainly SQL, there are over 200 programming languages, mainly Java and PHP, and each is used in a wide variety of ways. Different languages are used depending on the characteristics and purpose of the product being developed, and they are used in a variety of ways.
Type of SQL
There are three main types of SQL:
- data definition language
- data manipulation language
- data control language
Each language is used within the database, but their purpose of use and what they can do differs greatly.
From here, we will explain in order how each language is used and what it can do.
data definition language
A data definition language is a language that defines the structure of objects and the relationships between objects. An object in a database is the actual data or table itself. Data definition language is called “DDL”, which is short for “Data Definition Language” in English.
Here are the main command statements of the data definition language and their meanings.
- CLEATE: Define a new object
- ALTER: Change the contents of an object once defined
- RENAME: Rename object
- JOIN: join two or more tables together
- DROP: Delete an object
- TRUNCATE: Delete data in bulk
data manipulation language
A data manipulation language is, as the name suggests, a language for manipulating data in a database. Data manipulation languages allow you to update, add, and delete data from a database. In addition, data manipulation languages have a large number of patterns and can specify detailed data.
Data Manipulation Language is written as “Data Manipulation Language” in English. For this reason, it is sometimes called the acronym “DML.”
The main command statements of the data manipulation language and their meanings are as follows.
- SELECT: retrieve data from within the database
- INSERT: insert data into the database
- UPDATE: Update data
- DELETE: Delete data from the database
- MERGE: Execute INSERT, UPDATE, DELETE in bulk
data control language
A data control language is a language that restricts access to data. Data management is extremely important when handling data. Particular care should be taken when handling personal information. This data control language restricts who can access the database.
Data control languages also play a role in managing databases. Data control language is written as “Data Control Language” in English. Therefore, it is called “DCL” by its acronym.
Click here for the data control language commands and their meanings.
- GRANT: Grant user privileges
- REVOKE: Remove user privileges
- BEGIN: start a transaction
- COMMIT: Commit the transaction
- ROLLBACK: cancel a transaction
The transaction mentioned above is the process of combining instructions made up of multiple SQL statements into one and reflecting them in the database.
Four major SQL database languages
Up to this point, we have provided an overview of SQL. From here, we will explain databases that use SQL. Among the databases that can use SQL, the following are the four most commonly used databases.
- MySQL
- Microsoft SQL
- PostgreSQL
- Oracle Database
Although they all share the ability to use SQL, they each have slightly different characteristics.
MySQL
MySQL is an open source database developed and supported by Oracle. It has a commercial license and is also available for free. Free use requires the contents of the database to be made public, but purchasing a paid commercial license prevents the software from being freely viewed. Commercial license holders also have the advantage of receiving official support.
MySQL is highly flexible and scalable, and is used in many situations. Due to its fast search speed, this database is also used by systems used around the world such as Google, YouTube, and Yahoo!.
Microsoft SQL
As the name suggests, Microsoft SQL is a database developed and supported by Microsoft. The feature is that it is a database compatible with Excel. While many databases are mainly operated using a “CUI” that is operated by inputting commands, Microsoft SQL uses a “GUI” that can be operated visually. Therefore, Microsoft SQL allows intuitive operation, and its strength is that it is extremely easy to use.
Additionally, it is highly compatible with Windows products, so it is an option when using Windows Server.
PostgreSQL
PostgreSQL is commonly known as “Postgres”, and like MySQL, it is an open source database that can be used for free. In addition, a paid license is required to use all functions, and in that respect, it is the same usability as MySQL.
Basically, it is handled in the same way as MySQL, but it is a little more difficult to handle and has many features. It is preferred by programmers because it is easy to incorporate database processing into programming.
Oracle Database
Oracle Database, like MySQL, is a database developed and supported by Oracle. In recent years, more and more people are switching from Oracle Database to MySQL.
Although Oracle Database is expensive, it is used for many web services due to its robust security posture and ability to lock it against simultaneous editing by multiple users. Another reason for its popularity is the generous support provided by Oracle.
How to use the SELECT statement
Finally, we will introduce an example of the use of the “SELECT statement”, which is a typical SQL statement. As explained in the data manipulation language section, the “SELECT statement” is a syntax that searches for specific data within a database.
The syntax for using the “SELECT statement” is as follows.
SELECT column name 1, column name 2, column name 3 FROM table name (which table to search from) WHERE extraction condition
Let’s look at one specific usage example. Suppose we have a CLIENT table that stores NAME, AGE, and FROM. If you want to extract customer data for people over 40 years old, write a sentence like the one below.
SELECT * FROM CLIENT WHERE AGE >= 40
*”*” means “all”.
By using the above SELECT statement, you can extract all customer data over the age of 40 without omission.
summary
In this article, we provided an overview of SQL and databases, and introduced databases that can use SQL. SQL is a must-understand language in today’s world where we need to handle big data.
If programmers can also master SQL, the range of possible development will increase. In particular, data manipulation language is one of the most frequently used languages in SQL. Why not start by learning a data manipulation language?