01-SQL: A language with a long half-life

In daily work, we use database management systems such as MySQL, Oracle, SQL Server, DB2, SQLite, etc. In fact, these DBMSS follow the SQL language, which means that we deal with them through THE SQL language. So for people engaged in programming or Internet industry, the most powerful language is SQL language.

  1. The SQL language is so ubiquitous that it can be valuable to people in different positions. SQL is not only a skill for technical personnel, but also for product managers and operation personnel.
  2. The SQL language has changed little since its birth. This means that once you master it, it can be used for good, at least in your career.
  3. Getting started with SQL is not difficult.

In the whole digital world, the most important and most common meta-foundation is Data, and the language that deals with Data directly is SQL language. Many people ignore the importance of SQL language, think it is not SELECT, INSERT, it should be a data analyst to master it. In fact, the power of SQL should not be underestimated in the real world. SQL is essential for many of today’s business processes on the Internet, as they work with data.

What about SQL, which is ubiquitous in a variety of technologies and businesses? SEQUEL: A Structured Query language for English has not changed much since 1974, when IBM researchers published a paper that unveiled database technology. SQL has a much longer half-life than other languages.

SQL has two important standards, namely SQL92 and SQL99. They represent the SQL standards issued in 1992 and 1999 respectively, and the SQL language we use today still follows these standards. You know, ’92 was when Windows3.1 was released, and how many people remember it today, but if you do data analysis, or anything related to data, you still use SQL.

As people in the technology and Internet industry, we always want to find a language with strong versatility, relatively little change, and relatively easy to use. SQL is one of the few languages that can meet these three conditions.

Getting started with SQL is not difficult

SQL is so powerful, is it hard to learn? Not at all. SQL doesn’t need a lot of programming language foundation like other languages. SQL is more like English, with a few simple English words, and when you use it, it’s like talking to a database in English.

SQL language can be divided into the following four parts according to functions:

  1. DDL, or Data Definition Language, is used to define our database objects, including databases, Data tables, and columns. Using DDL, we can create, delete, and modify database and table structures.
  2. DML (Data Manipulation Language) is used to manipulate records related to a database, such as adding, deleting, and modifying records in a Data table.
  3. DCL, or Data Control Language, is used to define access permissions and security levels.
  4. DQL, English called Data Query Language, Data Query Language, we use it to Query the record we want, it is the most important SQL Language. In the actual business, we are dealing with queries in most cases, so learning to write correct and efficient query statements is the focus of learning.

Learning SQL is like learning English grammar. SQL is one of the few declarative languages where all you have to do is tell the computer what results you want from the raw data. For example, if I want to find a hero whose main character is a warrior, along with their hero name and maximum health, I can type the following language:

SELECT name, hp_max FROM heros WHERE role_main = 'hp_main ';

Here I define the HEROS data table, including the name, HP_max, ROLE_main and other fields. I will explain the specific data table as an example later in the course, but here is a brief explanation.

As you can see from this code, I’m not telling the computer what to do to get results, which is one of the great conveniences of declarative languages. We don’t need to specify specific execution steps, such as which step to execute first, which step to execute next, whether to check whether condition A is met before executing, and so on.

Wouldn’t it be great if the SQL language defines our requirements and different DBMSS extract the results for us based on the specified SQL?

Start your SQL journey

SQL is the language that we communicate with DBMS. Before we create DBMS, we also need to design it. For RDBMS, it adopts ER Diagram (Entity Relationship Diagram), that is, the way of designing entity-relationship Diagram.

After the ER graph was reviewed, we used SQL statements or visual management tools (such as Navicat, SQLyog, PL/SQL) to create data tables.

What’s the use of entity-relationship diagrams? It is a conceptual model that we use to describe the real world, in which there are three elements: entities, attributes, and relationships.

Entities are the objects we manage, properties are the attributes that identify each entity, and relationships are the relationships between objects. For example, if we create the “hero” entity, the following attributes include name, class, maximum health, initial health, maximum mana, initial mana, and range of attack. We also have the “user” entity, whose attributes include the user ID, login name, password, gender, and avatar.

The relationship between the “hero” and “user” entities is many-to-many, meaning that a hero can belong to multiple users, and a user can have multiple heroes.

In addition to many-to-many, there are one-to-one and one-to-many relationships.

After creating the table, we are ready to use SQL. You can see that many SQL statements have different case and case. Although case does not affect SQL execution, I recommend that you use a uniform writing specification because good code specification is the key to efficiency.

With respect to SQL case, I summarize the following two points:

  1. Table names, table aliases, field names, field aliases, and so on are all lowercase;
  2. SQL reserved words, function names, bound variables, and so on are capitalized.

For example, the following SQL statement:

SELECT name, hp_max FROM heros WHERE role_main = 'hp_main ';

You can see that common SQL reserved words such as SELECT, FROM, and WHERE are all uppercase, while field names such as name, hP_max, roLE_main, and table names are all lowercase. In addition, it is recommended to name the columns in the data table with underscores, such as ROLE_main.

conclusion

Excerpt from geek time column “SQL must know will”, if there is infringement, please contact me, timely deletion.