Normally, newbie like me will often use tools such as workbench, PhpMyadmin, … to manipulate the database. But for the linux operating system has supported an easier, simpler, faster and cooler way to do it. I will show you the commands to be able to use mysql in the terminal. To access the database from the terminal, use the following command
1 2 | $ mysql -u root -p |
If you type correctly, the terminal will force you to enter mk of mysql and the result if you enter correctly is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.31-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> |
To change the mk of root user use the following command:
1 2 | ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; |
To exit mysql we use the command
1 2 | mysql> quit |
To show all the database in mysql we use
1 2 | SHOW DATABASES; |
The result looks like this
1 2 3 4 5 6 7 8 9 10 | +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) |
To create the database
1 2 3 | mysql> CREATE DATABASE pets; Query OK, 1 row affected (0.01 sec) |
To create a table inside the database, we first have to choose which database to use
1 2 3 | mysql> USE pets Database changed |
To create a new table
1 2 3 4 5 6 7 8 9 | CREATE TABLE cats ( id INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record name VARCHAR(150) NOT NULL, # Name of the cat owner VARCHAR(150) NOT NULL, # Owner of the cat birth DATE NOT NULL, # Birthday of the cat PRIMARY KEY (id) # Make the id the primary key ); |
To see all the tables in the database
1 2 3 4 5 6 7 8 | mysql> SHOW TABLES; +----------------+ | Tables_in_pets | +----------------+ | cats | +----------------+ 1 row in set (0.00 sec) |
To see the information of the columns in that table
1 2 3 4 5 6 7 8 9 10 11 | mysql> DESCRIBE cats; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(150) | NO | | NULL | | | owner | varchar(150) | NO | | NULL | | | birth | date | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) |
Add a record to the table
1 2 3 4 5 | INSERT INTO cats ( name, owner, birth) VALUES ( 'Sandy', 'Lennon', '2015-01-03' ), ( 'Cookie', 'Casey', '2013-11-13' ), ( 'Charlie', 'River', '2016-05-21' ); |
See the records in the table
1 2 3 4 5 6 7 8 9 | +----+---------+--------+------------+ | id | name | owner | birth | +----+---------+--------+------------+ | 1 | Sandy | Lennon | 2015-01-03 | | 2 | Cookie | Casey | 2013-11-13 | | 3 | Charlie | River | 2016-05-21 | +----+---------+--------+------------+ 3 rows in set (0.00 sec) |
Clear goals in the table
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> DELETE FROM cats WHERE name='Cookie'; Query OK, 1 row affected (0.05 sec) mysql> SELECT * FROM cats; +----+---------+--------+------------+ | id | name | owner | birth | +----+---------+--------+------------+ | 1 | Sandy | Lennon | 2015-01-03 | | 3 | Charlie | River | 2016-05-21 | +----+---------+--------+------------+ 2 rows in set (0.00 sec) |
Add a column to the table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> ALTER TABLE cats ADD gender CHAR(1) AFTER name; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE cats; +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(150) | NO | | NULL | | | gender | char(1) | YES | | NULL | | | owner | varchar(150) | NO | | NULL | | | birth | date | NO | | NULL | | +--------+------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) |
Delete columns in the table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> ALTER TABLE cats DROP gender; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE cats; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(150) | NO | | NULL | | | owner | varchar(150) | NO | | NULL | | | birth | date | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) |
So I have introduced to you one more way to be able to manipulate databases.