Curso MySQL #04 - Melhorando a Estrutura do Banco de Dados

♫ Humming the music of 007 ♫ Anh? Unh? ♫ Music ♫ Hello little "grasshopper"!

Be welcome the more a class of its database course with MySQL My name is Gustavo Guanabara and I am your teacher And we got the fourth class of the database course. Where we do WHAT we had promised before We will improve the structure of the database we've created in the previous lesson Well, basically we will not "improve", we'll redo in a better way. Because there are some commands that we will see in the next lessons, What are the command Alter Table, commands to change the structure of a table already created.

As people not registered anyone and we want to improve, and how we are in the first steps and everything. The people to clear the table . .

. Delete the bank . .

. and create it again. And break we'll solve a problem that was joined Sr.

Vladimir! Remember I told you gave to register several Srs. Vladirmir So we will solve this problem in a very simple way they are the Primary Keys.

So that's grasshopper! Prepares ouch! Open your environment .

. . all cute Because agent will start, and you have to practice!

♪ ♪ Transition If you remember the last lesson, I introduced my friend Godfrey, remember? 32 years of age, male, 78kg . .

. He had some characteristics such as name, age, sex, weight, height and nationality. These characteristics were shared by his wife Dolores, and his little daughter Godolores, and they all had the same characteristics.

And then you must remember to put all of them inside a container, We put the container on a ship and there began to study database from this crazy theory, I created in my insane and sickening head. Last lesson you learned commands CREATE DATABASE and CREATE TABLE Only in a very simple way. We used the well simplesinhos commands, The CREATE DATABASE command without any parameters and the command CREATE TABLE with the minimum of possible parameters So we are still evolving in the database study So Go slowly, calmly we will create better database yet.

With more than one table, relationship between tables. Calm! this course will get to that point.

You just need to have patience! Let's start improving the CREATE DATABASE, for example And as you already know the CREATE DATABASE command it creates a database . .

. . .

. Over to create a database better than this here . .

. Why the database contains data, it contains words, names, numbers From me to specify these data formats will be directly from the CREATE DATABASE If you remember well, when we started to study HTML If you did the course in HTML, here ye, all playlist organized Of course to make a HTML ball show, and there you will join algorithms with PHP, With HTML and everything else and will become a programmer grasshopper. A lot of people thanking us, a lot of people that already getting job getting jobs because of our courses, is I'm very happy, very proud because of the size that this project won.

so if you remember the HTML course we had a formatting problem of accented characters because we work with the Portuguese, and the Portuguese language contains emphases and this emphasis is not American standard for example English words have no accent Portuguese words yes and I presented to you the UTF-8 format The UTF-8 format has special characters, including emphases that agent works And there is not only our stress, there are other types of accents in other languages. UTF-8 is prepared for our characters, characters with accents Latin So, before we create the database here, we will delete the previous database To do this, open your environment, open your WampServer, open your MySQL Workbench And we will work. So I'm already here inside my Workbench environment .

. . and I'm with my MySQL server properly open .

. . is active.

here in my WAMPServer, since the icon is whiting. If you do not know how to make it all work It is why you are a stubborn grasshopper jumped and the first classes. In the first classes, I show how you prepare the environment .

. . as you open the files as you open the Workbench .

. . and makes it work.

Do not skip steps. We have here the registration database, which agent created last class. So, I will not want more this database "Register".

To delete the database "Register", I'll come here oh! Click on the button to create a new SQL File table And I will give the DROP command, which is drop, leave DATABASE register To run, just click on this button, or press Ctrl + Enter. From now on the database "Register", no longer exists.

You saw how simple it is? Then, the CREATE DATABASE creates . .

. DROP DATABASE deletes. I will delete this line here, because I do not need her anymore.

So I do not have a database, we will rebuild the bank. But we will recreate modifying a bit this line. So the command of the previous class was CREATE DATABASE register ";" Remembering that ";" indicates the end of command in MySQL, I can put CREATE .

. . DATA BASE.

. . register ; So are three lines .

. . but it is one single command.

This indicates the end of the command is "". So I'll take the "" . .

. why I'll add things and I will put two parameters these parameters in MySQL are called CONSTRAINTS So I'll put two CONSTRAINTS The first will be DEFAULT CHARACTER SET, I will put UTF-8. So let's put there in your environment.

CREATE DATA BASE register with. . .

DEFAULT SET utf-8 This utf-8 here has to be in lowercase okay? Then, the rest you can choose upper or lower case. Here, in lowercase.

