Introduction to MySQL

MySQL

 

MySQL is the most widely used RDBMS (Relational Database Management System) for web application data storage. This MySQL tutorial will guide you through the process of getting started with MySQL. 

 

MySQL is a quick, easy-to-use relational database management system (RDBMS) that is utilised by many small and large organisations. MySQL AB, a Swedish corporation, is responsible for its development, marketing, and support of MySQL.

 

Before diving deep into the blog, discover more over SQL tutorials. 

 

 

MySQL data types

 

• Numeric – Integers of various sizes, floating-point(real) numbers of varying precision, and formatted numbers are all included in this data type.

• Character-string – The number of characters in these data formats is either fixed or variable. CHARACTER Big OBJECT (CLOB) is a variable-length string that is used to define columns with large text values in this data type.

• Bit-string – These data types have a fixed length of bits or a variable length of bits. BINARY Huge OBJECT(BLOB) is a variable-length bit string data type that may be used to represent columns that have large binary values, such as pictures.

• Boolean – TRUE or FALSE values are available for this data type. Because SQL includes NULL values, UNKNOWN is utilised as three-valued logic.

• Date & Time – YEAR, MONTH, and DAY in the format YYYY-MM-DD make up the DATE data type. Similarly, the HOUR, MINUTE, and SECOND components of the TIME data type are in the format HH:MM:SS. Depending on the situation, these forms may alter.

• Timestamp & Interval – In addition to the DATE and TIME fields, the TIMESTAMP data type has a minimum of six slots for decimal fractions of seconds and an optional WITH TIME ZONE qualifier. A relative value can be used to increase or decrement an absolute value of a date, time, or timestamp, as shown by the INTERVAL data type.

 

(Must read: Introduction to NoSQL)

 

 

11 MySQL Commands

---

MySQL Commands

---

1. SELECT — SELECT statements are used to get information from a database.

 

SELECT column_name

FROM table_name;

 

2. UPDATE — We can change rows in a table using UPDATE commands.

 

UPDATE table_name

SET some_column = some_value

WHERE some_column = some_value;

 

3. DELETE — DELETE statements are used to get rid of rows from a table.

 

DELETE FROM table_name

WHERE some_column = some_value;

 

4. INSERT INTO — INSERT statements are used to create a new row in a table.

 

INSERT INTO table_name (column_1, column_2, column_3)

VALUES (value_1, ‘value_2’, value_3);

 

5. CREATE DATABASE — CREATE DATABASE statements are used to create databases. Make a fresh SQL database.

 

CREATE DATABASE databasename;

 

(Also read: Top 7 Commands in Data Definition Language (DDL))

 

6. ALTER DATABASE —ALTER DATABASE commands help to modify a database's properties.

 

ALTER DATABASE database_name

[COLLATE collation_name ]

 

7. CREATE TABLE — CREATE TABLE statements create a new table in the database.

 

CREATE TABLE table_name (

column_1 datatype,

column_2 datatype,

column_3 datatype

);

 

8. ALTER TABLE — ALTER TABLE commands change the columns in an existing table by adding, deleting, or modifying them.

 

ALTER TABLE table_name

ADD column_name datatype;

 

9. DROP TABLE — DROP TABLE statements drop an existing table in a database.

 

DROP TABLE table_name;

 

10. CREATE INDEX — Index statements created on existing tables to retrieve the rows quickly.
 

CREATE INDEX index_name

ON table_name (column_name1, column_name2…);

 

11. DROP INDEX — DROP INDEX statements delete an index in a table.

 

ALTER TABLE table_name

DROP INDEX index_name;

 

(Suggest blog: Top Sites for learning SQL)

 

MySQL architecture

 

MySQL’s architecture consists of several layers:

 

1. Application layer

 

The application layer of MySQL is where customers connect to MySQL and submit queries. MySQL Workbench and Looker, for example, communicate with MySQL's Application Layer to transmit queries and manage user access to the database.

 

 

2. Processor of queries

 

The query processor converts a query issued by the application layer to read or write data from the underlying storage into a query plan that the database can execute.

 

 

3. Transaction management

 

MySQL is ACID-compliant, which means that you may encapsulate a collection of queries in a transaction and have them all succeed or fail. The transaction manager handles this by executing each transaction with a COMMIT command. 

 

