If you are a developer working in a MySQL environment, this blog post is for you. I share 3 MySQL commands or statements that you should know. That is a bold statement, I know. Turns out, once you do know (of) these commands, you will use them all the time. They minimize guesswork which leads to better productivity in other facets of your programming and querying workflow. I use them myself almost daily and am sure you will too once you see how simple they are to use. So why should you know them? Continue reading and find out…
OS, Software, and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.20
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Note: these commands/statements are listed in no particular order.
DESC table_name
If for no other reason than knowing the names of which columns to query and their respective datatypes, you must know DESC table_name.
What columns and their respective datatypes are in the ‘actor’ table?
Use DESC for this information:
1 2 3 4 5 6 7 8 9 10 | MySQL localhost:33060+ ssl sakila SQL > DESC actor; +-------------+----------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+-------------------+-----------------------------------------------+ | actor_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | MUL | NULL | | | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +-------------+----------------------+------+-----+-------------------+-----------------------------------------------+ 4 rows in set (0.0028 sec) |
(Tip: DESC is an abbreviation for DESCRIBE, which works just the same. In addition to DESC, you may find the similar SHOW COLUMNS statement useful as well. Read about SHOW COLUMNS online if interested.)
DESC table_name also reveals those columns which allow NULL values, the PRIMARY KEY column – if implemented – and many other column attributes (i.e., DEFAULT values, CHECK constraints, AUTO_INCREMENT, etc…).
If you ever find yourself working in a terminal/command-line type of environment, then you will definitely want DESC table_name in your toolbox.
Visit the official documentation on DESCRIBE for more information.
SHOW CREATE TABLE
What are the indexes – if any – on this table? Are there any FOREIGN KEY‘s defined? Any CHECK constraint’s here?
Guessing and/or blind querying will get you nowhere. Instead, get answers to all of these types of questions and more with a SHOW CREATE TABLE statement.
Here are a couple of examples:
1 2 3 4 5 6 7 8 9 10 11 12 | MySQL localhost:33060+ ssl sakila SQL > SHOW CREATE TABLE actor\G *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 1 row in set (0.0007 sec) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | MySQL localhost:33060+ ssl sakila SQL > SHOW CREATE TABLE film\G *************************** 1. row *************************** Table: film Create Table: CREATE TABLE `film` ( `film_id` smallint unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `description` text, `release_year` year DEFAULT NULL, `language_id` tinyint unsigned NOT NULL, `original_language_id` tinyint unsigned DEFAULT NULL, `rental_duration` tinyint unsigned NOT NULL DEFAULT '3', `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99', `length` smallint unsigned DEFAULT NULL, `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99', `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G', `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`film_id`), KEY `idx_title` (`title`), KEY `idx_fk_language_id` (`language_id`), KEY `idx_fk_original_language_id` (`original_language_id`), CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 1 row in set (0.0869 sec) |
Look at all of that information! Column names, datatypes, column attributes, AUTO_INCREMENT attribute values; it is all there. No more guessing. You can clearly see those FOREIGN KEY‘s you were scratching your head over as you considered your JOIN queries.
(Tip: You can also run SHOW CREATE against a VIEW so long as you have privileges on the VIEW of course.)
Visit the SHOW CREATE TABLE official documentation for more information.
SELECT COUNT(*) or SELECT COUNT(<em>column_name</em>)
As a developer working with data, how many times have you wondered how many rows of data a table has? If you are anything like me, quite often. How can you find out? Of course, with a simple SELECT COUNT(*) type of query.
How many rows are in the ‘category’ table?
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl sakila SQL > SELECT COUNT(*) FROM category; +----------+ | COUNT(*) | +----------+ | 16 | +----------+ 1 row in set (0.0382 sec) |
While retrieving a row count is useful, perhaps you need to know how many of a certain something exists in a given table. For example, how many occurrences of each of the ‘staff_id’ column values are present in the ‘rental’ table?
A COUNT(<em>column_name</em>) query – with GROUP BY – answers that easily:
1 2 3 4 5 6 7 8 9 10 | MySQL localhost:33060+ ssl sakila SQL > SELECT staff_id, COUNT(staff_id) -> FROM rental -> GROUP BY staff_id; +----------+-----------------+ | staff_id | COUNT(staff_id) | +----------+-----------------+ | 1 | 8040 | | 2 | 8004 | +----------+-----------------+ 2 rows in set (0.0099 sec) |
(Tip: There is a difference between a COUNT(*) and a COUNT(<em>column_name</em>) query. The COUNT(*) variant counts all rows, however, COUNT(<em>column_name</em>) only counts occurrences of non-null values.)
This list is by no means, exclusive, nor a de facto MySQL commands for developer’s ultimate list. That being said, which are those commands you cannot live without and feel should be added to this list? I would love to know of them so please post them in the comments and thank you for reading!
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.