Who works with database knows how to create and edit tables is a relatively simple task, but at the same time inspires many care. In creation, modeling the bank is essential and enables it to work in a logical and fully optimized, since the change is necessary to be aware of existing records after a change column, for example, can mean the loss of important data.
In MySQL, the commands for creating and editing tables are quite simple, as you can see below.
To create a table in MySQL using the following command:
CREATE TABLE [Table Name]
As a practical example we will create a simple product table that will contain information such as code, product name and value.
CREATE TABLE Product
( cod_prod int unsigned not null auto_increment primary key,
name char(80) not null,
price decimal(9,2) not null)
Note that this table already created the product code as the primary key which will help when creating relationships between tables and we are also saying that the fields do not allow nulls, so all the table fields need to be filled to inserting the record is successful.
To make the change table ALTER is used, the sequence should specify what you want to change.
To add a new column in the table is added to the ADD command syntax as shown below:
ALTER TABLE Product ADD email char(80) null AFTER Price
Note that at the end AFTER the command indicates that the column should be created after the column value.
Now to delete a column of a table using the DROP command as shown below:
ALTER TABLE Product DROP email
It is important to check before any exclusion if the column data is not important, directly interfere in some kind of analysis or if the column is not called in procedures or views in the database.
To edit the attributes of a column simply use ALTER along with the CHANGE command, as shown below:
ALTER TABLE Product CHANGE name name char(300) null
In the above example we change the number of characters of the name column of the Products table, but it is important to pay attention. With this command you can also change the data type of a column, but if this is done with incompatible data added to those already existing data will be lost.
Thanks to the ease and cost-effective, MySQL has become increasingly common in development environments WEB. Because of this the importance of knowing this fantastic DBMS. Visit our official site and learn about the advantages of working with MySQL in PHP projects.