How MySQL manages the creation of CREATE tables and indexes 03/26 Update SLTechnology News&Howtos

How MySQL manages the creation of CREATE tables and indexes

2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you how MySQL manages the creation of CREATE tables and indexes. I hope you will get something after reading this article. Let's discuss it together.

SQL statement:

Database

Table

Indexes

View

DML statement

Single field:

PRIMARY KEY primary key

UNIQUE KEY unique key

Single or multiple fields:

PRIMARY KEY (col,...)

UNIQUE KEY (col,...)

INDEX (col,...)

Data type:

Data_type:

BIT [(length)] bit

| | TINYINT [(length)] [UNSIGNED] [ZEROFILL] very small integer (1 byte) |

| | SMALLINT [(length)] [UNSIGNED] [ZEROFILL] small integer (2 bytes) |

| | MEDIUMINT [(length)] [UNSIGNED] [ZEROFILL] Integer (3 bytes) |

| | INT [(length)] [UNSIGNED] [ZEROFILL] Integer (4 bytes) |

| | INTEGER [(length)] [UNSIGNED] [ZEROFILL] Integer (4 bytes) is equivalent to INT |

| | BIGINT [(length)] [UNSIGNED] [ZEROFILL] large integer (8 bytes) |

| | REAL [(length,decimals)] [UNSIGNED] [ZEROFILL] Real number |

| | DOUBLE [(length,decimals)] [UNSIGNED] [ZEROFILL] even numbers |

| | FLOAT [(length,decimals)] [UNSIGNED] [ZEROFILL] floating point |

| | DECIMAL [(length [, decimals])] [UNSIGNED] [ZEROFILL] decimal point |

| | NUMERIC [(length [, decimals])] [UNSIGNED] [ZEROFILL] numerical type |

| | DATE date type |

| | TIME time type |

| | TIMESTAMP time zone |

| | DATETIME date and time type |

| | YEAR year |

| | CHAR [(length)] fixed-length character type |

VARCHAR (length) variable length character type

[CHARACTER SET charset_name] [COLLATE collation_name]

| | BINARY [(length)] binary number |

| | VARBINARY (length) variable length binary number |

| | very small logarithm of TINYBLOB |

| | BLOB large logarithm |

| | large logarithm in MEDIUMBLOB |

| | large logarithm of LONGBLOB length |

| | TINYTEXT [BINARY] very small text string |

[CHARACTER SET charset_name] [COLLATE collation_name]

| | TEXT [BINARY] text string |

[CHARACTER SET charset_name] [COLLATE collation_name]

| | text string in MEDIUMTEXT [BINARY] |

[CHARACTER SET charset_name] [COLLATE collation_name]

| | LONGTEXT [BINARY] long text string |

[CHARACTER SET charset_name] [COLLATE collation_name]

| | ENUM (value1,value2,value3,...) | Enumerated type

[CHARACTER SET charset_name] [COLLATE collation_name]

| | SET (value1,value2,value3,...) | Collective type

[CHARACTER SET charset_name] [COLLATE collation_name]

| | Type of spatial_type space |

1. Create a database:

CREATE DATABASE | SCHEMA [IF NOT EXISTS] db_name [CHARACTER SET=] [COLLATE]

To create a database, you can set character sets and sort rules.

Mysql > SHOW CHARACTER SET; # View character set

+-- +

+-- +

| | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| | tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| | cp1250 | Windows Central European | cp1250_general_ci | 1 |

| | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| | cp852 | DOS Central European | cp852_general_ci | 1 |

| | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

+-- +

39 rows in set (0.00 sec)

Mysql > SHOW COLLATION; # View collation

+-+ +

+-+ +

| | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |

| | big5_bin | big5 | 84 | | Yes | 1 | |

| | cp1250_polish_ci | cp1250 | 99 | | Yes | 1 | |

| | gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |

| | gbk_bin | gbk | 87 | | Yes | 1 | |

| | latin5_turkish_ci | latin5 | 30 | Yes | Yes | 1 |

+-+ +

197 rows in set (0.00 sec)

Mysql > CREATE DATABASE IF NOT EXISTS students CHARACTER SET 'gbk' COLLATE' gbk_chinese_ci'

# create a students database with a character set of gbk and a collation of gbk_chinese_ci

Query OK, 1 row affected (0.01sec)

Mysql >\ Q

Bye

[root@lamp ~] # ls / mydata/data # check whether the students is created successfully

Ib_logfile1 mysql-bin.000001 mysql-bin.000006 mysql-bin.000011 students

Lamp.err mysql-bin.000002 mysql-bin.000007 mysql-bin.000012 test

