Curso MySQL #06 - Alterando a Estrutura da Tabela (ALTER TABLE e DROP TABLE)

Hello, little grasshopper is welcome to most a lesson from your current database video! My name is Gustavo Guanabara, I'm your teacher. And here we come to the sixth class of the database course.

To talk about a subject continuing what we saw last class. And this time we will learn how to change the structure of a table. then recapitulating We are doing the database class, step by step Each class we see 1, 2 commands in maximum The idea is exactly that And we already made the creation of the database, The creation of the table We already entered data in Table And now we will make a change in the structure of this table For that We will learn two new commands in this class So my dear Active there your server 'MySql' Open your Tools Close Facebook!

I'm seeing it! And we will work! * Bell sound * Just remembering We had created this structure there in our database We have a table named "people" With the fields: Id, name, birth, gender, weight, height and nationality, and my ID is my primary key We will here enable our server 'MySql' Open WampServer Oh, it's red Let's wait to green .

. . It turned green.

Now our server is active We will also open our 'Workbench' Oh there oh, my connection is active I clicked on it It will open the 'Workbench' already with the last status Then we have the database "Register" If you are not bold double-click, to open we have the tables Within the tables, we have the table "People" With all that information oh ID, birth name, all right here already organized And now I will propose some changes to the table The first thing we learn is the command to change the table structure And it's easy ALTER TABLE ALTER TABLE means changing table and the first thing we will do with the ALTER TABLE Add a new column Pay attention to the following: Remember that agent has a data bank In the agent database has the tables The tables have fields The fields in the 'MySql' are called columns So whenever you go to see the word "column" or "column" You are referring to the fields of your table So I will do the following: "ADD COLUMN profession varchar (10)" So what I'm going to put here is that In the registration of people, I will also want to register the profession of them. So So what I will do is add this field (or column) Using the "ADD COLUMN" from the "ALTER TABLE" So we're back here in the workbench And we will do the following If you do not only see the table structure so You can use the describe command describe people; Presses Ctrl Enter And it will show you right Describe for me The word describe can also be compressed as desc So desc at the start command It is the describe command So Pressing Ctrl Enter will also be displayed the same So I have here id, name, birth, gender, weight, height and nationality Let's add here ALTER TABLE people Let's add profession Remember that we can not utilisar accents So profession will be without his uncle (? ) And I'll put the fields in lowercase So I'll put here the type of profession ten And you can ta thinking that ten letters to put a profession is very little It may even be But this class I'll teach you how it increases How are we going to change this kind of thing Awaits little grasshopper has faith I'll put a semicolon here Ctrl Enter If you want to see if everything was just right you squeeze here and there will appear the rhythm No problem Was added, it has a whiting ok Use describe also again Ctrl Enter to describe And now I have id, name, birth, sex, weight height, nationality and profession Exactly the way I asked You saw how simple it is a small comandinho already added a field in your table And there you are wondering But Guanabara I had registered people there what happened to the profession of these people?

Let's take a look If you remember we saw the start of a command select * from people; Pressing Ctrl Enter there has O People were registered And I put the profession O Ja has profissao They already have a profession But with the null value This is because we added a column And he did not put his data Later on in the course we will learn commands to add things to modify data in the records later on you will learn a command we can without having to delete these records and put everything again We will add the profession of each person But there you may have noticed that when we add a column This column will always stop at the end the profession was added as the last column From a peek here If we go to describe O Ctrl Enter You see that the profession was added as the last field But Guanabara and if I want to put the profession in another position It's easy and I'll teach you But before putting the profession in another position You'll have to remove it Deleting a column is as simple as placing a column So to add a column I put ADD COLUMN To remove a column I will use the ALTER TABLE people But using DROP COLUMN The DROP drop word is right If I take one thing off and is DROP So Drp a column Would eliminate this table column The word in DROP SQL is also used in other situations We will see later on Then there's DROP that column So just remembering oh . . .

The profession is here at last The profession is here at last If I come here and put alter table people drop colum profession Needless to say kind or anything, I'll just drop the profession Pressing Ctrl + Enter or an information was given. Here appears profession yet But we will update here oh . .

. The system automatically the profession is gone, if you use the describe . .

. The there oh . .

. It was eliminated from the final Now comes the process of adding it again. We will add it in another position For example, after the name My God!

This work does not end! Before it was hammered, now is an electric clenches! I hope he does not come here.

Eentão after "drop" the column We will choose a new location for it. And it is simple too, We'll put altert TABLE people ADD COLUMN profession varchar (10) like it was before. After name after the word is then, that is: After the name we will add the profession Let's test this command there .

. . So oh .

