How to create table and columns inside a database?

[Gaurav]

I have created a database named “abc” and now i wanna manually create table and column inside it. How ? My program has connected to database but now i wanna create table here so i can save and update queries.

[aiven_angie]

Hi there, @Gaurav! Welcome to the Aiven community!

Once your database connection is working properly, any MySQL tutorial out there should do the trick! For example, when I was learning MySQL, I found the exercises at W3Schools to be super helpful, as they give you a dynamic environment to check your understanding of the work as you go.

But at any rate, here are a few notes to get you started!

Connecting to the Database

(Sounds like you already have this figured out, but just in case!)

1. In the Aiven Console, select Services > your MySQL database. This will give you a bunch of info like your database name, host name, port, and so on.
2. If you click the “Quick connect” button in the upper-right of that info, you’ll get a nifty screen that has some copy/paste commands you can use to quickly verify your connection:

3. You’ll notice the password is all ***ed out, to protect you in case of dangerous shoulder-surfers. To view it, you can click the “eyeball” icon to temporarily un-hide it, then copy/paste that command into your terminal.
4. If all is working, you should see a mysql> prompt, which means you’re all set to type some SQL!

However, in your case if you already created a database abc and wanted to use it rather than the default, you’d connect to MySQL as above, and then do:

USE abc;

And then you could find out what other tables already exist in that database (if any) with:

SHOW TABLES;

Creating a table

For this, you need to use a CREATE TABLE statement, and to do that you first need to have some idea of what data you want to store, since columns can be a lot of different data types (numeric, text, etc.)

But for a simple example, let’s say you want a table that stores your video game collection, and you want columns for the video games’ name (text) and your highest score (number).

You would do that like the following:

CREATE TABLE video_games (
  id INT AUTO_INCREMENT,
  title VARCHAR(255),
  high_score DECIMAL(5,2),
  PRIMARY KEY(id)
);

Woah, what is all that about?! Let’s break it down:

• id: We didn’t ask for this, but it’s very common for database rows to have something unique about them so that they can be referred back to by other tables’ data (as one example, let’s say later on you wanted to create a table that stored “collections” of one or more video games). So, we add an integer (INT) column and with AUTO_INCREMENT we tell it to automatically increase this number by 1 every time a new row is inserted.
• title: This is the video game’s name, and stores text (string) data. There are several options here, such as CHAR (useful for things like State/Province abbreviations where you know upfront how many letters there will be) or TEXT if you want to store big ol’ paragraphs of data, but we’re going with VARCHAR here because we don’t really know how many letters there will be, but it’d be unusual for one to exceed 255 in length.
• high_score: This one is also numeric, and this time uses a DECIMAL as a completely arbitrary decision to show you how to use different column types. The 5, 2 refers to this being a number up to 5 digits in length, with 2 of those digits reserved for after the decimal place (so, a number like 103.54)
• PRIMARY KEY: Remember that bit above about us creating the id field so other tables could reference rows in this table? This line specifies a Primary Key Optimization which gives MySQL a heads up when trying to find rows to use this field as the quickest way to reference which game is which.

If everything worked properly, you should see Query OK like the following:

mysql> CREATE TABLE video_games (
    ->   id INT AUTO_INCREMENT,
    ->   title VARCHAR(255),
    ->   high_score DECIMAL(5,2),
    ->   PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.36 sec)

If not, check for misspellings, missing commas or semi-colons, mis-matched parentheses, and this sort of thing.

Populating a table

So… it’s all well and good to have a shiny new table, but it’s really not much use without having something in it. For this, you use an INSERT statement, like so:

INSERT INTO video_games (title, high_score) VALUES ('The Fantastic Adventures Of Crabby', 123.45);

This says to put inside the video_games table the title of ‘The Fantastic Adventures Of Crabby’ and high_score of 123.45.

Go ahead and add a few more (feel free to get creative).

If all goes well, it should look something like this:

mysql> INSERT INTO video_games (title, high_score) VALUES ('The Fantastic Adventures Of Crabby', 123.45);

Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO video_games (title, high_score) VALUES ('Crabby II: Return Of The Carapace', 100.00);

Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO video_games (title, high_score) VALUES ('Crabtastic Crustaceans: Revenge', -1.23);

Query OK, 1 row affected (0.16 sec)

Ok cool, it says Query OK, but how do we actually know it worked? I’m so glad you asked!

Reading data from a table

Let’s verify our table has what we think it ought with a well-placed SELECT statement:

SELECT * FROM video_games;

This just says “grab me all of everything from the video_games table” and ends up looking something like this:

mysql> SELECT * FROM video_games;
+----+------------------------------------+------------+
| id | title                              | high_score |
+----+------------------------------------+------------+
|  1 | The Fantastic Adventures Of Crabby |     123.45 |
|  2 | Crabby II: Return Of The Carapace  |     100.00 |
|  3 | Crabtastic Crustaceans: Revenge    |      -1.23 |
+----+------------------------------------+------------+
3 rows in set (0.07 sec)

But that’s only scratching the surface; you can get super powerful with your SELECT statements by filtering records out (WHERE clause), JOINing the table data up with others, etc.

Here’s a slightly fancier example which only shows the titles of the video games if your high score was positive:

SELECT title FROM video_games WHERE high_score > 0;
+------------------------------------+
| title                              |
+------------------------------------+
| The Fantastic Adventures Of Crabby |
| Crabby II: Return Of The Carapace  |
+------------------------------------+
2 rows in set (0.09 sec)

Hope that helps!

This is just scratching the surface, there are ALL kinds of fancy things you can do with SQL (entire books and courses have been written on this ), but hopefully that’s enough to get you off to the races!

One last note: If the command line freaks you out, there are also a number of GUI tools for doing this work. I’m a big fan of Sequel Pro on the Mac, for example.