[root@lamp ~] # file / mydata/data/students/db.opt # View db.opt file types in students database

/ mydata/data/students/db.opt: ASCII text

2. Modify the database:

ALTER {DATABASE | SCHEMA} [db_name] alter_specification. # modify database attributes, such as character set or collation, alter_specification CHARACTER SET = charset_name COLLATE = collation_name

Alter_specification contains:

[DEFAULT] CHARACTER SET [=] charset_name

| | [DEFAULT] COLLATE [=] collation_name |

ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME # upgrade database data directory

3. Delete the database:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name # delete the database

4. Create tables:

1. Define an empty table directly; col_name field name col_defination field definition

CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination,)

Col_defination field definition contains: data_type field type

Data_type [NOT NULL | NULL] [DEFAULT default_value]

[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

[COMMENT 'string']

[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]

[STORAGE {DISK | MEMORY | DEFAULT}]

[reference_definition]

Usage:CREATE TABLE tb1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR (20) NOT NULL,Age TINYINT NOT NULL); # create a table tb1 that contains three fields: id field is unsigned (UNSIGNED), non-empty (NOT NULL), auto-increment (AUTO_INCREMENT), integer for primary key (PRIMARY KEY). Name field is a fixed length 20 (CHAR (20)), non-empty character. The Age field is a very small integer that is not empty.

Or CREATE TABLE tb2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT,Name CHAR (20) NOT NULL,Age TINYINT NOT NULL,PRIMARY KEY (id), Unique KEY (Name), INDEX (age)); Unique KEY unique key, INDEX index

two。 Query data from other tables and create new tables from them

CREATE TABLE testcourses SELECT * FROM courses WHERE CID DESC courses

+-+ +

+-+ +

| | CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

2 rows in set (0.00 sec)

5. Modify the table definition: ALTER TABLE

Add, delete, modify fields, add, delete, modify indexes, change table names, modify table properties.

Mysql > ALTER TABLE test ADD INDEX (Couse); # add Couse field as index to test table

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > SHOW INDEXES FROM test; # View the index of the test table

+- -+

+- -+

| | test | 0 | PRIMARY | 1 | CID | A | 0 | NULL | NULL | | BTREE |

+- -+

2 rows in set (0.00 sec)

Mysql > DESC test; # View table structure

+-+ +

+-+ +

| | CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

2 rows in set (0.00 sec)

Mysql > ALTER TABLE test CHANGE Couse Course VARCHAR (50) NOT NULL; # modify the Couse field name of the test table to Course and define it as a variable length of 50 characters, not empty

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > DESC test; view table structure

+-+ +

+-+ +

| | CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

2 rows in set (0.00 sec)

Mysql > DROP TABLE testcourses; # Delete testcourses table

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW TABLES; view all tables

+-+

| | Tables_in_students |

+-+

| | courses |

| | test |

+-+

2 rows in set (0.00 sec)

Mysql > ALTER TABLE test RENAME TO testcourses; # modify the name of test table to testcourses

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW TABLES; view all tables

+-+

| | Tables_in_students |

+-+

| | courses |

| | testcourses |

+-+

2 rows in set (0.00 sec)

Mysql > RENAME TABLE testcourses TO test; # can also be renamed directly using RENAME.

Query OK, 0 rows affected (0.00 sec)

6. Add an index: (the index can only be created and deleted, not modified)

CREATE INDEX index_name ON tb_name (col,...)

Col_name (length) ASC | DESC specifies that the length of the first few fields is indexed, and the ASC is sorted in ascending order

DESC is arranged in descending order.

Create an index index_name in the col field on the tb_ name table

CREATE INDEX name_on_student ON student (Name) USING BTREE

# create an index named name_on_student on the Name field in the student table. The type is BTREE, and the default is BTREE.

Mysql > CREATE INDEX name_on_student ON student (Name) USING BTREE

