The fourth theoretical basis of Mysql database-the management of tables and indexes 03/28 Update SLTechnology News&Howtos

The fourth theoretical basis of Mysql database-the management of tables and indexes

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

Shulou(Shulou.com)06/01 Report--

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database server is fast, reliable and easy to use.

5. MySQL server works in client / server mode, or in embedded system.

2. MySQL storage engine, also known as table type

2.1. Single field:

PRIMARY KEY primary key

UNIQUE KEY unique key

2.2 single or multiple fields:

PRIMARY KEY (col,...)

UNIQUE KEY (col,...)

INDEX (col,...)

2.3 data types:

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] double-precision integer |

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

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

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

Time:

| | DATE date type |

| | TIME time type |

| | TIMESTAMP time zone |

| | DATETIME date and time type |

| | YEAR year |

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

VARCHAR (length) variable length character 65535 characters

[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 |

Text: (not uppercase)

| | TINYTEXT [BINARY] A very small text string of 255 characters 2x8 |

[CHARACTER SET charset_name] [COLLATE collation_name]

| | TEXT [BINARY] text string 65535 characters 2x16 |

[CHARACTER SET charset_name] [COLLATE collation_name]

| | text string 16777215 characters 2x24 in MEDIUMTEXT [BINARY] |

[CHARACTER SET charset_name] [COLLATE collation_name]

| | LONGTEXT [BINARY] long text string 4294967295 characters 2x32 |

[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 |

2.4. 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 |

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

...

197 rows in set (0.00 sec)

Mysql > show global variables like'% char%'

+-+ +

| | Variable_name | Value |

+-+ +

| | character_set_client | latin1 |

| | character_set_connection | latin1 |

| | character_set_database | latin1 |

| | character_set_filesystem | binary |

| | character_set_results | latin1 |

| | character_set_server | latin1 |

| | character_set_system | utf8 |

| | character_sets_dir | / usr/share/mysql/charsets/ |

8 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

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

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

2.5. 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

2.6. Delete the database:

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

2.7. Create a table:

2.7.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:

The id field is unsigned (UNSIGNED), non-empty (NOT NULL), automatic increment (AUTO_INCREMENT), and is the integer of the primary key (PRIMARY KEY).

The Name field has a fixed length of 20 (CHAR (20)), which is not an 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

2.7.2. 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)

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)

2.9. New index: (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 first few lengths of the field are indexed, sorted in ascending ASC and sorted in descending DESC.

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

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

1.1. Create a students database

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

# character set is gbk and collation is 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 # database engine

Version: 10

Row_format: Compact # is similar to compressed format storage

Rows: 0

Avg_row_length: 0

Data_length: 16384

...

1.2. Delete the table and create a new table whose engine is MyISAM

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

...

1 row in set (0.00 sec)

1.3.1 add values to the new table

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-B-tree index

+-+

+-+

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

1 row in set (0.00 sec)

1.3.2 query values are added to the new table, but the resulting table will be inconsistent with the original table format. You can create the table before inserting the value.

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 the structure of the testcourses table is different from the courses above

+-+ +

+-+ +

| | 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)

Example 2. Establish student table, insert related data, query operation exercises, modify engine, modify field modification, and add foreign key index.

2.1. Establish student table

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

# create a student table with 3 fields. The SID field is an integer of unsigned, non-empty, auto-growing and primary key.

The Name field has a variable length of 30 characters

The CID character is a non-empty integer.

Mysql > SHOW TABLES

+-+

| | Tables_in_students |

+-+

| | courses |

| | student |

| | testcourses |

3 rows in set (0.00 sec)

2.2. Insert data into the student table

Mysql > INSERT INTO student (Name,CID) VALUES ('Li Li',1), ('ChengChen',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 Li | 1 |

| | 2 | ChengChen | 2 |

2 rows in set (0.00 sec)

2.3. Query data in the student table

Mysql > SELECT * FROM courses; # query courses table

+-+ +

| | CID | Couse |

+-+ +

| | 1 | physics |

| | 2 | english |

| | 3 | chemistry |

| | 4 | maths |

| | 5 | Hamagong |

| | 6 | Pixiejianfa |

| | 7 | Kuihuabaodian |

7 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 Li | physics |

| | Cheng Chen | english |

2 rows in set (0.00 sec)

2.4. Delete and modify data in student table

Mysql > DELETE FROM courses WHERE CID > 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 decorated 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 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; # 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 `student_ibfk_ 1` FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`))

# error message. Because there is no 5 CID in the CID foreign key index courses table, it cannot be increased.

Mysql > ALTER TABLE student AUTO_INCREMENT=5; # sets the auto-growth primary key SID of the next data item in the student table 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 just inserted starts from 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

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 SID for the next data item in the table 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)

2.5. Create an index in a student table for comparison or sorting

Mysql > SELECT * FROM student

+-+

| | SID | Name | CID | |

+-+

| | 1 | Li Li | 1 |

| | 2 | Cheng Chen | 2 |

| | 3 | YangGuo | 3 |

| | 4 | GuoJing | 4 |

4 rows in set (0.00 sec)

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

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

The mysql > DROP INDEX name_on_student ON student; # index can only be created or deleted because its structure cannot be modified

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

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

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > SHOW INDEXES FROM student

+-+ +

+-+

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

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

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

3 rows in set (0.00 sec)

-end-6

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.