So next up, we're going to learn about the alter table statement, which helps us change tables.
The thing is, there's a lot of ways to change a table.
We can add columns to a table.
We can remove columns from a table.
We can rename columns.
We can change the data type of a column.
We can add constraints, remove constraints.
If we go to the docs and you look at the altar table statement syntax.
This shows you all of the options for how you run it.
I think this is the most complicated statement in terms of the number of options.
It's actually very simple to use.
It just it's like a Swiss army knife.
It does everything.
So instead of there being a single command called like rename column and then drop column and change
column type and that sort of thing, they all live within alter table, which makes sense.
It's fine, but it does mean that it's easy to forget the syntax.
Sometimes you can come here and find if you're trying to add a column or add a constraint or drop a
column, drop a constraint, modify a column.
All this stuff you can do from within the alter table syntax.
And the first thing I'll show is how to add a new column to an existing table.
So we use alter table, and then the table we're trying to alter and then add column.
Although technically the column portion is optional.
If you look at the syntax, see how it's in brackets right there, ADD is required, column name is
required, but column inside of those brackets means put it in there if you want optional.
I like to put it there because it's a little clearer, but it doesn't matter.
So add some column.
What is the columns name going to be and what is its definition?
The data type, not no constraints, whatever you want afterwards.
So in this case, we're adding a column to companies called City and it's a var char 25.
Let's try an example of this.
We have our companies table at the moment and companies has a name and an address.
What if I wanted to add a phone number column so the tables are already created, right?
If we select star from companies.
From companies.
There's two rows.
Neither of them have a phone number.
So that's something we'll have to consider.
What happens when you add a column to a table that already has data?
What do you do for those rows that don't have a value?
Do you give them a default value?
Do you update them after the fact?
Do you make them null?
So what we'll start with is just a simple alter table.
Companies.
I want to add column.
Remember, column is optional there.
I'm going to call it, phone it to var char, let's say of 15.
So I'm adding it to this table.
It doesn't have a phone column.
And let's say that.
Well, let's just start like this Simple phone is a var char 15.
That's all that we say.
OC Let's try running it.
What do we see?
It looks like it worked.
Let's select star from companies.
And we now see that there is a new column called Phone and the default value for all of the columns
that were already in the or all of the rows that were already in the table is to have phone set to null.
And that's because I never said they couldn't be null.
So that's the logical choice, right?
That means the lack of a value, no phone number.
And those rows did not have a phone number.
They were created at a time there was no phone column.
And maybe that's what I want.
I mean, it really depends on what I'm trying to do, what the situation is.
Do these businesses not have a phone number at all?
Okay, then maybe null makes sense.
Do I need to go and get their phone numbers and update them manually?
Or what I could also do is when I create a column, I can say this cannot be null.
And when I do that, if I say this new column cannot be null, there will be a default value added in
for string columns.
It will be an empty string.
For number columns, it will be zero.
So let me show that.
Let's do an alter table companies.
Once again, let's add a column here, and this time the column will be called Employee Count.
And what I can do is say employee count cannot be null.
And when I run this here, add column, employee count, not null.
What is it complaining about to me?
Oh, duh.
I have to say, it's an integer.
I don't know how I forgot that.
Thank you.
SQL workbench.
Let's try running this.
When I run it, we'll see that none of these rows, the two existing rows, they didn't have employee
count, but when I select staff from companies, they now have zero as their initial value.
But I could also control that if I set up a default.
So if I instead had said default is one employee, which probably makes more sense because, you know,
is there a really any company that has zero employees versus one is probably the real minimum.
If I had done this, which I can't rerun this right now because there already is a column called Employee
count, but if I had done that, we would have seen the default value would be one for all of those
rows that did not yet have an employee count column.
So next, we're going to learn how to drop columns.