A database is more than a storage location for your data. It’s also the backend workhorse for applications and reports. DDL and SQL commands are queries that extract specific information from your databases so that you can use a data set for frontend visualizations, information for customers, or output for internal applications.
What Is a DDL Command?
Data Definition Language (DDL) offers database programmers and administrators a way to write scripts to create, change, or delete database objects. Structured Query Language (SQL) is the DDL of choice for most engines. You might have slight differences in SQL between different vendors (e.g., Microsoft SQL Server vs. MariaDB vs. Oracle), but the general structure remains the same. You have a command (e.g., CREATE or DELETE) that performs an action on a specific database object. Some commands change the structure of a table or create objects like a trigger or function.
Popular DDL Commands with Examples
When you work with any database, you’ll encounter the need to manage data objects (e.g. tables, stored procedures, or functions). The following DDL commands are common SQL statements used to manage your database objects.
Create Database Objects with DDL Command CREATE
When you first create a database, you need to create a new table. The CREATE command will create objects in a database, including tables, stored procedures, triggers, indexes, and even other databases. The following is an example of a CREATE command to add a table to a database:
CREATE TABLE Customers (
Custid int,
LastName varchar(255),
FirstName varchar(255)
);
In the example above, the table Customers is created with three columns. The columns are Custid, LastName, and FirstName. The Custid column is defined as an integer, which should be a unique value in the table.
Modify Database Objects with DDL Command ALTER
After you create a table, you might need to add a column to it as you make changes to an application. The ALTER command changes objects in a database. The following example adds an address column to the Customers table:
ALTER TABLE Customers
ADD Address varchar(255);
You can also remove a column from a table. The following example deletes the FirstName column from the Customers table:
ALTER TABLE Customers
DROP COLUMN FirstName;
Make sure that you want to remove columns before you delete them. Any data in these columns will be deleted as well.
Delete Database Objects with DDL Command DROP
The previous example used the DROP command to remove a column from a table, but you might want to remove an entire table from a database. The following example shows you how to use the DROP command to delete a table:
DROP TABLE Shippers;
Just like deletion of a table column, ensure that you want to completely remove a table from a database. Once you remove it, the data is no longer available. You would need to restore the table from backups.
Deleting Data in a Table with DDL Command TRUNCATE
Deleting the entire table object isn’t always necessary. In many cases, you only want to remove the data but leave the table structure intact. The TRUNCATE command deletes all data from a table but leaves the table object so that you can refill it with new data. The following is an example:
TRUNCATE table Customers;
In this command, the Customers table is cleared of data, but the table object still exists in the database.
Adding Comments with DDL Command COMMENT
For long SQL scripts, comments help you understand the code better when you need to go back and edit commands. Comments also help other programmers understand your code and what it does to the data and objects in a database. A single comment looks like the following:
-- adding a customer table
CREATE TABLE Customers (
Custid int,
LastName varchar(255),
FirstName varchar(255)
);
The double hyphen indicates that the rest of the content in that line should not be executed. You might need to use multiple lines to explain a SQL command, so you can use the multi-line statement shown below:
/* adding a customer table so that
we can record all new customer signups */
CREATE TABLE Customers (
Custid int,
LastName varchar(255),
FirstName varchar(255)
);
Notice the change in characters for multi-line comments. You need the opening characters (‘/*’) with the closing characters (‘*/’) to complete the comments. If you forget the closing characters, you’ll receive an error message.
DDL Commands vs. DML Commands
DDL commands change database objects, and Data Manipulation Language (DML) makes changes to data. Just like DDL, SQL is also the syntax used to make changes to data, but commands differ slightly based on the platform used.
Insert New Rows with DML Command INSERT
The INSERT command adds a record to your tables. Any INSERT command must add data that conforms to column constraints. For example, you can’t add text to a number column. The following is an example INSERT statement:
INSERT INTO Customers (FirstName, LastName, State)
VALUES (‘John’, 'Smith', 'California');
Update Existing Rows with DML Command UPDATE
At some point, record data changes. The UPDATE command makes changes to a record in a table. The following example makes a change to a customer record:
UPDATE Customers
SET LastName = 'Doe’
WHERE CustID = 1;
Note that the WHERE clause is important in an UPDATE statement. If the WHERE clause is eliminated, all table records are updated with the set changes. As you can imagine, changing all customer last names to a single value could damage your data integrity.
Delete Rows with DML Command DELETE
Occasionally, you need to remove a record from a database table. The DELETE statement removes one or several records, depending on its WHERE clause. The following example removes a customer with the ID of 1:
DELETE FROM Customers WHERE CustID = 1;
Like the UPDATE statement, the WHERE clause is critical in a DELETE command. Without a WHERE clause, you could delete all records from your tables.
Merge Data with DML Command MERGE
The MERGE command takes a source table, manipulates data based on your set of criteria, and performs an action based on results. You can use a MERGE command to insert, delete, or update records without writing separate statements. The following SQL statement inserts a new log table item if the merge statement indicates that the customer does not exist:
MERGE Customers AS TARGET
USING CustomerLog AS SOURCE
ON (TARGET.CustId = SOURCE.CustID)
WHEN NOT MATCHED BY TARGET
THEN INSERT (CustId, LogItem) VALUES (SOURCE.CustID, ‘Customer does not exist’)
Conclusion
Database development and design takes a lot of engineering time, but the DDL commands and DML statements listed here are common in most applications. After you create your SQL statements, you can edit them later, so it’s fine to experiment with them until you find the right statement to deploy into production.
