List tables from a mysql database

List tables from a mysql database

Let us select the database which we are going to use it.

mysql> use shivpedia;

Output:

Database changed

list the tables already available in database 'shivpedia'.

 mysql> show tables; 

Output:

+---------------------+
| Tables_in_shivpedia |
+---------------------+
| tbwildlife          |
+---------------------+
1 row in set (0.00 sec)
    

Let us fetch the records in the table 'tbwildlife'.

  mysql> select * from tbwildlife;  

Output:

+----+------------+------------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
| id | date_today | animal     | title                                 | short_desc                            | desc                                  | url                                       |
+----+------------+------------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
|  1 | 2019-11-03 | Rhinoceros | Rhinoceros eating grasses             | Rhinoceros eating grasses!!!          | Rhinoceros eating grasses!!!          | https://www.youtube.com/embed/LdKNQ0cTvo0 |
|  2 | 2019-11-03 | Bear       | Bear climbing up and down on tree !!! | Bear climbing up and down on tree !!! | Bear climbing up and down on tree !!! | https://www.youtube.com/embed/LdKNQ0cTvo0 |
+----+------------+------------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
2 rows in set (0.04 sec)
    

Filter mysql database table rows based on condition using where clause

Let us see how to the required rows in a table using filter condition in select query in mysql database.

Here select the rows which are havinng column animal as 'Bear' from tbwildlife table

mysql> select * from tbwildlife where animal='Bear';

Output:

+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
| id | date_today | animal | title                                 | short_desc                            | desc                                  | url                                       |
+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
|  2 | 2019-11-03 | Bear   | Bear climbing up and down on tree !!! | Bear climbing up and down on tree !!! | Bear climbing up and down on tree !!! | https://www.youtube.com/embed/LdKNQ0cTvo0 |
+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

Filter rows based on column having matches only prefix string

mysql> select * from tbwildlife where title like "Bear%";

Output:

+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
| id | date_today | animal | title                                 | short_desc                            | desc                                  | url                                       |
+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
|  2 | 2019-11-03 | Bear   | Bear climbing up and down on tree !!! | Bear climbing up and down on tree !!! | Bear climbing up and down on tree !!! | https://www.youtube.com/embed/LdKNQ0cTvo0 |
+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

Filter rows based on column having matches both prefix and suffix string

mysql> select * from tbwildlife where title like "%climbing%";

Output:

+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
| id | date_today | animal | title                                 | short_desc                            | desc                                  | url                                       |
+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
|  2 | 2019-11-03 | Bear   | Bear climbing up and down on tree !!! | Bear climbing up and down on tree !!! | Bear climbing up and down on tree !!! | https://www.youtube.com/embed/LdKNQ0cTvo0 |
+----+------------+--------+---------------------------------------+---------------------------------------+---------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

« Previous Next »