If you’re dealing with databases, it’s only a matter of time before you come across the term DDL, which is an abbreviation of Data Definition Language, three words capable of striking fear in the heart of almost anyone without a computer science degree.
Don’t worry, though, it’s nothing to be scared of. In fact, the sole purpose of DDL is to make your life easier.
Using this nifty database tool, you can make crucial changes to your database objects in a matter of seconds — sounds pretty good, right?
So, let’s dig a little deeper into DDL and set out in plain English exactly what it is!
Terminology Surrounding DDL
The problem people face when trying to understand DDL is that it’s surrounded by other forms of impenetrable techie jargon, so let’s address those first.
Database
Let’s start simple. DDL is used in database management. A database is simply any digital location that holds multiple pieces of data in a logical and easy-to-understand manner.
Databases are made up of database objects.
Database Objects
Database objects are any structures used within the database as a whole to store and display data.
For example, the most common and simple database object is a table. Another example of a database object would be an index or sequence. Make sense?
It may help to think of databases, database objects, and data as a grocery store…
The database is the building.
Once you arrive at and enter the building, the database objects are like the shelves, fridges, and freezers, and the data is like the produce held in or on all these modes of presentation — simple, huh?
SQL
Another scary abbreviation, SQL stands for Structured Query Language, and it’s essentially an umbrella term for database languages DDL and DML.
You can kind of think of DDL and DML as different regional dialects of the same language, that language being SQL.
… and with those definitions taken care of, we can finally dig into DDL — hooray!
Just What The Heck Is DDL?
DDL is a database programming language, and it’s used specifically for modifying and creating database objects.
Now, just to refresh your memory, database objects were the shelves, fridges, and freezers in our metaphorical grocery store.
Remember the days of My Space and furiously typing HTML to make your profile super cool?
Well, in a way, DDL is kind of like a very specific and simple form of HTML designed to be as efficient as possible in order to streamline database management.
As a subset of SQL which uses standard English as opposed to the more code-like structure of HTML, DDL is mostly composed of imperative verbs… it’s got sort of a bossy caveman vibe to it.
But that’s exactly what you need for whipping a database into shape in record time.
In some contexts, you may hear that DDL stands for Data Description Language, as it also describes the metrics of a database, i.e. what sort of data is being displayed.
Okay, But How Is DDL Different From DML?
In both subsets of SQL, people often get confused about how DDL and DML differ, and, granted, they do seem quite similar to the laymen, but in actual fact, they are very, very different. Let’s explore how…
Heading back to our metaphorical grocery store, we have the shelves (database objects), and we have the produce on the shelves (data).
DDL is the tool we’d use to add more shelves, take shelves away, or modify existing shelves. DML (Data Modification Language), on the other hand, would be used to modify the produce sitting on the shelves.
DML can be used to alter large amounts of data in one fell swoop, saving some poor souls from having to edit each individual piece of data manually.
DDL is used to structure said data efficiently, so the same poor soul doesn’t have to create all the database objects themselves on a spreadsheet.
Examples Of DDL
Possibly the best way to come to terms with DDL is to take a look at a few commonly used examples. The ones we’ll discuss briefly today include:
- Create
- Alter
- Drop
- Truncate
Create
The “Create” command is used to create a new object. Within this command is pre-defined, implied syntax, which is a fancy way of saying that you have to structure the command in the same order every time you use it.
The Create syntax is as follows:
- Create [type of object] [name of object] ([columns defined]) [table parameters];
The semicolon on the end there is basically a period. It completes and triggers the processing of all the information within the command.
Alter
“Alter” commands are used to, you’ve guessed it… alter database objects. It can add columns to a table, take columns away from a table, and can even change the data types of columns.
Much like the Create command, Alter has its own syntax, which is as follows:
- Alter [type of object] [name of object] [object parameters];
Let’s take a look at an example of this in action…
- Alter Table would be the object type Employee ADD would be the name of the object PRIMARY KEY (employee_pk); would be the object parameters.
Drop
The “Drop” command is used to delete database objects such as tables, indexes… you name it.
It’s an incredibly helpful tool, but be warned, activating a Drop command is a point of no return; once it’s done… it’s done for good.
The whole “With great power comes great responsibility” Uncle Ben quote from Spider-man couldn’t be more pertinent here.
The pre-defined syntax for Drop commands is as follows:
Drop [type of object] [object name]
Now, let’s take a look at Drop in action…
Drop Table would be the object type Employee would be the object name.
Truncate
You’d use the “Truncate” command if you wanted to delete all the contents of a database object but save the structure of the object for use at a later date.
The Truncate command syntax is as follows:
Truncate [type of object] [name of object]
For example…
Truncate Table would be the object type Employee would be the object name.
Final Thoughts
That’s the core essentials of DDL covered. It’s not as daunting as it first seems, is it?
There are, of course, a few more commands in the DDL dictionary than we’ve discussed here today, but you’ll pick them up in no time, making you a veritable database wizard!
Here at Open Query I want to make it as easy as possible for you to learn about topics that might not be easily accessible elsewhere online. I don’t know about you, but I haven’t seen many other websites talking about the MariaDB Foundation and its updates.
That’s what makes Open Query truly unique. While others might assume everyone knows this information already automatically, I am filling my online resource to help.
- What Is Percentile Rank? - February 15, 2022
- How To Use Distinct In SQL - February 14, 2022
- How To Drop A Column In SQL - February 14, 2022