1 00:00:00,000 --> 00:00:07,840 [Music] 2 00:00:09,720 --> 00:00:13,320 Hello, welcome by the TiDB Specific Features and Transaction Control 3 00:00:13,320 --> 00:00:17,340 training. I'm Daniel Van Eden and I will be your instructor today. 4 00:00:19,500 --> 00:00:22,320 First off we will start with the data definition language. 5 00:00:24,420 --> 00:00:31,980 The objectives of this training are creating and dropping databases, describing database 6 00:00:31,980 --> 00:00:36,900 character sets. Creating, modifying, deleting and flashback tables. 7 00:00:38,220 --> 00:00:45,300 Use table and index related DDL. DDL here stands for data definition language as we said before. 8 00:00:46,440 --> 00:00:49,860 Describe and set column properties and table options. 9 00:00:51,120 --> 00:00:55,800 Creating indexes, primary keys, unique keys and other constraints. 10 00:00:56,700 --> 00:01:06,120 Working with cross geo location region placement rules. Using cached tables. Using partition tables. 11 00:01:06,660 --> 00:01:13,920 And, last but not least, optimize analytical queries with TiFlash. Let's start off with creating 12 00:01:13,920 --> 00:01:24,660 database. Database is usually a unique name and one database system like the TiDB can hold multiple 13 00:01:24,660 --> 00:01:32,160 databases. The syntax for creating a database is create database and then the database name. 14 00:01:33,180 --> 00:01:39,600 You can also exchange the word database for schema. Both will work and give you the same result. 15 00:01:40,560 --> 00:01:48,180 The name schema and database in MySQL context usually referring to the same kind of object. 16 00:01:48,720 --> 00:01:57,960 Objects in the database can be named using regular English letters and underscores. The limit is 64 17 00:01:57,960 --> 00:02:05,160 characters. This is not only true for databases but for many other things. There are some characters 18 00:02:05,160 --> 00:02:13,260 you probably don't want to use in database names like ASCII 0, ASCII 255, slash, backslash, 19 00:02:15,000 --> 00:02:22,380 etc. Well some of the other characters might be possible it's usually smarter to give the database 20 00:02:22,380 --> 00:02:28,500 a short single name without spaces and without any special characters. 21 00:02:29,280 --> 00:02:33,420 Because this is something you will be using over and over again. TiDB stores 22 00:02:33,420 --> 00:02:39,540 object names in case sensitive manner but compares them in a case insensitive manner. 23 00:02:41,040 --> 00:02:47,580 Dropping a database is simple just to drop database and then the database name here you can 24 00:02:47,580 --> 00:02:54,240 also use drop schema which will also work. There are some optional keywords that are available the 25 00:02:54,240 --> 00:03:02,340 `IF EXISTS`, so you can say `DROP DATABASE IF EXISTS` and then the name of the database. This changes 26 00:03:02,340 --> 00:03:08,520 how the statement behaves case of failures so if you're trying to drop a database it doesn't 27 00:03:08,520 --> 00:03:15,600 exist, normally you will get an error but with the `IF EXISTS` you won't get an error. This is usually 28 00:03:15,600 --> 00:03:23,640 used to create databases or drop databases in a script or some other place that just wants to make 29 00:03:23,640 --> 00:03:31,620 sure that database exists not create the database again if it already exists or have to deal with 30 00:03:32,280 --> 00:03:39,720 errors. A character set is a collection of symbols and encodings. The default character 31 00:03:39,720 --> 00:03:46,380 set in TiDB is utf8mb4 which matches the default character set in MySQL 8 and later. 32 00:03:47,160 --> 00:03:53,220 And it's also something we would definitely recommend you to use. Collation is a rule 33 00:03:53,220 --> 00:03:59,040 that compares characters in a character set and the sort of order of characters. 34 00:03:59,880 --> 00:04:05,700 So, basically this defines two things. The first thing is the the set of characters you 35 00:04:05,700 --> 00:04:12,120 can use if you restrict things to ASCII, for example you cannot use Chinese characters. 36 00:04:12,840 --> 00:04:21,840 With unicode you can use pretty much any kind of character that's defined in unicode. In MySQL 37 00:04:21,840 --> 00:04:29,220 there is utf8 in their utf8mb4. We have the same in TiDB. We would definitely recommend you to 38 00:04:29,220 --> 00:04:36,600 use utf8mb4 because it allows you to have four byte unicodes. The utf8, that's 39 00:04:36,600 --> 00:04:44,400 a an old Legacy encoding that only allows you to store up to three bytes of unicodes per character, 40 00:04:45,360 --> 00:04:53,580 which restricts you in what you can do with the data. As you can see on the screen there are three 41 00:04:53,580 --> 00:05:00,600 different correlations for utf8mb4. This defines how characters are compared with each 42 00:05:00,600 --> 00:05:07,380 other. And, this also defines the sorting order of the character. So, for example, if you want to 43 00:05:07,380 --> 00:05:16,260 know if a lowercase a an uppercase a are the same character the database will use the rules defined 44 00:05:16,260 --> 00:05:24,360 by the collation to do this. So, for `utf8mb4_bin`, lowercase a and uppercase A 45 00:05:25,080 --> 00:05:32,760 are not considered to be the same. But for the case insensitive, collation is the one ending in CI, 46 00:05:34,140 --> 00:05:39,960 these are considered to be the same. And, then we have to collate clause. The collate 47 00:05:39,960 --> 00:05:48,000 clause allows you to set the collation which is to be used for a specific comparison.