MySQL Learning notes-view tables, modify tables, delete tables

/* Look at the table */describe test --view the structure of the table show create table test --view the table creation statement Show create  table test  \g --queries the created statement and normalizes the display (this command is supported only under console, because only the console will appear garbled ...). )/* Change table */alter table test rename to testo; --to rename test to testoalter table  example0 rename to user;select table_name from information_schema.tables   where table_schema =  ' Test '--queries all table names (table_name) of Table_schema (database name) test from the entire system---test disappears, Instead, the last testodescribe user;--is to look at the data type of the original User table Name property alter table user modify name  varchar (+);  --Change the Name property of the Testo table to the data type of varchar (30) describe user;--Verify that the modification succeeds----create an experimental table example1, Mending pit Create table example1 (Stu_id int (one)  not null primary key,  stu _name varchar,  --does not specify the default value, which is Default null  stu_sex tinyint (1)  )--- Modify field names and types of fields in a table use test;describe example1--first look at the data type of the original Exmaple table Stu_sex property Alter table example1 change stu_sex sex  tinyint (2);--Change the data type of the field name and field describe example1 --Verify that, complete---add fields alter table  User add phone varchar (20);--Increase the unconstrained field describe user --check results,okalter table  User add age int (4)  not null --Increase the integrity constraint field/* When adding a field, if you can add an integrity constraint, be sure to add it to ensure the security of this field */describe user --inspection results, Okalter table user add num int (8)  primary  key first--, there is a mistake here, before I was clever to set the ID as the primary key, now cannot increase the NUM primary key field. --First learn to delete the primary key bar alter table user drop primary key--change the user table drop its primary  key--re-add new primary key field Alter table user add num int (8)  primary key--error, Prompt for a combined field--check that describe user --didn't drop the primary key. ' A table can have only one self-increment field, and that field must be positioned as the primary key '--so the idea of deleting the self-increment field first seems to only redefine the field, but what to do in a production environment? Back up and Insert into a  select * from b?alter table first user change id id int (11)--Take a look at Describe user--ok and remove the self-increment attribute--now try to delete the primary key alter  Table user drop primary key--ok,command (s)  completed  successfully.--re-attempt to add a new primary key field Alter table user add num int (8)  primary key  --ok,command (s)  completed successfully. --forgot to add first position limit-check the describe user -- The NUM field was successfully added to the table, but the last field was queued ... Alter table user add address varchar ( not ) null after phone --Add an Address field after the Phone field/* Delete field */alter table user drop id  --drop Drop the ID field of the User table--check that describe user --has no/* Modify the position of the field */alter table user modify  num int (8)  first --first to fill in the previous pit describe user --is very good, moisten things silently alter table  User modify name varchar ( first describe user --) Well, this one's right.//Change the field to the specified position */  /* Note that the data type is also attached when modify */ALTER TABLE USER&NBsp;modify sex tinyint (1)  after age --Modify the Sex field of the user table after the age field--if I don't add the field type row? describe sc --find a temporary table to experiment with alter table user modify grade after sno -- No, it seems that the data type plays the role of partition ... /* Before modifying the storage engine */show create table user --modify the--engine=innodbalter table to see how the Create statement defines the storage engine  user engine = myisam--change the storage engine to Myisam,command (s)  completed  successfully.--View the modified creation statement show create table user --engine=myisam/* delete the foreign KEY constraint */show create  table example3 --first find the name of the foreign key for ' C_FK ' alter table example3 drop foreign  key c_fk --no quotes ', Command (s)  completed successfully.--check show create table  example3--no foreign key defined/* Delete table */--before you check if you have describe example5----Create a bar create table example5 (ID  int (one)  not null primary key,  stu_id int (one)  default null  unique,--, key column is uni, which is a uniqueness constraint &NBSp; name varchar (   ) 

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7F/D4/wKioL1cvKTOjIyXNAAEUWXRKlPg361.png "title=" Key constraint named uni. png "alt=" Wkiol1cvktojiyxnaaeuwxrklpg361.png "/>

--Delete Table Drop table example5 --command (s)  completed successfully.--see if there's a describe . Example --ok, there is no errormessage: table  ' test.example '  doesn ' t exist /* delete the table that is dependent on the foreign key */ --First fill pit create table example4 (Id int (one)  primary key,  name varchar (20)  not null,  stu_id int (one)  DEFAULT NULL,  CONSTRAINT D_FK  foreign key (stu_id)  references example1 (stu_id)--error, the foreign key name here should not add '    '  engine  = innodb default charset = utf8--try to delete the main table Example1drop table example1  --Error,errormessage: cannot delete or update a parent row: a  foreign key constraint fails--Delete the associated sub-table foreign key first alter table example4 drop   Foreign key d_fk--command (s)  completed successfully.--check if the foreign key of the child table is deleted use testshow   create table example4 --foreign key definition statement is deleted--continue to delete the dependent primary table Drop table example1 --command (s)  completed  successfully.--to see if describe example1--ok,errormessage: table  ' Test.example1 ' was deleted   doesn ' t exist

MySQL Learning notes-view tables, modify tables, delete tables