Curso MySQL #05 - Inserindo Dados na Tabela (INSERT INTO)

You who attended the first classes of "Database" must have heard it here, look . . .

(Background noise) Can you hear? A hammer from time to time. I guess you know ?

! I would like to apologize, but . .

. It has another very nearby building that is at work. And so, as much as the studio has acoustic treatment is not an acoustic treatment for hammer So I was in doubt throw it or not .

. . Surely you will prefer the "Video Course" with hammer than having an ongoing video.

So my dear, not complain, thank you! We return to our regular programming . .

. (Noise Fund) . .

. With hammer blows. ♫ ♫ Humming a song ♫ ♫ Background Music Hello, little grasshopper!

Welcome to another lesson. This is the fifth lesson of his course "Database". My name is Gustavo Guanabara, I am your teacher.

and this fifth period it will continue a subject we stopped last class which is the "Data Entry Table" Yes, in classes past we apendeu how to create the database to "create database" and how to create a table with "create table. " Now, you will learn how to enter data feeding these data within the "Bank" you created on your computer. So, back boring Guanabara .

. . Have you created your database?

You have already installed your environment "MySQL"? My dear, look . .

. Several students have already contacted and spoke so "I do not watch cosigo I can not hit the PHP exercises. " Beauty!

During class you do the exercises? If you do not, my dear, no use. So if by chance you get here and not yet made its "database" You are seeing the first class .

. . face, there is no possibility you learn "Database" without practicing.

There is no possibility to learn anything in the area of ​​technology without practicing. And I tell you more: no use you trying to learn anything in life without practice! So pauses .

. . You're not doing marathon, you are not watching a series, you are not watching on YouTube a guy playing Minecraft, for God's sake!

So install your environment . . .

prepares all cute . . .

and let's start! You must remember, the past lessons, I introduced my friend to you His name is Godfrey, he is 32 years old, male, weighs 78. 5 kg 1.

83 meters tall and he was born in Brazil. And we use these data to Godofredo create our structure of the database and our first table. You remember the fields that exist, right?

There are fields: name, date of birth, sex, weight, height and nationality. You remember that we had to make a small change why not join in the old database. If you register age in the database, you have to change every time the person makes birthday.

That does not exist. So if you put the date of birth of the person, it becomes easier. So let's make a change here.

Godofredo is 32 years old but he was born on January 2, 1984 and it is this date that I will enter in my database. So the first step has been taken: we have created the bank `` cadastro` with Person` table. Now we will enter the Godofredo data inside.

To create tables, if you remember well, she had this structure here oh: I used 'CREATE TABLE', put the fields `ID`, that will be my primary key, And I put it as self-incrementável I put a `nome` to accept up to 30 letters. And do not forget that the varchar accepts up to 30 letters. It will not keep 30 letters.

If the person's name is "Godofredo" he will save those letters and the other he will not take. CHAR if he would get all 30 letters, and complete with space, if necessary So it is VARCHAR. It is a variable number of characters, but reaches 30 letters, that the case here.

Also used the NOT NULL constraint for both the `ID`, as for` nome`. He will not accept you do not enter, do not enter the name and do not inform the ID. The date of birth is type DATE.

Sex is ENUM. I podeira use SET in this case. Then he will accept only "M" or "F" capital.

Weight is DECIMAL (5,2). 5 are home to all, 2 houses to decimals, and the height is DECIMAL (3,2). 3 are home to all and, 2 houses to decimals.

Nationality is a VARCHAR 20 and if I do not enter the nationality DEFAULT will be Brazil. Finally, within the structure, I set my PRIMARY KEY, which is my primary key, as the `id` is my first camp up there. The DEFAULT CHARSET will indicate that I can accept accented characters the Latin standard, which is the standard that Brazil uses.

Beauty? This was the last class. Back to being boring.

If you did not Back to School and reviews doing! Now we will learn how to enter data in this table. Data entry is very simple, but before you take the first step, I want to give you a recollected on one thing, which is a very important setting.

