MySQL is a popular open-source relational database management system (RDBMS) that is free to use. Despite its richness in features, it is easy to setup and use.
The name MySQL derives from the name of co-founder Michael Widenius's daughter and SQL.
MySQL runs on-premise and on virtually deployed platforms such as Windows, Linux, and UNIX.
Developers may use MySQL for free under the GNU General Public License.
However, enterprise licenses must be obtained from Oracle and can be downloaded from this link.
MySQL provides multi-user access over a network or the cloud and can be integrated into different applications.
MySQL follows the client-server model in which a client sends requests and the MySQL server handles these requests and then returning a response. The server is available as a separate program in a client-server environment, or as a library that can be embedded in standalone applications.
MySQL can handle large data stores quickly and with ease. Users can access it from different MySQL client interfaces at different locations. MySQL comes with several utility programs with which administrative tasks can be performed.
Uses standard SQL commands
Available in over 20 platforms such as Windows, Mac, Linux, and Unix
Supports large databases with millions of records and many data types
Uses an access-privilege and encrypted password system
Allows several connection protocols such as TCP/IP sockets
May replicate data and partition tables for better performance and durability
Supports numerous client and utility programs as well as administration tools
Enables data to be stored and accessed across multiple storage engines, such as InnoDB, CSV, and NDB
MySQL Workbench is a graphical tool for database management.
It is used for SQL design, development, maintenance, and administration.
Workbench is available on Windows, Linux and Mac OS X.
MySQL Workbench main functionalities are as follows.
SQL Development. Create and manage database connections. It also allows you to configure connection parameters. The built-in SQL Editor executes SQL queries using the database connection.
Data Modeling (Design). Create database models with graphical schema. Reverse and forward engineer between a schema and a live database. The Table Editor allows you to edit all aspects of database tables including columns, indexes, triggers, partitions, options, inserts, privileges, routines, and views.
Server Administration. Administer MySQL server instances by managing users, performing backup and recovery, auditing data, viewing database health, and monitoring MySQL server performance.
Data Migration. Easily migrate from Microsoft SQL Server, Microsoft Access, Sybase ASE, SQLite, SQL Anywhere, PostgreSQL, and other RDBMS to MySQL. It also supports migration from earlier versions of MySQL to the latest.
Enterprise Support. Supports MySQL Enterprise Backup, MySQL Firewall, and MySQL Audit.
Table optimization helps you improve data input and output speeds.
It is recommended for tables performing many writes, resulting in fragmentation.
Fragmentated tables take up more space than necessary which slows query execution.
Optimization arranges data inside the tables which speeds up queries.
Optimizing large tables takes a long of time and requires locking which affects the transactional system. It is recommended that you follow these steps:
Syntax for MySQL table optimization for single table.
OPTIMIZE TABLE table-name
Syntax for MySQL table optimization for multiple tables.
OPTIMIZE TABLE table-name-1, table-name-2, ..., table-name-n
Optimize the Customer, Product, and Supplier tables.
OPTIMIZE TABLE Customer, Product, Supplier
The GROUP BY clause groups a set of rows by value, column, or expression.
GROUP BY returns a single row for each group in the result set.
GROUP BY often uses aggregate functions, such as COUNT, SUM, MAX etc.
Syntax for MySQL GROUP BY clause.
SELECT * | column-names | aggregate-function FROM table-name WHERE condition GROUP BY column-names | expression
The WHERE clause is optional.
List the number of customers from each country.
SELECT Country, COUNT(Id) FROM Customer GROUP BY Country
MySQL supports a variety of string, numeric, and datetime data types.
| Data Type | Description | Size |
|---|---|---|
CHAR(size) |
A fixed length string which may contain letters, numbers, and special characters |
0 to 255 Default is 1 |
VARCHAR(size) |
A variable length string which may contain letters, numbmers, and special characters | 0 to 65535 (required) |
BINARY(size) |
Equal to CHAR() but stores string as binary byte |
Depends on the size specified (required) Default is 1 |
VARBINARY(size) |
Equal to VARCHAR() but stores string as binary byte |
Depends on the size specified (required) |
TINYBLOB |
For Binary Large Objects (BLOBs) | Max is 255 bytes |
TINYTEXT |
Holds a string | Max 255 characters |
TEXT(size) |
Holds a string | Max is 65,535 bytes |
BLOB(size) |
For Binary Large Objects (BLOBs) | Max is 65,535 bytes |
MEDIUMTEXT |
Holds a string | 16,777,215 characters |
MEDIUMBLOB |
For Binary Large Objects (BLOBs) | Max is 16,777,215 bytes |
LONGTEXT |
Holds a string | 4,294,967,295 characters |
LONGBLOB |
For Binary Large Objects (BLOBs) | Max is 4,294,967,295 bytes |
ENUM(val1, val2, val3, ...) |
A string object from list of values. It value is not in the list, blank will be used. | Max is 65535 values in the ENUM list |
SET(val1, val2, val3, ...) |
A string object from list of values. | 0 to 64 values in the SET list |
| Data Type | Description | Size |
|---|---|---|
BIT(size) |
Holds a bit-value |
1 to 64 Default is 1 |
TINYINT(size) |
A very small integer |
Signed range is from -128 to 127. Unsigned range is from 0 to 255. |
BOOL | BOOLEAN |
A boolean value (true or false) | 0 = false, 1 = true |
SMALLINT(size) |
A small integer |
Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. |
MEDIUMINT(size) |
A medium integer |
Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. |
INT(size) | INTEGER(size) |
An integer |
Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. |
BIGINT(size) |
A large interger |
Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. |
FLOAT(p) |
A floating point |
If p is 0 to 24, FLOAT() will be used.If 25 to 52, DOUBLE will be used.
|
DOUBLE(size, d) |
A normal-size floating point |
size is the number of digits from before decimal. d is the number of digits after the decimal point. |
DECIMAL(size, d) | DEC(size, d) |
An exact fixed-point number |
size is the number of digits from before decimal. d is the number of digits after the decimal point. size max is 65 while d is 30. size default is 10 while d is 0. |
| Data Type | Description | Size |
|---|---|---|
DATE |
A date |
'1000-01-01' to '9999-12-31' Default format is YYYY-MM-DD |
DATETIME(fsp) |
A date and time |
'1000-01-01 00:00:00' to '9999-12-31 23:59:59' Default format is YYYY-MM-DD hh:mm:ss |
TIMESTAMP(fsp) |
A timestamp |
'1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC Default format is YYYY-MM-DD hh:mm:ss |
TIME(fsp) |
A time |
'-838:59:59' to '838:59:59' Default format is hh:mm:ss |
YEAR |
A year in four-digit format | 1901 to 2155, and 0000. |
Concatenation combines two or more string values.
MySQL uses the CONCAT function for this.
Syntax for MySQL CONCAT function.
CONCAT(value1, value2, ..., valuen)
List the customers full names.
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Customer
MySQL date, time, and datetime values can be formatted in different ways.
The DATE_FORMAT function formats date and time values.
Syntax for MySQL DATE_FORMAT function.
DATE_FORMAT(date, format)
This table lists the possible values for the format parameter.
| Format | Description |
|---|---|
%a |
Abbreviated weekday name (Sun to Sat) |
%b |
Abbreviated month name (Jan to Dec) |
%c |
Month number in single digit (0 to 11) |
%D |
Day of the month with suffix (1st, 2nd, ..., Nth) |
%d |
Day of the month in two digits (01 to 31) |
%e |
Day of the month in single digit (1 to 31) |
%f |
Microseconds (000000 to 999999) |
%H |
Hour in 24-hour clock and two digits (00 to 23) |
%h | %I |
Hour in 12-hour clock and two digits (00 to 12) |
%i |
Minutes (00 to 59) |
%j |
Day of the year (001 to 366) |
%k |
Hour in 24-hour format and single digit (0 to 23) |
%l |
Hour in 12-hour format and single digit (1 to 12) |
%M |
Full month name (January to December) |
%m |
Month in two digits (00 to 11) |
%p |
AM or PM |
%r |
Time in 12-hour clock with AM / PM (hh:mm:ss AM/PM) |
%S | %s |
Seconds (00 to 59) |
%T |
Time in 24-hour clock format (hh:mm:ss) |
%U |
Week (00 to 53) with Sunday as the first day of the week |
%u |
Week (00 to 53) with Monday as the first day of the week |
%V |
Week (01 to 53) with Sunday as the first day of the week |
%v |
Week (01 to 53) with Monday as the first day of the week |
%W |
Weekday full name (Sunday to Saturday) |
%w |
Day of the week (0 = Sunday to 6 = Saturday) |
%X |
Year for the week where Sunday is the first day of the week |
%x |
Year for the week where Monday is the first day of the week |
%Y |
Year in 4-digit value |
%y |
Year in 2-digit value |
Display November 10, 2021 1:14 PM in different formats.
SELECT
DATE_FORMAT('2021-11-10 13:14', '%b %d, %Y'), /* Nov 10, 2021 */
DATE_FORMAT('2021-11-10 13:14', '%c-%e-%y'), /* 11-10-21 */
DATE_FORMAT('2021-11-10 13:14', '%l:%i %p'), /* 1:14 PM */
DATE_FORMAT('2021-11-10 13:14', '%M %e, %Y %H:%i %W') /* November 10, 2021 13:14 Wednesday */
MySQL can be downloaded and installed on different operating system.
For Linux, it can be downloaded from the MySQL repositories.
For Windows, a MySQL Installer can be downloaded and executed directly.
For MacOS, the DMG file has to be downloaded and extracted.
For other platforms, the correct repository must be selected and configured.
Free and open-source
Easy to integrate with development tools and environments
Compatible with many programming languages such as Node.js, C#, Java, and PHP
Supports failure recovery for transactions
Efficient use of resources in terms of CPU and memory level
Cross-platform support
Easy to install, use, and deploy
Lack of documentation for some MySQL utilities
Sometimes requires that migrations are done manually
MySQL is not community-driven and no major changes have been implemented since Oracle took ownership
