Curso MySQL #03 - Criando o primeiro Banco de Dados

♫ Humming a song ♫ Hello little grasshopper Welcome to another lesson of its database course My name is Gustavo Guanabara I am your teacher And we come to another database course And this time we'll do what everybody's waiting Create our first database So, look It is very important that I be boring again and tell You can not skip steps You will talk like this: "Oh no, I just want to create the database" "Then I will try what the video to create the database" In the first lesson I explained what a database For what it serves . . how it works In the second lesson we saw how to install and prepare the environment So I have to with all prepared environment If you did not see this class, you are not in the prepared environment So, my dear, go playlist Oh!

click here You go straight to the playlist Or there accesses the cursoemvideo. com Or access our YouTube playlist You will know to turn And watch the first classes It is very valuable It's very important You know What I said in previous lessons So my dear Do not skip steps And like all Course Video courses to date, This database of course could not be otherwise And it is geared for the beginner public I've talked it anteriorly And I speak several times If you are already an advanced user If you already know MySQL You will not learn much here Watch the videos Because you will always have something new But the focus here is for the beginner It is for that guy who never created a database Or created few databases Or do not know much MySQL You know how to create in another bank for example You know Access If you already know Oracle But still do not know MySQL Then you will see small differences And once again explaining And focus of this course is in MySQL In SQL In SQL statements We'll just pass on the relational model But it is not our focus of database theory We'll put your hands dirty after all The Video Course is this And besides all And beyond all that you know The Video Course gives us a chewed I give that explained That little way you like I know to do right I organize everything cute And I organized a story here for you For you to understand The structure of a database I know we've seen it In iniciozinho there the first class But now I will explain to you in another way So if you have not understood If you have forgotten Your you are still a little confused Pay attention This time will I present to you my little friend here What's it? Found ugly?

It's nothing, it's cute If you found it ugly, Imagine his name His name is Godofredo He is my friend I put the name I want? Thanks? Godofredo is 32 years old, It is male, It has 78,5kg It has 1,83m And it's Brazilian.

Are features that Godofredo has and you also have You also do not have a name? You do not have an age? Weight?

Height? A nationality? Everyone has it.

And in addition to Godofredo, I will introduce you to his partner Some years ago Godofredo met this beautiful woman The most beautiful of her name yet Dolores Dolores she is 30 years old She is female It has 52. 3 kg Is 1. 65m tall And live in Mexico So Godofredo met Dolores On a trip, I do not know Inventa there in your head what you want So are two people With similar characteristics But I'll introduce you to a third person Fruit Godofredo love with Dolores is born The daughter of Godofredo and Dolores That is called Godolores Did you expect anything better?

The Godolores is 3 aninhos It is female It has 25. 8 kg It has 89 cm And he was born in the United States Yes Godofredo and Dolores traveled to the United States And they had the Godolores Do not interfere in my story The fact here is not my taste for choosing names The fact here is that these three people They have similar characteristics Take a look Any one of the three It has features Which are common to all of them They have a name They have an age They have sex They have the weight The height And nationality Each of the characters I have presented here His own name, his own height, Its own weight, their own nationality Their own sex And that makes the instances And that makes people different from each other And that is the purpose of the database Register separate instances Things that have similar characteristics So basically using this pattern I can register any person Because they all have the same characteristics And note that features are not the values The characteristics are the same All these have names But each has its own name So basically I can get all these people And put, you know, in a container Follow my line of reasoning In this container I will write on the outside and big "People" That is this container Every time I have a person And I want to save this person I'll put this container And all the instances that are inside this container They will have these features All they will have to have these characteristics Some, for example, may be optional How, for example, nationality I may Do not need the nationality of everyone But there is. .

. @ # $% * But it is possible to register this nationality It was clear there? So I can create different containers For different things For example, I can have a container Where I put all the features of Games People and games are different And so they have different characteristics And because of that they have to be in different containers He gave to understand what is the idea of ​​the thing?

So this container games I'll put several games They each have their own characteristics, such as title The manufacturer, gender The platform and all He gave to understand about the idea? So in the world of database I will group things Which have similar characteristics And separate things that have different characteristics And these things have different characteristics I will group among them For all things grouped in containers Have similar characteristics Now I'll take these two containers And place inside a ship I'll put this container and take it somewhere He gave to understand this madness? I think no one ever explained to you database this way This work're hard Come on.

. . But what matters is the class Then he gave to understand how the business works?