. . I have people alter table, adding the profession column after name So I have a column name, if you look here I have a name column after the name, it will add the profession let's Ctrl + Enter And see the describe, noticed there oh .

. . After the name I have the profession he added the profession with that structure After the name Eai you may be thinking: I made a basic English course I know after is after and before it is before, so it must have before esxite not, and there you speak And if I want to add a field before any Before the first for example.

since after only places after To do this is very simple! Let's put again, ALTER TABLE people And I want to add a field as the first table field and then I put ADD column, int code for example, first. then to the positioning of the first, I'll put first for any other position after the field, you will put AFTER and if you do not put anything, he considers the last there is not the last parameter then is this, if you want to put a field there at the end, you do not have to say anything places the alter table add column if you want to put after any other field, you will put after and if you want it to be the first field you put first so let's do the test, come on !

! I put people alter table, let's add the code column type "int" there in the first only a small observation following: the word column here, it is optional, if I take it also work I can use a simplified form alter table people adding int code as a first field let's see if it works I give enter, and see if he added, we will see the describe I click on the line, ctrl + enter, look there O, the code and the type int and it is just the beginning and there, saw difficulty in that? I also do not see !

! and another thing you can do to alter table the guanabara I added the professional field but I put only 10 little letters, I want to raise, I put 20 little letters It is easy too dear and we use the same alter table for you change the definition of the structure we will use instead of add column another word instead of add it is to add we will use the modify I just put modify column profession varchar 20 for example to modify the word it means change, ta very close to you understand and you can change primitive type field and all the constraints you can reset the constraints you just can not rename a field So for example, profession. If you want to rename profession and call profi for example.

I can not really use the modify, but agent will learn already have what the word is to rename as well. For now, let's learn how to use modify. so what I'll do here oh .

. . Take it away I'll put people alter table modify colum As I said before, column is optional I can not put I like to put on, especially when I'm lecturing.

and we will change profession for varchar 20 Can I put up constrant such as: not null and give the semicolon I press Ctrl + Enter He gave me a warning, he gave me a warning here and it is easy to understand, gives a peek here. If you notice, I put a constraint not null That is, the profession can not be null. The problem is that when I give select to see the fields, registered records.

Remember that he had added the profession as a field and set it as null? So if you put a new field can not be null and when the mySql adds a new column and puts everything to null. We had a conflict there.

So either you take the contraint or you can do it here I'll show you . . .

you can put for example the default for vizio. Opens and unquote Let's run . .

. and he accepted now Giving select, you will realize that the profession was empty here. Tranquilinho?

So you can never make a pass constraint over the other. if not you will receive this warning But what if I want, guanabara, modify the column name? There's no way I modify using the modify Has no way But I'll show you another parameter that you will utiliazar instead of modify If you want to change the name of a column and also its constraints and its primitive type.

Instead of you will modify using CHAGE But the change has a syntax a little different The change he is chatinho because you have to put the old name and the new name. So if you are not changing the column name, you use the modify. Modify allows only change the primitive type and constrints but if you want to change the name, you'll have to use the change Let's take a look there.

I will create down here oh, alter table people change coloumn profession So profession is the column name that exists at the moment I have a column profession and I will move to prof varchar (20) One thing is this, look . . if I do this type here, he will lose these settings not null and default if I want to keep the old settings, I have to put here .

. . not null and void default I will not put just to see if it will continue with the settings ctrl + enter, let's describe here.

and you realize that it's there . . .

the profession, he accepts null. Formerly did not accept and did not have a default value so I have here the profession now called prof varchar (20) It accepts null So that not null it had previously lost. And the default value is also null, it also lost the default value.

So I have to specify if I use change I can not use the change only to rename the column. I have to rename the column and put all the contraints he has. It was clear?

So I showed you two options The modify, to modify types and constraints And the CHANGE that is also what is to modify the type of constraints and the column name. But what if instead of rename a column, I want to rename an entire table? Can you do and I'll show you how.

To rename the column place the entire table, You will also use the alter table, alter table people and will use the RENAME TO parameter So we will change the name of TableA people to Grasshoppers;) Remembering that we have modify, which is to modify column change, which is to modify column too. And RENAME TO is to change the name of the entire table Let's see s works Then I put up here O After describe I'll put alter table people to rename See here I put on another line but you could put everything on the same line It's just an organization of quetão gafanhatos; I'll press Ctrl + Enter It seems that nothing happened even Even here still people But I'm going to save people here O already gave me an error has said that the table people there She did not hesitate because I just rename it to grasshopper And then it comes poxa but not ta appearing up there It's because you did not refresh, let's shake here The Refresh button (UPPER LEFT CORNER) and now the table I have here is 'locusts' if I give 'DESC ganfanhotos;' 're done there. You saw how simple it is?

