{Music} Hello, welcome by the TiDB Specific Features and Transaction Control training.

I'm Daniel Van Eden and I will be your instructor today.

First off we will start with the data definition language.

The objectives of this training are creating and dropping databases, describing database character sets.

Creating, modifying, deleting and flashback tables.

Use table and index related DDL.

DDL here stands for data definition language as we said before.

Describe and set column properties and table options.

Creating indexes, primary keys, unique keys and other constraints.

Working with cross geo location region placement rules.

Using cached tables.

Using partition tables.

And, last but not least, optimize analytical queries with TiFlash.

Let's start off with creating database.

Database is usually a unique name and one database system like the TiDB can hold multiple databases.

The syntax for creating a database is create database and then the database name.

You can also exchange the word database for schema.

Both will work and give you the same result.

The name schema and database in MySQL context usually referring to the same kind of object.

Objects in the database can be named using regular English letters and underscores.

The limit is 64 characters.

This is not only true for databases but for many other things.

There are some characters you probably don't want to use in database names like ASCII 0, ASCII 255, slash, backslash, etc.

Well some of the other characters might be possible it's usually smarter to give the database a short single name without spacesand without any special characters.

Because this is something you will be using over and over again.

TiDB stores object names in case sensitive manner but compares them in a case insensitive manner.

Dropping a database is simple just to drop database and then the database name here you can also use drop schema which will also work.

There are some optional keywords that are available the `IF EXISTS`, so you can say `DROP DATABASE IF EXISTS` and then the name of the database.

This changes how the statement behaves case of failures so if you're trying to drop a database it doesn't exist, normally you will get an error but with the `IF EXISTS` you won't get an error.

This is usually used to create databases or drop databases in a script or some other place that just wants to make sure that database exists not create the database again if it already exists or have to deal with errors.

A character set is a collection of symbols and encodings.

The default character set in TiDB is utf8mb4 which matches the default character set in MySQL 8 and later.

And it's also something we would definitely recommend you to use.

Collation is a rule that compares characters in a character set and the sort of order of characters.

So, basically this defines two things.

The first thing is the the set of characters you can use if you restrict things to ASCII, for example you cannot use Chinese characters.

With unicode you can use pretty much any kind of character that's defined in unicode.

In MySQL there is utf8 in their utf8mb4.

We have the same in TiDB.

We would definitely recommend you to use utf8mb4 because it allows you to have four byte unicodes.

The utf8, that's a an old Legacy encoding that only allows you to store up to three bytes of unicodes per character, which restricts you in what you can do with the data.

As you can see on the screen there are three different correlations for utf8mb4.

This defines how characters are compared with each other.

And, this also defines the sorting order of the character.

So, for example, if you want to know if a lowercase a an uppercase a are the same character the database will use the rules defined by the collation to do this.

So, for `utf8mb4_bin`, lowercase a and uppercase A are not considered to be the same.

But for the case insensitive, collation is the one ending in CI, these are considered to be the same.

And, then we have to collate clause.

The collate clause allows you to set the collation which is to be used for a specific comparison.