MySQL table operations and indexing operations

Tag:drop   plain   sel   xpl    Direct      Table name     auto    incr   tab   

Table operations: 1. Table creation: CREATE TABLE if not EXISTS table_name (field definition); Example: CREATE table if not exists user (id int auto_increment,uname var char (+), address varchar ($), UpdateTime datetime,primary key (ID)); Set the primary key 2. Table (show tables;) definition view: 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 tab Le_name Add column column_nname (modifier); Adds a field at the first position of the table, adds it at the end of the statement, add it after the specified field in the table, and finally the after property name; Example: ALTER TABLE user add The column age int is not NULL, the default 0 after uname;//is incremented after the uname column, and is not empty, and defaults to 0. (3) Delete column: ALTER TABLE table_name drop (column) column_name, (4). Field modification: ALTER TABLE table_name change Cloumn old_column_name NEW _cloumn_name property name data type; example: ALTER TABLE user change column adddress address varchar (1024x768) NOT null default ' Beijing ';//Will ADDD The ress is renamed address with a range of 1024, not NULL, and the default value is Beijing (5). Delete Table: DROP table table_name; (6): Copy table: Create TABLE new_table_name like Old_ TABLE_NAME; 4. Constraints on the Operation table:(1). Non-null constraint (not NULL); Directly following the field, for example: name varchar NOT NULL, (2). Set field defaults (default defaults); Directly following the field, for example: name varchar (default ' name ';(3). Set UNIQUE constraint (unique); The unique constraint refers to the inability to add duplicate values; Directly following the field, e.g. name varchar (4) unique; Set PRIMARY KEY constraint (primary key); Single field primary key: Constraint Pk_name primary KEY (name) Directly following the field, for example: ID int primary KEY, multi-field primary key: Constraint Pk_name1_name2 primary KEY (name1, name2); This statement is placed at the end of the creation table. (5). Set the value to automatically increase (auto_increment), followed directly in 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 (property name 2); Sets the foreign key to be placed only in a table that exists in the outer key, placed in the last sentence of the table, and cannot be inserted into 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 outer constraint and the multiple-field primary KEY constraint in the above 6, only the last sentence of the table is created, the rest should be placed after the field when the table was created, and multiple constraints immediately follow. For example: Name varchar (not) Null default ' name ';//setting name is NOT NULL for the name.  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) when queried: by explain select * from users where username = "condition"; you can see that the type is rel, and the normal query is all, and Is the overall scan, reduce efficiency, build index can improve the efficiency of the search. (2). Unique index (value unique cannot be duplicated): Create UNNQIUE index INDEX_UNIQUE_NAME on table_name (attribute name); Example: Create unique index idx_unique_name on users (username); You cannot add records with the same name. (3). Primary key index (must be a unique index, but the unique index is not necessarily the primary key index): ALTER TABLE TABLE_NAME ADD PRIMARY key (property name);(4). Composite index: ALTER TABLE TABLE_NAME ADD index Index_name (property name 1, property name 2 ...); Composite indexes work only in compound lookups, that is, when a multi-conditional query is in effect. (5). Full-Text indexing (InnoDB does not support full-text indexing): Create FULLTEXT INDEX INDEX_NAME on table_name (attribute name); 2. View of the index: Show create TABLE Table_name;show Index from table_name;3. Deletion of indexes: DROP INDEX index_name on table_name; indexes cannot be updated directly, only by deleting and adding them to mimic the update. via MySQL The index operation in Workbench can be used to see the statements executed in detail to deepen the understanding.

operation and indexing of MySQL tables