and you can still do several other things with the 'ALTER TABLE' but for that I have to create an extra table. let's get the job here. we will create a new table with the following structure: I want the `nome` fields,` descricao`, `carga`,` totaulas` (total classes) and also the `ano` these data will be for courses so I'll create a table chmada courses I notice up there that used the 'CREATE TABLE IF NOT EXISTS' the 'IF NOT EXISTS' or 'IF EXISTS' is a very cool parameter 'CREATE' which is as follows: you will only create a table or a database if it does not exist you will only drop a table or a database if there then you have the 'IF NOT EXISTS' parameters and 'IF EXISTS' we now define the structure of each of these fields the `nome`'ll put as VARCHAR 30, the` descricao` as TEXT .

. . and do not confuse VARCHAR TEXT TEXT as we have seen in the class of primitive types, is for long texts, a description I can put several paragraphs, a number of values the workload ( `carga`) is INT the total classes ( `totaulas`) is also INT and is ano` YEAR Now let's move on to our constraints some fields here planned with constraints the `nome`que is varchar (30) I'll put as 'NOT NULL' this because you can not register an unnamed course, right?

the name is required and another thing, not two courses in the same register with the same name eg course of java, java has course for beginner and advanced course of java, I know . . .

It can not be like this: java, stroke java the guy is cheating. then you have a constraint only to this this constraint is 'UNIQUE' do not confuse with UNIQUE PRIMARY KEY PRIMARY KEY besides being unique it has other features then is this, the UNIQUE is this, I'm not telling you that the name is a primary key it will not identify the records but it will not help put two courses with the same name the `carga` it is INT. `Carga`'s workload is how many hours has the course, whatever.

I'll put a course of 10 hours, 40-hour course It is an integer, but realizes that this number is never negative ah, how many hours have a course? ah I did a course in less than 18 hours which was . .

. oh crap although it has some courses out there that seems less hours . .

. it's normal, but let it go. the fact is in my database here I will not accept courses with negative charge then I will use a specific constraint here to `carga` which is of the type INT I'll put a constraint UNSIGNED UNSIGNED means no signal this will save a Byte for each record that has `registered carga` and 'ano` will use a constraint here pro YEAR it will be DEFAULT' 2016 ', that is: if the registered course you have not set the year of creation then place in 2016 which is the current year notice down there that `ano` YEAR DEFAULT 2016 has no comma because it is the last setting and DEFAULT CHARSET there at the end put utf8 to us have not typing problems, stress there in `nome` field.

Beauty? you should is missing a primary key there, calm my dear, type this way here accompanying his uncle's reasoning. then I will do the following.

I will press <Ctrl> + <a> here select all and delete. we put the new command here CREATE . .

. IF NOT EXISTS want to see? for example, will create a table CREATE TABLE IF NOT EXISTS .

. . I will try to create a table `gafanhotos` `Gafanhotos` will have a` teste` type INT only that the command is totally right.

the problem is that if I create a table `gafanhotos` it will overwrite what I have here all cute, oh, with the columns all organized, I'll miss it all. her do it, oh, if I put it here and give <Ctrl> + <Enter> you will delete the table 'gafanhotos` and create a new but I'll do next, oh, this will only create the table 'gafanhotos` if it does not exist we will see. I will press <Ctrl> + <Enter> here and oh, he gave you a warning, oh, no rows affected We had a warning that it was `gafanhotos` table already existed let's create the table courses, courses that I'll have are `nome`,` descricao`, `carga`,` `and totaulas` ano` put here DEFAULT CHARSET = utf8 we put the names here, types, right?

VARCHAR 30 pro `nome` the `descricao` will be the` TEXT carga` will be INT, total classes ( `totaultas`) also INT and 'ano` will be YEAR let the constraints. my `nome`não can be null (NOT NULL) and will be UNIQUE (unique) put a comma at the end, do not forget, the description will have no constraint, I'll put the comma the `carga` will be UNSIGNED and total classes (` totaulas`) will also be UNSIGNED, okay? and will be YEAR DEFAULT '2016' remembering that even if numeric, I have to put in single quotes tá entered our command, we press <Ctrl> + <Enter>, oh, sees down the green little button it's all ok, hide down here and we'll update here.

so now you realize that we have two tables table courses and grasshoppers table table courses with the columns that I set there. just click here for courses and also you see down here the structure or you come here and places DESCRIBE or DESC courses; <Ctrl> + <Enter>. ta there.

