Learn MySQL And MySQL JSON – Step by Step

What Is MySQL?

MySQL is an Oracle-sponsored open-source social database whose execution framework (RDBMS) is built upon Structured Query Language (SQL). MySQL will run on essentially all OS platforms, including Linux, UNIX, macOS, and Windows. Although it tends to be utilized in a wide scope of uses, MySQL is regularly connected with web applications and internet distribution.

MySQL is a significant part of an open source-undertaking stack called LAMP. Light is a web advancement stage that utilizes Linux as the working framework, Apache as the web server, MySQL as the social database execution framework and PHP as the item arranged scripting language. (Now and then Perl or Python is utilized rather than PHP.)

Today, MySQL is the RDBMS behind a significant number of the top sites on the planet and innumerable corporate and purchase based online applications, including Facebook, Twitter, and YouTube.

Difference Between SQL & MYSQL

SQL is a short abbreviation of Structured Query Language. According to ANSI (American National Standards Institute), SQL is the standard language used to operate a relational database management system.

MySQL is a RDBMS that allows a DBA to keep the information that exists in a database arranged logically. MySQL is articulated as “My S-Q-L,” however, it is likewise called “My Sequel.” MySQL gives multi-client access to databases. This RDBMS framework is utilized with the mix of PHP and Apache Web Server, over a Linux circulation. MySQL utilizes the SQL language to question the database.

Why MySQL?

MySQL is a relational database model, which provides us a complete and secure way to interact with the database through our application in a secure way. In my MySQL database, we are not only allowed to create MYSQL Login schema but also after logging in to MySQL database, we can create relationships of our table.

Here are 10 reasons why to use MySQL.

• Allows creating the MySQL login process.
• Provides a secure way to send data to the server.
• Designed for web applications.
• On-demand scalability.
• High availability.
• Utilizing the power of memory.
• The LAMP stacks
• MySQL Cluster for web-scale
• Quick Start capability
• Reliability and uptime

Installing And Getting Started With MYSQL

There are different ways to install MySQL. The following covers the easiest methods for installing and creating a MySQL login on different platforms.

Linux: The least demanding approach to introduce MySQL is to utilize MySQL storehouses:

For Yum-based Linux dissemination like Oracle Linux, Red Hat Enterprise Linux, and Fedora, adhere to the directions on a Quick Guide to Using the MySQL Yum Repository. On the off chance that your framework cannot utilize the MySQL Yum store for reasons unknown, adhere to the guidelines in Installing MySQL on Linux Using RPM Packages from Oracle.

For APT-based dissemination like Debian and Ubuntu, adhere to the directions in a quick guide to Using the MySQL APT Repository. On the off chance that your framework cannot utilize the MySQL APT store for reasons unknown, adhere to the guidelines in Installing MySQL on Linux Using Debian Packages from Oracle.

For SUSE Linux Enterprise, adhere to the guidelines in a Quick Guide to Using the MySQL SLES Repository. If your framework can’t utilize the MySQL SUSE vault for reasons unknown, adhere to the guidelines in Installing MySQL on Linux Using RPM Packages from Oracle.

Microsoft Windows. The prescribed method to introduce MySQL on Microsoft Windows is to utilize the MySQL Installer; MySQL Installer Method on the best way to download and run the MySQL Installer. For a nitty-gritty clarification for each progression of the establishment wizard, see MySQL Installer for Windows.

On the off chance that you have designed MySQL as a Windows administration during the establishment procedure which is the default choice (see Windows Service for subtleties), the MySQL server will begin naturally after the establishment procedure is finished.

Point by point data with respect to Windows establishment, including elective establishment techniques and guidelines for investigating, can be found in Installing MySQL on Microsoft Windows.

Other platforms: For establishments on different stages (for instance, FreeBSD and Solaris), just as establishment strategies not secured above, see Installing and Upgrading MySQL.

What Is MySQL Login?

MySQL login is the key starting point to make a connection between your application and the MySQL database. To create this connection, we must write some lines of code in our application and provide a username and password to login into our MySQL database. But if we want to login into another server, then we also provide a host name. After providing such information our MySQL login is ready.

MySQL is compatible with a list of programming languages like PHP, PERL, JAVA, C# and so on but each language has a different way of creating MYSQL login.

You have installed MySQL and want to create MySQL login.

The Syntax For Remote-Server

This is process to create a MySQL Login when the server is running on a different machine.

Host: The host represents the name where MySQL server is running.

User: The User represents your user name for MySQL server account.

After that enter the password given by the hosting company.

Now you have a successful MYSQL login instance.

Example:

If you get this type of message, your MySQL Login is successful.

The Syntax For Localhost

This is the processing to create MySQL Login when the server is running on your own machine.

