開源日報 每天推薦一個 GitHub 優質開源項目和一篇精選英文科技或編程文章原文,堅持閱讀《開源日報》,保持每日學習的好習慣。
今日推薦開源項目:《五的三百萬種寫法 five》
今日推薦英文原文:《Absolute beginners guide to SQL》
2019年1月8日:開源日報第306期
今日推薦開源項目:《五的三百萬種寫法 five》傳送門:GitHub鏈接
推薦理由:一個 JS 庫,唯一的作用就是弄出個五齣來——各種語言,各種進位,應有盡有,但是我敢保證你可能一輩子都用不著這麼多形式的五。所以說,在自己寫 JS 庫的時候,先想想看這個庫究竟能幹啥,實用的才是好東西不是嗎?
今日推薦英文原文:《Absolute beginners guide to SQL》作者:Prashanth Xavier
原文鏈接:https://medium.com/xavier-code21/absolute-beginners-guide-to-sql-601aad53f6c9
推薦理由:推薦給新手的 SQL 教程,不包括如何讓數據喵一聲的方法。

Absolute beginners guide to SQL

2019年1月8日:開源日報第306期
SQL is one of the most sought after skills, not only in the IT sector but also in the banking and financial industry. This article is for anyone looking to wrap around the idea of databases, or the ones who just want to take a stroll in the SQL world and add a cool new skill to the resume.

What we will learn?
  • Databases and SQL
  • Flavors of SQL
  • SQL queries
All this and more in just under 10 minutes.

Databases and SQL

Data transcends the way we perceive technology in this era. It is said that about 2.5 quintillion bytes of data is created every single day. Quintillion is too large a number to fathom, the data that is generated in your phone alone is gigantic, information about phone calls; messages; facebook chats; whatsapp messages; location information; and much more makes up the bulk of all the data we, as a single individual generate. Even more, add IOT, the hottest tech right now into the mix and you have giga bytes of live data being churned out every second. This is proof that handling data becomes really essential and that』s where Databases come into the picture.
2019年1月8日:開源日報第306期
Source:https://www.usmaritimedata.com/fenthion-import-data-us

We know that data comes from different places and it is most probably random. Database is a place for storing this data but in an organized structure. What organizing gives us ? Easy management!

Database is a system that allows data to be easily stored, organized and managed.

Knowing what is a database; we now can focus on SQL. It is an abbreviation for Structured Query Language. As the name suggests, its merely a language to query databases.

In a way SQL is the standard for interfacing with databases. It goes back to 1970』s initially developed by IBM, it was originally called as SEQUEL(Structured English Query Language) because it was easily readable and understandable. It has transformed over the years and now remains the most dominant method to interact with databases.

SQL is the means of communication with the database.

Essential overview of database is required to grasp how SQL works. At its simplest, database is made of rows and columns, similar to a spreadsheet, but its far more powerful with vast number of features. Data is categorized and stored in the form of tables. For example: a company might have a database with couple of tables, one for employees and another for departments.

Each row in a table is called a record, and column a field that describes the row. In an employee table, each row is a unique person, and fields like name, address, department describe this particular employee.

Relational Databases

It is a structural form of database that stores data in tables, and these tables can be somehow linked to each other. In the example of our company database employee table can be linked to a department table, the relation here is that the employee belongs to a department.

In a traditional relational database, employee and department data can be presented as two tables below, where DeptNo relates to the department table.
2019年1月8日:開源日報第306期
Employee table

2019年1月8日:開源日報第306期
Department table

The obvious visible advantage here is that if the Purchasing department decides to rename itself as Investments, it needs to be renamed in only one place.

Flavors of SQL

SQL has been widely accepted as the language for data management, therefore there exists many different implementations of the language and its functionalities, by different vendors.

Here is a short list of these implementations:

T SQL: developed by Microsoft is the proprietary procedural language used in Microsoft SQL Server.

PL/SQL: is the procedural language used by Oracle.

PL/pgSQL: is the procedural language used by PostgreSQL.

These procedural languages are designed to add more functionality to existing capabilities of SQL, but no matter what you choose, to comply with ANSI standard the fundamental sql queries remain same across all implementations.

SQL queries

Queries can be categorized as DML(Data Manipulation Language) and DDL(Data Definition Language).

DML queries:

Select: It is used to display collection of records. It is comparable to 『print』 statement in programming languages.
Insert: It is used to insert data into the table.
2019年1月8日:開源日報第306期
Employee table after adding new employee 『Doug』

Update: It is a statement to update existing values in a table. The following update query changes the department of employee named 『Doug』.
Delete: As you can expect, delete statement is used to delete records from table. Looks like our company is not so fond of Doug. DML queries operate on the level of data. Just by using four sql queries shown above you can explore databases in a great detail.

DDL queries:

Create: is used to create a new table in a particular database. Query below creates a new table Interns. May be Doug gets another chance. Alter: Alter is multi-functional statement that can add a column, drop a column, modify a column rename a column or rename a table. Drop: An SQL statement that inspired meme』s of various kinds, is used to remove table definitions including all the data. The query below needs no explanation. As opposed to DML, DDL queries operate on object level and is used to modify, add, delete definitions of tables and other objects. Apart from the two categories there exists more like DCL(Data Control Language) and TCL (Transaction Control Language).
下載開源日報APP:https://openingsource.org/2579/
加入我們:https://openingsource.org/about/join/
關注我們:https://openingsource.org/about/love/