The names, all in lowercase letters. And besides I will set up a business called COLLECTION The COLLECTION, also serves to define the characters Then I'll put there DEFAULT COLLATE utf8_general_ci; And then I can put ";" My command is finished So let's enter default collate utf8_general_ci; it was clear! Now I'm already further improving the structure of my database.

I will create a database and it will already have a standard for character encoding and collation by default all of them facing the UTF8, which as I have already explained are accented characters in the pattern that we will work to run this command, Ctrl + Enter or click on the button to the command execution Ctrl Enter, let me open before precionar Ctrl Enter, let me open here ah, ah list Ctrl Enter, Ò, the last line indicates that the database was created successfully We will update the schema and here I have the register Yeah, but and what is the difference? Let's do this, I will create a database called test here No set Collation Let down here, I will give create databese MYDATABASE. GDB; No space may not have room Ctrl Enter to execute We will update the schematics, I have my bank here What is the difference?

I'll click here on Information my registration and I click here on the information from my bank The registration information You see here the O Default collation is UTF8, as I sent the characterset is utf8 And my bank here, for my environment he created as "latin1 swedish" Sweden, Switzerland, as sla the business And the Default characterset by default is latin1 That was for my environment as a can be even different then This create database is slightly bigger agree with you but he has a special setting for characters that we will need more forward So my dear has not laziness, learn the command in the correct way I will close here, close here I will delete my bank here because I do not want him then drop datebase MYDATABASE. GDB semicolon Ctrl Enter Already I deleted the MYDATABASE. GDB database that was there This is my command, create database with updates we now also update the structure of my table thinking smarter fields In primitive types better sized, and more.

If you remember well your create table command last lesson was precisely this here So I had the fields Name Age Sex Weight Height and nationality And they estavão dimencionados using very simple principles, such as very simploros Use only char, varchar, int, and float. It is important that already face you can optimize the structure of your tables When you probably learned in your school or your college If your teacher did not have as much patience to explain it he sent you to use int I've put here tinyint, I Optimized a bit more I'm saving space Think with me here if one int occupies 11 bytes And a tinyint occupies 3 bytes The most ah difference is small but for each record If I have 1 million people registered disk space savings will be much higher So Think about it, the thing instead of using the char use varchar So that's the kind of thing you have to think when creating a database Let's take an optimized, let's improved in these fields here giving a recalled in the primitive types that we We saw last class So if you remember we had these primitive types. What basically I'm going to do is the following: The first thing It is to work with real numbers.

That's because I used a float, a very general type already there places the value in the format that he decides in the database. We will leave for the decimal type, which is a more customizable type, I will teach you how it works. In addition we will work with date and time types.

If you remember, last lesson I talked to old records is not a legal thing. So we will register the day the person was born. Instead of registering her age.

That's because if I sign age, for example Today, I have 37 years, but here two months, I'll have 38 I'll have to enter the database and keep updating my age? if I register the day I was born By the time I am working with the data He will already know how to calculate my age, according to the day I was born Much smarter So, as I asked you last class to have patience Now we are already solving this problem. You will not register the person's age We will register the day of her birth.

Another thing that we will work is about sex We will work with a collection Not just with a single character So let's put our hands dirty And let us understand what we planned here for this new structure. In this simple table, still simple But now, she'll be more improved. So the first thing we will do is remove primitive types, and change the age to birth.

Now, we will put the primitive types for each of the fields that we set here First of all, we will put also The default character set to create the table. for that we put there at the end, the closing parenthesis Default charset = utf8 is also important when creating the table, you can set The default character set that will be supported. Let's set the name as 30 varchar remains the same primitive type, but I will add some more constraints, remembering constraints are rules We will set for creating things within my database.

The first constraint we will see, is not null It is not "space" null, not null means that you will have to fill in the details By default, if I want to register a person and do not want to inform for example the date of her birth, no problem, by default. But if you want to force, for example that every person has name I do not have to register a person if it does not have a name then these fields are mandatory digitáveis I'll put a constraint not null, it became clear ? !

I'll put the birth date as sex instead of char one, I will use a type of collection What is "enum" so I'll put in there, enum, 'M', 'F'. When I use the set or enum, placing brackets quoted values values, separated by commas, I'm saying what are the values ​​that will be accepted then to sex, he only will accept 'M' or 'F' this will allow you to define the structure in a slightly more rigid To the guy did not put, you know, the letter "A", Sex "A" char with one, I can put "A", now enum, I can only put 'M' or 'F' the weight that was float. Now I'll put as decimal and I will put 5,2 in parenthesis separated by commas and which means that 5,2?

