MySQL Basic Operations Learning notes (i)

?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? .?? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

1.auto_increment

?

?

2. Main health

Auto_increment must be used with the primary key, and the primary key is not necessarily used with the former

?

3. Unique key (primary key can have only one in a table , and unique key may have more than one in a single table)

?

?

4.default (default value)

Show the structure of the table????????????????????????????????????????? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

?

Insert Table (does not assign a value to sex, it is automatically populated with default values)

?

View table contents (Sex Auto-fill with no assignment is 3)

?

?

?

5. External health constraints

?

Index is automatically created if the Reference column has no index, and the foreign key column does not have an index.

Table with external health is a child table, the table referenced is the parent table

?

?

(2) Reference operation

?

@1.cascade

Before deleting

Table creation (ID of theusers pid reference provinces, and constrained to Cascade)

Parent Table Provinces

Child table Users1

After deletion

?

Parent table

Child table

?

?

Table-level constraints and column-level constraints

?

?

6. Modify the Data sheet

(1) Add a single column

If you do not add first (the first of all columns) or after (one column later), the default is the last column

Before it was added

Formally added

Add complete

?

(2) Adding multiple columns

You cannot specify a location when you add multiple columns , only the last face of the table (this is different from adding a single column)

?

?

(3) Delete a column

Before deleting

?

After deletion

?

?

7. Adding and removing primary keys

(1) Add ALTER TABLE? USERS2 add constraint? pk_users2? PRIMARY key (ID); (red font can not)

Before adding

When added

After adding

?

(2) Delete ALTER TABLE users1 drop PRIMARY key

?

8. Add and remove unique constraints ( can have multiple, and primary key can only have one )

(1) Add ALTER TABLE? USERS2 Add unique (username)

Before adding

When added,

After adding

?

(2) Delete

ALTER TABLE users2 drop Index/key? (ID)

9. Add a FOREIGN KEY constraint

ALTER TABLE users2 add? constraintfk_users2? Foreign KEY (PID) references provinces (ID);

(red font can not)

?

10. Modify the Data sheet

(1) Modifying a column definition ( changing from a large type to a small type can result in loss of data )

Before modification

When modified

After modification

?

(2) Modify the class name

Before modification

When modified

After modification

?

(3) Renaming of data sheets

Method 2 can modify multiple table names at the same time

(1) Before modification

(2) When modified

(3) After modification

MySQL Basic Operations Learning notes (i)