all the more perfeira tranquility? now what we will do is add the primary key, for example: create table and forget to make the primary key ah, I'll have to clear the table and do it again. do not.

You learned how to add a field, not learned? so I put the course code as first column to do this I will use the ALTER TABLE `cursos`, which is the name of our table ADD COLUMN `idcurso` INT FIRST; so I'll put the course of the handle as the first column in my courses table that we have seen, this command has been given. then we do here, oh, ALTER TABLE courses adding the column (ADD COLUMN) Course identifier ( `idcurso`) as integer (INT) in the table as the first column (FIRST); <Ctrl> + <Enter>.

let's take the DESCRIBE here I added the id of the course, ta there, all cute as the first field there're the first column of the table. Beauty? from now you will use another command to add the primary key has no way to add the column and put it as primary key in a command You have to use two and this second command is as simple as the first we'll give ALTER TABLE adding courses (ADD) rather than COLUMN, PRIMARY KEY to `idcursos` We saw how simple it is?

It will be part of what should be done there in the CREATE TABLE, only now in the ALTER TABLE because we forgot to do then instead of typing ADD COLUMN or simply just ADD because COLUMN is the standard we will put ADD PRIMARY KEY in which case you can not omit the PRIMARY KEY so come on, let's ALTER TABLE `cursos` We will add (ADD) to a PRIMARY KEY `idcursos` point-colon, <Ctrl> + <Enter> added, let's see here, the DESCRIBE, if we have it there, oh. the name is unique, oh there UNIQUE, and id docurso ( `idcurso`) is PRIMARY KEY proving that the two are really different. primary key is unique, but it is not simply one the name is unique, but it is not the primary key primary key will be what you define as PRIMARY KEY UNIQUE is another thing do not confuse the balls, little grasshopper you imagined is there, "damn the Guanabara promised me there at the beginning of class that would teach me 2 commands until now only seen ALTER TABLE, ALTER TABLE, ALTER TABLE " ALTER TABLE is a great command, it has several parameters is neither the largest, will be ready there what, when we get in the SELECT thing will start to look pretty.

but I will teach you a new command here Now there is a command, for example: if I want to delete a table I created if I want to delete the table courses I will use the ALTER TABLE and DROP? do not. ALTER TABEL with DROP is to delete columns to delete the entire table command is different for example, if I want to delete the table courses I just created I can use the DROP TABLE command courses; We saw how simple it is?

seen as the DROP word appeared otherwise? the word DROP it can be a parameter ALTER TABLE then ALTER TABLE DROP is to delete column but if the command is DROP, DROP TABLE in the way something or DROP DATABASE something I'll delete the database or table set in command of course I will not delete my table I just created courses, she's cute here I'll create a table any so we can see how that goes then again I will select all <Ctrl> + <a>. I blacked out.

and we will do next, oh CREATE TABLE IF NOT EXISTS now AND START to use it straight away. create a table `teste` with the fields `id` `field nome` and the field, I know, old can not, I will put there, "just delete" does not have no problem I will not put CHARSET here <Ctrl> + <Enter> . .

. I will update here, oh. now I have the tables courses, grasshoppers and test We will add, you know, a record here in the face.

INSERT INTO test the values, I will put the values, '1' pro `id`, 'Pedro' old '22', we put too So we have seen in a previous lesson, right? if you missed it access the list of course play for power have no doubt in instert command INTO not for me to stop sea * free * this too, right? 'Maricota' It has '77' years I will add these three people here pressing <Ctrl> + <Enter> and I pressed the <Ctrl> + <Enter> twice, oh what will happen, oh.

SELECT * FROM test; looks that belezura oh there, Pedro, Maria and Maricota were added three times if you notice here, oh, the `id` is repeating itself, including this because I'm no primary key, saw the importance of the primary key there? then the id primary key was, I would not have this problem but this table here is already about to leave the fact is: I created a table, added records with a single command I delete the table and the data that is within the table then the tables have, oh, travel, have grasshoppers, and test gave SELECT, ta all bonitinho. agora I'll take a DROP TABLE you can put IF EXISTS that is, I'll delete it, you know, if there is a table `alunos` this table, it does not exist, then I will not be able to erase I'll just delete if it exists <Ctrl> + <Enter>, oh, she gave you a warning there saying that the table does not exist if I try to delete test here, which was the table I just created, oh <Ctrl> + <Enter>, there oh, oh, now appeared no blue whiting came a warning the table does not exist we will update courses and grasshoppers, the test table that existed here, no longer exists.

He was quiet? 're very command? this class is over, is calm, breathe, test everything, because this class is over.

