2. Modify tables, field attribute settings, index settings, constraint settings, and field Indexes

Source: Internet
Author: User

2. Modify tables, field attribute settings, index settings, constraint settings, and field Indexes
Zookeeper

Field attribute settings
Not null is not empty
Auto_increment: set the value of an int type field to "auto-increment", that is, the value is automatically obtained and added without "writing ".
Note: This attribute must be used with the primary key or unique key.
[Primary] key: set as the primary key. Is the unique key "strengthen": it cannot be repeated and cannot use null, and can be used as a "key value" to determine any row of data"
Unique [key]: Set to unique: indicates that the values of all rows of the field cannot be repeated (unique ).
Default 'default': set the value automatically used when a field is not inserted.
Comment 'field annotation'


Index settings
What is an index:
An index is a "built-in table". The data in this table is stored after the data of a certain (some) Field in a real table is sorted.
Its function is to greatly speed up table data search! -- Its efficiency (speed) can rival binary search.
Note: indexes provide search speed while reducing the speed of addition, deletion, and modification.
For creating (designing) tables, creating an index is very simple. The format is as follows:
Index type (field name 1, field name 2,...) // you can use multiple fields to create an index.
There are the following indexes:
Normal index: key (field name 1, field name 2,...): it only has the basic function of index-speed up
Unique index: unique key (field name 1, field name 2 ,....)
Primary key Index: primary key (field name 1, field name 2 ,....)
Full-text index: fulltext (field name 1, field name 2 ,....)

Constraint settings
What is constraints: constraints are a form (mechanism) that limits data to meet certain requirements)
Constraints:
Primary key constraint: primary key (field name 1, field name 2 ,....)
It is actually a primary key index and also a primary key attribute. That is, the primary key has three perspectives: the Field attribute is set as the primary key, the primary key index created, or a primary key constraint, but they are essentially the same.
Unique constraint: unique key (field name 1, field name 2,...) is actually "three-in-one" (similar to primary key)
Foreign key constraints:
What is a foreign key: it is to set the value of a field in a table. It must be "from" the value of a primary key field in another table.
Syntax format:
Foreign key (field name 1, field name 2,...) references table name 2 (field name 1, field name 2 ,....)
Note:
If you set a foreign key for a field, the corresponding fields of other tables must be set as the primary key.
Non-empty constraint: the value of this field cannot be blank. It can only be set as a field attribute on the field.
Default constraint: when the field value is required to be "null", the default value is automatically filled in, and can only be set on the field.
Check constraints: an expression (logical judgment) is used to determine whether the data is valid, such as the age field.
Tinyint may exceed 127.
Tinynit unsigned, then 0-25 5 is OK.
However, if you consider the actual situation, assume that an insurance company only provides insurance for people under 150 years of age. Then we can continue to "constrain" the data that this field may store ". For example: if (age> 150) {return false}
Unfortunately, mysql does not support the syntax and functions for checking constraints.

Modify Table
Modifying a table is to modify the structure or features of a table. In theory, you can create a table and modify the table. There are 20 or 30 items to modify a table, including adding, deleting, modifying, and modifying fields, adding and deleting indexes, adding and deleting constraints, and modifying table options.
Example:
Add field: alter table name add [column] New Field Name field type [field attribute list];

Modify Field (and rename): alter table name change [column] Old field name new field type [New Field attribute list];

Modify Field (modify attribute only): alter table name modify [column] field name new field type [New Field attribute list];

Modifying the field name: I'm sorry for being very gray. I don't have the function of modifying the field name!

Delete field: alter table Name drop [column] field name;

Add a common index: alter table name add index [index name] (field name 1 [, field name 2,...]);

Add a primary key index (constraint): alter table name add primary key (field name 1 [, field name 2,...]);

Add a foreign key index (constraint): alter table name 1 add foreign key (Field 1, [, field name 2,...]) references table name 2 (Field 1, [, field name 2,...]);

Add a unique index (constraint): alter table name add unique (field name 1 [, field name 2,...]);

Add field default value (constraint): alter table name alter [column] field name set default value;

Delete field default value (constraint): alter table name alter [column] field name drop default;

Delete primary key: alter table Name drop primay key; # Each table can have only one primary key

Delete foreign key: alter table Name drop foreign key name;

Delete index: alter table Name drop index name;

Modify table Name: alter table name rename [to] new table name;

Modify table options: alter table name option name 1 = option value 1, option name 2 = option value 2 ,...;


Show all tables: show tables;
Display the structure of a table: desc table name; or: describe table name;
Display the table creation statement: show create table name;
Rename table: rename table old table name to new table name;
Copy the table structure from an existing table: create table [if not exists] new table name like original table name;
Copy the table structure from an existing table: create table [if not exists] new table name select * from original table name where 1 <> 1;
Create [unique | fulltext] index name on table name (field name 1 [, field name 2,...]). Unique or fulltext is omitted here, which is a common index. In fact, this index creation statement maps to an "alter table" index addition statement in the system.
Delete index: drop index name on table name. In fact, this statement is also mapped to a "alter table" delete index statement.


What is a view:
A view can be viewed as a "Table composed of temporary stored data" (non-real table). In fact, it is essentially a select statement. The select statement (usually complicated) is used as a "Wrap" and a name is set. Then, the name can be used as a table.
If a select statement is complex and needs to be used on multiple pages, you can create a view for ease of use.
If some fields in a data table do not want to be viewed by others (when data business is exchanged between different companies), but the other needs to be viewed by others, you can also use the view.
View creation form:
Create view name [(column name 1, column name 2,...)] as a complex select statement;
You can rename the columns obtained by the select statement. If you do not rename the columns, use the given column name in the select statement.
View usage:
It is actually used as a query table (usually used only for select)
Select * from view name where condition order ......
Modify View:
Alter view name [(column name 1, column name 2,...)] as select statement;
Delete View:
Drop view [if exists] view name;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.