Basic MySQL Tutorial
1
|
CREATE
DATABASE [IF NOT EXISTS] database_name;
|
Let’s examine the CREATE DATABASE statement in greater detail:
- Followed
by the CREATE
DATABASE statement is database
name that you want to create. It is recommended that the database name
should be as meaningful and descriptive as possible.
- The IF NOT EXISTS is an optional element of the statement.
The IF
NOT EXISTS statement prevents you
from an error of creating a new database that already exists in the
database server. You cannot have 2 databases with the same name in a MySQL
database server.
For example, to create classicmodels database, you can execute the CREATE DATABASE statement as follows:
1
|
CREATE
DATABASE classicmodels;
|
After executing the statement, MySQL returns a
message to notify that the new database has been created successfully or not.
Displaying Databases
The SHOW DATABASE statement displays all databases in the MySQL database
server. You can use the SHOW DATABASE statement
to check the database that you’ve created or to see all the databases on the
database server before you create a new database, for example:
1
|
SHOW
DATABASES;
|
We have thee databases in the MySQL database
server. The information_schema and mysqlare the default
databases that are available when we install MySQL, and the classicmodels is the new database that we have created.
Selecting a database to work with
Before working with a particular database, you
must tell MySQL which database you want to work with by using the USE statement.
1
|
USE
database_name;
|
1
|
USE
classicmodels;
|
From now all operations such as querying
data, create
new tables or stored procedures which you perform, will take
effects on the current database.
Removing Databases
Removing database means you delete the
database physically. All the data and related objects inside the database are
permanently deleted and this cannot be undone, therefore it is very important
to execute this query with extra cautions.
To delete a database, you use the DROP DATABASE statement as follows:
1
|
DROP
DATABASE [IF EXISTS] database_name;
|
Followed the DROP DATABASE is the database name that you want to
remove. Similar to the CREATE DATABASE statement,
the IF EXISTS is an optional
part of the statement to prevent you from removing a database that does not
exist in the database server.
If you want to practice with the DROP DATABASE statement, you can create a new
database, make sure that it is created and remove it. Take a look at the
following queries:
1
2
3
|
CREATE
DATABASE IF NOT EXISTS temp_database;
SHOW
DATABASES;
DROP
DATABASE IF EXISTS temp_database;
|
In this tutorial, you’ve learned various
statements to manage databases in MySQL including creating a new database,
removing an existing database, selecting a database to work with and displaying
all databases in a MySQL database server.
Understanding MySQL
Table Types, or Storage Engines
Summary: in this tutorial, you will learn
various MySQL table types, or storage engines. It is essential to understand the features of each table type in MySQL so that you can use them
effectively to maximize the performance of your databases.
MySQL provides various storage engines for its
tables as below:
- MyISAM
- InnoDB
- MERGE
- MEMORY
(HEAP)
- ARCHIVE
- CSV
- FEDERATED
Each storage engine has its own advantages and
disadvantages. It is crucial to understand each storage engine features and
choose the most appropriate one for your tables to maximize the performance of
the database. In the following sections we will discuss about each storage
engine and its features so that you can decide which one to use.
MyISAM
MyISAM extends the former ISAM storage engine.
The MyISAM tables are optimized for compression an speed. MyISAM tables are
also portable between platforms and OSes.
The size of MyISAM table can be up to 256TB,
which is huge. In addition, MyISAM tables can be compressed into read-only
tables to save space. At startup, MySQL checks MyISAM tables for corruption and
even repair them in case of errors. The MyISAM tables are not transaction-safe.
Before MySQL version 5.5, MyISAM is the
default storage engine when you create a table without explicitly specify the
storage engine. From version 5.5, MySQL uses InnoDB as the default storage
engine.
InnoDB
The InnoDB tables fully support ACID-compliant
and transactions. They are also very optimal for performance. InnoDB table
supports foreign keys, commit, rollback, roll-and forward operations. The size
of the InnoDB table can be up to 64TB.
Like MyISAM, the InnoDB tables are portable
between different platforms and OSes. MySQL also checks and repair InnoDB
tables, if necessary, at startup.
MERGE
A MERGE table is a virtual table that combines
multiple MyISAM tables, which has similar structure, into one table. The MERGE
storage engine is also known as the MRG_MyISAM engine. The MERGE table does not
have its own indexes; it uses indexes of the component tables instead.
Using MERGE table, you can speed up
performance in joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations
on the MERGE tables. If you use DROP TABLE statement on
a MERGE table, only MERGE specification is removed. The underlying tables will
not be affected.
Memory
The memory tables are stored in memory and
used hash indexes so that they are faster than MyISAM tables. The lifetime of
the data of the memory tables depends on the up time of the database server.
The memory storage engine is formerly known as HEAP.
Archive
The archive storage engine allows you to store
a large number of records, which for archiving purpose, into a compressed
format to save disk space. The archive storage engine compresses a record when
it is inserted and decompress it using zlib library as
it is read.
The archive tables only allow INSERT and
SELECT commands. The archive tables do not support indexes, so reading records
requires a full table scanning.
CSV
The CSV storage engine stores data in
comma-separated values file format. A CSV table brings a convenient way to
migrate data into non-SQL applications such as spreadsheet software.
CSV table does not support NULL data type and
read operation requires a full table scan.
FEDERATED
The FEDERATED storage engine allows you to
manage data from a remote MySQL server without using cluster or replication
technology. The local federated table stores no data. When you query data from
a local federated table, the data is pull automatically from the remote
federated tables.
Choosing MySQL Table Types
You can download the following checklist to
choose the most appropriate storage engine, or table type, based on various
criteria.
MySQL Data Types
Summary: in this tutorial, you will learn about MySQL data types and how to use them effectively in the MySQL
database design.
Database table contains multiple columns with specific
data types such as numeric or string. MySQL provides more data types other than
just numeric or string. Each data type in MySQL can be determined by the following characteristics:
- Kind
of values it can represent.
- The
space that takes up and whether the values are fixed-length or
variable-length.
- Does
the values of the data type can be indexed.
- How
MySQL compares the value of a specific data type.
Numeric Data Types
You can find all SQL standard numeric types in
MySQL including exact number data type and approximate numeric data types
including integer, fixed-point and floating point. In addition, MySQL also
supports BIT data type for storing bit field values. Numeric types can be
signed or unsigned except the BIT type. The following table shows you the
summary of numeric types in MySQL:
Numeric
Types
|
Description
|
TINYINT
|
A very small integer
|
SMALLINT
|
A small integer
|
MEDIUMINT
|
A medium-sized integer
|
INT
|
A standard integer
|
BIGINT
|
A large integer
|
DECIMAL
|
A fixed-point number
|
FLOAT
|
A single-precision floating-point
number
|
DOUBLE
|
A double-precision floating-point
number
|
BIT
|
A bit field
|
String Data Types
In MySQL, string can hold anything from plain
text to binary data such as images and files. String can be compared and
searched based on pattern matching by using the LIKE operator or regular expression. The following table shows you the
string data types in MySQL:
String
Types
|
Description
|
CHAR
|
A fixed-length non-binary
(character) string
|
VARCHAR
|
A variable-length non-binary
string
|
BINARY
|
A fixed-length binary string
|
VARBINARY
|
A variable-length binary string
|
TINYBLOB
|
A very small BLOB (binary large
object)
|
BLOB
|
A small BLOB
|
MEDIUMBLOB
|
A medium-sized BLOB
|
LONGBLOB
|
A large BLOB
|
TINYTEXT
|
A very small non-binary string
|
TEXT
|
A small non-binary string
|
MEDIUMTEXT
|
A medium-sized non-binary string
|
LONGTEXT
|
A large non-binary string
|
ENUM
|
An enumeration; each column value
may be assigned one enumeration member
|
SET
|
A set; each column value may be
assigned zero or more set members
|
Date and Time Data Types
MySQL provides types for date and time as well
as a combination of date and time. In addition, MySQL also provides timestamp data
type for tracking the changes of a row in a table. If you just want to store
the year without date and month, you can use YEAR data type. The following
table illustrates the MySQL date and time data types:
Date
and Time Types
|
Description
|
DATE
|
A date value in ‘CCYY-MM-DD’
format
|
TIME
|
A time value in ‘hh:mm:ss’ format
|
DATETIME
|
A date and time value in
‘CCYY-MM-DD hh:mm:ss’ format
|
TIMESTAMP
|
A timestamp value in ‘CCYY-MM-DD
hh:mm:ss’ format
|
YEAR
|
A year value in CCYY or YY format
|
Spatial Data Types
MySQL supports many spatial data types that
contain various kind of geometrical and geographical values as shown in
the following table:
Spatial
Data Types
|
Description
|
GEOMETRY
|
A spatial value of any type
|
POINT
|
A point (a pair of X Y
coordinates)
|
LINESTRING
|
A curve (one or more POINT values)
|
POLYGON
|
A polygon
|
GEOMETRYCOLLECTION
|
A collection of GEOMETRY values
|
MULTILINESTRING
|
A collection of LINESTRING values
|
MULTIPOINT
|
A collection of POINT values
|
MULTIPOLYGON
|
A collection of POLYGON values
|
Creating Tables Using
MySQL CREATE TABLE Statement
Summary: in this tutorial, we will show you how to create new tables in a particular database
usingMySQL CREATE TABLE statement.
MySQL CREATE TABLE syntax
In order to create a new table within a
database, you use the MySQL CREATE TABLE statement. TheCREATE TABLE statement is one of the most complex statement in MySQL.
The following illustrates the syntax of
the CREATE TABLE statement
in the simple form:
1
2
3
|
CREATE
TABLE [IF NOT EXISTS] table_name(
column_list
) engine=table_type
|
Let’s examine the syntax in greater detail:
- First,
you specify the name of table that you want to create after the CREATE TABLE keywords. The table name must be unique within a database. The IF NOT EXISTS is an optional part of the statement that allows
you to check if the table you are creating already exists in the
database. If this is the case, MySQL will ignore the whole statement and
it will not create any new table. It is highly recommended that you to
use IF
NOT EXISTS in every CREATE TABLE statement for preventing from an error of
creating a new table that already exists.
- Second, you specify a list of columns for the
table in the column_list section. Columns are separated by a comma ( ,).
We will show you how to define columns in more detail in the next
section.
- Third,
you need to specify the storage engine for the
table in the engine clause. You can use any storage engine such as
InnoDB, MyISAM, HEAP, EXAMPLE, CSV, ARCHIVE, MERGE FEDERATED or
NDBCLUSTER. If you don’t declare the storage engine explicitly, MySQL will
use InnoDB by default.
InnoDB became the default storage engine since
MySQL version 5.5. The InnoDB table type brings many benefits of relational
database management system such as ACID transaction, referential integrity and
crash recovery. In the previous versions, MySQL used MyISAM as the
default storage engine.
To define a column for the table in
the CREATE TABLE statement,
you use the following syntax:
1
2
|
column_name
data_type[size] [NOT NULL|NULL] [DEFAULT value]
[AUTO_INCREMENT]
|
The most important components of the syntax
above are:
- The column_name specifies the name of the column. Each column always
associates with a specific data type and
the size e.g., VARCHAR(255).
- The NOT NULL or NULL indicates that the column accepts NULL value or not.
- The DEFAULT value is used to specify the default value of the
column.
- The AUTO_INCREMENT indicates that the value of column is increased
by one whenever a new row is inserted into the table. Each table has
one and only one AUTO_INCREMENT column.
If you want to set particular columns
of the table as the primary key, you use the following syntax:
1
|
PRIMARY
KEY (col1,col2,...)
|
Example of MySQL CREATE TABLE statement
Let’s practice with an example of creating a
new table named tasks in
our sample database as follows:
You can use the CREATE TABLE statement to create the tasks table as follows:
1
2
3
4
5
6
7
8
|
CREATE
TABLE IF NOT EXISTS tasks (
task_id int(11) NOT NULL AUTO_INCREMENT,
subject varchar(45) DEFAULT NULL,
start_date DATE DEFAULT NULL,
end_date DATE DEFAULT NULL,
description varchar(200) DEFAULT NULL,
PRIMARY KEY (task_id)
)
ENGINE=InnoDB
|
In this tutorial, you have learned how to use
MySQL CREATE TABLE to create a new tables within a database.
MySQL Sequence
Summary: in this tutorial, we will show you how to
use MySQL sequence to automatically generate unique numbers for ID columns of
tables.
MySQL create sequence
In MySQL, a sequence is a list of integers
generated in the ascending order i.e., 1,2,3… Many applications need sequences
to generate unique numbers mainly for identification e.g., customer ID in CRM,
employee number in HR, equipment number in services management system, etc.
To create a sequence in MySQL automatically,
you set the AUTO_INCREMENT attribute to a column, which typically is primary key column.
The following are rules that you must follow when you useAUTO_INCREMENT attribute:
- Each
table has only one AUTO_INCREMENT column whose data type is typically integer or float
which is very rare.
- The
AUTO_INCREMENT column must be indexed, which means it can be
either PRIMARY
KEY or UNIQUE index.
- The AUTO_INCREMENT column must have NOT NULL constraint. When you setAUTO_INCREMENT attribute to a column, MySQL will make it NOT NULL for you in case you don’t define it explicitly.
MySQL create sequence example
The following example creates employees table whose emp_no column is AUTO_INCREMENTcolumn:
1
2
3
4
5
|
CREATE
TABLE employees(
emp_no
INT(4) AUTO_INCREMENT PRIMARY KEY,
first_name
VARCHAR(50),
last_name VARCHAR(50)
)ENGINE
= INNODB;
|
How MySQL sequence works
The AUTO_INCREMENT column has the following attributes:
- The
starting value of an AUTO_INCREMENT column is 1 and it is increased by 1 when you insertNULL value into the column or when you omit its value in
the INSERT statement.
- To
obtain the last generated sequence number, you use the LAST_INSERT_ID() function. You often use the last insert ID for
the subsequent statements e.g., insert data into child tables. The last
generated sequence is unique across sessions.In other words, if another
connection generates a sequence number, from your connection you can
obtain it by using the LAST_INSERT_ID()function.
For more details on LAST_INSERT_ID() function, check it out
the MySQL LAST_INSERT_ID() function tutorial.
- If
you insert
a new row into a table and specify a value for the
sequence column, MySQL will insert the sequence number if the sequence
number does not exist in the column or issue an error if it already
exists. If you insert a new value that is greater than the next sequence
number, MySQL will use the new value as the starting sequence number and
generate a unique sequence number greater than the current one for the
next use. This creates gaps in the sequence.
- If
you use UPDATE statement
to update an AUTO_INCREMENT column to a value that already exists, MySQL will issue
a duplicate-key error if the column has a unique index. If you update anAUTO_INCREMENT column to a value that is larger than the existing
values in the column, MySQL will use the next number of the last insert
sequence number for the next row e.g., if the last insert sequence number
is 3, you update it to 10, the sequence number for the new row is 4. See
the example in the below section.
- If
you use DELETE statement
to delete the last insert row, MySQL may or may not reuse the deleted
sequence number depending on the storage engine of the table. A MyISAM table does not reuse the deleted sequence
numbers if you delete a row e.g., the last insert id in the
table is 10, if you remove it, MySQL still generates the next
sequence number which is 11 for the new row. Similar to MyISAM tables,
InnoDB tables do use reuse sequence number when rows are deleted.
Once you set AUTO_INCREMENT attribute for a column, you can reset auto increment value in various ways e.g.,
by using ALTER
TABLE statement.
Let’s practice with the MySQL sequence.
First, insert two new employees into the employees table:
1
2
3
|
INSERT
INTO employees(first_name,last_name)
VALUES('John','Doe'),
('Mary','Jane');
|
Second, select data from the employees table:
1
|
SELECT
* FROM employees;
|
Third, delete the second employee whose emp_no is 2:
1
2
|
DELETE
FROM employees
WHERE
emp_no = 2;
|
Fourth, insert a new employee:
1
2
|
INSERT
INTO employees(first_name,last_name)
VALUES('Jack','Lee');
|
Because the storage engine of the employees table is InnoDB, it does not reuse the deleted
sequence number. The new row has emp_no 3.
Fifth, update an existing employee with emp_no 3 to 1:
1
2
3
4
|
UPDATE
employees
SET
first_name = 'Joe',
emp_no
= 1
WHERE
emp_no = 3;
|
MySQL issued an error of duplicate entry for
the primary key. Let’s fix it:
1
2
3
4
|
UPDATE
employees
SET
first_name = 'Joe',
emp_no
= 10
WHERE
emp_no = 3;
|
Sixth, insert a new employee after updating
the sequence number to 10:
1
2
|
INSERT
INTO employees(first_name,last_name)
VALUES('Wang','Lee');
|
The next sequence number of the last insert is
4, therefore MySQL use 4 for the new row instead of 11.
In this tutorial, you have learned how to use
MySQL sequence to generate unique numbers for a primary key column by assigning
the column AUTO_INCREMENT attribute.
MySQL Primary Key
Summary: in this tutorial, you will learn how to
use MySQL primary key constraint to create a primary key for the table.
Introduction to MySQL primary key
A primary key is a column or a set of columns
that uniquely identifies each row in the table. The following are the
rules that you must follow when you define a primary key for a table:- A
primary key must contain unique values. If the primary key consists of
multiple columns, the combination of values in these columns must be
unique.
- A
primary key column cannot contain NULL values.
It means that you have to declare the primary key column with NOT NULL attribute. If you don’t, MySQL will force the
primary key column as NOT NULL implicitly.
- A
table has only one primary key.
Because MySQL works faster with integers, the
primary key column’s type should be an integer type e.g.,INT or BIGINT. You can choose a smaller integer type such
as TINYINT, SMALLINT, etc., however you should make sure that the
range of values of the integer type for the primary key is sufficient for
storing all possible rows that the table may have.
A primary key column often has AUTO_INCREMENT attribute that generates a unique sequence for
the key automatically. The the primary key of the next row is greater than the
previous one.
MySQL creates an index named PRIMARY with PRIMARY type for the primary key in a table.
Defining MySQL PRIMARY KEY Constraints
MySQL allows you to to create a primary key by
defining a primary key constraint when you create or modify the table.
Defining MySQL PRIMARY KEY constraints using
CREATE TABLE statement
MySQL allows you to create the primary key
when you create the table by using the CREATE
TABLEstatement. To create a PRIMARY KEY constraint for the table, you specify the PRIMARY KEY in the primary key column’s definition.
The following example creates users table whose primary key is user_id column:
1
2
3
4
5
6
|
CREATE
TABLE users(
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(40),
password VARCHAR(255),
email VARCHAR(255)
);
|