Blog

MySQL Storage Engines

Storage Engines

Storage engines (underlying software component) are MySQL components, that can handle the SQL operations for different table types to store and manage information in a database. InnoDB is mostly used general-purpose storage engine and as of MySQL 5.5 and later it is the default engine. There are many storage engines available in MySQL and they are used for different purposes.

Version : MySQL 5.6

Storage engines of MySQL

Engines	Description
InnoDB	This is the default storage engine for MySQL 5.5 and higher. It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints. It supports commit, rollback, and crash-recovery capabilities to protect data. It also support row-level locking. It’s “consistent nonlocking reads” increases performance when used in a multiuser environment. It stores data in clustered indexes which reduces I/O for queries based on primary keys.
MyISAM	This storage engine, manages non transactional tables, provides high-speed storage and retrieval, supports full text searching.
MEMORY	Provides in-memory tables, formerly known as HEAP. It sores all data in RAM for faster access than storing data on disks. Useful for quick looks up of reference and other identical data.
MERGE	Groups more than one similar MyISAM tables to be treated as a single table, can handle non transactional tables, included by default.
EXAMPLE	You can create tables with this engine, but can not store or fetch data. Purpose of this is to teach developers about how to write a new storage engine.
ARCHIVE	Used to store a large amount of data, does not support indexes.
CSV	Stores data in Comma Separated Value format in a text file.
BLACKHOLE	Accepts data to store but always returns empty.
FEDERATED	Stores data in a remote database.

Other Topics :

List of Storage Engines supported by your MySQL installation

Setting the Storage Engine

Differences between InnoDB and MyISAM

List of Storage Engines supported by your MySQL installation

The following command display the status information of the server’s storage engines.

https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=1662646095&pi=t.aa~a.4097345806~i.31~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706401174&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-storage-engines.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC40MSIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMTciXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC40MSJdXSwxXQ..&dt=1706401172825&bpp=10&bdt=4478&idt=10&shv=r20240122&mjsv=m202401240101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706354346%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706354346%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0&nras=2&correlator=6076281334072&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706401171&ga_hid=716742525&ga_fc=1&u_tz=-480&u_his=3&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=1515&biw=1297&bih=644&scr_x=0&scr_y=0&eid=44759875%2C44759926%2C44759837%2C31080589%2C42532524%2C31080696%2C95322181%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=1669872665432750&tmod=1999805865&uas=3&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-installation-on-linux-and-windows.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=2&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=9&uci=a!9&btvi=2&fsb=1&dtd=1361mysql> SHOW ENGINES; +——————–+———+—————————————————————-+————–+——+————+ | Engine | Support | Comment | Transactions | XA | Savepoints | +——————–+———+—————————————————————-+————–+——+————+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +——————–+———+—————————————————————-+————–+——+————+ 9 rows in set (0.28 sec)

Setting the Storage Engine

In CREATE TABLE STATEMENT you can add ENGINE table option to mention a storage engine. See the following CREATE TABLE statements, where different engines have used :CREATE TABLE t1 (i INT) ENGINE = INNODB; CREATE TABLE t2 (i INT) ENGINE = CSV; CREATE TABLE t3 (i INT) ENGINE = MEMORY;

In MySQL 5.6, the default engine is InnoDB. The default storage engine is used if you do not mention the other engine name in ENGINE option. You can specify the default engine by using the –default-storage-engine server startup option (Command-Line Format), or by setting the default-storage-engine option in the my.cnf configuration file.

You can set the default storage engine for the current session by setting the default_storage_engine variable using set command.SET default_storage_engine=ARCHIVE;

If you want to convert a table form one storage engine to another, use an ALTER TABLE statement. See the following statement :ALTER TABLE table1 ENGINE = InnoDB;

To store the table and column definitions for a new table, MySQL always creates an .frm file. Depending on the storage engine the table’s index and data may be stored in one or more other files. The server creates the .frm file above the storage engine level.

MySQL: InnoDB Storage Engine

InnoDB is a storage engine for MySQL that balances high reliability and high performance. As of MySQL 5.5 and later, it is the default storage engine.

Feaures of InnoDB storage engine :

Storage limits	64TB	Transactions	Yes	Locking granularity	Row
MVCC (Multiversion concurrency control)	Yes	Geospatial data type support	Yes	Geospatial indexing support	No
B-tree indexes	Yes	T-tree indexes	No	Hash indexes	No
Full-text search indexes	Yes	Clustered indexes	Yes	Data caches	Yes
Index caches	Yes	Compressed data	Yes	Encrypted data	Yes
Cluster database support	No	Replication support	Yes	Foreign key support	Yes
Backup / point-in-time recovery	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes

Advantages of InnoDB storage engine

InnoDB has maximum performance when processing large data volumes.
Its DML operations (add, update and delete data) is ACID (atomic, consistent, isolated and durable) model compatible, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
Row-level locking (locks are placed on single records (rows)) system increase multi-user concurrency and performance. All InnoDB locks held by a transaction are released when the transaction is committed or aborted.
InnoDB tables arrange your data on disk to optimize queries based on primary keys.
InnoDB supports FOREIGN KEY constraints to maintain data integrity. Therefore inserts, updates, and deletes are all checked to ensure they do not result in inconsistencies across different tables.
It is possible to mix InnoDB tables with tables from other MySQL storage engines within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.

Creating InnoDB tables :
Use CREATE TABLE statement to create am InnoDB table without any special clauses. As of MySQL 5.5, it is the default MySQL storage engine. In MySQL 5.6, issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table. Here is an example :mysql> CREATE TABLE table1 (col1 INT, col2 CHAR(30), PRIMARY KEY (col1)); Query OK, 0 rows affected (1.11 sec) mysql> DESC table1; +——-+———-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+———-+——+—–+———+——-+ | col1 | int(11) | NO | PRI | 0 | | | col2 | char(30) | YES | | NULL | | +——-+———-+——+—–+———+——-+ 2 rows in set (0.21 sec)

https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=304308034&pi=t.aa~a.4097345806~i.68~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706401182&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-storage-engines.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC40MSIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMTciXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC40MSJdXSwxXQ..&dt=1706401172874&bpp=16&bdt=4528&idt=16&shv=r20240122&mjsv=m202401240101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706354346%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706354346%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0%2C715x280&nras=3&correlator=6076281334072&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706401171&ga_hid=716742525&ga_fc=1&u_tz=-480&u_his=3&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=560&biw=1312&bih=644&scr_x=0&scr_y=0&eid=44759875%2C44759926%2C44759837%2C31080589%2C42532524%2C31080696%2C95322181%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=1669872665432750&tmod=1999805865&uas=3&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-installation-on-linux-and-windows.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=10&uci=a!a&fsb=1&dtd=9658

Handling AUTO_INCREMENT in InnoDB :

InnoDB provides a method that improves scalability and performance of SQL statements that insert rows into tables with AUTO_INCREMENT columns. To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column (col1 in the example) must be defined as part of an index. See the following example :mysql> CREATE TABLE table1 (col1 INT(10) NOT NULL AUTO_INCREMENT, col2 CHAR(30), PRIMARY KEY (col1)); Query OK, 0 rows affected (0.50 sec)

Handling FOREIGN KEY Constraints in InnoDB :

MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. Foreign key definitions for InnoDB tables are subject to the following conditions :

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.
InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Limitation: InnoDB table :

Maximum 1017 columns are allowed in a table (raised in MySQL 5.6.9 from the earlier limit of 1000).
Maximum 64 secondary indexes are allowed in a table. Secondary indexes is a type of InnoDB index that represents a subset of table columns.
By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
The maximum table space size is four billion database pages (64TB) and the minimum table space size is slightly larger than 10MB.

MySQL: MyISAM Storage Engine

MyISAM storage engine is based on the older ISAM storage engine (not available now) but has many useful extensions.

Features of MyISAM storage engine :