I first caught People who have similar characteristics I put in a container I took games, which have other features I put in another container These two containers are important to me I'll take the two And put on the ship Beauty? In the database world that all have names The ship is my database Then the database are data collections Which are separate features But they are arranged in specific locations These specific locations are my Tables Tables store data of things that have similar characteristics And if you want, I can have multiple tables within a database I think this relationship was very clear in your head now The data in the tables also have a specific name, which are records So in short, databases are sets of tables And tables are sets of records Remember the first class when I said the example of chip placed inside a briefcase? Is placed inside a metal file?

It is exactly the same relationship But I decided to extrapolate this and explain in a different way Creating a story here for you But the concepts are exactly the same I just created a way a bit more fun and more crazy little So, to begin with, the first thing I have to create is my database Create a database using MySQL is very simple You simply have to give the command 'Create database' and the database name in my case here I will call 'register' Then, create database records; Yeah, but where I'm going to put this line? Exactly the environment we prepared, all cute, last class Do you remember it? So let's go straight to our environment So I'm here in Windows, and the first thing I will do is open the WAMP Server So WAMP Server installed last class Then I'll open it If you notice down here you have a 'W' red When it turns green .

. . tá yellow .

. . green Now yes, it is already running my server To access the database I can click here and I open my MySQL Workbench In a few seconds it will open the application Let me maximize here And we will open the instance of WAMP Server, which was previously opened Never forget, you have to open the WAMP Server before and expect the 'W' turns green So this is the Workbench environment Basically, you'll have to understand the following Here in the corner, oh, you realize that perhaps is not showing all It may be that your screen is a little more settled this way I do not like this mess he creates So what I will do is hide the bars For that I can hide, oh, the left bar, the bottom bar and the right bar I'm only with Querys, only with my queries here With the instructions I'll put If still here I will open the left column So I can see if what I have created is working For this I will here in schemes .

. . And let's see if we will actually create a database So let's put our hands dirty and type that command there The command we saw was .

. . create database register; we will create a database called 'register' You can type in uppercase, lowercase, you can choose That done, we run the command To run the command that is active, You will click on this lightning here that has a vertical stroke That is to run the command that is selected Clicking on it .

. . The command was successful To verify that it was successful or not Let's open the bottom, here oh create databade register; one row affected (an affected line) The concept of affected rows you will use enough So basically, it's like I did not have a bank When I created a bank I afetei a line, that is, I have a new database Let's see if I really have a new database Come here on the left, oh And we will update the schemes by clicking on this button Now, oh, I have a database 'register' Within the database I have, as I said oh, I have tables I have other things, but I have tables For now we will only work with tables But you have other things such as views, stored procedures, functions So let's start step by step As I said, we created the ship, we created the database What is the registration database The next step is to go to our tables To create our table we have to take a remembered My container of people it has standard features for all of them Then they all table people will have: name, age, sex, weight height and nationality These features are called fields Then there will annotating, look Database contains tables Tables contain records Records are made up of fields He sees how was much like your database class of high school or college Only in a slightly different way Let's start creating this structure there To create we will use a simple command also To create the database was: create database To create table will be: create table Then I create the table 'people' Open, close parenthesis, semicolon We will return here to our environment This command already performed, I will run another command here create table people Open, closing parenthesis semicolon I will organize here about, I'll give a little space Do not worry about this error, since already it will disappear Inside the command 'create table' we will put in the fields: name, age, sex, weight, height and nationality Let's put there Name, age, sex nationality Oh, I'll put a comma here after each So we can exemplify So I have to separate the nationality need not vírgulo because it is the last Do not worry about errors still Now I have to say the type of each of these fields Yes, as well as the algorithms you have to put the variables related to a primitive type *What?

You did algorithm yet? * Here oh! cursoemvideo.

com We have the course algorithm, OK in the playlist, click here You go straight to the playlist, watch it, algorithm is the basis of everything When you learn MySQL and need to use PHP To unify the database with PHP The first thing you have to know is algorithm So we're walking around in Video Course for a greater good thing So prepare Grasshopper So you'll have to know what are the primitive types before placing them MySQL has a lot of primitive types I will select a few for you here Early MySQL separates all types in some families As the family of numeric, date and time literal and epacial So when you work there with the algorithms we have 4 right families: integers, real numbers, characters and logical Here are also four families: Numbers, date and time, the literal and space But they have subdivisions and subtypes The numeric types are divided into: Integer, Real and Logical And here comes one of the big differences MySQL Precision As the data will be stored on disk You need to know scale very well the structure of your table In this first lesson we will not improve much, does not go much further Also because let me to digress here Who knows very database must already be execrating me here, you should already be me cruxificando Jeez, registering age in the database? Calm Grasshopper! We're starting I'll have to undo this business Who has database experience you know not join old in database Also because age is constantly changing But we are in step by step Patience Grasshopper!

