Mysql table operations and index operations, mysql Indexes

Mysql table operations and index operations, mysql Indexes
Table operations: 1. create table: create table if not exists table_name (field definition); example: create table if not exists user (id int auto_increment, uname varchar (20), address varchar (200 ), updateTime datetime, primary key (id); // Set primary key 2. view table (show tables;) Definitions: show create table table_name; DESC table_name; show full columns from table_name; 3. table update: (1) table rename: alter table old_user_name rename to new_user_name; rename table old_user_name to new_user_name; (2) add column: alter table table_name add column column_nname (modifier ); add a field at the FIRST position of the table, add "FIRST" at the end of the statement, add the field AFTER the specified field in the table, and add the "AFTER" attribute name at the end. Example: alter table user add column age int not null default 0 after uname; // add the age column after the uname column, which is not empty. The default value is 0. (3) Delete A column: alter table table_name drop (column) column_name; (4 ). field modification: alter table table_name change cloumn old_column_name new_cloumn_name attribute name data type; example: alter table user change column adddress address varchar (1024) not null default 'beijing '; // rename adddress as address, range: 1024, not empty, default value: beijing (5 ). drop table table_name; (6): Copy table: create table new_table_name like old_table_name; 4. operation table constraints: (1 ). non-null constraint (not null); directly following the field, for example, name varchar (20) not null; (2 ). set the default value of a field (default). Follow the field directly, for example, name varchar (20) default 'name'; (3 ). set the unique constraint (unique). The unique constraint indicates that duplicate values cannot be added. The unique constraint is directly followed by a field, such as name varchar (20) unique; (4 ). set the primary key constraint (primary key); single-field primary key: constraint pk_name primary key (name); directly following the field, such as: id int primary key; multi-field primary key: constraint pk_name1_name2 primary key (name1, name2); this statement can be placed at the end of the table creation. (5 ). the set value is automatically increased (auto_increment), directly following the field, such as: id int auto_increment; (6 ). set foreign key constraint (foreign key): Syntax: constraint foreign key constraint name foreign key (attribute name 1) references table name (attribute name 2 ); the set foreign key can only be placed in a table with a foreign key. It is placed in the last sentence of the table being created. After setting, it cannot insert a value that does not exist in the parent table. syntax example: constraint fk_deptno foreign key (deptno) references t_dept (deptno); in addition to the external constraints and multi-field primary key constraints, the above six can only be placed in the last sentence of the table creation, the rest should be placed after the field at the time of table creation, followed by multiple constraints. for example, name varchar (20) not null default 'name'; // you can specify the default value for a name that is not null. index operation: 1. index creation (1 ). normal index: create index index_name on table_name (attribute name); example: create index idx_name on users (username); query: Use explain select * from users where username = "condition "; it can be seen that the type is rel, while the common query is ALL, that is, full scan, which reduces the efficiency. index creation can improve the search efficiency. (2 ). unique index (unique values cannot be repeated): create unnqiue index index_unique_name on table_name (attribute name); example: create unique index idx_unique_name on users (username ); // records with the same name cannot be added. (3 ). primary key Index (it must be a unique index, but the unique index is not necessarily a primary key index): alter table table_name add primary key (attribute name); (4 ). composite index: alter table table_name add index index_name (attribute name 1, attribute name 2 ....); composite indexes only work in composite queries, that is, they work in Multi-condition queries. (5 ). full-text index (innodb does not support full-text index): create fulltext index index_name on table_name (attribute name); 2. view indexes: show create table table_name; show index from table_name; 3. index deletion: drop index index_name on table_name; indexes cannot be directly updated. You can only delete and then add indexes to simulate the update. through the index operation in mysql workbench, we can see the executed statements in detail to better understand them.