MySQL Learning notes

MySQL Deletes the contents of the table, but does not delete the tables structure

TRUNCATE TABLE DONGFANG_HK    clears all data, primary key starting from 1
Delete from Dongfang_hk   deletes all data, the primary key continues to grow

MySQL add columns, modify columns, delete columns

ALTER table: Add, modify, delete table columns, constraints, and other table definitions.

• View column: Desc table name;
• Modify table name: ALTER TABLE T_book Rename to BBB;
• Add column: ALTER TABLE name add column name varchar (30);
• Delete column: ALTER TABLE table name drop column name;
• Modify Column name Mysql:alter table BBB change nnnnn hh int;
• Modify the column name Sqlserver:exec sp_rename ' t_student.name ', ' nn ', ' column ';
• Modify the Column name Oracle:lter table BBB Rename column nnnnn to HH int;
• Modify Column properties: ALTER TABLE T_book modify name varchar (22);

ALTER TABLE User_info modify user_name varchar (ten) after user_id;
Move the user_name field to the back of user_id
If you want to move to the front:
ALTER TABLE User_info Modify USER_ID char (8) first;//move user_id to the front!!
The prerequisite column must exist in the table

MySQL error SQL error:1366:incorrect string value.

MySQL error SQL error:1366:incorrect string value: \xe8\xaf\xa6\xe7\xbb\x86:for column

Set Names UTF8

To make Excel batch import MySQL, first convert Excel to TXT format, then bulk Import

TXT loaded into MySQL

Load Data InFile ' d:/1.txt ' into Table tablename field terminated by ', ' lines terminated by ' \ n '//per domain ', ' terminate, per line ' \ n ' Terminate

MySQL exported as txt

SELECT * into outfile ' D:\man.txt ' from TableName;

CREATE TABLE Erollment (
Sno varchar (8) NOT NULL,
Cno varchar (3) NOT NULL,
Tno varchar (6) NOT NULL,
Grade double NOT NULL,
Primary KEY (SNO,CNO,TNO), foreign Key (Sno) references student (Sno),
Foreign KEY (CNO) References courses (CNO), foreign Key (TNO) references teacher (TNO)
);

But my watch has been built, now how to add foreign key ah?

Reply

ALTER TABLE erollment
Add constraint fk_s foreign key (SNO) references student (SNO),
Constraint Fk_c foreign KEY (CNO) References courses (CNO),
Constraint fk_t foreign KEY (TNO) references teacher (TNO)

Ask

What does that fk_s mean, can you change it to something else?

Reply

Can be changed, is the constraint name, but cannot repeat

MySQL Learning notes