Storage limits	256TB	Transactions	No	Locking granularity	Table
MVCC (Multiversion concurrency control)	No	Geospatial data type support	Yes	Geospatial indexing support	Yes
B-tree indexes	Yes	T-tree indexes	No	Hash indexes	No
Full-text search indexes	Yes	Clustered indexes	No	Data caches	No
Index caches	Yes	Compressed data	Yes	Encrypted data	Yes
Cluster database support	No	Replication support	Yes	Foreign key support	No
Backup / point-in-time recovery	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes

Each MyISAM table is stored on disk in three files.

An .frm file stores the table format.
The data file has an .MYD (MYData) extension.
The index file has an .MYI (MYIndex) extension.

Creating MyISAM tables :
Use CREATE TABLE statement to create am MyISAM table with ENGINE clause. As of MySQL 5.6, it is necessary to use ENGINE clause to specify the MyISAM storage engine because InnoDB is the default engine. Here is an example :mysql> CREATE TABLE table2 (col1 INT, col2 CHAR(30)) ENGINE = MYISAM;
Query OK, 0 rows affected (0.19 sec)

Main characteristics of MyISAM tables :

Up to 63-bit file length large files are supported on file systems and operating systems that support large files.
(2³²)²(1.844E+19) rows are allowed in a MyISAM table.
Maximum 64 number of indexes and 16 number of columns per index are allowed.
The maximum key length is 1000 bytes.
Internal handling of one AUTO_INCREMENT column per table is supported.
You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE
BLOB and TEXT columns can be indexed.
NULL values are permitted in indexed columns. This takes 0 to 1 bytes per key.
Each character column can have a different character set.
Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.
Tables with VARCHAR columns may have fixed or dynamic row length.
The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.
Arbitrary length UNIQUE constraints.

Corrupted MyISAM Tables :

MyISAM table format is very reliable, but in some occasion you can get corrupted tables if any of the following events occur :

The mysqld (Known as MySQL Server) process is killed in the middle of a write.
Hardware failures.
An unexpected computer shutdown occurs.
Using an external program to modify a table
A software bug in the MySQL or MyISAM code.

MySQL: MEMORY Storage Engine

The MEMORY storage engine creates tables that are stored in memory. Because the data can be crashed due to hardware or power issues, you can only use these tables as temporary work areas or read-only caches for data pulled from other tables. When the MySQL server halts or restarts, the data in MEMORY tables is lost.

Features of MEMORY storage engine :

Storage limits	RAM	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial data type support	No	Geospatial indexing support	No
B-tree indexes	Yes	T-tree indexes	No	Hash indexes	Yes
Full-text search indexes	No	Clustered indexes	No	Data caches	N/A
Index caches	N/A	Compressed data	No	Encrypted data	Yes
Cluster database support	No	Replication support	Yes	Foreign key support	No
Backup / point-in-time recover	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes

Creating MEMORY tables:
Use CREATE TABLE statement to create am MEMORY table with ENGINE clause. As of MySQL 5.6, it is necessary to use ENGINE clause to specify the MEMORY storage engine because InnoDB is the default engine. The following example shows how to create and use a MEMORY table :mysql> SELECT * FROM hr.departments; +—————+———————-+————+————-+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +—————+———————-+————+————-+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | | 110 | Accounting | 205 | 1700 | | 120 | Treasury | 0 | 1700 | |- – – – – – – -|- – – – – – – – – – – |- – – – – – |- – – – – – -| |- – – – – – – -|- – – – – – – – – – – |- – – – – – |- – – – – – -| +—————+———————-+————+————-+ 27 rows in set (0.01 sec) mysql> CREATE TABLE test7 ENGINE = MEMORY SELECT * FROM hr.departments; Query OK, 27 rows affected (0.06 sec) Records: 27 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test7 WHERE LOCATION_ID>1700; +—————+——————+————+————-+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +—————+——————+————+————-+ | 20 | Marketing | 201 | 1800 | | 40 | Human Resources | 203 | 2400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | +—————+——————+————+————-+ 4 rows in set (0.00 sec)

Remove a MEMORY table:mysql> DROP TABLE TEST7;
Query OK, 0 rows affected (0.00 sec)

https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=44759875%2C44759926%2C44759837%2C31080589%2C42532524%2C31080696%2C95322181%2C95320888%2C95321627%2C95322163%2C95323009%2C0%2C21404%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=5321706401173056&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=3&u_tz=-480&dt=1706401173058&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-storage-engines.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-installation-on-linux-and-windows.php

Indexes : The MEMORY storage engine supports both HASH and BTREE indexes. Adding a USING clause you can specify one or the other for a given index. See the following examples :CREATE TABLE test (id INT, INDEX USING HASH (id)) ENGINE = MEMORY; CREATE TABLE test (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;

When to Use MEMORY storage engine:

Operations involving transient, non-critical data such as session management or caching.
In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.
By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
The maximum tablespace size is four billion database pages (64TB) and the minimum tablespace size is slightly larger than 10MB.

MySQL: MERGE Storage Engine

The MERGE storage engine (also known as MRG_MyISAM) is a collection of identical MyISAM tables (identical column and index information with same order) that can be used as single table. You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table.

Creating MERGE tables :
To create a MERGE table, you must specify a UNION=(list-of-tables) option (indicates which MyISAM tables to use) in the CREAE TABLE statement. The following example at first we have created three tables with two rows then merge it into one table use MERGE storage engine :mysql> CREATE TABLE tabl1 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM; Query OK, 0 rows affected (0.07 sec) mysql> CREATE TABLE tabl2 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM; Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE tabl3 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO tabl1 (class, student_name) VALUES (‘V’,’Steven’), (‘V’, ‘Neena’); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO tabl2 (class, student_name) VALUES (‘VI’,’Lex’), (‘VI’, ‘Alexander’); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO tabl3 (class, student_name) VALUES (‘VII’,’Bruce’), (‘VII’, ‘David’); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE allclass (rollno INT NOT NULL, class CHAR(5), student_name CHAR(40)) ENGINE = MERGE UNION = (tabl1, tabl2, tabl3) INSERT_METHOD = LAST; Query OK, 0 rows affected (0.09 sec) mysql> select * from allclass; +——–+——-+————–+ | rollno | class | student_name | +——–+——-+————–+ | 1 | V | Steven | | 2 | V | Neena | | 1 | VI | Lex | | 2 | VI | Alexander | | 1 | VII | Bruce | | 2 | VII | David | +——–+——-+————–+ 6 rows in set (0.00 sec)

The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to ‘tutorial’ database).mysql> SHOW TABLE STATUS FROM tutorial; +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ | allclass | MRG_MYISAM | 10 | Fixed | 6 | 140 | 840 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | tabl1 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL | utf8_general_ci | NULL | | | | tabl2 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL | utf8_general_ci | NULL | | | | tabl3 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL | utf8_general_ci | NULL | | | | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | | | table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | | | test7 | MEMORY | 10 | Fixed | 27 | 39 | 59400 | 16357770 | 0 | 0 | NULL | 2014-02-17 11:06:46 | NULL | NULL | utf8_general_ci | NULL | | | +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ 7 rows in set (0.16 sec)

Security issue: If a user has access to MyISAM table, say t1, that user can create a MERGE table m1 that accesses t1. However, if the administrator revokes the user’s privileges on t1, the user can continue to access the data of t1 through m1.

MySQL: CSV Storage Engine

The CSV storage engine stores data in text files using comma-separated values format and the CSV storage engine is always compiled into the MySQL server. The server creates a table format file (.frm extension) and a data file (.csv extension) in the database directory when you create a CSV table. Both .frm and .csv files name begins with the table name. The data file is a plain text file and the storage engine saves data in comma-separated values format. The following example shows how to create and use a CSV table :

You can can read, modify the ‘color.CSV’ file by spreadsheet applications such as Microsoft Excel or StarOffice Calc.

CSV Limitations :

Does not support indexing.
Does not support partitioning.
All columns must have the NOT NULL attribute in a CSV table.