Patience is very important Another family that is divided is the family of literals Literals are subdivided into characters, text, binary and collections Tá difficult too? Calm we will not even need it all now I'm just going to illustrate this here be completinho As I said, each of these types has different accuracies Let's see for example what are the integer types An algorithm, integer is integer, the database did not! integer in a MySQL database it is subdivided into several other types of different precisions For example: TinyInt, SmallInt, Int, MEDIUMINT and BigInt The difference of these these types is the amount of bytes that it will use to store this data The TinyInt for example it uses much less data than for example the BigInt So BigInt will use many bytes on disk and will use little TinyInt This will directly influence the number of size you will save For example, age you will not need to use the entire Int You use the TinyInt that uses only 3 bytes in memory The same goes for the actual types that have the decimal, float, double and real The logical type also has two, bit and boolean The logical type he keeps yes and no, true and false, 0 and 1 For date and time we have: Date, DateTime, Timestamp, Time and Year Basically Date is a date, DateTime and TimeStamp will be dates, times and some more information Time only time and Year only one year Let us now for the literal types, starting with the character that has the types Char and VarChar The difference between Char and VarChar's in the name A is fixed the other variant is Basically it is the following: If I say that the name has 30 letters .

. . the name is a literal kind, right?

If I say a name has 30 letters and it is Char, it will store with 30 letters Ah but the guy's name is Joe, only used 2 letters. He will have two letters saved and the rest all filled with space, because the disk you said it will have 30 fixed spaces VarChar is different, you put there, the name will be up to 30 letters, VarChar then if Joe, he will keep only two and will let others it disponoveis he will not put spaces, it will not take 30. It was clear?

If you have not got further ahead you will learn better then there are two types: Char and Varcha. Most us will ultilizar VaChar type text I have: TINYTEXT, Text, MEDIUMTEXT and LongText and there what is the difference between the character and the text? Text is for long texts you want a description of a person you want to save a long text in your database uses the type Text You want to save a name, address, phone, character thing, keep in Char or VarChar type.

Binary type are the Blob types. I have TINYBLOB, Blob, MEDIUMBLOB and LONGBLOB Blob type it allows you to store anything in binary for example, a picture although it is not recommended to store an image in a database You can use such as Blob we also have the collection type that is the Enum and Set Emun and Set are types that you can configure what are the allowed values and at the time of registration so it will accept those values we will this more in the next lesson how specifically the types Enum and Set and finally we have the likes of Space Diometry, Point, Polygon, MultiPolygon and many others the spatial type is a new type of MySql from version 5 if I'm not mistaken and it can store information about Volumetric we will not deal with this kind of data, this spatial family in our course but if you need to register something volumetric and need information of some research in this space family and you will have certainly lots of cool stuff to learn So that there are primitive types of MySql It became very thing? You need to memorize it all, slowly you will utiizando It is important you know and scale a database Again I will make clear In this table we ta now creating this first table, I'm not scaling very well I'm a better sense more forward So let's go back to the command that we had prepared so far And let's start giving types for each field The name, for example, I'll put as varchar (30) Age, as tinyint with 3 spaces The tinyint you do not have the obligation to put the '3' in brackets can only put tinyiny And it indicates that is occupying 3 bytes The Int type, such as curiosity, occupies 11 bytes Sex I'll define as char (1) That is, it will always hold a separate letter if I put or not sex He will reserve a space there because I used char The weight will be float The height will also be float And nationality will be varchar (20) We will put into practice by entering all these values ​​in my MySQL Workbench So come on, name varchar (30) age will be tinyint As I said has no obligation to put (the value 3) You realize that mistakes are fading here in the corner?

char sex (1) weight float height float And varchar nationality (20) Recalling that has no point in the last command, if you put a comma here It will give error down because he is expecting another So I'm not here Basically a MySQL command is not composed So if I put here 8 lines I do not have 8 command, I have one command Because I only have a semicolon ';' The point symbol and comma indicates the end of command Now let's run this command Pause the video on the screen, enter your code and run No use you assister the class so oh . . .

