SQL is a database language that allows you to define and manipulate databases.
SQL is standardized by ISO (International Organization for Standardization), and once learned, it can be operated in almost the same way in other databases.
This time, we will introduce the basic knowledge of SQL, which is a database language, the types of languages, and their characteristics.
table of contents
- 1. Basic knowledge of SQL
- 2. Database and programming languages
- 3. SQL type
- 4. SQL features
- 5. Importance of SQL to be reviewed again
Basic knowledge of SQL
SQL is called “sequel” or “sequel”. SQL is a proper noun and is defined as not an abbreviation for SQL as an international standard.
In the 1970s, IBM developed “System R”, the world’s first RDBMS (relational database management system). “SEQUEL (Structured English Query Language)” that operates is the basis of the current SQL, and the reading of “sequel” also comes from here. Since then, the database language has been revised and renamed “SQL” many times, but the reading has been retained.
SQL is one of the most popular database languages. It is a language for controlling data and databases on a DBMS (database management system). It receives commands from users and systems, queries the RDB (relational database), and returns the results. The returned result will be displayed on the monitor.
SQL is standardized by ISO (International Organization for Standardization) is a database language. So once you have learned the SQL for one database, you can use it in most other RDBs as well. Databases that can use SQL include Oracle Database from Oracle, Microsoft SQL Server and Access from Microsoft, and open-source MySQL and PostgreSQL, each of which has a different database engine, but all can be operated with SQL. is.
Until now, various standards were used for each RDBMS, but since it was renamed to SQL, it has been standardized by ANSI (American National Standards Institute) and ISO. SQL standards are still being revised every few years.
Database language and programming language
SQL is a database language whose purpose is to operate and control the software that manages the database. Even though it is a language, it is not a programming language, so it is not possible to develop a system. Understand how database languages and programming languages are different.
● Database language
A database is a database that organizes the collected data with a DBMS (a program that manages the data) so that it can be operated. RDB is often used in databases, and it expresses the relationship between data in a table (table format), and the table and the table work together to express the structure of the data. There are other types of databases such as hierarchical type and network type.
The database language is a control language that commands the processing required by the DBMS and is called a declarative language (non-procedural language). The database language is for managing data and finding out what meets user-specified criteria, with no further capabilities. It’s simple, and its grammar and specifications are completely different from other languages. A user or system instruction sends a query to the database and returns the returned value to the user or system. It does not compile and behaves like an interpreted language.
● Programming language
A programming language is a procedural language that is used when creating the body of a program. It describes the processing steps that the program takes and tells the computer what to do and how to do it.
Programming languages are more complex than database languages, and there are many types, including COBOL, C, and Java. It is said that there are thousands of types, and they can be used properly depending on the environment and machine.
SQL is roughly divided into three languages. It is used properly when controlling and operating the database, and it is a syntax that combines verbs and objects in all statements.
● Data Definition Language
Data definition language is abbreviated as DDL and is a syntax that defines the structure of objects (tables, indexes, etc.) handled by RDBMS and the relationships between objects.
CREATE: Define objects such as new databases and tables
JOIN: Join tables
DROP: Delete the defined object
ALTER: Change the contents of the defined object
TRUNCATE: Delete all data
● Data Manipulation Language
Data manipulation language is abbreviated as DML and is a syntax for manipulating databases. Used in combination with objects and keywords that indicate various conditions, there are many patterns. You can also add conditions such as “more than” and “less than or equal to” to the object, and you can also use a function called a SQL function.
SELECT: Search the database
INSERT: Insert data
DELETE: Delete data
UPDATE: Update data
● Data Control Language
Data control language, abbreviated as DCL, is a syntax that controls access to data. It is used to manage transactions and systems, and to control user access rights.
GRANT: Grant user rights
REVOKE: Delete user rights
BEGIN: Start a transaction
COMMIT: Confirm the transaction
ROLLBACK: Cancel the transaction
Today’s DBMSs use a lot of relational types. Therefore, by understanding SQL, it is possible to use most DBMSs.
● One-sided order
SQL exchanges statements and gives instructions like a dialogue. Programming languages write instructions in the order in source code and execute them, but SQL only sends simple instructions unilaterally to the database. It is completed in one sentence, and when performing complicated processing, the instruction statement is exchanged to interact with the database, such as sending the instruction again according to the result of the returned processing.
● You cannot create an app using SQL alone
SQL is a language for working with databases and cannot create or manipulate applications. When developing applications and systems, use them in combination with other programs and programming languages.
If you want to use the database from the application, write the SQL statement in the application. The value returned by operating the RDBMS is used in the application, and the process is repeated.
● How to operate the database with SQL
There are two ways to send instructions to manipulate a database in SQL.
This is a method in which the user types SQL commands on the command line of the program and operates them directly, which is called interactive. In this case, wait for the result of the process to be displayed before sending the next instruction.
This is a method of dynamically embedding SQL statements directly in source code written in other programming languages such as Java. Use SQL as part of your system by embedding it in other programs. The final result can be obtained by making the instruction statement a nested structure (nested) and performing processing such as “instruction 2 if …, instruction 3 otherwise” according to the result of instruction 1. .. This is called a subquery.
Importance of SQL to be reviewed again
When you use a computer, you almost always have access to some database.
In recent years, marketing by utilizing big data has become a hot topic, but SQL is generally provided as an interface for processing big data.
As a result, SQL is being reviewed as an important database language.
The prototype of SQL was created more than 40 years ago, but it is unlikely that the demand for databases and SQL will disappear, and its presence will only increase.
If you ever want to know about similar things, check out the Facebook page Maga Techs