开源日报 每天推荐一个 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/