Query OK, 0 rows affected (0.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > SHOW INDEXES FROM student

+ -- +

+ -- +

| | student | 0 | PRIMARY | 1 | SID | A | 4 | NULL | NULL | | BTREE |

| | student | 1 | foreign_cid | 1 | CID | A | 4 | NULL | NULL | | BTREE |

| | student | 1 | name_on_student | 1 | Name | A | 4 | NULL | NULL | YES | BTREE |

+ -- +

3 rows in set (0.00 sec)

Mysql > DROP INDEX name_on_student ON student;# deletes the index name_on_student in the student table

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > CREATE INDEX name_on_student ON student (Name (5) DESC); # for student table with Name field

Sets up an index of DESC arrangement for the first five characters of.

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

Example 1: create a students database and practice the functions of creating and finding tables

Mysql > CREATE DATABASE IF NOT EXISTS students CHARACTER SET 'gbk' COLLATE' gbk_chinese_ci'

# create a students database with a character set of gbk and a collation of gbk_chinese_ci

Query OK, 1 row affected (0.01sec)

Mysql > USE students

Database changed

Mysql > CREATE TABLE courses (CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Couse VARCHAR (50) NOT NULL)

Query OK, 0 rows affected (0.07 sec)

Mysql > SHOW TABLE STATUS LIKE 'courses'\ G

* * 1. Row *

Name: courses

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 1

Create_time: 2017-04-25 10:19:13

Update_time: NULL

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql > DROP TABLES courses; # Delete tables

Query OK, 0 rows affected (0.01 sec)

Mysql > CREATE TABLE courses (CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Couse VARCHAR (50) NOT NULL) ENGINE=MyISAM; # ENGINE sets the engine to MyISAM

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW TABLE STATUS LIKE 'courses'\ G

* * 1. Row *

Name: courses

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: 1

Create_time: 2017-04-25 10:51:45

Update_time: 2017-04-25 10:51:45

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql > INSERT INTO courses (Couse) values ('physics'), (' english'), ('chemistry'), (' maths')

# insert Couse course field data, add physics, English, chemistry, math and other courses.

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM courses; # query the entries of the courses table

+-+ +

| | CID | Couse |

+-+ +

| | 1 | physics |

| | 2 | english |

| | 3 | chemistry |

| | 4 | maths |

+-+ +

4 rows in set (0.00 sec)

Mysql > SHOW INDEXES FROM courses; # View the index of the courses table

+ -+

+ -+

| | courses | 0 | PRIMARY | 1 | CID | A | 4 | NULL | NULL | | BTREE |

+ -+

1 row in set (0.00 sec)

Mysql > CREATE TABLE testcourses SELECT * FROM courses WHERE CID SHOW TABLES; # View the information of the current database table

+-+

| | Tables_in_students |

+-+

| | courses |

| | testcourses |

+-+

2 rows in set (0.00 sec)

Mysql > SELECT * FROM testcourses; # View the contents of the testcourses table

+-+ +

| | CID | Couse |

+-+ +

| | 1 | physics |

| | 2 | english |

+-+ +

2 rows in set (0.00 sec)

Mysql > DESC courses; # View courses table structure

+-+ +

+-+ +

| | CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

2 rows in set (0.00 sec)

Mysql > DESC testcourses; # View testcourses table structure

+-+ +

+-+ +

| | CID | tinyint (3) unsigned | NO | | 0 | |

+-+ +

2 rows in set (0.00 sec)

Mysql > CREATE TABLE test LIKE courses; # creates an empty test table using the courses table as a template.

Query OK, 0 rows affected (0.00 sec)

Mysql > DESC test; # View test table structure

+-+ +

+-+ +

| | CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

2 rows in set (0.00 sec)

Mysql > SHOW TABLE STATUS LIKE 'test'\ G; # View the status of the test table

* * 1. Row *

Name: test

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: 1

Create_time: 2017-04-25 11:31:46

Update_time: 2017-04-25 11:31:46

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

Example 2. Establish student table, insert related data, query operation exercises, modify engine, modify field modification

Add foreign key index

Mysql > CREATE TABLE student (SID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name

VARCHAR (30), CID INT NOT NULL); # create the student table with three fields. The SID field is an integer type of unsigned, non-empty, auto-growing primary key, the Name field is a variable length of 30 characters, and the CID character is a non-empty integer type.

Mysql > SHOW TABLES

+-+

| | Tables_in_students |

+-+

| | courses |

| | student |

| | test |

+-+

3 rows in set (0.00 sec)

Mysql > INSERT INTO student (Name,CID) VALUES ('Li Lianjie',1), ('Cheng Long',2); # insert 2 pieces of data into the Name,CID field

Query OK, 2 rows affected (0.01sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM student; # query student table

+-+

| | SID | Name | CID | |

+-+

| | 1 | Li Lianjie | 1 |

| | 2 | Cheng Long | 2 |

+-+

2 rows in set (0.00 sec)

Mysql > SELECT * FROM courses;# query courses table

+-+ +

| | CID | Couse |

+-+ +

| | 1 | physics |

| | 2 | english |

| | 3 | chemistry |

| | 4 | maths |

+-+ +

4 rows in set (0.00 sec)

Mysql > SELECT Name,Couse FROM student,courses WHERE student.CID=courses.CID; # query the same Name and Couse field contents of CID in student table and courses table

+-+ +

| | Name | Couse |

+-+ +

| | Li Lianjie | physics |

| | Cheng Long | english |

+-+ +

2 rows in set (0.00 sec)

Mysql > DELETE FROM student WHERE SID > 5; delete rows with SID greater than 5.

Query OK, 5 rows affected (0.01sec)

Mysql > ALTER TABLE courses ENGINE=Innodb; # modify the engine of courses table to Innodb

Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > ALTER TABLE student MODIFY CID TINYINT UNSIGNED NOT NULL;# modifies the MODIFY of the CID field in the student table.

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > DESC courses

+-+ +

+-+ +

| | CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

2 rows in set (0.00 sec)

Mysql > DESC student

+-+ +

+-+ +

| | SID | int (10) unsigned | NO | PRI | NULL | auto_increment |

| | CID | tinyint (3) unsigned | NO | | NULL |

+-+ +

3 rows in set (0.00 sec)

Mysql > ALTER TABLE student ADD FOREIGN KEY foreign_cid (CID) REFERENCES courses (CID)

Add a foreign key foreign_cid associated with the CID field of the courses table to the CID field of the student table.

Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > SHOW INDEXES FROM student; to view the index of the student table

+-+ -+

+-+ -+

| | student | 0 | PRIMARY | 1 | SID | A | 2 | NULL | NULL | | BTREE |

| | student | 1 | foreign_cid | 1 | CID | A | 2 | NULL | NULL | | BTREE |

+-+ -+

2 rows in set (0.00 sec)

Mysql > INSERT INTO student (Name,CID) VALUES ('Guo Xiang',5)

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`students`.`student`, CONSTRAINT `students`FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`)) # indicates an error. The CID in the CID foreign key index courses table does not have 5, so it cannot be added.

Mysql > ALTER TABLE student AUTO_INCREMENT=5;# sets the auto-growing primary key SID of the next data item in the student table

It starts to grow from 5.

Query OK, 4 rows affected (0.05sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM student;# query the contents of the table

+-+

| | SID | Name | CID | |

+-+

| | 1 | Li Lianjie | 1 |

| | 2 | Cheng Long | 2 |

| | 3 | Xiao Longnv | 3 |

| | 4 | Yang Guo | 4 |

+-+

4 rows in set (0.00 sec)

Mysql > INSERT INTO student (Name,CID) VALUES ('Guo Xiang',3); # insert a piece of data, the SID primary key grows from 5 because of the above setting, so the data you just inserted starts at 5

Query OK, 1 row affected (0.00 sec)

Mysql > SELECT * FROM student

+-+

| | SID | Name | CID | |

+-+

| | 1 | Li Lianjie | 1 |

| | 2 | Cheng Long | 2 |

| | 3 | Xiao Longnv | 3 |

| | 4 | Yang Guo | 4 |

| | 5 | Guo Xiang | 3 | |

+-+

5 rows in set (0.00 sec)

Mysql > INSERT INTO student (Name,CID) VALUES ('Qiao Feng',2); insert data

Query OK, 1 row affected (0.00 sec)

Mysql > SELECT * FROM student

+-+

| | SID | Name | CID | |

+-+

| | 1 | Li Lianjie | 1 |

| | 2 | Cheng Long | 2 |

| | 3 | Xiao Longnv | 3 |

| | 4 | Yang Guo | 4 |

| | 5 | Guo Xiang | 3 | |

| | 6 | Qiao Feng | 2 |

+-+

6 rows in set (0.00 sec)

Mysql > DELETE FROM student WHERE SID > 2 AND SID SELECT * FROM student

+-+

| | SID | Name | CID | |

+-+

| | 1 | Li Lianjie | 1 |

| | 2 | Cheng Long | 2 |

+-+

2 rows in set (0.00 sec)

Mysql > ALTER TABLE student AUTO_INCREMENT=3; # sets the auto-growth primary key of the next data item in student table

SID began to grow from 3.

Query OK, 2 rows affected (0.07 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > INSERT INTO student (Name,CID) VALUES ('Yang Guo',3), (' Guo Jing',4); # insert 2 pieces of data

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM student

+-+

| | SID | Name | CID | |

+-+

| | 1 | Li Lianjie | 1 |

| | 2 | Cheng Long | 2 |

| | 3 | Yang Guo | 3 |

| | 4 | Guo Jing | 4 |

+-+

4 rows in set (0.00 sec)

After reading this article, I believe you have a certain understanding of "how MySQL manages the creation of CREATE tables and indexes". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.