mysql--additions and deletions to search

• Modify Table Name

  • --Create a user tableCREATE TABLE IF  not EXISTSUser7 (IDSMALLINTUNSIGNEDKEYAuto_increment,usernameVARCHAR( -) not NULL UNIQUE, PasswordCHAR( -) not NULL, emailVARCHAR( -) not NULL DEFAULT '[email protected]', AgeTINYINTUNSIGNEDDEFAULT  -, Sex ENUM ('male','female','Confidentiality') not NULL DEFAULT 'male', addrVARCHAR( -) not NULL DEFAULT 'Shanghai', SalaryFLOAT(6,2),--This is a floating-point 6-digit number, reserved for two decimal places.RegtimeINTUnsigned,faceCHAR( -) not NULL DEFAULT 'default.jpg');INSERTUser6 (Id,username,password,regtime)VALUES(1,'Zhiqiang','Dazhi',1994);--Rename table name: User7 into User8ALTER TABLEUser7 RENAME toUser8;ALTER TABLEUser8 RENAME asUser7;--both to and as can be omittedALTER TABLEUser8 RENAME User7; RENAMETABLEOriginal table name toThe name of the new table;--this to cannot be omitted

• Adding and Removing fields

  • --Add a field, delete a field--add: ALTER TABLE tbl_name Add field Name field type [integrity constraint][first| After field name] (after the header or a field) and the end of the writing field can be the location of the fields--Add Card field CHAR (+)ALTER TABLEUser7ADDCardCHAR( -);ALTER TABLEUser7ADDTest1VARCHAR( -) not NULL UNIQUE;ALTER TABLEUser7ADDTest2VARCHAR( -) not NULL UNIQUEFirst ;--add multiple fields at once select a table to complete multiple operationsALTER TABLEUser7ADDTest5VARCHAR( -) not NULL UNIQUEAfter sex,ADDTest6SET('A','B','C');--Delete the specified field ALTER TABLE tab_name DROP field name ALTER TABLEUser7DROPTest6; --Disposable Delete Test1,test2,test3,test4,test5 ALTER TABLEUser7DROPTest1,DROPTest2,DROPTest3,DROPtest4;--Add Test field delete Addr field ALTER TABLEUser7ADDTestINTUNSIGNED not NULL DEFAULT TenAfter sex,DROPAddr

• modifying fields

  • --modify field Alter TABLE tab_name MODIFY field Name field type [integrity constraint][first| After field name] --MODIFY Modifying the field type will email modify the bit varchar ($) ALTER TABLEUser7 MODIFY EmailVARCHAR( $);--It's best to bring all the integrity constraints on this change. ALTER TABLEUser7 MODIFY EmailVARCHAR( -) not NULL DEFAULT '[email protected]';--with complete restraint  --After you move the card field to the Test field ALTER TABLEUser7 MODIFY CardCHAR( -) after test;--Here's the full binding condition of the card.  --Change the test field to a bit char (+) not NULL the DEFAULT ' 123 ' moves to the first position ALTER TABLEUser7 MODIFY TestCHAR( +) not NULL DEFAULT '1234'First ; --Modify the name of the field (you can also complete the Modify function) ALTER TABLE tab_name the original field name new field Name field type --Change the test field to Test1 ALTER TABLEUser7 Change Test Test1CHAR( +) not NULL;

• Add Delete default value

  • -- Add Delete default value ALTER TABLE ALTER SET DEFAULT  alterTABLEalterDROPDEFAULT

• Add Delete primary key

  • --adding and removing primary keys ALTER TABLETable nameADD [CONSTRAINT] PRIMARY KEY(Field name,...)--... Indicates that the parentheses can be multiple fields, that is, the composite primary key, if it is one, it is a single primary key ALTER TABLETable nameDROP PRIMARY KEY CREATE TABLEtest12 (IDINT );ALTER TABLETest12ADD PRIMARY KEY(ID);CREATE TABLEtest13 (IDINT, cardVARCHAR( -) not NULL);ALTER TABLEtest13ADD PRIMARY KEY(Id,card);ALTER TABLEtest13DROP PRIMARY KEY;--DeleteALTER TABLETest12DROP PRIMARY KEY;--Delete--Note: If you are building a table at the same time as the primary key, you cannot directly delete the primary key, but you should delete the aotu_increment first, then delete the primary keyALTER TABLETable name MODIFY ID UNSIGNED not NULL;--that's not going to grow.

• Add Delete Unique

  • --Add Test IndexALTER TABLETable nameADD UNIQUE(username);ALTER TABLETable nameADD  CONSTRAINTSymbolUNIQUEUNI_ID (username);--The uni_id here is the name of an index, and if you do not write the system it will change the name of the field to the default index name.ALTER TABLETable nameADD  CONSTRAINTSymbolUNIQUEAA (Card,username);--Composite Index Unique--Delete Index UniqueALTER TABLETable nameDROP{INDEX|KEY} unique name;--modifying the engine of a tableALTER TABLETable name ENGINE=MyISAM;--modifying self-growth valuesALTER TABLETable name Auto_increment= -;

• Delete a data table
  • 　　

    -- Delete a data     table DROP TABLE [IF  EXISTS] table name; -- You can delete more than one  at a time, or you can delete a    similar add. of course, manual removal is also possible. You must turn off MySQL, close the MySQL service, and finally manually delete the data table in the file-- when we enter MySQL to log in and open a database directly:- - - D database name.

mysql--additions and deletions to search