Kevin Lioy - MYSQL: SQL Database Programming for Beginners

MYSQL: SQL Database Programming for Beginners — read online for free the complete book (whole text) full work

Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "MYSQL: SQL Database Programming for Beginners" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.

Light

Font size:

↓

↑

Georgia Tahoma Arial Verdana

Reset

Interval:

↓

↑

Bookmark:

Make

1234567...9

Sommario

Intro

Every application, whether it is a Web application or not, is built on multiple levels to help developers organize the development of each feature. In the nineties the two-tier architecture was very widespread. Two-tier architecture is a simple client-server architecture in which a client connects to a server to use a service. In this way the server allows to share resources between various clients using a protocol that can be clear or encrypted.

In the new millennium, three-tier software architectures, also known as three-tiers, have become increasingly widespread, showing similarities to the MVC pattern (Model - View - Controller). This type of architecture is composed of: a presentation level (the highest) that presents information to the user; the application level also called business logic that performs the calculations and coordinates the entire application, performing logical evaluations; and the data level (the lowest) which represents the place where the information is stored and from which it is retrieved and therefore traceable back to the database used.

In this book we will start from the definition of Database, how many and what types of databases exist and in particular we will deepen one of the most used: MySQL.

The book starts with the installation and configuration to get to advanced queries, passing through the administration console. We will examine the advantages of MySQL and when it is convenient to use it, the SQL syntax and how to create databases and tables with data relationships.

A book for beginners

As highlighted in the introduction, this book is aimed primarily at developers who want to enrich their knowledge on MySQL or who simply want to use this kind database.

This book is also dedicated to those who want to create a site via Joomla, Wordpress or Drupal as they use MySQL as well as the LAMP platform (Linux, Apache, MySQL, Perl / Python / PHP) to build a Web App.

Show me the code

In this book we will use different fonts and styles to indicate different types of information.

Command line input and output for MySQL appear as follows:

SELECT * FROM user WHERE id = 12;

The Terminal input appears as follows:

cd /tmp/

New terms, important words, folders or directories and interface elements are shown in italics .

Requirements

There are no special requirements for using MySQL on your PC. We will use version 8.0 which may not be available for some operating systems. Please check at the following link ( https://www.mysql.com/it/support/supportedplatforms/database.html ) if your operating system is supported, if its not, you can still use version 5.7.

In case you have to use version 5.7, you will not have some new features such as roles, hidden indexes, character sets and default collation but these are advanced features that we will not cover in this book.

Basics

Whats a Database?

A database is a set of homogeneous and structured data, stored in an electronic computer. Therefore, it is an electronic "file" with many more functions and better performances than a traditional one.

Through a well designed database it is possible to access, manipulate, update and delete data in a very simple way and in short time.

Just think of the applications that surround us, our e-mail client, social networks, apps on our smartphone: all of them use a database to store data.

In this aspect, the Web has started the development of new generations of databases that make the use of the same resources by several users really efficient.

A database is also capable of performing complex operations, as well see in later chapters, for example aggregations and / or ordering of returned data, even involving multiple tables.

You can also update records in a single block, even millions of records in a single transaction, create relationships between the tables to retrieve a customer's orders or calculate the average amount of each purchase.

In my computer career, I even found entire programs written in databases. This highlights the importance of this tool.

Database types

Now that we know what a database is, lets see how many different types exist and what they are.

In essence, all the categories lead back to two macro-categories: relational databases and non-relational databases .

SQL

Relational databases, also called SQL databases, are the best known and widespread even in large enterprise contexts and, as evidenced by the etymology of the word, refer to the relationships between the data.

This type of model organization is suitable for organizing data in tables that are usually composed of rows (also called records or tuples) and columns and with a single primary key for each row.

The tables, as can be inferred, represent the entities of the application (for example Customer and Order) while each row represents a different instance of the entity (for example Phil Rossi is an instance of Customer).

Instances can be linked together through unique keys which therefore represent an identity constraint.

Some examples of relational databases are: MySQL, Oracle, DB2, Microsoft SQL Server and MariaDB.

Below is a table showing pros and cons of relational databases:

Pros	Cons
Structured data	Semi-structured data are hard to manage
Native management of data integrity	Data normalization
Constraints due to relationships	Scalability

NoSQL

Non-relational databases, also known as NoSQL databases, have had widespread use and development thanks to Web 2.0 and are mainly used for real-time applications, such as financial apps like stock market performance or big data applications.

These databases support SQL syntax but work best where relational databases lacks, for example, with semi-structured data such as XML or when performance needs to be really high in order to manage a large number of users.

The NoSQL databases consist of 4 distinct categories:

Key-value stores where each element of the database is historicized by means of a key or an attribute together with its own value;

Wide-column stores historicizes the set of data as columns and not as rows, therefore particularly suitable for large data sets;

Document maps each key with a data structure called document which, in turn, can contain a document or several key-value type structures, key-array of values;

Graphs usually used for information on networks.

This type of database is easily scalable, economical and easy to maintain but places a limit on both data consistency and referential integrity. This limit allows faster queries and insertions but results in a propagation of changes through slower nodes than relational databases. It is not recommended, therefore, to use this type of database for sensitive applications where updating the data must be instantaneous as in a bank, while it is suitable for a social network where millions of users are assumed.

Let's see what are the main pros and cons of NoSQL:

Pros	Cons
Flexibility and scalability	Data integrity
Simple and fast	A standard is missing

Next page

1234567...9

Light

Font size:

↓

↑

Georgia Tahoma Arial Verdana

Reset

Interval:

↓

↑

Bookmark:

Make

Similar books «MYSQL: SQL Database Programming for Beginners»

Look at similar books to MYSQL: SQL Database Programming for Beginners. We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.

Michael McLaughlin

Oracle Database 11g & MySQL 5.6 Developer Handbook

Pettit Thomas

The MySQL Workshop: A practical guide to working with data and managing databases with MySQL

Vinicius M. Grippa

Learning MySQL: Get a Handle on Your Data

Dwight

SQL: Learn SQL in 24 Hours or Less - A Beginners Guide To Learning SQL Programming Now (SQL, SQL Programming, SQL Course)

Skudaev

PHP Programming for Beginners. Key Programming Concepts. How to use PHP with MySQL and Oracle databases (MySqli, PDO)

DeBarros

Practical SQL: a beginners guide to storytelling with data

Adrian W. West

Practical PHP 7, MySQL 8, and MariaDB Website Databases: A Simplified Approach to Developing Database-Driven Websites

Anthony DeBarros

Practical SQL: A Beginners Guide to Storytelling with Data

Bagaria G.

MySQL for Beginners

Michelle Malcher

Oracle Database 11g & MySQL 5.6 Developer Handbook

Michael McLaughlin

MySQL Workbench Data Modeling & Development (Oracle Press)

Adrian W. West

Practical PHP and MySQL Website Databases: A Simplified Approach

Discussion, reviews of the book MYSQL: SQL Database Programming for Beginners and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.