// Create connection
$conn = new mysqli($servername, $username, $password, #database);
//Check connection
if ($conn->connect_error) {
	 die("Connection failed: " . $conn->connect_error);
}
	echo "Connected succesfully";

What we have above is a PHP code to check is MySQL login created or not. We have used a check “if” which when there is an error will return the message connection failed or if you get “Connected successfully” then your MySQL login is also created.

MySQL login is not only limited to just creating a connection with your database but it is also a secure and advance process to add more functionalities in your application and passing your user data to the server. If you have MySQL login instance and it is working properly, then you get a space where you will place your application user’s personal information in such a way that a third person is not allowed to access it without the right permission or privileges. 

Now you can create a table structure, add the required columns and migrate them into your database.

Creating Database In MySQL

Congratulations! By following the above methods, you have created your MySQL Login now you can interact with MYSQL.

MySQL is a complete DBMS (Database Management System) and it provides different queries for interacting with databases. MySQL provides multiple ways to create a database on different operating systems. We can create a database using a graphical user interface or command line.

Creating Database Using Command Line

This process of creating a database in the command line is for Linux operating system. To proceed with this method, you first must install MySQL in your computer and then create MYSQL Login.

The complete installation process is briefly explained above. Read it carefully and then try this.

After installing MySQL, database management system in any flavor of Linux. MySQL login with your selected user name and password, you can easily create a new database using simple commands.

Data Types In MySQL

What is meant by data type in a database or programming language? Data type means what kind of data you want to store in that variable or column. For example, if you want to store the name of a customer, you must select string or varchar datatype of that column not number data type.

There are three basic data types in MySQL.

1) Numeric

2) Date and Time

3) String Types.

We divide three data types further in multiple sub categories:

Numeric Data Type

i) INT

ii) TINYINT

iii) SMALLINT

iv) BIGINT

v) FLOAT(M,D)

vi) DOUBLE(SIZE,D)

vii) DECIMAL(SIZE,D)

Date And Time

i)  DATE

ii) DATETIME

iii) TIMESTAMP

String Types

i) CHAR(SIZE)

ii) VARCHAR(SIZE)

iii) TEXT

iv) ENUM

MySQL Queries

After MYSQL login, creating database, creating tables, now there are some queries of call process provided my MySQL to perform on our data and interact with the database. Major 4 queries are there.

• INSERT QUERY
• CREATE QUERY
• DELETE QUERY
• UPDATE QUERY

INSERT QUERY

After MySQL login, we can insert a record or attribute into the table / relation. The simplest insert query is a request to add one record or tuple in to the table.

Example: 

CREATE QUERY

We can also create a table in MySQL Database Management System using console or terminal in Linux or IOs operating system. A simple query for creating a table is given below:

DELETE QUERY

MySQL provide a simpler query for deleting a record from the table. Using Delete query we cannot delete a single attribute from whole record. We delete something from a table based on a specific condition.

UPDATE QUERY

In MySQL most of the time, we need to change the record in our database without changing the all records or values. For this purpose, MySQL provide an easier query for updating a specific record or tuple.

What is IView in MySQL?

 “Database provide us a way to access or display the data using virtual table rather than an actual table called VIEW. These views do not store the actual table data. View retrieve data from the actual Table and display that data. We can use MySQL ‘select’ statement to retrieve data from the base table. Basically, view is a good feature for security purposes, because when someone accesses data through view, he can only access main view rather than base table.”

Use Of JSON in MYSQL

MySQL underpins a local JSON information type characterized by RFC 7159 that empowers productive access to information in JSON (JavaScript Object Notation) archives. The JSON information type gives these favorable circumstances over putting away JSON-position strings in a string section:

• Automatic approval of MYSQL JSON records put away in JSON segments. Invalid archives produce a blunder.
• Optimized stockpiling position. JSON archives put away in JSON sections are changed over to an inner configuration that grants fast-perused access to record components. At the point when the server later should peruse a JSON worth put away in this double organization, the worth need not be parsed from a content portrayal. The paired organization is organized to empower the server to gaze upward sub objects or settled qualities straightforwardly by key or exhibit file without perusing all qualities previously or after them in the record.

The space required to store a JSON report is generally equivalent to LONGBLOB or LONGTEXT;” Information Type Storage Requirements”, for more data. It is essential to remember that the size of any MYSQL JSON report put away in a JSON segment is constrained to the estimation of the max_allowed_packet framework variable. (At the point when the server s controlling a JSON esteem inside in memory, it very well may be bigger than this; the cutoff applies when the server stores it).You can get the measure of room required to store a JSON report utilizing the JSON_STORAGE_SIZE() work; note that for a JSON segment, the capacity size—and subsequently the worth returned by this capacity—is that utilized by the segment preceding any fractional updates that may have been performed on it (talk of the MYSQL JSON incomplete update enhancement later in this area).

