MySql Database

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.

SQL
SQL MySQL

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.

Using MySQL

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.

How mysql works

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.


Core Features of MySQL

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

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.


MySQL Optimize Table

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.

When should you optimize tables?

Optimizing large tables takes a long of time and requires locking which affects the transactional system. It is recommended that you follow these steps:

  • For each table, drop the indexes, optimize, and then add the indexes back on.
  • Only optimize write-intensive tables with many updates and deletes.
  • Don't perform optimizations during peak hours on a production system.

Syntax

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

Example

Optimize the Customer, Product, and Supplier tables.

OPTIMIZE TABLE Customer, Product, Supplier

MySQL Group By

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

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.

Example

List the number of customers from each country.

SELECT Country, COUNT(Id)
  FROM Customer
 GROUP BY Country

MySQL Data Types

MySQL supports a variety of string, numeric, and datetime data types.

String 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

Numeric Data Types

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.

DateTime Data Types

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.

MySQL Concat

Concatenation combines two or more string values.

MySQL uses the CONCAT function for this.

Syntax

Syntax for MySQL CONCAT function.

CONCAT(value1, value2, ..., valuen)

Example

List the customers full names.

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
  FROM Customer

MySQL Format Date

MySQL date, time, and datetime values can be formatted in different ways.

The DATE_FORMAT function formats date and time values.

Syntax

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

Example

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 Download

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.


Advantages of MySQL

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


Disdvantages of MySQL

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


You may also like



Guides


vsn 3.1