but further ahead you will see more command. for now we ended most possibilities the ALTER TABLE and DROP TABLE. and there comes a question: last class I set CREATE TABLE and CREATE DATABASE as DDL commands, setting commands and the INSERT INTO command as DML, data manipulation command and then I ask you, the ALTER TABLE and DROP TABLE would fit in which classification?

DDL or DML? I'll give you time to think. then DDL are command to the table definition I define structure, I move the structure DML commands are for data manipulation then I ask you, ALTER TABLE, and DROP TABLE they messed that?

the structure or data? ALTER TABLE is easier, right? ALTER TABLE moves in the definition it moves in the structure, then it is classified as DDL command DROP TABLE fly to can generate a confusãozinha, why, look, one thing that is important.

at the time that I put there: DROP TABLE `teste` he turned off the table and deleted the data also there cê says: "hey he did not asked for a confrimação? " face, look, watch if you were able to enter DROP TABLE and the table name is a sign that you want to delete. it will not ask you, "you really want to delete?

" "Are you sure you want to delete it? ", Not my brother. you learned to type the entire command, then it will delete has not <ctrl> + <z> so very careful when using the commands, always keep a backup of your database one thing that is very common, never touch the database in production, never move in a database that is currently active Always make a copy for you to move it and by chance you give any DROP, if you drop a table if you drop a column all data is lost and then you can is acanhdo: "ah, so beauty then the DROP TABLE statement is a DML command, it handles data do not.

manipulate data, delete the data was only a consequence of the structure I delete a table. exitem commands to erase data, even to erase all data. that we will see more ahead what we are dealing with here is the following the DROP TABLE, it deletes the table structure of course if I delete the structure, the data does not remain then the classification is now simple.

DROP TABLE is also a DDL command is the two command that we saw today the ALTER TABLE and DROP TABLE are set command ♫ ♫ drums it was clear? then is this, little grasshopper I hope you've enjoyed most this class database course is showing to people "just look at this course, that's cool and all, shows the command . .

. dé is Batuta" and never forget, by clicking here you pose sign up and whenever you have a cool class like this you will be warned clicking on that side you will see the play list with all the classes that make up the database course. and here in the middle of the course on video, where you will be able to attend classes and the classes finished, when all database classes finish you will be able to print your certificate never forget also the boring Guanabara again comes, you can not attend this class holding his chin in his hand if you're here, you know, all this time and so're just looking, looking I use .

. . I know how to use, you have not then activate your MySQL if you got in this class, in the middle and not, not, seen earlier yeah, the play list that I showed you .

. . then.

there you will be able to attend all classes, including how you activate the server has everything step-by-step. here we are in the sixth class so if you are in the 6th class you have the 1st, 2nd, 3rd . .

. do not skip steps ah, but I just came here because of the ALTER TABLE Beauty, you have an active server in your home? there is not?

then back here, back to the first classes and see how you install an environment, how do you do the test and everything "Ah, but I hear that the database is only if I have a server" no, my dear you can try at home. esto testing here without Internet access then you can learn database correctly but once speaking, we're not creating a data bank of course or a conceptual course database we're creating a practical course data bank so I'm not talking about relational model, more forward I am going to talk a little bit but you have to have a basic theory database to can attend this course if you have not, you can break a database without any problem just will not normalize, not only will have the structure as well prepared although I'm making the elaborate structure here, but never forget This video course is for beginners so if you have any complaints, "Oh, him but he did not speak of the relational model," beauty . .

. It is for beginners, I like to teach beginners, starting with SQL see him in practice more forward he will learn other more boring things like relational model, normalization and to be able to evolve as a professional but we go to the chat this lesson we come to an end I want to leave here a thank you to all subscribers everyone who is having patience with those hammer blows with burning lamp, see to the end of this lesson you will understand the burning lamp is light that goes here it is difficult to record these classes but I'm making the effort, the will power then it's only fair that you give joinha share on social networks favoritar this video show the current video to people I'm doing my effort, make your pro video course survive that's it, my dear, practice always study hard. a big hug and until the next hey, I was inventing recording different thing, a different angle, gave me wrong, look ♫ ♫ soundtrack in the background so here you know you have light, right?

is, I'll just show so . . .

"ah it's easy to just write a tutorial", look without light I like wire this is the tutorial, the first courses in videos were this way that taking this hammer noise was that way now . . .

looks like with the light, it is, you know, better only that light is, oh, so here is a normal light bulb burned, okay looking, down, oh, broke the filament Now, there are seconds ago, this happened, fired one and I'm here locked in a room, you know, 3x3 imagine the size of my fright, calculate there. "Ah face is easy, it's just you write and put on the Internet.