0:02
[Music]
0:11
hello friends welcome back to our
0:13
channel
0:14
so in today's session we discuss about
0:16
alter command
0:19
alter command in
0:21
in the previous session we have seen a
0:23
few commands so now we will see
0:25
alter command
0:28
in sql so what we can do with this alter
0:31
so mainly this alter command
0:34
is used to
0:36
change
0:38
user to change the schema of a database
0:42
schema of
0:43
database right or a structure of a
0:45
database that includes so one we can
0:49
add any column new column
0:52
any new column
0:54
next we can delete
0:56
delete an existing column
0:59
or if you want to
1:00
modify
1:03
the data type
1:05
data type
1:07
okay of existing
1:10
column
1:12
and if you want to
1:17
set constraints
1:19
constraints like
1:22
not null
1:25
primary key
1:29
unique key
1:34
etcetera right so we can set the
1:36
constraints also
1:37
right so or
1:41
we can remove the constraints
1:44
we can remove the constraints
1:46
right so all these things will be done
1:48
with the alter command okay and this
1:51
alter command
1:53
is
1:54
the comes under the ddl command ddl
1:58
command ddl means a data definition
2:00
language so that these commands will
2:02
change the schema of a database schema
2:05
or a structure so we can call anything
2:08
schema off or
2:09
a structure
2:13
right so
2:14
now let us see
2:16
uh one by one i will execute each and
2:19
everything and in the mysql and i will
2:21
show how we can add a column or how we
2:24
can delete a column how we can modify
2:26
the data types or we can set the
2:28
constraints and remove the constraints
2:30
so let me uh demonstrate that by
2:32
executing in the mysql so let us open
2:35
the mysql
2:38
so
2:38
let's clear screen
2:42
so user
2:45
in our demonstrations we are using this
2:48
youtube database so show tables so here
2:51
we are having only one table let me drop
2:53
that table
2:57
so now there are no tables so you can
2:59
see empty set so let us create one table
3:03
without any constraints and anything
3:05
right so
3:06
create table
3:09
student
3:12
so let it take s id
3:15
which is of an integer
3:18
yes name which is of a var cap over 30
3:23
okay percentage which is of a float
3:27
which is of a float data type or
3:29
let me take its integer
3:31
integer
3:33
right
3:34
so you can see query okay 0 is affected
3:37
so
3:38
show tables
3:39
you can see only one table is there
3:41
and there are no data there is no data
3:44
in it okay
3:45
now
3:46
the first
3:47
thing
3:49
the first thing is how to add one more
3:52
column so if you want to add a one more
3:55
column that means age if you want to add
3:58
age so add
4:01
column so what's the syntax here so sql
4:04
so what we have to do so alter
4:07
table
4:10
table name
4:13
table name
4:15
add
4:18
column name
4:20
and the data type
4:22
so simply by using this one it will add
4:24
a column let us check so alter table
4:28
student
4:30
add
4:31
let us add age age which is of an
4:34
integer data type simply use this one so
4:36
query okay 0 rows affected you can
4:38
describe the student so that
4:41
you will be finding the age attribute
4:44
one more attribute has been added and if
4:47
you want to drop so the next one is
4:51
drop column so hope you understood alter
4:53
means we are just changing the structure
4:55
of the database
4:57
so
4:59
alter table table name
5:01
and if you want to delete a column so
5:05
drop
5:08
column
5:10
column name
5:12
so it will automatically
5:14
drop that particular column if you want
5:16
to delete some percentage or is some age
5:20
so you can use alter
5:23
table
5:24
student
5:27
drop
5:28
column
5:29
what is the column name
5:31
let it be it's a percentage or age
5:34
see query okay zero was affected and if
5:36
you observe that
5:38
i mean describe this
5:39
student
5:40
the structure of the student you can
5:42
find only the
5:44
three rows that means the three columns
5:45
are available sid s name and percentage
5:48
so in order to delete any column
5:51
so you can delete that one
5:53
and the next one modify the data data
5:55
now we know that percentage we have
5:57
declared this percentage with an integer
5:59
but the value of a percentage will be
6:01
always a float
6:02
right so if you want to change the data
6:05
type right see
6:09
modify
6:11
data type
6:14
so what is the syntax sql
6:16
alter table
6:18
table name
6:20
so instead of add and drop here you need
6:23
to use a modify modifier class so column
6:27
column name
6:30
column name and the data type what what
6:33
data type we need to give
6:34
so alter
6:36
table
6:38
student
6:39
modify
6:40
percentage
6:43
so column column name and data type so
6:46
modify
6:47
column
6:49
percentage with a float
6:52
yeah see query okay zero rows affected
6:55
that implies the percentage data type
6:57
has been changed so let us check with
6:59
that one describe student database so
7:02
here you can see percentage with the
7:03
float and previously the percentages of
7:05
integer
7:06
so this is how we can modify the data
7:09
type and one more thing
7:11
how we can also add a not null
7:13
constraint
7:14
add
7:17
not null
7:18
how can we add the not null so for that
7:21
also use a
7:22
alter
7:23
table table name so this is a common for
7:26
all the things right alter table table
7:29
name and after that
7:31
simply we can we have to use a add
7:33
constraint add constraint
7:37
okay sorry sorry
7:38
modify
7:40
okay modify similar to our modified data
7:42
type modify the column name
7:47
modify
7:48
the column name
7:50
data type
7:53
and
7:54
normal constraint
7:56
not null constraint see for example here
7:59
null is yes for percentage if i want to
8:02
change this uh not null here so let us
8:05
take alter
8:07
table
8:08
student
8:10
so
8:10
modify
8:12
percentage with the float with the north
8:17
so query okay 0 is affected you can
8:20
describe the student so that
8:22
see float is
8:24
no
8:25
here you can observe
8:26
right not a null not a null constraint
8:30
and then
8:31
adding a constant you more uh unique
8:34
okay add a constraint
8:36
unique
8:38
constraint uni so this is
8:40
also a simple so give the sql
8:44
alter table
8:46
table name
8:49
right so give the add
8:51
constraint
8:53
unique
8:56
unique
8:59
of
9:01
column name
9:03
column so automatically the given column
9:05
will be given as a unit so let us add
9:07
first one so alter
9:10
table
9:12
student
9:13
okay so we'll add a one more field that
9:16
is a
9:17
email
9:18
with a where cat
9:20
of
9:21
50.
9:23
so now you can observe describe student
9:27
so there is a one more field there is an
9:29
email now we will give a email as a
9:33
unique
9:34
okay so alter
9:36
table
9:38
student
9:40
alter table student add constraint
9:44
add constraint
9:46
okay unique
9:49
of
9:50
email so
9:52
automatically the email will be set as a
9:55
unique constraint so which which will
9:57
which will be unique that means no
9:59
duplicates but it can be left blank that
10:02
is a null
10:05
see
10:06
and a key is a unique k is a unit
10:09
similarly
10:11
add
10:13
primary key constraint
10:15
primary key constraint similar to our
10:17
unique key constraint okay similar to
10:19
our unique key constraint so for this
10:22
also we have to use the alter
10:25
table
10:26
table name
10:29
add
10:30
constraint
10:32
primary key
10:35
of
10:39
column name so if it is a single column
10:41
we can give a single column otherwise we
10:43
can give multiple columns with a comma
10:44
separated right so let us
10:47
set this sid as a primary key so alter
10:50
table
10:51
student
10:53
add
10:54
constraint
10:57
primary key of
10:59
id
11:01
so you can see query okay 0 is affected
11:03
that means you can observe describe
11:06
student
11:07
so you can see s id in
11:10
no i mean primary key the key the key is
11:13
a primary key
11:15
okay
11:16
and how to add a constraint check
11:19
add
11:21
constraint
11:23
check
11:24
so if you want to add the constraint
11:26
check so this is also alter
11:29
table
11:32
table name
11:36
add constraint
11:39
check followed by the condition so
11:41
whatever the condition we are giving
11:42
that that we can use so for example we
11:45
can add one more field alter
11:48
table student
11:51
add
11:52
age with integer
11:56
so now we can
11:58
check the condition we can set the
11:59
condition
12:00
so
12:02
describe
12:04
student
12:06
right so here you got the age age field
12:09
so we
12:11
give the check constraint for this age
12:13
alter
12:15
table student
12:18
add
12:19
constraint
12:21
check
12:22
give the condition h greater than 18
12:27
so
12:27
you can now check
12:29
describe
12:32
student okay here we'll be having the
12:35
age the check constraint has been set
12:38
and if you want to drop any constraint
12:41
if you want to
12:42
drop
12:44
constraint
12:47
right so for that also we have to use a
12:50
alter command so alter
12:52
table
12:54
table name
12:56
drop
13:00
drop
13:01
constrain
13:05
constraint
13:07
constraint
13:09
name so whatever the constraint we need
13:11
to drop that should be deleted so here
13:14
you can see alter
13:16
table
13:18
student
13:22
drop
13:25
constraint
13:27
unique
13:31
so let us
13:33
drop sorry
13:34
so let us take alter
13:37
table
13:39
student
13:42
drop index
13:44
so what is a unique
13:46
email so automatically it will be
13:49
dropped so if you want to delete the
13:51
unit key the syntax so delete the
13:55
unique key
13:59
i will write once again okay
14:04
drop constraint unique
14:08
so for this only it will be
14:10
different so sql
14:13
alter
14:14
table
14:17
table name
14:21
drop
14:22
index we need to drop index
14:25
so what is the index email
14:27
right
14:28
so index and
14:32
column name
14:33
right so here alter table student
14:37
drop
14:38
index
14:39
so for which we are giving the unique
14:41
that particular column we have to give
14:43
so automatically it will be deleted now
14:46
let us check the esc
14:47
the student so whether the unique has
14:50
been deleted yes the unit has been
14:51
deleted and if you want to delete the
14:53
primary key also so use the same thing
14:56
alter
14:57
table student
15:00
okay
15:02
drop
15:03
constraint
15:08
give the primary key simply you can use
15:09
a primary key so this is the syntax drop
15:11
constraint and constraint
15:13
so drop constraint primary key so it
15:16
will
15:16
sorry
15:22
sorry alter
15:24
table student
15:27
drop
15:28
primary key
15:30
so automatically it will be deleted
15:32
right so sorry the syntax is slight
15:34
difference you can remove this
15:36
constraint
15:37
so
15:38
this is a
15:39
primary key
15:42
primary okay
15:46
so simply you can
15:49
use the syntax for deleting the primary
15:51
key constraint and the unique constraint
15:53
okay so you can observe here after
15:55
deleting the primary key
15:58
see there is no primary key
16:01
okay so this is how we can use the alter
16:04
command so alt command is used to change
16:06
the schema of the database or a
16:08
structure of the database so in order to
16:11
change the structure we can add a column
16:13
we can drop a column we can modify the
16:16
data type we can add different
16:18
constraints
16:20
right so hope you understood this one
16:22
and uh
16:24
let's stop here and if you are having
16:25
any doubts regarding this one feel free
16:27
to post your doubts in the comment
16:28
section definitely i'll try to clarify
16:30
all your doubts if you really enjoyed my
16:32
session like my session share my session
16:34
with your friends and don't forget to
16:36
subscribe to our channel thanks for
16:38
watching thank you very much