This blog provides a query language type overview.
The idea for this blog originated from some customers asking me questions. When working in a particular field, you often a dedicated vocabulary that makes sense to your peers. It often includes phrases and abbreviations because it’s efficient. It’s no different in the database world. Much of this language might make sense to DBA’s, but it might sound like “voodoo” to people not used to it. The overview below covers the basic types of query languages inside SQL. I hope it clarifies what they mean, how they’re used and how you should interpret them.
DDL (Data Definition Language)
A database schema is a visualization of information. It contains the data structure separated by tables structures, views and anything that contains structure for your data. It defines how you want to store and visualize the information.
It’s like a skeleton, defining how data is organized. Any action that creates/updates/changes this skeleton is DDL.
Do you remember spreadsheets? A table definition describes something like:
| Account number | Account name | Account owner | Creation date | Amount |
| Sorted ascending | Unique, indexed | Date, indexed | Number, linked with transactions |
Whenever you want to create a table like this, you must use a DDL query. For example:
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE Accounts ( Account_number Bigint(16) , Account_name varchar(255), Account_name varchar(255), Creation_date date, Amount Bigint(16), PRIMARY KEY (Account_number), UNIQUE(Account_name), FOREIGN KEY (Amount) REFERENCES transactions(Balancevalue) ); |
CREATE, ALTER, DROP, etc.: all of these types of structure modification queries are DDL queries!
Defining the structure of the tables is important as this defines how you would potentially access the information stored in the database while also defining how you might visualize it.
Why should you care that much?
DDL queries define the structure on which you develop your application. Your structure will also define how the database server searches for information in a table, and how it is linked to other tables (using foreign keys, for example).
You must design your MySQL schema before adding information to it (unlike NoSQL solutions such as MongoDB). MySQL might be more rigid in this manner, but it often makes sense to design the pattern for how you want to store your information and query it properly.
Due to the rigidity of an RDBMS system, changing the data structure (or table schema) requires the system to rebuild the actual table in most cases. This is potentially problematic for performance or table availability (locking). Often this is a “hot” procedure (since MySQL 5.6), requiring no downtime for active operations. Additionally, tools like pt-osc or other open source solutions can be used for migrating the data structure to a new format without requiring downtime.
An example:
1 | ALTER TABLE accounts ADD COLUMN wienietwegisisgezien varchar(20) |
DML (Data Manipulation Language)
Data manipulation is what it sounds like: working with information inside a structure. Inserting information and deleting information (adding rows, deleting rows) are examples of data manipulation.
An example:
1 2 3 | INSERT into resto_visitor values(5,'Julian',’highway 5’,12); UPDATE resto_visitor set name='Evelyn',age=17 where id=103; |
Sure, but why should I use it?
Having a database environment makes no sense unless you insert and fetch information out of it. Remember that databases are plentiful in the world: whenever you click on a link on your favorite blog website, it probably means you are fetching information out of a database (and that data was at one time inserted or modified).
Interacting with a database requires that you write DML queries.
DCL (Data Control Language)
Data control language is anything that is used for administrating access to the database content. For example, GRANT queries:
1 | GRANT ALL PRIVILEGES ON database.table to ‘jeffbridges’@’ourserver’; |
Well that’s all fine, but why another subset “language” in SQL?
As a user of database environments, at some point you’ll get access permission from someone performing a DCL query. Data control language is used to define authorization rules for accessing the data structures (tables, views, variables, etc.) inside MySQL.
TCL (Transaction Control Language) Queries
Transaction control language queries are used to control transactional processing in a database. What do we mean by transactional processes? Transactional processes are typically bundled DML queries. For example:
1 2 3 4 5 6 7 8 9 | BEGIN FETCH INFORMATION OF TABLE B INSERT DATA INTO A REMOVE STALE DATA FROM B COMMIT or ROLLBACK |
This gives you the ability to perform or rollback a complete action. Only storage engines offering transaction support (like InnoDB) can work with TCL.
Yet another term, but why?
Ever wanted to combine information and perform it as one transaction? In some circumstances, for example, it makes sense to make sure you perform an insert first and then perform an update. If you don’t use transactions, the insert might fail and the associated update might be an invalid entry. Transactions make sure that either the complete transaction (a group of DML queries) takes place, or it’s completely rolled back (this is also referred to as atomicity).
Conclusion
Hopefully this blog post helps you understand some of the “insider” database speech. Post comments below.
