begin;
select * from test;
update test set add=''25-933-733-123444'' where ID=1;#未提交
delete fromorder where orderID=2;#已提交
truncate test;#已提交
select * from test;#已提交
drop table dbshards.aly_test;#已提交
show tables from dbshards like ''test'';#已提交
create table test (orderID int);#已提交
insert into dbshards.aly_test (orderID) values (1),(2),(3),(4);#已提交
select * from dbshards.aly_test;#已提交
rollback;#无效

Home / Basic MySQL Tutorial / Manage Database in MySQL

 

Summary: in this tutorial, you will learn how to manage databases in MySQL. You will learn how to create new databases, remove existing databases, and display all databases in the MySQL database server.

Let’s start creating a new database in MySQL.

Creating Database

Before doing anything else with the data, you need to create a database. A database is a container of data. It stores contacts, vendors, customers or any kind of data that you can think of. In MySQL, a database is a collection of objects that are used to store and manipulate data such as tables, database views, triggers, stored procedures, etc.

To create a database in MySQL, you use the CREATE DATABASE  statement as follows:

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 clause of the statement. The IF NOT EXISTS clause 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:

After executing this statement, MySQL returns a message to notify that the new database has been created successfully or not.

Displaying Databases

The SHOW DATABASES statement displays all databases in the MySQL database server. You can use the SHOW DATABASES 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:

We have three databases in the MySQL database server. The information_schema  and mysql are 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.

You can select the classicmodels  sample database using the USE statement as follows:

From now all operations such as querying data, create new tables or calling stored procedures which you perform, will take effects on the current database i.e., classicmodels .

Removing Databases

Removing database means you delete the database physically. All the data and associated 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:

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. Let’s look at the following queries:

The sequence of three statements is as follows:

1. First, we created a database named temp_database using the CREATE DATABASE statement.
2. Second, we displayed all databases using the SHOW DATABASES statement.
3. Third, we removed the temp_database using the DROP DATABASE statement.

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.

Home / Basic MySQL Tutorial / 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 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 and speed. MyISAM tables are also portable between platforms and operating systems.

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 spaces. At startup, MySQL checks MyISAM tables for corruption and even repairs them in a 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 specifying the storage engine explicitly. 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 optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-forward operations. The size of an InnoDB table can be up to 64TB.

Like MyISAM, the InnoDB tables are portable between different platforms and operating systems. MySQL also checks and repairs InnoDB tables, if necessary, at startup.

MERGE

A MERGE table is a virtual table that combines multiple MyISAM tables that have a 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 when 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 use hash indexes so that they are faster than MyISAM tables. The lifetime of the data of the memory tables depends on the uptime 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 the zlib library as it is read.

The archive tables only allow INSERT and SELECT statements. The ARCHIVE tables do not support indexes, so it is required a full table scanning for reading rows.

CSV

The CSV storage engine stores data in comma-separated values (CSV) 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. In addition, the read operation requires a full table scan.

FEDERATED

The FEDERATED storage engine allows you to manage data from a remote MySQL server without using the cluster or replication technology. The local federated table stores no data. When you query data from a local federated table, the data is pulled 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 Storage Engine Feature Summary

 

In this tutorial, you have learned various storage engines or table types available in MySQL.

Home / Basic MySQL Tutorial / MySQL Data Types

 

Summary: in this tutorial, you will learn about MySQL data types and how to use them effectively in designing database in MySQL.

A 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:

• The kind of values it represents.
• The space that takes up and whether the values is a fixed-length or variable length.
• The values of the data type can be indexed or not.
• How MySQL compares the values of a specific data type.

Download MySQL Data Types Overview

MySQL numeric data types

In MySQL, you can find all SQL standard numeric types including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addition, MySQL also hasBIT data type for storing bit values. Numeric types can be signed or unsigned except for the BIT type.

The following table shows 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

MySQL Boolean data type

MySQL does not have the built-in  BOOLEANor BOOL data type. To represent Boolean values, MySQL uses the smallest integer type which isTINYINT(1). In other words, BOOLEAN and BOOL are synonyms for TINYINT(1).

MySQL String data types

In MySQL, a string can hold anything from plain text to binary data such as images or files. Strings can be compared and searched based on pattern matching by using the LIKE operator, regular expression, and full-text search.

