• Choose a category
• All
• Classic-Fiction

Analysis

DESCRIBE requires that a table name be specified (customers in this example), and returns a row for each field containing the field name, its datatype, whether NULL is allowed, key information, default value, and extra information (such as auto_increment for field cust_id).

* * *

Note: What Is Auto Increment?

Some table columns need unique values. For example, order numbers, employee IDs, or (as in the example just seen) customer IDs. Rather than have to assign unique values manually each time a row is added (and having to keep track of what value was last used), MariaDB can automatically assign the next available number for you each time a row is added to a table. This functionality is known as auto increment. If it is needed, it must be part of the table definition used when the table is created using the CREATE statement. We look at CREATE in Chapter 21, “Creating and Manipulating Tables.”

* * *

* * *

Tip: The SHOW COLUMNS FROM Statement

DESCRIBE is actually a shortcut for SHOW COLUMNS FROM. In other words, the statement DESCRIBE customers; is functionally identical to the statement SHOW COLUMNS FROM customers;.

* * *

Other SHOW statements are supported too, including

• SHOW STATUS—Used to display extensive server status information

• SHOW CREATE DATABASE and SHOW CREATE TABLE—Used to display the MariaDB statements used to create specified databases or tables respectively

• SHOW GRANTS—Used to display security rights granted to users (all users or a specific user)

• SHOW ERRORS and SHOW WARNINGS—Used to display server error or warning messages

It is worthwhile to note that client applications use these same MariaDB SQL commands as you’ve seen here. Applications that display interactive lists of databases and tables, that allow for the interactive creation and editing of tables, that facilitate data entry and editing, or that allow for user account and rights management, and more, all accomplish what they do using the same MariaDB SQL commands that you can execute directly yourself.

* * *

Tip: Learning More About SHOW

In the mysql command line utility, execute command HELP SHOW; to display a list of allowed SHOW statements.

* * *

* * *

Note: Want Even More Information?

MariaDB supports the use of INFORMATION_SCHEMA to obtain and filter even more schema details. Coverage of INFORMATION_SCHEMA is beyond the scope of this book. But, if you should need it, know that it’s there for you.

* * *

Summary

In this chapter, you learned how to connect and log in to MariaDB; how to select databases using USE; and how to introspect MariaDB databases, tables, and internals using SHOW and DESCRIBE. Armed with this knowledge, you can now dig into the all-important SELECT statement.

4. Retrieving Data

In this chapter, you learn how to use the SELECT statement to retrieve one or more columns of data from a table.

The SELECT Statement

As explained in Chapter 1, “Understanding SQL,” SQL statements are made up of plain English terms called keywords. Every SQL statement is made up of one or more keywords. The SQL statement you’ll probably use most frequently is the SELECT statement. Its purpose is to retrieve information from one or more tables.

To use SELECT to retrieve table data you must, at a minimum, specify two pieces of information—what you want to select, and from where you want to select it.

Retrieving Individual Columns

We start with a simple SQL SELECT statement, as follows:

Input

SELECT prod_name

FROM products;

Analysis

The previous statement uses the SELECT statement to retrieve a single column called prod_name from the products table. The desired column name is specified right after the SELECT keyword, and the FROM keyword specifies the name of the table from which to retrieve the data. The output from this statement is shown in the following:

Output

+----------------+

| prod_name |

+----------------+

| .5 ton anvil |

| 1 ton anvil |

| 2 ton anvil |

| Oil can |

| Fuses |

| Sling |

| TNT (1 stick) |

| TNT (5 sticks) |

|