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

# #

The Mysql- index type index is similar to the "book of contents" index. The advantage of the index is to speed up the query. The disadvantage of the index slows down the speed of writing (taking up physical storage space) (1) the general index a. When creating a table, directly set the normal index create table stu (> age int, > name char (8), > index (age)); create a normal index for the age field, and the key of the desc stu / age field named age show index from stu is MUL b. Set the normal index create index name (index name) on stu (age (field name)) to the field after creating the table; c. Delete ordinary index drop index name on stu; (2) Primary key characteristics: there can be no duplicate values in the field, and can not be assigned null values if there is already a primary key in the table, can not create a primary key. a. Set the primary key create table stu (> age int, > name char (8), > primary key (name)) directly when creating the table; the key for creating the primary key desc stu; / name field for the name field is PRI b. Create the primary key alter table stu add primary key (name) for the field after creating the table; c. Delete the primary key alter table stu drop primaru key; / delete the primary key, the value can be repeated, but can not be assigned a null value alter table stu modify name char (8); / if you want to assign control, continue to modify field d. Compound primary key: multiple fields are used as primary keys together. The values of fields cannot repeat create table stu (> port int, > cip char (15), > status enum ("up", "down") default "up" > primary key (port,cip)). If you set two primary keys at the same time, you cannot set desc stu; insert into stu (port,cip) values (23, "1.1.1.1") at the same time. Ok insert into stu (port,cip) values (24, "1.1.1.1"); ok insert into stu (port,cip) values (24, "1.1.1.2"); ok insert into stu (port,cip) values (24, "1.1.1.1"); compound primary key, the value in a field can be repeated, but the value in multiple fields cannot be repeated at the same time. Primary key in conjunction with auto_increment (self-plus 1) is generally used for id serial numbers to use auto_increment on the premise that the primary key and the numerical type create table stu (> id int (2) zerofill primary key auto_increment, / int ()) represents the display width, the size of the field assignment cannot be limited, and the size is determined by the type. Zerofill uses 0 complement > port int); insert into stu (port) values (24); desc stu; select * from stu; + id port 01 24 + (3) unique index unique features: fields can be assigned null values, but can not be duplicated a. When creating a table, directly set the normal index create table stu (> age int, > name char (8), > unique index (name)); create a unique index for the name field desc stu; / name field key is UNI b. Set a unique index create unique index name (index name) on stu (name (field name)) to the field after creating the table; c. Delete unique index drop index name on stu; (4) Foreign key function: restrict assignment to fields. The value must be within the range of the specified field value. The storage engine must be innodba. When creating a table, directly set the primary key to specify the reference table create table T1 (> age int, > primary key (age) reference table if the reference field is one of the indexes >) engine=innodb; / set table T1 storage engine to create table T2 create table T1 for engine=innodb (> age int, > name char (8), > foerign key (age) references T1 (age) create foreign key > on update cascade synchronous update > on delete cascade synchronous delete >) engine=innodb / set the value of the age field of the T2 table must be within the range of the t1age field after the successful creation of the table T1 storage engine for engine=innodb, and the value of the t1age field updates or deletes the T2 table to change (update or delete) b. Create the primary key alter table stu add foreign key (age) references T1 (age) on update cascade on delete cascade; c for the field after creating the table. Delete the primary key show create table T2; / check the table creation process CONSTRAINT `t5roomibfk1` FOREIGN KEY to find t5_ibfk_1 alter table stu drop froeign key t5roomibfkroom1; / delete the foreign key. The T2 table is no longer constrained after deletion. # Mysql storage engine show engines; / View storage engine defaults to innodbmysql service architecture: (8 functional modules) connection pool sql interface analyzer query cache storage engine file system management tools Storage engine introduction: procedures included in mysql database service software. Different storage engines have different functions and data storage methods. The processor of the table sets the storage engine of the table: create table table name (.) Engine=innodb; modifies the storage engine of the table alter table table name engine= storage engine name Modified table default storage engine / etc/my.cnfdefault-storage-engine=myisam myisam features: table. MYI index information table. MYD data table. Frm table structure supports table-level locking (lock a table) does not support transaction rollback innodb features: table. frm table structure table. Ibd index information + data support row-level locks (lock only the currently accessed rows) support transaction rollback locks The role of: to solve the problem of concurrent access conflicts. Lock type: read lock and write lock select update delete insert transaction: the process from the beginning to the end of a data access is called transaction rollback: any step of a data access fails and all operations are resumed. Transaction characteristics: consistency atomicity isolation transaction log files: records operations performed on the tables of the innodb storage engine. How to work determines the storage engine used by tables: tables that receive a lot of writes are suitable for using the innodb storage engine. Tables with many read operations are suitable for using myisam storage engine

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.