The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to ‘tutorial’ database).mysql> SHOW TABLE STATUS FROM tutorial; +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ | allclass | MRG_MYISAM | 10 | Fixed | 6 | 140 | 840 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | color | CSV | 10 | Fixed | 3 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | tabl1 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL | utf8_general_ci | NULL | | | | tabl2 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL | utf8_general_ci | NULL | | | | tabl3 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL | utf8_general_ci | NULL | | | | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | | | table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | | | test7 | MEMORY | 10 | Fixed | 27 | 39 | 59400 | 16357770 | 0 | 0 | NULL | 2014-02-17 11:06:46 | NULL | NULL | utf8_general_ci | NULL | | | +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ 8 rows in set (0.00 sec)

MySQL: ARCHIVE Storage Engine

The ARCHIVE storage engine is used to store large amounts of unindexed data in a very small footprint. The storage engine is included in MySQL binary distributions. To enable this storage engine (if you build MySQL from source), invoke CMake with the -DWITH_ARCHIVE_STORAGE_ENGINE option. When you create an ARCHIVE table, the server creates a table format file (.frm extension) in the database directory.

Features of ARCHIVE storage engine:

Storage limits	None	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial data type support	Yes	Geospatial indexing support	No
B-tree indexes	No	T-tree indexes	No	Hash indexes	No
Full-text search indexes	No	Clustered indexes	No	Data caches	No
Index caches	No	Compressed data	Yes	Encrypted data	Yes
Cluster database support	No	Replication support	Yes	Foreign key support	No
Backup / point-in-time recovery	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes

ARCHIVE storage engine supports

INSERT and SELECT.
ORDER BY operations
BLOB columns
AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index.
AUTO_INCREMENT table option in CREATE TABLE statements

ARCHIVE storage engine does not support

DELETE, REPLACE, or UPDATE
Inserting a value into an AUTO_INCREMENT column less than the current maximum column value.

ARCHIVE storage engine: Storage & Retrieval

The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/).
Rows are compressed as they are inserted.
On retrieval, rows are uncompressed on demand; there is no row cache.

MySQL: EXAMPLE Storage Engine

The EXAMPLE storage engine is a stub engine that does nothing and serve as an example in the MySQL source code that clarify how to begin writing new storage engines. To examine the source for the EXAMPLE engine, look in the storage/example directory of a MySQL source distribution. When you create an EXAMPLE table :

The server creates a table format file (.frm extension) in the database directory.
No other files are created
No data can be stored into the table.
Retrievals return an empty result.
Does not support indexing.

To enable the EXAMPLE storage engine if you build MySQL from source, invoke CMake with the -DWITH_EXAMPLE_STORAGE_ENGINE option.

MySQL: BLACKHOLE Storage Engine

The BLACKHOLE storage engine acts as a “black hole” that accepts data but returns an empty result. To enable the BLACKHOLE storage engine (in case of MySQL build from source), invoke CMake with the -DWITH_BLACKHOLE_STORAGE_ENGINE option. When you create a BLACKHOLE table, the server creates a table format file (.frm) in the database directory. The BLACKHOLE storage engine supports all kinds of indexes. Here is an example :mysql> CREATE TABLE test10 (slno INT, message CHAR(40)) ENGINE = BLACKHOLE; Query OK, 0 rows affected (0.16 sec) mysql> INSERT INTO test10 VALUES(1, ‘message1’), (2, ‘message2’); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test10; Empty set (0.03 sec)

The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to ‘tutorial’ database).mysql> SHOW TABLE STATUS FROM tutorial; +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ | allclass | MRG_MYISAM | 10 | Fixed | 6 | 140 | 840 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | color | CSV | 10 | Fixed | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | tabl1 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL | utf8_general_ci | NULL | | | | tabl2 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL | utf8_general_ci | NULL | | | | tabl3 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL | utf8_general_ci | NULL | | | | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | | | table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | | | test10 | BLACKHOLE | 10 | Fixed | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | test7 | MEMORY | 10 | Fixed | 0 | 39 | 0 | 16357770 | 0 | 0 | NULL | 2014-02-19 11:42:17 | NULL | NULL | utf8_general_ci | NULL | | | +———-+————+———+————+——+—————-+————-+——————-+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+ 9 rows in set (1.05 sec)

MySQL: FEDERATED Storage Engine

The FEDERATED storage engine is used to access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables. To include the FEDERATED storage engine (in case of MySQL build from source), invoke CMake with the -DWITH_FEDERATED_STORAGE_ ENGINE option.
To enable FEDERATED (not enabled by default in the running server), you must start the MySQL server binary using the –federated option. To check the source for the FEDERATED engine, look in the storage/ federated directory of a MySQL source distribution.

Create a FEDERATED table

You can create a FEDERATED table in the following ways :

Using CONNECTION
Using CREATE SERVER

