Second, modify table, field property settings, index settings, constraint settings



Field property settings
Not NULL is not empty
Auto_increment sets the value of the Int type field to "self-grow", meaning that its value does not need to be "written" and is automatically obtained and incremented
Note: This property must be used with primary key or unique key.
[PRIMARY] Key: Sets the primary key. Is the unique key "enhanced": also cannot be duplicated and cannot use NULL, and can be used as a "key value" for determining any row of data
Unique [key]: set to Unique key: The value of all rows representing the field cannot be duplicated (uniqueness).
Default ' defaults ': sets a value that the field automatically uses when no data is inserted.
Comment ' field comment '

Index settings
What is an index:
An index is a "built-in table" of data that is stored in the form of a "sort" of data for one of the fields of a real table.
The effect is: greatly improve the speed of table lookup data! -its efficiency (speed) can match the binary search.
Note: The index reduces the speed of additions and deletions while providing the search speed.
Indexing is a very simple thing to create (design) tables, in the following form:
Index type (field name 1, field Name 2, ...). )//Can be indexed using multiple fields, but is usually a
There are several indexes:
Normal index: Key (field name 1, field Name 2, ....) ): It only has the basic function of indexing--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 a constraint: A constraint is a form of qualifying data to conform to a requirement (mechanism) The
constraints are mainly:
PRIMARY KEY constraint:primary  key (field name 1, field Name 2, ...). The
is actually the primary key index and the primary key attribute. That is, primary  key has 3 angles of understanding (argument): The field property is set as the primary key, or the key index is established, or a PRIMARY KEY constraint is set, but their essence is the same
Unique constraint:unique  key  (field name 1, field name 2, .... ), is actually "3 body in one" (similar to primary key)
foreign KEY constraint:
What is a foreign key: is to set the value of a field in a table and must "originate" from the value of one of the primary key fields of the other table.
Syntactic form:
Foreign key (field name 1, field Name 2, ...) References table name 2 (field name 1, field Name 2, ...)
Description:
To set a foreign key for one of the fields, the corresponding field of the other table corresponding to it needs to be set as the primary key.
Non-null constraint: It is required that the value of the field cannot be empty and can only be set on the field as a field property.
Default constraint: The value of the field is required to automatically populate the default value of the setting when it is "empty", and only the field is set.
CHECK constraints: is to use an expression (logical judgment) to determine whether the data is valid, such as the Age field, you can use
tinyint, it may be more than 127 is inappropriate.
tinynit  unsigned, then 0-255 is possible.
However: if the situation is considered, it is assumed that an insurance company is only insured for persons under the age of 150. Then we can continue to "constrain" the data that the field might store. For example: if (age >) {return false}
Unfortunately, MySQL does not support checking the syntax and functionality of constraints.

Modify Table
Modifying a table refers to modifying the structure or attributes of a table. In theory, creating a table can do things, modify the table can do. The modified table has twenty or thirty items, including adding and removing fields, adding and deleting indexes, adding and removing constraints, modifying table options, and so on.
Examples are as follows:
Add field: ALTER TABLE name add [column] new field type [field attribute list];

Modify field (and rename): ALTER TABLE name change [column] Old field name new field name new field type [new field attribute list];

Modify a field (property only): ALTER TABLE name modify [column] Field name new field type [new field property list];

Modify field name: Gray often sorry, there is no simple modification of the field name this function!

Delete field: ALTER TABLE name drop [column] field name;

Add normal index: ALTER TABLE name add index [index name] (field name 1[, field name 2,...]) ；

Add primary key index (CONSTRAINT): ALTER TABLE name add primary key (field name 1[, field name 2,...]) ；

Add 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 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 (constraint): ALTER TABLE name ALTER [column] field name drop default;

Delete PRIMARY key: ALTER TABLE name drop Primay key; #每一个表最多只能有一个主键

Delete foreign key: ALTER TABLE name drop FOREIGN key 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;
Show the structure of a table: Desc table name; or: describe table name;
Show creation statement for a table: Show create table table name;
Rename table: Rename table name to new table name;
Copy table structure from existing table: CREATE TABLE [if not EXISTS] new table name like original table name;
Copy table structure from an existing table: CREATE TABLE [if not EXISTS] new table name select * FROM original table name where 1<>1;
Creating an index: Create [unique | fulltext] index index name on table name (field name 1[, field name 2,...]). This omits the unique or fulltext, which is the normal index. In effect, this creates an index statement, which is mapped inside the system as an "ALTER TABLE" add-on index statement.
Delete index: Drop the index name on the table name. In fact, this statement is also mapped to an "ALTER TABLE" DELETE index statement.

What is a view:
A view can be thought of as a "table of temporarily stored data" (not a real table), but essentially a SELECT statement. Just make the SELECT statement (usually more complex) a "wrapper" and set a name that can then be used as a table by that name.
If a SELECT statement is complex and needs to be used on multiple pages, it can be made into a view for ease of use.
Also, if some of the fields in a datasheet don't want to be seen (when data is exchanged between companies), but the other needs to be seen, the view can be used at this point.
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, but you can also do so without renaming, using the given column name in the SELECT statement.
Use of Views:
is actually used as a query table (usually only for select)
SELECT * from view name where Condition ORDER by ...
To modify a view:
ALTER VIEW name [(column name 1, column Name 2, ... )] AS SELECT statement;
To delete a view:
Drop view [if exists] views name;

Second, modify table, field property settings, index settings, constraint settings