MySQL Base exercise

To create a data table:
CREATE TABLE TT1 (
ID int,
Name varchar (20),
Age Int,sex Boolean
);
INSERT into TT1 values (1, "Zhang", 25,0);
INSERT into TT1 values (2, "Wang", 25, 1);
Insert into TT1 (Id,name,age,sex) VALUES (3, "Li", 28, 1);
Insert into TT1 (id,name,sex,age) VALUES (4, "Sun", 0,22);

modifying data tables
Modify Table Name:
ALTER TABLE name rename to new table name
Eg:alter table tt1 Rename to info;

Modify Field Name:
ALTER TABLE name change old field name new data type for new field name;
Eg:alter table info Change ID number int (11);

To modify the field data type:
ALTER TABLE name modify need to modify the field name of the data type new data type;
Eg:alter Table info Modify sex char (2);

Adding and Removing fields
Add to:
ALTER TABLE name add new field name new data type;
Eg:alter Table Info Add class int (10);

Delete:
ALTER TABLE name drop field name;
Eg:alter table Info Drop class;

Supplemental (delete) Constraints:

Constraint name: Constraint type _ table name _ Field name
Add primary key:
ALTER TABLE name ADD CONSTRAINT constraint name primary key (field name);
Eg:alter table info Add constraint Pk_number primary key (number);
To delete a primary key:
ALTER TABLE name drop PRIMARY key [primary key name];
Eg:alter table info drop primary key;

FOREIGN key:
ALTER TABLE name ADD CONSTRAINT constraint name foreign key (field name) References reference table table name (referenced field name);

Check:
ALTER TABLE name ADD constraint constraint name check (constraint condition);

Default:
ALTER TABLE name add the name of the field to be modified set default defaults;

Self-increment:
ALTER TABLE table name modify column field name Type auto_increment;
Eg:alter Table info Modify column number int auto_increment;

To delete a data table:
No foreign Key associations:
drop table name;

There are foreign key associations:
Disassociate First:
ALTER TABLE from the table name drop foreign key foreign key name;
Again
drop table name;

Insert data:
All columns are inserted into values:
Insert into table name values (value 1, value 2, value 3 ...);
Feature: Column value same number, column name same order

Specific column inserts:
Insert into table name (field name 1, field name 2, field name 3 ...) Values (value 1, value 2, value 3 ...);

Insert multiple data at once:
Insert into table name (field name 1, field name 2, field name 3 ...) Values (value 1, value 2, value 3 ...), (value 1, value 2, value 3 ...), (value 1, value 2, value 3 ...);

To modify the data:
All:
The Update table name set needs to modify the data name;

Specific:
The Update table name set needs to modify the data name where condition;
Eg:update Info Set name = ' Chapter ' where number = 1;

To delete data table data:
Delete:delete from table name [where condition];
Eg:delete from info;
truncate:truncate table name; (Empty data table all data)
Eg:truncate info;

MySQL Base exercise