If a transaction fails, the transaction manager will issue a ROLLBACK command, which will reverse any database changes made during the failed transaction.

 

 

4. Recovery management

 

MySQL is extremely robust, and in the case of a crash, the recovery manager is in charge of restoring the database to its previous stable condition. It keeps track of every action performed on the database from its creation and, in the case of a crash, executes every command in the log, effectively restoring the database to its previous stable condition.

 

 

5. Management of storage

 

The storage manager is in charge of assigning the memory resources required to extract data from the actual disc and deliver it to the client.

(Recommended blog: SQL applications)

 

 

Advantages of MySQL

 

1. Data Security

 

MySQL is the most secure and dependable database management system available, and it's utilised in major online applications like WordPress, Drupal, Joomla, Facebook, and Twitter. MySQL transactions operate as a single unit, which means the transaction will not be cleared until and until each individual operational stage is completely finished. 

 

As a result, if any stage of an operation fails, the entire transaction inside that group fails. MySQL maintains data integrity in financial transactions, allowing consumers to conduct business online without concern. Money is not deducted until the entire procedure is finished, and in the event of a failure, all processes are rolled back to the previous step.

 

(Also read: SQL project ideas for beginners)

 

 

2. On-Demand Scalability

 

MySQL has unrivalled versatility, allowing for the effective administration of deeply embedded applications even in massive data centres with massive volumes of mission-critical data to be stored. 

 

It allows for comprehensive flexibility to meet the specific needs of eCommerce companies with a smaller footprint. MySQL gives businesses the most platform flexibility when it comes to adding new features and functions to their database servers.

 

 

3. High Availability 

 

MySQL's strong feature is its consistent availability; businesses that use it may expect round-the-clock uptime. MySQL has a number of cluster servers and master-slave replication setups that allow for fast failover and continuous access. 

 

MySQL is intended to perform millions of queries and thousands of transactions while maintaining unique memory caches, full-text indexes, and optimal speed, whether you're running an eCommerce website or a high-speed processing system.

 

 

4. Rock-Solid Reliability

 

Every company's first concern is the security of important corporate data. MySQL provides outstanding data security with its data protection mechanisms. 

 

SSH and SSL support provide safer connections, while powerful data encryption prevents unwanted reading of data. It also has a strong system that limits server access to authorised users and allows users to be blocked at the man-machine level. Finally, the data backup function makes point-in-time recovery much easier.

 

(Referred blog: SQL vs NoSQL)

 

 

5. Reduced Total Cost Of Ownership 

 

Businesses may save a lot of money on new projects by moving their existing database software to MySQL. MySQL's reliability and simplicity of management save you time troubleshooting, which would otherwise be spent resolving downtime and performance concerns.

 

 

Disadvantages of MySQL

 

1. Stability issues

 

MySQL is less dependable than its competitors. These difficulties with stability are connected to the way it conducts specific functions (such as references, transactions, and auditing). While the database is still functional in the face of these issues, they do make MySQL a bad choice for some applications.

 

 

2. Poor multitasking

 

Although MySQL is capable of handling almost infinite amounts of data, it has a worrying propensity to grind to a standstill when faced with too many tasks at once. Because of the poor performance scalability, anyone with a lot of concurrent users should definitely search for a solution.

 

 

3. Heavily dependent on addons 

 

Although MySQL is simple to install, it has less out-of-the-box capability than many other database systems on the market. Certain functionality, like text search and ACID compliance, is dependent on applications and addons rather than the core engine. 

 

While there are many well-made MySQL apps available, finding them may be difficult at times, leading some developers to choose an alternative that, while not as simple to instal, provides more instant functionality.

 

Conclusion

 

MySQL was created with the goal of managing large databases more quickly than other database software. For decades, MySQL has been used in demanding operational, transactional, and production contexts, and it has evolved in tandem with the shift of computing and storage to the cloud.

 

(Top read: Top Sites for learning SQL)

 

MySQL currently offers significant support for distributed applications and is included in most cloud data platforms, despite being traditionally deployed on separate computers.

 

MySQL is an older technology than many other data storage and processing options on the market today, yet it shows no indications of waning in popularity or value. Due to its speed, stability, ease of use, and broad compatibility, MySQL has recently seen a revival over even more specialised contemporary storage solutions.