calm, I'll explain! 5,2 is the following, think they are 5 houses in all 5 so that first value and the total homes The second number is the amount of numbers that will be after the comma. So these five, two will be after the comma, and three before the comma.

From now on I can put any weight For example 102. 35 KG You get the idea? Then you can put any pair of values provided that the former is greater And it will indicate the total number of digits And then, the number of digits after the decimal point.

With this, you save space And what sets the exact precision that the number will need to have The point I'm going to put using the decimal type I will decimal colcoar, 3. 2 Only giving a revised, three digits in all two after the decimal point, so I can only have 1 digit comma 2 digits this because no one has 10 meters high, the maximum is two meters high 3 meters height maximum then, and a digit point two digits Thus I save the amount of bytes I'm using to store this data and the data will be more accurate. The nationality will keep 20 varchar only that I will also put a constraint, the constraint default then default 'Brazil' This means the following nationality If anyone typing anything by default will be Brazil saw?

! so I can use some constraints We have seen here some as not null and default Going forward we'll see some more constraints is calm my little grasshopper you still have a lot to learn for now, let's type exactly this command there in MySql Workbench. so come on, create table the name of my table will be "people" open and close parentheses, using default charset = utf8 remembering, utf8 all in lowercase we put the fields here, name, birth, gender, weight, height and nationality I put a comma at the end so I do not forget very important that you do this, step by step You are not trying to type all at once nationality has no comma because it is the last It is very important that you do not try to get decorating entire command Will gradually doing, do as I am doing here so I'm typing again Let us now put the primitive types the name varchar (30) not null, is a constraint so is blue birth and date type, gender enum M or F noting that M or F have to be placed in capital letters, and in single quotes ta ?

! then when the user types it will accept M or F If you want to accept 'M', 'F' lowercased also you put M F tiny Not recommend, leave everything like that. The weight will be decimal 5.

2, and the decimal point, 3. 2 nationality I'll put varchar 20, default, another constraint is 'Brazil' We define here Important to say a business that I did not speak last class also because keeping more accurate information It can happen in any tutorial or material, including the export of your database That the field names are enclosed in backquotes so O I can tell people here between backquotes, name also between backquotes and so on for each it allows me to use for example, fields with accents fields with spaces, although this is not recommended then you can see it here in a few tutorials or some specific material you can see this kind of Syntactic here It is correct completion without any problem I am not going to be typing every time the same thing And do not confuse single quotes with crase with acute accent you have to use certinho every character in SQL in single quotes, and every word of definition between backquotes we run it here CTRL + ENTER he gave an error that the database was not selected you can use the USE, or you double-click O, he has opened the registration database and before you press CTRL + ENTER, not just the no command let's add a few more things Remember a problem that we saw last class remember that with that we command could register all those people there Including its vladimir, which is 65 years old and came from Russia And I said it was possible with this structure we created, registering not only two, but several of his vladimir and anyone else Yes, a database I can have two people calls vladimir, right ? !

or maria da silva They are people, there are homonyms, people who have the same name And a table is important that you set at least one of the fields, as a field we call primary key A primary key field, it is not repeated So for example, when you know, you have your gym So you have your registration in the gym you are studying in college, you have your enrolls in college Register of physical person (CPF) You do not have your name as your primary key you have your Social Security number No two people with the same CPF in the world No two students in college with the same registration No two students in CADEMIA with the same registration then registration or your CPF are fields, primary keys These are fields in which people will not have the same value There will be two tuples, speaking a more technical poquinho There will be two records with the same value for the primary key We will then learn how to solve it Basically we caught this command we just type and will add two lines one at the beginning and at the end I'll take a lightened here so we can focus only on new lines basiamento what I'll do and create a new field Since name can not be the primary key as I said, there are people with the same name as you can imagine, there may be several people with the same nationality with the same sex, exactly the same age with the same date of birth, height, same weight So none of these fields that are here, can be primary keys Then I will create a handle to a person this identifier will be numeric I'll put there, ID will be my field, and will be full and I will put two constraint it will be not null, so why can not you be a student if you do not have a matriculation Then he will compel you to enter we have seen this constraint and have another constraint that I find cool Not always you will use, but in this case we will that is the constraint auto_increment The auto_increment constraint works as follows The first person I register, will be code 1 2 The second code, the third code 3 all automatically to set the ID as the primary key I go there on the last line, after nationality, and I will put PRIMARY KEY and brackets will put ID What is the name of the field that I created there as primary key, beauty? we put the hands on to see how it in my command here O, I will add ID as int and will be not null that is, will not accept nulls and it will be auto_increment The constraints are separated by "space" not by commas Not null is with space between them auto_increment and underline I put a comma at the end, and after nationality I'll put a comma, because I'll have more thing, there has error but will enter below Primary key, O, he suggests I have here primary key and brackets will put ID, has no comma because I no longer command there at the end So this is the command beauty? these are our command today!