The following table shows the string data types in MySQL:

String Types	Description
CHAR	A fixed-length nonbinary (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

MySQL date and time data types

MySQL provides types for date and time as well as the combination of date and time. In addition, MySQL supports timestamp data type for tracking the changes in a row of a table. If you just want to store the year without date and month, you can use the 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 inCCYY-MM-DD hh:mm:ssformat
TIMESTAMP	A timestamp value in CCYY-MM-DD hh:mm:ss format
YEAR	A year value in CCYY or YY format

MySQL spatial data types

MySQL supports many spatial data types that contain various kinds 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 GEOMETRYvalues
MULTILINESTRING	A collection of LINESTRINGvalues
MULTIPOINT	A collection of POINTvalues
MULTIPOLYGON	A collection of POLYGONvalues

JSON data type

MySQL supported a native JSON data type since version 5.7.8 that allows you to store and manage JSON documents more efficiently. The native JSON data type provides automatic validation of JSON documents and optimal storage format.

In this tutorial, you have learned various MySQL data types that help you determine which data type you should use for columns when you create tables.

Home / Basic MySQL Tutorial / MySQL UNIQUE Constraint

 

Summary: in this tutorial, you will learn about MySQL UNIQUE constraint to enforce the uniqueness of the values in a column or a group of columns.

Introduction to MySQL UNIQUE constraint

Sometimes, you want to enforce the uniqueness value in a column e.g., the phones of the suppliers in the suppliers table must be unique, or the combination of the supplier name and address must not be duplicate.

To enforce this rule, you need to use the UNIQUE constraint.

The UNIQUE constraint is either column constraint or table constraint that defines a rule that constrains values in a column or a group of columns to be unique.

To add the UNIQUE constraint to a column, you use the following syntax:

Or you can define the UNIQUE constraint as the table constraint as follows:

If you insert or update a value that causes a duplicate value in the column_name_1 column, MySQL will issue an error message and reject the change.

In case you want to enforce unique values across columns, you must define the UNIQUE constraint as the table constraint and separate the each column by a comma:

MySQL will use the combination of the values in both column_name_1 and column_name_2 columns to evaluate the uniqueness.

If you want to assign a specific name to a UNIQUE constraint, you use the CONSTRAINT clause as follows:

MySQL UNIQUE constraint example

The following statement creates a new table named suppliers with the two UNIQUE constraints:

The first UNIQUE constraint is applied on the phone column. It means that every supplier must have a distinct phone number. In other words, no two suppliers have the same phone number.

The second UNIQUE constraint has a name uc_name_address that enforces the uniqueness of values in the name and address columns.  It means suppliers can have the same name or address, but cannot have the same name and address.

Let’s insert some rows into the suppliers table to test the UNIQUE constraint.

The following statement inserts a row into to the suppliers table.

 

We try to insert a different supplier but has the phone number that already exists in the supplierstable.

MySQL issued an error:

Let’s change the phone number to a different one and execute the insert statement again.

 

Now we execute the following INSERT statement to insert a row with the values in the name and address columns that already exists.

MySQL issued an error.

Because the UNIQUE constraint uc_name_address was violated.

Managing MySQL UNIQUE constraints

When you add a unique constraint to a table MySQL creates a corresponding BTREE index to the database. The following SHOW INDEX statement displays all indexes created on the suppliers table.

As you see, there are two BTREE indexes corresponding to the two UNIQUE constraints created.

To remove a UNIQUE constraint, you use can use DROP INDEX or ALTER TABLE statement as follows:

 

For example, to remove the uc_name_address constraint on the suppliers table, you the following statement:

Execute the SHOW INDEX statement again to verify if the uc_name_unique constraint has been removed.

What if you want to add a UNIQUE constraint to a table that already exists?

To do this, you use the ALTER TABLE statement as follows:

For example, to add the uc_name_address UNIQUE constraint back to the suppliers table, you use the following statement:

Note that the combination of values in the name and address columns must be unique in order to make the statement execute successfully.

In this tutorial, you have learned how to use the MySQL UNIQUE constraint to enforce the uniqueness of values in a column or a group of columns in a table.