LinuxAndUbuntu - Linux News | FOSS Reviews | Linux Tutorials HowTo
  • Home
    • Linux >
      • Ubuntu
      • Linux Mint
      • Fedora
      • Arch Linux
      • Open Suse
      • Other Linux Distros
  • Linux Store
  • Video Tutorials
  • Linux Themes
  • FAQs
  • Advertise
  • Contact Us
  • Block Adblock
  • Favorites
  • Ubuntu Releases
  • Linux Mint Releases

Creating and Modifying Tables - MySQL Series Part 3

26/1/2019

Comments

 
Creating and Modifying Tables
Today we'll continue our MySQL series and learn how to create and modify tables. MySQL tables are the containers that actually hold data. A table with multiple columns and records can be seen as an excel sheet.
database spreadsheet
In this article, we will try to create some tables that can hold our data and also modify them. So fire up your mysql command line clients and Let's learn.

Creating Tables

The first thing we will do is create a table. To do this we need to select the database we want to work with. This can easily be done by use theitstuff;. Replace theistuff by the database of your choice. If you haven’t created the database you can do that by create database theitstuff;.
create database mysql command
The command is fairly simple. Just type in the following command.
create table employee;
This should give you the following output.
create table command mysql
We got an error saying that a table must have at least 1 column. This is because we tried creating a table without describing a single column. A table must have at least 1 column.

​Now we need to add some columns to this table. Have a look at the following code.
create table employee( name varchar(50), email varchar(50), salary int);
We have added 3 columns in the above table name, email and salary. For now just run the code above and then type describe employee;. This should give you the following output.
create table keys value command mysql
You can see that we have described the structure of the employee table that we created and it has 3 fields or columns. And they have the characteristics that we defined while creating the table.
  • name - Varchar with maximum length of 50.
  • email - Varchar with maximum length of 50.
  • salary - Integer with maximum length of 4 bytes.
You can see some other columns as well, like Null, Key, Default and Extra. These are all attributes of each column. We will discuss them in detail later.

​Now we have successfully created a table with 3 columns. Let’s say in future we wanted to add a new column called
city. We could do that with the following command.
alter table employee add column city varchar(50);
alter table command mysql
We could also drop the column from the table by using.
alter table employee drop column city;
alter table drop column command mysql
You can see that the city column has been dropped.
​

We can also give multiple commands such as adding multiple columns or deleting columns. This can be achieved by simply putting the commands separated by a comma sign.
alter table employee add column city varchar(50), add column age int;
alter table add multiple column
Now that you know how to add or delete columns from a database, we can finally learn how to alter a column. This means we will be modifying the attributes of a column.
​

We will be using the following command to change the size of city column to 20 characters. This means we will have to use varchar(20).
alter table employee add column city;
alter table command mysqli
You can see in the example above that we have successfully changed the city column to a varchar(20) from a varchar(50).
​

Finally, if you ever wanted to rename a column you could do that as well. But doing this will require you to enter all the attributes of the new column including the new name and datatype. To change the name of city column to country. We could do this.
alter table employee change city country varchar(20);
alter table column command mysql

Conclusion

We saw various table operations in MySQL. We created and modified various columns in our tables. We also deleted and renamed existing columns in our tables. We will learn more about column attributes some other day. If you get stuck somewhere or have a doubt, drop it in the comment sections and I’ll be there for you.


Related articles
Comments
comments powered by Disqus

    Most Read Articles

    5 Lightweight Linux For Old Computers
    20 Best Themes For Ubuntu In 2017

    8 Best IDEs Or Code Editors For Linux
    8 Best IDEs Or Code Editors For Linux

    3 best twitter clients for linux
    [How To] Install Latest NVIDIA Drivers In Linux

    Ubuntu Vs. Linux Mint - Which Is Better in 2016?
    5 Lightweight Linux For Old Computers

    dual boot ubuntu and windows
    10 Basic Linux Commands That Every Linux Newbies Should Remember

    dual boot ubuntu and windows
    6 Best Linux Distributions For Programming


    Translate Our Site



    Notice : Please be careful, after translation commands will change.

    This website uses marketing and tracking technologies. Opting out of this will opt you out of all cookies, except for those needed to run the website. Note that some products may not work as well without tracking cookies.

    Opt Out of Cookies

About LinuxAndUbuntu

​​LinuxAndUbuntu.com  focuses on open source projects but  specific on debian based distributions. We also give value to other linux distributions.

Links

Ubuntu.com
Video Tutorials
Tutorials & HowTos
Privacy Policy
Contact us

Like Us

More From LinuxAndUbuntu

Contribute  Your Work
Advertise

RSS Feed

Picture
Ubuntu is a registered trademark of  Canonical Ltd.

Any material in this site can not be reproduced without permission.
  • Home
    • Linux >
      • Ubuntu
      • Linux Mint
      • Fedora
      • Arch Linux
      • Open Suse
      • Other Linux Distros
  • Linux Store
  • Video Tutorials
  • Linux Themes
  • FAQs
  • Advertise
  • Contact Us
  • Block Adblock
  • Favorites
  • Ubuntu Releases
  • Linux Mint Releases