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 SELECT ALL
14 Where to go next?

MariaDB database login

mysql -u root -p
[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

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

MariaDB [(none)]>

USE

USE dbtest;
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

\q
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)
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) );

SHOW TABLES

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

MariaDB [dbtest]>

DESCRIBE TABLE 

DESCRIBE tabletest;
]> 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",
    -> "fb.com/pepearg",
    -> "@pepearg");
Query OK, 1 row affected (0.00 sec)

MariaDB [dbtest]> 
INSERT INTO tabletest VALUES (1,"Pepe","Argento","pepe@argento.com","fb.com/pepearg","@pepearg");

INSERT INTO tabletest VALUES (2,"John","Doe","john@example.com","fb.com/john","@johndoe");

INSERT INTO tabletest VALUES (3,"Alice","Smith","alice@example.com","fb.com/alice","@alice_s");

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

SELECT first_name, last_name, email FROM tabletest;
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

DROP TABLE tabletest;
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

SELECT facebook_url FROM tabletest WHERE first_name = 'Pepe';
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]>

SELECT ALL

SELECT * FROM tabletest;

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.