MySQL basic operation extension

Constraints:

Not null : Non-null constraint, specifying that a column is not empty

Unique : Unique constraint, specifying that data for a column and a combination of columns cannot be duplicated

PRIMARY Key : PRIMARY KEY constraint, specifying that data for a column cannot be duplicated, unique

FOREIGN Key : Foreign key, which specifies that the column record belongs to a record in the primary table, referencing another data

Check : Check, specify an expression to validate the specified data

NOT NULL

< Span style= "font-size:14px;line-height:130%;font-family: ' The song Body '; >create table  temp (         id int not   null,         name varchar (255)  not null default

create Table Temp   (         id int not   null,         name  varchar (+),         password   varchar (+), constraint  uk_name_pwd Unique (name)

Primary Key

CREATE TABLE Temp ( ID int PRIMARY KEY, Name varchar (25), Password varchar (16) ); Set Primary key self-increment: auto_increment CREATE TABLE Temp ( ID int auto_increment PRIMARY key, Name varchar (25), Password varchar (16) );

FOREIGN key:

Establish association relationships in two tables or tables to ensure data integrity

Note: 1. The value of the child table reference must be within the range of the value of the reference field of the primary table

2. If a value is referenced in the primary table, the corresponding record in the primary table cannot be deleted

3. The column of the child table's foreign key reference can only be the primary key column of the primary table or the column of a unique constraint

CREATE TABLE Student ( Sid INT PRIMARY KEY auto_increment, Sname VARCHAR (20), SCID INT, --FOREIGN KEY (SCID) REFERENCES class (CID) CONSTRAINT fk_scid FOREIGN Key (SCID) REFERENCES class (CID) ) Engine=innodb; DROP TABLE student; DROP TABLE class; Syntax 1:foreign key (foreign key field) REFERENCES Main Table (primary key field); Syntax 2:constraint foreign key name FOREIGN key (foreign key field) REFERENCES Main Table (primary key field); (Foreign Key Name: Fk_ field name) --------------------------- ENGINE: engine MySQL a unique data storage mechanism

(no effect in MySQL database)

CREATE TABLE  student ( sid INT PRIMARY KEY, sname VARCHAR () not NULL, ssex CHAR (1), sage INT Check (sage<19) )

to create a table after the operation of the table structure:

To view the table structure:

DESC table name; mysql> desc Student;

To modify a column type:

Alter TABLE table name MODIFY column name target type; mysql> ALTER TABLE student MODIFY Sid VARCHAR (10);

Add Columns:

Alter TABLE table name ADD column name type; Mysql>alter TABLE student ADD Sage int;

To delete a column:

Alter TABLE table name DROP column name; mysql> ALTER TABLE student drop sname;

To modify a column name:

Alter TABLE table name change old column name new column name type; mysql> ALTER TABLE Student change AAA BBB INT;

Modify Table Name:

Syntax 1: ALTER TABLE table name RENAME new table name; Syntax 2: RENAME TABLE old table name to new table name; mysql> ALTER TABLE student rename Stu; Mysql> Rename table student to Stu;

MySQL basic operations extension