As you remember, SQL, as I say it is the search for language, full language, it is separated by categories, it has commands which fall under this category. The CREATE TABLE that are here on the screen as an example, it is a command for setting my table. That is, I will move the database settings.

All SQL command that is part of the structure, which is part of the definition the structure of a database, follows an acronym. Then the CREATE TABLE command, it is known as one of the SQL DDL statements. DDL means 'Data Definition Languange' or Data Definition Language.

Then the commands: CREATE DATABASE and CREATE TABLE we have viewed so far, they are said DDL commands. Pay attention only in the middle of the letter. and set so: it is always D and L.

It is D, and L Date of Language. What changes is the middle letter, the middle letter being D is Definition. So is 'Data Definition Languange'.

These DDL command, they are defined commands. So when creating the database, I use a camando to define a database: DDL When creating a table, I used a table setting for command: DDL also So, you might be asking? But Guanabara .

. . - Exitem others?

- There are others! There are DML, DQL, DCL and a lot . .

. DTL. But we will see this gradually.

For now, let's focus on the DDL's last class, and a new class that we will see in today's class. It was clear? So now you can go to the next step which is to create the command to be able to include data in this table we just defined.

The first thing we must do is to get all the fields. The fields we just put here: id, name, birth, gender, weight, height and nationality. So what you will do is get a list and put the field names.

realize there separated by commas and between a parenthesis "()" big one. So this is the first step. Let's open the environment, right, because I forgot to open here.

So let's turn our machine, I'm using Windows 10. Recalling Windows 10, WampServer and MySQL Workbench. This is my user picture.

Fashion model. I'm pretty much right. (͡ ° ͜ʖ ͡ °) You can speak.

Just turn on the machine, you realize why you will do it in your home. You probably already going to this with the power machine if not you were not looking. No, you may be seeing in the right cell.

And I will open here WampServer, first thing Remembering it will get vermelinho, orange and then green. Orange . .

. green. Turned green, I come here, and open the Workbench.

The environment will be loaded it will tell me that I have an instance in 64-bit wampmysql. I'll click . .

. it will open the structure. I'm with the bank `` cadastro` and the bank teste`, which comes as standard, and I'm not with open registration.

So the first thing we have to do is open one of the databases, if our `cadastro`. You can open one of two ways: either double-click registration, which is simple, but I will give a recollected in charge. So I have a charge here.

USE master; The command "USE master;" It is to use the registry. Now or I click here this raiozinho with dash up or you press Ctrl + Enter. The register bank, has already been opened.

I will open the tables. I have a table `people`s In the table `people`s, I have: id, name, birth, gender, weight, height and nationality. So, come on.

Let's start! I'll put in parentheses, separated by commas, I'll put all the names of the fields. (Id, name, birth, gender, weight, height and nationality) Remember that you can put between backquotes, the name of the field.

But I will not put, because I do not like, but you can put here. Beauty? First step taken.

Forget that little mistake there in front because the command is not yet complete. We will continue here. From now on the bottom line.

Let's take a little space and put the following, brackets as well, and in single quotes, each of the data that will insert, for example: id ( '1'), name ( 'godofredo'), birth ( '2/1/1984') and then, you may be finding it strange that date in this format. The dates in MySQL databases, are given as follows: First year, minus sign, then the month, minus sign, then the day. So is the exact date in the opposite format than we are used to.