Using CONNECTION : To use this method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. Seethe following example :CREATE TABLE federated_table ( roll_no INT(3) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(42) NOT NULL DEFAULT ”, total_marks INT(5) NOT NULL DEFAULT ‘0’, PRIMARY KEY (roll_no), INDEX stu_name (stu_name), INDEX total_marks (total_marks) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION=’mysql://feduser@remote_host:9306/federated/test10_table’;

The format of the connection string is as follows :

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

Copy

Where:

scheme : A recognized connection protocol. Only mysql is supported as the scheme value at this point.
The user name for the connection, must have been created on the remote server, and have suitable privileges to perform the required actions like SELECT, INSERT, UPDATE, and so forth on the remote table.
The password for user_name. (Optional)
host_name: The host name or IP address of the remote server.
port_num: The port number (default : 3306) for the remote server. (Optional)
db_name: The name of the database holding the remote table.
tbl_name: The name of the remote table.

Using CREATE SERVER: To use this method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. See the following example:CREATE SERVER server_name FOREIGN DATA WRAPPER wrapper_name OPTIONS (option [, option] …)

The server_name is used in the connection string when creating a new FEDERATED table.

Differences between InnoDB and MyISAM

Feature	InnoDB	MyISAM
Storage limits	64TB	256TB
Transactions	Yes	No
Locking granularity	Row	Table
MVCC	Yes	No
Geospatial data type support	Yes	Yes
Geospatial indexing support	No	Yes
B-tree indexes	Yes	Yes
T-tree indexes	No	No
Hash indexes	No	No
Full-text search indexes	Yes	Yes
Clustered indexes	Yes	No
Data caches	Yes	No
Index caches	Yes	Yes
Compressed data	Yes	Yes
Encrypted data	Yes	Yes
Cluster database support	No	No
Replication support	Yes	Yes
Foreign key support	Yes	No
Backup / point-in-time recovery	Yes	Yes
Query cache support	Yes	Yes
Update statistics for data dictionary	Yes	Yes

January 27, 2024

MySQL Connectors and APIs

Connectors and APIs

MySQL Connectors provide connectivity to the MySQL server for client programs and APIs provide low-level access to the MySQL protocol and resources. You can connect and execute MySQL statements from another language or environment, including ODBC, Python, Perl, Ruby, PHP, Java (JDBC), and native C and embedded MySQL instances through connectors and the APIs

What is a Driver?
A driver is a piece of software designed to communicate with a particular database server. The driver may also call a library, such as the ‘MySQL Client Library’ or the ‘MySQL Native Driver’. These libraries implement the low-level protocol used to communicate with the database server.

What is a Connector?
A connector refers to a piece of software that allows your application to connect to the MySQL database server. MySQL provides connectors for a variety of languages, including Python, Perl, Ruby, PHP, Java (JDBC), C etc.

What is an API?
API, an abbreviation of application program interface, is a set of programming instructions (through classes, methods, functions and variables) and standards for accessing a web-based software application or web tool.
APIs can be procedural or object-oriented. With a procedural API you call functions to carry out tasks, with the object-oriented API, you instantiate classes and then call methods on the resulting objects. Of the two the second one is usually the preferred interface, as it is more modern and leads to better-organized code.

List of MySQL Connectors

ODBC/Connector : It provides driver support for connecting (Windows, Unix, and Mac OS X platforms.) to MySQL using the Open Database Connectivity (ODBC) API.
.Net/Connector : It provides support to create .NET applications that connect to MySQL.
J/Connector : It provides driver support for connecting to MySQL from Java applications using Java Database Connectivity (JDBC) API.
Python/Connector : It provides driver support for connecting to MySQL from Python applications using Python DB API version 2.0.
C++/Connector : is a standalone replacement for the MySQL Client Library (libMySQLclient), to be used for C applications.

Note : libMySQLclient is included in MySQL distributions and in MySQL Connector/C distributions.

List of Third-Party MySQL APIs

Language	API	Type
Ada	GNU Ada MySQL Bindings	libMySQLclient	MySQL Bindings for GNU Ada
C	C API	libMySQLclient	Section 23.8, “MySQL C API”.
C	Connector/C	Replacement forlibMySQLclient	MySQL Connector/C Developer Guide.
C++	Connector/C++	libMySQLclient	MySQL Connector/C++ Developer Guide.
	MySQL++	libMySQLclient	MySQL++ Web site.
	MySQL wrapped	libMySQLclient	MySQL wrapped.
Cocoa	MySQL-Cocoa	libMySQLclient	Compatible with the Objective-C Cocoa environment. http://MySQL-cocoa.sourceforge.net/
D	MySQL for D	libMySQLclient	MySQL for D.
Eiffel	Eiffel MySQL	libMySQLclient	Section 23.14, “MySQL Eiffel Wrapper”.
Erlang	erlang-MySQL-driver	libMySQLclient	erlang-MySQL-driver.
Haskell	Haskell MySQL Bindings	Native Driver	Brian O’Sullivan’s pure Haskell MySQL bindings.
	hsql-MySQL	libMySQLclient	MySQL driver for Haskell .
Java/JDBC	Connector/J	Native Driver	MySQL Connector/J Developer Guide.
Kaya	MyDB	libMySQLclient	MyDB.
Lua	LuaSQL	libMySQLclient	LuaSQL.
.NET/Mono	Connector/Net	Native Driver	MySQL Connector/Net Developer Guide.
Objective Caml	OBjective Caml MySQL Bindings	libMySQLclient	MySQL Bindings for Objective Caml.
Octave	Database bindings for GNU Octave	libMySQLclient	Database bindings for GNU Octave.
ODBC	Connector/ODBC	libMySQLclient	MySQL Connector/ODBC Developer Guide.
Perl	DBI/DBD::MySQL	libMySQLclient	Section 23.10, “MySQL Perl API”.
	Net::MySQL	Native Driver	Net::MySQL at CPAN
PHP	MySQL, ext/MySQLinterface (deprecated)	libMySQLclient	Original MySQL API (MySQL).
	MySQLi,ext/MySQLiinterface	libMySQLclient	MySQL Improved Extension (MySQLi).
	PDO_MySQL	libMySQLclient	MySQL Functions (PDO_MySQL) (MySQL (PDO)).
	PDO MySQLnd	Native Driver
Python	Connector/Python	Native Driver	MySQL Connector/Python Developer Guide.
	MySQLdb	libMySQLclient	MySQL Python API
Ruby	MySQL/Ruby	libMySQLclient	Uses libMySQLclient. The MySQL/Ruby API.
	Ruby/MySQL	Native Driver	Section 23.12.2, “The Ruby/MySQL API”.
Scheme	Myscsh	libMySQLclient	Myscsh.
SPL	sql_MySQL	libMySQLclient	sql_MySQL for SPL.

List of MySQL Connector and Server versions

Connector	Connector version	MySQL Server version
Connector/C	6.1.0 GA	5.6, 5.5, 5.1, 5.0, 4.1
Connector/C++	1.0.5 GA	5.6, 5.5, 5.1
Connector/J	5.1.8	5.6, 5.5, 5.1, 5.0, 4.1
Connector/Net	6.5	5.6, 5.5, 5.1, 5.0
Connector/Net	6.4	5.6, 5.5, 5.1, 5.0
Connector/Net	6.3	5.6, 5.5, 5.1, 5.0
Connector/Net	6.2 (No longer supported)	5.6, 5.5, 5.1, 5.0
Connector/Net	6.1 (No longer supported)	5.6, 5.5, 5.1, 5.0
Connector/Net	6.0 (No longer supported)	5.6, 5.5, 5.1, 5.0
Connector/Net	5.2 (No longer supported)	5.6, 5.5, 5.1, 5.0
Connector/Net	1.0 (No longer supported)	5.0, 4.0
Connector/ODBC	5.1	5.6, 5.5, 5.1, 5.0, 4.1.1+
Connector/ODBC	3.51 (Unicode not supported)	5.6, 5.5, 5.1, 5.0, 4.1

January 27, 2024

MySQL workbench tutorial

MySQL Workbench is GUI based tool to work with MySQL Servers. You may use this for Server Administration, for creating Entity Relationship Diagrams and for SQL Development (run queries etc).

This tutorial discusses how to perform basic MySQL Administration tasks using MySQL Workbench.

Installing MySQL workbench

To install MySQL Workbench on Linux (we used Ubuntu), you may run the following command from your terminal.sudo apt-get install MySQL-workbench

MySQL Workbench version installed at the time of writing this tutorial is 5.2.40.

On Windows, if you are installing MySQL Community Server 5.6, MySQL Workbench is installed in the installation process of the server itself. Download the appropriate .msi file from the MySQL download page, run it and follow the instructions.

After installing it successfully, when you start, you get a window like following.

Pictorial presentation of when you open MySQL Workbench

You may see that there are three sections – SQL Development, Data Modelling, and Server Administration.

Create a new database connection

The following slideshow shows you how to create a new connection using MySQL Workbench.

Create Database and table

Once the connection is created, you may use that connection to enter SQL Editor to do SQL Development tasks like creating/modifying/deleting modify database and tables, running SQL queries etc.

Edit table data

With MySQL Workbench you can select a connection, a schema and table under that to edit table data.

Edit SQL Scripts

Click on Edit SQL Scripts to edit an existing SQL script.

Manage Connections

You can manage existing connections using MySQL Workbench.

Click on Manage Connections using the link and then from the windows as shows above, you can create a new connection, delete an existing connection, create a clone of an existing connection and change parameters like name, method, username, port, password, default schema etc. of an existing connection.

Data Modelling

Using this section, you can create a new Entity Relationship models using various components available, creating EER from an existing database, from a SQL Script and open an existing one.

The following slideshow shows how to create an EER from an existing database.

Server Administration

You can create new Server Instance and manage existing Server Instances on one hand and on the other you can manage security, that is creating and managing user and provide them with permissions to perform various tasks on MySQL objects and manage import and export data and structure.

Manage Security

Import & Export

We have covered the basic tasks you can perform with MySQL Workbench in this tutorial. Hopefully, this will be helpful to you and get you started with MySQL Administration.

January 27, 2024
MySQL installation on Linux and Windows
installation on Linux and Windows

In this section we are going to discuss how to install MySQL on windows and Linux machines.

Download MySQLYou can download MySQL from http://dev.MySQL.com/downloads/.

Install MySQL on Linux (RedHat and its variants)

1. After downloading the required RPM package, you have to run the appropriate package with root privilege.

Syntax: rpm -i PackageName.rpm

With the similar syntax, you can install MySQL client, workbench or any other software downloaded and required.

Install MySQL on Linux (Ubuntu and Debian)

Syntax for installing MySQL server
```
 apt-get install MySQL-server
 
```
Copy

Syntax for installing PHP module MySQL
```
sudo apt-get install php5-MySQL
```
Copy

Install MySQL on Windows

Download the binary distributions (MSI files) of MySQL server and other necessary MySQL tools from MySQL site.

Run MSI files on your Windows Machines. Make sure that you have privilege for installing software applications. Follow the instructions.

After the installation is completed, if you want to make PHP work with MySQL, make sure you complete the following steps –

1. Open php.ini file. Find and uncomment the following lines –
extension=php_MySQL.dll
extension=php_MySQLi.dll.

2. Create a php file on the root of your Apache or any other web server you are using. Write echo phpinfo(); in that php file. Run that PHP file form your browser (http://localhost/info.php, if your php file name is info.php). You should be able to see the following output if you have configured PHP and MySQL properly.
January 27, 2024

MySQL Tutorial

Introduction

MySQL is (as of July 2013) the world’s most widely used open-source relational database management system (RDBMS), enabling the cost-effective delivery of reliable, high-performance and scalable Web-based and embedded database applications. It is widely-used as the database component of LAMP (Linux, Apache, MySQL, Perl/PHP/Python) web application software stack.

MySQL was developed by Michael Widenius and David Axmark in 1994. Presently MySQL is maintained by Oracle (formerly Sun, formerly MySQL AB).

MySQL tutorial of w3resource is a comprehensive tutorial to learn MySQL(5.6). We have hundreds of examples covered, often with PHP code. This helps you to learn how to create PHP-MySQL based web applications.

Production Releases MySQL 5.6 : Latest General Availability (Production) release

Table of Contents :

What is MySQL?
MySQL Editions
Who uses MySQL
Supported platforms Third party tools & Front ends tools
Key features, relational database features of MySQL
w3resource MySQL tutorials
Database Management System

What is MySQL

MySQL is a database management system.
MySQL databases are relational.
MySQL software is Open Source.
The MySQL Database Server is fast, reliable, scalable, and easy to use.
MySQL Server works in client/server or embedded systems.
Initial release : 23 May 1995
Current stable release : 5.6.13 / 30 July 2013
Written in : C, C++
Operating system : Cross-platform
Available in : English
The license of MySQL is available under GNU General Public License (version 2) or proprietary EULA.
MySQL reduces the Total Cost of Ownership (TCO)
- Reducing database licensing costs by over 90%
- Cutting systems downtime by 60%
- Lowering hardware expenditure by 70%
- Reducing administration, engineering and support costs by up to 50%

MySQL Editions

There are five types MySQL editions.

MySQL Enterprise Edition : This edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.
MySQL Standard Edition : This edition enables you to deliver high-performance and scalable Online Transaction Processing (OLTP) applications. It provides the ease of use that has made MySQL famous along with industrial-strength performance and reliability.
MySQL Classic Edition : This edition is the ideal embedded database for ISVs, OEMs, and VARs developing read-intensive applications using the MyISAM storage engine.
MySQL Cluster CGE : MySQL Cluster is a scalable, real-time, ACID-compliant database, combining 5 x 9s availability and open source technology. With a distributed, multi-master architecture and no single point of failure, MySQL Cluster scales horizontally on commodity hardware accessed via SQL and NoSQL APIs.
MySQL Embedded (OEM/ISV) : MySQL Database is a full-featured, zero-administration database that more than 3000 ISVs, OEMs, and VARs rely on to bring their products to market faster and make them more competitive.

Who uses MySQL

Some of the most visited websites like Flickr, Facebook, Wikipedia, Google (not for search), YouTube.
Content Management Systems like WordPress, phpBB, Drupal, Joomla, TYPO3, MODx.
Last but not least, a large number of web developers across the world.

MySQL supported platforms

MySQL runs on

Linux (RedHat, SUSE, Mandrake, Debian)
Embedded Linux (MontaVista, LynuxWorks BlueCat)
Unix (Solaris, HP-UX, AIX)
BSD (Mac OS X, FreeBSD)
Windows (Windows 2000, Windows NT)
RTOS (QNX)

MySQL supported third party tools

Drivers :

ODBC
JDBC
.NET
C++

Languages :

C
C++
C#
Java
Delphi
Visual Basic
Perl
Python
PHP

Development Tools :

Microsoft Visual Studio
Borland Delphi and JBuilder
Eclipse
NetBeans

Some of the widely used MySQL front ends (tools for managing MySQL)

The MySQL GUI Tools Bundle is a cross-platform open source suite of desktop applications, building and manipulating the data within MySQL databases Development on the GUI Tools bundle has stopped, The GUI Tools bundle has been replaced by MySQL Workbench with the beta releases of MySQL Workbench 5.2. Currently, MySQL Workbench Team are working on Version 6.0. The first public beta, labeled version 6.0.2, was released on June 14, 2013.
There are a lot of third-party free and proprietary graphical administration applications available that integrate with MySQL and users to work with the database. Here are some third-party tools for managing MySQL :

Tools	Description
phpMyAdmin	Third party, Free, Web-based
HeidiSQL	Third party, Free, For Windows
Adminer	Third party, Free
DBEdit	Third party, Free
dbForge GUI Tools	Third party, Free
Navicat	Third party, Commercial
Maatkit	Third party, Command line, free
MySQL Sandbox	Third party, Command line, free
SQLBuddy	A free Web-based front end, developed in PHP.
SQLyog	Commercial, but a free ‘community’ edition is available.
Toad for MySQL	Third party, free from Quest Software

Key features of MySQL

MySQL follows ANSI SQL 99, the standard SQL.
Cross Platform.
Unicode support
ACID compliance
Stored procedures
Triggers
Cursors
Views
Information schema
Strict mode (ensures MySQL does not truncate or otherwise modify data to conform to an underlying data type when an incompatible value is inserted into that type)
Independent storage engines
Transactions with the InnoDB and NDB Cluster storage engines; savepoints with InnoDB
SSL support
Query caching
Sub-SELECTs (i.e. nested SELECTs)
Replication support (i.e. Master-Master Replication & Master-Slave Replication) with one master per slave, many slaves per master
Full-text indexing and searching using MyISAM engine
Embedded database library
Shared-nothing clustering through MySQL Cluster
Support for hotbackup
Multiple storage engines, allowing one to choose the one that is most effective for each table in the application
Commit grouping, gathering multiple transactions from multiple connections together to increase the number of commits per second.

Key relational database features supported by MySQL

High-performance
Main-memory tables with disk-based tables
Single-User and Multi-User
Multi-Threaded
Multi-Process
SQL-92 and SQL-99
ACID Transactions
Referential Integrity
Cascading Updates and Deletes
Multi-table Joins
Row-level Locking
Online Hot Backup
Replication
Clustering
BLOBs (Binary Large Objects)
UDFs (User Defined Objects)
OLTP (On-Line Transaction Processing)
Unicode and Double-Byte character support
Drivers for ODBC, JDBC, .NET and C++

Tutorial objectives

1. A short and concise description to help you to understand about the topic / MySQL command.

2. The syntax of the MySQL command with descriptions of all the parameters used.

https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=21404%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=781706400850607&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=1&u_tz=-480&dt=1706400850609&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-tutorials.php&referer=https%3A%2F%2Fwww.google.com%2F

3. Display of the actual table on which the MySQL command is being applied.

4. How that MySQL command can be executed with PHP.

5. Explanation of the code.

6. The output of the MySQL command when executed from windows command prompt.

7. Display of the output with PHP in a separate browser window.

8. After reading this largest third party online MySQL tutorial by w3resource, you will be able to install, manage and develop PHP-MySQL web applications by your own.

9. You may refer MySQL Documentation along with this tutorial.

We have a comprehensive, SQL TUTORIAL -2003 standard , which will help you to understand how to prepare queries to fetch data against various conditions.

January 27, 2024

Abstraction in Python
Abstraction is used to hide the internal functionality of the function from the users. The users only interact with the basic implementation of the function, but inner working is hidden. User is familiar with that “what function does” but they don’t know “how it does.”

In simple words, we all use the smartphone and very much familiar with its functions such as camera, voice-recorder, call-dialing, etc., but we don’t know how these operations are happening in the background. Let’s take another example – When we use the TV remote to increase the volume. We don’t know how pressing a key increases the volume of the TV. We only know to press the “+” button to increase the volume.

That is exactly the abstraction that works in the object-oriented concept.

Why Abstraction is Important?

In Python, an abstraction is used to hide the irrelevant data/class in order to reduce the complexity. It also enhances the application efficiency. Next, we will learn how we can achieve abstraction using the Python program.

Abstraction classes in Python

In Python, abstraction can be achieved by using abstract classes and interfaces.

A class that consists of one or more abstract method is called the abstract class. Abstract methods do not contain their implementation. Abstract class can be inherited by the subclass and abstract method gets its definition in the subclass. Abstraction classes are meant to be the blueprint of the other class. An abstract class can be useful when we are designing large functions. An abstract class is also helpful to provide the standard interface for different implementations of components. Python provides the abc module to use the abstraction in the Python program. Let’s see the following syntax.

Syntax
```
from abc import ABC  

class ClassName(ABC):
```
We import the ABC class from the abc module.

Abstract Base Classes

An abstract base class is the common application program of the interface for a set of subclasses. It can be used by the third-party, which will provide the implementations such as with plugins. It is also beneficial when we work with the large code-base hard to remember all the classes.

Working of the Abstract Classes

Unlike the other high-level language, Python doesn’t provide the abstract class itself. We need to import the abc module, which provides the base for defining Abstract Base classes (ABC). The ABC works by decorating methods of the base class as abstract. It registers concrete classes as the implementation of the abstract base. We use the @abstractmethod decorator to define an abstract method or if we don’t provide the definition to the method, it automatically becomes the abstract method. Let’s understand the following example.

Example –
```
# Python program demonstrate  

# abstract base class work   

from abc import ABC, abstractmethod   

class Car(ABC):   

    def mileage(self):   

        pass  

  

class Tesla(Car):   

    def mileage(self):   

        print("The mileage is 30kmph")   

class Suzuki(Car):   

    def mileage(self):   

        print("The mileage is 25kmph ")   

class Duster(Car):   

     def mileage(self):   

          print("The mileage is 24kmph ")   

  

class Renault(Car):   

    def mileage(self):   

            print("The mileage is 27kmph ")   

          

# Driver code   

t= Tesla ()   

t.mileage()   

  

r = Renault()   

r.mileage()   

  

s = Suzuki()   

s.mileage()   

d = Duster()   

d.mileage()
```
Output:The mileage is 30kmph The mileage is 27kmph The mileage is 25kmph The mileage is 24kmph

Explanation –

In the above code, we have imported the abc module to create the abstract base class. We created the Car class that inherited the ABC class and defined an abstract method named mileage(). We have then inherited the base class from the three different subclasses and implemented the abstract method differently. We created the objects to call the abstract method.

Let’s understand another example.

Let’s understand another example.

Example –
```
# Python program to define   

# abstract class  

  

from abc import ABC  

  

class Polygon(ABC):   

  

   # abstract method   

   def sides(self):   

      pass  

  

class Triangle(Polygon):   

  

     

   def sides(self):   

      print("Triangle has 3 sides")   

  

class Pentagon(Polygon):   

  

     

   def sides(self):   

      print("Pentagon has 5 sides")   

  

class Hexagon(Polygon):   

  

   def sides(self):   

      print("Hexagon has 6 sides")   

  

class square(Polygon):   

  

   def sides(self):   

      print("I have 4 sides")   

  

# Driver code   

t = Triangle()   

t.sides()   

  

s = square()   

s.sides()   

  

p = Pentagon()   

p.sides()   

  

k = Hexagon()   

K.sides()
```
Output:Triangle has 3 sides Square has 4 sides Pentagon has 5 sides Hexagon has 6 sides

Explanation –

In the above code, we have defined the abstract base class named Polygon and we also defined the abstract method. This base class inherited by the various subclasses. We implemented the abstract method in each subclass. We created the object of the subclasses and invoke the sides() method. The hidden implementations for the sides() method inside the each subclass comes into play. The abstract method sides() method, defined in the abstract class, is never invoked.

Points to Remember

Below are the points which we should remember about the abstract base class in Python.
- An Abstract class can contain the both method normal and abstract method.
- An Abstract cannot be instantiated; we cannot create objects for the abstract class.
Abstraction is essential to hide the core functionality from the users. We have covered the all the basic concepts of Abstraction in Python.
January 27, 2024

Python Inheritance

Inheritance is an important aspect of the object-oriented paradigm. Inheritance provides code reusability to the program because we can use an existing class to create a new class instead of creating it from scratch.

In inheritance, the child class acquires the properties and can access all the data members and functions defined in the parent class. A child class can also provide its specific implementation to the functions of the parent class. In this section of the tutorial, we will discuss inheritance in detail.

In python, a derived class can inherit base class by just mentioning the base in the bracket after the derived class name. Consider the following syntax to inherit a base class into the derived class.

Syntax

class derived-class(base class):  

    <class-suite>

A class can inherit multiple classes by mentioning all of them inside the bracket. Consider the following syntax.

Syntax

class Animal:  

    def speak(self):  

        print("Animal Speaking")  

#child class Dog inherits the base class Animal  

class Dog(Animal):  

    def bark(self):  

        print("dog barking")  

d = Dog()  

d.bark()  

d.speak()

Example 1

class Animal:  

    def speak(self):  

        print("Animal Speaking")  

#child class Dog inherits the base class Animal  

class Dog(Animal):  

    def bark(self):  

        print("dog barking")  

d = Dog()  

d.bark()  

d.speak()

Output:dog barking Animal Speaking

Python Multi-Level inheritance

Multi-Level inheritance is possible in python like other object-oriented languages. Multi-level inheritance is archived when a derived class inherits another derived class. There is no limit on the number of levels up to which, the multi-level inheritance is archived in python.

The syntax of multi-level inheritance is given below.

Syntax

class class1:  

    <class-suite>   

class class2(class1):  

    <class suite>  

class class3(class2):  

    <class suite>  

.  

.

Example

class Animal:  

    def speak(self):  

        print("Animal Speaking")  

#The child class Dog inherits the base class Animal  

class Dog(Animal):  

    def bark(self):  

        print("dog barking")  

#The child class Dogchild inherits another child class Dog  

class DogChild(Dog):  

    def eat(self):  

        print("Eating bread...")  

d = DogChild()  

d.bark()  

d.speak()  

d.eat()

Output:dog barking Animal Speaking Eating bread…

Python Multiple inheritance

Python provides us the flexibility to inherit multiple base classes in the child class.

The syntax to perform multiple inheritance is given below.

Syntax

class Base1:  

    <class-suite>  

  

class Base2:  

    <class-suite>  

.  

.  

.  

class BaseN:  

    <class-suite>  

  

class Derived(Base1, Base2, ...... BaseN):  

    <class-suite>

Example

class Calculation1:  

    def Summation(self,a,b):  

        return a+b;  

class Calculation2:  

    def Multiplication(self,a,b):  

        return a*b;  

class Derived(Calculation1,Calculation2):  

    def Divide(self,a,b):  

        return a/b;  

d = Derived()  

print(d.Summation(10,20))  

print(d.Multiplication(10,20))  

print(d.Divide(10,20))

Output:30 200 0.5

The issubclass(sub,sup) method

The issubclass(sub, sup) method is used to check the relationships between the specified classes. It returns true if the first class is the subclass of the second class, and false otherwise.

Consider the following example.

Example

class Calculation1:  

    def Summation(self,a,b):  

        return a+b;  

class Calculation2:  

    def Multiplication(self,a,b):  

        return a*b;  

class Derived(Calculation1,Calculation2):  

    def Divide(self,a,b):  

        return a/b;  

d = Derived()  

print(issubclass(Derived,Calculation2))  

print(issubclass(Calculation1,Calculation2))

Output:True False

The isinstance (obj, class) method

The isinstance() method is used to check the relationship between the objects and classes. It returns true if the first parameter, i.e., obj is the instance of the second parameter, i.e., class.

Consider the following example.

Example

class Calculation1:  

    def Summation(self,a,b):  

        return a+b;  

class Calculation2:  

    def Multiplication(self,a,b):  

        return a*b;  

class Derived(Calculation1,Calculation2):  

    def Divide(self,a,b):  

        return a/b;  

d = Derived()  

print(isinstance(d,Derived))

Output:

Method Overriding

We can provide some specific implementation of the parent class method in our child class. When the parent class method is defined in the child class with some specific implementation, then the concept is called method overriding. We may need to perform method overriding in the scenario where the different definition of a parent class method is needed in the child class.

Consider the following example to perform method overriding in python.

Example

class Animal:  

    def speak(self):  

        print("speaking")  

class Dog(Animal):  

    def speak(self):  

        print("Barking")  

d = Dog()  

d.speak()

Output:Barking

Real Life Example of method overriding



class Bank:  



    def getroi(self):  



        return 10;  



class SBI(Bank):  



    def getroi(self):  



        return 7;  



  



class ICICI(Bank):  



    def getroi(self):  



        return 8;  



b1 = Bank()  



b2 = SBI()  



b3 = ICICI()  



print("Bank Rate of interest:",b1.getroi());  



print("SBI Rate of interest:",b2.getroi());  



print("ICICI Rate of interest:",b3.getroi());

Output:Bank Rate of interest: 10 SBI Rate of interest: 7 ICICI Rate of interest: 8

Data abstraction in python

Abstraction is an important aspect of object-oriented programming. In python, we can also perform data hiding by adding the double underscore (___) as a prefix to the attribute which is to be hidden. After this, the attribute will not be visible outside of the class through the object.

Consider the following example.

Example



class Employee:  



    __count = 0;  



    def __init__(self):  



        Employee.__count = Employee.__count+1  



    def display(self):  



        print("The number of employees",Employee.__count)  



emp = Employee()  



emp2 = Employee()  



try:  



    print(emp.__count)  



finally:  



    emp.display()

Output:The number of employees 2 AttributeError: ‘Employee’ object has no attribute ‘__count’

January 27, 2024

Python Constructor

A constructor is a special type of method (function) which is used to initialize the instance members of the class.

In C++ or Java, the constructor has the same name as its class, but it treats constructor differently in Python. It is used to create an object.

Constructors can be of two types.

Parameterized Constructor
Non-parameterized Constructor

Constructor definition is executed when we create the object of this class. Constructors also verify that there are enough resources for the object to perform any start-up task.

Creating the constructor in python

In Python, the method the __init__() simulates the constructor of the class. This method is called when the class is instantiated. It accepts the self-keyword as a first argument which allows accessing the attributes or method of the class.

We can pass any number of arguments at the time of creating the class object, depending upon the __init__() definition. It is mostly used to initialize the class attributes. Every class must have a constructor, even if it simply relies on the default constructor.

Consider the following example to initialize the Employee class attributes.

Example

class Employee:  

    def __init__(self, name, id):  

        self.id = id  

        self.name = name  

  

    def display(self):  

        print("ID: %d \nName: %s" % (self.id, self.name))  

  

  

emp1 = Employee("John", 101)  

emp2 = Employee("David", 102)  

  

# accessing display() method to print employee 1 information  

  

emp1.display()  

  

# accessing display() method to print employee 2 information  

emp2.display()

Output:ID: 101 Name: John ID: 102 Name: David

Counting the number of objects of a class

The constructor is called automatically when we create the object of the class. Consider the following example.

Example

class Student:    

    count = 0    

    def __init__(self):    

        Student.count = Student.count + 1    

s1=Student()    

s2=Student()    

s3=Student()    

print("The number of students:",Student.count)

Output:The number of students: 3

Python Non-Parameterized Constructor

The non-parameterized constructor uses when we do not want to manipulate the value or the constructor that has only self as an argument. Consider the following example.

Example

class Student:  

    # Constructor - non parameterized  

    def __init__(self):  

        print("This is non parametrized constructor")  

    def show(self,name):  

        print("Hello",name)  

student = Student()  

student.show("John")

Python Parameterized Constructor

The parameterized constructor has multiple parameters along with the self. Consider the following example.

Example

class Student:  

    # Constructor - parameterized  

    def __init__(self, name):  

        print("This is parametrized constructor")  

        self.name = name  

    def show(self):  

        print("Hello",self.name)  

student = Student("John")  

student.show()

Output:This is parametrized constructor Hello John

Python Default Constructor

When we do not include the constructor in the class or forget to declare it, then that becomes the default constructor. It does not perform any task but initializes the objects. Consider the following example.

Example

class Student:  

    roll_num = 101  

    name = "Joseph"  

  

    def display(self):  

        print(self.roll_num,self.name)  

  

st = Student()  

st.display()

Output:101 Joseph

More than One Constructor in Single class

Let’s have a look at another scenario, what happen if we declare the two same constructors in the class.

Example

class Student:  

    def __init__(self):  

        print("The First Constructor")  

    def __init__(self):  

        print("The second contructor")  

  

st = Student()

Output:

In the above code, the object st called the second constructor whereas both have the same configuration. The first method is not accessible by the st object. Internally, the object of the class will always call the last constructor if the class has multiple constructors.

Note: The constructor overloading is not allowed in Python.

Python built-in class functions

The built-in functions defined in the class are described in the following table.

SN	Function	Description
1	getattr(obj,name,default)	It is used to access the attribute of the object.
2	setattr(obj, name,value)	It is used to set a particular value to the specific attribute of an object.
3	delattr(obj, name)	It is used to delete a specific attribute.
4	hasattr(obj, name)	It returns true if the object contains some specific attribute.

Example

class Student:  

    def __init__(self, name, id, age):  

        self.name = name  

        self.id = id  

        self.age = age  

  

    # creates the object of the class Student  

s = Student("John", 101, 22)  

  

# prints the attribute name of the object s  

print(getattr(s, 'name'))  

  

# reset the value of attribute age to 23  

setattr(s, "age", 23)  

  

# prints the modified value of age  

print(getattr(s, 'age'))  

  

# prints true if the student contains the attribute with name id  

  

print(hasattr(s, 'id'))  

# deletes the attribute age  

delattr(s, 'age')  

  

# this will give an error since the attribute age has been deleted  

print(s.age)

Output:John 23 True AttributeError: ‘Student’ object has no attribute ‘age’

Built-in class attributes

Along with the other attributes, a Python class also contains some built-in class attributes which provide information about the class.

The built-in class attributes are given in the below table.

SN	Attribute	Description
1	__dict__	It provides the dictionary containing the information about the class namespace.
2	__doc__	It contains a string which has the class documentation
3	__name__	It is used to access the class name.
4	__module__	It is used to access the module in which, this class is defined.
5	__bases__	It contains a tuple including all base classes.

Example

class Student:    

    def __init__(self,name,id,age):    

        self.name = name;    

        self.id = id;    

        self.age = age    

    def display_details(self):    

        print("Name:%s, ID:%d, age:%d"%(self.name,self.id))    

s = Student("John",101,22)    

print(s.__doc__)    

print(s.__dict__)    

print(s.__module__)

Output:None {‘name’: ‘John’, ‘id’: 101, ‘age’: 22} __main__

January 27, 2024

Classes and Objects in Python
Python is an object-oriented programming language that offers classes, which are a potent tool for writing reusable code. To describe objects with shared characteristics and behaviours, classes are utilised. We shall examine Python’s ideas of classes and objects in this article.

Classes in Python:

In Python, a class is a user-defined data type that contains both the data itself and the methods that may be used to manipulate it. In a sense, classes serve as a template to create objects. They provide the characteristics and operations that the objects will employ.

Suppose a class is a prototype of a building. A building contains all the details about the floor, rooms, doors, windows, etc. we can make as many buildings as we want, based on these details. Hence, the building can be seen as a class, and we can create as many objects of this class.

Creating Classes in Python

In Python, a class can be created by using the keyword class, followed by the class name. The syntax to create a class is given below.

Syntax
```
class ClassName:    

    #statement_suite
```
In Python, we must notice that each class is associated with a documentation string which can be accessed by using <class-name>.__doc__. A class contains a statement suite including fields, constructor, function, etc. definition.

Example:

Code:
```
class Person:  

    def __init__(self, name, age):  

        # This is the constructor method that is called when creating a new Person object  

        # It takes two parameters, name and age, and initializes them as attributes of the object  

        self.name = name  

        self.age = age  

    def greet(self):  

        # This is a method of the Person class that prints a greeting message  

        print("Hello, my name is " + self.name)
```
Name and age are the two properties of the Person class. Additionally, it has a function called greet that prints a greeting.

Objects in Python:

An object is a particular instance of a class with unique characteristics and functions. After a class has been established, you may make objects based on it. By using the class constructor, you may create an object of a class in Python. The object’s attributes are initialised in the constructor, which is a special procedure with the name __init__.

Syntax:
```
# Declare an object of a class  



object_name = Class_Name(arguments) 
```
Example:

Code:
```
class Person:  

    def __init__(self, name, age):  

        self.name = name    

        self.age = age      

    def greet(self):  

        print("Hello, my name is " + self.name)  

  

# Create a new instance of the Person class and assign it to the variable person1  

person1 = Person("Ayan", 25)  

person1.greet()
```
Output:“Hello, my name is Ayan”

The self-parameter

The self-parameter refers to the current instance of the class and accesses the class variables. We can use anything instead of self, but it must be the first parameter of any function which belongs to the class.

_ _init_ _ method

In order to make an instance of a class in Python, a specific function called __init__ is called. Although it is used to set the object’s attributes, it is often referred to as a constructor.

The self-argument is the only one required by the __init__ method. This argument refers to the newly generated instance of the class. To initialise the values of each attribute associated with the objects, you can declare extra arguments in the __init__ method.

Class and Instance Variables

All instances of a class exchange class variables. They function independently of any class methods and may be accessed through the use of the class name. Here’s an illustration:

Code:
```
class Person:  

    count = 0   # This is a class variable  

  

    def __init__(self, name, age):  

        self.name = name    # This is an instance variable  

        self.age = age  

        Person.count += 1   # Accessing the class variable using the name of the class  

person1 = Person("Ayan", 25)  

person2 = Person("Bobby", 30)  

print(Person.count)
```
Output:2

Whereas, instance variables are specific to each instance of a class. They are specified using the self-argument in the __init__ method. Here’s an illustration:

Code:
```
class Person:  

    def __init__(self, name, age):  

        self.name = name    # This is an instance variable  

        self.age = age  

person1 = Person("Ayan", 25)  

person2 = Person("Bobby", 30)  

print(person1.name)    

print(person2.age)
```
Output:Ayan 30

Class variables are created separately from any class methods and are shared by all class copies. Every instance of a class has its own instance variables, which are specified in the __init__ method utilising the self-argument.

Conclusion:

In conclusion, Python’s classes and objects notions are strong ideas that let you write reusable programmes. You may combine information and capabilities into a single entity that is able to be used to build many objects by establishing a class. Using the dot notation, you may access an object’s methods and properties after it has been created. You can develop more logical, effective, and manageable code by comprehending Python’s classes and objects.
January 27, 2024

Python OOPs Concepts

Like other general-purpose programming languages, Python is also an object-oriented language since its beginning. It allows us to develop applications using an Object-Oriented approach. In Python, we can easily create and use classes and objects.

An object-oriented paradigm is to design the program using classes and objects. The object is related to real-word entities such as book, house, pencil, etc. The oops concept focuses on writing the reusable code. It is a widespread technique to solve the problem by creating objects.

Major principles of object-oriented programming system are given below.

Class
Object
Method
Inheritance
Polymorphism
Data Abstraction
Encapsulation

Class

The class can be defined as a collection of objects. It is a logical entity that has some specific attributes and methods. For example: if you have an employee class, then it should contain an attribute and method, i.e. an email id, name, age, salary, etc.

Syntax

class ClassName:     

        <statement-1>     

        .     

        .      

        <statement-N>

Object

The object is an entity that has state and behavior. It may be any real-world object like the mouse, keyboard, chair, table, pen, etc.

Everything in Python is an object, and almost everything has attributes and methods. All functions have a built-in attribute __doc__, which returns the docstring defined in the function source code.

When we define a class, it needs to create an object to allocate the memory. Consider the following example.

Example:

class car:  

    def __init__(self,modelname, year):  

        self.modelname = modelname  

        self.year = year  

    def display(self):  

        print(self.modelname,self.year)  

  

c1 = car("Toyota", 2016)  

c1.display()

Output:Toyota 2016

In the above example, we have created the class named car, and it has two attributes modelname and year. We have created a c1 object to access the class attribute. The c1 object will allocate memory for these values. We will learn more about class and object in the next tutorial.

Method

The method is a function that is associated with an object. In Python, a method is not unique to class instances. Any object type can have methods.

Inheritance

Inheritance is the most important aspect of object-oriented programming, which simulates the real-world concept of inheritance. It specifies that the child object acquires all the properties and behaviors of the parent object.

By using inheritance, we can create a class which uses all the properties and behavior of another class. The new class is known as a derived class or child class, and the one whose properties are acquired is known as a base class or parent class.

It provides the re-usability of the code.

Polymorphism

Polymorphism contains two words “poly” and “morphs”. Poly means many, and morph means shape. By polymorphism, we understand that one task can be performed in different ways. For example – you have a class animal, and all animals speak. But they speak differently. Here, the “speak” behavior is polymorphic in a sense and depends on the animal. So, the abstract “animal” concept does not actually “speak”, but specific animals (like dogs and cats) have a concrete implementation of the action “speak”.

Encapsulation

Encapsulation is also an essential aspect of object-oriented programming. It is used to restrict access to methods and variables. In encapsulation, code and data are wrapped together within a single unit from being modified by accident.

Data Abstraction

Data abstraction and encapsulation both are often used as synonyms. Both are nearly synonyms because data abstraction is achieved through encapsulation.

Abstraction is used to hide internal details and show only functionalities. Abstracting something means to give names to things so that the name captures the core of what a function or a whole program does.

Object-oriented vs. Procedure-oriented Programming languages

The difference between object-oriented and procedure-oriented programming is given below:

Index	Object-oriented Programming	Procedural Programming
1.	Object-oriented programming is the problem-solving approach and used where computation is done by using objects.	Procedural programming uses a list of instructions to do computation step by step.
2.	It makes the development and maintenance easier.	In procedural programming, It is not easy to maintain the codes when the project becomes lengthy.
3.	It simulates the real world entity. So real-world problems can be easily solved through oops.	It doesn’t simulate the real world. It works on step by step instructions divided into small parts called functions.
4.	It provides data hiding. So it is more secure than procedural languages. You cannot access private data from anywhere.	Procedural language doesn’t provide any proper way for data binding, so it is less secure.
5.	Example of object-oriented programming languages is C++, Java, .Net, Python, C#, etc.	Example of procedural languages are: C, Fortran, Pascal, VB etc.

January 27, 2024

Blog

Storage Engines

Connectors and APIs

installation on Linux and Windows

Introduction

Why Abstraction is Important?

Abstraction classes in Python

Abstract Base Classes

Working of the Abstract Classes

Points to Remember

Syntax

Syntax

Example 1

Python Multi-Level inheritance

Syntax

Example

Python Multiple inheritance

Syntax

Example

The issubclass(sub,sup) method

Example

The isinstance (obj, class) method

Example

Method Overriding

Example

Real Life Example of method overriding

Data abstraction in python

Example

Creating the constructor in python

Example

Counting the number of objects of a class

Example

Python Non-Parameterized Constructor

Example

Python Parameterized Constructor

Example

Python Default Constructor

Example

More than One Constructor in Single class

Example

Note: The constructor overloading is not allowed in Python.

Python built-in class functions

Example

Built-in class attributes

Example

Classes in Python:

Creating Classes in Python

Objects in Python:

The self-parameter

_ _init_ _ method

Class and Instance Variables

Conclusion:

Class

Object

Method

Inheritance

Polymorphism

Encapsulation

Data Abstraction

Object-oriented vs. Procedure-oriented Programming languages