EN: |+| {Music} |+| Hello, welcome by the TiDB Specific Features and Transaction Control |+| training. EN: I'm Daniel Van Eden and I will be your instructor today. EN: |+| First off we will start with the data definition language. EN: |+| The objectives of this training are creating and dropping databases, describing database |+| character sets. EN: Creating, modifying, deleting and flashback tables. EN: |+| Use table and index related DDL. EN: DDL here stands for data definition language as we said before. EN: |+| Describe and set column properties and table options. EN: |+| Creating indexes, primary keys, unique keys and other constraints. EN: |+| Working with cross geo location region placement rules. EN: Using cached tables. EN: Using partition tables. EN: |+| And, last but not least, optimize analytical queries with TiFlash. EN: Let's start off with creating |+| database. EN: Database is usually a unique name and one database system like the TiDB can hold multiple |+| databases. EN: The syntax for creating a database is create database and then the database name. EN: |+| You can also exchange the word database for schema. EN: Both will work and give you the same result. EN: |+| The name schema and database in MySQL context usually referring to the same kind of object. EN: |+| Objects in the database can be named using regular English letters and underscores. EN: The limit is 64 |+| characters. EN: This is not only true for databases but for many other things. EN: There are some characters |+| you probably don't want to use in database names like ASCII 0, ASCII 255, slash, backslash, |+| etc. EN: 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. EN: |+| Because this is something you will be using over and over again. EN: TiDB stores |+| object names in case sensitive manner but compares them in a case insensitive manner. EN: |+| 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. EN: 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. EN: 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. EN: 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. EN: A character set is a collection of symbols and encodings. EN: The default character |+| set in TiDB is utf8mb4 which matches the default character set in MySQL 8 and later. EN: |+| And it's also something we would definitely recommend you to use. EN: Collation is a rule |+| that compares characters in a character set and the sort of order of characters. EN: |+| So, basically this defines two things. EN: 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. EN: |+| With unicode you can use pretty much any kind of character that's defined in unicode. EN: In MySQL |+| there is utf8 in their utf8mb4. EN: We have the same in TiDB. EN: We would definitely recommend you to |+| use utf8mb4 because it allows you to have four byte unicodes. EN: 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. EN: As you can see on the screen there are three |+| different correlations for utf8mb4. EN: This defines how characters are compared with each |+| other. EN: And, this also defines the sorting order of the character. EN: 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. EN: So, for `utf8mb4_bin`, lowercase a and uppercase A |+| are not considered to be the same. EN: But for the case insensitive, collation is the one ending in CI, |+| these are considered to be the same. EN: And, then we have to collate clause. EN: The collate |+| clause allows you to set the collation which is to be used for a specific comparison.