Preceding MySQL 8.0.13, a JSON section cannot have a non-NULL default esteem.

Alongside the JSON information type, many SQL capacities is accessible to empower tasks on JSON values, for example, creation, control, and looking. The accompanying discourse indicates instances of these tasks. For insights concerning individual capacities, see Section 12.17, “JSON Functions”.

Partial Update Of MySQL JSON VALUES

In MySQL, the enhancer can play out a halfway, set up update of a JSON segment as opposed to expelling the old record and composing the new archive completely to the segment. This streamlining can be performed for an update that meets the accompanying conditions:

The section being refreshed was proclaimed as MYSQL JSON.

The UPDATE articulation utilizes any of the three capacities JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() to refresh the segment. An immediate task of the section esteem (for instance, UPDATE mytable SET jcol = ‘{“a”: 10, “b”: 25}’) cannot be executed as a fractional update.

Updates of various MySQL JSON sections in a solitary UPDATE articulation can be enhanced in this design; MySQL can perform halfway updates of just those segments whose qualities are refreshed utilizing the three capacities simply recorded.

The info segment and the objective section must be a similar segment; an announcement, for example, UPDATE mytable SET jcol1 = JSON_SET(jcol2, ‘$.a’, 100) can’t be executed as a halfway update.

The update can utilize settled calls to any of the capacities recorded in the past thing, in any mix, if the info and target sections are the equivalent.

All progressions supplant existing exhibit or item esteems with new ones and do not add any new components to the parent article or cluster. The worth being supplanted must be at any rate as huge as the substitution esteem. At the end of the day, the new worth cannot be any bigger than the bygone one.

Creating JSON VALUES

A JSON array contains a list of values separated by commas and enclosed within [ and ] characters:  

["abc", 10, null, true, false]
{"k1": "value", "k2": 10}

A JSON article contains a lot of key-esteem sets isolated by commas and encased inside {and} characters:

As the models show, MYSQL JSON clusters and items can contain scalar qualities that are strings or numbers, the JSON invalid strict, or the JSON Boolean genuine or false literals. Keys in JSON items must be strings. Worldly (date, time, or datetime) scalar qualities are additionally allowed:

You can likewise acquire MYSQL JSON values from various capacities provided by MySQL for this reason (“Capacities That Create JSON Values”) just as by throwing estimations of different sorts to the JSON type utilizing CAST(value AS JSON) (Converting among JSON and non-JSON values). The following a few sections depict how MySQL handles JSON qualities gave as info.

In MySQL, JSON qualities are composed as strings. MySQL parses any string utilized in a setting that requires a JSON worth and produces a blunder on the off chance that it is not legitimate as JSON. These settings incorporate embeddings an incentive into a section that has the JSON information type and passing a contention to a capacity that expects a JSON, esteem (generally appeared as json_doc or json_val in the documentation for MySQL JSON capacities), as the accompanying models illustrate:

 mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20sec)

mysql> INSERT INTO t1 VALUES ('{"key1":"value1", "key2":"value2"}');
Query OK, 1 row affected (0.01sec)

mysql> INSERT INTO t1 VALUES('[1,2');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1,2,'.

Merging JSON Values

Merging MySQL JSON arrays. In settings that join different clusters, the exhibits are converged into a solitary cluster. JSON_MERGE_PRESERVE() does this by connecting exhibits named later to the finish of the principal cluster. JSON_MERGE_PATCH() considers every contention as a cluster consisting of a solitary component (accordingly having 0 as its file) and after that applies “last copy key successes” rationale to choose just the last contention. You can analyze the outcomes appeared by this question:

 mysql> SELECT
 -> JSON_MERGE_PRESERVE('[1,2]', '["a","b","c"]','[true, false]') AS Preserve,
 -> JSON_MERGE_PATCH('[1,2]', '["a","b","c"]','[true, false]') AS PATCH,\G
 **************************1.row**************************
 Preserve:[1,2,"a","b","c", true, false]
   Patch:[true, false]

Aggregation Of JSON Values

For accumulation of MySQL JSON values, SQL NULL qualities are overlooked with respect to other information types. Non-NULL qualities are changed over to a numeric kind and totaled, aside from MIN(), MAX(), and GROUP_CONCAT(). The transformation to number should deliver a significant outcome for MYSQL JSON values that are numeric scalars, although (contingent upon the qualities) truncation and loss of accuracy may happen. Change to a number of other JSON qualities may not deliver a significant outcome.

At the end, whatever which functionality of MySQL you are performing but without creating MYSQL LOGIN you can’t do any type of interaction with it. Therefore, in simple words, it is the key root of database interaction, no matter which type of database you are using.