MariaDB CREATE TABLE

Contents hide
1 MariaDB database login
2 SHOW DATABASES
3 USE
4 QUIT
5 CREATE TABLE
5.1 Create a table separating columns manually
5.2 Create a table using one long command
6 SHOW TABLES
7 DESCRIBE TABLE
8 INSERT INTO
8.1 Insert data separating columns manually
8.2 Insert data using one long command
9 SELECT
10 DROP TABLE
11 SHOW TABLES (empty)
12 Custom Query example
13 Where to go next?

MariaDB database login

[juan@pc ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

SHOW DATABASES

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbtest             |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

MariaDB [(none)]>

USE

MariaDB [(none)]> USE dbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [dbtest]>

QUIT

MariaDB [dbtest]> QUIT
Bye
[juan@pc ~]$

CREATE TABLE

Create a table separating columns manually

MariaDB [dbtest]> CREATE TABLE tabletest (
    -> user_id int,
    -> first_name varchar(20),
    -> last_name varchar(30),
    -> email varchar(50),
    -> facebook_url varchar(90),
    -> twitter_handle varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [dbtest]>

Create a table using one long command

MariaDB [dbtest]> CREATE TABLE tabletest ( user_id int, first_name varchar(20), last_name varchar(30), email varchar(50), facebook_url varchar(100), twitter_handle varchar(20) ); 
Query OK, 0 rows affected (0.02 sec)

SHOW TABLES

MariaDB [dbtest]> SHOW TABLES;
+------------------+
| Tables_in_dbtest |
+------------------+
| tabletest        |
+------------------+
1 row in set (0.00 sec)

MariaDB [dbtest]>

DESCRIBE TABLE 

]> DESCRIBE tabletest;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| user_id        | int(11)      | YES  |     | NULL    |       |
| first_name     | varchar(20)  | YES  |     | NULL    |       |
| last_name      | varchar(30)  | YES  |     | NULL    |       |
| email          | varchar(50)  | YES  |     | NULL    |       |
| facebook_url   | varchar(100) | YES  |     | NULL    |       |
| twitter_handle | varchar(20)  | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MariaDB [dbtest]>

INSERT INTO

Insert data separating columns manually

MariaDB [dbtest]> INSERT INTO tabletest VALUES (1,
    -> "Pepe",
    -> "Argento",
    -> "pepe@argento.com",
    -> "facebook.com/pepearg",
    -> "@pepearg");
Query OK, 1 row affected (0.00 sec)

MariaDB [dbtest]>

Insert data using one long command

MariaDB [dbtest]> INSERT INTO tabletest VALUES (1,"Pepe","Argento","pepe@argento.com","facebook.com/pepearg","@pepearg");
Query OK, 1 row affected (0.01 sec)

MariaDB [dbtest]>

SELECT

MariaDB [dbtest]> SELECT first_name, last_name, email FROM tabletest;
+------------+-----------+------------------+
| first_name | last_name | email            |
+------------+-----------+------------------+
| Pepe       | Argento   | pepe@argento.com |
+------------+-----------+------------------+
1 row in set (0.00 sec)

MariaDB [dbtest]>

DROP TABLE

MariaDB [dbtest]> DROP TABLE tabletest;
Query OK, 0 rows affected (0.00 sec)

MariaDB [dbtest]>

SHOW TABLES (empty)

MariaDB [dbtest]> SHOW TABLES;
Empty set (0.00 sec)

MariaDB [dbtest]>

Custom Query example

MariaDB [dbtest]> SELECT facebook_url
    -> FROM tabletest
    -> WHERE first_name = 'Pepe';
+----------------------+
| facebook_url         |
+----------------------+
| facebook.com/pepearg |
+----------------------+
1 row in set (0.00 sec)

MariaDB [dbtest]>

Where to go next?

We’ve just scratched the surface when it comes to working with MySQL, our intention was never to go deep, in fact we’re going to use the GUI (Graphical User Interface) provided by the tool called phpMyAdmin.