This article introduces SQL beginners to exactly what SQL is and what it can do. Since SQL is used to deal with databases, we also introduce some basic database terminology.
1. Database Basics
The fact that you are reading this article suggests that you need to deal with databases in some way. SQL is the language used to accomplish this task, so before you learn SQL, you should have some understanding of databases and some basic concepts of database technology.
You may not realize it yet, but you actually use databases all the time. Whenever you select a contact on your phone or look up a name from your email address book, you are using a database.
You are also using a database when you do a search on a website. You are also relying on a database to verify your username and password when you log on to the Web at work. Even when you use your ATM card at an ATM, you use a database for password verification and balance inquiries.
Although we use databases all the time, we don't have a clear understanding of what exactly a database is. This confusion is further exacerbated by the fact that people may use the same database terminology to mean different things.
Therefore, we first give some of the most important database terms and explain them.
Hint: Review of Basic Concepts
This is followed by a brief introduction to some basic database concepts. If you already have some database experience, you can use this to review and consolidate; if you are just starting out with databases, you can use this to understand the necessary basics.
Understanding database concepts is an important prerequisite for learning and mastering SQL, and you should probably get a good book to catch up on database basics if necessary.
1.1 Databases
There are many uses of the term database, but for the purposes of the articles in this blog under the SQL category, a database is a collection of data stored in some organized way.
The easiest way to think of a database is as a file cabinet. A file cabinet is simply a physical location for data; it doesn't care what the data is or how it is organized.
database
A container (usually a file or group of files) that holds organized data.
Caution: misuse leads to confusion
People often use the term database to represent the database software they use, which is incorrect and has led to a lot of confusion. To be precise, database software should be called a database management system (DBMS).
A database is a container created and manipulated through a DBMS, and exactly what it is and what form it takes varies from database to database.
1.2 Tables
When you put information into a filing cabinet, you don't just throw them into some drawer and be done with it; you create files in the cabinet and then put the relevant information into a specific file.
In the database world, such files are called tables. A table is a structured file that can be used to store a specific type of data. A table can hold a list of customers, a catalog of products, or a list of other information.
Table
A structured list of a particular type of data.
The key point here is that the data stored in the table is the same type of data or list. A customer's list should never be stored in the same database table as an order's list, otherwise it will be difficult to retrieve and access later. Two tables should be created, one for each list.
Each table in the database has a name to identify itself. This name is unique, i.e. no other table in the database has the same name.
Description: Table Name
Makes a table name unique, and is actually a combination of the database name, table name, etc. Some databases also use the name of the database owner as part of the unique name.
That is, while you cannot use the same table name twice in one database, it is perfectly possible to use the same table name in different databases.
Tables have some characteristics that define how data is stored in a table, including information about what kind of data is stored, how the data is broken down, and how each part of the information is named.
This set of information describing a table is the so-called schema (schema), which can be used to describe specific tables in a database, or to describe the entire database (and the relationships of the tables in it).
schema
Information about the layout and characteristics of databases and tables.
1.3 Columns and data types
A table consists of columns. A column stores information about a part of a table.
column
A field in a table. All tables are made up of one or more columns.
The best way to understand columns is to think of a database table as a grid, like a spreadsheet. Each column in the grid stores a particular kind of information.
For example, in a customer table, one column stores the customer number, another column stores the customer name, and the address, city, state, and zip code are all stored in their own columns.
Hint: Data Breakdown
It is extremely important to break up the data into multiple columns correctly. For example, city, state, and zip code should always be separate columns from each other. By breaking down this data, it is possible to sort and filter the data using specific columns (e.g. to find all customers in a specific state or a specific city).
If cities and states are combined in a single column, sorting or filtering by state can be difficult. You can decide how far to break down the data based on your specific needs.
For example, you can generally store the door number and street name together in the address.
This is no problem, unless you want to sort by street name someday, in which case it is better to separate the door number from the street name.
Each column in the database has a corresponding data type. The datatype (data type) defines what kinds of data the column can store.
For example, if the column stores a number (perhaps the number of items in an order), the corresponding datatype should be a numeric type. If the column stores a date, text, comment, amount, etc., then the appropriate datatype should be specified.
Data type
What type of data is allowed. Each table column has a corresponding data type, which restricts (or allows) the data stored in that column.
The data type limits the kind of data that can be stored in the column (for example, preventing the entry of character values in a numeric field). Data types also help to classify data correctly and play an important role in optimizing disk usage.
Therefore, special attention must be paid to the data types used when creating tables.
Note: Data type compatibility
Data types and their names are a major cause of SQL incompatibility. While most basic data types are consistently supported, many advanced data types are not.
Worse, occasionally the same data type will have different names in different DBMSs. There is nothing the user can do about this, and it is important to keep these differences in mind when creating table structures.
1.4 Rows
Data in a table is stored on a row-by-row basis, with each record saved stored in its own row. If you imagine the table as a grid, the vertical columns in the grid are the table columns and the horizontal rows are the table rows.
For example, a customer table could store one customer per row. The row number in the table is the number of the record.
Row(s)
A record in the table.
Description: Is it a record or a row?
You may hear users refer to rows as database records. The two terms are mostly interchangeable, but technically speaking, row is the correct term.
1.5 Primary keys
Each row in a table should have a column (or columns) that uniquely identifies itself. A customer table could use the customer number, while an order table could use the order ID. The Employee table can use Employee ID. The bibliography table can use the ISBN.
primary key
A column (or columns) whose value uniquely identifies each row in the table.
The column (or columns) that uniquely identifies each row in the table is called the primary key. The primary key is used to represent a specific row. Without a primary key, it is extremely difficult to update or delete a specific row in a table because you cannot guarantee that the operation involves only the row in question and no innocent people are harmed.
Hint: You should always define the primary key
Although primary keys are not always required, most database designers make sure that each table they create has a primary key to facilitate data manipulation and management later.
Any column in a table can be used as a primary key, as long as it meets the following conditions.
- no two rows have the same primary key value.
- each row must have a primary key value (a null value
NULLis not allowed in the primary key column). - the values in the primary key column are not allowed to be modified or updated
- primary key values cannot be reused (if a row is deleted from the table, its primary key cannot be assigned to a new row later).
The primary key is usually defined on one column of the table, but it is not mandatory to do so; it is also possible to use multiple columns together as primary keys. When using multiple columns as primary keys, the above conditions must be applied to all columns, and the combination of all column values must be unique (but the values of individual columns within them may not be unique).
There is another very important type of key, called foreign key, which we introduce in How to join two or more tables using SQL INNER JOIN.
2. What is SQL
SQL (pronounced with the letters S-Q-L or sequel) is an abbreviation for Structured Query Language. SQL is a language specifically designed to communicate with databases.
Unlike other languages (such as English or programming languages like Java, C, or PHP), there are very few words in SQL, and this is intentional. SQL was designed to do a task well - to provide a simple and efficient way to read and write data from a database.
What are the advantages of SQL?
- SQL is not a language that is proprietary to a particular database vendor. Most major DBMSs support SQL, so learning the language allows you to work with almost any database.
- SQL is easy to learn. Its statements are all made up of highly descriptive English words, and there are not many of them.
- Although SQL may look simple, it is actually a powerful language that can be used flexibly to perform very complex and advanced database operations using its language elements.
Here we will start learning SQL for real.
Description: Extensions to SQL
Many DBMS vendors have extended SQL by adding statements or instructions. The purpose of such extensions is to provide additional functionality or simplified methods of performing specific operations. While such extensions are useful, they are generally specific to individual DBMSs, and rarely are they supported by both vendors.
Standard SQL is governed by the ANSI Standards Committee, and thus called ANSI SQL. all major DBMSs, even with their own extensions, support ANSI SQL. each implementation has its own name, such as Oracle's PL/SQL, Transact-SQL for Microsoft SQL Server, etc.
3. Hands-On Practice
As with any other language, the best way to learn SQL is to do it yourself. To do this, you need a database and an application system for testing SQL statements.
3.1 Which DBMS should I choose?
You need to use a DBMS to follow along, so which one should you choose?
There are basically two approaches. One is to install a DBMS (and related client software) on your own computer, so that you can use it conveniently and control it well. But for many people, the most troublesome part of learning SQL is installing and configuring the DBMS.
The alternative is to use a remote (or cloud-based) DBMS over the network, where you don't need to manage or install anything.
If you are going to install it on your own computer, there are actually a lot of options. I'll give two suggestions.
- MySQL (or the derived MariaDB) is great, free, supported by every major OS, easy to install, and it's one of the most popular DBMSs.
MySQL comes with a command line tool where you can enter SQL commands, but it's best to use MySQL Workbench, which you can also download and install (it's usually a separate installation).
- Windows users can use Microsoft SQL Server Express, a free version of the powerful SQL Server, which also includes a user-friendly client called SQL Server Management Studio.
If you are going to use a remote (or cloud-based) DBMS, my advice is.
- If you are learning SQL for work, then your company should have a DBMS for you to use. In that case, you should have access to a login and connection tools to access the DBMS and enter and test your SQL statements.
- A cloud DBMS is a DBMS that runs on a virtual server and is used as if you had the DBMS installed on your own machine, without actually having to install it. All major cloud service vendors (e.g. Google, Amazon, Microsoft) offer cloud DBMS.
However, setting up a cloud DBMS (including configuring remote access) is not easy and often more work than installing a DBMS yourself.
The two exceptions are Oracle's Live SQL and IBM's DB2 in the cloud, which offer a free version with a web interface where you can just type SQL statements into a browser.
The Sample Table Scripts for Learning SQL explains what a sample table is and details how to get (or create) a sample table.
4. Summary
This article has introduced what SQL is and why it is useful. Since SQL is used to deal with databases, we also reviewed some basic database terminology.
We also suggested that the best way to learn SQL is to practice it yourself. To do this, you need to choose a database and an application to test the SQL statements.
Link to the original article: https://www.developerastrid.com/sql/learn-sql/
(End)
Top comments (0)