MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+

MySQL Introduction

From WikiOD

Remarks[edit | edit source]

MySQL Logo

MySQL is an open-source Relational Database Management System (RDBMS) that is developed and supported by Oracle Corporation.

MySQL is supported on a large number of platforms, including Linux variants, OS X, and Windows. It also has APIs for a large number of languages, including C, C++, Java, Lua, .Net, Perl, PHP, Python, and Ruby.

MariaDB is a fork of MySQL with a slightly different feature set. It is entirely compatible with MySQL for most applications.

Versions[edit | edit source]

Version Release Date
1.0 1995-05-23
3.19 1996-12-01
3.20 1997-01-01
3.21 1998-10-01
3.22 1999-10-01
3.23 2001-01-22
4.0 2003-03-01
4.1 2004-10-01
5.0 2005-10-01
5.1 2008-11-27
5.5 2010-11-01
5.6 2013-02-01
5.7 2015-10-01

Getting Started[edit | edit source]

Creating a database in MySQL


Return value:

Query OK, 1 row affected (0.05 sec)

Using the created database mydb

USE mydb;

Return value:

Database Changed

Creating a table in MySQL

  id              int unsigned NOT NULL auto_increment,
  username        varchar(100) NOT NULL,
  email           varchar(100) NOT NULL,
  PRIMARY KEY     (id)

CREATE TABLE mytable will create a new table called mytable.

id int unsigned NOT NULL auto_increment creates the id column, this type of field will assign a unique numeric ID to each record in the table (meaning that no two rows can have the same id in this case), MySQL will automatically assign a new, unique value to the record's id field (starting with 1).

Return value:

Query OK, 0 rows affected (0.10 sec)

Inserting a row into a MySQL table

INSERT INTO mytable ( username, email )
VALUES ( "myuser", "" );

Example return value:

Query OK, 1 row affected (0.06 sec)

The varchar a.k.a strings can be also be inserted using single quotes:

INSERT INTO mytable ( username, email )
VALUES ( 'username', '' );

Updating a row into a MySQL table

UPDATE mytable SET username="myuser" WHERE id=8

Example return value:

Query OK, 1 row affected (0.06 sec)

The int value can be inserted in a query without quotes. Strings and Dates must be enclosed in single quote ' or double quotes ".

Deleting a row into a MySQL table

DELETE FROM mytable WHERE id=8

Example return value:

Query OK, 1 row affected (0.06 sec)

This will delete the row having id is 8.

Selecting rows based on conditions in MySQL

SELECT * FROM mytable WHERE username = "myuser";

Return value:

| id | username | email               |
|  1 | myuser   |  |

1 row in set (0.00 sec)

Show list of existing databases

SHOW databases;

Return value:

| Databases         |
| information_schema|
| mydb              |

2 rows in set (0.00 sec)

You can think of "information_schema" as a "master database" that provides access to database metadata.

Show tables in an existing database

SHOW tables;

Return value:

| Tables_in_mydb |
| mytable        |

1 row in set (0.00 sec)

Show all the fields of a table

DESCRIBE databaseName.tableName;

or, if already using a database:

DESCRIBE tableName;

Return value:

| Field     | Type           | Null   | Key     | Default           | Extra |
| fieldname | fieldvaluetype | NO/YES | keytype | defaultfieldvalue |       |

Extra may contain auto_increment for example.

Key refers to the type of key that may affect the field. Primary (PRI), Unique (UNI) ...

n row in set (0.00 sec)

Where n is the number of fields in the table.

Creating user

First, you need to create a user and then give the user permissions on certain databases/tables. While creating the user, you also need to specify where this user can connect from.

CREATE USER 'user'@'localhost' IDENTIFIED BY 'some_password';

Will create a user that can only connect on the local machine where the database is hosted.

CREATE USER 'user'@'%' IDENTIFIED BY 'some_password';

Will create a user that can connect from anywhere (except the local machine).

Example return value:

Query OK, 0 rows affected (0.00 sec)

Adding privileges

Grant common, basic privileges to the user for all tables of the specified database:

GRANT SELECT, INSERT, UPDATE ON databaseName.* TO 'userName'@'localhost';

Grant all privileges to the user for all tables on all databases (attention with this):

GRANT ALL ON *.* TO 'userName'@'localhost' WITH GRANT OPTION;

As demonstrated above, *.* targets all databases and tables, databaseName.* targets all tables of the specific database. It is also possible to specify database and table like so databaseName.tableName.

WITH GRANT OPTION should be left out if the user need not be able to grant other users privileges.

Privileges can be either


or a combination of the following, each separated by a comma (non-exhaustive list).



Generally, you should try to avoid using column or table names containing spaces or using reserved words in SQL. For example, it's best to avoid names like table or first name.

If you must use such names, put them between back-tick `` delimiters. For example:

   `first name` VARCHAR(30)

A query containing the back-tick delimiters on this table might be:

 SELECT `first name` FROM `table` WHERE `first name` LIKE 'a%';

Information Schema Examples[edit | edit source]

Processlist[edit | edit source]

This will show all active & sleeping queries in that order then by how long.


This is a bit more detail on time-frames as it is in seconds by default

TIME as time_seconds, 
ROUND(TIME / 60, 2) as time_minutes, 
ROUND(TIME / 60 / 60, 2) as time_hours, 

Stored Procedure Searching[edit | edit source]

Easily search thru all Stored Procedures for words and wildcards.