day, month and year. Puts year, month, day. sex is ( 'Male'), the weight is ('78 .

5 '), the height is (' 1. 83 ') and nationality (' Brazil '). Nothing different from what we put before, so let's put the data in parenthesis, quoted separately each given, separated by commas and the final, the semi-colon (;).

Come to the environment. And, let's enter it there. Let's put: '1', 'Gordofredo' .

. . [Hammered Hotties] .

. . .

. . .

[I sound in my ear] . . .

"01/02/1984", 'male', Here, the first is the ID, the second is the name Third is the date of birth, room is sex. See, it is in order. Now let's put the weight, '78, 5 ' and you must be an absurd question now.

But "Guanabara"! You taught there in the programming language, what is in quotes, just and only Character String, would only Varchar? No!

What is in quotes are given, pro database: what is in quotation marks are given. What is not in quotes is considered to (Constraint). You will already see it, there.

So. . .

The weight. Let the height of which is '1. 83', Nationality, 'Brazil' It closed the parentheses and point-colon [;].

Pause your video, type exactly here. Do not run yet, boy. Place here, remembering that the only thing that ends the command is the point-colon.

This is not to command, my command is going to have multiple lines Beauty up there? We will continue! From now agent will complete the command as follows, look .

. . I want to insert the table 'people', those data that are up with the values ​​that are below.

Do you agree with me? Do you agree? Do you agree?

Says man! Whether you agree or not? Speaks .

. . I will not go to class, if you do not say!

Go! Speak. Says, "I agree," says .

. . "Laughs" I can not believe you talked to your computer Crazy!

! so what else is enter the table people The values ​​1, godofredo, 2 . .

. etc. quiet?

! only it would be great if we could write in Portuguese But the commands are in English but it is not hard enter the table people saw insert into people values, values and here comes the next guy. It is not wrong, but for God's sake do not insert INTU none of that right is INSERT INTO You have any problems to speak it?

please just talk to me ? ! insert "INTU" bothers me insert "INTU" sucks then this is the command enter the table people with ID fields, name, birth, gender, weight, height and nationality values: 1, Godofredo, The date of birth there Sex it His weight, his height and his nationality.

Simple like that We will complement our command there So I'll put here the command 'insert into' To be 'insert into' but say 'intiu' face people the values only that he saw ? ! but it has no error now I have a ready command to be executed mamamamamama calm, do not run waiting waiting there You must remember the structure of your table, do not you?

I will show you if you remember well, then look at the structure of your table the 'ID' ta with an integer value, not zero . . .

that is, it ta whole, one is full It is not null? is, first, it is not null and he with ta 'AUTO_INCREMENT' if you remember last class, I used the constant 'AUTO_INCREMENT' for it every time a new person is registered the system itself decides, set sequentially as the code will be generated then I do not need to inform the 'Godofredo' is the number 1 I can just say, look inserts it . .

. quiet? so I do not need to specify the ID let's see if it works!

! so what I can do here, O, is to eliminate the ID and eliminate the '1' here then enter the name, birth, gender, etc. godofredo enter the name, birth January 2, 1984 male, weight '78, 5 'tall' 1.

83 'nationality' Brazil ' I do not informed the ID this because it is auto incremented let's run by pressing ctrl + enter "Ahh," before performing O, click here to display the status bar He has given me the use command register, which was successfully! if you run this command and give problem it will show an "x" here then ctrl + enter, look there, entered no problem! wants to see if it has been inserted?

I will show the beginning of a program that we will see further ahead! but it is a simple command I'll hide the bottom Select all people This is all the data for people CTRL + ENTER Down here, I signed up the 'Godofredo' with the date that I put, sex, weight, height and nationality everything fine And the 'ID' was placed as 1 Let's insert another person here I'll put here, 'Maria', was born in '12. 30.

1999' woman is, she has 55,2kg and she has 1. 65m Maria comes from Portugal. right!

Let's run. CTRL + ENTER You realize that's gone down there Let's see if the command was successful . .

. Was! I'll hide here You realize that I do not have the 'ID', right?

! , We will give the SELECT, SELECT and click the Ctrl + ENTER And it will show there, 'Godofredo' is ID: 1 and 'Maria' is ID: 2 And then you may be thinking . .

. but it was lacking the ID up there. It's bothering me this You can put the ID.

Let's insert another person with the set ID. So I'll put the ID here again. And now I do not know what the next name.

I say the next ID. I can not put here, I do not know . .

. 3 I mean even I, will work. But.

. . .

. . Instead of the 3 I will replace the value of this field by a CONSTRAINT Recalling that CONSTRAINT's has no quotes.

So I'll put here oh! DEFAULT can be lowercased okay, no problem not My ID will be the DEFAULT DEFAULT, default means And so, my ID will be the standard . .

. it is! If he's as auto numbering, it will be the standard For example.

. . Remember the estutura bank?

We will return here in the seat structure. If you remember well. Take a look there on nationality Nationality is varchar (20).

And DEFAULT 'BRAZIL' . . .

it is! If I do not tell is 'BRAZIL'. So for example here .

. . Let's add creuza .

. . Creuza is an old lady.

1920 . . .

is . . .

female weighs 50 kg . . .

even 1. 85m . .

. and here I'll put DEFAULT That is, let's see what will happen to creuza. CTRL + ENTER Let's see if it worked.

Oh! It worked! It is the last command there, the 6 command.

"Give" a SELECT. Yeah there. Look there, Creuza .

. . .

. . Has the ID 3, which was made as DEFAULT, It was made her birth, gender, weight, height, and nationality O, Brazil.

I do not informed . . .

. . .

The DEFAULT is Brazil. That is because I set the database structure. We see how the use of DDL's important?

So, I showed you several ways to enter data, but also has more thing. So, as I said, there can replace the ID, instead of 1, and put the word DEFAULT, which is more intelligent. I can also put the DEFAULT there in Brazil that since the Godfrey is Brazilian, I put DEFAULT, it goes like Brazil.

But what matters is the following, if you notice there on the list, he was placed exactly in the order that is there in MySQL. Let's take a peek. If you notice here O, ID, name, birth, gender, weight, height and nationality, They are exactly in that order, ID, name, birth, gender, weight, height and nationality, exactly the same order.

When the order is EXACTLY the same when you do not want to hide or when you do not want to define, any of the fields, if the order is exactly the order of the fields, you do not need to enter the fields at the beginning. Let's see how it works it. So, if the order of my table is ID, name, birth, gender, weight, height and nationality, I can simply omit this O, making .

. . .

. . thus, putting INSERT INTO people, values, and placing the value there.

It is even simpler. So, if you are entering data, and the field order is exactly the order that was set in the bank. So you do not need to say what are the fields.

Let's see if it works? Let's go practice. I'll put someone else here, I do not know .

. . Adalgiza, He was born in 1930, the 11th month (November) On day 2, It's woman, weighs 53.

2 kg, measures 1. 75m, I put the weight 63. 2 kg, as it is high.

I'll put that she was born in Ireland. Who knows why. Then I'll add here.

If the order is: ID, name, birth, gender, weight, height and nationality, simply, according to the rule, I can select it here, and simply, puum, delete, then O's here. Beauty? !

So what will he set? DEFAULT . .

. is the ID, Adalgiza is the name, . .

. this is the date of birth, this one is sex, this one . .

. right ? !

Until the end. You must report all data. We will press CTRL + ENTER.

Let's see if he . . .

He added Adalgiza owner. Come here at SELECT. CTRL + ENTER.

The O there. Dona Adalgiza was added without any problem. The code is 4, the name, date, sex, everything is perfect.

Within the database. Saw? So there is a complete way, and simplified manner.

And there's another technique. Do you think the Video Course would leave out anything? There are ways to insert multiple data simultaneously.

I do not need to be putting the INSERT INTO statement to each record that I want to put, I can use an INSERT INTO, and register how many people I want. How do you do it? I show you.

So let's return to the previous command. Look, let's INSERT INTO people put all the fields, they are optional, if you can even omit. Let's put the figures.

The first person will be DEFAULT, Ana, 22/12/1975, Female, 52. 3, 1:45, United States [US]. Realize there in the end I did not put point-colon ( ";"), I put a comma ( ",") And I will put, the other brackets, with the second person data.

Finished, . . .

. . .

semicolon (";"). Take a look at this code, pauses, and looks well this code. realize each record, each person It is in parentheses Note that the data is separated by a comma, and each person is separated from others as well by commas.

Only the last person, will have point-colon ( ";") to terminate the command. Beauty? !

I hope you have paused, and analyzed. But now we enter and let's see if this works. So come on, Enter the people .

. . I will not put the data here do not, okay?

I will enter Claudius, who was born in 1975, in 4 month (April), on the 1st. No on 22. Male.

It weighs 99 kg. It has 15. 02 meters.

Giant. Claudio is from Brazil. In place of point-colon ( ";") O, comma ( ",") I will open close parentheses and I will put the data from the next person DEFAULT .

. . I will put with tiny here for you to see No problem O.

Pedro comma 1999 12 month, day 3 It is also male, Weighs 87 kg. I can report, is all here, no problem 2 meters, I will do everything . .

. everything round here. Pedro is is is is Brazil.

He is from Brazil? Then I put here, DEFAULT. Comma.

Open parentheses. Let's put one more person Janaina. 1987, month 11, day 12.

It is a woman, has 75. 4 kg Take Hammered ! !

! ! It 1.

66m Ih, is, you know, the United States (US) TOC, TOC, TOC [HAMMERED] Janaina is the United States Ended, semi-colon. You can do this as many times as you want Come on . .

. open the screen here, terminal, and run the command. He ran smoothly.

And let's see SELECT * FROM people Let me hide here. And're there. Look end here.

Claudio, Peter and Janaina. Everything perfect. No problem.

Look at Peter I had put UHH . . .

Here O. 87 and 2. Whole numbers The O there.

87 E 2. Without any problem. You see?

All organizadinho with two decimal places, because I set the database that the weight has two decimal places. 're In doubt? Look weight there It has 5 houses in all .

. . .

. . And two decimal places.

As time has 3 houses in total and 2 decimals All data were successfully added Quiet? ! !

Did you like this class? There is one more thing, just for you, before finishing. Learn more a novelty.

Remember that I said, that the CREATE BATABASE and CREATE TABLE are DDL commands are setting commands, then, as I said earlier. DDL commands are defined. What counts is the middle letter.

The command we saw today, is the INSERT INTO command. And the INSERT INTO command, he did not serve to define the structure of the Bank, it served to enter data, it served to manipulate data. And because of this manipulation, INSERT INTO command, is the first command you will learn, it is .

. . .

. . DML classification, this M It is 'Data Manipulation Language'.

Then, the data manipulation commands They are considered DML, and data definition commands They are considered DDL. It was clear? So this was the first command DML, we are learning.

Then, there are several other DDL commands, various other DML statements. Let's see it gradually. Because this class, just end.

As always, at the very end. I wanted to ask you honor I wanted to ask you, for you become if you already are not. A "grasshopper" inscribed.

Then the. by clicking here, you will subscribe to the channel. Did you like this class?

Gives Joinha. Favorita. Shows pros friends.

Gee, look at this class. Look patience that this guy is having. Look at that thing of Job.

Look at those fucking hammered. And never forget, after you sign up. Click on the gear and say you want to receive by email.

We have several courses. It has ongoing progress. It has more than one course, you know you're having .

. . .

. . You know, a lot of information there for you.

Then you can not lose face. It is the channel where you will learn things. And if you are here in this class just to take a peek.

Welcome. Never forget, here . .

. I folded his finger. folded leaves.

You will have access to all database classes that have come out so far This is the fifth class? So I think you've assumed . .

. . .

. There are other four (4). Then, by clicking here, you will have access to all playlists Up here, the interactive part, has a "izinho" here ?

, You can tighten. Here you will have to check some courses So then. To the database, is not important that you have previous knowledge, no.

But it is important to . . .

put up here. A course Algorithm. A course of PHP.

Because? Later, you will be able to unify these database we are creating with your PHP code. So little grasshopper If your focus is not Database.

O study here. Algorithm and PHP Because this will be of paramount importance, inside your resume . .

. . .

. of . .

. IT education Again, I wanted to thank you for all the comments. For all the positive comments, for all constructive criticism.

Huh. The guys who complained of hammering. I'm sorry.

Again I say. To stop now. You prefer not to have course?

Or course with hammer? I decide To travel to with hammer. That's it, little grasshopper.

Always practice, study Make your database, install your environment, configure all cute, enter it you want. Create extra tables, you can create more than one table. Experiment.

I will teach more forward and delete table changes and everything. So goes step-by-step, will you are on the right track. A big hug!

And to the next class.