MYSQL Learning Path-Advanced 2

Directory:

1. Constraints 2. ALTER TABLE3. VIEW

1. Constraints

Description: SQL constraints are used to specify the data rules in the table, and if there is a violation of the data behavior of the constraint, the behavior will be constrained to terminate, the constraint can be in the construction of the table is a rule, can also be established after the table, through ALTER TABLE implementation.

Grammar:

CREATE TABLE TABLENAME (column_name1 data_type (size) constraint_name,column_name2 data_type (size) constraint_name, Column_name3 data_type (SIZE) constraint_name);

Constraints are commonly used in the following ways:

Not NULL                          --Indicates that a column cannot store a NULL value unique-                            -guarantees that each row of a column must have a unique value primary KEY                       -the combination of the first 2 constraints, each table can have only one primary KEY constraint    CHECK                             --guarantees that the values in the column compound the specified condition                           Default--defaults when the column is not assigned a value

For the above constraints, we look at specific application examples

Example 1:

1 CREATE TABLETABLENAME2 (3IdINT  not NULLAuto_increment,4NAMEVARCHAR(255)UNIQUE ,5AgeINT ,6CityVARCHAR(255)DEFAULT ' China',7 PRIMARY KEY(ID),8 CHECK(age>  -)9)；

If you give a column a name or attach multiple constraints to multiple columns, you can use the following method

Example 2:

1 CREATE TABLETABLENAME2 (3IdINT  not NULLauto_increment,--automatically populate this column starting from 14NAMEVARCHAR(255)UNIQUE ,5AgeINT ,6CityVARCHAR(255)DEFAULT ' China',7 PRIMARY KEY(ID),8 CHECK(age>  -)9 CONSTRAINTalias constraint name (column 1, column 2, ...)Ten)；

If multiple columns add a unique constraint, the 9th line of code can be changed to the following form

CONSTRAINT(age,city)

If more than one column adds a check constraint, the 9th line of code can be changed to the following form

CONSTRAINT CHECK (age>+ and='China')

We have established a few constraints on the table, so if we execute the following SQL will be an error

INSERT  intoTABLENAMEVALUES(1,'Xuechao', A,'CH');INSERT  intoTABLENAMEVALUES(2,'Xuechao', the,'CH');

Because name has added a unique constraint, age adds a check constraint

PS: I am still a rookie, understand is not very comprehensive, I understand the constraint is like this, if you have any different understanding, can comment area tell me, thank you!

2.ALTER TABLE

Description: We have added constraints in front of the table, and if we do not add constraints when we build the table, then we can add the constraint through ALTER TABLE, and look at the following example

Example 1:

ALTER TABLE TABLENAME ADD UNIQUE (ID); ALTER TABLE  ADDCONSTRAINTUNIQUE (ID, NAME);

Adding other constraints is the same principle and notation

Description: ALTER table can attach a constraint to a table, there is another purpose, add a new field to the table, delete the field, modify the field data type (I know these ^_^)

Example 2:

1 ALTER TABLETABLENAMEADD COLUMNScoreINT;--new Field Score2 3 ALTER TABLETABLENAME MODIFY ScoreVARCHAR(255);--modifying field data types4 5 ALTER TABLETABLENAMEDROP COLUMNScore;--Delete a field

The view adds, a little busy no time to write ....

MYSQL Learning Path-Advanced 2