What is SQL DDL(Data Definition Language)?
DDL deals with the type of data in the database. In other words, we do all of these with DDL, what will be the relations between the tables in the database and the types of data in the table. DDL basically consists of three commands.
CREATE: Used to create objects (Table/Database etc.)
ALTER: It is used to modify objects.
DROP: Used to delete objects
It is used to create database and database objects. What are database objects?
|Table||A collection of rows with related columns|
|Constraints||It imposes restrictions on values contained in columns|
|Index||Provides fast access to data|
|View||Provides access to information from one or more tables or views|
|Stored Procedure||A set of SQL commands|
|Trigger||A set of SQL commands that are automatically executed when the user makes a change on the information (A kind of stored procedure)|
In this article, we will create databases and tables that are database objects.
CREATE DATABASE database_name
And this usage is the same for all database management systems.
CREATE DATABASE baransel
Let’s see if the database has been created.
As you can see, we have created the baransel database.
Before creating the table, we need to specify on which database you will create the table. For this;
The general usage is as follows.
We have specified on which database you will create it, now let’s create our table.
The general usage for this is as follows.
CREATE TABLE table_name(column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size))
It is used in the form.
Then let’s create the students table as an example.
CREATE TABLE students( student_id int, student_name varchar(15), student_surname varchar(20), student_average_grade float);
Let’s explain what we did, first we created a table named
students, then first we created a column named
student_id with data type
integer in this database. Then we added our other
varchar() data type columns, here
varchar(15) states that it can take up to 15 characters, you can get any data length you want.
This is how to create a table in a basic simple way. But we need to add some properties to this table, for example,
student_id must be different for each student so that I can distinguish them.
Primary Key Expression
primary key is used to make a field, i.e. a column, a unique value in a table. What does this mean if there is a student with
student_id 15 in the table, you cannot add a second student with
student_id of 15.
Let’s use it now;
CREATE TABLE students( student_id int primary key, student_name varchar(15), student_surname varchar(20), student_average_grade float);
By typing a
primary key next to the variable type, we have informed that this column is a
primary key, that is, a unique value.
Did you get an error like this
#1050 - Table 'students' already exists
Don’t worry, it gave an error because we created a table named
studentsbefore. Because table names are singular, think of them as primary. There cannot be more than one table with the same name in a database. So let’s delete our first table and run it again.
Now you have to ask me a question here; If the
student_id number is not entered, then how will we distinguish it? That is, if it fills in other fields and leaves the
student_id column blank. In this case, you cannot distinguish, for this we must make the
student_id part mandatory. Most of the time, when registering on a site or a place, you have received a warning that the username or email address cannot be blank. Here we will warn the user that the
student_id cannot be empty.Let’s do it now;
CREATE TABLE students( student_id int not null primary key, student_name varchar(15), student_surname varchar(20), student_average_grade float);
This is how we got it done. The
student_id column is now both primary and not null, so it cannot be left blank. So is there anything missing? No, it’s not over yet, we got the
student_id part, but we forgot to check something. You have come across many times today, just because you have to enter a 10-character phone number, or you will enter an exam score, but you must enter a value between
0 - 100 for the exam score. Yes, we should check that here. For this;
CREATE TABLE students( student_id int not null primary key check (student_id <100), student_name varchar(15), student_surname varchar(20), student_average_grade float);
Yes, we have stated here that our
student_id number can be max 100.
So, should it give an error only when it is greater than 100? ID number cannot be negative, then we have to check more than one situation.
CREATE TABLE students( student_id int not null primary key check (student_id <100 and student_id >0), student_name varchar(15), student_surname varchar(20), student_average_grade float);
We stated above that
student_id is primary, cannot be empty and will be less than 100 and greater than 0.
What if multiple columns have separate conditions? For example, if both columns need to be primary ? If 3 columns need to be not null? Should we write one by one?
Constraint expression is the expression we will use when we control more than one column. Let’s show it with an example.
CREATE TABLE students( student_id int, student_name varchar(15), student_surname varchar(20), student_average_grade float, class_no int, constraint p_key primary key(student_id,class_no));
Here, we specified that the
student_id columns are the
primary key at the same time. My request from you is to use the word
constraint together with the
check statement in the same way.
Finally, let’s show another expression.
In some cases, not ourselves, but the id number, etc. We will want to add the columns automatically. We will use the
identity statement for this. If you wish, you can specify that adding to the table will be automatically added to the table with the
IDENTITY(1,1) statement. This usage is used in the sense of starting from 1 and continuing at intervals.
CREATE TABLE students( student_id int not null IDENTITY(1,1), student_name varchar(15), student_surname varchar(20), student_average_grade float, class_no int, constraint p_key primary key(student_id,class_no));
This command, which is change and update, is used to update the tables. Let’s see our table first.
ALTER command takes three different parameters:
ADD: This parameter allows us to add columns to the table.
ALTER TABLE table_name ADD column_name column_property
Now let’s add a new column, for example the date column;
ALTER TABLE students ADD date datetime
Now let’s look at the final version of our table;
As you can see, we have added the
DROP: With this parameter, it allows us to delete a column from the table.
ALTER TABLE table_name DROP COLUMN column_name
Now let’s delete a column in our table, I’m deleting the
date column we just added.
ALTER TABLE students DROP date
date column was deleted.
MODIFY: Allows you to update the column specified in the table with this parameter.
ALTER TABLE table_name MODIFY COLUMN column_name column_property
Now, let’s make it
text while the data type of the
student_name column on the table is
ALTER TABLE students MODIFY COLUMN student_name text
This command is for deleting the database and tables we have created.
Common usage for deleting an existing database:
DROP DATABASE baransel
DROP TABLE students
In this way, we learned
table deletion operations.