MySQL command basics

This chapter shows how to

There's more on the MySQL CLI in Appendix B: MySQL command line cheat sheet !-- https://dev.mysql.com/doc/en/select.html -- <!-- CREATE TABLE IF NOT EXISTS todo ( id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100) NOT NULL DEFAULT '', description TEXT, priority ENUM('Low', 'Medium', 'High') NOT NULL );

INSERT INTO todo VALUES (0, 'Update About page','Wrong font','Low');

INSERT INTO todo VALUES (0, 'Put password in env','gulp!','High'), (0, 'Check indexes','Drop some?','Low'), (0, 'Add site map','See Jordan','Medium') ; -->

Creating a database and admin user

What you might think of as a database (for example, a list of items in a todo list) is actually called a table. If you think about it, you probably want other tables, for example, a list of users who create those todo items, or a list of projects those todo items are attached to.

These tables are grouped into a database. In turn, you can have multiple databases.

Before you create tables, you have to create a database. After that you'll create a separate user to administer that database with its own privileges to limit damage by malicious users. Here's how to do create the database:

mysql> CREATE DATABASE IF NOT EXISTS TODO;
# Or if you're feeling brave:
mysql> CREATE DATABASE TODO;

If that worked, you get this reply:

Query OK, 1 row affected (0.07 sec)

Next, you should immediately create an administrative user for the database instead of relying on the default root user, which has too much power. It's a standard security practice.

Creating a database administration role

It's good form to avoid creating root user for a database application. For example, in a larger team it's likely the person maintaining the database isn't going to be the programmer. Even if it's all just you, it's smart to separate roles. 

# Replace todoadmin with your desired
# database administrator name.

# Replace admin9999- with your desired
# password.
mysql> CREATE USER 'todoadmin'@'%' IDENTIFIED BY 'admin9999-';
# Give all privileges to user named todoadmin on any
# domain, but only on the 'todo' database.
mysql> GRANT ALL ON todo.* TO 'todoadmin'@'%';
mysql> FLUSH PRIVILEGES;

Get the current list of databases

Here's how to find out what databases MySQL knows about. Your mileage will vary:

mysql> show databases;

A list of databases will appear:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sampledatabase     |
+--------------------+
4 rows in set (0.08 sec)

Choosing a database to work on with USE

Let's find out how to learn what tables are in a database. First you must select the database you want to examine. The list looks like this. 

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

It may seem slightly confusing that one of them is named mysql but it's just a regular database, albeit one with many mission-critical tables.

  • Enter USE mysql; at the prompt:
mysql> USE mysql;

Getting a list of all tables within a database with SHOW TABLES

Let's see what tables are contained within the database we have chosen to use, which in this case is the one named mysql:

mysql> SHOW TABLES;

The result might surprise you:

| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

Create a table in the database using the mysql CLI CREATE TABLE statement

Suppose you want a project to track todo items. A simplified version of it could be records with an integer ID for a unique identifier, a short title, a longer description, and a priority level. Here's how you could define the table.

  • First, make sure you start by choosing a database to hold the table:
USE sampledatabase;
  • Now define the table:
mysql> CREATE TABLE IF NOT EXISTS `todo` (
`id` MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
`title` VARCHAR(100) NOT NULL DEFAULT '',
`description` TEXT,
`priority` ENUM('Low', 'Medium', 'High') NOT NULL
);

On success you're notified like this:

Query OK, 0 rows affected (0.07 sec)

Add (insert) a record using the mysql CLI INSERT statement

Now let's add a record (sometimes called a row) to the table:

mysql> INSERT INTO todo VALUES
(0, 'Update About page','Wrong font','Low');

Note how autoincrement fields like id can be represented by a 0.

Insert multiple records using the mysql CLI INSERT statement

You can also add (insert) records in bulk:

mysql> INSERT INTO todo VALUES
(0, 'Put password in env','gulp!','High'),
(0, 'Check indexes','Drop some?','Low'),
(0, 'Add site map','See Jordan','Medium')
;

Query/search the database using the mysql CLI SELECT statement

Let's see how to search for records in the database. The easiest is using the * wildcard operator.

Select all records in the table

  • To list all records in the table, use SELECT * FROM:
mysql> SELECT * FROM TODO;

Resulting in:

+----+---------------------+-------------+----------+
| id | title               | description | priority |
+----+---------------------+-------------+----------+
|  1 | Update About page   | Wrong font  | Low      |
|  2 | Put password in env | gulp!       | High     |
|  3 | Check indexes       | Drop some?  | Low      |
|  4 | Add site map        | See Jordan  | Medium   |
+----+---------------------+-------------+----------+
4 rows in set (0.00 sec)

Select records according to a condition with a WHERE clause

  • To obtain only low-priority records, use a WHERE clause:
SELECT * FROM TODO WHERE priority = 'Low';

Resulting in:

+----+-------------------+-------------+----------+
| id | title             | description | priority |
+----+-------------------+-------------+----------+
|  1 | Update About page | Wrong font  | Low      |
|  3 | Check indexes     | Drop some?  | Low      |
+----+-------------------+-------------+----------+
2 rows in set (0.00 sec)

Select records according to a condition using a WHERE clause with multiple conditions

  • Let's find out how to get records that satisfy 2 conditions: low priority and contains the word fonts.
mysql> SELECT * FROM todo WHERE
  description LIKE '%font%' AND 
  priority = 'Low';

The only matching record is returned:

+----+-------------------+-------------+----------+
| id | title             | description | priority |
+----+-------------------+-------------+----------+
|  1 | Update About page | Wrong font  | Low      |
+----+-------------------+-------------+----------+
1 row in set (0.00 sec)

For more on the SELECT statement

That's only a superficial mention of the powers of SELECT. Here's where to learn more.

MySQL SELECT Syntax

Delete records from a table using the mysql CLI DELETE FROM statement

To delete records from the database, you need to use the retrieval you've just acquired in the WHERE clause:

mysql> DELETE FROM todo WHERE priority = 'Low';

Let's see if it worked:

mysql> SELECT * FROM todo;
+----+---------------------+-------------+----------+
| id | title               | description | priority |
+----+---------------------+-------------+----------+
|  2 | Put password in env | gulp!       | High     |
|  4 | Add site map        | See Jordan  | Medium   |
+----+---------------------+-------------+----------+
2 rows in set (0.01 sec)

results matching ""

    No results matching ""