From a comparative there as we did earlier what we did now, now better to run this command, you realize here O the table is empty for you to open the registration database, you simply give the command use registration or double-click, O, I clicked twice, opened bunch of test data I clicked twice opened registration data pack open registration database for the cursor anywhere in the command, pressionr CTRL + ENTER to run the command and we will update the schema to see if the table was created now I have the table people clicking on the icon information, now I have the table people with the pretty columns everything organized my primary key, O, my index I have a primary index which is the ID column, now I have a key ♫ ♫ Music We interrupt your database class for a very important statement, actually two warnings the first, you should be seeing some new things here But know that we have a video showing how it all happened and what happened and what is changing the video in progress Has some new things, you should be noticing this video is kind of timeless and the second notice and the next, you're enjoying the database class? We like to create a new data pack? all cute, organized with knowledge of primary keys and everything?

But know that the net host, which is the sponsor Course eternal video the company that supports the video in progress people you have no idea of ​​the support that this company who lives in our hearts, lives here ye, here ye ta the company helps the ongoing video from the beginning of history you have a lot to thank these guys But what I came to show you is the following how do you create a database on the Internet of course I'm just going to start, just the "iniciozinho" You'll have more appearances there during class But What do I want to say is: Thanks to this net Host DATABASE COURSE is going to you And she has a MySQL Professional online solution And I'll show you how: So for you who you are already a subscriber of Hostnet simply enter the company's control panel To access this www. hostnet. com.

br @ @ Control panel You will be diverted to this site Where will you put your 'login' and 'password' Click enter And if you are not a subscriber, On the main page you have the part of the 'sign' There you will be able to put your website on the air (paid) with MySQL Database You will be able to unite all we are learning from the practical part, the part of the servers. On the homepage of your dashboard You will select the domain that you will work here O: (shown in video) My example is criar-meu-site. com @ @ is an area that I have here .

. . I'll click on Database MySQL database By default it comes with an established bank But you can create multiple UNTIL 5 for free Then you can come here "New Bank" And it will create the database of O data: I'll put a password Repeat password: And we will click add Immediately I can access this database directly from my server address Let's click on that address And you will be diverted here pro 'phpMyAdmin' What is a database administrator It is similar to the WorkBench you are using And further ahead on the course I will teach you to use the 'phpMyAdmin' I'm just going to show here that the database is already created You do not even give the "CREATE DATABASE" Only the "CREATE TABLE" So if you look here at O ​​corner: Have your Database 'create-my-site' And I have the ability to create a table here: And create a table is very simple you just learn the command.

"But Guanabara, where I put this command? " Cinch "Little Grasshopper" Just click SQL And here you type the command you want for example: CREATE TABLE Oh he's even helping you The name of my table will be: test Open and close parentheses I'll put there ID it is full AUTO INCREMENT Will have VARCHAR name also 20 e. g.

NOT NULL put "," here "," on here I can also use the primary key PRIMARY KEY id Let's run. Click on GO And ready! your database is created And you can experiment Test commands that we are using in class Within your server So if you are creating a project with MySQL and MariaDB The Hostnet is the right choice We returned to our normal schedule Continues with the class there "grasshopper" ♫ ♫ Music I hope it has been clear what it is a primary key And what it functionality It is of paramount importance Use of a primary key If you do not want repetitions of tuples Within a same table In the next video we'll know how to include data Within this table using the INSERT IN TO command Do not miss any database class Or any other class that we cast Click here on "inscreva-se"(subscribe) and YOU can click the gear and enable the option to receive updates by email This channel teaches me very important things!

you will only gain from it. When signing the channel and receive information about new classes! Clicking here + classes, you will be redirected to playlists, where you will see all classes of travel database, all organized.

And never forget to see the other playlists because they are all organized, you have never seen as organized channel! And here in the middle you have the full experience, www. cursoemvideo.

com Here, you will find information about everything that is released and everything is downloadable in the database course. So sign up and watch the classes! That's it little grasshopper, next week we will have another class, another experience another command of our course for you to learn to master MySQL What is this powerful database solution that everyone should know how to use!

Sincerely, study hard and until next time!