You will see me doing and the time you do you will not remember the command So little Grasshopper, do it! Let's run the command that is marked Clicking on this button He made a mistake here because I do not have a database open For me to open a database I can use the command here oh I'll put up here use registration Or you double-click here to register You are seeing that registration is clarinho here? You leave here selected the command being executed I will run Now 'registration' is the database that is active Now let's run again Now he gave the 'create table' command valid I can come here in tables, we will update the schema Now I have the table 'people' Then the table 'people' has the following columns: name, age, sex, weight, height and nationality See how easy it is business?

You want to see how it works, more or less, the internal structure of a table? Come on. You can come down here I will put a new command I will give the command: 'table describe people' 'Describe' people means people describe to me By the time I run the command It will open the structure, oh Let me hide the bottom to see you get name, age, sex, weight, height, nationality It has all the structures, oh Float, such has some information that we have not seen We'll see in the next lesson But the table was created You saw how simple it is when you use MySQL and you use Workbench?

But basically, you can also do this also in terminal mode Let's take a look here If you click below the WAMP Server icon and click MySQL MySQL console, it will open as a last class, the terminal screen Has no password, remember? Only to Enter And I will put oh here, for example, databases show Him see and show what are the databases created You can see here that there is the database 'register' we create this class The environment is the same Then we open the registration database use registration Look here, I'm using For you see the database that is open, the command 'status' At the command 'status' you have here the current database is the database 'register' I give the command 'show tables' For him to show me which tables are If here the registration database I only have the table people I'll give 'describe people' He will describe just like I did there in the other room It was clear? So I'm not using more here the terminal environment for the thing become more cute, For things get clearer But the goal here is we learn the commands There is one thing I would like to explain very clearly here So we can understand a business When you learned Java with me, I said it was better to use an IDE To stay by entering the command was a very laborious thing In SQL it is very important that you learn the SQL language Why are these commands I am giving you here You will use the time to progrmar in PHP for example Then you can use the PHP commands 'create database' The command 'create table' So this kind of thing is very important you learn the command So, my little grasshopper, Learn the way I'm telling you It's the way I judge most important If you believe otherwise, beauty!

"Oh I've done another course and taught it by clicking the button and making" Beauty, you will learn how to create the database but you will not know the command to create That is valuable time for you to program in languages Like Java, PHP So my goal here is to teach SQL commands and do not use Workbench tools Then I'll get out of here To exit the MySQL, exit, and it comes out You can have more information on the table by clicking on this iconezinho here with a small 'i' Here you will get the information as it is occupying disk What are the columns, which is the database structure What are the indices triggers Foreign keys, partitions, who is allowed So here it is important. To close it, I will close here the tab And you go back to your area of ​​Query We saw the advantage of Workbench? The Workbench will facilitate you in relation to this kind of thing But I have a little problem in this structure With the table we created You should be able to register any of these people who is appearing there on the screen As an example, Mr Vladimir That is 65 It is man, has 115,78kg 1.

85 m height And live in Russia To register new records, we will use another command that is the 'insert into' You will see in another class Usually people call "INSERT INTO" The problem with this structure that we have now created Thus, this structure has several problems, for example, You can not register age The data are not well defined And one very important thing that is a problem that will happen in this database If by chance I equivocate, already registering Mr Vladimir once And want to register it again, it will allow This is because the table that we created to create does not have a primary key But that's a topic for another class Without a primary key can this happen Sign Mr Vladimir twice Not just two, can I sign whenever I want you Vladimir And that's not what I'm going to want in the database But for now, this class for here In the next lesson we will improve the structure of this table We just created So, little grasshopper, that's See you next, the 4th class database course I hope you liked it I hope you have understood I sincerely hope you are practicing Is doing with me in class As usual, at the very end of our class, I wanted to ask you always To subscribe to the channel Give me this honor Being one of the students enrolled We are already more than 120 thousand students So give me this honor? Please? Sign up here in the channel and is more one of the lucky students registered on the course in Video Clicking here You will have access to playlist in the database course All classes will be there all organized All cute, so you can learn right exclusive materials, material slides available Any download you want to do WAMP Server, download the Workbench Everything here, www.

cursoemvideo. com Sign up there Does the database course there For all extra information, all files will be there If you are a teacher and want to use Slides I make available them in pdf there for you You can use in class this madness there that I'm using here during class You can use as long as the rights are held You have to say there, look, I used the Video Course This one is a material created by Professor Gustavo Guanabara Beauty gentlemen? A strong little hug Grasshopper Study always!

Study hard! If you have not done the course algorithm, the course of PHP Do it!